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

Usar "Datos-->Consolidar" para comparar listas en Excel

En entradas anteriores he hablado sobre cómo comparar listas en Excel usando Formato Condicional.
Otra manera de comparar dos listas en Excel es usar Consolidación de Datos (menú: Datos ---> Consolidar; sobre este tema les invito a leer esta entrada).

Explicaremos esta técnica con un ejemplo. El cuaderno con este ejemplo se puede descargaraquí.

Supongamos un cuaderno que contiene cuatro hojas a las que les hemos dado los nombres "comparar", "lista 1", "lista 2" y "lista 3". Cada una de estas tres últimas contiene una lista de socios de una institución. Nuestra tarea es comparar entre las listas de manera que sepamos quien figura sólo en una de ellas, quien figura en dos de ellas (y en cuáles) y quien figura en las tres.
Empezaremos mostrando la técnica para comparar dos listas.
1 – En la hoja Lista 1 agregamos en la columna B el valor "1" al lado del nombre de cada socio



2 - Hacemos lo mismo con la lista 2, agregando en este caso el valor "2"
3 – Abrimos la hoja "Comparar" y seleccionamos la celda A1.
4 – Abrimos el menú Datos--->Consolidar



5 – Completamos los datos como está indicado en la figura aquí abajo



y pulsamos "Aceptar"



La tabla que aparece nos indica la situación de cada socio de acuerdo al valor que recibe:
1 = aparece solamente en la lista 1
2 = aparece solamente en la lista 2
3 = aparece en ambas listas

Para aplicar esta técnica con tres listas, procedemos de manera similar con la lista 3 pero en la columna B escribimos el valor "4" (no 3 como pudiéramos suponer).



Volvemos a la hoja "Comparar", seleccionamos la celda A1. Abrimos el menú Datos--->Consolidar y agregamos el rango A:B de la hoja Lista3



Pulsamos "Aceptar" y obtenemos esta tabla



Los resultados los interpretamos de la siguiente manera:
1 = aparece sólo en la lista 1
2 = aparece sólo en la lista 2
3 = aparece en la lista 1 y en la lista 2
4 = aparece sólo en la lista 3
5 = aparece en la lista 1 y en la lista 3
6 = aparece en la lista 2 y en la lista 3
7 = aparece en las tres listas



Categorías: Manejo de Datos_

Technorati Tags: Comparar Listas, Consolidar Datos