Seguimos trabajando con el modelo de datos que suelo usar habitualmente, ventas de productos, en diferentes centros comerciales, provincias, y, fechas.
Queremos crear una lista desplegable donde aparezcan los años, después, en la celda siguiente deben de aparecer los demás años, a partir del año seleccionado, teniendo en cuenta, que si seleccionamos el ultimo año, debe de aparecer desde el primer año a un año antes hasta el último.
Vemos en el modelo, que tenemos una columna con los años.
En la celda J2, voy a crear la lista desplegable con todos los años, para ello, vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Desplegamos permitir, y, seleccionamos lista, en la ventana origen, vamos a usar la función INDIRECTO, para traernos la columna años, como sigue:
Si desplegamos, veremos los años, ya sabemos que, en la última actualización de validación de datos, quita los duplicados, por lo que no tenemos que hacerlo nosotros.
Seleccionamos un año.
Pues a partir de la celda J3, deben de aparecer los siguientes años al seleccionado, por ejemplo, si elijo 2020, pues, solo debe de aparecer el año 2021.
Vamos a empezar trayendo los años únicos de la columna años.
=UNICOS(Table1[Años])
Ahora, con la función FILTRAR, voy a filtrar la columna de años únicos, siempre que la columna de años únicos sea mayor al año seleccionado.
=FILTRAR(UNICOS(Table1[Años]);UNICOS(Table1[Años])>J2)
Aceptamos, y, vemos los años superiores al seleccionado.
Pero, que ocurre, si seleccionamos el ultimo año, pues, que nos devuelve un error de cálculo.
Entonces, cuando seleccione el ultimo año, quiero que me aparezcan todos los años, excepto, el ultimo, para ello, voy a usar el argumento si vacío, de la función FILTRAR, donde vuelvo a filtrar los años únicos, siempre que sea menor al año seleccionado.
=FILTRAR(UNICOS(Table1[Años]);UNICOS(Table1[Años])>J2;FILTRAR(UNICOS(Table1[Años]);UNICOS(Table1[Años])<J2))
Vemos como ahora, aparecen todos los años, excepto el ultimo.
Podemos ver que la expresión, UNICOS(Table1[Años]), se repite varias veces, por lo que voy a usar la función LET, donde voy a declarar una variable, y, almaceno dicha expresión, y, después, la sustituyo.
=LET(a;UNICOS(Table1[Años]);FILTRAR(a;a>J2;FILTRAR(a;a<J2)))
De esta manera, la sintaxis es más corta.
En la celda K1, voy a traerme los centros únicos en horizontal.
=TRANSPONER(UNICOS(Table1[Centro]))
En la celda K2, donde hemos seleccionado un año, voy a traerme los centros donde se ha vendido durante el año seleccionado.
Voy a preguntar con el condicional SI, que si la columna años, es igual al año seleccionado (J2), pues que me devuelva el centro, en caso contrario, que me devuelva un texto en blanco.
=SI(Table1[Años]=J2;Table1[Centro];"")
Obtenemos una matriz desbordada con los centros a los que corresponde el año seleccionado.
Me quedo con los valores únicos.
=UNICOS(SI(Table1[Años]=J2;Table1[Centro];""))
Y transpongo los datos.
=TRANSPONER(UNICOS(SI(Table1[Años]=J2;Table1[Centro];"")))
Lo siguiente es preguntar que, si el resultado de la expresión anterior es igual al valor de K1, donde le añado el operador de rango derramado, entonces, que una ponga una “X”, en caso contrario, que ponga un texto en blanco.
=SI(TRANSPONER(UNICOS(SI(Table1[Años]=J2;Table1[Centro];"")))=K1#;"X";"")
Vemos que ahora en vez de aparecer el centro, aparece una X, pero nos aparece una celda más con un error.
Pues, voy a usar la función SI.ERROR, para que en caso de error, ponga un texto en blanco.
=SI.ERROR(SI(TRANSPONER(UNICOS(SI(Table13[Años]=J2;Table13[Centro];"")))=K1#;"X";"");"")
Fijamos K1, y, arrastramos, y, ya lo tenemos.
=SI.ERROR(SI(TRANSPONER(UNICOS(SI(Table13[Años]=J2;Table13[Centro];"")))=$K$1#;"X";"");"")
Comentários