seychellesartprojects.org

Indexuje shodu více řádků a sloupců s více kritérii

Všichni používáme VLOOKUP každý den k načtení dat a také jsme si vědomi skutečnosti, že VLOOKUP může načíst data zleva doprava, takže vyhledávací hodnota by měla být vždy na levé straně sloupců výsledků. Máme však několik alternativ, které lze použít jako alternativu k funkci VLOOKUP v aplikaci Excel. S pokročilou technologií můžeme tyto vzorce INDEX + MATCH použít k porovnání více kritérií pro řádky a sloupce. Tento speciální článek vás tedy podrobně provede touto technikou.

Jak používat vzorec INDEX + MATCH ke splnění více kritérií?

Zde vysvětlíme, jak pomocí vzorce index + shoda porovnat více kritérií pro řádky a sloupce s příklady.

Tuto šablonu Excel Match Multiple Criteria Excel si můžete stáhnout zde - Šablona Excel Match Multiple Criteria Excel

Příklad č. 1 - Vzorec INDEX + MATCH

Není většina funkcí vyhledávání uživatelů aplikace Excel nad rámec VLOOKUP, důvodů může být tolik. Než přejdeme na pokročilou úroveň, pojďme si jednoduše představit tento vzorec.

Podívejte se například na níže uvedenou datovou strukturu v aplikaci Excel.

Máme názvy „Sales Rep“ a jejich příslušné prodejní hodnoty. Na druhou stranu máme v buňce D2 rozevírací seznam „Sale Rep“.

Na základě výběru provedeného z rozevíracího seznamu se částka prodeje musí objevit v buňce E2.

Problém je v tom, že nemůžeme použít vzorec VLOOKUP, protože vyhledávací hodnota „Sales Rep“ je napravo od sloupce výsledků „Sales“, takže v těchto případech můžeme použít kombinovaný vzorec vyhledávací hodnoty INDEX + MATCH.

INDEX hledá uvedenou hodnotu čísla řádku v rozsahu A2: A11 a v tomto rozsahu musíme uvést, ze kterého řádku potřebujeme, aby hodnota prodeje pocházela. Tato hodnota řádku je založena na názvu „Sales Rep“ vybraného v rozevíracím seznamu v aplikaci Excel, takže funkce MATCH vyhledá číslo řádku „Sales Rep“ v rozsahu B2: B11 a vrátí číslo řádku odpovídající hodnoty. .

Příklad č. 2 - Více kritérií ve vzorci INDEX + MATCH

Nyní máme datovou strukturu, jako je níže uvedená.

Máme měsíční hodnoty prodeje „Sales Rep“. Z této tabulky potřebujeme dynamické výsledky, jako například v buňce A15 jsem vytvořil rozevírací seznam „Prodejní zástupce“ a v buňce B14 jsem vytvořil rozevírací seznam „Měsíc“.

Na základě výběru provedeného v těchto dvou buňkách musí náš vzorec načíst data z výše uvedené tabulky.

Například pokud zvolím „Rep 8“ a „Apr“, pak musí ukázat prodejní hodnotu „Rep 8“ za měsíc „Apr“.

V těchto případech tedy musíme odpovídat řádkům i sloupcům. Podle níže uvedených kroků použijte vzorec tak, aby odpovídal řádkům i sloupcům.

Krok 1: Otevřete funkci INDEX v buňce B15.

Krok 2: Prvním argumentem funkce INDEX je „Array“, tj. Ze kterého rozsahu buněk potřebujeme výsledek. V tomto případě tedy potřebujeme prodejní hodnoty, proto si vyberte rozsah buněk od B2 po G11.

Krok 3: Další argument funkce INDEX, ze kterého řádku vybraného rozsahu potřebujeme výsledek. V tomto případě musíme dospět k číslu řádku „Prodejní zástupce“ na základě výběru provedeného v buňce rozevírací buňky A15. Chcete-li tedy dynamicky načíst číslo řádku na základě výběru, otevřete funkci MATCH.

Krok 4: HODNOTA HODNOCENÍ funkce MATCH je „Prodejní zástupce“, proto vyberte jako referenci buňku A15.

Krok 5: Vyhledávací pole bude v hlavní tabulce rozsah názvů „Prodejní zástupce“. Vyberte tedy rozsah od A2 do A11.

Krok 6: Typ shody funkce MATCH bude přesný, proto jako hodnotu argumentu zadejte nulu.

Krok 7: Dalším argumentem funkce INDEX je „Číslo sloupce“, tj. Z vybraného rozsahu buněk, ze kterého sloupce potřebujeme výsledek. To závisí na měsíci, který vybereme z rozevíracího seznamu buňky B14. Chcete-li tedy získat číslo sloupce, automaticky otevřete další funkci MATCH.

Krok 8: Tato hodnota časového vyhledávání bude název měsíce, proto vyberte jako referenci buňku B14.

Krok 9: Vyhledávací pole bude měsíční rozsah buněk v hlavní tabulce, tj. Od B1 po G1.

Krok 10: Posledním argumentem je typ shody, jako kritérium zvolte možnost „Přesná shoda“. Uzavřete dvě závorky a stisknutím klávesy Enter získáte výsledek.

Jak vidíme výše, zvolili jsme „Rep 6“ a „Apr“ jako měsíc a náš vzorec vrátil hodnotu prodeje pro měsíc „Apr“ pro „Rep 6“.

Poznámka: Žlutě zbarvená buňka je pro vás referencí.

Věci k zapamatování

• Kombinace INDEX + MATCH může být silnější než vzorec VLOOKUP.
• INDEX & MATCH může odpovídat záhlaví řádků i sloupců a vrátit výsledek z prostřední tabulky.
• MATCH může vrátit číslo řádku a číslo sloupce záhlaví tabulky obou řádků a sloupců.