Az Excel MATCH funkciójával megkeresheti egy adott érték relatív pozícióját egy cellatartományban vagy egy tömbben.
A MATCH függvény hasonló a VLOOKUP függvényhez, mivel mindkettő az Excel Lookup/Reference Functions kategóriába sorolható. A VLOOKUP egy adott értéket keres egy oszlopban, és egy értéket ad vissza ugyanabban a sorban, míg a MATCH függvény egy bizonyos értéket keres egy tartományban, és visszaadja az érték pozícióját.
Az Excel MATCH függvény egy megadott értéket keres egy cellatartományban vagy egy tömbben, és visszaadja az adott érték tartományban való első megjelenésének relatív pozícióját. A MATCH függvény arra is használható, hogy kikeressen egy bizonyos értéket, és az INDEX függvény segítségével visszaadja a megfelelő értéket (akárcsak a Vlookup). Nézzük meg, hogyan használhatjuk az Excel MATCH funkcióját egy keresési érték pozíciójának meghatározására cellatartományban.
Excel MATCH függvény
A MATCH függvény az Excel beépített függvénye, és elsősorban a keresési érték relatív helyzetének meghatározására szolgál egy oszlopban vagy egy sorban.
A MATCH függvény szintaxisa:
=MATCH(keresési_érték,keresési_tömb,[egyezési_típus})Ahol:
keresési_érték – Az az érték, amelyet egy megadott cellatartományban vagy egy tömbben szeretne keresni. Ez lehet numerikus érték, szöveges érték, logikai érték vagy cellahivatkozás, amelynek értéke van.
lookup_array – A cellák tömbjei, amelyekben értéket keres. Egy oszlopból vagy egy sorból kell állnia.
match_type – Ez egy opcionális paraméter, amely 0, 1 vagy -1 értékre állítható, és az alapértelmezett érték 1.
- 0 pontos egyezést keres, ha nem található, hibát ad vissza.
- -1 a legkisebb értéket keresi, amely nagyobb vagy egyenlő, mint a lookup_value, amikor a keresési tömb növekvő sorrendben történik.
- 1 a legnagyobb értéket keresi, amely kisebb vagy egyenlő, mint a look_up érték, amikor a keresési tömb csökkenő sorrendben történik.
Találja meg a pontos egyezés pozícióját
Tegyük fel, hogy a következő adatkészlettel rendelkezünk, ahol egy bizonyos érték pozícióját szeretnénk megtalálni.
Ebben a táblázatban meg akarjuk találni a városnév pozícióját (Memphis) az (A2:A23) oszlopban, ezért ezt a képletet használjuk:
=MATCH("memphis",A2:A23;0)A harmadik argumentum „0”-ra van állítva, mert szeretnénk megtalálni a város nevének pontos egyezését. Mint látható, a képletben a városnév „memphis” kisbetűvel, míg a táblázatban a város nevének első betűje nagybetűvel szerepel (Memphis). Ennek ellenére a képlet képes megtalálni a megadott érték pozícióját az adott tartományban. Ez azért van, mert a MATCH függvény nem tesz különbséget a kis- és nagybetűk között.
Jegyzet: Ha a keresési_érték nem található a keresési tartományban, vagy ha rossz keresési tartományt ad meg, a függvény a #N/A hibát adja vissza.
Közvetlen érték helyett használhat cellahivatkozást a függvény első argumentumában. Az alábbi képlet megkeresi az érték helyét az F2 cellában, és az eredményt az F3 cellában adja vissza.
Találja meg a hozzávetőleges egyezés pozícióját
Kétféleképpen keresheti a keresési érték közelítő vagy pontos egyezését, és visszaadhatja a pozícióját.
- Az egyik módja a legkisebb érték megkeresése, amely nagyobb vagy egyenlő (a következő legnagyobb egyezés) a megadott értéknél. Ezt úgy érhetjük el, hogy a függvény utolsó argumentumát (match_type) „-1”-re állítjuk
- Egy másik módszer a legnagyobb érték, amely kisebb vagy egyenlő (a következő legkisebb egyezés) az adott értékkel. Ezt úgy érhetjük el, hogy a függvény match_type-ját 1-re állítjuk
Következő legkisebb gyufa
Ha a függvény nem talál pontos egyezést a megadott értékkel, amikor az egyezés típusa '1', akkor megkeresi a legnagyobb értéket, amely valamivel kisebb, mint a megadott érték (ami a következő legkisebb értéket jelenti), és visszaadja a pozícióját. . Ahhoz, hogy ez működjön, a tömböt növekvő sorrendbe kell rendezni, ha nem, az hibát eredményez.
A példában az alábbi képletet használjuk a következő legkisebb egyezés megtalálásához:
=EGYEZÉS(F2;D2:D23;1)Ha ez a képlet nem találja a pontos egyezést az F2 cellában lévő értékkel, akkor a következő legkisebb érték, azaz a 98 (16) pozíciójára mutat.
Következő Legnagyobb mérkőzés
Ha az egyezés típusa „-1”-re van állítva, és a MATCH függvény nem talál pontos egyezést, megkeresi a legkisebb értéket, amely nagyobb a megadott értéknél (ami a következő legnagyobb értéket jelenti), és visszaadja a pozícióját. A keresési tömböt csökkenő sorrendbe kell rendezni ennél a metódusnál, különben hibát ad vissza.
Például írja be a következő képletet, hogy megtalálja a következő legnagyobb egyezést a keresési értékkel:
=EGYEZÉS(F2;D2:D23;-1)Ez a MATCH függvény az F2 (55) értéket keresi a D2:D23 keresési tartományban, és ha nem találja a pontos egyezést, akkor a következő legnagyobb érték, azaz 58 pozícióját (16) adja vissza.
Helyettesítő egyezés
A helyettesítő karakterek csak akkor használhatók a MATCH függvényben, ha a match_type értéke „0”, és a keresési érték egy szöveges karakterlánc. Vannak helyettesítő karakterek, amelyeket a MATCH függvényben használhat: egy csillag (*) és egy kérdőjel (?).
- Kérdőjel (?) arra szolgál, hogy egyetlen karaktert vagy betűt párosítson a szöveges karakterlánccal.
- Csillag (*) a karakterlánc tetszőleges számú karakterének párosítására szolgál.
Például két „?” helyettesítő karaktert használtunk a MATCH függvény lookup_value (Lo??n) mezőjében, hogy olyan értéket találjunk, amely megfelel a szöveges karakterláncnak bármely két karakterrel (a helyettesítő karakterek helyén). És a függvény visszaadja az egyező érték relatív pozícióját az E5 cellában.
=MATCH("Lo??n",A2:A22;0)
A (*) helyettesítő karaktert ugyanúgy használhatja, mint a (?), de a csillag tetszőleges számú karakterhez, míg a kérdőjel egyetlen karakterhez használható.
Például, ha az „sp*”-t használja, a függvény egyezhet a hangszóróval, a sebességgel vagy a spielberggel stb. De ha a függvény több vagy ismétlődő értéket talál, amelyek megfelelnek a keresési értéknek, akkor csak az első érték pozícióját adja vissza.
A példában a „Kil*o” értéket írtuk be a lookup_value argumentumba. Tehát a MATCH() függvény olyan szöveget keres, amely tartalmazza a „Kil”-t az elején, az „o”-t a végén, és tetszőleges számú karaktert között. A „Kil*o” megfelel a Kilimandzsárónak a tömbben, ezért a függvény a Kilimandzsáró relatív pozícióját adja vissza, ami 16.
INDEX és MATCH
A MATCH függvényeket ritkán használják önmagukban. Gyakran más függvényekkel párosítva hatékony képleteket hoztak létre. Ha a MATCH funkciót az INDEX funkcióval kombinálják, akkor speciális kereséseket végezhet. Sokan még mindig szívesebben használják a VLOOKUP-ot az értékek megkeresésére, mert egyszerűbb, de az INDEX MATCH rugalmasabb és gyorsabb, mint a VLOOKUP.
A VLOOKUP csak függőlegesen tud kikeresni egy értéket, azaz az oszlopokat, míg az INDEX MATCH kombó függőleges és vízszintes keresést is végezhet.
INDEX függvény, amely egy tábla vagy egy tartomány egy adott helyén lévő érték lekérésére szolgál. A MATCH függvény egy érték relatív pozícióját adja vissza egy oszlopban vagy egy sorban. Ha kombinálja, a MATCH megkeresi egy adott érték sor- vagy oszlopszámát (helyét), az INDEX függvény pedig az adott sor- és oszlopszám alapján kér le egy értéket.
Az INDEX függvény szintaxisa:
=INDEX(tömb,sor_száma,[oszlop_száma],)Mindenesetre lássuk, hogyan működik az INDEX MATCH egy példán keresztül.
Az alábbi példában szeretnénk lekérni az „Anne” tanuló „Kvíz2” pontszámát. Ehhez az alábbi képletet használjuk:
=INDEX(B2:F20;MATCH(H2;A2:A20;0),3)Az INDEX-nek sor- és oszlopszámra van szüksége az érték lekéréséhez. A fenti képletben a beágyazott MATCH függvény megkeresi az „Anne” (H2) érték sorszámát (pozícióját). Ezután ezt a sorszámot adjuk meg az INDEX függvénynek egy B2:F20 tartománnyal és egy oszlopszámmal (3), amit megadunk. És az INDEX függvény a '91' pontszámot adja vissza.
Kétirányú keresés INDEX és MATCH segítségével
Használhatja az INDEX és a MATCH függvényeket is egy kétdimenziós tartományban lévő érték keresésére (kétirányú keresés). A fenti példában a MATCH függvényt használtuk egy érték sorszámának megkeresésére, de az oszlop számát kézzel írtuk be. De a sort és az oszlopot is megtalálhatjuk, ha két MATCH függvényt egymásba ágyazunk, az egyiket az INDEX függvény sor_száma argumentumában, egy másikat pedig oszlopszám argumentumában.
Használja ezt a képletet az INDEX és a MATCH kétirányú kereséséhez:
=INDEX(A1:F20;MATCH(H2;A2:A20;0),MATCH(H3;A1:F1;0))Mint tudjuk, a MATCH függvény vízszintesen és függőlegesen is tud értéket keresni. Ebben a képletben a második MATCH függvény az oszlop_szám argumentumában megkeresi a Quiz2 (4) pozícióját, és továbbítja az INDEX függvénynek. És az INDEX lekéri a pontszámot.
Most már tudja, hogyan kell használni a Match funkciót az Excelben.