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...
Y queremos llegar, mediante fórmulas a:
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
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.
Casi listos. Falta componer el listado por columnas.
Disponemos encabezados por filas y columnas.
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)))
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 ;-)
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...
Y queremos llegar, mediante fórmulas a:
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
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.
Casi listos. Falta componer el listado por columnas.
Disponemos encabezados por filas y columnas.
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)))
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 ;-)
Hola Ismael
ResponderEliminar¿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
;-)
EliminarMuy bueno también
Mucho más elegante Daniel!
ResponderEliminar