Para el siguiente ejemplo, seguimos trabajando con el modelo que suelo usar habitualmente.
El modelo está en formato de tabla, y, se llama ventas.
El ejercicio va a consistir en crear unas primeras dos listas desplegables, para poder seleccionar dos provincias, dos centros, o, dos productos.
Después, crearemos otras dos listas desplegables para poder seleccionar dos años.
A continuación, crearemos un reporte filtrado por los datos anteriores.
Lo siguiente será calcular el total para, en este caso, cada provincia, y, el porcentaje de mayores ventas contra menor ventas.
Calcularemos el total por años, y, el porcentaje de mayores ventas contra menor ventas.
Después, calcularemos el total para cada provincia y cada año.
Daremos un formato dinámico.
Ocultaremos la tabla.
Y este será el ejercicio.
Pues, empecemos…
Vamos a preparar los datos con los que vamos a trabajar en una hoja nueva.
Esta primera lista desplegable que vamos a crear es para si seleccionamos centro, la lista desplegable, se rellenara con los centros únicos, si seleccionamos una provincia, se rellenara con las provincias únicas, y, si seleccionamos un producto se rellenara con los productos únicos, son con las tres columnas que vamos a trabajar.
En la celda I1 de la hoja datos, debemos de seleccionar el encabezado, provincia, centro, o, producto.
En la celda I1 de esta nueva hoja, voy a traerme los encabezados, y, mediante una constante de matriz, en el argumento include, ponemos 0 en la columna que no queremos ver, y,1 en la columna que queremos ver.
=FILTRAR(Ventas[#Encabezados];{0\1\1\1\0\0\0})
Ya tenemos los encabezados con los que trabajar.
Creamos una lista desplegable en la hoja datos en la celda I1 con dichos encabezados.
Seleccionamos un encabezado.
En la celda A2 de hoja1, voy a usar la función INDIRECTO para crear la estructura de la columna de la tabla a traer, donde entre comillas dobles, ponemos el nombre de la tabla, y una apertura de corchete, concatenamos con A1 de hoja1, y, concatenemos con un cierre de corchete, entre comillas dobles.
Cerramos paréntesis.
=INDIRECTO("Ventas[" & DATOS!I1& "]")
Como en la lista desplegable he seleccionado centro, tengo los centros, pero duplicados.
Usamos UNICOS, para quedarnos con los valores únicos.
=UNICOS(INDIRECTO("Ventas[" & DATOS!I1& "]"))
Vamos a la hoja datos, en la celda I3, creamos otra lista desplegable, en este caso, con los valores del encabezado seleccionado.
Debemos de seleccionar dos centros, queremos el informe por uno o dos centros, para ello, en la celda B2 de hoja1, voy a filtrar la matriz desbordada anterior, siempre que dicha matriz sea diferente al valor seleccionado en la celda I2 de la hoja datos.
=FILTRAR(A2#;A2#<>DATOS!I3)
Podemos ver que tenemos los mismos valores, excepto, el valor seleccionado, he seleccionado C.C.Nervion.
En la hoja datos, en la celda J3, creamos esta lista desplegable.
También, queremos elegir un año, o, dos años para comparar, en la celda D2 de hoja1, nos traemos los años únicos de la columna fecha de la tabla ventas.
=UNICOS(AÑO(Ventas[Fecha]))
Igual que antes, debemos de crear otra lista, pero sin el año seleccionado, para ello, usamos la siguiente expresión:
Ahora, debemos de crear una segunda lista sin el año seleccionado, para ello, usamos la siguiente expresión:
=FILTRAR(D2#;D2#<>DATOS!L2)
Ya tenemos la segunda lista.
Pues, al lado de la lista anterior, creamos esta lista desplegable.
Vamos a crear dos matrices, una para encontrar las coincidencias con los años seleccionados, y, otra para encontrar coincidencia con los encabezados seleccionados, después, crearemos un nombre de rango para cada uno, así será más fácil su uso, y, la sintaxis no será demasiado larga.
Empecemos por encontrar las coincidencias con los años, he seleccionado el año 2017 y 2018.
Debemos de buscar en la columna fecha de la tabla ventas, aquellos años que sean mayor o igual a 2017, y, menor o igual a 2018, debemos de usar el operador lógico Y, porque se deben de cumplir todas las condiciones, el problema es que el operador lógico Y, no lo podemos usar de forma matricial, pero vamos a usar la función BYROW, que nos va a permitir usar dicho operador de forma matricial.
Ponemos la función, abrimos un paréntesis, ponemos AÑO aplicado a la columna fecha.
=BYROW(AÑO(Ventas[Fecha])
Punto y coma, ponemos LAMBDA, declaramos una variable, que va a almacenar la función AÑO.
=BYROW(AÑO(Ventas[Fecha]);LAMBDA(b
Punto y coma, como argumento cálculo de LAMBDA, ponemos el operador lógico Y, abrimos un paréntesis, ponemos la variable B y comparamos que sea mayor o igual que el primer año, punto y coma, ponemos de nuevo la variable B, y, comparamos que sea menor o igual al segundo año.
Cerramos paréntesis.
=BYROW(AÑO(Ventas[Fecha]);LAMBDA(b;Y(b>=L3;b<=M3)))
Aceptamos, y, tenemos una matriz desbordada donde vemos VERDADERO donde se cumplen las dos condiciones, y, FALSO donde no se cumplen.
Ahora, vamos a hacer lo mismo, pero para los valores de los encabezados seleccionados.
Aquí vamos a hacer uso de la función INDIRECTO, porque debemos de cargar el encabezado seleccionado, y, también debemos de usar de nuevo BYROW.
En la celda contigua a la anterior, ponemos BYROW y abrimos un paréntesis.
Como argumento array, ponemos INDIRECTO, entre comillas dobles ponemos “Ventas[“, concatenamos con el valor de la celda I1, y, concatenamos, entre comillas dobles, con un cierre de corchete.
Cerramos paréntesis.
=BYROW(INDIRECTO("Ventas[" & DATOS!I1 & "]")
Punto y coma, ponemos LAMBDA, y, declaramos una variable, que almacenara INDIRECTO.
=BYROW(INDIRECTO("Ventas[" & DATOS!I1 & "]");LAMBDA(a
Punto y coma, como argumento cálculo de LAMBDA, ponemos el operador lógico O, en este caso, nos vale con que se cumpla una condición, abrimos paréntesis, ponemos la variable A y la igualamos a I3, punto y coma, ponemos de nuevo la variable A, y, igualamos a J3.
Cerramos paréntesis.
=BYROW(INDIRECTO("Ventas[" & DATOS!I1 & "]");LAMBDA(a;O(a=I3;a=J3)))
Aceptamos, y, tenemos una matriz desbordada con VERDADERO donde hay coincidencias, y, FALSO donde no la hay.
Tenemos una matriz al lado de la otra, podemos observar que donde hay VERDADERO al lado de otro VERDADERO, quiere decir que hay coincidencia tanto en los encabezados buscados como en los años.
Pues, donde hay dos VERDADEROS son los datos para filtrar.
Como dije anteriormente, vamos a crear dos nombres de rangos, uno para la primera matriz, y, otro para la segunda matriz, pero antes debemos de fijar las siguientes celdas.
=BYROW(INDIRECTO("Ventas[" & DATOS!$I$1 & "]");LAMBDA(a;O(a=$I$3;a=$J$3)))
Seleccionamos la expresión, pulsamos CTRL más C para copiar, y, Escape.
Vamos a la pestaña de fórmulas, dentro de nombres definidos, hacemos clic en asignar nombre.
Como nombre le voy a poner años, y, en la ventana se refiere a, borramos lo que hay, y, pulsamos CTRL más V para pegar.
Aceptamos.
Vamos a la segunda expresión, y, fijamos las siguientes celdas:
=BYROW(INDIRECTO("Ventas[" & DATOS!$I$1 & "]");LAMBDA(a;O(a=$I$3;a=$J$3)))
Seleccionamos la segunda expresión, la seleccionamos, pulsamos CTRL más C copiar, y, Escape.
Volvemos a asignar nombre, como nombre le voy a poner encabezados, y, en la ventana se refiere a, borramos lo que hay, y, pulsamos CTRL más V para pegar.
Aceptamos, ya no necesitamos las fórmulas dentro de la hoja de Excel, por lo que podemos borrarla.
Lo siguiente es crear el informe.
Vamos a usar la función MAP, donde como argumento array1, es el nombre de rango años, como argumento array2, es el nombre de rango de encabezados, y, como argumento array3, es la función FILA de ventas.
=MAP(años;encabezados;FILA(Ventas)
Punto y coma, ponemos LAMBDA, abrimos un paréntesis, declaramos tres variables, cada variable almacenará años, encabezados y FILA(ventas).
=MAP(años;encabezados;FILA(Ventas);LAMBDA(a;b;c
Punto y coma, como argumento cálculo de LAMBDA, ponemos el condicional SI, abrimos un paréntesis, ponemos el operador lógico Y, porque vamos a hacer dos preguntas, y, ambas preguntas se deben de cumplir, preguntamos si A es igual a VERDADERO, y, si B es igual a VERDADERO, en ese caso, que nos devuelva la variable C.
Cerramos paréntesis.
=MAP(años;encabezados;FILA(Ventas);LAMBDA(a;b;c;SI(Y(a=VERDADERO;b=VERDADERO);c;"")))
Aceptamos, y, tenemos una matriz desbordada con el número de fila donde hay coincidencia, y, blanco donde no la hay.
Para el siguiente paso, vamos a usar LET, porque la expresión anterior, la vamos a usar dos veces, declaramos una variable y almacenamos la expresión.
Queremos quitar esos espacios en blanco.
LET(x;MAP(años;encabezados;FILA(Ventas);LAMBDA(a;b;c;SI(Y(a=VERDADERO;b=VERDADERO);c;"")))
Punto y coma, filtramos la variable X, siempre que la variable X sea diferente a blanco.
LET(x;MAP(años;encabezados;FILA(Ventas);LAMBDA(a;b;c;SI(Y(a=VERDADERO;b=VERDADERO);c;"")));FILTRAR(x;x<>""))
Cerramos paréntesis y aceptamos.
Vemos que solo tenemos los números de filas a recuperar.
Usamos INDICE, para recuperar los registros, donde como argumento matriz, seleccionamos las columnas A:G.
INDICE(A:G
Punto y coma, como argumento número de fila es LET.
INDICE(A:G;LET(x;MAP(años;encabezados;FILA(Ventas);LAMBDA(a;b;c;SI(Y(a=VERDADERO;b=VERDADERO);c;"")));FILTRAR(x;x<>""))
Punto y coma, como argumento número de columna, nos debe de devolver cada columna de la tabla ventas, sabemos que son 7, ponemos poner directamente 7 como argumento columnas de SECUENCIA, o, usar la siguiente expresión para calcular los encabezados, de esa forma, siempre serán dinámicos, y, tendremos el resultado correcto haya más o menos encabezados.
=SECUENCIA(;CONTARA(Ventas[#Encabezados]))
En mi caso, voy a poner SECUENCIA(7).
Cerramos paréntesis.
=INDICE(A:G;LET(x;MAP(años;encabezados;FILA(Ventas);LAMBDA(a;b;c;SI(Y(a=VERDADERO;b=VERDADERO);c;"")));FILTRAR(x;x<>""));SECUENCIA(;7))
Aceptamos, y, ya tenemos nuestro informe, en este caso, para las provincias de Sevilla y, Huelva, para los años 2017 y 2018.
Pero, que ocurre si queremos elegir la misma provincia, pues en la segunda lista desplegable no podemos hacerlo, pues, debemos de deshacernos de la segunda lista desplegable, y, ambas listas desplegables se cargan de:
=UNICOS(INDIRECTO("Ventas[" & DATOS!I1& "]"))
Igual para los años.
Vamos a verificar, vamos a seleccionar en ambas listas la misma provincia, y, mismo año, y, vemos que tenemos el informe para la misma provincia y mismo año.
Hasta aquí todo bien, pero necesitamos resumir estos datos, es decir, queremos que aparezca cada provincia única, lo vendido en cada centro, lo vendido de cada producto, y, lo vendido por años seleccionados.
Vamos a usar APILARV (VSTOCK) para traernos los datos de los encabezados.
=APILARV(I3;J3)
Ahora, debemos de calcular el total para estos datos dentro de la matriz desbordada obtenida, debemos de rescatar los valores de los encabezados seleccionados, por ejemplo, he seleccionado Sevilla y Cádiz.
Debo de rescatar de la matriz desbordada (I7#), los valores que corresponden a la provincia, si selecciono como encabezado centro, debo de rescatar los valores que pertenecen a los centros, la provincia siempre se va a encontrar en la columna 2, el centro en la columna 3, y, el producto en la columna 4, pero para poder calcular los totales por uno de estos encabezados, debemos de traernos dichos valores.
Como vamos a comparar columnas, vamos a usar la función BYCOL, donde como argumento array es la matriz desbordada (I7#).
=BYCOL($I$7#
Punto y coma, ponemos LAMBDA, declaramos una variable, que almacenara I7#.
=BYCOL($I$7#;LAMBDA(a
Punto y coma, como argumento cálculo de LAMBDA, ponemos el condicional SI, ponemos le operador lógico O, comparamos la variable A con el valor de Q7, fijamos, y con el valor de Q8, fijamos, con que se cumpla una de las condiciones nos vale.
Cerramos paréntesis.
=BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0)))
Aceptamos.
Obtenemos una matriz desbordada en horizontal con 0 donde no hay coincidencia y 1 donde la hay.
Esta expresión, es el argumento include de la función FILTRAR que vamos a usar para filtrar la matriz desbordada (I7#).
=FILTRAR(I7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0))))
Aceptamos, y, vemos que se ha recuperado los datos, en este caso, de las provincias.
Si cambiamos el encabezado, veremos como obtenemos dicha columna.
Vamos a agregar a estos datos una nueva columna con los totales, para ello, vamos a usar la función APILARH (HSTOCK), donde como argumento matriz1, es la expresión anterior.
=APILARH(FILTRAR($I$7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0))))
Punto y coma, como argumento matriz2, debemos de usar INDICE, donde como argumento array es la matriz desbordada (I7#), omitimos el argumento número de fila, y, como argumento número de columna, ponemos 7.
Cerramos paréntesis.
=APILARH(FILTRAR($I$7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0))));INDICE($I$7#;;7))
Aceptamos.
Tenemos una matriz desbordada de dos columnas, la primera contiene el encabezado seleccionado, y, la segunda contiene la columna de total.
Usamos LET porque usaremos la expresión anterior dos veces, creamos una variable y almacenamos dicha expresión.
=LET(a;APILARH(FILTRAR($I$7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0))));INDICE($I$7#;;7))
Punto y coma, usamos el condicional SI, como argumento prueba lógica de SI, usamos INIDCE para traernos de la variable A la primera columna y la igualamos al valor de la celda Q7.
=LET(a;APILARH(FILTRAR($I$7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0))));INDICE($I$7#;;7));SI(INDICE(a;;1)=Q7
Como argumento valor si verdadero con la función INDICE que nos devuelva de la variable A la segunda columna, y, como argumento valor si falso, debe de poner un texto en blanco.
Cerramos paréntesis.
=LET(a;APILARH(FILTRAR($I$7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0))));INDICE($I$7#;;7));SI(INDICE(a;;1)=Q7;INDICE(a;;2);""))
Aceptamos, y, tenemos una matriz desbordada con los totales, en este caso, para Sevilla y blanco donde no hay coincidencia.
Sumamos los resultados.
=SUMA(LET(a;APILARH(FILTRAR($I$7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0))));INDICE($I$7#;;7));SI(INDICE(a;;1)=Q7;INDICE(a;;2);"")))
Ya tenemos el total para la provincia de Sevilla, como ya tenemos fijadas las celdas que necesitamos, arrastramos una vez.
Tenemos el total para ambas provincias.
Si cambiamos de encabezado, veremos que tenemos el total para dichos valores seleccionados.
Lo siguiente es calcular la diferencia porcentual entre el mayor valor y el menor valor, para ello, debemos de extraer tanto el valor máximo como el valor mínimo.
La expresión quedaría como sigue:
=(MAX(R8:R9)-MIN(R8:R9))/MAX(R8:R9)
Lo ponemos en formato de porcentaje con dos decimales.
En este caso, Sevilla tiene un 62,63% de más ventas que Cádiz.
Pero, queremos que aparezca el nombre de la provincia, la palabra “ha tenido un” seguido del porcentaje, y, seguido de “más de ventas”.
Para ello, debemos de concatenar, pero, primero debemos de saber a qué provincia corresponde el valor máximo, para ello, usare BUSCARX donde como argumento valor buscado, es el valor máximo de los valores.
=BUSCARX(MAX(R7:R8)
Punto y coma, como argumento matriz buscada es las cantidades, y, como argumento matriz devuelta son las provincias.
Cerramos paréntesis.
=BUSCARX(MAX(R7:R8);R7:R8;Q7#)
Aceptamos, y, ya tenemos las provincias, en este caso, es Sevilla.
Concatenamos con ha tenido un.
=BUSCARX(MAX(R7:R8);R7:R8;Q7#) & ", ha tenido un "
Concatenamos con el porcentaje obtenido.
=BUSCARX(MAX(R7:R8);R7:R8;Q7#) & ", ha tenido un " & (MAX(R7:R8)-MIN(R7:R8))/MAX(R7:R8)
Y concatenamos con más de ventas.
=BUSCARX(MAX(R7:R8);R7:R8;Q7#) & ", ha tenido un " & (MAX(R7:R8)-MIN(R7:R8))/MAX(R7:R8)& " más de ventas."
Ya tenemos el resultado esperado, pero vemos que el porcentaje ha perdido su formato.
Para solventar el problema, usamos TEXTO donde como argumento valor es el cálculo del porcentaje, y, como argumento formato, entre comillas dobles, ponemos “0,00%”.
=BUSCARX(MAX(R7:R8);R7:R8;Q7#) & ", ha tenido un " & TEXTO((MAX(R7:R8)-MIN(R7:R8))/MAX(R7:R8);"0,00%") & " más de ventas."
Ya lo tenemos.
Vamos a cambiar por un producto.
En este caso, frigorífico ha tenido un 50,27 por ciento más de ventas que de TV 32 pulgadas.
Volvemos a seleccionar provincias, el siguiente paso es el cálculo del total para los años donde ha vendido la primera provincia.
Igual con antes con APILARV (VSTOCK) nos traemos los años seleccionados.
=APILARV(L3;M3)
Los pasos son muy parecidos a los anteriores, pero las fechas siempre van a estar en la columna 1, por lo que no debemos de usar BYROW, simplemente, con INDICE rescatamos la columna 1.
=INDICE(I7#;;1)
Nos traemos el año.
=AÑO(INDICE(I7#;;1))
Con el condicional SI preguntamos que si el año devuelvo por INDICE es igual al primer año seleccionado, que nos devuelva la columna de total.
=SI(AÑO(INDICE(I7#;;1))=Q11;INDICE(I7#;;7);"")
Aceptamos, y, tenemos una matriz desbordada con los totales donde hay coincidencia y blanco donde no la hay.
Sumamos los resultados.
=SUMA(SI(AÑO(INDICE(I7#;;1))=Q11;INDICE(I7#;;7);""))
Tenemos el total para el primer año.
Fijamos las siguientes celdas.
=SUMA(SI(AÑO(INDICE($I$7#;;1))=Q11;INDICE($I$7#;;7);""))
Arrastramos una vez y tenemos el total para ambos años.
Volvemos a seleccionar las provincias.
Lo siguiente es calcular el total para cada centro y producto de la primera provincia, para ello, volvemos a usar BYCOL, donde como argumento array es la matriz desbordada (I7#).
=BYCOL($I$7#
Punto y coma, ponemos LAMBDA, declaramos una variable que almacena la matriz desbordada.
=BYCOL($I$7#;LAMBDA(a
Punto y coma, como argumento cálculo de LAMBDA, usamos el condicional SI, como argumento prueba lógica, usamos el operador lógico O, donde preguntamos que, si la variable A es igual a Q7, o, igual a Q8, que ponga 1, en caso contrario, que ponga 0.
=BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0)))
Filtramos igual que antes.
=FILTRAR(I7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0))))
En este caso, obtenemos las provincias, ahora, a esta columna debemos de añadir el resto de las columnas de la matriz desbordada (I7#), excepto, la columna de provincias.
Volvemos a usar la función APILARH (HSTOCK), donde como argumento matriz1 y matriz2 es la expresión anterior, lo único que en el argumento matriz2, cambiamos 1 por 0, y, 0 por 1.
=APILARH(FILTRAR(I7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0))));FILTRAR(I7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);0;1)))))
Aceptamos, y, tenemos una matriz desbordada donde la primera columna es el encabezado seleccionado, y, el resto de las columnas son todas las columnas excepto el encabezado seleccionado.
Lo siguiente es filtrar este modelo por la primera provincia, para ello, usamos LET, creamos una variable, y, almacenamos la expresión anterior.
=LET(a;APILARH(FILTRAR(I7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0))));FILTRAR(I7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);0;1)))))
Punto y coma, como argumento include con INDICE nos traemos la primera columna y la igualamos al valor de Q7.
=LET(a;APILARH(FILTRAR(I7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0))));FILTRAR(I7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);0;1)))));FILTRAR(a;INDICE(a;;1)=Q7))
Aceptamos, y, tenemos solo las ventas para la primera provincia.
Solo necesitamos para calcular el total la columna 2 para filtrar por año, y, las columnas 3, 4, y, 7, para ello, usamos INDICE donde como argumento array es la expresión anterior.
=INDICE(LET(a;APILARH(FILTRAR(I7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0))));FILTRAR(I7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);0;1)))));FILTRAR(a;INDICE(a;;1)=Q7))
Punto y coma, como argumento número de fila, debemos de calcular el número de filas de I7#, para ello, debemos de contar todas las filas del modelo, y, dividirla entre 7 columnas, como sigue:
=INDICE(LET(a;APILARH(FILTRAR(I7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0))));FILTRAR(I7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);0;1)))));FILTRAR(a;INDICE(a;;1)=Q7));SECUENCIA(CONTARA(I7#)/7)
Punto y coma, como argumento número de columna a través de una constante de matriz, indicamos que nos devuelva las columnas 2, 3, 4, y, 7.
Cerramos paréntesis.
=INDICE(LET(a;APILARH(FILTRAR(I7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0))));FILTRAR(I7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);0;1)))));FILTRAR(a;INDICE(a;;1)=Q7));SECUENCIA(CONTARA(I7#)/7);{2\3\4\7})
Aceptamos, y, vemos que tenemos las columnas que necesitamos para calcular el total para la primera provincia.
Me he dado cuenta, que al usar esta expresión anterior, donde no hay coincidencia devuelve un error, por lo que vamos a usar la función SI.ERROR en la siguiente expresión.
=SI.ERROR(ORDENAR(INDICE(LET(a;APILARH(FILTRAR($I$7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);1;0))));FILTRAR($I$7#;BYCOL($I$7#;LAMBDA(a;SI(O(a=$Q$7;a=$Q$8);0;1)))));FILTRAR(a;INDICE(a;;1)=$Q$7));SECUENCIA(CONTARA($I$7#)/7);{2\3\4\7}));"")
Como esta expresión la vamos a necesitar y se hace larga, como hemos hecho antes, vamos a crear un nombre de rango, que llámate Total_primer_valor, porque corresponde con el primer valor seleccionado, y almacenamos la expresión.
Debemos de crear otro nombre de rango para el segundo valor, lo único que cambia de la expresión anterior, es que en vez de Q7 es Q8.
Hasta este momento, tenemos el nombre de rango Total_primer_valor que contiene las ventas para la primera provincia, y, tenemos un segundo nombre de rango, Total_segundo_valor, que contiene las ventas para la segunda provincia.
Tenemos dos años seleccionados.
Ahora, queremos calcular el total para la primera provincia, dentro del primer año para cada centro comercial único, después para el segundo año, a continuación, debemos de hacerlo para la segunda provincia.
Vamos a crear el cuadro, para poder ser rellenado.
En la celda Q15, concatenamos el primer año con la barra inclinada y el valor de Q7, que es la primera provincia para este ejemplo.
=Q11 & “/” & Q7
En la celda Q16, nos traemos ordenados, los valores únicos de la tercera columna del nombre de rango Total_primer_valor.
=LET(a;ORDENAR(UNICOS(INDICE(Total_primer_valor;;3)));FILTRAR(a;a<>""))
En la celda R15, nos traemos ordenados los valores únicos de la segunda columna del nombre de rango Total_primer_valor, pero cono nos va a devolver una celda vacía, usamos LET y almacenamos en una variable la expresión.
=TRANSPONER(ORDENAR(LET(a;UNICOS(INDICE(Total_primer_valor;;2))
Punto y coma, filtramos la variable A siempre que dicha variable sea diferente a blanco.
Cerramos paréntesis.
=TRANSPONER(ORDENAR(LET(a;UNICOS(INDICE(Total_primer_valor;;2));FILTRAR(a;a<>""))))
En este caso, tengo seleccionado dos centros y dos años.
Por lo que en vertical tengo los productos, y, en horizontal las provincias, pues, calculemos el total para cada producto y provincia.
En la celda R16, abrimos un paréntesis, extraemos el año de la primera columna de Total_primer_valor, y, la igualamos al valor de la celda Q11, la cual fijamos.
=(AÑO(INDICE(Total_primer_valor;;1))=$Q$11)
Obtenemos una matriz desbordada con VERDADERO donde hay coincidencia, y, FALSO donde no la hay.
Ponemos el asterisco, abrimos otro paréntesis, preguntamos si la columna 2 de Total_primer_valor, es igual al valor de la celda R15, y, fijamos la fila para la hora de copiar, cerramos paréntesis.
(AÑO(INDICE(Total_primer_valor;;1))=$Q$11)*(INDICE(Total_primer_valor;;2)=R$15)
Ahora, obtenemos 1 donde hay coincidencia, y, 0 donde no la hay.
Ponemos otro asterisco, abrimos otro paréntesis, y, preguntamos si la tercera columna de Total_primer_valor es igual al valor de Q16, y, fijamos la columna.
(AÑO(INDICE(Total_primer_valor;;1))=$Q$11)*(INDICE(Total_primer_valor;;2)=R$15)*(INDICE(Total_primer_valor;;3)=$Q16)
Estas son las tres condiciones que se deben de cumplir, ponemos otro asterisco, que son los valores que debe de devolver, abrimos un paréntesis, indicamos que nos devuelva la columna 4 de Total_primer_valor.
(AÑO(INDICE(Total_primer_valor;;1))=$Q$11)*(INDICE(Total_primer_valor;;2)=R$15)*(INDICE(Total_primer_valor;;3)=$Q16)*(INDICE(Total_primer_valor;;4))
Aceptamos, y, tenemos los totales para el primer producto, primera provincia, y, primer año.
Pero, vemos que donde no hay ventas aparece cero, pues vamos a cambiar cero por nada, para ello, con LET creamos una variable y almacenamos la expresión anterior, después con el condicional SI, preguntamos que, si la variable A es igual a cero, que ponga blanco, en caso contrario, que devuelva la variable A.
=LET(a;SUMA(SI.ERROR((AÑO(INDICE(Total_primer_valor;;1))=$Q$11)*(INDICE(Total_primer_valor;;2)=R$15)*(INDICE(Total_primer_valor;;3)=$Q16)*(INDICE(Total_primer_valor;;4));""));SI(a=0;"";a))
Nos colocamos en la celda, pulsamos CTRL mas C para copiar, selecciono el rango donde pegar, y, pulsamos CTRL más V para pegar.
Ya tenemos los totales.
Como si seleccionamos provincias, los datos van a cambiar, vamos a dar formato de forma dinámica, para ello, seleccionamos el año y todas las provincias.
Desplegamos formato condicional, y, seleccionamos nueva regla.
En la ventana que se abre, seleccionamos la última opción, y, preguntamos que si el valor de Q15, donde ponemos el símbolo de dólar delante de la fila, para que se aplique el formato a todas las celdas seleccionadas, es diferente a blanco.
Hacemos clic en formato, y, le damos un relleno claro.
Aceptamos.
Ya lo tenemos.
Ahora, a los valores vamos a darle un formato de líneas solo a las celdas con valores, para ello, seleccionamos todos los valores, desplegamos formato condicional, seleccionamos nueva regla, elegimos la última opción, y, ponemos:
Hacemos clic en formato, clic en la pestaña borde, y, clic en contorno.
Aceptamos.
Creamos el mismo cuadro para el año 2018.
En la función, solo debemos de cambiar Q16 por Q23.
=SUMA(SI.ERROR((AÑO(INDICE(Total_primer_valor;;1))=$Q$12)*(INDICE(Total_primer_valor;;2)=R$22)*(INDICE(Total_primer_valor;;3)=$Q23)*(INDICE(Total_primer_valor;;4));""))
Seguimos los pasos anteriores para copiar la función y dar formato.
Copiamos ambos cuadros hacia abajo.
A continuación, pongo las formulas a usar, que son las mismas solo hay que cambiar algunas celdas.
Celda Q29 - =Q11 & "/" & Q8
Celda R28 - =TRANSPONER(ORDENAR(LET(a;UNICOS(INDICE(Total_primer_valor;;2));FILTRAR(a;a<>""))))
Celda Q30 - =LET(a;ORDENAR(UNICOS(INDICE(Total_primer_valor;;3)));FILTRAR(a;a<>""))
Celda R30 - =LET(a;SUMA(SI.ERROR((AÑO(INDICE(Total_segundo_valor;;1))=$Q$11)*(INDICE(Total_segundo_valor;;2)=R$29)*(INDICE(Total_segundo_valor;;3)=$Q30)*(INDICE(Total_segundo_valor;;4));""));SI(a=0;"";a))
Celda Q36 - =Q12 & "/" & Q8
Celda R36 - =TRANSPONER(ORDENAR(LET(a;UNICOS(INDICE(Total_primer_valor;;2));FILTRAR(a;a<>""))))
Celda Q37 - =LET(a;ORDENAR(UNICOS(INDICE(Total_primer_valor;;3)));FILTRAR(a;a<>""))
Celda R37 - =LET(a;SUMA(SI.ERROR((AÑO(INDICE(Total_segundo_valor;;1))=$Q$12)*(INDICE(Total_segundo_valor;;2)=R$36)*(INDICE(Total_segundo_valor;;3)=$Q37)*(INDICE(Total_segundo_valor;;4));""));SI(a=0;"";a))
Pues ya tenemos nuestro ejercicio resuelto, tenemos las ventas para la primera provincia y ambos años, y, segunda provincia y ambos años.
Miguel Angel Franco Garcia
Commentaires