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

Comparar listas en Excel con Consolidación de Datos, una variante

Esta entrada es un complemento a la entrada de ayer sobre el tema. Sucede que un compañero de trabajo, después de leer la nota, me dijo que muy bien, pero él tiene las listas en una sola hoja. Cómo hace, entonces, para comparar entre ellas con el método Datos--->Consolidar?
Supongamos que tenemos estas tres listas en una sola hoja (les invito a bajar el archivo con el ejemplo aquí):



Para hacer la comparación con el método explicado ayer (agregamos a cada lista el numero 1, 2 o 4 al lado del nombre de cada miembro), seguimos los siguientes pasos:
1 – Agregamos tres columnas a la izquierda de la hoja, donde se anotarán los resultados de la consolidación
2 – como siempre recomiendo, definimos nombres para los rangos de las tres listas (cómo hacerlo pueden ver en esta nota).



3 – Seleccionamos la celda A1 y activamos el menú Datos--->Consolidar. En la ventanilla "referencia" anotamos los nombres de los rangos (lista_1, lista_2). La forma más práctica de hacerlo es pulsando F3 y eligiendo el nombre.



4 – Pulsamos "aceptar" y obtenemos la tabla de resultados como en el ejemplo de ayer



El significado de los resultados, como a lo indicamos, es el siguiente:
1 = aparece en lista 1
2 = aparece en lista 2
3 = aparece en las dos listas

Para comparar entre las tres listas, definimos un nombre para el rango de la lista 3 (en nuestro ejemplo será lista_3), lo agregamos en el menú de Datos--->Consolidar



Y obtendremos una nueva lista de comparación. Sobre el significado de los resultados vean esta nota.

Categorías: Manejo de Datos_

Technorati Tags: Comparar Listas, Consolidar Datos