Para el siguiente ejemplo, seguimos trabajando con el modelo que suelo usar habitualmente, y, queremos añadir una línea en blanco, después de cada provincia.
En la celda I2, voy a traerme las provincias únicas, para ello, usaremos la función UNICOS.
=UNICOS(Ventas[Provincia])
Con la función ORDENAR, ordenamos.
=ORDENAR(UNICOS(Ventas8[Provincia]))
Ahora voy a usar la función CONTAR.SI donde como argumento rango seleccionamos la columna provincia, y, como argumento criterio, seleccionamos la primera provincia.
=CONTAR.SI(Ventas[Provincia];I2)
Vemos que la provincia de Albacete aparece 56 veces.
Lo podemos hacer de forma matricial, para ello, solo debemos de añadir el operador de rango derramado a I2.
Ahora, tenemos las veces que aparece cada provincia.
Pero, necesito que estas dos columnas, sean una sola matriz, para ello, vamos a usar la función APILARH (HSTACK), donde como argumento matriz1, es la función ORDENAR y UNICOS.
=APILARH(ORDENAR(UNICOS(Ventas[Provincia]))
=APILARH(ORDENAR(UNICOS(Ventas8[Provincia]));CONTAR.SI(Ventas8[Provincia];I2#))
Pero, debemos de hacer una modificación, porque el argumento criterio de la función CONTAR.SI esta buscando la provincia en la celda I2, y, esos datos ya no nos van a hacer falta, por lo que después del signo igual, voy a poner la función LET, abrimos un paréntesis, declaramos una variable y almacenamos ORDENAR(UNICOS(Ventas[Provincia])).
=LET(a;ORDENAR(UNICOS(Ventas[Provincia]))
Punto y coma, volvemos a poner la función APILARH, como argumento matriz1, es la variable A, y, como argumento matriz2, es la función CONTAR.SI, donde como argumento rango sigue siendo la columna provincia, y, como argumento criterio, es la variable A.
Cerramos paréntesis.
=LET(a;ORDENAR(UNICOS(Ventas8[Provincia]));APILARH(a;CONTAR.SI(Ventas8[Provincia];a)))
Aceptamos, y, tenemos el mismo resultado, pero en una sola matriz, ahora ya podemos eliminar los datos primeros.
Para este ejemplo las cantidades deben de estar ordenadas de mayor a menor, en caso contrario, habrá filas que no aparezcan, por lo que a la expresión anterior, después del signo igual, ponemos la función ORDENAR, donde como argumento matriz es la expresión anterior.
=ORDENAR(LET(a;ORDENAR(UNICOS(Ventas8[Provincia]));APILARH(a;CONTAR.SI(Ventas8[Provincia];a)))
Punto y coma, como argumento ordenar índice, ponemos 2, quiere decir que vamos a ordenar la columna 2.
Punto y coma, como argumento criterio ordenación, ponemos -1, que quiere decir orden descendente.
Cerramos paréntesis.
=ORDENAR(LET(a;ORDENAR(UNICOS(Ventas8[Provincia]));APILARH(a;CONTAR.SI(Ventas8[Provincia];a)));2;-1)
Aceptamos, y, tenemos las provincias ordenadas de mayor a menor.
Seguimos.
Voy a preguntar con el condicional SI que, si la columna provincia es igual a la primera provincia, que devuelva la fila de la columna provincia, en caso contrario, que devuelva un texto en blanco.
=SI(Ventas[Provincia]=I2;FILA(Ventas[Provincia]);"")
Tenemos las filas para la primera provincia, y, blanco donde no hay coincidencia.
Para quitar los blancos, la expresión anterior, la vamos a usar dos veces, por lo que voy a usar la función LET, creo una variable, y, almaceno dicha expresión.
=LET(a;SI(Ventas[Provincia]=I2;FILA(Ventas[Provincia]);"")
Punto y coma, como argumento calculo, usamos la función FILTRAR, donde como argumento array es la variable A, y, como argumento include, es la variable A siempre que sea distinto a blanco.
=LET(a;SI(Ventas[Provincia]=I2;FILA(Ventas[Provincia]);"");FILTRAR(a;a<>""))
Aceptamos y tenemos el numero de fila donde aparece la primera provincia.
Usamos la función APILARV, donde como argumento matriz1, es la expresión anterior, y como argumento matriz2, ponemos un texto en blanco.
=APILARV(LET(a;SI(Ventas[Provincia]=I2;FILA(Ventas[Provincia]);"");FILTRAR(a;a<>””));"")
Podemos ver que después de la ultima fila, hay una celda en blanco.
Transponemos.
=TRANSPONER(APILARV(LET(a;SI(Ventas[Provincia]=I2;FILA(Ventas[Provincia]);"");FILTRAR(a;a<>""));""))
Arrastramos hacia abajo, y, tenemos las filas para cada provincia.
El informe lo vamos a crear en una hoja nueva, por lo que añadimos una hoja nueva.
Debemos de crear una columna, donde aparezcan las filas empezando por la primera provincia, a continuación, la segunda provincia, tercera, y, así hasta la última, para ello, vamos a usar la función ENCOL (TOROW), donde como argumento matriz, ponemos la celda K2 seguido del operador de rango derramado de la hoja ejemplo, dos puntos, ponemos la celda K21 seguido del operador de rango derramado de la hoja ejemplo.
Cerramos paréntesis.
=ENCOL(Ejemplo!K2#:Ejemplo!K21#)
Si vamos bajando veremos zonas donde aparecen ceros, estos ceros debemos de quitarlo, encaso contrario, no obtendremos el resultado deseado, para ello, voy a usar de nuevo la función LET, creo una variable y almaceno la expresión anterior.
=LET(a;ENCOL(Ejemplo!K2#:Ejemplo!K21#)
Punto y coma, como argumento calculo usamos la función FILTRAR, donde como argumento array es la variable A y como argumento include es la variable A siempre que sea distinto a cero.
=LET(a;ENCOL(Ejemplo!K2#:Ejemplo!K21#);FILTRAR(a;a<>0))
Ya no tenemos los ceros.
Para recuperar dichos valores, vamos a usar la función INDICE de forma matricial, donde como argumento matriz es la tabla ventas.
=INDICE(Ventas
Punto y coma, como argumento número de filas, es la expresión anterior.
=INDICE(Ventas;LET(a;ENCOL(Ejemplo!K2#:Ejemplo!K21#);FILTRAR(a;a<>0))
Punto y coma, como argumento numero de columnas, debemos de especificar tantas columnas como encabezados haya, por lo que vamos a usar la función SECUENCIA, omitimos el argumento filas, y, como argumento columnas, con la función CONTARA contamos los encabezados.
Cerramos paréntesis.
=INDICE(Ventas;LET(a;ENCOL(Ejemplo!K2#:Ejemplo!K21#);FILTRAR(a;a<>0));SECUENCIA(;CONTARA(Ventas[#Encabezados])))
Aceptamos y ya tenemos nuestro modelo.
Pero vemos que no aparece la primera provincia, sino la siguiente.
Esto es debido a que como no se cuenta la primera fila, de ahí, que nos de una provincia de más, solo tenemos que restar 1 a la función ENCOL.
=INDICE(Ventas;LET(a;ENCOL(Ejemplo!K2#:Ejemplo!K21#);FILTRAR(a;a<>0))-1;SECUENCIA(;CONTARA(Ventas[#Encabezados])))
Ahora si lo tenemos.
Si vamos bajando vemos que entre provincia y provincia tenemos un error, pues vamos a usar la función SI.ERROR, y, ponemos que en caso de error nos devuelva un texto en blanco.
=SI.ERROR(INDICE(Ventas;LET(a;ENCOL(Ejemplo!K2#:Ejemplo!K21#);FILTRAR(a;a<>0))-1;SECUENCIA(;CONTARA(Ventas[#Encabezados])));"")
Si vamos bajando veremos como entre provincia y provincia hay una línea en blanco.
Usamos la función APILARV para traernos los encabezados.
=APILARV(Ventas[#Encabezados];SI.ERROR(INDICE(Ventas;LET(a;ENCOL(Ejemplo!K2#:Ejemplo!K21#);FILTRAR(a;a<>0))-1;SECUENCIA(;CONTARA(Ventas[#Encabezados])));""))
Pues ya tenemos nuestro ejercicio resuelto, faltaría dar un formato pero lo dejo a vuestra elección.
Miguel Angel Franco Garcia
Comments