30 ago 20225 Min. de lectura

Ejemplo de EXPANDIR y FILTRAR

Vamos a ver un ejemplo de la función EXPANDIR, y, FILTRAR, donde de nuevo, vamos a volver a usar la función INDIRECTO, para referirnos a una columna de la tabla.

El ejemplo se trata de obtener la cantidad, precio, y, total de uno de los siguientes encabezados:

· Fecha

· Provincia

· Centro

· Producto

Vamos a empezar, e, iremos comprendiendo el ejemplo.

En la celda I2, voy a crear una lista desplegable con los 4 primeros encabezados, 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, y, en la ventana origen, seleccionamos los cuatro primeros encabezados.

Seleccionamos un encabezado.

Vemos que tenemos el encabezado de fecha, pero cuando elija dicho encabezado, no quiero que se carguen las fechas, sino los años únicos, decir, que, con la nueva actualización de validación de datos, cuando seleccionamos una columna donde hay valores repetidos, en la lista desplegable, solo aparecerán los valores únicos.

Para ello, en la celda N2, voy a preguntar que si el valor de la celda I2, es igual a fecha, que me devuelva los años de la columna fecha, donde usare la función AÑO, pero no voy a quitar duplicados, porque la lista desplegable, ya los quita, si no tuviéramos esta actualización, si tendríamos que quitarlos.

=SI(I2="Fecha";AÑO(Table1[Fecha]);"")

En la celda O2, voy a usar la función INDIRECTO, para traerme el valor del encabezado seleccionado, para ello, después del signo igual, ponemos la función INDIRECTO, abrimos un paréntesis, entre comillas dobles, ponemos le nombre de la tabla, junto con un corchete de apertura.

=INDIRECTO("Table1["

Concatenamos con la celda I2.

=INDIRECTO("Table1[" & I2

Concatenamos con un corchete de cierre, y, cerramos paréntesis.

=INDIRECTO("Table1[" & I2 & "]")

Aceptamos.

Ya tenemos rescatada la columna.

En la celda J2, creo otra lista desplegable con los valores de la columna rescatada, pero en este caso, en la ventana origen de lista, he de usar el condicional SI, para preguntar que, si el valor de la celda I2 es igual a Fecha, que lo rellene con los valores a partir de la celda N2, en caso contrario, que lo rellene con los valores a partir de la celda O2, en ambas celdas, uso el operador de rango derramado (#) para que siempre se rellene desde N2 u O2 hasta la ultima celda ocupada.

Aceptamos.

Si probamos, y, elegimos fecha veremos que queda rellena con los años únicos.

Pero, si seleccionamos un encabezado distinto a fecha, se rellenan con los valores de dicho encabezado.

Lo siguiente es con la función FILTRAR, filtrar las columnas cantidad, precio, y, total:

=FILTRAR(Table13[[Cantidad]:[Total]

Y en el argumento include, volvemos a usar INDIRECTO, para rescatar el encabezado seleccionado, e, igualarlo al valor de la celda J2.

Cerramos paréntesis y aceptamos.

=FILTRAR(Table13[[Cantidad]:[Total]];INDIRECTO("Table1["&I2&"]")=J2)

Y tenemos filtrado el modelo par el valor de la celda J2.

Voy a rellenar el argumento si vacío, con un texto en blanco.

=FILTRAR(Table13[[Cantidad]:[Total]];INDIRECTO("Table1["&I2&"]")=J2;"")

Ahora mismo, la función FILTRAR, esta filtrando por el valor de la celda J2, pero si elegimos fecha, y, un año, no nos devuelve nada.

Cosa que es correcto, porque al seleccionar la columna fecha en la lista desplegable, vemos que la siguiente lista se rellena con los años, pero esta comparando la columna fecha con el valor de J2, donde hay un año, pero en la columna fecha, solo hay fechas, no años, por lo que no nos presente nada, es correcto.

Por lo que debo de añadir una nueva condición a la función FILTRAR.

Voy a poner la condición a añadir a la función FILTRAR en una celda, donde después del signo igual, pongo la función AÑO, después INDIRECTO como lo hemos hecho anteriormente.

=AÑO(INDIRECTO("Table1["&I2&"]"))

Vemos que obtenemos la columna fecha, pero solo los años.

Ya sabemos que no tenemos que quitar duplicados, porque la lista desplegable lo hará.

Pues, esta es la segunda condición para añadir en la función FILTRAR.

Lo hare con un condicional SI, he de modificar el argumento include de la función FILTRAR, donde pregunto que si el valor de I2 es diferente a Fecha.

=FILTRAR(Table1[[Cantidad]:[Total]];SI(I2<>"Fecha"

Si el valor de la celda I2, es diferente a Fecha, debe de realizar la condición que ya tenemos.

=FILTRAR(Table1[[Cantidad]:[Total]];SI(I2<>"Fecha";INDIRECTO("Table1["&I2&"]")=J2

En caso contrario, aplicamos la expresión que hemos realizado antes en una celda, y, la igualamos al valor de J2.

=FILTRAR(Table1[[Cantidad]:[Total]];SI(I2<>"Fecha";INDIRECTO("Table1["&I2&"]")=J2;AÑO(INDIRECTO("Table1["&I2&"]"))=J2))

Aceptamos.

Vemos como ahora, el modelo ha sido filtrado por el año seleccionado.

Si elegimos una provincia, por ejemplo, vemos como se filtra correctamente.

Bien, hemos hecho que nos devuelva tres columnas, que son cantidad, precio, y, total, pues, ahora con la función EXPANDIR, que será aplicada a la función FILTRAR, le diré que me devuelva 4 columnas, por lo que he de ignorar el argumento filas.

=EXPANDIR(FILTRAR(Table1[[Cantidad]:[Total]];SI(I2<>"Fecha";INDIRECTO("Table1["&I2&"]")=J2;AÑO(INDIRECTO("Table1["&I2&"]"))=J2));;4)

Vemos que nos devuelve las tres columnas mas una, con un error, porque, de momento, no la hemos rellenado.

Si no queremos ver el error, la función EXPANDIR, tiene un ultimo argumento que es pad_with, es decir, lo que queremos que aparezca si devuelve un error, en este caso, voy a poner un texto en blanco.

=EXPANDIR(FILTRAR(Table1[[Cantidad]:[Total]];SI(I2<>"Fecha";INDIRECTO("Table1["&I2&"]")=J2;AÑO(INDIRECTO("Table1["&I2&"]"))=J2));;4;"")

Al encabezado que tenemos en la celda I4, voy a aplicar también la función EXPANDIR, igual que antes, añado 4 columnas.

=EXPANDIR(Table13[[#Encabezados];[Cantidad]:[Total]];;4)

Tenemos el encabezado más una celda más con un error.

=EXPANDIR(Table13[[#Encabezados];[Cantidad]:[Total]];;4)

Pues, en el argumento pad_with, voy a señalar la celda I2, para que ponga el encabezado seleccionado.

=EXPANDIR(Table1[[#Encabezados];[Cantidad]:[Total]];;4;I2)

En la función FILTRAR anterior, vamos a cambiar el argumento pad_with, por el valor de la celda J2, para que aparezca por lo que estamos filtrando.

=EXPANDIR(FILTRAR(Table1[[Cantidad]:[Total]];SI(I2<>"Fecha";INDIRECTO("Table1["&I2&"]")=J2;AÑO(INDIRECTO("Table1["&I2&"]"))=J2));;4;J2)

Ahora, en la columna M, queremos que aparezca los valores del siguiente encabezado seleccionado, es decir, si elijo fecha, el siguiente encabezado es provincia, pero si elegimos producto, que es el ultimo encabezado, debe de elegir el primer encabezado.

En la celda M5, voy a usar el condicional SI, para preguntar que si el valor de la celda I2, es igual a Producto.

=SI(I2="Producto";

Que ponga el valor 1.

=SI(I2="Producto";1;

En caso contrario, uso la función COINCIDIR, para encontrar el valor de la celda I2, en los encabezados desde fecha a producto, con una coincidencia exacta.

=SI(I2="Producto";1;COINCIDIR(I2;Table1[[#Encabezados];[Fecha]:[Producto]];0)

Pero, debo de sumar 1, para que me dé el siguiente encabezado.

=SI(I2="Producto";1;COINCIDIR(I2;Table1[[#Encabezados];[Fecha]:[Producto]];0)+1)

Aceptamos, y, tenemos la posición del siguiente encabezado seleccionado.

Ahora, con la función INDICE, voy a traerme la columna devuelta por la expresión anterior del modelo.

Como me tengo que traer una columna, voy a omitir el argumento filas.

=INDICE(Table13;;SI(I2="Producto";1;COINCIDIR(I2;Table13[[#Encabezados];[Fecha]:[Producto]];0)+1))

Ahora, tengo que filtrar la función INDICE, por las mismas condiciones que hemos usado anteriormente en la función FILTRAR.

=FILTRAR(INDICE(Table1;;SI(I2="Producto";1;COINCIDIR(I2;Table1[[#Encabezados];[Fecha]:[Producto]];0)+1));SI(I2<>"Fecha";INDIRECTO("Table1[" & I2 & "]")=J2;AÑO(INDIRECTO("Table1[" & I2 & "]"))=J2))

En este caso, he elegido fecha, pues me ha devuelto la provincia correspondiente a cada fila.

Vuelvo a la función FILTRAR en la celda I5, y, fijo las referencias que se encuentran dentro del condicional SI.

=EXPANDIR(FILTRAR(Table1[[Cantidad]:[Total]];SI($I$2<>"Fecha";INDIRECTO("Table1["&$I$2&"]")=$J$2;AÑO(INDIRECTO("Table1["&$I$2&"]"))=$J$2));;4;J2)