V tomto článku sa naučíme, ako vyhľadávať hodnoty v 2d tabuľke pomocou funkcie INDEX-MATCH-MATCH v programe Excel.
Scenár:
Predpokladajme, že musíte vykonať niekoľko vyhľadávaní z tabuľky, ktorá má stovky stĺpcov. V takýchto prípadoch bude použitie rôznych vzorcov pri každom vyhľadávaní trvať príliš dlho. Čo tak vytvoriť vzorec dynamického vyhľadávania, ktorý môžete vyhľadať podľa poskytnutej hlavičky. Áno, dokážeme to. Tento vzorec sa nazýva vzorec INDEX MATCH MATCH alebo povedzme 2d vyhľadávací vzorec.
Ako vyriešiť problém?
Aby vzorec najskôr porozumel, musíme si trocha zrevidovať nasledujúce funkcie
- Funkcia INDEX
- Funkcia MATCH
Funkcia INDEX vracia hodnotu v danom indexe v poli.
Funkcia MATCH vracia index prvého výskytu hodnoty v poli (pole s jednou dimenziou).
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 hodnoty2 v poli hlavičky stĺpca. Indexové čísla budú teraz vložené do funkcie INDEX, aby sa hodnoty dostali pod vyhľadávaciu hodnotu z údajov 2D 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ľadanie.
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 riadok hlavička, tabuľka a hlavička 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.
Dokazuje to, ž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 neexistuje žiadny spôsob
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ý, údaje a záhlavie stĺpca uvedené ako pomenované rozsahy, ako je uvedené na snímke vyššie.
Ako vidíte na vyššie uvedenom obrázku, máme cenu získanú podľa výšky (34) & Šírka (21) ako 53.10. Dokazuje to, ž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 ste pochopili, ako používať tabuľku Lookup in 2 D pomocou funkcie INDEX & MATCH v programe Excel. Tu nájdete ďalšie články o hodnote vyhľadávania v Exceli. Neváhajte a uveďte svoje otázky nižšie v poli pre komentáre. Určite vám pomôžeme.
Na vyhľadanie hodnoty použite INDEX a MATCH : Funkcia INDEX & MATCH na vyhľadanie hodnoty podľa potreby.
Rozsah SUM s INDEX v Exceli : Pomocou funkcie INDEX nájdite SÚČET hodnôt podľa potreby.
Ako používať funkciu SUMA v programe Excel : Nájdite SÚČET čísel pomocou funkcie SUMA vysvetlenej na príklade.
Ako používať funkciu INDEX v programe Excel : Nájdite INDEX poľa pomocou funkcie INDEX vysvetlenej na príklade.
Ako používať funkciu MATCH v programe Excel : Nájdite MATCH v poli pomocou hodnoty INDEX vo funkcii MATCH vysvetlenej na príklade.
Ako používať funkciu LOOKUP v programe Excel : Nájdite hodnotu vyhľadávania v poli pomocou funkcie LOOKUP vysvetlenej na príklade.
Ako používať funkciu VLOOKUP v programe Excel : Nájdite hodnotu vyhľadávania v poli pomocou funkcie VLOOKUP vysvetlenej na príklade.
Ako používať funkciu HLOOKUP v programe Excel : Nájdite vyhľadávaciu hodnotu v poli pomocou funkcie HLOOKUP vysvetlenej na príklade.
Populárne články
50 Skratka pre Excel, ktorá zvýši vašu produktivitu
Upravte rozbaľovací zoznam
Absolútna referencia v Exceli
Ak s podmieneným formátovaním
Ak so zástupnými znakmi
Vlookup podľa dátumu
Pripojte meno a priezvisko v Exceli
