Vamos a ver un ejemplo, con el modelo de Contoso, en la hoja ventas.
¿Qué queremos?
Si nos fijamos en el modelo, tenemos diversas columnas, la primera de ellas, es IDTienda.
Queremos seleccionar un IDTienda, y, que nos devuelva el valor del resto de encabezados, es decir, desde IDProducto hasta Total.
Realmente, no es lo que queremos, lo que queremos es que seleccionemos un encabezado, y, nos devuelva las coincidencias, desde ese encabezado hasta el final.
Vamos a empezar, creando una lista única de IDTiendas, lo vamos a hacer, por ejemplo, en la celda Z2, por lo que escribimos el signo igual, seguido de la función UNICOS, abrimos un paréntesis, seleccionamos la columna IDTienda, y, cerramos paréntesis.
=UNICOS(TVentas[IDTienda])
Por último, ordenamos estos valores, con la función ORDENAR, y, los argumentos predeterminados.
=ORDENAR(UNICOS(TVentas[IDTienda]))
Vamos a crear una validación de datos, para poder seleccionar un IDTienda, lo haremos en la celda Q2, para ello, teniendo seleccionada la celda, vamos a la pestaña datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.
Se abre la ventana de validación de datos, donde desplegamos permitir, y, seleccionamos lista.
En la ventana, origen, escribimos la celda Z2, donde comienza el primer ID, seguido del operador de rango derramado (#), con esto conseguimos, que si se añaden, o, eliminan registros, la lista siempre este actualizada.
Aceptamos, y, seleccionamos un ID.
Pues, empecemos.
El modelo esta en formato de tabla, y, esta se llama TVentas, la T, es para hacer referencia que es una tabla.
Lo primero que vamos a hacer, es buscar el IDTienda, que hemos seleccionado, y, que nos devuelva la segunda columna.
Para ello, escribimos el signo igual, ponemos la función BUSCARV, y, abrimos un paréntesis.
=BUSCARV(Q2;
Como argumento valor buscado, es la celda P2, donde tenemos la validación.
=BUSCARV(Q2;
Como argumento matriz_tabla, pues, ponemos la tabla TVentas.
=BUSCARV(Q2;TVentas;
Indicamos que nos devuelve la segunda columna, y, en tipo de combinación, seleccionamos exacta.
Cerramos paréntesis, y, aceptamos.
=BUSCARV(Q2;TVentas;2;FALSO)
Obtenemos el IDProducto, correspondiente al IDTienda, que hemos seleccionado.
Y, ¿si quiero que me devuelva la columna 2, y, la columna 3?
Entonces, deberíamos de hacer uso de una constante de matriz, la cual debemos de poner los valores entre llaves, y, nos crea una matriz desbordada, con los valores que le indiquemos.
Por ejemplo, si en una celda escribo, ={1;2;3}, obtengo una matriz desbordada, en vertical, con los números 1, 2, y, 3.
Si en una celda escribo, ={1\2\3}, obtengo una matriz desbordada, en horizontal, con los números 1, 2, y, 3.
Pues, vamos a modificar el argumento, numero de columna, de la función BUSCARV, y, ponemos la siguiente constante de matriz, {2;3}, es decir, que nos devuelva las columnas 2 y 3, en vertical.
=BUSCARV(Q2;TVentas;{2;3};FALSO)
Aceptamos, y, vemos como resultado, una matriz desbordada, en vertical, con las columnas 2 y 3.
Si quisiera que me devolviera todas las columnas, pues, dentro de la constante de matriz, debería de poner desde el numero 2, hasta el numero 14, que es el ultimo encabezado, lo cual seria una “tarea tediosa”, además, de no ser dinámico, porque si aumentan, o, disminuyen los encabezados, no se actualizaría, pero el caso, es que quiero que me devuelva todos los encabezados.
¿Cómo lo hacemos?
Tenemos en Excel, una función matricial, llamada SECUENCIA, que nos genera una matriz desbordada, con el numero de filas, y, de columnas, que le indiquemos.
Si en una celda, escribo, =SECUENCIA(5;2), obtengo una matriz desbordada, de cinco filas, y, dos columnas.
A ver, si la podemos usar como argumento, numero de columna, de la función BUSCARV.
Voy a cambiar el argumento, numero de columna de la función BUSCARV, por SECUENCIA(14), donde solo uso el argumento filas, para que me genere una matriz en vertical, empezando desde el numero 1, hasta el número 14.
=BUSCARV(Q2;TVentas;SECUENCIA(14);FALSO)
Aceptamos, y, vemos como nos ha rescatado todas las columnas.
Pero ¿Qué ocurre si se eliminan, o, añaden encabezados?
Pues, que siempre nos dará hasta el encabezado numero 14, independientemente, del numero de encabezados que haya.
Esto quiere decir, que no esta bien, no podemos dejarlo así, sin tener en cuenta este problema.
Tenemos una función llamada CONTARA, que nos cuenta el número de celdas alfanuméricas que hay, dentro de un rango.
Voy a usar la función CONTARA, para contar el número de encabezados.
=CONTARA(TVentas[# Encabezados])
Vemos que nos devuelve 14, el numero de encabezados que hay, hasta el momento, ya sabemos que al estar en formato de tabla, si añadimos, o, quitamos registros, se actualizarán de forma automática.
Como argumento filas, de la función SECUENCIA, hemos puesto 14, pues vamos a cambiarlo, por la función CONTARA.
=BUSCARV(Q2;TVentas;SECUENCIA(CONTARA(TVentas[# Encabezados]));FALSO)
Vemos que obtenemos los mismos resultados, pero ahora, si es dinámico, porque siempre va a contar el numero de encabezados que hay.
Lo siguiente que quiero, es no ver todos los encabezados, sino, elegir un encabezado, y, que me devuelva desde ese encabezado hasta el final.
Vamos a crear otra validación de datos, como sabemos, para elegir un encabezado.
Seleccionamos un encabezado.
Entonces, en este caso, quiero que me busque el IDTienda 5, y, que me devuelva a partir de IDCliente, encabezado incluido.
Tenemos otra función llamada COINCIDIR, que nos devuelve la posición de un elemento dentro de un rango, pues, voy a usar la función COINCIDIR, para que me devuelva la posición del encabezado seleccionado, con una coincidencia exacta.
=COINCIDIR(R2;TVentas[# Encabezados];0)
Nos devuelve la posición 5, quiere decir, que debe de devolver desde el encabezado numero 5, hasta el último encabezado.
Bien, se donde empieza, pero teniendo en cuenta que los encabezados, pueden cambiar, ¿Cómo lo decimos hasta donde debe de llegar?
la función CONTARA, nos da un recuento de las celdas alfanuméricas dentro de un rango, en este caso nos devuelve 14.
Bien, se donde empieza, pero teniendo en cuenta que los encabezados, pueden cambiar, ¿Cómo lo decimos hasta donde debe de llegar?
la función CONTARA, nos da un recuento de las celdas alfanuméricas dentro de un rango, en este caso nos devuelve 14.
Si restamos, el resultado de CONTARA, menos el resultado de COINCIDIR nos devuelve 9.
Si contamos, desde el encabezado siguiente al que hemos elegido hasta el ultimo encabezado, veremos que es 9.
Podíamos decir, que ya tengo el argumento numero de columna, de la función BUSCARV, pero queremos, que el encabezado que seleccione este incluido, por lo que tenemos que sumar a la resta anterior.
=(T2-S2)+1
Tenemos que modificar la función SECUENCIA, el primer argumento es filas, es decir, de cuantas filas va a estas compuesta la matriz, pues, eso lo obtenemos con la función resta de la función CONTARA, menos COINCIDIR, sumándole 1.
= SECUENCIA((CONTARA(TVentas[# Encabezados])-COINCIDIR(R2;TVentas[# Encabezados];0))+1
El siguiente argumento es columnas, pues, ponemos 1.
El siguiente argumento es inicio, es decir, donde debe de empezar a contar, pues eso, ya sabemos que lo decide la función COINCIDIR.
=SECUENCIA((CONTARA(TVentas[# Encabezados])-COINCIDIR(R2;TVentas[# Encabezados];0))+1;1;COINCIDIR(R2;TVentas[# Encabezados];0))
Pues, esta es la función, que debemos de poner, como argumento numero de columna, de la función BUSCARV, quedaría:
=BUSCARV(Q2;TVentas;SECUENCIA((CONTARA(TVentas[# Encabezados])-COINCIDIR(R2;TVentas[# Encabezados];0))+1;1;COINCIDIR(R2;TVentas[# Encabezados];0);1);FALSO)
Probemos, elijo IDTienda 11, y, el encabezado precio neto, vemos que nos devuelve desde precio neto hasta el último encabezado.
Lo siguiente, seria poner al lado del valor rescatado, el nombre del encabezado, cosa que nos va a resultar fácil, porque ya tenemos el numero de columna, que nos lo devuelve la función SECUENCIA, pues, voy a usar la función INDICE, donde como argumento matriz, voy a seleccionar los encabezados, como argumento número de fila, ponemos 1, y, como argumento numero de columna, ponemos la función SECUENCIA, que acabamos de usar, por supuesto, seria dinámico.
=INDICE(TVentas[[# Encabezados];[IDTienda]:[Total]];1;SECUENCIA((CONTARA(TVentas[# Encabezados])-COINCIDIR(R2;TVentas[# Encabezados];0))+1;1;COINCIDIR(R2;TVentas[# Encabezados];0);1))
Ya tenemos también, los encabezados.
Con esto, damos por finalizado este ejemplo.
Comments