Otra técnica para "aplanar" tablas de datos

sábado, noviembre 23, 2013

Hace un poco más de dos años atrás publiqué una nota sobre cómo crear una tabla sumario en Excel. La idea es "aplanar" una matriz de datos de manera que pueda ser usada como base de datos para tablas dinámicas.

¿Qué significa aplanar en este caso? Veamos este ejemplo de un cuadro de ventas de productos por meses



Para poder aprovechar eficientemente las posibilidades de las tablas dinámicas, tendríamos que organizar estos mismos datos de esta manera:



Si usamos Excel 2010 o 2013 podemos usar, además de la técnica que mostré en la nota anterior, la nueva herramienta Power Query (anteriormente, Data Explorer).

El primer paso consiste en descargar e instalar el complemento.

Luego convertimos nuestra matriz de datos en Tabla



Seleccionamos la tabla y en el menú de Power Query usamos la opción "From Table"



para introducirla en la ventana de Power Query



Ahora viene la parte menos evidente del proceso (esperemos que Microsoft lo mejore en el futuro): en la ventana del Query seleccionamos las columnas (clic al encabezamiento de la columna manteniendo el botón Ctrl apretado) y abrimos el menú contextual con un clic del botón derecho; en el menú hacemos un clic en la opción "Unpivot Columns"


con este resultado



Ahora apretamos el botón "Done" en el editor del Query. Excel crea una nueva hoja con el resultado de Query



Todo los que nos queda por hacer es reemplazar los encabezamientos de las columnas que el Query creó ("Mes" en lugar de "Attribute" y "Ventas" en lugar de "Value", en nuestro ejemplo).

La gran ventaja de este método sobre el anterior, es que podemos actualizar el Query en caso de hacer cambios en la tabla de origen. Por ejemplo, si agregamos una línea para el nuevo Producto 11 a la tabla de datos, todo lo que tenemos que hacer es usar la opción "Refresh" en la opción Query de la cinta de opciones



El único inconveniente es que debemos volver a reemplazar los encabezamientos ya que Excel vuelve a poner los valores por defecto (Attribute y Value).

Si agregamos una nueva columna a la tabla, digamos el mes de Mayo, tendremos que rehacer el Query, ya que en este caso la nueva columna aparecerá como tal también en el Query (no ha sido incluida en el proceso Unpivot Column).



12 comments:

Anónimo,  23 noviembre, 2013 15:27  

Gracias por la publicación

Anónimo,  25 noviembre, 2013 01:22  

Excelente aporte, muchas gracias

Anónimo,  26 noviembre, 2013 22:43  

Hola Jorge, Como Estas

Referente al tema, al instalar el complemento me dice que el Office tiene que ser instalado con la opción .NET Programmabality, ya reinstale el office, pero no veo la opción de habilitar el .NET, en este caso que se puede hacer, tengo Office 2013.

Saludos

Jorge Dunkelman 27 noviembre, 2013 14:52  

Muy curioso ya que una vez instalado el Office 2013, el complemento se instala sin problemas.

Anónimo,  29 marzo, 2014 01:27  

Buenas noches,
Me preguntaba cómo poder hacer esto con power pivot en vez de power query. ¿hay forma de hacerlo igual de sencillo?
Gracias!

Jorge Dunkelman 29 marzo, 2014 10:56  

Hasta donde dan mis conocimientos la funcionalildad Unpivot existe sólo en el Power Query.

Anonimo MaaPer,  29 marzo, 2014 13:57  

El problema que tengo es que no puedo instalar el complemento en el excel 2013 de mi pc. Viene plataformado de mi empresa con explorer 8 y me pide explorer 9 :(

Jorge Dunkelman 29 marzo, 2014 17:04  

Te sugiero que instales el navegaor Chrome o que pidas en tu empresa que te instalen una versión más reciente del Explorer. Sino puedes usar el método que expongo en el primer enlace de esta nota.

Anónimo,  23 junio, 2015 22:24  

Ojo que el power pivot solo esta disponible en el office 2013 en la version profesional plus, quien tenga una version inferior le toca comprar el paquete excel de forma independiente.
Quiera comentar que a la que se empiezan a relacionar bastantea tablas externas de tamaño considerable estos complementos se vuelven bastantes inestables y avisa que se queda sin memoria. No es la primera vez que se me queda el ordenador colgado y eso que el ordenador es nuevo.

Jorge Dunkelman 24 junio, 2015 06:54  

Estimado,
Power Pivot puede instalarse como complemento en Excel 2010 (puedes descargarlo aquí).

Ninja Gerwalk 20 agosto, 2015 15:56  

¡Hola Jorge!
Ante todo, te felicito y agradezco por esta tremenda fuente de conocimiento.

Ayer crei encontrar un posteo tuyo donde utilizabas DESREF (y creo que CONCIDIR) para llegar a un resultado similar. No logro encontrarlo.

Lo que necesito es, a partir de una matrix de doble entrada ("sectores" en la vertical y "fechas" en las columnas) generar una tabla en columnas para procesar con TD y gráficos dinámicos (generando gráficos por semana, mes y año). Es una matriz de asistencias a reuniones, por lo que los datos de sectores y los estatus (presencias/ausencias/no requeridos) se repiten en los diferentes registros.

Dicho posteo no encontrado, ¿sirve para lo que describo?

Mil gracias de antemano.

Jorge Dunkelman 20 agosto, 2015 17:41  

¿Y por qué no usar la técnica que muestro en esta nota? También esta técnica te puede servir (si es que entendí la consulta).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP