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

sábado, marzo 04, 2006

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: ,

19 comments:

Anónimo,  06 septiembre, 2007 17:58  

Quisiera agradecer por tan valiosa página para que podamos mejorar en el manejo de Excel.
Mi pregunta es ¿Puede la tabla dinámica repetir los datos en los subtotales no dejarlos en blanco?
Esto me interesa de sobremanera agradeceré su respuesta.

Jorge L. Dunkelman 06 septiembre, 2007 19:07  

Hola

disculpa pero no entiendo la pregunta. Que quieres decir con "repetir los datos en los subtotales no dejarlos en blanco?"

Anónimo,  05 marzo, 2008 16:20  

Hola,

Hace tiempo que te consulté una duda, y ahora vuelvo de nuevo. La consulta es la siguiente: Tengo una tabla dinámica, sobre un conjunto de datos de la misma hoja. Este conjunto de datos se van actualizando dependiendo de unos valores que tomen otra celda.
La cuestión es ¿cómo podria hacer que los datos de la tabla dinámica se actualizaran automáticamente al actualizarse los datos del rango de datos?. Lo estoy intentando hacer sin hacer una macro, ya que este fichero lo tienen que habrir terceras personas y no me gustaria que visualizaran el mensaje de macros de windows.

Muchas gracias de nuevo.
Email: javierm.prado@telefonica.net

Anónimo,  05 marzo, 2008 16:22  

Hola,

Hace tiempo que te consulté una duda, y ahora vuelvo de nuevo. La consulta es la siguiente: Tengo una tabla dinámica, sobre un conjunto de datos de la misma hoja. Este conjunto de datos se van actualizando dependiendo de unos valores que tomen otra celda.
La cuestión es ¿cómo podría hacer que los datos de la tabla dinámica se actualizaran automáticamente al actualizarse los datos del rango de datos?. Lo estoy intentando hacer sin hacer una macro, ya que este fichero lo tienen que abrir terceras personas y no me gustaría que visualizaran el mensaje de macros de windows.

Muchas gracias de nuevo.
Email: javierm.prado@telefonica.net

Jorge L. Dunkelman 05 marzo, 2008 18:33  

Hola

la única forma que conozco de actualizar automáticamente una tabla dinámica, es con macros (o más precisamente, programando un evento).
Qué quieres decir con no me gustaría que visualizaran el mensaje de macros de windows?

Anónimo,  19 agosto, 2008 21:10  

creo que Anonimo se referia a repetir las etiquetas de las filas a la izquierda cuando dijo : "repetir los datos en los subtotales no dejarlos en blanco?"
ejemplo 2004, 2005 en la tabla muestra.

Anónimo,  22 febrero, 2010 21:42  

A mi también me interesa esto, ¿Se puede la tabla dinámica repetir los datos en los subtotales no dejarlos en blanco?
Por ejemplo 2004, 2005 en la tabla muestra.
Porque en otro archivo estoy haciendo una sumaproducto con 2 condiciones, una es la col. A y la otra es la col. B, pero en la col. A solo aparece el dato una vez y luego no se repite para abajo en las celdas, estas quedan en blanco hasta que aparece un datos distinto en la col. A.

Anónimo,  22 febrero, 2010 21:43  

¿Puede la tabla dinámica repetir los datos en los subtotales no dejarlos en blanco?
Por ejemplo 2004, 2005 en la tabla muestra.

Jorge L. Dunkelman 22 febrero, 2010 21:55  

No logro entender la consulta. Si te refieres a que el año aparezca al lado de cada mes en la fila pertinente, la respuesta es no.
¿Podrías explicar de otra manera el segundo párrafo de tu consulta?

Anónimo,  23 febrero, 2010 20:51  

Si, me refería a eso. Y muchas gracias por tu pronta respuesta.
En cuanto al segundo párrafo, tomando tu ejemplo de modelo y solo para el Agente uno, necesito traer el dato del total de ventas del agente uno correspondientes a enero de 2004.
Eso lo hago poniendo en el destino la formula sumaproducto con 2 condiciones, si la Col. A es igual a 2004 y si la Col. B es igual a enero, entonces que sume la Col. C.
Pero el problema se me presenta cuando quiero traer el dato de febrero de 2004 porque en la Col. A de febrero no hay datos.
Esos datos los podría sacar de la tabla que le dió origen a la tabla dinámica, pero en mi caso no puedo porque la tabla de origen tiene más de 20.000 líneas y el archivo destino tarda demasiado en abrirse. Por ello quería hacer una tabla dinámica y sacar los datos desde allí.
Saludos y gracias nuevamente.-

Jorge L. Dunkelman 23 febrero, 2010 20:56  

Usando IMPORTARDATOSDINAMICOS (GETPIVOTDATA) puedes extraer los datos en forma dinámica sib que el tamaño de la tabla dinámica o la tabla de origen tenga alguna influencia notable en el tiempo de respuesta de la hoja.

Anónimo,  03 marzo, 2010 22:36  

Investigué un poco sobre importatdatosdinamicos y me resultó útil.
Muchas gracias Jorge.

mikkel 09 marzo, 2010 20:15  

Hola,

Muchas gracias, me está sirviendo de mucho tu blog, estoy aprendiendo mucho.

Anónimo,  21 enero, 2011 17:19  

He llegado aqui de casualidad buscando informacion sobre lo mismo.

Solo comentar que en la version 2010 si es posible repetir todas las etiquetas.

Anónimo,  07 septiembre, 2011 16:18  

me ha gustado mucho

Anónimo,  03 junio, 2012 07:00  

Hola.
Al tratar de descargar el archivo de ejemplo me lleva a una página de CHICAS HOT. Favor revisar.
Muy interesante el blog.
Gracias.

alvarez1 13 enero, 2017 07:47  

Hola yo quisiera saber como hacer para q cuando actualizo o cambio los datos en mi base de datos se actualizen tambien en la pivot table. Thank you

Jorge Dunkelman 14 enero, 2017 07:37  

Apretando el botón Actualizar (Refresh o Refresh All) en el menú de las tablas dinámicas. Si te referís a la actualización automática, fijate en este post

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP