Všeobecný vzorec
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Zhrnutie
Na vyhľadanie hodnoty pomocou zhody vo viacerých stĺpcoch môžete použiť maticový vzorec založený na MMULT, TRANSPOSE, COLUMN a INDEX. V zobrazenom príklade je vzorec v H4:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
kde „names“ je pomenovaný rozsah C4: E7 a „groups“ je pomenovaný rozsah B4: B7. Vzorec vráti skupinu, do ktorej každé meno patrí.
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesovej skratky Enter.
Vysvetlenie
Logické kritériá použité v tomto vzorci sú zvnútra:
--(names=G4)
kde názvy sú pomenovaný rozsah C4: E7. Toto vygeneruje výsledok TRUE / FALSE pre každú hodnotu v dátach a dvojitý zápor vynúti hodnoty TRUE FALSE na 1 a 0, čím sa získa pole podobné tomuto:
(0,0,0;1,0,0;0,0,0;0,0,0)
Toto pole má 4 riadky a 3 stĺpce, ktoré zodpovedajú štruktúre „mien“.
Vytvorí sa druhé pole s týmto výrazom:
TRANSPOSE(COLUMN(names)^0))
Funkcia COLUMN sa používa na vytvorenie číselného poľa s 3 stĺpcami a 1 riadkom a funkcia TRANSPOSE toto pole prevedie na 1 stĺpec a 3 riadky. Pri zvýšení na nulu sa jednoducho prevedú všetky čísla v poli na 1. Funkcia MMULT sa potom použije na vykonanie násobenia matíc:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
a výsledok ide do funkcie MATCH ako pole s 1 ako vyhľadávacou hodnotou:
MATCH(1,(0;1;0;0),0)
Funkcia MATCH vráti pozíciu prvej zhody, ktorá zodpovedá riadku prvého zodpovedajúceho riadku, ktorý spĺňa zadané kritériá. Toto je vstup do INDEXU ako čísla riadku s pomenovaným rozsahom „groups“ ako poľom:
=INDEX(groups,2)
Nakoniec INDEX vráti „Medveď“, do ktorého patrí Adam.
Doslovný obsahuje pre kritériá
Ak chcete namiesto presnej zhody skontrolovať konkrétne textové hodnoty, môžete použiť funkcie ISNUMBER a SEARCH spoločne. Napríklad na priradenie buniek, ktoré obsahujú „jablko“, môžete použiť:
=ISNUMBER(SEARCH("apple",data))
Tento vzorec je vysvetlený tu.
