Všeobecný vzorec
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Zhrnutie
Na vyhľadanie hodnôt pomocou indexov INDEX a MATCH pomocou viacerých kritérií môžete použiť vzorec poľa. V zobrazenom príklade je vzorec v H8:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter, s výnimkou Excel 365.
Vysvetlenie
Toto je pokročilejší vzorec. Základné informácie nájdete v časti Ako používať INDEX a MATCH.
Normálne je vzorec INDEX MATCH nakonfigurovaný s nastavením MATCH tak, aby prehliadal rozsah jedného stĺpca a poskytoval zhodu na základe daných kritérií. Bez zreťazenia hodnôt v stĺpci pomocníka alebo vo vzorci samotnom neexistuje spôsob, ako zadať viac ako jedno kritérium.
Tento vzorec obchádza toto obmedzenie tak, že pomocou logickej logiky vytvorí pole jednotiek a núl predstavujúcich riadky zodpovedajúce všetkým 3 kritériám, a potom pomocou MATCH porovná prvú nájdenú hodnotu. Dočasné pole jednotiek a núl sa generuje pomocou tohto úryvku:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Tu porovnávame položku v H5 so všetkými položkami, veľkosť v H6 proti všetkým veľkostiam a farbu v H7 proti všetkým farbám. Počiatočným výsledkom sú tri polia TRUE / FALSE výsledkov, ako je tento:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tip: na zobrazenie týchto výsledkov použite kláves F9. Stačí zvoliť výraz na riadku vzorcov a stlačiť kláves F9.
Matematická operácia (násobenie) 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, ako je toto:
(0;0;1;0;0;0;0)
ktorý sa vkladá do funkcie MATCH ako vyhľadávacie pole s vyhľadávacou hodnotou 1:
MATCH(1,(0;0;1;0;0;0;0))
V tomto okamihu je vzorec štandardným vzorcom INDEX MATCH. Funkcia MATCH vráti hodnotu 3 na INDEX:
=INDEX(E5:E11,3)
a INDEX vráti konečný výsledok 17,00 USD.
Vizualizácia poľa
Polia vysvetlené vyššie môžu byť ťažko vizualizovateľné. Obrázok nižšie zobrazuje základnú myšlienku. Stĺpce B, C a D zodpovedajú údajom v príklade. Stĺpec F sa vytvorí vynásobením troch stĺpcov dohromady. Je to pole odovzdané MATCH.
Verzia bez poľa
K tomuto vzorcu je možné pridať ďalší INDEX, aby ste sa vyhli nutnosti zadávať ako maticový vzorec s kombináciou klávesov control + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Funkcia INDEX dokáže natívne spracovať polia, takže druhý INDEX sa pridá iba na to, aby „zachytil“ pole vytvorené pomocou booleovskej logickej operácie a vrátil rovnaké pole znova do MATCH. Za týmto účelom je INDEX nakonfigurovaný s nulovými riadkami a jedným stĺpcom. Trik nulového radu spôsobí, že INDEX vráti stĺpec 1 z poľa (ktoré je aj tak už jedným stĺpcom).
Prečo by ste chceli verziu bez poľa? Ľudia niekedy zabudnú zadať vzorec poľa pomocou klávesovej skratky control + shift + enter a vzorec vráti nesprávny výsledok. Takže vzorec bez poľa je „nepriestrelnejší“. Avšak kompromis je zložitejší vzorec.
Poznámka: V programe Excel 365 nie je potrebné zadávať vzorce polí špeciálnym spôsobom.
