Note: The other languages of the website are Google-translated. Back to English
Přihlásit se  \/ 
x
or
x
Registrace  \/ 
x

or

Jak používat INDEX a MATCH společně v Excelu

Při práci s excelovými tabulkami můžete neustále nacházet situace, kdy potřebujete vyhledat hodnotu. V tomto tutoriálu vám ukážeme, jak použít kombinaci funkcí INDEX a MATCH pro horizontální a vertikální vyhledávání, obousměrné vyhledávání, vyhledávání rozlišující malá a velká písmena a vyhledávání splňující více kritérií.

Co dělají funkce INDEX a MATCH v Excelu

Jak používat funkce INDEX a MATCH společně


Co dělají funkce INDEX a MATCH v Excelu

Než použijeme funkce INDEX a MATCH, ujistíme se, že víme, jak nám mohou INDEX a MATCH pomoci nejprve vyhledat hodnoty.

Použití funkce INDEX v Excelu

Společnost INDEX funkce v Excelu vrátí hodnotu na daném místě v určitém rozsahu. Syntaxe funkce INDEX je následující:

=INDEX(array, row_num, [column_num])
  • řada (povinné) odkazuje na rozsah, ze kterého chcete vrátit hodnotu.
  • řádek_číslo (povinné, pokud není uvedeno číslo_sloupce) odkazuje na číslo řádku pole.
  • číslo_sloupce (volitelné, ale povinné, pokud je vynecháno číslo_řádku) odkazuje na číslo sloupce pole.

Například vědět skóre závěrečné zkoušky Jeffa, 6. student na seznamu, můžete použít funkci INDEX takto:

= INDEX (E2: E11, 6) >>> se vrací 60

Excel index shoda 01

√ Poznámka: Rozsah E2: E11 je tam, kde je uvedena závěrečná zkouška, zatímco číslo 6 zjistí skóre zkoušky 6student.

Tady si uděláme malý test. Pro formuli =INDEX(B2:E2,3;XNUMX), jakou hodnotu vrátí? --- Ano, vrátí se Čínase 3. hodnotu v daném rozsahu.

Nyní bychom měli vědět, že funkce INDEX dokáže perfektně pracovat s horizontálními nebo vertikálními rozsahy. Ale co když potřebujeme, aby vrátil hodnotu ve větším rozsahu s několika řádky a sloupci? V tomto případě bychom měli použít jak číslo řádku, tak číslo sloupce. Například zjistit země, ze které Emily pochází pomocí INDEXu můžeme najít hodnotu s číslem řádku 8 a číslem sloupce 3 v buňkách přes B2 až E11 takto:

=INDEX(B2:E11,8,3;XNUMX) >>> se vrací Čína

Excel index shoda 02

Podle výše uvedených příkladů o funkci INDEX v aplikaci Excel byste měli vědět, že:

  • Funkce INDEX může pracovat s vertikálními a horizontálními rozsahy.
  • Funkce INDEX nerozlišuje velká a malá písmena.
  • Číslo řádku předchází číslo sloupce (pokud potřebujete obě čísla) ve vzorci INDEX.

Pro opravdu velkou databázi s více řádky a sloupci nám ale rozhodně není vhodné použít vzorec s přesným číslem řádku a sloupce. A to je, když bychom měli kombinovat použití funkce MATCH.

Nyní se nejprve seznámíme se základy funkce MATCH.


Využití funkce MATCH v Excelu

Funkce MATCH v Excelu vrací číselnou hodnotu, umístění konkrétní položky v daném rozsahu. Syntaxe funkce MATCH je následující:

=MATCH(lookup_value, lookup_array, [match_type])
  • vyhledávací_pole (povinné) označuje rozsah buněk, ve kterých chcete hledat MATCH.
  • typ_shody (volitelný), 1, 0 or -1:
  • 1(výchozí), MATCH najde největší hodnotu, která je menší nebo rovna lookup_value. Hodnoty v vyhledávací_pole musí být řazeny vzestupně.
  • 0, MATCH najde první hodnotu, která se přesně rovná lookup_value. Hodnoty v vyhledávací_pole může být v libovolném pořadí. (V případech, kdy je typ shody nastaven na 0, můžete použít zástupné znaky.)
  • -1, MATCH najde nejmenší hodnotu, která je větší nebo rovna lookup_value. Hodnoty v vyhledávací_pole musí být uspořádány sestupně.

Například vědět pozici Věry v seznamu jmen, můžete použít vzorec MATCH takto:

=MATCH("vera",C2:C11,0;XNUMX) >>> se vrací 4

Excel index shoda 03

√ Poznámka: Funkce MATCH nerozlišuje velká a malá písmena. Výsledek „4“ znamená, že jméno „Vera“ je na 4. pozici seznamu. „0“ ve vzorci je typ shody, který najde první hodnotu ve vyhledávacím poli, která se přesně rovná vyhledávací hodnotě „Vera“.

Vědět pozice skóre „96“ v řadě od B2 do E2, můžete použít MATCH takto:

= MATCH (96, B2: E2,0) >>> se vrací 4

Excel index shoda 04

☞ Věci, které bychom měli vědět o funkci MATCH v Excelu:

  • Funkce MATCH vrací pozici hledané hodnoty ve vyhledávacím poli, nikoli samotnou hodnotu.
  • Funkce MATCH vrací první shodu v případě duplikátů.
  • Stejně jako funkce INDEX může funkce MATCH pracovat také s vertikálními a horizontálními rozsahy.
  • MATCH také nerozlišuje velká a malá písmena.
  • Pokud je vyhledávací hodnota vzorce MATCH ve formě textu, uzavřete ji do uvozovek.

Nyní, když víme o základním použití funkcí INDEX a MATCH v Excelu, vyhrňme si rukávy a připravme se na kombinaci obou funkcí.


Jak používat funkce INDEX a MATCH společně

V této části budeme hovořit o různých okolnostech použití funkcí INDEX a MATCH ke splnění různých potřeb.

Příklad pro kombinaci INDEX a MATCH

Podívejte se prosím na níže uvedený příklad, abyste zjistili, jak můžeme kombinovat funkce INDEX a MATCH:

Například vědět Evelynina závěrečná zkouška, měli bychom použít vzorec:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> se vrací 90

Excel index shoda 05

Protože vzorec může vypadat složitě, pojďme si projít každou jeho část.

Excel index shoda 06

Jak můžete vidět výše, velký INDEX vzorec obsahuje tři argumenty:

  • řada: A2: D11 říká INDEXu, aby vrátil odpovídající hodnotu z buněk skrz A2 až D11.
  • řádek_číslo: MATCH („evelyn“, B2: B11,0) říká INDEXu přesný řádek hodnoty.
  • O vzorci MATCH to můžeme vysvětlit takto: vrátit pozici první hodnoty, která se přesně rovná „evelyn“ v buňkách od B2 do B11 v číselné hodnotě, která je 5.
  • číslo_sloupce: MATCH("závěrečná zkouška";A1:D1,0) řekne INDEX přesný sloupec hodnoty.
  • O vzorci MATCH to můžeme vysvětlit takto: vrátit pozici první hodnoty, která se přesně rovná „závěrečné zkoušce“ v buňkách od A1 do D1, v číselné hodnotě, která je 4.

Takže můžete vidět velký vzorec stejně jednoduchý jako ten, který jsme ukázali níže:

= INDEX (A2: D11,5,4)

V příkladu jsme použili pevně zakódované hodnoty, "evelyn" a "final exam". V tak velkém vzorci však nechceme pevně zakódované hodnoty, protože je budeme muset měnit pokaždé, když budeme hledat něco nového. Za takových okolností můžeme použít odkazy na buňky, aby byl vzorec dynamický takto:

= INDEX (A2: D11,ZÁPAS(G2,B2:B11,0),ZÁPAS(F3,A1:D1,0))

Excel index shoda 07


INDEX a MATCH použijete levé vyhledávání

Nyní řekněme, že potřebujete znát Evelyninu třídu, jak můžeme použít INDEX a MATCH, abychom znali odpověď? Pokud jste věnovali pozornost, měli byste si všimnout, že sloupec třídy je na levé straně sloupce názvu, což je mimo možnosti jiné výkonné vyhledávací funkce aplikace Excel, SVYHLEDAT.

Ve skutečnosti je možnost levého vyhledávání jedním z aspektů, kde je kombinace INDEX a MATCH lepší než SVYHLEDAT.

Vědět Evelynina třída, vše, co musíte udělat, je změnit hodnotu v buňce F3 na „Class“ a použít stejný vzorec, jak je uvedeno výše, funkce INDEX a MATCH vám pak hned řeknou odpověď:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> se vrací A

Excel index shoda 08

Pokud máte nainstalovánovedl Kutools pro Excel, profesionální doplněk Excel vyvinutý naším týmem, můžete také použít levé vyhledávání pro zadané hodnoty s jeho VYHLEDÁVÁNÍ zprava doleva funkce několika kliknutími. Chcete -li tuto funkci implementovat, přejděte na Kutools kartu ve svém Excelu, najděte Vzorec skupiny a klikněte na VYHLEDÁVÁNÍ zprava doleva v rozevíracím seznamu Super VZHLED. Uvidíte vyskakovací dialogové okno, jako je toto:

Excel index shoda 09

Kliknutím sem zobrazíte konkrétní kroky k použití funkce levého vyhledávání v aplikaci Kutools for Excel.


INDEX a MATCH pro použití obousměrného vyhledávání

Nyní jste schopni vytvořit kombinační vzorec INDEX a MATCH s hodnotami dynamického vyhledávání pro použití obousměrného vyhledávání? Procvičme si vytváření vzorců v buňkách G3, G4 a G5, jak je uvedeno níže:

Excel index shoda 10

Zde jsou odpovědi:

Buňka G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Buňka G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Buňka G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ Poznámka: Po použití vzorců můžete snadno získat informace o všech studentech změnou jména v buňce G2.


INDEX a MATCH pro použití vyhledávání s rozlišením velkých a malých písmen

Z výše uvedených příkladů víme, že funkce INDEX a MATCH nerozlišují velká a malá písmena. V případech, kdy potřebujete svůj vzorec k rozlišení velkých a malých písmen, můžete přidat PŘESNÝ fungovat podle vašich vzorců takto:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ Poznámka: Toto je maticový vzorec, který vyžaduje zadání pomocí Ctrl + Shift + Enter. V řádku vzorců se pak zobrazí dvojice složených závorek.

Například vědět JIMMYho výsledek zkoušky, použijte funkce takto:

Excel index shoda 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> se vrací 86

Nebo můžete použít odkazy na buňky:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> se vrací 86
Poznámka: Nezapomeňte zadat s Ctrl + Shift + Enter.


INDEX a MATCH pro použití vyhledávání s více kritérii

Při práci s velkou databází s několika sloupci a popisky řádků je vždy obtížné najít něco, co splňuje více podmínek. V takovém případě vyhledejte více kritérií podle následujícího vzorce:

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
Poznámka: Toto je maticový vzorec, který vyžaduje zadání pomocí Ctrl + Shift + Enter. V řádku vzorců se pak zobrazí dvojice složených závorek.

Chcete-li například najít skóre závěrečné zkoušky třídy A's Coco, který pochází z Indie, vzorec je následující:

Excel index shoda 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> se vrací 88
√ Poznámka: Nezapomeňte zadat pomocí Ctrl + Shift + Enter.

No, co když neustále zapomínáte používat Ctrl + Shift + Enter dokončit vzorec tak, aby vzorec vracel nesprávné výsledky? Zde máme složitější vzorec, se kterým stačí doplnit jeden jednoduchý vstoupit klíč:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

Pro stejný příklad výše najít skóre závěrečné zkoušky třídy A's Coco, který pochází z Indie, vzorec, který potřebuje pouze obvyklé vstoupit hit je následující:

Excel index shoda 13

=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0)) >>> se vrací 88

Zde nebudeme používat pevně zakódované hodnoty, protože budeme chtít univerzální vzorec v případě s více kritérii. Pouze tímto způsobem můžeme snadno získat požadovaný výsledek změnou hodnot v buňkách G2, G3, G4 v příkladu výše.

S Kutools pro Excel"Funkce vyhledávání podle více podmínek umožňuje vyhledávat konkrétní hodnoty s více kritérii několika kliknutími. Chcete -li tuto funkci implementovat, přejděte na Kutools kartu ve svém Excelu, najděte Vzorec skupiny a klikněte na Vyhledávání více podmínek v rozevíracím seznamu Super VZHLED. Poté uvidíte vyskakovací dialogové okno, jak je znázorněno níže:

Excel index shoda 14

Kliknutím sem zobrazíte konkrétní kroky k použití funkce vyhledávání více podmínek pomocí Kutools pro Excel.


INDEX a MATCH pro použití vyhledávání ve více sloupcích

Pokud máme excelovou tabulku s různými sloupci sdílejícími jeden titulek, jak je uvedeno níže, jak můžeme pomocí INDEXU a MATCH přiřadit jméno každého studenta k jeho třídě?

Excel index shoda 15

Zde mi dovolte, abych vám ukázal způsob, jak dokončit úkol pomocí našeho profesionálního nástroje Kutools pro Excel. S jeho Pomocník formule, můžete rychle spojit studenty s jejich třídami podle níže uvedených kroků:

1. Vyberte cílovou buňku, kde chcete funkci použít.

2. Pod Kutools kartu, přejděte na Pomocník vzorců, Klepněte na tlačítko Pomocník vzorců v rozevíracím seznamu.

Excel index shoda 16

3. Vybrat Vyhledávání z Formula Type, pak klikněte na Index a shoda na více sloupcích.

Excel index shoda 17

4. a. Klikněte na 1 ikona shody indexu aplikace Exceltlačítko na pravé straně Look__col vyberte buňky, ze kterých chcete vrátit hodnotu, tj. názvy tříd. (Zde můžete vybrat pouze jeden sloupec nebo řádek.)
    b. Klikněte na 2 ikona shody indexu aplikace Exceltlačítko na pravé straně Table_rng vyberte buňky tak, aby odpovídaly hodnotám ve vybraném Look__col, tj. jména studentů.
    C. Klikněte na 3 ikona shody indexu aplikace Exceltlačítko na pravé straně Vyhledávací_hodnota vyberte buňku, kterou chcete vyhledat, tj. jméno studenta, které chcete přiřadit k jeho třídě.

Excel index shoda 18

5. Klikněte na OK, v cílové buňce se zobrazí název třídy Jimmyho.

Excel index shoda 19

6. Nyní můžete přetáhnout úchyt výplně dolů a vyplnit tak třídy ostatních studentů.

Excel index shoda 20

Kliknutím si stáhnete Kutools pro Excel na 30denní bezplatnou zkušební verzi.



  • Super Formula Bar (snadno upravit více řádků textu a vzorce); Rozložení pro čtení (snadno číst a upravovat velké množství buněk); Vložit do filtrovaného rozsahu...
  • Sloučit buňky / řádky / sloupce a uchovávání údajů; Rozdělit obsah buněk; Zkombinujte duplicitní řádky a součet / průměr... Zabraňte duplicitním buňkám; Porovnat rozsahy...
  • Vyberte možnost Duplikovat nebo Jedinečný Řádky; Vyberte prázdné řádky (všechny buňky jsou prázdné); Super hledání a fuzzy hledání v mnoha sešitech; Náhodný výběr ...
  • Přesná kopie Více buněk beze změny odkazu na vzorec; Automaticky vytvářet reference do více listů; Vložte odrážky, Zaškrtávací políčka a další ...
  • Oblíbené a rychlé vkládání vzorců„Rozsahy, grafy a obrázky; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
  • Extrahujte text, Přidat text, Odebrat podle pozice, Odebrat mezeru; Vytváření a tisk mezisoučtů stránkování; Převod mezi obsahem buněk a komentáři...
  • Super filtr (uložit a použít schémata filtrů na jiné listy); Rozšířené řazení podle měsíce / týdne / dne, frekvence a dalších; Speciální filtr tučnou kurzívou ...
  • Kombinujte sešity a pracovní listy; Sloučit tabulky na základě klíčových sloupců; Rozdělte data do více listů; Dávkový převod xls, xlsx a PDF...
  • Seskupování kontingenčních tabulek podle číslo týdne, den v týdnu a další ... Zobrazit odemčené, zamčené buňky různými barvami; Zvýrazněte buňky, které mají vzorec / název...
karta kte 201905
  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
officetab dno
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.