Obecný vzorec
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
souhrn
Chcete-li vyhledat hodnoty pomocí INDEX a MATCH, pomocí více kritérií, můžete použít maticový vzorec. V zobrazeném příkladu je vzorec v H8:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Poznámka: Toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter, s výjimkou Excel 365.
Vysvětlení
Toto je pokročilejší vzorec. Základní informace najdete v části Jak používat INDEX a MATCH.
Normálně je vzorec INDEX MATCH konfigurován s MATCH sadou tak, aby prohledával rozsah jednoho sloupce a poskytoval shodu na základě daných kritérií. Bez zřetězení hodnot ve sloupci pomocníka nebo ve vzorci samotném neexistuje způsob, jak zadat více než jedno kritérium.
Tento vzorec funguje kolem tohoto omezení pomocí logické logiky k vytvoření řady jedniček a nul, které představují řádky odpovídající všem 3 kritériím, a poté pomocí MATCH k porovnání první nalezené 1. Dočasné pole jednotek a nul je generováno pomocí tohoto fragmentu:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Zde porovnáváme položku v H5 se všemi položkami, velikost v H6 proti všem velikostem a barvu v H7 proti všem barvám. Počáteční výsledek jsou tři pole TRUE / FALSE výsledků, jako je tato:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tip: K zobrazení těchto výsledků použijte klávesu F9. Stačí vybrat výraz na řádku vzorců a stisknout klávesu F9.
Matematická operace (násobení) transformuje hodnoty TRUE FALSE na 1 s a 0 s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Po násobení máme jediné pole, jako je toto:
(0;0;1;0;0;0;0)
který se přivádí do funkce MATCH jako vyhledávací pole s vyhledávací hodnotou 1:
MATCH(1,(0;0;1;0;0;0;0))
V tomto okamžiku je vzorec standardní vzorec INDEX MATCH. Funkce MATCH vrací 3 na INDEX:
=INDEX(E5:E11,3)
a INDEX vrací konečný výsledek 17,00 $.
Vizualizace pole
Pole vysvětlená výše může být obtížné vizualizovat. Obrázek níže ukazuje základní myšlenku. Sloupce B, C a D odpovídají datům v příkladu. Sloupec F je vytvořen vynásobením tří sloupců dohromady. Je to pole předané MATCH.
Verze bez pole
K tomuto vzorci je možné přidat další INDEX, aby se zabránilo nutnosti zadávat jako maticový vzorec s klávesami control + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Funkce INDEX dokáže nativně zpracovat pole, takže druhý INDEX je přidán pouze k „zachycení“ pole vytvořeného pomocí logické logické operace a vrácení stejného pole opět MATCH. K tomu je INDEX nakonfigurován s nulovými řádky a jedním sloupcem. Trik nulové řady způsobí, že INDEX vrátí sloupec 1 z pole (což je stejně jeden sloupec).
Proč byste chtěli verzi bez pole? Někdy lidé zapomenou zadat vzorec pole pomocí kláves Control + Shift + Enter a vzorec vrátí nesprávný výsledek. Non-array vzorec je tedy více „neprůstřelný“. Avšak kompromis je složitější vzorec.
Poznámka: V aplikaci Excel 365 není nutné zadávat maticové vzorce zvláštním způsobem.
