Hľadať hodnotu pomocou jedného jedinečného kľúča v tabuľke je jednoduché. Jednoducho môžeme použiť funkciu VLOOKUP. Keď však vo svojich údajoch nemáte tento jedinečný stĺpec a potrebujete vyhľadať vo viacerých stĺpcoch, aby ste zhodovali hodnotu, funkcia VLOOKUP nepomáha.
Na vyhľadanie hodnoty v tabuľke s viacerými kritériami teda použijeme vzorec INDEX-MATCH-INDEX.
Generický vzorec na vyhľadávanie viacerých kritérií
= INDEX (rozsah_hľadania, MATCH (1, INDEX ((kritérium1 = rozsah1)*(kritérium2 = rozsah2)*(kritériumN = rozsahN), 0,1), 0))
rozsah_hľadania: Je to rozsah, z ktorého chcete získať hodnotu.
Kritériá1, Kritériá2, Kritériá N: Toto sú kritériá, ktoré chcete priradiť v rozsahu 1, rozsahu 2 a rozsahu N. Môžete mať až 270 kritérií - dvojice rozsahov.
Rozsah 1, rozsah 2, rozsah N: Toto sú rozsahy, v ktorých budete zodpovedať svojim príslušným kritériám.
Ako to bude fungovať? Pozrime sa…
INDEX a MATCH s príkladom viacerých kritérií
Tu mám tabuľku údajov. Chcem vytiahnuť meno zákazníka pomocou dátumu rezervácie, staviteľa a oblasti. Takže tu mám tri kritériá a jeden rozsah vyhľadávania.
Napíšte tento vzorec do bunky I4 a stlačte kláves Enter.
= INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0))
Ako to funguje:
Už vieme, ako fungujú funkcie INDEX a MATCH v programe EXCEL, takže to tu nebudem vysvetľovať. Povieme si o triku, ktorý sme tu použili.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Hlavná časť je táto. Každá časť tohto príkazu vracia pole true false.
Keď sú booleovské hodnoty vynásobené, vrátia pole 0 a 1. Násobenie funguje ako operátor AND. Hense, keď sú všetky hodnoty pravdivé, vráti iba 1 ďalšiu 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Celkom sa to vráti
{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}
Čo sa preloží 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): Funkcia INDEX vráti rovnaké pole ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0} 0)), aby funkcia MATCH fungovala ako vyhľadávacie pole.
ZÁPAS(1,INDEX((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Funkcia MATCH vyhľadá 1 v poli {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. A vráti indexové číslo prvého 1 nájdeného v poli. Čo je tu 8.
INDEX(E2: E16,ZÁPAS(1,INDEX((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Nakoniec INDEX vráti hodnotu z daného rozsahu (E2: E16) pri zistení index (8).
Jednoduché ????. Ospravedlňujeme sa, nemôže to byť jednoduchšie.
Array riešenie
Ak následne stlačíte CTRL + SHIFT + ENTER, môžete odstrániť vnútornú funkciu INDEX. Stačí napísať tento vzorec a stlačiť CTRL + SHIFT + ENTER.
= INDEX (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0))
Generický vzorec poľa na vyhľadávanie viacerých kritérií
= INDEX (rozsah_hľadania, MATCH (1, (kritérium1 = rozsah1)*(kritérium2 = rozsah2)*(kritériumN = rozsahN), 0))
Vzorec funguje rovnako ako vyššie uvedené vysvetlenie.
Snažil som sa to vysvetliť čo najjednoduchšie. Ak mi to však nebolo dosť jasné, dajte mi vedieť v sekcii komentárov nižšie. Mimochodom, pri riadení auta nemusíte vedieť, ako funguje motor. Musíte len vedieť, ako s ním jazdiť. A ty to veľmi dobre vieš.
Ako vyhľadávať 5 najlepších hodnôt pomocou duplicitných hodnôt pomocou INDEX-MATCH v programe Excel
Ako VYHĽADAŤ viac hodnôt v programe Excel
Ako VLOOKUP s Dynamic Col Index v programe Excel
Ako používať VLOOKUP z dvoch alebo viacerých vyhľadávacích tabuliek v programe Excel
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity
Ako používať funkciu VLOOKUP v programe Excel
Ako používať funkciu COUNTIF v programe Excel
Ako používať funkciu SUMIF v programe Excel