Manejo de pequeñas bases de datos en Excel – Referencias dinámicas con nombres (NAMES).

En la entrada de ayer sobre tablas dinámicas vimos que cada vez que agregamos datos a la base de datos, debemos actualizar la referencia al rango. De no hacer esto, las nuevas líneas de datos no aparecerán en la tabla dinámica.
En el ejemplo de ayer hicimos esto manualmente. Excel nos permite construir referencias dinámicas, que se actualizan con los cambios en el tamaño de la base de datos. La técnica para hacer esto es utilizar "nombres" (NAMES).
Si no estás familiarizado con este tema, puedes ver mi nota sobre uso de nombres en Excel.
Como ya explicamos, los nombres pueden referirse no sólo a rangos sino también a fórmulas. Para crear el rango dinámico en nuestro caso definiremos un nombre que contendrá la formula DESREF (Offset en la versión inglesa). El archivo de este ejemplo se puede descargar aquí.
La sintaxis de esta fórmula es la siguiente; DESREF(referencia ;filas;columnas;alto;ancho) donde:

referencia: la celda en el ángulo superior derecho de la lista (en nuestro caso será A1);
filas: para nuestro uso será siempre 0
columnas: para nuestro uso será siempre 0
alto: la cantidad de filas en nuestra lista
ancho: la cantidad de columnas en nuestra lista.

Para convertir esta fórmula en dinámica, usaremos la función CONTARA para determinar los valores de los parámetros alto y ancho. Esta es nuestra fórmula:

=DESREF(Hoja1!$A$1,0,0,CONTARA(Hoja1!$A:$A),CONTARA(Hoja1!$1:$1))


como se puede ver comenzamos en la celda A1, contamos cuantos valores hay en la columna A (que representan la cantidad de líneas de la tabla) y también contamos la cantidad de valores en la línea 1 (que representan la cantidad de columnas en la lista).

Ahora sólo nos queda reemplazar la referencia al rango de datos en la lista por el nombre que acabamos de definir. Los pasos a seguir son:
1 – activamos la Hoja2 y ubicamos el mouse en alguna de las celdas de la tabla dinámica
2 – abrimos el menú Tabla Dinámica--->Asistente



3 – pulsamos el botón "atrás"; en el diálogo que se abre reemplzamos el rango



con el nombre (la forma más práctica es pulsar F3)





A partir de este momento, la referencia de la tabla dinámica se adaptará automáticamente de acuerdo a la cantidad de líneas que agreguemos, o que borremos, a la base de datos.

Categorías: Funciones&Formulas_, Manejo de Datos_


Technorati Tags: Tablas Dinamicas, Pivot Tables

Manejo de pequeñas bases de datos en Excel con tablas dinámicas (pivot tables) - Actualización de Datos

Ayer dimos una breve explicación sobre las tablas dinámicas (pivot tables) en la primera nota sobre manejo de pequeñas bases datos en Excel.
Vimos que a partir de una lista que cumpla ciertas condiciones mínimas, Excel puede construir tablas dinámicas con las cuales podemos realizar análisis de datos en forma eficiente y sorprendentemente rápida.
La lista debe tener encabezamientos en su primer fila, de tal manera que Excel los pueda reconocer como tales. Aquí se puede descargar el archivo con el ejemplo de lista para construir tabla dinámica.

Los elementos de la tabla dinámica son campos, que podemos ordenar como filas o columnas, y datos.
Otros elementos que debemos conocer:
1 – la barra de herramientas de las tablas dinámicas





En esta nota sólo nos referiremos icono "actualizar datos" (el signo de exclamación "!"). Por definición las tablas dinámicas no se actualizan con los cambios en la base de datos. Para actualizar la tabla debemos pulsar el icono "!".

2 – Al apretar el botón derecho del mouse, cuando el cursor se encuentra en la zona de la tabla dinámica, se abre un menú.



Aquí nos referiremos sólo al menú "configuración de campo". Para nuestro ejemplo veremos cómo usar las opciones de "Resumir por…" y el botón "Número".



"Resumir por…" determina el tipo de operación que utilizará Excel: suma, cuenta, promedio, etc. Importante saber: la primera opción de Excel es usar la operación suma. Si alguno de los datos no es reconocido por Excel como número, la función utilizada será cuenta. Esto también es cierto si hay alguna celda en blanco en el campo de los datos.

"Número" permite formar las celdas con los mismos formatos de del menú Formato de celdas.

A esta altura ya estamos en condiciones de generar tablas dinámicas a partir de listas en hojas de Excel.

En la próxima entrada veremos como usar referencias dinámicas. Por qué es importante esto? Si agregamos datos a nuestra lista, digamos otro año de ventas, y actualizamos la tabla dinámica, veremos que los datos nuevos no aparecen en la tabla.
Esto se debe a que no hemos actualizado la referencia al rango en la definición de la tabla dinámica.
Si descargan este archivo con el ejemplo de tabla dinámica, verán que he agregado las ventas del año 2003. Sin embargo, también después de pulsar el icono de actualización los datos no aparecen en la tabla dinámica.
Para que esto suceda debemos hacer lo siguiente:

1 – Pulsar el botón "Tabla dinámica" en la barra.

2 – En el diálogo que se abre, pulsar la opción "asistente"
3 - En el nuevo diálogo apretar el botón "atrás"

4 – Corregir el rango para incluir toda la lista

5 – Al apretar "Finalizar", los nuevos datos serán incluidos en la tabla dinámica

En lugar de actualizar la referencia manualmente, podemos utilizar referencias dinámicas, técnica que veremos en la próxima entrada.

Categorías: Manejo de Datos_

Technorati Tags: Tablas Dinámicas, Pivot Tables

Manejo de pequeñas bases de datos en Excel con tablas dinámicas (pivot tables).

Es un hecho bastante común manejar pequeñas bases de datos en Excel. Por lo general se trata de tablas de hasta mil o dos mil líneas y hasta unas 10 o 15 columnas. Excel ofrece mucha flexibilidad y es muy fácil montar este tipo de base de datos.
Hay muchas consideraciones que hacer sobre este tema, pero en esta entrada nos limitaremos a los aspectos prácticos.
Una de las mejores funcionalidades de Excel son las tablas pívot, llamadas tablas dinámicas en la versión en español.
Haremos una rápida revisión del tema, en su versión más sencilla. El archivo del ejemplo se puede descargar aquí.
Supongamos que tenemos esta tabla (que será nuestra "base de datos") de ventas de agentes. En cada línea aparece el nombre del agente, el mes, el año, el monto de ventas





Para generar una tabla dinámica (tabla pívot o pívot table en su versión inglesa), activamos el menú Datos--->Informe de tablas y gráficos dinámicos



Se abre un diálogo de tres pasos para construir la tabla dinámica



En el primer paso, en nuestro ejemplo, elegimos la alternativa más sencilla (y la más común), "lista o base de datos de Microsoft Excel", es decir que los datos s encuentran en una hoja de Excel (en el mismo cuaderno o en otro),

En el segundo paso



Debemos señalar la ubicación del rango de datos. En una próxima nota veremos como construir una referencia dinámica para este rango,

En el tercer y último paso debemos decir a Excel dónde poner la tabla dinámica



En nuestro caso utilizamos le existente Hoja 2, señalando la celda A1



Excel abre una plantilla vacía donde construiremos nuestra tabla dinámica



Los elementos con los cuales construimos la tabla (llamados dimensiones y medidas), aparecen en el cuadro pequeño a la derecha de la plantilla (lista de campos de tabla...).
Todo lo que hay que hacer es arrastrar estos elementos al lugar donde queremos que aparezcan en la tabla. Naturalmente el elemento "ventas" lo pondremos en el área de datos. La palabra "campos" que aparece en la zona de filas y de columnas se refiere a los encabezamientos de las columnas en nuestra "base de datos".
Por ejemplo en esta tabla



hemos ubicado los campos "año" y "mes" en la zona de las filas y el campo "nombre" en la zona de las columnas.
Si queremos cambiar la disposición de la tabla dinámica, todo lo que hay que hacer es arrastrar los campos fuera de la tabla y disponerlos de otra manera.
Por ejemplo, para comparar los totales de ventas por agente y por año, ponemos el campo "nombre" en la zona de las filas y el campo "año" en la zona de las columnas



En la próxima nota mostraremos algunos cambios de diseño elementales y como construir tablas con rangos dinámicos.



Technorati Tags: Pivot Tables, Tablas Dinamicas