sábado, noviembre 23, 2013

Otra técnica para "aplanar" tablas de datos

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

  1. Gracias por la publicación

    ResponderBorrar
  2. Excelente aporte, muchas gracias

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

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

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

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

    ResponderBorrar
  7. Anonimo MaaPer29 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 :(

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

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

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

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

    ResponderBorrar
  12. ¿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).

    ResponderBorrar

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