Filtrar por productos y años

Tenemos el modelo que suelo usar habitualmente.

El modelo está en formato de tabla, y, se llama Ventas.

Queremos elegir uno o más productos, además, de uno o más años, y, que nos filtre la tabla ventas en un reporte nuevo.

Lo vamos a realizar a través de casillas de verificación.

En las celdas J1:N1, nos traemos los productos únicos y lo penemos en horizontal.

=ENFILA(UNICOS(Ventas[Producto]))

Seleccionamos el rango J2:N2, vamos a la pestaña de insertar, y, hacemos clic en Checkbox.

En la celda J3, ponemos la funcion SUMA, como argumento usamos el condicional SI, como argumento prueba lógica, preguntamos si el rango J2:N2 es igual a VERDADERO, en ese caso, que devuelva 1, en caso contrario, que devuelva 0.

=SUMA(SI(J2:N2;1;0))

Obtenemos la suma de todos los VERDADEROS.

En la celda J4, nos traemos los años únicos, y, lo ponemos en horizontal.

Seleccionamos el rango J5:O5, vamos a la pestaña de insertar, y, hacemos clic en Checkbox.

En la celda J6, repetimos la operación de SUMA anterior.

=SUMA(SI(J5:O5;1;0))

En la celda J8, usamos LET, creamos una variable, ponemos el operador lógico Y, como argumento valor logico1, preguntamos si la celda J3 es igual a cero, como argumento valor logico2, preguntamos si la celda J6 es igual a cero, es decir, no hemos seleccionamos ni un producto ni un año.

=LET(a;Y(J3=0;J6=0);a)

Obtenemos VERDADERO, porque no hemos seleccionado ningún elemento.

Creamos otra variable, volvemos a usar el operador lógico Y, preguntamos si la celda J3 es mayor a cero, y, si la celda J6 es igual a cero, es decir, hemos seleccionado uno o más productos.

=LET(a;Y(J3=0;J6=0);b;Y(J3>0;J6=0);b)

Seleccionamos un producto, y, vemos que nos devuelve VERDADERO.

Creamos otra variable, volvemos a usar el operador lógico Y, preguntamos si la celda J3 es igual a 0, y, si la celda J6 es mayor a cero, es decir, hemos seleccionamos un año o más, y, ningún producto.

=LET(a;Y(J3=0;J6=0);b;Y(J3>0;J6=0);c;Y(J3=0;J6>0);c)

Seleccionamos un año, o, más, y, ningún producto, y, nos devuelve VERDADERO.

Creamos otra variable, volvemos a usar el operador lógico Y, preguntamos si la celda J3 es mayor a cero, y, si la celda J6, también, es mayor a cero, es decir, hay seleccionamos uno o más productos, y, un año o más años.

=LET(a;Y(J3=0;J6=0);b;Y(J3>0;J6=0);c;Y(J3=0;J6>0);d;Y(J3>0;J6>0);d)

Seleccionamos un producto y un año, o, más, y, nos devuelve VERDADERO.

Ya tenemos las cuatro posibilidades posibles.

Creamos otra variable, o, usamos el condicional SI, como argumento prueba lógica, ponemos la variable “a”, como argumento valor si verdadero, ponemos Ventas, es decir, si no hay seleccionado productos, ni años, que nos devuelva el modelo completo.

=LET(a;Y(J3=0;J6=0);b;Y(J3>0;J6=0);c;Y(J3=0;J6>0);d;Y(J3>0;J6>0);e;SI(a;Ventas

Como argumento valor si falso, ponemos otro condicional SI, como argumento prueba lógica, ponemos la variable “b”, como argumento valor si verdadero de este segundo SI, usamos BUSCARX, como argumento valor buscado, seleccionamos la columna de producto, como argumento matriz de búsqueda, seleccionamos el rango J1:N5, como argumento matriz devuelta, seleccionamos el rango J2:N2.

Vamos a copiar la funcion BUSCARX, y, la pegamos en una celda.

=BUSCARX(Ventas[Producto];J1#;J2:N2)

Tenemos una matriz desbordada en vertical con VERDADERO donde el producto seleccionado aparece en la columna de producto, y, FALSO donde no hay coincidencia.

Si seleccionamos otro producto, obtendremos más VERDADEROS.

Pues, es lo que obtenemos cuando la variable “b” devuelve VERDADERO.

Probemos la variable.

=LET(a;Y(J3=0;J6=0);b;Y(J3>0;J6=0);c;Y(J3=0;J6>0);d;Y(J3>0;J6>0);e;SI(a;Ventas;SI(b;FILTRAR(Ventas;BUSCARX(Ventas[Producto];J1#;J2:N2));e)

Seleccionamos dos productos.

Tenemos el reporte por dichos productos.

Vamos por los años.

Si seleccionamos un año, y, ningún producto, debe de filtrarse el modelo por dicho año.

Vamos al argumento valor si falso del segundo SI, preguntamos que si la variable “c”, quiere decir que se ha seleccionado un año, y, ningún producto, filtramos la tabla Ventas, que es el argumento array de FILTRAR, como argumento include, usamos BUSCARX, como argumento valor buscado, usamos la funcion AÑO, y, como argumento seleccionamos la columna de fecha, como argumento matriz de búsqueda, seleccionamos J4 junto con el operador de rango derramado, como argumento matriz devuelta, seleccionamos el rango J5:O5.

=LET(a;Y(J3=0;J6=0);b;Y(J3>0;J6=0);c;Y(J3=0;J6>0);d;Y(J3>0;J6>0);e;SI(a;Ventas;SI(b;FILTRAR(Ventas;BUSCARX(Ventas[Producto];J1#;J2:N2));SI(c;FILTRAR(Ventas;BUSCARX(AÑO(Ventas[Fecha]);J4#;J5:O5)))));e)

Si seleccionamos un año, o, mas de un año, vemos como el modelo queda filtrado por el o los años seleccionados.

Como argumento valor si falso, filtramos la tabla Ventas, como argumento include, entre paréntesis, ponemos BUSCARX, como argumento valor buscado, seleccionamos la columna de producto, como argumento matriz de búsqueda, seleccionamos la celda J1 junto con el operador de rango derramado, como argumento matriz devuelta, seleccionamos el rango J2:N2, ponemos el símbolo de asterisco, abrimos otros paréntesis, volvemos a usar BUSCARX, como argumento valor buscado, usamos AÑO, como argumento seleccionamos la columna de fecha, como argumento matriz de búsqueda, seleccionamos la celda J4 junto con el operador de rango derramado, como argumento matriz devuelta, seleccionamos el rango J5:O5, e, igualamos a 1.

=LET(a;Y(J3=0;J6=0);b;Y(J3>0;J6=0);c;Y(J3=0;J6>0);d;Y(J3>0;J6>0);e;SI(a;Ventas;SI(b;FILTRAR(Ventas;BUSCARX(Ventas[Producto];J1#;J2:N2));SI(c;FILTRAR(Ventas;BUSCARX(AÑO(Ventas[Fecha]);J4#;J5:O5));FILTRAR(Ventas;(BUSCARX(Ventas[Producto];J1#;J2:N2))*(BUSCARX(AÑO(Ventas[Fecha]);J4#;J5:O5))=1))));e)

Vamos a copiar los dos BUSCARX, y, probamos en una celda.

=(BUSCARX(Ventas45[Producto];J1#;J2:N2))*(BUSCARX(AÑO(Ventas45[Fecha]);J4#;J5:O5))

El primer BUSCARX, nos devuelve una matriz desbordada en vertical donde vamos a obtener VERDADERO si uno de los productos seleccionados se encuentra en la columna de producto, y, FALSO donde no hay coincidencia.

El siguiente BUSCARX nos devuelve VERDADERO donde el o los años seleccionados se encuentran en la columna de fecha, y, FALSO donde no hay coincidencia.

Los dos juntos, nos devuelve 1 donde el producto o productos, y, el año, o, años se encuentran en la columna de fecha y producto, y, 0, donde no hay coincidencia, con la funcion FILTRAR, filtramos el modelo donde el resultado de los BUSCARX sea igual a 1.

LET(a;Y(J3=0;J6=0);b;Y(J3>0;J6=0);c;Y(J3=0;J6>0);d;Y(J3>0;J6>0);e;SI(a;Ventas;SI(b;FILTRAR(Ventas;BUSCARX(Ventas[Producto];J1#;J2:N2));SI(c;FILTRAR(Ventas;BUSCARX(AÑO(Ventas[Fecha]);J4#;J5:O5));FILTRAR(Ventas;(BUSCARX(Ventas[Producto];J1#;J2:N2))*(BUSCARX(AÑO(Ventas[Fecha]);J4#;J5:O5))=1))));e)

Usamos el argumento calculo de LET, usamos APILARV, como argumento matriz1, nos traemos el encabezado, como argumento matriz2, ponemos la variable “e”.

=LET(a;Y(J3=0;J6=0);b;Y(J3>0;J6=0);c;Y(J3=0;J6>0);d;Y(J3>0;J6>0);e;SI(a;Ventas;SI(b;FILTRAR(Ventas;BUSCARX(Ventas[Producto];J1#;J2:N2));SI(c;FILTRAR(Ventas;BUSCARX(AÑO(Ventas[Fecha]);J4#;J5:O5));FILTRAR(Ventas;(BUSCARX(Ventas[Producto];J1#;J2:N2))*(BUSCARX(AÑO(Ventas[Fecha]);J4#;J5:O5))=1))));APILARV(Ventas[#Encabezados];e))

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco