Cómo crear una tabla de un sumario en Excel

lunes, agosto 30, 2010

Esta nota viene a colación de la consulta de un lector que tiene una tabla que totaliza los resultados de una encuesta y quiere convertirla en una tabla plana (lista) que le permita usarla como base de datos para analizarlos con una tabla dinámica.

Es decir, por lo general partimos de una tabla (o lista) de datos y los analizamos con una tabla dinámica. Supongamos ahora que queremos hacer exactamente el proceso inverso, partir de la tabla totalizada (no dinámica, estática) y convertirla en la base de datos de origen.

Por ejemplo, supongamos esta tabla que totaliza los resultados de una encuesta



En Excel Clásico (97-2003) hacemos los siguientes pasos:

1 - accionamos el menú Datos-Tablas Dinámicas-Rangos de consolidación múltiples


2 – en el siguiente paso del asistente de tablas dinámicas señalamos "campos de página personalizados"



3 – en el próximo paso señalamos el rango de la tabla totalizada en "rango" del asistente



4 – en el último paso de asistente elegimos la ubicación de la nueva tabla dinámica y apretamos Finalizar



Excel crea una tabla dinámica idéntica al origen a la que agrega los totales por columna y fila



EL truco ahora consiste en arrastrar los campos "Fila" y "Columna" fuera de la tabla dinámica. Ésta queda reducida a una sola celda con el gran total



El último paso consiste en hacer un doble clic sobre el total (53 en nuestro ejemplo).Esto hace que Excel cree una hoja con el detalle de todos los datos



A partir de esta tabla (o lista) podemos crear tablas dinámicas para analizar los datos que previamente recibimos totalizados.

Este video muestra todo el proceso.



En Excel 2007 tenemos que hacer una pequeña manipulación antes de comenzar con el proceso. El asistente de Tablas Dinámicas no está presente en la cinta de comandos. Pero podemos agregarlo a la barra de herramientas de acceso rápido. Abrimos las opciones de Excel en el botón de Office y en Personalizar elegimos la opción "Comandos que no están en la cinta"



Esto nos permite acceder al asistente, tal como en Excel Clásico.

Esta técnica aparece en el libro de John Walkenbach "Favorite Excel 2007 Tips and Tricks"

13 comments:

carolsurz 31 agosto, 2010 09:21  

Gracias Jorge por este nuevo post,
Me parece muy interesante todo lo relacionado con Tablas Dinámicas; como siempre que posteo aprovecho para hacer una pregunta, ahí va:
Al final, lo de darle doble click a la celda de totales (53), ¿no se podría hacer en el 53 que aparece en la Tabla Dinámica original creada, es decir en la que no arrastras ni columnas ni filas?

Un Saludo

Jorge L. Dunkelman 31 agosto, 2010 10:49  

La tabla original no es una tabla dinámica. Esa es toda la idea. Recibimos una cuadro sumario y queremos revertirlo a la base de datos que le dio origen.

carolsurz 31 agosto, 2010 13:11  

Hola Jorge.
Cuando en mi anterior comentario escribía sobre la "Tabla Dinámica original", me refería a la que generas, es decir a la en el texto pones "Excel crea una tabla dinámica idéntica al origen a la que agrega los totales por columna y fila"; pues en dicha tabla dinámica si le das al 53 (celda N8), sale el mismo resultado que arrastrando filas y columnas.

Jorge L. Dunkelman 31 agosto, 2010 15:19  

Correcto. Nuevamente estás en lo cierto.

Anónimo,  31 agosto, 2010 22:41  

Como siempre Jorge, excelente.
una duda: a que se refiere excel con "Rangos de consolidacion multiple" y "Campos de pagina personalizado"
Gracias

Jorge L. Dunkelman 31 agosto, 2010 22:53  

Son opciones del asistente de tablas dinámicas. Dado que la opción Rangos de consolidación múltiple tiene pocos usos prácticos, no vale la pena profundizar en el tema.

alecartuja 01 septiembre, 2010 17:33  

Hola - gracias, esto me es muy útil e interesante ya que a veces tengo que trabajar mucho con los datos fuente antes de poder operar con ellos.
Una pregunta - imagina que insertáramos otra columna (B) con otro criterio - el país por ejemplo. (Así que la primera fila sería encuesta1-españa-valorpregunta1-valor pregunta2..., la segunda sería encuesta1-portugal-valor pregunta1 etc.). ¿Podríamos hacer la misma operación entonces?

Jorge L. Dunkelman 01 septiembre, 2010 19:44  

Mucho me temo que no, porque la columna de los países entraría en el área de datos y no en el área de filas.

alecartuja,  24 mayo, 2011 17:24  

Hola Jorge -
Leí el siguiente artículo (de 2009) en Bacon Bits esta mañana. Te envío el link porque contiene un truco para hacer lo que te preguntaba en septiembre.
Saludos
http://datapigtechnologies.com/blog/index.php/transposing-a-dataset-with-a-pivottable/

Sandra Pacheco 18 septiembre, 2013 21:47  

Buenisima la ayuda, pero tengo una solicitud adicional. Necesito fijar el valor de 2 columnas, es decir que me devuelva en la lista el valor de varias columnas como puedo hacerlo? ejm. tengo nombre, sede, producto 1, producto 2 producto 3, y al final quisiera una lista que me arroje en la primer columna el nombre, en la segunda la sede, en la tercera el tipo de producto y en la cuarta la cantidad, es decir finalmente solo estaría listando las columnas 3 4 y 5 asociadas a las columnas 1 y 2.

Jorge Dunkelman 20 septiembre, 2013 23:22  

Sandra, no termino de entender el planteo. La idea de la nota es que tenemos una matriz de varias columnas y la queremos convertir en un base de datos plana, que es lo que estás describiendo como punto de partida. Te sugiero que me mandes el archivo para que me haga una idea de lo que querés hacer.

Anónimo,  23 enero, 2014 17:34  

Muchísimas gracias!! Me has resuelto un problema que se me planteaba muy a menudo y me daba muchos quebraderos de cabeza.

Jorge Dunkelman 23 enero, 2014 17:49  

Me alegro que te haya resultado útil. Si estás usando Excel 2010 o 2013 te sugiero leer esta nota donde muestro otra técnica para el mismo problema.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP