Az INDEX függvény önmagában is használható, de a MATCH függvény beágyazása egy speciális keresést eredményez.
Ez a beágyazott függvény rugalmasabb, mint a VLOOKUP, és gyorsabban tud eredményt hozni.
Ez a cikk elmagyarázza, hogyan használhatja együtt az INDEX és a MATCH függvényeket az Excel összes verziójában, beleértve az Excel 2019-et és a Microsoft 365-öt is.
Mik azok az INDEX és MATCH függvények?
INDEX és MATCH az Excel keresőfunkciói. Bár két teljesen különálló függvényről van szó, amelyek önmagukban is használhatók, kombinálhatók is speciális képletek létrehozásához.
Az INDEX függvény egy adott kijelölésen belüli értéket vagy egy értékre való hivatkozást ad vissza. Használható például az érték megkeresésére egy adatkészlet második sorában vagy az ötödik sorban és a harmadik oszlopban.
Míg az INDEX nagyon jól használható önmagában, a MATCH beágyazása a képletbe kissé hasznosabbá teszi. A MATCH függvény egy megadott elemet keres egy cellatartományban, majd visszaadja az elem relatív pozícióját a tartományban. Használható például annak meghatározására, hogy egy adott név a harmadik elem a névjegyzékben.
Image
INDEX és MATCH Syntax & Arguments
Így kell mindkét függvényt megírni, hogy az Excel megértse őket:
=INDEX(tömb, sor_száma, [oszlop_száma])
array a képlet által használt cellák tartománya. Ez lehet egy vagy több sor és oszlop, például A1:D5. Kötelező.
A
row_num az a sor a tömbben, amelyből értéket, például 2-t vagy 18-at kell visszaadni. Ez kötelező, hacsak nincs jelen az oszlop_száma.
A
oszlop_száma a tömb azon oszlopa, amelyből értéket kell visszaadni, például 1-et vagy 9-et. Ez nem kötelező.
lookup_value az az érték, amelyet a lookup_array-ben egyeztetni szeretne. Ez lehet szám, szöveg vagy logikai érték, amelyet manuálisan írnak be, vagy cellahivatkozáson keresztül hivatkoznak rá. Ez kötelező.
lookup_array a cellák tartománya, amelyeken keresztül kell nézni. Ez lehet egy sor vagy egyetlen oszlop, például A2:D2 vagy G1:G45. Ez kötelező.
match_type lehet -1, 0 vagy 1. Meghatározza, hogy a lookup_value hogyan egyeztethető össze a lookup_array értékeivel (lásd alább). 1 az alapértelmezett érték, ha ez az argumentum kimarad.
Melyik egyezési típust kell használni
Egyezés típusa
Mit csinál?
Szabály
Példa
1
Megkeresi a legnagyobb értéket, amely kisebb vagy egyenlő, mint a lookup_value.
A lookup_array értékeket növekvő sorrendben kell elhelyezni (pl. -2, -1, 0, 1, 2; vagy A-Z; vagy FALSE, TRUE.
lookup_value 25, de ez hiányzik a lookup_array-ből, ezért a következő legkisebb szám, például a 22 pozíciója kerül visszaadásra.
0
Megtalálja az első értéket, amely pontosan egyenlő a lookup_value értékkel.
A lookup_array értékek tetszőleges sorrendben lehetnek.
lookup_value 25, tehát a 25 pozíciót adja vissza.
-1
Megkeresi a legkisebb értéket, amely nagyobb vagy egyenlő a lookup_value értékkel.
A lookup_array értékeket csökkenő sorrendben kell elhelyezni (pl. 2, 1, 0, -1, -2).
lookup_value 25, de hiányzik a lookup_array-ből, ezért a következő legnagyobb szám, például a 34 pozíciója kerül visszaadásra.
Használja az 1-et vagy a -1-et olyan esetekben, amikor közelítő keresést kell futtatnia egy skála mentén, például amikor számokkal foglalkozik, és ha a közelítések rendben vannak. De ne feledje, hogy ha nem adja meg a match_type értéket, az 1 lesz az alapértelmezett, ami torzíthatja az eredményeket, ha valóban pontos egyezést szeretne.
Példa INDEX és egyezési képletek
Mielőtt megvizsgálnánk, hogyan lehet az INDEX-et és a MATCH-t egy képletbe kombinálni, meg kell értenünk, hogyan működnek ezek a függvények önmagukban.
INDEX Példák
=INDEX(A1:B2, 2, 2)
=INDEX(A1:B1, 1)
=INDEX(2:2, 1)=INDEX(B1:B2; 1)
Image
Ebben az első példában négy INDEX képlet található, amelyek segítségével különböző értékeket kaphatunk:
=INDEX(A1:B2, 2, 2) végignézi az A1:B2-t, hogy megtalálja az értéket a második oszlopban és a második sorban, ami Stacy.
=INDEX(A1:B1, 1) végignézi az A1:B1-et, hogy megtalálja az értéket az első oszlopban, amely Jon.
=INDEX(2:2, 1) mindent átnéz a második sorban, hogy megtalálja az értéket az első oszlopban, ami Tim.
=INDEX(B1:B2, 1) végignézi a B1:B2-t, hogy megkeresse az első sorban lévő értéket, ami Amy.
MATCH Példák
=MATCH("Stacy", A2:D2, 0)
=MATCH(14, D1:D2)
=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)
Image
Íme négy egyszerű példa a MATCH funkcióra:
=MATCH("Stacy", A2:D2, 0) Stacyt keres az A2:D2 tartományban, és 3-at ad vissza.
=MATCH(14, D1:D2) 14-et keres a D1:D2 tartományban, de mivel nem található a táblázatban, a MATCH megtalálja a következő legnagyobb értéket ez kisebb vagy egyenlő, mint 14, ami ebben az esetben 13, ami a lookup_array 1. pozíciójában van.
=MATCH(14, D1:D2, -1) megegyezik a felette lévő képlettel, de mivel a tömb nem csökkenő sorrendben van, mint ahogy a -1 megköveteli, hibát kapunk.
=MATCH(13, A1:D1, 0) a 13-at keresi a munkalap első sorában, ami 4-et ad vissza, mivel ez a negyedik elem ebben a tömbben.
INDEX-MATCH Példák
Íme két példa, ahol az INDEX-et és a MATCH-t egy képletben kombinálhatjuk:
Cellahivatkozás keresése a táblázatban
=INDEX(B2:B5, MATCH(F1, A2:A5))
Image
Ez a példa beágyazza a MATCH képletet az INDEX képletbe. A cél a cikk színének azonosítása a cikkszám segítségével.
Ha megnézi a képet, az "Elválasztott" sorokban láthatja, hogyan lennének írva a képletek önmagukban, de mivel egymásba ágyazzuk őket, ez történik:
MATCH(F1, A2:A5) az F1 értéket (8795) keresi az A2:A5 adatkészletben. Ha visszaszámoljuk az oszlopot, azt látjuk, hogy 2, tehát a MATCH függvény éppen ezt találta ki.
Az INDEX tömb B2:B5, mivel végső soron ebben az oszlopban keressük az értéket.
Az INDEX függvény most így átírható, mivel a MATCH a 2-t találta: INDEX(B2:B5, 2, [oszlop_szám]).
Mivel a oszlop_száma nem kötelező, eltávolíthatjuk, hogy ez maradjon: INDEX(B2:B5, 2).
Tehát ez olyan, mint egy normál INDEX képlet, ahol a B2:B5 második elemének értékét találjuk, ami piros.
A MATCH és INDEX e példájában kétirányú keresést végzünk. Az ötlet az, hogy megnézzük, mennyi pénzt kerestünk a zöld termékekből májusban. Ez nagyon hasonlít a fenti példához, de egy extra MATCH képlet van beágyazva az INDEX-be.
MATCH(G1, A2:A13, 0) az első megoldott tétel ebben a képletben. A G1-et (a "május" szót) keresi az A2:A13-ban, hogy egy adott értéket kapjon. Itt nem látjuk, de 5.
MATCH(G2, B1:E1, 0) a második MATCH képlet, és nagyon hasonlít az elsőre, de inkább a G2-t keresi (a "zöld" szót)) a B1:E1 oszlopfejlécekben. Ez 3.
Most átírhatjuk az INDEX képletet így, hogy megjelenítsük, mi történik: =INDEX(B2:E13, 5, 3). Ez a teljes táblázatban, B2:E13, az ötödik sort és a harmadik oszlopot keresi, ami 180 dollárt ad vissza.
MATCH és INDEX Szabályok
Több dolgot érdemes szem előtt tartani, amikor képleteket ír ezekkel a függvényekkel:
A MATCH nem különbözteti meg a kis- és nagybetűket, így a nagy- és kisbetűket ugyanúgy kezeli a rendszer a szöveges értékek egyeztetésekor.
A MATCH az N/A értéket adja vissza több okból is: ha a match_type értéke 0, és a keresési_érték nem található, ha a match_type értéke -1, és a keresési_tömb nem csökkenő sorrendben van, ha a match_type értéke 1, és a keresési_tömb nem növekvő sorrendben, és ha a lookup_array nem egyetlen sor vagy oszlop.
Használhat helyettesítő karaktert a lookup_value argumentumban, ha a match_type értéke 0, a keresési_érték pedig egy szöveges karakterlánc. A kérdőjel bármely karakterre egyezik, a csillag pedig bármilyen karaktersorozatra (pl.például =MATCH("Jo", 1:1, 0)). Ha a MATCH használatával szeretné keresni a tényleges kérdőjelet vagy csillagot, írja be először a ~ szót.
INDEX visszaadja a REF-et! ha a sor_szám és az oszlop_szám nem mutat egy cellára a tömbön belül.
Kapcsolódó Excel-függvények
A MATCH funkció hasonló a LOOKUP funkcióhoz, de a MATCH az elem pozícióját adja vissza az elem helyett.
A VLOOKUP egy másik keresőfunkció, amelyet az Excelben használhat, de a MATCH-szal ellentétben, amely INDEX-et igényel a speciális keresésekhez, a VLOOKUP képleteknek csak erre az egy függvényre van szükségük.
A Microsoft Excel ISBLANK funkciója nagyszerű eszköz lehet a lyukak megtalálásához az adatbázisban. Így használhatja a lehető legteljesebb mértékben, feltételes formázással és nem
Az Excel DAY függvénye a dátumot egész számként adja vissza, ami bizonyos pénzügyi helyzetekben hasznos. Ismerje meg a szintaxist, az argumentumokat és a DAY függvény használatát az Excelben
Az Excel COUNTIF függvénye megszámolja a bizonyos feltételeknek megfelelő cellák számát, ami számos esetben hasznos lehet. A következőképpen használhatja a COUNTIF-et egyetlen paraméterrel az Excelben
Példák az Excel HA-THEN függvényére, amellyel logikai összehasonlítást végezhet egy érték és a várt érték között. Frissítve, hogy tartalmazza az Excel 2019-et
