Seguimos trabajando con el modelo de ventas de productos en diferentes centros comerciales, provincias, y, fechas.
Vamos a crear una lista desplegable con los encabezados, excepto, las columnas de cantidad, precio, y, total.
El modelo esta en formato de tabla, y, se llama Venta.
En la celda V2, voy a traerme los encabezados.
=Venta[# Encabezados]
Desde la celda V1 hasta la celda AB0, voy a poner 1 en la columna que quiero ver, y, 0 la columna que no quiero ver.
Voy a filtrar los encabezados, y, como argumento include, selecciono los ceros, y, unos.
=FILTRAR(Venta[# Encabezados];V1:AB1)
Ya tengo los encabezados con los que trabajar.
En la celda I1, voy a crear una lista desplegable, con dichos encabezados, para ello, voy a la pestaña de datos, dentro del grupo herramientas de datos, hago clic en validación de datos.
Desplegamos permitir, y, seleccionamos lista, y, en la ventana origen, seleccionamos la celda V2, seguido del operador de rango derramado (#).
Aceptamos.
Si desplegamos, vemos que tenemos los encabezados que necesitamos.
En la celda I2, creamos otra lista desplegable, con los valores del encabezado seleccionado.
Volvemos a validación de datos, desplegamos permitir, y, seleccionamos lista, y, en la ventana origen, usamos la expresión, que ya hemos usado en videos anteriores, INDIRECTO, seguido del nombre de la tabla, junto con una apertura de corchetes, concatenado con el valor de la celda I1, concatenado con un cierre de corchetes, y, cerramos paréntesis.
Aceptamos.
Seleccionamos un valor.
En la celda I6, voy a volver a usar INDIRECTO, y, me traigo los valores del encabezado seleccionado.
=INDIRECTO("Venta[" & I1 & "]")
En la celda J6, voy a filtrar la tabla venta, por los encabezados diferentes a encabezado seleccionado en la celda I1.
=FILTRAR(Venta;Venta[# Encabezados]<>I1)
Ahora, con la función APILARH (HSTACK), voy a unir ambas expresiones.
=APILARH(INDIRECTO("Venta[" & I1 & "]");FILTRAR(Venta;Venta[# Encabezados]<>I1))
Tenemos la misma matriz anterior, pero en una sola matriz, porque aun no la hemos filtrado, y, debemos de filtrarla por el valor seleccionado según el encabezado seleccionado.
Entonces, después del signo igual, pongo la función FILTRAR.
=FILTRAR(APILARH(INDIRECTO("Venta["&I1&"]");FILTRAR(Venta;Venta[# Encabezados]<>I1))
Y, como argumento include, usamos de nuevo la función INDIRECTO con el valor de la celda I1, que debe de ser igual al valor de la celda I2.
=FILTRAR(APILARH(INDIRECTO("Venta["&I1&"]");FILTRAR(Venta;Venta[#Encabezados]<>I1));INDIRECTO("Venta["&I1&"]")=I2)
Aceptamos, y, podemos ver como ha quedado filtrado por el valor del encabezado seleccionado.
Ahora, en la celda J1, vamos a crear otro desplegable, pero con los encabezados que necesitamos, excepto, el encabezado que hemos seleccionado en la celda I1.
Para ello, en la celda V5, voy a traerme la matriz desbordada de la celda V2, que son los encabezados del primer desplegable, filtro dicha matriz desbordada, siempre que sea diferente a al valor de la celda I1.
=FILTRAR(V2#;V2#<>I1)
Podemos ver que tenemos los encabezados, excepto, el seleccionado en la celda I1.
Pues, en la celda J1, creamos una lista desplegable con dichos valores.
En la celda J2, creamos otra lista desplegable, donde usamos de nuevo la función INDIRECTO, para rellenar la lista.
Seleccionamos un valor.
Ahora debe de filtrase por ambas condiciones.
He de preguntar que si el valor de la celda J2, es igual a blanco, debe de filtrarse por la primera condición, en caso contrario, debe de filtrarse por las dos condiciones.
Usare el condicional SI.
Entonces, empezamos con la pregunta.
=SI(J2="";
En ese caso, el argumento valor si verdadero, debe de filtrar el modelo por la primera condición, que es la expresión anterior.
=SI(J2="";FILTRAR(APILARH(INDIRECTO("Venta["&I1&"]");FILTRAR(Venta;Venta[#Encabezados]<>I1));INDIRECTO("Venta["&I1&"]")=I2)
En caso contrario, en el argumento valor si falso, debe de filtrar por ambas condiciones, por lo que la primera condición de la expresión anterior, la voy a encerrar entre paréntesis.
=FILTRAR(APILARH(INDIRECTO("Venta["&I1&"]");FILTRAR(Venta;Venta[#Encabezados]<>I1));(INDIRECTO("Venta["&I1&"]")=I2))
Copio y pego la función FILTRAR anterior, pongo el símbolo de asterisco, abro un paréntesis, uso la función INDIRECTO con la celda J1, y, lo igualo al valor de la celda J2, cierro paréntesis de condición, cierro paréntesis de FILTRAR, y, cierro paréntesis del condicional SI.
=SI(J2="";FILTRAR(APILARH(INDIRECTO("Venta["&I1&"]");FILTRAR(Venta;Venta[#Encabezados]<>I1));(INDIRECTO("Venta["&I1&"]")=I2));FILTRAR(APILARH(INDIRECTO("Venta["&I1&"]");FILTRAR(Venta;Venta[#Encabezados]<>I1));(INDIRECTO("Venta["&I1&"]")=I2)*(INDIRECTO("Venta["&J1&"]")=J2)))
Vemos como ha quedado filtrado por los encabezados seleccionados.
Si borro el valor de la celda J2, vemos como ha quedado filtrado por el primer encabezado.
Si nos fijamos en la expresión, INDIRECTO("Venta["&I1&"]"), vemos que se repite cuatro veces, por lo que voy a usar la función LET, y, en una variable, almaceno dicha expresión, y, después sustituto cada expresión por la variable.
=SI(J2="";FILTRAR(APILARH(INDIRECTO("Venta["&I1&"]");FILTRAR(Venta;Venta[#Encabezados]<>I1));(INDIRECTO("Venta["&I1&"]")=I2));FILTRAR(APILARH(INDIRECTO("Venta["&I1&"]");FILTRAR(Venta;Venta[#Encabezados]<>I1));(INDIRECTO("Venta["&I1&"]")=I2)*(INDIRECTO("Venta["&J1&"]")=J2)))
Quedaría:
=LET(a;INDIRECTO("Venta["&I1&"]");SI(J2="";FILTRAR(APILARH(a;FILTRAR(Venta;Venta[#Encabezados]<>I1));(a=I2));FILTRAR(APILARH(a;FILTRAR(Venta;Venta[#Encabezados]<>I1));(a=I2)*(INDIRECTO("Venta["&J1&"]")=J2))))
La sintaxis queda algo más corta.
Ahora, en la celda I5, me traigo el encabezado de la celda I1, y, en la celda J5, me traigo los encabezados que sean diferentes a I1.
=FILTRAR(Venta[#Encabezados];Venta[#Encabezados]<>I1)
Ya tenemos los encabezados dinámicos.
Comments