Splošna formula
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Povzetek
Za iskanje vrednosti z ujemanjem v več stolpcih lahko uporabite matrično formulo, ki temelji na MMULT, TRANSPOSE, COLUMN in INDEX. V prikazanem primeru je formula v H4:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
pri čemer je "imena" imenovano območje C4: E7, "groups" pa imenovano območje B4: B7. Formula vrne skupino, ki ji pripada vsako ime.
Opomba: to je formula matrike in jo je treba vnesti s tipko shift shift enter.
Pojasnilo
Logična merila, uporabljena v tej formuli, delujejo od znotraj navzven:
--(names=G4)
kjer je imena imenovani obseg C4: E7. To ustvari rezultat TRUE / FALSE za vsako vrednost v podatkih, dvojni negativ pa prisili vrednosti TRUE FALSE na 1 in 0, da dobi matriko, kot je ta:
(0,0,0;1,0,0;0,0,0;0,0,0)
Ta matrika je 4 vrstice s 3 stolpci, kar ustreza strukturi "imen".
S tem izrazom se ustvari drugo polje:
TRANSPOSE(COLUMN(names)^0))
Funkcija COLUMN se uporablja za ustvarjanje številskega polja s 3 stolpci in 1 vrstico, TRANSPOSE pa to matriko pretvori v 1 stolpec in 3 vrstice. Dviganje na stopnjo nič preprosto pretvori vsa števila v matriki v 1. Funkcija MMULT se nato uporablja za množenje matric:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
in posledično gre v funkcijo MATCH kot matrika, z 1 kot vrednost iskanja:
MATCH(1,(0;1;0;0),0)
Funkcija MATCH vrne položaj prvega ujemanja, ki ustreza vrstici prve ujemajoče se vrstice, ki ustreza izpolnjenim pogojem. To je vir v INDEX kot številka vrstice, z matriko pa imenovane skupine ":"
=INDEX(groups,2)
Končno INDEX vrne "Bear", ki spada v skupino Adam.
Dobesedno vsebuje merila
Če želite namesto natančnega ujemanja preveriti določene besedilne vrednosti, lahko skupaj uporabite funkciji ISNUMBER in SEARCH. Na primer za ujemanje celic, ki vsebujejo "jabolko", lahko uporabite:
=ISNUMBER(SEARCH("apple",data))
Ta formula je razložena tukaj.
