Explicación
Para buscar valores con INDICE y COINCIDIR, utilizando múltiples criterios, puede usar una fórmula de matriz. En el ejemplo que se muestra, la fórmula en H8 es:
Nota: esta es una fórmula de matriz, y debe ingresarse con control + shift + enter.
Cómo funciona esta fórmula
Esta es una fórmula más avanzada. Para lo básico, vea Cómo usar INDICE y COINCIDIR.
Normalmente, una fórmula INDICE COINCIDIR está configurada con COINCIDIR configurado para mirar a través de un rango de una columna y proporcionar una coincidencia basada en criterios dados. Sin concatenar valores en una columna auxiliar, o en la fórmula misma, no hay forma de proporcionar más de un criterio.
Esta fórmula soluciona esta limitación al usar la lógica booleana para crear una matriz de unos y ceros para representar filas que coinciden con los 3 criterios, y luego usar COINCIDIR para que coincida con el primer 1 encontrado. La matriz temporal de unos y ceros se genera con este fragmento:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Aquí comparamos el artículo en H5 con todos los artículos, el tamaño en H6 con todos los tamaños y el color en H7 con todos los colores. El resultado inicial es tres matrices de resultados VERDADERO / FALSO como este:
{VERDADERO;VERDADERO;VERDADERO;FALSO;FALSO;FALSO;VERDADERO}*{FALSO;FALSO;VERDADERO;FALSO;FALSO;VERDADERO;FALSO}*{VERDADERO;FALSO;VERDADERO;FALSO;FALSO;FALSO;VERDADERO}
Consejo: use F9 para ver estos resultados. Simplemente seleccione una expresión en la barra de fórmulas y presione F9.
La operación matemática (multiplicación) transforma los valores VERDADERO FALSO en 1s y 0s:
{1;1;1;0;0;0;1}*{0;0;1;0;0;1;0}*{1;0;1;0;0;0;1}
Después de la multiplicación, tenemos una matriz única como esta:
{0;0;1;0;0;0;0}
que se alimenta a la función COINCIDIR como la matriz de búsqueda, con un valor de búsqueda de 1:
COINCIDIR(1,{0;0;1;0;0;0;0})
En este punto, la fórmula es una fórmula estándar de INDICE COINCIDIR. La función COINCIDIR devuelve 3 a INDICE:
=INDICE(E5:E11,3)
e INDICE devuelve un resultado final de $ 17.00.
Visualización de matriz
Las matrices explicadas anteriormente pueden ser difíciles de visualizar. La siguiente imagen muestra la idea básica. Las columnas B, C y D corresponden a los datos del ejemplo. La columna F se crea multiplicando las tres columnas juntas. Es la matriz entregada a COINCIDIR.
Versión sin arreglo
Es posible agregar otro ÍNDICE a esta fórmula, evitando la necesidad de ingresar como una fórmula de matriz con control + shift + enter:
La función INDICE puede manejar matrices de forma nativa, por lo que el segundo INDICE se agrega solo para "capturar" la matriz creada con la operación lógica booleana y devolver la misma matriz nuevamente a COINCIDIR. Para hacer esto, INDICE se configura con cero filas y una columna. El truco de la fila cero hace que INDICE devuelva la columna 1 de la matriz (que de todos modos ya es una columna).
¿Por qué querrías la versión sin matriz? A veces, las personas olvidan ingresar una fórmula de matriz con control + shift + enter, y la fórmula devuelve un resultado incorrecto. Por lo tanto, una fórmula sin matriz es más "a prueba de balas". Sin embargo, la compensación es una fórmula más compleja.
0 comentarios:
Publicar un comentario