16 ago 20225 Min. de lectura

Recuperar encabezados de tablas seleccionadas

En el siguiente ejemplo, vamos a trabajar con el modelo CONTOSO, donde tenemos varias tablas, que son:

· Ventas

· Tienda

· Promoción

· Cliente

· SubCategoriaProducto

· CategoriaProducto

· Producto

· Canal

En una hoja nueva, queremos seleccionar un producto, y, elegir que encabezados de la tabla ventas, queremos que nos devuelva.

Todos los modelos están en formato de tabla, y, cada tabla tiene el nombre de la hoja.

Vamos a ir haciéndolo, y, lo iremos comprendiendo.

En una hoja nueva, voy a traerme los encabezados de la tabla ventas.

=Ventas[# Encabezados]

De todos los encabezados, para este ejemplo, voy a trabajar con los encabezados IDTienda, IDProducto, IDPromocion; IDCanal, e, IDCliente.

En estas tablas, el campo a recuperar se llama Tienda, Producto, Promocion, Canal, y, Cliente.

Si nos fijamos en los ID´s, si quito la palabra ID, me queda el nombre de la columna, entonces, si en la siguiente fila a la anterior, uso la función HALLAR, que no distingue entre mayúsculas, y, minúsculas, para encontrar la posición de la letra D en cada encabezado, lo haremos de forma matricial.

=HALLAR("d";C1#)

Obtengo una matriz desbordada con la posición de la palabra D, donde se encuentre, y, error donde no este.

Usamos la función SI.ERROR, para que ponga un texto en blanco, en caso de error.

=SI.ERROR(HALLAR("d";C1#);"")

Si a la longitud de la cadena, le resto hasta la letra D, me quedan los caracteres a extraer, pero decir, que, a la expresión anterior, hay que sumarle 1, que es donde debe de empezar a extraer, la función quedaría:

=EXTRAE(C1#;HALLAR("d";C1#)+1;LARGO(C1#)-HALLAR("d";C1#))

Obtenemos el nombre de la columna de cada tabla, la que necesitamos, y, error donde no hay coincidencia, pero no debemos de preocuparnos, porque vamos a trabajar con los primeros cinco encabezados.

Lo siguiente va a ser unir C2 concatenado con un corchete de apertura, concatenado con C1, y, concatenado con un paréntesis de cierre, de esta forma, tendremos el nombre de la tabla y la columna con la que trabajar.

=C2 & "[" &C1 & "]"

Arrastramos hacia la derecha.

El campo que, una vez realizada la búsqueda, se llama igual que la tabla, es decir, de la tabla tienda, vamos a recuperar la columna tienda, igual, para las demás hojas.

Con la función SUSTITUIR, de la celda C3, voy a sustituir la palabra ID por nada, y, me quedara el nombre de la tabla, y, la columna.

=SUSTITUIR(C3;"ID";"")

Arrastramos hacia la derecha.

Quiere decir, que tengo los ID para trabajar en la columna ventas, los nombres de las tablas, el nombre de la tabla, y, la columna a buscar, y, el nombre de la tabla, y, la columna a devolver.

Ahora, vamos a crear una lista desplegable, para elegir un producto, 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 la hoja de productos, y, seleccionaos los productos.

Aceptamos.

Elegimos un producto.

Voy a añadir una línea con ceros y unos, debajo de cada línea, uno es el encabezado que quiero ver, y, cero el que no quiero ver.

Estos ceros, y, unos hacer referencia a los encabezados que vamos a recuperar, por lo que con la función FILTRAR, voy a filtrar los nombres de tablas (C2#), cuyo valor sea 1, solo tengo puesto el valor 1, en tienda, y, canal, el resto son ceros.

=FILTRAR(C2#;C5:P5)

Lo siguiente que voy a hacer, es encontrar el ID de producto, del producto seleccionado, para ello, voy a usar la función BUSCARX, donde como primer argumento, valor buscado, es el producto seleccionado, como argumento matriz de búsqueda, es la columna B de la hoja de productos, y, como argumento matriz devuelta, es la columna A de la hoja de productos.

=BUSCARX(C8;Producto!B:B;Producto!A:A)

Lo siguiente que voy a hacer es filtrar la tabla ventas, cuyo ID de producto sea igual al devuelto por la función BUSCARX.

=FILTRAR(Ventas;Ventas[IDProducto]=BUSCARX(Hoja3!C8;Producto!B:B;Producto!A:A))

Obtengo una matriz desbordada cuyo id producto es igual al producto seleccionado.

Pero, solo deben de aparecer los datos de los encabezados que hemos seleccionado, por lo que de nuevo, filtro esta función, y, como argumento include, uso de nuevo el contador.

=FILTRAR(FILTRAR(Ventas;Ventas[IDProducto]=BUSCARX(Hoja3!C8;Producto!B:B;Producto!A:A));C5:P5)

Ya tenemos solo los datos de los encabezados seleccionados.

Lo siguiente es recuperar los datos de los ID que tenemos en pantalla.

Como tenemos tienda, y, canal, tengo que trabajar con la columna IDTienda de la tabla tienda, y, lo mismo para canal, entonces, si uso la función BUSCARX, donde como argumento valor buscado es el valor de la celda B10, que es el primer encabezado a recuperar, como argumento matriz de búsqueda, señalamos desde C2 a G2, y, como argumento matriz devuelta, selecciono desde C4 a G4.

=BUSCARX(B10;C2:G2;C4:G4)

Obtengo el nombre de la tabla, y, una de las columnas con trabajar, por supuesto, es dinámico, en este caso, obtengo:

Para obtener los datos de dicho encabezado, voy a usar la función INDIRECTO.

=INDIRECTO(BUSCARX(B10;C2:G2;C4:G4))

Obtengo una matriz desbordada con los valores de la columna tienda de la tabla tienda.

Ahora, con otro BUSCARX, vuelvo a buscar el valor de la celda B10, tienda en este caso, en la matriz C2:G2, y, que me devuelva la matriz C3:G3.

=BUSCARX(B10;C2:G2;C3:G3)

En este caso, obtengo el encabezado IDTienda, la otra columna con la que debemos de trabajar, igual que antes, para recuperar los valores, vuelvo a usar INDIRECTO.

=INDIRECTO(BUSCARX(B10;C2:G2;C3:G3))

Ahora, con la función FILTRAR, voy a recuperar el nombre de la tienda, donde como argumento array, será el primer BUSCARX, es decir, el nombre de la tienda, y, como argumento include, es el segundo BUSCARX, que lo igualamos con el valor de la celda B11, que es el primer IDTienda.

=FILTRAR(INDIRECTO(BUSCARX(B10;C2:G2;C4:G4));INDIRECTO(BUSCARX(B10;C2:G2;C3:G3))=B11)

Obtengo el nombre de la tienda del primer IDTienda.

Ahora, he de arrastrar hacia abajo, pero ¿cuántas veces he de arrastrar?, como tenemos una matriz desbordada, si uso la función CONTARA, y, como argumento pongo la celda B11, junto con el operador de rango derramado (#), me cuenta las celdas alfanuméricas de toda la matriz.

=CONTARA(B11#)

Para este ejemplo, me devuelve 8, pero no son ocho veces lo que he de arrastrar, si divido 8 entre el numero de columnas, si me dará las filas de cada columna de la matriz, para ello, voy a usar la función COLUMNAS, quedando la sintaxis, de la siguiente manera:

=CONTARA(B11#)/COLUMNAS(B11#)

Ahora, me devuelve 4, que son las filas de cada columna dentro de la matriz.

Ahora, voy a usar la función SECUENCIA, para que genere una matriz de cuatro números, en este caso, porque el argumento filas, va a ser la función anterior, empezando desde el número 1.

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

Tenemos una matriz desbordada, en este caso, desde el numero 1 al número 4.

Pues son cuatro veces lo que tengo que arrastrar, pero antes de arrastrar he de fijar algunas referencias, la celda B10, solo he de fijar la fila, para que cuando copiemos hacia abajo, no se actualice, pero cuando copiemos hacia la derecha, la columna si se debe de actualizar, ponemos en absoluta, los argumentos matriz de búsqueda, y, matriz devuelta de los dos BUSCARX, y, la referencia B11, la dejamos en relativa, para que se actualice al copiar hacia abajo, y, cuando copiemos hacia la derecha, se actualice la columna.

=FILTRAR(INDIRECTO(BUSCARX(B$10;$C$2:$G$2;$C$4:$G$4));INDIRECTO(BUSCARX(B$10;$C$2:$G$2;$C$3:$G$3))=B11)

Pues, arrastramos cuatro veces hacia abajo.

Arrastro cuatro veces hacia la derecha, porque son los encabezados máximos, que voy a recuperar, pero, obtenemos un error, donde no hay encabezado a recuperar.