V tomto článku sa naučíme, ako používať funkciu INDEX a MATCH na vyhľadanie hodnoty v programe Excel.
Funkcia INDEX & MATCH nahradí funkciu vlookup
Na nájdenie hodnoty poznajúcej riadok Index a číslo stĺpca zvyčajne používame funkciu VLOOKUP. Na funkciu VLOOKUP musíme mať kritériá zhody stĺpcov s číslom stĺpca a najdôležitejšiu vec, ktorú treba zistiť, aby bola hodnota napravo od zodpovedajúcej hodnoty. Údaje to však vždy nepodporujú. Mnohokrát musíme len vyhľadať hodnoty bez znalosti indexu riadkov alebo stĺpcov, ale iba s hodnotami, ktoré sa majú zhodovať. Napríklad: zistenie platu zamestnanca s ID zamestnanca. V tomto budeme hľadať ID zamestnanca pre riadkový index a Plat pre stĺpcový index. Naučme sa, ako môžeme toto vyhľadávanie vykonať pomocou INDEX & MATCH.
Ako vyriešiť problém?
Aby vzorec najskôr porozumel, musíme si trocha zrevidovať nasledujúce funkcie
- Funkcia INDEX
- Funkcia MATCH
Teraz vytvoríme vzorec pomocou vyššie uvedených funkcií. Funkcia zhody vráti index vyhľadávacej hodnoty1 v poli hlavičky riadka. A ďalšia funkcia MATCH vráti index vyhľadávanej hodnoty 2 z poľa hlavičky stĺpca. Indexové čísla budú teraz vložené do funkcie INDEX, aby sa hodnoty dostali pod vyhľadávaciu hodnotu z údajov tabuľky.
Generický vzorec:
| = INDEX (údaje, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0)))) |
údaje : pole hodnôt v tabuľke bez hlavičiek
lookup_value1 : hodnota, ktorá sa má vyhľadať v hlavičke riadka.
riadky_záhlavia : Riadok Indexové pole na vyhľadávanie.
lookup_value1 : hodnota, ktorá sa má vyhľadať v hlavičke stĺpca.
hlavičky stĺpcov : stĺpec Indexové pole na vyhľadávanie.
Príklad:
Pochopenie vyššie uvedených tvrdení môže byť komplikované. Poďme to teda pochopiť pomocou vzorca v príklade
Tu nájdete zoznam skóre, ktoré študenti získali pomocou zoznamu predmetov. Musíme nájsť skóre pre konkrétneho študenta (Garyho) a predmetu (sociálne štúdie), ako je to znázornené na obrázku nižšie.
Študentská hodnota1 sa musí zhodovať s poľom Row_header a predmetná hodnota2 s poľom Column_header.
V bunke J6 použite vzorec:
| = INDEX (tabuľka, MATCH (J5, riadok, 0, MATCH (J4, stĺpec, 0))) |
Vysvetlenie:
- Funkcia MATCH zhoduje hodnotu Student v bunke J4 s poľom záhlavia riadkov a vracia jej pozíciu 3 ako číslo.
- Funkcia MATCH zhoduje hodnotu Subject v bunke J5 s poľom záhlavia stĺpcov a vracia jej pozíciu 4 ako číslo.
- Funkcia INDEX preberá číslo indexu riadka a stĺpca, vyhľadá údaje v tabuľke a vráti zodpovedajúcu hodnotu.
- Argument typu MATCH je pevný na 0. Pretože vzorec extrahuje presnú zhodu.
Tu sú hodnoty vzorca uvedené ako odkazy na bunky a záhlavie riadka, tabuľka a záhlavie stĺpca uvedené ako pomenované rozsahy.
Ako vidíte na obrázku vyššie, získali sme skóre získané študentom Gary v predmete Sociálne štúdie ako 36. A to dokazuje, že vzorec funguje dobre, a v prípade pochybností si prečítajte nižšie uvedené poznámky.
Teraz použijeme približnú zhodu s hlavičkami riadkov a hlavičkami stĺpcov ako čísla. Približná zhoda má iba číselné hodnoty, pretože na textové hodnoty to neexistuje
Tu uvádzame cenu hodnôt podľa výšky a šírky produktu. Musíme nájsť cenu pre konkrétnu výšku (34) a šírku (21), ako je to znázornené na obrázku nižšie.
Hodnota Height1 sa musí zhodovať s poľom Row_header a hodnota Width2 s poľom Column_header.
V bunke K6 použite vzorec:
| = INDEX (údaje, MATCH (K4, výška, 1, MATCH (K5, šírka, 1))) |
Vysvetlenie:
- Funkcia MATCH zhoduje hodnotu Výška v bunke K4 s poľom záhlavia riadkov a vracia jej pozíciu 3 ako číslo.
- Funkcia MATCH zhoduje hodnotu Šírka v bunke K5 s poľom záhlavia stĺpcov a vracia jej pozíciu 2 ako číslo.
- Funkcia INDEX preberá číslo indexu riadka a stĺpca, vyhľadá údaje v tabuľke a vráti zodpovedajúcu hodnotu.
- Argument typu MATCH je pevný na 1. Pretože vzorec extrahuje približnú zhodu.
Tu sú hodnoty vzorca uvedené ako odkazy na bunky a riadok_hlavičkového súboru, údaje a záhlavie stĺpca uvedené ako pomenované rozsahy, ako je uvedené vo vyššie uvedenej snímke.
Presné zhody vyhľadávania môžete vykonávať aj pomocou funkcií INDEX a MATCH v programe Excel. Získajte viac informácií o tom, ako postupovať pri rozlišovaní malých a veľkých písmen pomocou funkcie INDEX & MATCH v programe Excel. Čiastkové zhody môžete vyhľadať aj pomocou zástupných znakov v programe Excel.
Ako vidíte na vyššie uvedenom obrázku, dostali sme cenu získanú podľa výšky (34) & Šírka (21) ako 53.10. A to dokazuje, že vzorec funguje dobre, a v prípade pochybností si prečítajte nižšie uvedené poznámky, ktoré vám pomôžu porozumieť.
Poznámky:
- Funkcia vráti chybu #NA, ak je argument vyhľadávacieho poľa pre funkciu MATCH 2D pole, čo je pole záhlavia údajov …
- Funkcia zodpovedá presnej hodnote, pretože argument typu zhody pre funkciu MATCH je 0.
- Hodnoty vyhľadávania je možné zadať ako odkaz na bunku alebo priamo pomocou symbolu úvodzovky (") vo vzorci ako argumentov.
Dúfam, že tento článok o tom, ako používať funkciu INDEX a MATCH na vyhľadanie hodnoty v programe Excel, je vysvetľujúci. Tu nájdete ďalšie články o výpočte hodnôt a súvisiacich vzorcoch programu Excel. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku.
Na vyhľadanie hodnoty použite INDEX a MATCH : Vzorec INDEX-MATCH sa používa na dynamické a presné vyhľadávanie hodnoty v danej tabuľke. Je to alternatíva k funkcii VLOOKUP a prekonáva nedostatky funkcie VLOOKUP.
Použite VLOOKUP z dvoch alebo viacerých vyhľadávacích tabuliek : Na vyhľadávanie z viacerých tabuliek môžeme použiť prístup IFERROR. Pri vyhľadávaní z viacerých tabuliek sa chyba považuje za prepínač pre nasledujúcu tabuľku. Ďalšou metódou môže byť prístup If.
Ako vyhľadávať veľké a malé písmena v programe Excel : Funkcia VLOOKUP v Exceli nerozlišuje malé a veľké písmená a vráti prvú zodpovedajúcu hodnotu zo zoznamu. INDEX-MATCH nie je výnimkou, ale môže byť upravený tak, aby rozlišoval veľké a malé písmena. Pozrime sa, ako…
Vyhľadávanie často sa vyskytujúceho textu s kritériami v programe Excel : Vyhľadávanie sa najčastejšie vyskytuje v texte v rozsahu, v ktorom používame funkciu INDEX-MATCH s funkciou REŽIM. Tu je metóda.
Populárne články:
Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.
Ako používať funkciu VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.