Explicación
Para revertir un CONSULTAV, es decir, para encontrar el valor de búsqueda original usando un resultado de fórmula CONSULTAV, puede usar una fórmula difícil basada en la función ELEGIR, o fórmulas más sencillas basadas en INDICE y COINCIDIR o XLOOKUP como se explica a continuación. En el ejemplo que se muestra, la fórmula en H10 es:
Con esta configuración, CONSULTAV encuentra la opción asociada con un costo de 3000 y devuelve "C".
Nota: este es un tema más avanzado. Si recién está comenzando con CONSULTAV, comience aquí.
Introducción
Una limitación clave de CONSULTAV es que solo puede buscar valores a la derecha. En otras palabras, la columna con valores de búsqueda debe estar a la izquierda de los valores que desea recuperar con CONSULTAV. Como resultado, con la configuración estándar, no hay forma de usar CONSULTAV para "mirar a la izquierda" y revertir la búsqueda original.
Desde el punto de vista de CONSULTAV, podemos visualizar el problema de esta manera:
La solución alternativa que se explica a continuación utiliza la función ELEGIR para reorganizar la tabla dentro de CONSULTAV.
Cómo funciona esta fórmula
Comenzando por el principio, la fórmula en H5 es una fórmula normal de CONSULTAV:
=CONSULTAV(G5,B5:D8,3,0) // returns 3000
Usando G5 como el valor de búsqueda ("C"), y los datos en B5: D8 como la matriz de la tabla, CONSULTAV realiza una búsqueda de valores en la columna B y devuelve el valor correspondiente de la columna 3 (columna D), 3000. Aviso se proporciona cero (0) como último argumento para forzar una coincidencia exacta.
La fórmula en G10 simplemente extrae el resultado de H5:
=H5 // 3000
Para realizar una búsqueda inversa, la fórmula en H10 es:
El bit complicado es la función ELEGIR, que se utiliza para reorganizar la matriz de la tabla de modo que Costo sea la primera columna y Opción sea la última:
ELEGIR({3,2,1},B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
La función ELEGIR está diseñada para seleccionar un valor basado en un índice numérico. En este caso, estamos proporcionando tres valores de índice en una constante de matriz:
{3,2,1} // array constant
En otras palabras, estamos pidiendo la columna 3, luego la columna 2, luego la columna 1. A esto le siguen los tres rangos que representan cada columna de la tabla en el orden en que aparecen en la hoja de trabajo.
Con esta configuración, ELEGIR devuelve las tres columnas en una única matriz 2D como esta:
{1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D"}
Si visualizamos esta matriz como una tabla en la hoja de trabajo, tenemos:
Nota: los encabezados no son parte de la matriz y se muestran aquí solo por claridad.
Efectivamente, hemos intercambiado las columnas 1 y 3. La tabla reorganizada se devuelve directamente a CONSULTAV, que coincide con 3000, y devuelve el valor correspondiente de la columna 3, "C".
Con INDICE y COINCIDIR
La solución anterior funciona bien, pero es difícil de recomendar ya que la mayoría de los usuarios no entenderán cómo funciona la fórmula. Una mejor solución es INDICE y COINCIDIR, usando una fórmula como esta:
Aquí, la función COINCIDIR encuentra el valor 3000 en D5: D8, y devuelve su posición, 3:
COINCIDIR(G10,D5:D8,0) // returns 3
Nota: COINCIDIR se configura para una coincidencia exacta al establecer el último argumento en cero (0).
COINCIDIR devuelve un resultado directamente a INDICE como el número de fila, por lo que la fórmula se convierte en:
=INDICE(B5:B8,3) // returns "C"
e INDICE devuelve el valor de la tercera fila de B5: B8, "C".
Esta fórmula muestra cómo INDICE y COINCIDIR pueden ser más flexibles que CONSULTAV.
Con XLOOKUP
XLOOKUP también proporciona una muy buena solución. La fórmula equivalente es:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Con un valor de búsqueda de G10 (3000), una matriz de búsqueda de D5: D8 (costos) y una matriz de resultados de B5: B8 (opciones), XLOOKUP ubica el 3000 en la matriz de búsqueda y devuelve el elemento correspondiente de la matriz de resultados, "C". Debido a que XLOOKUP realiza una coincidencia exacta de forma predeterminada, no es necesario establecer el modo de coincidencia explícitamente.
0 comentarios:
Publicar un comentario