Excel Avanzado

Función Personalizada

2011-12-13T00:18:00.000+01:00

Descargar el fichero: Funcion_Personalizada.xlsm Programar una función personalizada en #Excel es una tarea sencilla y que nos puede reportar un gran ahorro de tiempo. Dispone de un vídeo donde podemos ver el proceso para crear una función que calcula el margen comercial de un establecimiento comercial. Vídeo

Eliminar filas con cierta periodicidad en #Excel

2011-12-11T12:37:00.000+01:00

Descargar el fichero: elimina_filas.xlsm Para eliminar filas que se repiten con cierta periodicidad hemos diseñado una macro. Es frecuente que al importar informes de otras aplicaciones obtengamos un texto plano que al volcarse a Excel lleva muchas líneas de "basura" que deseamos eliminar. Si las filas que deseamos suprimir se repiten de forma periódica podemos utilizar la macro que explicaremos

Fechador: Establece la fecha automaticamente al cambiar un dato

2011-12-11T07:52:00.000+01:00

Descargar el fichero: fechador.xlsm Fechador contiene una macro que establece la fecha y la hora junto a cierta celda cuando ésta experimenta cambios. Esto se logra manejando los eventos, concretamente el evento Change que detecta cuando se produce un cambio en alguna celda de una hoja. Podemos ejecutar la macro cuando el cambio se produzca en todas las celdas, o bien únicamente en una de ellas,

Creación de una campana de Gauss con #Excel

2011-12-09T01:21:00.000+01:00

Descargar el fichero: campana.xlsm Veamo cómo crear una campana de Gauss. Trataremos de ver gráficamente el Teorema Central del Límite. Partimos de una serie de números aleatorios que se distribuyen según una distribución uniforme entre 0 y 1. U[0,1] Los aleatorios se obtienen con la función de Excel: =ALEATORIO() El teorema central del límite nos dice que necesitaríamos infinitos valores,

Sumar los valores en negrita

2011-11-25T09:40:00.001+01:00

Descargar el fichero: suma_negritas.xlsm En #Excel es frecuente trabajar introduciendo colores o negritas en las celdas que deseamos resaltar, y luego se presenta la tarea de tener que contar o sumar los valores de las celdas que hemos resaltado. En otra entrada de este blog ya dimos varios métodos para sumar las celdas según su color. Puedes verlo en este enlace: Contar y Sumar los valores de

Crea una hoja Index en tu #Excel

2011-11-17T10:46:00.004+01:00

Descargar el fichero: index.xlsm Hagamos un indice en la primera hoja de un libro de Excel, para listar todas las hoja y establecer un hipervínculo que nos lleve a cada hoja. Cuando en un libro de Excel tenemos muchísimas hojas, en ocasiones es aburrido llegar a ir hasta una concreta. Para facilitar nuestro trabajo vamos a establecer una primera hoja llamada Index en la que vamos a escribir el

Consolidar tablas de datos

2011-11-01T22:47:00.000+01:00

Descargar el fichero: xPoblamun_TD.xlsx Ya hemos tratado el tema del consolidado. Para ello utilizábamos Tablas Dinámicas con rangos de consolidación múltiple. Puede consultar el Post que se publicó: Tablas Dinámicas con Rangos de Consolidación En esta ocasión realizaremos el trabajo de consolidación con la herramienta Datos, Consolidar. En la versión de Excel 2007 se encuentra en el menú

Rangos variables en fórmulas

2011-10-30T13:44:00.000+01:00

Descargar el fichero: FormulasVariables.xlsx Algo con lo que siempre hemos soñado los usuarios avanzados de Excel es con la posibilidad de poder modificar la sintaxis de una fórmula para que se adapte de forma automática a nuestras necesidades. En este caso, no es que se logre completamente este sueño, pero si seremos capaces de modificar la función SUMA para que el rango que sume sea el que

Libro Personal en #Excel

2011-10-19T00:44:00.000+02:00

Descargar el fichero: RentaGeoFracECO2011.xlsm Mostramos un vídeo de cómo se crea el libro Personal.xlsb en Excel 2007. Este libro nos permitirá disponer de todas las macros que contenga, para que se puedan ejecutar desde cualquier otro libro que abramos en el futuro. En este caso vamos a incluir en el libro personal las funciones creadas por el usuario: VAgeo VFgeo que calculan en valor actual

Facturación por meses

2011-10-18T01:18:00.000+02:00

Descargar el fichero: facturacion_mensual.xlsx Veamos cómo podemos totalizar por meses con varios métodos: SUMAR.SI, SUMAPRODUCTO y sobre todo con funciones matriciales. Destaca el quinto método que no necesita emplear la columna auxiliar 'Mes' (columna D). Se ha creado un nombre de rango que es en realidad una fórmula. Se denomina 'miMES' y la fórmula es: =MES(Fechas) Este nombre de rango

Seleccinar datos con un desplegable

2011-10-16T01:40:00.002+02:00

Descargar el fichero: desplegable.xlsx Utilizar un desplegable es una tarea cotidiana e intuitiva. Lo que pretendemos es que al elegir con el desplegable un año, la consecuencia sea que una tabla con datos se rellene automáticamente con datos de ese año. Conseguiremos nuestro proposito con el uso de la función INDIRECTO de la que podemos ver unos cuantos Post publicados en este mismo Blog. Para

Marcar la celda si se encuentra en ella una palabra o fragmento

2011-09-29T00:11:00.000+02:00

Descargar el fichero: busca_fragmento.xls Al buscar una palabra o un fragmento en Excel podemos utilizar Control+B y así buscaremos a mano, de uno en uno. Pero si deseamos buscar un fragmento que pudiera estar dentro de una celda, y deseamos hacer la búsqueda de forma masiva necesitaremos una pequeña macro que nos ayude. En este caso creamos la macro y la utilizamos junto con Formato Condicional

Último valor de la fila

2011-09-02T07:42:00.005+02:00

Descargar el fichero: ultimo.xlsx Puede llegar a se muy útil determinar cuál es el último valor de una fila o columna. Para lograrlo utilizaremos funciones matriciales. En esta ocasión veremos dos hojas en las que planteamos dos casos en los que se emplean estas fórmulas matriciales que calculan el último valor escrito en una fila. Hoja1 Veamos cómo se calcula con una fórmula matricial el

Convertir una tabla de doble entrada en una base de datos (sin repetición)

2011-08-15T00:31:00.001+02:00

Descargar el fichero: matricial_extrae.xlsx En #Excel podemos convertir una tabla de doble entrada en una base de datos. Los datos de la tabla de doble entrada tienen que ser sin repetición. En este caso se trata de países que no se pueden repetir. En la Hoja1 del fichero matricial_extrae.xlsx estudiamos cómo extraer información de una tabla de doble entrada. Puede verse el siguiente post: http

Tiempo de Respuesta

2011-08-11T08:41:00.000+02:00

Descargar el fichero: TiempoRespuesta.xlsm Una macro en #Excel que calcula el tiempo de respuesta de un servicio técnico, excluyendo domingos y festivos. Se trata de una empresa que proporciona el servicio técnico a sus clientes. Se pretende controlar cuántas horas se tarda en proporcionar el servicio, desde que se toma nota del aviso hasta que queda finalmente solucionado por el técnico que

Tablas Dinámicas y Elementos Calculados

2011-08-05T08:55:00.001+02:00

Descargar el fichero: TDelementocalculado.xlsx En un post anterior habíamos hablado de Campos Calculados de una Tabla Dinámica de #Excel. Ahora vamos a ver un caso un tanto especial en el que necesitamos calcular la variación porcentual de cierta magnitud económica entre los años 2010 y 2011. El problema es que la información de partida no distingue entre ambos años, sino que simplemente existe

Teoría de Colas con #Excel

2011-07-30T10:17:00.001+02:00

Descargar el fichero: colas.xlsx La Teoría de colas es una especialidad de la Investigación Operativa que nos permite estudiar el comportamiento de servicios atendidos en los que se producen esperas. Es el caso típico de una cola en un banco, o un microprocesador que debe atender una lista de trabajos. En este artículo vamos a considerar un servicio médico de una consulta donde un único médico ha

Control de almacén y pedidos en #Excel

2011-07-24T11:04:00.000+02:00

Descargar el fichero: pedidos.xls Controle su almacén y gestione sus pedidos de forma sencilla en Excel. Veremos un caso simple de control de Stocks y un avisador de realización de pedidos. Si su almacen es realmente complejo, no pretenda gestionarlo con Excel, para ello existen en el mercado programas específicos, y si desea crear su propia aplicación ha de utilizar una base de datos relacional

Un Juego en Excel: El que quite la última piedra, pierde

2011-07-20T20:25:00.004+02:00

Descargar el fichero: piedras.xls Juguemos en Excel. Podemos programar una macro que nos permita jugar contra el ordenador a un juego que consiste en ir quitando piedras y donde el último que quite es el que pierde. Al iniciar el juego, el ordenador genera de forma aleatoria un conjunto de piedras que oscilan entre 9 como mínimo y 30 como máximo. Al pulsar sobre el botón denominado 'Nuevo Juego

Programar una función de redondeo

2011-07-19T23:25:00.001+02:00

Descargar el fichero: redondear.xlsm Redondear un número es una tarea común que podemos hacer con la fórmula de Excel REDONDEAR. En éste post nos proponemos programar una función en VBA, mediante una macro, que produzca el redondeo. Redondear a cero decimales Comencemos redondeando a cero decimales. Así el número 3,14159 (¡me suena este número!) se puede redondear a cero decimales como 3. Y el

Eliminar Filas y Columnas vacias

2011-06-20T00:52:00.003+02:00

Descargar el fichero: LimpiaFilas.xls Es frecuente que un informe generado por otro programa se exporte a Excel, por ejemplo en SAP. Muchos programas contemplan la exportación directa a Excel, y en otras ocasiones lo que se hace es generar un fichero plano, de texto. Sea por un método o por otro, habitualmente los informes contienen filas vacías, y columnas vacías. Presentamos una macro que

UserForm para alimentar una base de datos

2011-06-16T10:54:00.000+02:00

Descargar el fichero: UserForm.xls Vamos a crear un UserForm, que es un formulario que nos permitirá pedir al usuario que introduzca una serie de datos para ir creando una tabla en Excel, en forma de base de datos. Supongamos que se trata de la empresa UniLink, Co. Ltd. Su departamento de personal está creando una pequeña base de datos sobre Excel. Se trata de una tabla con 4 campos: Nombre,

Selección de Gráficos

2011-06-15T10:40:00.000+02:00

Descargar el fichero: selectivo.xls Podemos seleccionar diferentes gráficos con solo pulsar sobre un botón o marcar una opción. Vamos a ver un sistema que permite elegir unos u otros datos sin más que seleccionar una opción. Estos datos son los que alimentan el gráfico y por tanto conseguimos que el gráfico cambie simplemente eligiendo una opción al pulsar un botón. Hoja 1 Supongamos que

Rango Dinámico

2011-06-15T01:10:00.000+02:00

Descargar el fichero: RangoDinamico.xls Si tenemos una tabla que cambia de tamaño nos vemos obligados a tener que redefinir el rango de los datos que queremos incluir en un gráfico o en una tabla dinámica. Si la tabla contiene un número de filas que varían con frecuencia es bastante tedioso tener que cambiar el rango de datos para el gráfico, y lo mismo con la tabla dinámica. Vamos a exponer un

Convertir una Tabla de doble entrada en una base de datos

2011-06-13T08:57:00.000+02:00

Descargar el fichero: Cronograma.xlsx Vamos a convertir una tabla de doble entrada (con cabeceras de fila y columna) en una auténtica base de datos, esto es, una tabla donde la información se organiza por campos (columnas). Para ello utilizaremos Tablas Dinámicas con Rangos de Consolidación Múltiple. Posteriormente, partiendo de la base de datos, resumiremos la información utilizando nuevamente