En el siguiente ejemplo, tenemos 10 hojas con provincias, queremos crear una lista de provincias, en una hoja nueva, el problema que se presenta es que, aunque, la estructura de todas las hojas son las mismas, la columna de provincias en la hoja de Cádiz se encuentra en otra posición, por ejemplo, en la hoja de Albacete, la columna de provincia se encuentra en la columna 2.
En la hoja de Cádiz, la columna provincia, se encuentra en la columna 3.
En las demás hojas, la columna provincia se encuentra en la columna 2.
Para este ejemplo, debemos de usar referencias 3D.
Si en una celda, escribo el signo igual, señalo la hoja de Albacete, dejo pulsado la tecla SHIFT, o, MAYUSC, y, señalo la ultima hoja, es decir, Cáceres, ahora, señalo la columna de provincia, desde B2 a B100, y, acepto.
=Albacete:Caceres!B2:B100
Me devuelve un error, no es capaz de devolverme la columna B de todas las hojas, pero, podemos usar la función VSTACK, APILARV en español, y, si nos dará los valores de la columna B de todas las hojas, como sigue:
=APILARV(Albacete:Caceres!B2:B100)
Ahora, con la función UNICOS, nos quedamos con los valores únicos.
=UNICOS(APILARV(Albacete:Caceres!B2:B100))
Tenemos una matriz de valores únicos, pero podemos ver que tenemos provincias, y, productos, porque la columna B en la hoja de Cádiz, es la de productos.
Vamos a intentar solventar este problema.
Voy a apilar en horizontal con APILARH la fila 1, y, 2, desde la columna A hasta la columna G, de todas, las hojas.
=APILARH(Albacete:Caceres!A1:G2)
Tenemos una matriz desbordada con las dos primeras filas de todas las hojas.
Ahora, voy a filtrar la matriz desbordada anterior, siempre que la primera fila sea igual a provincia.
=FILTRAR(APILARH(Albacete:Caceres!A1:G2);APILARH(Albacete:Caceres!A1:G1)="Provincia")
Tenemos una matriz desbordada en horizontal, pero solo los encabezados de columnas.
Ahora, con la función INDICE, le voy a decir que me devuelva la fila 2.
=INDICE(FILTRAR(APILARH(Albacete:Caceres!A1:G2);APILARH(Albacete:Caceres!A1:G1)="Provincia");2)
Ya tenemos las provincias, aunque estén colocadas en otras columnas.
Si la queremos en vertical, pues, transponemos.
=TRANSPONER(INDICE(FILTRAR(APILARH(Albacete:Caceres!A1:G2);APILARH(Albacete:Caceres!A1:G1)="Provincia");2))
Por último, con la función APILARV, le voy a añadir el encabezado provincias, quiere decie que será dinámico.
=APILARV("Provincias";TRANSPONER(INDICE(FILTRAR(APILARH(Albacete:Caceres!A1:G2);APILARH(Albacete:Caceres!A1:G1)="Provincia");2)))
Comments