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 ;-)
Excel
via [EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDAS] [https://ift.tt/2GwimHF]
December 5, 2018 at 05:15AM
No hay comentarios.:
Publicar un comentario