Je snadné vyhledat hodnotu pomocí jednoho jedinečného klíče v tabulce. Můžeme jednoduše použít funkci VLOOKUP. Pokud ale ve svých datech nemáte tento jedinečný sloupec a potřebujete vyhledat více sloupců, aby odpovídaly hodnotě, VLOOKUP nepomůže.
Chcete-li tedy vyhledat hodnotu v tabulce s více kritérii, použijeme vzorec INDEX-MATCH-INDEX.
Obecný vzorec pro vyhledávání více kritérií
= INDEX (rozsah vyhledávání, MATCH (1, INDEX ((kritéria1 = rozsah1)*(kritéria2 = rozsah2)*(kritériaN = rozsahN), 0,1), 0))
rozsah vyhledávání: Je to rozsah, ze kterého chcete získat hodnotu.
Kritéria 1, Kritéria 2, Kritéria N: Toto jsou kritéria, která chcete spojit v rozsahu 1, rozsahu 2 a rozsahu N. Můžete mít až 270 kritérií - dvojice rozsahů.
Rozsah1, rozsah2, rozsahN: Toto jsou rozsahy, ve kterých budete odpovídat příslušným kritériím.
Jak to bude fungovat? Uvidíme…
INDEX a MATCH s příkladem více kritérií
Tady mám datovou tabulku. Chci vytáhnout jméno zákazníka pomocí Datum rezervace, Tvůrce a Oblast. Takže tady mám tři kritéria a jeden rozsah vyhledávání.
Tento vzorec napište do buňky I4 a stiskněte Enter.
= INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0))
Jak to funguje:
Už víme, jak fungují funkce INDEX a MATCH v EXCEL, takže to zde nebudu vysvětlovat. Povíme si o triku, který jsme zde použili.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Hlavní část je tato. Každá část tohoto příkazu vrací pole true false.
Když jsou booleovské hodnoty vynásobeny, vrátí pole 0 a 1. Násobení funguje jako operátor AND. Hense, když jsou všechny hodnoty pravdivé, pak vrátí 1 else 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) To se celkem vrátí
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}* {FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}* {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Což se převede do
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
INDEX((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funkce INDEX vrátí stejné pole ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}), aby MATCH fungovala jako vyhledávací pole.
ZÁPAS(1,INDEX((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funkce MATCH vyhledá 1 v poli {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. A vrátí indexové číslo prvního 1 nalezeného v poli. Což je zde 8.
INDEX(E2: E16,ZÁPAS(1,INDEX((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Nakonec INDEX vrátí hodnotu nalezeného rozsahu (E2: E16) index (8).
Jednoduchý????. Omlouváme se, nemohlo to být jednodušší.
Pole řešení
Pokud následně můžete stisknout CTRL + SHIFT + ENTER, můžete odstranit vnitřní funkci INDEX. Stačí napsat tento vzorec a stisknout CTRL + SHIFT + ENTER.
= INDEX (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0))
Obecný vzorec pole pro vyhledávání více kritérií
= INDEX (rozsah vyhledávání, MATCH (1, (kritéria1 = rozsah1)*(kritéria2 = rozsah2)*(kritériaN = rozsahN), 0))
Vzorec funguje stejně jako výše uvedené vysvětlení.
Snažil jsem se to vysvětlit co nejjednodušeji. Pokud mi to ale nebylo dost jasné, dejte mi to vědět v sekci komentáře níže. Mimochodem, k řízení auta nepotřebujete vědět, jak motor funguje. Musíte jen vědět, jak s ním řídit. A ty to moc dobře víš.
Jak vyhledat 5 nejlepších hodnot pomocí duplicitních hodnot pomocí INDEX-MATCH v aplikaci Excel
Jak SVYHLEDAT více hodnot v Excelu
Jak SVYHLEDAT pomocí Dynamic Col Index v Excelu
Jak používat VLOOKUP ze dvou nebo více vyhledávacích tabulek v aplikaci Excel
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity
Jak používat funkci VLOOKUP v Excelu
Jak používat funkci COUNTIF v aplikaci Excel
Jak používat funkci SUMIF v aplikaci Excel