Přejít k hlavnímu obsahu

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.

indexová shoda s více poli 1

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.

indexová shoda s více poli 2

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

Funkce Excel INDEX vrací zobrazenou hodnotu na základě dané pozice z rozsahu nebo pole.

Funkce Excel MATCH

Funkce Excel MATCH vyhledá konkrétní hodnotu v rozsahu buněk a vrátí relativní polohu hodnoty.

Funkce Excel CHOOSE

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

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty  |  Odstranit prázdné řádky  |  Kombinujte sloupce nebo buňky bez ztráty dat  |  Kolo bez vzorce ...
Super VLookup: Více kritérií  |  Vícenásobná hodnota  |  Přes Multi-Sheets  |  Fuzzy vyhledávání...
Adv. Rozbalovací seznam: Snadno rozevírací seznam  |  Závislý rozbalovací seznam  |  Vícenásobný výběr rozevíracího seznamu...
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  Porovnejte sloupce s Vyberte stejné a různé buňky ...
Doporučené funkce: Zaměření mřížky  |  Návrhové zobrazení  |  Velký Formula Bar  |  Správce sešitů a listů | Knihovna zdrojů (Automatický text)  |  Výběr data  |  Zkombinujte pracovní listy  |  Šifrovat/dešifrovat buňky  |  Odesílat e-maily podle seznamu  |  Super filtr  |  Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky ...)  |  50+ Graf Typ nemovitosti (Ganttův diagram ...)  |  40+ Praktické Vzorce (Vypočítejte věk na základě narozenin ...)  |  19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty ...)  |  12 Konverze Tools (Čísla na slova, Přepočet měny ...)  |  7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky Excelu ...)  |  ... a více

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.
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
In sheet 1, I have a list of products about fifty different items and each one with a unique ID. On the next 12 columns is the price list for each month (Jan, Feb, Mar, Apr, May ... until Dec). Each month, the prices are slightly different. These products are to be distributed among 10 different persons with a unique ID (ex: P001) on sheet 2, I would like to have the data of the distributed items for P001 let's say for the month of Jan. how to get the price list referring to the column of Jan price list in sheet 1, Then next month, on sheet 2, if I type Feb, hot to get only the price list of Feb on sheet 1 and the same process for each month of the year.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations