Na vyhľadanie hodnôt pomocou INDEX a MATCH pomocou viacerých kritérií môžete použiť vzorec poľa. V uvedenom príklade vzorec v H8 je:
{= INDEX (range1, MATCH (1,(A1=range2)*(B1=range3)*(C1=range4),0))}
Poznámka: toto je an maticový vzorec , a musí byť zadané pomocou ovládacích prvkov + shift + enter, s výnimkou Excel 365 .
VysvetlenieToto je pokročilejší vzorec. Základné informácie nájdete v časti Ako používať INDEX a MATCH .
zaokrúhliť na najbližšiu tisícku excelovať
Vzorec INDEX MATCH je obvykle nakonfigurovaný s nastavením MATCH tak, aby prezeralo rozsah jedného stĺpca a poskytovalo zhodu na základe daných kritérií. Bez spájania hodnôt v a pomocný stĺp alebo v samotnom vzorci neexistuje spôsob, ako zadať viac ako jedno kritérium.
Tento vzorec obchádza toto obmedzenie pomocou booleovská logika vytvoriť pole jednotiek a núl, ktoré predstavujú riadky zodpovedajúce všetkým 3 kritériám, a potom pomocou funkcie MATCH priradiť prvých 1 nájdených. Dočasné pole jednotiek a núl je generované týmto úryvkom:
{= INDEX (E5:E11, MATCH (1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))}
Tu porovnávame položku v H5 so všetkými položkami, veľkosť v H6 so všetkými veľkosťami a farbu v H7 so všetkými farbami. Prvotným výsledkom sú tri polia TRUE/FALSE výsledkov takto:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Tip: na zobrazenie týchto výsledkov použite kláves F9 . Stačí vybrať výraz na paneli vzorcov a stlačiť kláves F9.
Matematická operácia (násobenie) transformuje hodnoty TRUE FALSE na 1 s a 0 s:
excel text a vzorec v jednej bunke
{TRUETRUETRUEFALSEFALSEFALSETRUE}*{FALSEFALSETRUEFALSEFALSETRUEFALSE}*{TRUEFALSETRUEFALSEFALSEFALSETRUE}
Po násobení máme jedno pole takto:
{1110001}*{0010010}*{1010001}
ktorý je privádzaný do funkcie MATCH ako vyhľadávacie pole s vyhľadávacou hodnotou 1:
{0010000}
V tomto bode je vzorec štandardným vzorcom INDEX MATCH. Funkcia MATCH vracia 3 do INDEXU:
MATCH (1,{0010000})
a INDEX vráti konečný výsledok 17,00 dolárov.
Vizualizácia poľa
Vyššie vysvetlené polia môžu byť ťažko predstaviteľné. Nasledujúci obrázok ukazuje základnú myšlienku. Stĺpce B, C a D zodpovedajú údajom v príklade. Stĺpec F vznikne 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, čím sa vyhnete potrebe zadávať ako maticový vzorec s ovládacím prvkom + shift + enter:
= INDEX (E5:E11,3)
Funkcia INDEX dokáže spracovať polia natívne, takže druhý INDEX je pridaný iba na „zachytenie“ poľa vytvoreného pomocou boolovskej logickej operácie a vrátenie rovnakého poľa znova do MATCH. Za týmto účelom je INDEX nakonfigurovaný s nulovými riadkami a jedným stĺpcom. Trik s nulovým riadkom spôsobuje, že INDEX vráti stĺpec 1 z poľa (čo je už aj tak jeden stĺpec).
Prečo by ste chceli verziu bez poľa? Niekedy ľudia zabudnú zadať maticový vzorec s ovládacím prvkom + shift + enter a vzorec vráti nesprávny výsledok. Vzorec bez poľa je teda „nepriestrelnejší“. Kompromis je však komplexnejší vzorec.
Poznámka: v Excel 365 , nie je potrebné zadávať vzorce poľa špeciálnym spôsobom.
ako previesť záporné číslo na kladné v programe ExcelPrílohy INDEX a MATCH s viacerými kritériami.xlsx Autor Dave Bruns