Indexujte zhodu viacerých riadkov a stĺpcov s viacerými kritériami
Všetci používame VLOOKUP každý deň na načítanie údajov a tiež sme si vedomí skutočnosti, že VLOOKUP dokáže načítať údaje zľava doprava, takže vyhľadávacia hodnota by mala byť vždy na ľavej strane stĺpcov s výsledkami. Máme však niekoľko alternatív, ktoré je možné použiť ako alternatívu k funkcii VLOOKUP v programe Excel. Vďaka pokročilej technológii môžeme tieto vzorce INDEX + MATCH použiť na priradenie viacerých kritérií pre riadky a stĺpce. Tento špeciálny článok vás teda podrobne prevedie touto technikou.
Ako používať vzorec INDEX + MATCH na splnenie viacerých kritérií?
Tu vysvetľujeme, ako použiť vzorec index + zhoda na priradenie viacerých kritérií pre riadky a stĺpce s príkladmi.
Príklad č. 1 - Vzorec INDEX + MATCH
Nie väčšina používateľov vyhľadávacích funkcií programu Excel nad rámec VLOOKUP, dôvodov môže byť toľko. Skôr ako prejdeme na pokročilú úroveň, urobme si jednoduchý úvod do tohto vzorca.
Napríklad sa pozrite na nasledujúcu dátovú štruktúru v programe Excel.
Máme názvy „Sales Rep“ a ich príslušné predajné hodnoty. Na druhej strane máme rozbaľovací zoznam „Sale Rep“ v bunke D2.
Na základe výberu, ktorý vykonáme z rozbaľovacieho zoznamu, sa musí objem predaja zobraziť v bunke E2.
Problém je v tom, že nemôžeme použiť vzorec VLOOKUP, pretože vyhľadávacia hodnota „Sales Rep“ sa nachádza napravo od výsledného stĺpca „Sales“, takže v týchto prípadoch môžeme použiť kombinovaný vzorec vyhľadávacej hodnoty INDEX + MATCH.
INDEX hľadá uvedenú hodnotu čísla riadku v rozsahu A2: A11 a v tomto rozsahu musíme uviesť, z ktorého riadku potrebujeme, aby pochádzala hodnota predaja. Táto hodnota riadku je založená na názve „Sales Rep“ vybratom v rozbaľovacom zozname v programe Excel, takže funkcia MATCH vyhľadá číslo riadku „Sales Rep“ v rozsahu B2: B11 a vráti číslo riadku priradenej hodnoty. .
Príklad č. 2 - Viaceré kritériá vo vzorci INDEX + MATCH
Teraz máme dátovú štruktúru, ako je tá nižšie.
Máme mesačné hodnoty predaja „Obchodný zástupca“. Z tejto tabuľky potrebujeme dynamické výsledky, ako napríklad v bunke A15, vytvoril som rozbaľovací zoznam „Obchodný zástupca“ a v bunke B14 som vytvoril rozbaľovací zoznam „Mesiac“.
Na základe výberu vykonaného v týchto dvoch bunkách musí náš vzorec načítať údaje z tabuľky vyššie.
Napríklad, ak vyberiem „Rep 8“ a „Apr“, potom musí ukazovať hodnotu predaja „Rep 8“ za mesiac „Apr.“
V týchto prípadoch teda musíme zladiť riadky aj stĺpce. Podľa nasledujúcich pokynov použite vzorec tak, aby sa zhodoval s riadkami aj stĺpcami.
Krok 1: Otvorte funkciu INDEX v bunke B15.
Krok 2: Prvý argument funkcie INDEX je „Array“, tj. Z ktorého rozsahu buniek potrebujeme výsledok. V tomto prípade teda potrebujeme hodnoty predaja, preto si vyberte rozsah buniek od B2 po G11.
Krok 3: Ďalší argument funkcie INDEX, z ktorého riadku zvoleného rozsahu potrebujeme výsledok. V takom prípade musíme dospieť k číslu riadku „Obchodný zástupca“ na základe výberu vykonaného v bunke rozbaľovacej ponuky A15. Takže pre dynamické načítanie čísla riadku na základe výberu otvorte funkciu MATCH.
Krok 4: HODNOTA HODNOTENIA funkcie MATCH je „Sales Rep“, takže ako referenciu vyberte bunku A15.
Krok 5: Vyhľadávacím poľom bude v hlavnej tabuľke rozsah názvov „Obchodný zástupca“. Vyberte si teda rozsah ako A2 až A11.
Krok 6: Typ zhody funkcie MATCH bude presný, takže ako hodnotu argumentu zadajte nulu.
Krok 7: Ďalším argumentom funkcie INDEX je „Číslo stĺpca“, tj. Z vybraného rozsahu buniek, z ktorých stĺpca potrebujeme výsledok. To závisí od mesiaca, ktorý vyberieme z rozbaľovacieho zoznamu bunky B14. Ak chcete získať číslo stĺpca automaticky, otvorte ďalšiu funkciu MATCH.
Krok 8: Táto hodnota časového hľadania bude názov mesiaca, takže ako referenciu vyberte bunku B14.
Krok 9: Vyhľadávacie pole bude mesačný rozsah buniek v hlavnej tabuľke, tj od B1 do G1.
Krok 10: Posledný argument je typ zhody; ako kritérium zvoľte možnosť „Presná zhoda“. Zatvorte dve zátvorky a stlačením klávesu Enter získate výsledok.
Ako vidíme vyššie, ako mesiac sme vybrali „Rep 6“ a „Apr“ a náš vzorec vrátil hodnotu predaja pre mesiac „Apr“ pre „Rep 6“.
Poznámka: Žlto sfarbená bunka je pre vás referenciou.
Na čo treba pamätať
- Kombinácia INDEX + MATCH môže byť účinnejšia ako vzorec VLOOKUP.
- INDEX & MATCH sa môže zhodovať s hlavičkami riadkov aj stĺpcov a vrátiť výsledok z prostrednej tabuľky.
- ZÁPAS môže vrátiť číslo riadku a číslo stĺpca hlavičiek tabuľky oboch riadkov a stĺpcov.
