Explicación
Para realizar una búsqueda de criterios múltiples y transponer los resultados en una tabla, puede usar una fórmula de matriz basada en INDICE y COINCIDIR. En el ejemplo que se muestra, la fórmula en G5 es:
Tenga en cuenta que esta fórmula es una fórmula de matriz y debe ingresarse con control + shift + enter.
Esta fórmula también utiliza tres rangos con nombre: ubicación = B5: B13, cantidad = D5: D13, fecha = C5: C13
Cómo funciona esta fórmula
El núcleo de esta fórmula es INDICE, que está recuperando un valor de la "cantidad" del rango nombrado (B5: B13):
=INDICE(amount,row_num)
donde row_num se resuelve con la función COINCIDIR y alguna lógica booleana:
COINCIDIR(1,($F5=location)*(G$4=date),0)
En este fragmento, la ubicación en F5 se compara con todas las ubicaciones, y la fecha en G4 se compara con todas las fechas. El resultado en cada caso es una matriz de valores VERDADERO y FALSO. Cuando estas matrices se multiplican juntas, la operación matemática obliga a los valores VERDADERO y FALSO a uno y ceros, de modo que la matriz de búsqueda que va a COINCIDIR se ve así:
{1;0;0;0;0;0;0;0;0}
COINCIDIR está configurado para coincidir con 1 como una coincidencia exacta, y devuelve la posición a INDICE como un número de fila. El número 1 funciona para el valor de búsqueda porque la matriz ahora contiene solo 1 y 0, como se muestra arriba.
F5 y G4 se ingresan como referencias mixtas para que la fórmula se pueda copiar a través de la tabla sin modificación.
Transponer con pasta especial
Si solo necesita transponer una tabla una vez, no olvide que puede usar pegado especial.
0 comentarios:
Publicar un comentario