Filtrar por 3 hojas o mas

Jaime Franco Jimenez
15 dic 2023
7 Min. de lectura

Tenemos tres hojas, cada hoja con una provincia, el modelo tiene la misma estructura, tenemos las provincias de Cádiz, Huelva, y, Sevilla.

En la hoja resultado, vamos a poner las provincias que tenemos junto con una casilla de verificación, después, ponemos los años disponibles, junto con una casilla de verificación.

Debemos de crear un reporte con la o las provincias seleccionadas, y, el o los años seleccionados.

Después, agrupamos por provincias, por años, y, meses.

Vamos a traernos la provincia que tenemos en cada hoja, pero, que sea de forma dinamica, es decir, que si añadimos provincias nuevas se añadan de forma automática.

En la celda A3, ponemos la función HOJAS, que nos devuelve el número total de hojas.

=HOJAS()

Nos devuelve 4, pero, debemos de restar 1, porque la hoja de resultado no cuenta.

=HOJAS()-1

Nos devuelve 3.

Después del signo igual, entre comillas dobles, ponemos “Hoja”, y, concatenamos con la función HOJAS.

="Hoja"&HOJAS()-1

Al final de la expresión, concatenamos con el signo de exclamación, y, B2, que es la celda donde se encuentra la primera provincia de cada hoja, porque todos los modelos tienen la misma estructura.

="Hoja"&HOJAS()-1&"!B2"

Tenemos:

Antes de la función HOJAS, ponemos SECUENCIA, como argumento filas, es la función HOJAS, omitimos el argumento columnas, como argumento inicio, ponemos 3, que es el numero de hoja donde empieza la primera provincia.

="Hoja"&SECUENCIA(HOJAS()-1;;3)&"!B2"

Obtenemos cada hoja a partir de la hoja3.

Lo siguiente es rescatar la celda B2 de cada hoja, cosa que conseguimos con la función INDIRECTO, pero esta función por si sola, no funciona de forma matricial.

Vamos a hacer uso de la función REDUCE, para poder trabajar con cada celda de forma independiente.

Ponemos la función REDUCE, como valor inicial, ponemos 0, como argumento array, es la expresión que acabamos de crear.

=REDUCE(0;"Hoja"&SECUENCIA(HOJAS()-1;;3)&"!b2"

Como argumento función, ponemos LAMBDA, y, declaramos dos variables.

=REDUCE(0;"Hoja"&SECUENCIA(HOJAS()-1;;3)&"!b2";LAMBDA(x;y

Como argumento calculo de LAMBDA, usamos APILARV, como argumento matriz1, ponemos la variable “x”, como argumento matriz2, usamos INDIRECTO, como argumento referencia, ponemos la variable “y”.

=REDUCE(0;"Hoja"&SECUENCIA(HOJAS()-1;;3)&"!b2";LAMBDA(x;y;APILARV(x;INDIRECTO(y))))

Aceptamos, y, tenemos cada provincia, pero, ya es de forma dinamica.

Pero, nos ha añadido el valor inicial, por lo que después del signo igual, ponemos EXCLUIR, como argumento matriz, es la expresión anterior, como argumento filas, ponemos 1.

=EXCLUIR(REDUCE(0;"Hoja"&SECUENCIA(HOJAS()-1;;3)&"!b2";LAMBDA(x;y;APILARV(x;INDIRECTO(y))));1)

Vamos a duplicar la última hoja.

Cambiamos el nombre a Hoja6.

Vamos a la hoja resultado, y, vemos como se ha añadido la provincia, aunque repetida.

Borramos la hoja.

Vamos a volver a la expresión, quitamos EXCLUIR, APILARV, y, dejamos solo la función INDIRECTO.

=REDUCE(0;"Hoja"&SECUENCIA(HOJAS()-1;;3)&"!b2";LAMBDA(x;y;INDIRECTO(y)))

Que es lo que hace la función REDUCE, evita los cálculos intermedios, y, nos devuelve el ultimo valor.

Volvemos a la función APILARV, añadimos el argumento matriz1, y, ponemos la variable “x”, quitamos la función INDIRECTO, y, dejamos solo la variable “y”.

=REDUCE(0;"Hoja"&SECUENCIA(HOJAS()-1;;3)&"!b2";LAMBDA(x;y;APILARV(x;y)))

Nos devuelve cada valor.

En la primera iteración, toma el valor 0, en la segunda iteración, empieza desde el valor 0, y, añade el valor Hoja3!B2, por lo que el acumulado ahora es 0 y Hoja3!B2, en la tercera iteración, valor inicial, vale y Hoja3!B2, y, añade el valor Hoja4!B2, por lo que tenemos 0, Hoja3!B2, y Hoja4!B2, y repite el paso hasta el último valor, por eso, tenemos cada valor, de esta manera podemos trabajar con cada valor de manera independiente.

A veces, podemos comenzar con un valor inicial, pero, la mayoría de las veces no vamos a necesitar un valor inicial.

Como he dicho anteriormente, tenemos los años, y, una casilla de verificación para cada año.

Vamos por el reporte.

En la celda D3, usamos LET, creamos una variable, usamos FILTRAR, como argumento array, seleccionamos el rango A3:A5 (provincias), como argumento include, seleccionamos el rango B3:B5 al no comparar con nada, iguala a VERDADERO.

=LET(a;FILTRAR(A3:A5;B3:B5);a)

He seleccionado dos provincias, vemos que nos devuelve dichas provincias.

Creamos otra variable, usamos REDUCE, la función a usar es la misma que hemos usado para las provincias, solo debemos de cambiar el argumento array, y, poner la variable “a”.

=LET(a;FILTRAR(A3:A5;B3:B5);b;REDUCE("";a;LAMBDA(x;y;APILARV(x;INDIRECTO(y))));b)

Seleccionamos una provincia o más.

Tenemos filtrado el modelo por los datos seleccionados.

Pero, en la primera fila, tenemos un error, pues, después del signo igual, usamos EXCLUIR, como argumento matriz, es la función REDUCE, como argumento filas, ponemos 1.

=LET(a;FILTRAR(A3:A5;B3:B5);b;EXCLUIR(REDUCE("";a;LAMBDA(x;y;APILARV(x;INDIRECTO(y))));1);b)

Vamos por los años.

Creamos otra variable, usamos FILTRAR, como argumento array, seleccionamos los años, como argumento include, seleccionas las casillas de verificación.

=LET(a;FILTRAR(A3:A5;B3:B5);b;EXCLUIR(REDUCE("";a;LAMBDA(x;y;APILARV(x;INDIRECTO(y))));1);c;FILTRAR(A8#;B8:B12);c)

Usamos ENFILA, como argumento matriz, es la función FILTRAR.

=LET(a;FILTRAR(A3:A5;B3:B5);b;EXCLUIR(REDUCE("";a;LAMBDA(x;y;APILARV(x;INDIRECTO(y))));1);c;ENFILA(FILTRAR(A8#;B8:B12));c)

Seleccionamos un año, o, más, e, igual que antes, obtenemos los años seleccionados.

Volvemos a la expresión de la variable “c”.

Antes de ENFILA, usamos REDUCE, como argumento valor inicial, ponemos 0, como argumento array, es la función ENFILA, como argumento función, ponemos LAMBDA, y, declaramos dos variables.

=LET(a;FILTRAR(A3:A5;B3:B5);b;EXCLUIR(REDUCE("";a;LAMBDA(x;y;APILARV(x;INDIRECTO(y))));1);c;REDUCE(0;ENFILA(FILTRAR(A8#;B8:B12));LAMBDA(x;y

Como argumento calculo de LAMBDA, usamos APILARH, como argumento matriz1, ponemos la variable “x”, como argumento matriz2, ponemos la variable “y”, e, igualamos al año de la primera columna de la variable “b”, por lo que usamos TOMAR, como argumento matriz, ponemos la variable “b”, omitimos el argumento filas, como argumento columnas, ponemos 1.

=LET(a;FILTRAR(A3:A5;B3:B5);b;EXCLUIR(REDUCE("";a;LAMBDA(x;y;APILARV(x;INDIRECTO(y))));1);c;REDUCE(0;ENFILA(FILTRAR(A8#;B8:B12));LAMBDA(x;y;APILARH(x;y=AÑO(TOMAR(b;;1)))));c)

Como tengo seleccionado dos años, obtenemos una matriz desbordada de tres columnas, en la primera columna tenemos error, las demás columnas corresponden con los años seleccionados, donde aparece VERDADERO donde hay coincidencia, y, FALSO donde no la hay.

La primera columna está de más, antes de REDUCE, usamos EXCLUIR, como argumento matriz, es la función REDUCE, omitimos el argumento filas, como argumento columnas, ponemos 1.

=LET(a;FILTRAR(A3:A5;B3:B5);b;EXCLUIR(REDUCE("";a;LAMBDA(x;y;APILARV(x;INDIRECTO(y))));1);c;EXCLUIR(REDUCE(0;ENFILA(FILTRAR(A8#;B8:B12));LAMBDA(x;y;APILARH(x;y=AÑO(TOMAR(b;;1)))));;1);c)

Creamos otra variable, usamos el condicional SI, como argumento prueba lógica, ponemos la variable “c”, como argumento valor si verdadero, tomamos la primera columna de la variable “b”, por lo que volvemos a usar la función TOMAR, como argumento valor si falso, ponemos un error.

Obtenemos las fechas donde hay coincidencia, y, error donde no la hay.

Antes del condicional SI, ponemos ENCOL, como argumento matriz es el condicional SI, como argumento ignorar, seleccionamos 3.

Tenemos las fechas donde hay coincidencias en una columna.

Creamos otra variable, volvemos a usar REDUCE, como argumento valor inicial, ponemos blanco, como argumento array, ponemos la variable “d”, como argumento función, ponemos LAMBDA, y, declaramos dos variables.

Como argumento calculo de LAMBDA, usamos APILARV, como argumento matriz1, ponemos la variable “x”, como argumento matriz2, usamos FILTRAR, como argumento array, ponemos la variable “b”, como argumento include, ponemos la variable “y” y comparamos con la primera columna de la variable “b”, por lo que volvemos a usar la función TOMAR.

Tenemos el modelo filtrado por la o las provincias seleccionadas, y, año o años seleccionados.

Como tenemos un error en la primera fila, volvemos a usar EXCLUIR.

=LET(a;FILTRAR(A3:A5;B3:B5);b;EXCLUIR(REDUCE("";a;LAMBDA(x;y;APILARV(x;INDIRECTO(y))));1);c;EXCLUIR(REDUCE(0;ENFILA(FILTRAR(A8#;B8:B12));LAMBDA(x;y;APILARH(x;y=AÑO(TOMAR(b;;1)))));;1);d;ENCOL(SI(c;TOMAR(b;;1);NOD());3);e;EXCLUIR(REDUCE("";d;LAMBDA(x;y;APILARV(x;FILTRAR(b;y=TOMAR(b;;1)))));1);e)

Vamos a preguntar si la variable “e”, devuelve un error.

=LET(a;FILTRAR(A3:A5;B3:B5);b;EXCLUIR(REDUCE("";a;LAMBDA(x;y;APILARV(x;INDIRECTO(y))));1);c;EXCLUIR(REDUCE(0;ENFILA(FILTRAR(A8#;B8:B12));LAMBDA(x;y;APILARH(x;y=AÑO(TOMAR(b;;1)))));;1);d;ENCOL(SI(c;TOMAR(b;;1);NOD());3);e;EXCLUIR(REDUCE("";d;LAMBDA(x;y;APILARV(x;FILTRAR(b;y=TOMAR(b;;1)))));1);ESERROR(e))

Obtenemos VERDADERO.

Si es error, nos debe de devolver el modelo filtrado solo por las provincias, entonces, creamos otra variable, usamos el condicional SI, como argumento prueba lógica, es la función ESERROR, como argumento valor si verdadero, ponemos la variable “b”, como argumento valor si falso, ponemos la variable “e”.

=LET(a;FILTRAR(A3:A5;B3:B5);b;EXCLUIR(REDUCE("";a;LAMBDA(x;y;APILARV(x;INDIRECTO(y))));1);c;EXCLUIR(REDUCE(0;ENFILA(FILTRAR(A8#;B8:B12));LAMBDA(x;y;APILARH(x;y=AÑO(TOMAR(b;;1)))));;1);d;ENCOL(SI(c;TOMAR(b;;1);NOD());3);e;EXCLUIR(REDUCE("";d;LAMBDA(x;y;APILARV(x;FILTRAR(b;y=TOMAR(b;;1)))));1);f;SI(ESERROR(e);b;e);f)

Tenemos el modelo filtrado solo por las provincias.

Si seleccionamos un año, pues queda filtrado por las provincias y años seleccionados, pero, donde no hay coincidencia, obtenemos error.

Creamos otra variable, filtramos la variable “f”, siempre que la primera columna de la variable “f” no sea un error.

=LET(a;FILTRAR(A3:A5;B3:B5);b;EXCLUIR(REDUCE("";a;LAMBDA(x;y;APILARV(x;INDIRECTO(y))));1);c;EXCLUIR(REDUCE(0;ENFILA(FILTRAR(A8#;B8:B12));LAMBDA(x;y;APILARH(x;y=AÑO(TOMAR(b;;1)))));;1);d;ENCOL(SI(c;TOMAR(b;;1);NOD());3);e;EXCLUIR(REDUCE("";d;LAMBDA(x;y;APILARV(x;FILTRAR(b;y=TOMAR(b;;1)))));1);f;SI(ESERROR(e);b;e);g;FILTRAR(f;NO(ESERROR(TOMAR(f;;1))));g)

Ya tenemos el modelo filtrado sin errores.

Si no tenemos nada seleccionado, devuelve un error.

Usamos el argumento cálculo de LET, usamos ORDENAR, como argumento matriz, ponemos la variable “g”, como argumento ordenar índice, ponemos 1, para ordenar por la primera columna.

=LET(a;FILTRAR(A3:A5;B3:B5);b;EXCLUIR(REDUCE("";a;LAMBDA(x;y;APILARV(x;INDIRECTO(y))));1);c;EXCLUIR(REDUCE(0;ENFILA(FILTRAR(A8#;B8:B12));LAMBDA(x;y;APILARH(x;y=AÑO(TOMAR(b;;1)))));;1);d;ENCOL(SI(c;TOMAR(b;;1);NOD());3);e;EXCLUIR(REDUCE("";d;LAMBDA(x;y;APILARV(x;FILTRAR(b;y=TOMAR(b;;1)))));1);f;SI(ESERROR(e);b;e);g;FILTRAR(f;NO(ESERROR(TOMAR(f;;1))));ORDENAR(g;1))

Usamos SI.ERROR antes de ORDENAR, como argumento valor es la función ORDENAR, como argumento valor si error, entre comillas dobles, ponemos Sin selección.

=LET(a;FILTRAR(A3:A5;B3:B5);b;EXCLUIR(REDUCE("";a;LAMBDA(x;y;APILARV(x;INDIRECTO(y))));1);c;EXCLUIR(REDUCE(0;ENFILA(FILTRAR(A8#;B8:B12));LAMBDA(x;y;APILARH(x;y=AÑO(TOMAR(b;;1)))));;1);d;ENCOL(SI(c;TOMAR(b;;1);NOD());3);e;EXCLUIR(REDUCE("";d;LAMBDA(x;y;APILARV(x;FILTRAR(b;y=TOMAR(b;;1)))));1);f;SI(ESERROR(e);b;e);g;FILTRAR(f;NO(ESERROR(TOMAR(f;;1))));SI.ERROR(ORDENAR(g;1);"Sin seleccion"))

Pues, ya tenemos la primera parte.

Lo siguiente es una agrupación, haremos tres agrupaciones, una por provincia, otra por año, y, otra por meses.

En la celda L3, ponemos LET, creamos una variable, usamos AGRUPARPOR, como argumento row_fields, usamos ELEGIRCOLS, como argumento matriz, ponemos la celda D3 junto con el operador de rango derramado, como argumento numero de columna1, ponemos 2, es decir, vamos a totalizar por provincia, como argumento values, volvemos a usar ELEGIRCOLS, como argumento matriz, volvemos a poner D3 junto con el operador de rango derramado, como argumento numero de columna1, ponemos 7, que es la columna de total, como argumento funcion, ponemos APILARH, como argumento matriz1, ponemos SUMA sin paréntesis, como argumento matriz2, ponemos PORCENTAJEDE, como argumento matriz3, ponemos MAX sin paréntesis, y, como argumento matriz4, ponemos MIN sin paréntesis.

=LET(a;AGRUPARPOR(ELEGIRCOLS(D3#;2);ELEGIRCOLS(D3#;7);APILARH(SUMA;PORCENTAJEDE;MAX;MIN));a)

Obtenemos el total de la o las provincias seleccionadas, el porcentaje respecto al total, la venta máxima de cada provincia, y, la venta mínima de cada provincia.

Usamos el argumento calculo de LET, preguntamos que, si la variable “a” es igual a blanco, que ponga, entre comillas dobles, Provincia, en caso contrario, que devuelva la variable “a”.

=LET(a;AGRUPARPOR(ELEGIRCOLS(D3#;2);ELEGIRCOLS(D3#;7);APILARH(SUMA;PORCENTAJEDE;MAX;MIN));SI(a="";"Provincia";a))

Ya tenemos el encabezado para provincia.

Seleccionamos las tres provincias.

Seleccionamos el modelo.

Vamos a la pestaña de inicio, desplegamos formato condicional, elegimos nueva regla.

En la ventana dar formato a los valores, ponemos la celda L3, pero, antes de L ponemos el símbolo de dólar, igualamos a la palabra Total, vamos a dar formato a la fila de total, damos un formato.

Aceptamos, damos formato a los encabezados.

Ya lo tenemos.

Si no seleccionamos ninguna provincia, nos aparece el color del encabezado, y, error en el encabezado.

Seleccionamos el encabezado, seleccionamos nueva regla de formato condicional, en la ventana dar formato a los valores, ponemos:

En formato ponemos sin relleno.

Aceptamos, y, si no tenemos ninguna provincia seleccionada, no aparece nada.

En otra celda, volvemos a usar LET, creamos una variable, usamos AGRUPARPOR, como argumento row_fields, usamos AÑO, como argumento usamos TOMAR, como argumento matriz, ponemos D3 junto con el operador de rango derramado, omitimos filas, como argumento columnas ponemos 1, es decir, vamos a totalizar por años.

=LET(a;AGRUPARPOR(AÑO(TOMAR(D3#;;1))

Como argumento values, usamos TOMAR, como argumento matriz, ponemos D3 junto con el operador de rango derramado, omitimos filas, como argumento columnas, ponemos -1, es decir, tomamos la columna de total.

=LET(a;AGRUPARPOR(AÑO(TOMAR(D3#;;1));TOMAR(D3#;;-1)

Como argumento funcion, ponemos APILARH, como argumento matriz1, ponemos SUMA sin paréntesis, como argumento matriz2, ponemos PORCENTAJEDE.

=LET(a;AGRUPARPOR(AÑO(TOMAR(D3#;;1));TOMAR(D3#;;-1);APILARH(SUMA;PORCENTAJEDE));a)

Tenemos el total y porcentaje respecto al total del año o años seleccionados.

Usamos el argumento cálculo de LET, preguntamos si la variable “a” es igual a blanco, que ponga Año, en caso contrario, que devuelva la variable “a”.

=LET(a;AGRUPARPOR(AÑO(TOMAR(D3#;;1));TOMAR(D3#;;-1);APILARH(SUMA;PORCENTAJEDE));SI(a="";"Año";a))

Tenemos el encabezado Años.

Repetimos la regla de formato condicional que el ejemplo anterior.

Igual que antes, seleccionamos todos los años, y, damos el mismo formato condicional que para provincia.

Por último, vamos a agrupar meses por año o años seleccionados.

En la celda L19, usamos la funcion PIVOTARPOR, como argumento row_fields, ponemos AÑO, como argumento usamos TOMAR, como argumento matriz, ponemos D3 junto con el operador de rango derramado, omitimos filas, como argumento columnas, ponemos1, nos vamos a quedar con el año de la columna fecha.

=PIVOTARPOR(AÑO(TOMAR(D3#;;1))

Como argumento col_fields, usamos MES, como argumento usamos TOMAR, como argumento matriz, ponemos D3 junto con el operador de rango derramado, omitimos filas, como argumento columnas, ponemos 1, vamos a tomar el mes de la columna fecha.