INDEX a MATCH s více poli
Řekněme, že máte několik tabulek se stejnými titulky, jak je uvedeno níže, vyhledání hodnot, které odpovídají kritériím zadání z těchto tabulek, pro vás může být obtížné. V tomto tutoriálu budeme hovořit o tom, jak vyhledat hodnotu ve více polích, rozsazích nebo skupinách přiřazování konkrétních kritérií INDEX, MATCH a VYBRAT funkce.
Jak vyhledat hodnotu ve více polích?
Znát vedoucí různých skupin, které patří do různých oddělení, můžete nejprve pomocí funkce VYBRAT cílit na tabulku, ze které chcete vrátit jméno vůdce. Funkce MATCH pak zjistí pozici lídra v tabulce, kam patří. Nakonec funkce INDEX načte vůdce na základě informací o pozici plus konkrétního sloupce, kde jsou uvedena jména vedoucích.
Obecná syntaxe
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- pole_num: Číslo CHOOSE používané k označení pole ze seznamu pole1, pole2,… vrátit výsledek z.
- pole1, pole2,…: Pole, ze kterých se má vrátit výsledek. Zde se odkazuje na tři tabulky.
- vyhledávací_hodnota: Hodnota, kterou kombinační vzorec použil k nalezení pozice odpovídající odkazové čáry. Zde se odkazuje na danou skupinu.
- vyhledávací_pole: Rozsah buněk, kde lookup_value je uvedena. Zde se odkazuje na rozsah skupiny. Poznámka: Skupinový rozsah můžete použít z jakéhokoli oddělení, protože jsou všechny stejné a potřebujeme pouze získat číslo pozice.
- sloupec_číslo: Sloupec, který určíte, ze kterého chcete načíst data.
Znát vedoucí skupiny D, která patří do oddělení A, zkopírujte nebo zadejte níže uvedený vzorec do buňky G5 a stiskněte vstoupit získat výsledek:
=INDEX(VYBRAT(1,$ B $ 5: $ C $ 8,$ B $ 11: $ C $ 14,$ B $ 17: $ C $ 20),ZÁPAS(F5,$ B $ 5: $ B $ 8, 0),2)
√ Poznámka: Znaky dolaru ($) výše označují absolutní odkazy, což znamená, že rozsahy názvu a tříd ve vzorci se při přesunutí nebo zkopírování vzorce do jiných buněk nezmění. Po zadání vzorce přetáhněte úchyt výplně dolů, abyste vzorec použili na níže uvedené buňky, a poté změňte pole_num odpovídajícím způsobem.
Vysvětlení vzorce
=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): Funkce CHOOSE vrátí 1st pole ze tří polí uvedených ve vzorci. Takže se to vrátí $ B $ 5: $ C $ 8, tj datový rozsah oddělení A.
- MATCH(F5;$B$5:$B$8,0): Typ shody 0 přinutí funkci MATCH vrátit pozici první shody Skupina D, hodnota v buňce F5, v poli $ B $ 5: $ B $ 8, který je 4.
- INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5;$B$5:$B$8,0),2) = INDEX ($ B $ 5: $ C $ 8,4,2): Funkce INDEX načte hodnotu na průsečíku 4th řádek a 2. sloupec rozsahu $ B $ 5: $ C $ 8, který je Emily.
Aby nedošlo ke změně pole_num ve vzorci pokaždé, když jej zkopírujete, můžete použít pomocný sloupec, sloupec D. Vzorec by vypadal takto:
=INDEX(VYBRAT(D5,$ B $ 5: $ C $ 8,$ B $ 11: $ C $ 14,$ B $ 17: $ C $ 20),ZÁPAS(F5,$ B $ 5: $ B $ 8, 0),2)
√ Poznámka: Čísla 1, 2, 3 ve sloupci pomocníka uveďte pole1, pole2, pole3 uvnitř funkce CHOOSE.
Související funkce
Funkce Excel INDEX vrací zobrazenou hodnotu na základě dané pozice z rozsahu nebo pole.
Funkce Excel MATCH vyhledá konkrétní hodnotu v rozsahu buněk a vrátí relativní polohu hodnoty.
Funkce CHOOSE vrací hodnotu ze seznamu argumentů hodnoty na základě daného indexového čísla. Například CHOOSE(3,”Apple”,”Peach”,”Orange”) vrátí oranžovou, číslo indexu je 3 a oranžová je třetí hodnota po čísle indexu ve funkci.
Související vzorce
Vyhledejte hodnoty z jiného listu nebo sešitu
Pokud víte, jak pomocí funkce VLOOKUP hledat hodnoty v listu, nebudou vám vlookup hodnoty z jiného listu nebo sešitu dělat problém.
Vlookup s názvem dymanického listu
V mnoha případech možná budete muset shromáždit data do více pracovních listů pro shrnutí. Díky kombinaci funkce VLOOKUP a funkce INDIRECT můžete vytvořit vzorec pro vyhledání konkrétních hodnot napříč listy s dynamickým názvem listu.
Vyhledávání podle více kritérií s INDEX a MATCH
Když pracujete s velkou databází v tabulce aplikace Excel s několika sloupci a titulky, je vždy obtížné najít něco, co splňuje více kritérií. V tomto případě můžete použít maticový vzorec s funkcemi INDEX a MATCH.
Nejlepší kancelářské nástroje produktivity
Kutools pro Excel - pomůže vám vyniknout před davem
Kutools pro Excel se může pochlubit více než 300 funkcemi, Zajištění toho, že to, co potřebujete, je jen jedno kliknutí...
Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)
- Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
- Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
- Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
- Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Edge a Firefox.
