Filtrar por provincia, centro, producto, y, año

Tenemos el ejemplo del video anterior, pero lo vamos a modificar.

De momento, voy a borrar las casillas y los años.

Esta parte no será modificada.

En la celda U1, voy a poner los encabezados.

=Ventas[# Encabezados]

Igual que antes, vamos a trabajar con las columnas provincias, centro, y, productos, vemos que son columnas contiguas.

En la celda I1, igual que antes, vamos a elegir el encabezado por el que filtrar, cuyos datos se derraman a partir de la celda S2.

Pero, lo que quiero es que en las celdas J1, y, K1, aparezcan los dos encabezados siguientes, es decir, si hemos seleccionado centro, pues, en las celdas J1 y K1, deben de aparecer provincia, y, producto.

Me voy a traer de nuevo los encabezados.

=Ventas[# Encabezados]

Ahora, filtro los encabezados que sean diferentes al valor de la celda I1, en la celda J1.

=FILTRAR(Ventas[# Encabezados];Ventas[# Encabezados]<>I1)

Hacemos lo mismo para los encabezados de la celda U1.

Vemos que aparecen todos los encabezados, excepto, el seleccionado en la celda I1.

Como hemos dicho anteriormente, en la posición 2 y 3, siempre se encontrarán los encabezados que vamos a necesitar, por lo que voy a poner 1 en el encabezado que quiero ver, y, 0 en el que no quiero ver, en la fila siguiente a la celda U1.

Ahora, filtro la expresión anterior, de la celda J1, y, como argumento include, selecciono los ceros, y, unos.

=FILTRAR(FILTRAR(Ventas[# Encabezados];Ventas[# Encabezados]<>I1);U2:Z2)

Ya tenemos los siguientes encabezados con los que voy a trabajar.

Si vamos cambiando, veremos como se actualizan los encabezados.

Vemos que usamos dos veces la expresión, Ventas[# Encabezados], por lo que voy a usar la función LET, y, dicha expresión, se la asigno a una variable.

=LET(a;Ventas[# Encabezados];FILTRAR(FILTRAR(a;a<>I1);U2:Z2))

Lo siguiente es crear un desplegable en las celdas J2 y K2, con los elementos del encabezado.

Vamos a la pestaña datos, dentro del grupo, herramientas de datos, hacemos clic en validación de datos.

Desplegamos permitir, y, seleccionamos lista, en la ventana origen, voy a usar la misma función, usada anteriormente, para referirme a una columna de la tabla.

Aceptamos.

Si desplegamos, veremos que se ha rellenado con los valores de del encabezado de J1.

En la celda K2, creamos otra lista desplegable, con la misma función, pero se cambia J1 por K1.

Ahora, vamos a filtrar por los valores seleccionados.

Lo siguiente que voy a hacer es preparar los nombres de columnas, para ello, en la celda U5, voy a seleccionar desde I1 a K1.

=I1:K1

Ahora, después del signo igual, entre comillas dobles, pongo el nombre de la tabla, que es ventas, seguido de una apertura de corchetes, concateno con el rango anterior (I1:K1), y, concateno con un corchete de cierre, entre comillas dobles.

="Ventas[" & I1:K1 & "]"

Tenemos una matriz desbordada en horizontal con el nombre de la tabla, y, nombre de encabezado, entre corchetes.

Lo siguiente es filtrar el modelo por estas condiciones, para ello, escribo el signo igual, seguido de la función FILTRAR, y, abrimos un paréntesis.

=FILTRAR(

Como argumento array, ponemos el nombre de la tabla, ventas.

=FILTRAR(Ventas

Como argumento include, abrimos un paréntesis, para poner la primera condición, que es:

=FILTRAR(Ventas;(INDIRECTO(U5)=I2)

Ponemos el símbolo de asterisco, abrimos otro paréntesis, y, ponemos la segunda condición.

=FILTRAR(Ventas;(INDIRECTO(U5)=I2)*(INDIRECTO(V5)=J2)

Volvemos a poner el símbolo de asterisco, abrimos otro paréntesis, para poner la tercera condición.

=FILTRAR(Ventas;(INDIRECTO(U5)=I2)*(INDIRECTO(V5)=J2)*(INDIRECTO(W5)=K2)

Como argumento, si vacío, ponemos un texto en blanco, y, cerramos paréntesis.

=FILTRAR(Ventas;(INDIRECTO(U5)=I2)*(INDIRECTO(V5)=J2)*(INDIRECTO(W5)=K2);"")

Aceptamos, y, vemos el modelo filtrado por las condiciones especificadas.

Podemos ir cambiando, y, veremos cómo se filtra correctamente.

Ahora, vamos a la función FILTRAR, y, seleccionamos las tres condiciones.

Fijamos todas las referencias.

Pulsamos CTRL más C para copiar, y, pulsamos la tecla de Escape.

Voy a crear un nombre de rango con las tres condiciones, para ello, vamos a la pestaña de fórmulas, dentro del grupo nombres definidos, hacemos clic en asignar nombre.

Sustituimos las tres condiciones por el nombre de rango, y, vemos que todo sigue igual.

=FILTRAR(Ventas;criterios;"")

Lo siguiente va a ser trabajar con la columna fecha filtrada, para quedarnos con los años.

La pregunta es, ¿Cuántas fechas tenemos filtradas?

Si uso la función CONTARA, me devuelve un recuento de todas las celdas ocupadas alfanuméricas.

Lo hare en la celda U9.

=CONTARA(I5#)

Me devuelve 42.

Pero, si divido la función CONTARA, entre el numero de columnas, con la función COLUMNAS, obtendré el numero de filas de cada columna.

=CONTARA(I5#)/COLUMNAS(I5#)

Me devuelve 6, es decir, cada columna esta compuesta de seis filas.

Con la función SECUENCIA, voy a crear una matriz desbordada en vertical donde solo usare el argumento filas, que será la expresión anterior.

=SECUENCIA(CONTARA(I5#)/COLUMNAS(I5#))

El problema es que no podemos extraer los años de la matriz desbordada (I5#), porque al filtrar por un año, esta matriz con los años cambiará, y, devolverá un error, por lo que debemos de hacerlo con el modelo ventas.

Entonces, voy a filtrar la columna fecha de la tabla ventas, por el nombre de rango criterios.

=FILTRAR(Ventas[Fecha];criterios;"")

Obtengo las mismas fechas que la matriz desbordada, la única diferencia, es que solo nos hemos traído la columna fecha.

Voy a usar la función Año, para quedarme con los años.

=AÑO(FILTRAR(Ventas[Fecha];criterios;""))

Me quedo con los valores únicos.

=UNICOS(AÑO(FILTRAR(Ventas[Fecha];criterios;"")))

Lo siguiente que voy a hacer es usar la función APILARV (VSTACK), donde como argumento matriz1, va a ser el texto no selección, y, como argumento matriz2, será la expresión anterior.

=APILARV("No selección";UNICOS(AÑO(FILTRAR(Ventas[Fecha];criterios;""))))

En la celda L2, voy a crear una lista desplegable, con los años.

Lo siguiente es filtrar el modelo que ya tenemos filtrado por el año que seleccionemos, pero si seleccionamos no selección, deben de aparecer todos los registros, la expresión FILTRAR(Ventas;criterios;""), para este ejemplo, la vamos a usar varias veces, porque siempre tenemos que trabajar con el modelo filtrado, nunca con la matriz desbordada obtenida (I5#), primero lo vamos a hacer repitiendo la expresión anterior.

Voy a usar el condicional SI, para preguntar por el valor de la celda L2, si es igual, o no, a no selección.

=SI(L2<>"No selección"

Si es diferente a no selección, tiene que filtrar la tabla ventas por los criterios, es decir, primero filtramos la tabla ventas por los criterios, y, después filtramos este resultado, donde vamos a aplicar los criterios, entonces filtrar la tabla ventas por los criterios, es el argumento array del primer filtrar.

=SI(L2<>"No selección";FILTRAR(FILTRAR(Ventas;criterios;"")

En el argumento include, extraemos el año con la función INDICE, cuyo argumento matriz es filtrar de nuevo la tabla ventas por los criterios, y nos devuelva el numero de filas devuelto por la función SECUENCIA, y, una columna.

AÑO(INDICE(FILTRAR(Ventas;criterios);SECUENCIA(CONTARA(FILTRAR(Ventas;criterios))/COLUMNAS(FILTRAR(Ventas;criterios)));1))

Y este matriz desbordada debe de ser igual al valor de la celda L2.

=SI(L2<>"No selección";FILTRAR(FILTRAR(Ventas;criterios;"");AÑO(INDICE(FILTRAR(Ventas;criterios);SECUENCIA(CONTARA(FILTRAR(Ventas;criterios))/COLUMNAS(FILTRAR(Ventas;criterios)));1))=L2)

En el argumento valor si falso del condicional SI, pues que nos filtre la tabla completa, porque ha sido seleccionado no selección.

=SI(L2<>"No selección";FILTRAR(FILTRAR(Ventas;criterios;"");AÑO(INDICE(FILTRAR(Ventas;criterios);SECUENCIA(CONTARA(FILTRAR(Ventas;criterios))/COLUMNAS(FILTRAR(Ventas;criterios)));1))=L2);FILTRAR(Ventas;criterios))

Si seleccionamos un año, veremos cómo queda filtrado por dicho año.

Si seleccionamos no selección, vemos como se filtra el modelo por todos los años.

Entonces, como usamos varias la expresión FILTRAR(Ventas;criterios;""), voy a usar la función LET, donde creo una variable con dicha expresión, y, la sustituyo.

=LET(a;FILTRAR(Ventas;criterios;"");SI(L2<>"No selección";FILTRAR(a;AÑO(INDICE(a;SECUENCIA(CONTARA(a)/COLUMNAS(a));1))=L2);a))

Vemos que ha quedado reducida la expresión.