sábado, marzo 04, 2006

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

19 comentarios:

  1. 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.

    ResponderBorrar
  2. Hola

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

    ResponderBorrar
  3. 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

    ResponderBorrar
  4. 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

    ResponderBorrar
  5. 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?

    ResponderBorrar
  6. 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.

    ResponderBorrar
  7. 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.

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

    ResponderBorrar
  9. 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?

    ResponderBorrar
  10. 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.-

    ResponderBorrar
  11. 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.

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

    ResponderBorrar
  13. Hola,

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

    ResponderBorrar
  14. He llegado aqui de casualidad buscando informacion sobre lo mismo.

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

    ResponderBorrar
  15. me ha gustado mucho

    ResponderBorrar
  16. 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.

    ResponderBorrar
  17. 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

    ResponderBorrar
  18. 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

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.