miércoles, 5 de diciembre de 2018

Desapilando columnas de datos en Excel.

Hoy estaba viendo algunos vídeos (sobre Excel claro) y me he fijado en una explicación del gran Oz du Soleil en su canal de You Tube (que te recomiendo!) - ver aquí -

En concreto el vídeo explicaba como usando las nuevas funciones dinámicas de la última versión de Excel se podía desapilar columnas de datos ('Unstack a Column of Records').
En mi caso pensé en la forma de conseguir lo mismo con las funciones de toda la vida ;-)


Imaginemos tenemos unos datos como siguen, dispuestos en una única columna...

Desapilando columnas de datos en Excel.



Y queremos llegar, mediante fórmulas a:

Desapilando columnas de datos en Excel.



El primer paso consiste es obtener el patrón de repetición: Empresa, País, Facturación
así en el rango D3:D32 añadimos la fórmula:
=RESIDUO(FILAS($C$3:C3);3)

con FILAS($C$3:C3) tendremos la numeración de las filas 1,2,3.. 26,27
y al aplicarle la función RESIDUO tendremos 1, 2 y 0 como resto de dividir el número de fila entre tres, lo que nos proporciona el patrón 1,2,0

Desapilando columnas de datos en Excel.



Siguiente paso intermedio. En el rango E3:E32 añadimos la fórmula:
=CONTAR.SI($D$3:D3;D3)

consiguiendo una ordenación de lo que deberían ser los registros si estuvieran bien situados.

Desapilando columnas de datos en Excel.



Casi listos. Falta componer el listado por columnas.
Disponemos encabezados por filas y columnas.

Desapilando columnas de datos en Excel.



Y acabamos insertando, en el rango H4:J13, la fórmula:
=INDICE($C$3:$C$32;SUMAPRODUCTO(($E$3:$E$32=$G5)*($D$3:$D$32=H$2)*(FILA($B$3:$B$32)-2)))

Desapilando columnas de datos en Excel.



La función descrita recupera los datos de la columna de 'valores' con la función INDICE.. basándose en la posición de fila dentro del rango seleccionado... cosa que hacemos con la función SUMAPRODUCTO y su estructura matricial condicionada...

Obviamente la forma descrita por Oz du Soleil es mucho más dinámica y ágil.. pero si aún no has dado el salto de versión, esta es una buena alternativa ;-)

2 comentarios:

  1. Hola Ismael

    ¿Y por qué no usar la fórmula "desref" de toda la vida?.

    En tu ejemplo, si te he cogido bien las referencias de celdas, en tres celdas consecutivas, de izquierda ha derecha.

    =DESREF($B$4;(FILA()-4)*3;1)
    =DESREF($B$5;(FILA()-4)*3;1)
    =DESREF($B$6;(FILA()-4)*3;1)

    Se arrastran las fórmulas y obtienes el resultado.

    Es cutre, pero de un apuro te saca ;-)

    Un abrazo


    ResponderEliminar