Excel

INDEX a MATCH s viacerými kritériami

Index Match With Multiple Criteria

Vzorec programu Excel: INDEX a MATCH s viacerými kritériamiGenerický vzorec | _+_ | Zhrnutie

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 .

Vysvetlenie

Toto 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.

INDEX a MATCH s viacerými kritériami - vizualizácia poľa

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 Excel
Prílohy Súbor INDEX a MATCH s viacerými kritériami.xlsx Autor Dave Bruns


^