Az Excelben gyakran szükséges bizonyos adatok pontos lekérése. Míg az INDEX és a MATCH funkcióknak megvannak a maga erősségei, ezek kombinálása hatékony eszközkészletet nyit az adatkereséshez. Együtt a keresési lehetőségek széles skáláját teszik lehetővé, az alapvető vízszintes és függőleges keresésektől a fejlettebb funkciókig, mint például a kétirányú, kis- és nagybetűk megkülönböztetése és a több feltétel alapján történő keresés. A VLOOKUP-hoz képest továbbfejlesztett képességeket kínálva az INDEX és a MATCH párosítása az adatkeresési lehetőségek szélesebb körét teszi lehetővé. Ebben az oktatóanyagban ássuk be a közösen elérhető lehetőségek mélységét.

• INDEX funkció
• MATCH funkció
• Kombinálja az INDEX-et és a MATCH-t

• Kétirányú keresés
• Bal oldali keresés
• Kis- és nagybetűk érzékeny keresés
• Legközelebbi mérkőzés
• Keresés több feltétellel
• Keresés több oszlop között
• Keresse meg az első nem üres értéket
• Keresse meg az első számértéket
• MAX vagy MIN társítások keresése
• Tipp: Szabja személyre #N/A hibaüzeneteit

Az INDEX és a MATCH használata Excelben

Mielőtt az INDEX és MATCH függvényeket használnánk, győződjön meg arról, hogy tudjuk, hogyan segíthet az INDEX és a MATCH az értékek keresésében.

Az INDEX függvény használata Excelben

A INDEX függvény az Excelben egy adott helyen, egy adott tartományban lévő értéket adja vissza. Az INDEX függvény szintaxisa a következő:

• sor (kötelező) arra a tartományra vonatkozik, ahonnan vissza szeretné adni az értéket.
• sor_szám (kötelező, hacsak nem oszlop_száma jelen van) a tömb sorszámára utal.
• oszlop_száma (nem kötelező, de kötelező, ha sor_szám ki van hagyva) a tömb oszlopszámára utal.

Például tudni a kotta Jeff, a 6A listán szereplő tanulók közül a következőképpen használhatja az INDEX függvényt:

=INDEX(C2:C11,6)

√ Megjegyzés: A tartomány C2: C11 ahol a pontszámok szerepelnek, míg a szám 6 megkeresi a vizsgapontszámot 6th diák.

Tegyünk egy kis tesztet. A képlethez =INDEX(A1:C1,2;XNUMX), milyen értéket ad vissza? --- Igen, vissza fog térni Születési dátum, a 2nd érték az adott sorban.

Most már tudnunk kell, hogy az INDEX funkció tökéletesen működik vízszintes vagy függőleges tartományokkal. De mi van akkor, ha arra van szükségünk, hogy egy nagyobb, több sorral és oszloppal rendelkező tartományban lévő értéket adjunk vissza? Nos, ebben az esetben sorszámot és oszlopszámot is alkalmaznunk kell. Például, hogy megtudja Jeff pontszáma táblázat tartományán belül egyetlen oszlop helyett a pontszámát a-val tudjuk megkeresni 6-as sorszám és egy 3-as oszlopszám a sejteket A2-től C11-ig mint ez:

=INDEX(A2:C11,6,3)

Egy igazán nagy, több sorból és oszlopból álló adatbázis esetében azonban biztosan nem kényelmes, ha a képletet pontos sor- és oszlopszámmal alkalmazzuk. És ez az, amikor kombinálnunk kell a MATCH függvény használatát.

Elképesztő! Hatékony lapok használata az Excelben, például a Chrome, az Edge, a Firefox és a Safari!
Takarítsa meg idejének 50% -át, és csökkentse az egérkattintások ezreit minden nap!

A MATCH funkció használata Excelben

Az Excel MATCH függvénye egy numerikus értéket ad vissza, egy adott elem helyét az adott tartományban. A MATCH függvény szintaxisa a következő:

• keresési_érték (kötelező) a megfelelő értékre utal lookup_array.
• lookup_array (kötelező) a cellák azon tartományára utal, ahol a MATCH keresni kíván.
• match_type (választható): 1, 0 or -1.
  • 1 (alapértelmezett), a MATCH megtalálja a legnagyobb értéket, amely kisebb vagy egyenlő keresési_érték. Az értékek a lookup_array növekvő sorrendben kell elhelyezni.
  • 0A MATCH megkeresi az első értéket, amely pontosan megegyezik a keresési_érték. Az értékek a lookup_array bármilyen sorrendben lehet. (Azokban az esetekben, amikor az egyezés típusa 0, használhat helyettesítő karaktereket.)
  • -1, A MATCH megtalálja a legkisebb értéket, amely nagyobb vagy egyenlő keresési_érték. Az értékek a lookup_array csökkenő sorrendben kell elhelyezni.

Például tudni Vera helye a névjegyzékben, használhatja a MATCH képletet a következőképpen:

=MATCH("Vera",A2:A11,0)

√ Megjegyzés: A „4” eredmény azt jelzi, hogy a „Vera” név a lista 4. pozíciójában található.

Most, hogy ismerjük az INDEX és a MATCH függvények alapvető használatát az Excelben, tűrjük fel az ingujjunkat, és készüljünk a két funkció kombinálására.

Az INDEX és a MATCH kombinálása az Excelben

Tekintse meg az alábbi példát, hogy megtudja, hogyan kombinálhatjuk az INDEX és a MATCH függvényeket:

Megtalálni Evelyn pontszáma, annak tudatában, hogy a vizsgapontszámok a 3rd oszlopot, megtehetjük használja a MATCH funkciót a sorpozíció automatikus meghatározásához anélkül, hogy manuálisan meg kellene számolni. Ezt követően az INDEX függvényt használhatjuk a érték az azonosított sor és a 3. oszlop metszéspontjában:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Nos, mivel a képlet kissé bonyolultnak tűnhet, nézzük végig az egyes részeket.

A INDEX a képlet három argumentumot tartalmaz:

• sor_szám: MATCH("Evelyn",A2:A11,0;XNUMX) megadja az INDEX-et a "" érték sorpozíciójávalEvelyn" határon belül A2: A11, Amely 5.
• oszlop_száma: 3 meghatározza a 3rd oszlopba az INDEX számára, hogy megkeresse a pontszámot a tömbön belül.
• sor: A2: C11 utasítja az INDEX-et, hogy adja vissza az egyező értéket a megadott sor és oszlop metszéspontjában, a következő tartományon belül: A2-től C11-ig. Végül megkapjuk az eredményt 90.

A fenti képletben kemény kódolt értéket használtunk, "Evelyn". A gyakorlatban azonban a keménykódolt értékek nem praktikusak, mivel minden alkalommal módosítani kellene őket, amikor különböző adatokat keresünk, például egy másik tanuló pontszámát. Ilyen esetekben cellahivatkozásokat használhatunk dinamikus képletek létrehozására. Például ebben az esetben megteszem változtasd az "Evelyn"-t F2-re:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

INDEX és MATCH képlet példák

Ebben a részben különböző körülményekről fogunk beszélni, hogy az INDEX és a MATCH függvényeket különböző igények kielégítésére használjuk.

INDEX és MATCH a kétirányú keresés alkalmazásához

Az előző példában tudtuk az oszlop számát, és egy MATCH képletet használtunk a sorszám megtalálásához. De mi van akkor, ha az oszlopszámban sem vagyunk biztosak?

Ilyen esetekben kétirányú keresést, más néven mátrixkeresést végezhetünk, két MATCH függvény használatával: az egyik a sorszám, a másik pedig az oszlopszám meghatározására szolgál. Például tudni Evelyn pontszáma, a következő képletet kell használnunk:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX és MATCH a bal oldali keresés alkalmazásához

Most nézzünk meg egy forgatókönyvet, ahol meg kell határoznia Evelyn osztályát. Talán észrevette, hogy az osztályoszlop a név oszloptól balra helyezkedik el, ami meghaladja egy másik hatékony Excel keresőfunkció, a VLOOKUP képességeit.

Valójában a bal oldali keresések végrehajtásának képessége az egyik olyan szempont, ahol az INDEX és a MATCH kombinációja felülmúlja a VLOOKUP-ot.

Megtalálni Evelyn osztálya, használja a következő képletet keresd Evelynt a B2:B11-ben és a lekérni a megfelelő értéket az A2:A11-ből.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX és MATCH a kis- és nagybetűk megkülönböztetéséhez

A MATCH függvény eleve nem érzékeny a kis- és nagybetűkre. Ha azonban arra van szüksége, hogy a képlet különbséget tegyen a kis- és nagybetűk között, javíthatja azt a PONTOS funkció. Ha az INDEX képletben a MATCH funkciót az EXACT funkcióval kombinálja, akkor hatékonyan végrehajthat egy kis- és nagybetűk közötti keresést, az alábbiak szerint:

• sor arra a tartományra utal, ahonnan vissza szeretné adni az értéket.
• keresési_érték az egyezni kívánt értékre utal, figyelembe véve a karakterek esetét, a lookup_array.
• lookup_array a cellák azon tartományára utal, amellyel a MATCH-t össze szeretné hasonlítani keresési_érték.

Például tudni JIMMY vizsgapontszáma, használja a következő képletet:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Megjegyzés: Ez egy tömbképlet, amellyel be kell írnia Ctrl + műszak + belép, kivéve az Excel 365-ben és az Excel 2021-ben.

INDEX és MATCH, hogy megtalálja a legközelebbi egyezést

Az Excelben olyan helyzetekbe ütközhet, amikor meg kell találnia a legközelebbi vagy legközelebbi egyezést egy adott értékhez egy adatkészleten belül. Ilyen esetekben az INDEX és MATCH funkciók, valamint az ABS és MIN funkciók kombinációja hihetetlenül hasznos lehet.

• sor arra a tartományra utal, ahonnan vissza szeretné adni az értéket.
• lookup_array arra az értéktartományra utal, amelyhez a legközelebbi egyezést meg szeretné találni keresési_érték.
• keresési_érték az értékre utal, hogy megtalálja a legközelebbi egyezést.

Például, hogy megtudja amelynek pontszáma a legközelebb van a 85-höz, használja a következő képletet keresse meg a 85-höz legközelebbi pontszámot a C2:C11-ben és a lekérni a megfelelő értéket az A2:A11-ből.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Megjegyzés: Ez egy tömbképlet, amellyel be kell írnia Ctrl + műszak + belép, kivéve az Excel 365-ben és az Excel 2021-ben.

Elképesztő! Hatékony lapok használata az Excelben, például a Chrome, az Edge, a Firefox és a Safari!
Takarítsa meg idejének 50% -át, és csökkentse az egérkattintások ezreit minden nap!

INDEX és MATCH, hogy több feltétellel történő keresést alkalmazzon

Ha több feltételnek is megfelel, és két vagy több oszlopban kell keresni, használja a következő képletet. A képlet lehetővé teszi, hogy több feltételre kiterjedő keresést hajtson végre különböző feltételek megadásával a különböző oszlopokban, és segít megtalálni a kívánt értéket, amely megfelel az összes megadott feltételnek.

√ Megjegyzés: Ez egy tömbképlet, amellyel be kell írnia Ctrl + műszak + belép. Ezután egy pár göndör zárójel jelenik meg a képletsorban.

• sor arra a tartományra utal, ahonnan vissza szeretné adni az értéket.
• (lookup_value=lookup_array) egyetlen feltételt jelent. Ez a feltétel azt ellenőrzi, hogy egy adott keresési_érték megegyezik az értékekkel lookup_array.

Például, hogy megtalálja a pontszáma az A osztályú Coco, akinek születési dátuma 7, használhatja a következő képletet:

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

INDEX és MATCH, hogy több oszlopot is lekérdezhessen

Képzeljen el egy forgatókönyvet, amelyben több adatoszloppal van dolgunk. Az első oszlop kulcsként működik a többi oszlopban lévő adatok besorolásához. Egy adott bejegyzés kategóriájának vagy besorolásának meghatározásához keresést kell végrehajtania az adatoszlopok között, és hozzá kell rendelnie a referenciaoszlopban található kulcshoz.

Például az alábbi táblázatban hogyan párosíthatjuk Shawn tanulót a megfelelő osztályával az INDEX és a MATCH használatával? Nos, egy képlettel elérheti, de a képlet meglehetősen kiterjedt, és nehéz lehet megérteni, nem beszélve az emlékezetről és a gépelésről.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Ahol Kutools az Excel számára's Index és egyezés több oszlopon jól jön a funkció. Leegyszerűsíti a folyamatot, így gyors és egyszerű az egyes bejegyzések és a megfelelő kategóriák párosítása. Egyszerűen feloldhatja ezt a hatékony eszközt, és könnyedén összehozhatja Shawnt az osztályával töltse le és telepítse a Kutools for Excel bővítményt, majd tegye a következőket:

1. Válassza ki azt a célcellát, ahol meg szeretné jeleníteni a megfelelő osztályt.
2. A Kutools fülre kattintva Formula Segítő > Keresés és referencia > Index és egyezés több oszlopon.
3. 
3. A felugró párbeszédpanelen tegye a következőket:
   1. Kattintson az 1 gomb mellett Look_col a visszaadni kívánt kulcsinformációkat, azaz az osztályneveket tartalmazó oszlop kiválasztásához. (Itt csak egy oszlopot választhat ki.)
   2. Kattintson a 2 gomb mellett Table_rng a cellák kiválasztásához, hogy megfeleljenek a kiválasztott értékeknek Look_col, azaz a tanulók nevei.
   3. Kattintson a 3 gomb mellett Keresési_érték jelölje ki azt a cellát, amely annak a diáknak a nevét tartalmazza, akit az osztályával egyeztetni szeretne, jelen esetben Shawn.
   4. Kattints OK.
   5. 

Eredmény

A Kutools automatikusan létrehozta a képletet, és Shawn osztályneve azonnal megjelenik a célcellában.

INDEX és MATCH az első nem üres érték kereséséhez

Ha egy oszlopból vagy egy sorból szeretné lekérni az első nem üres értéket, figyelmen kívül hagyva a hibákat, használhat egy INDEX és MATCH függvényeken alapuló képletet. Ha azonban nem szeretné figyelmen kívül hagyni a tartományból származó hibákat, adja hozzá az ISBLANK függvényt.

• Az első nem üres érték lekérése egy oszlopban vagy sorban a hibák figyelmen kívül hagyásával:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Az első nem üres érték lekérése egy oszlopban vagy sorban, beleértve a hibákat:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX és MATCH az első számérték kereséséhez

Ha egy oszlopból vagy egy sorból szeretné lekérni az első számértéket, használja az INDEX, MATCH és ISNUMBER függvényeken alapuló képletet.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX és MATCH a MAX vagy MIN társítások kereséséhez

Ha egy tartományon belüli maximális vagy minimális értékhez tartozó értéket kell lekérnie, használhatja a MAX vagy MIN függvényt az INDEX és MATCH függvényekkel együtt.

• INDEX és MATCH a maximális értékhez tartozó érték lekéréséhez:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX és MATCH a minimális értékhez tartozó érték lekéréséhez:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• A fenti képletekben két érv található:
• • sor arra a tartományra utal, ahonnan a kapcsolódó információkat vissza kívánja adni.
  • lookup_array a vizsgálandó vagy meghatározott kritériumok, azaz a maximális vagy minimális értékek alapján keresendő értékek halmazát jelenti.

Például, ha meg akarja határozni akinek a legmagasabb pontszáma van, használja a következő képletet:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Tipp: Szabja személyre #N/A hibaüzeneteit

Amikor az Excel INDEX és MATCH függvényeivel dolgozik, előfordulhat, hogy #N/A hibaüzenetet kap, ha nincs egyező eredmény. Például az alábbi táblázatban egy Samantha nevű tanuló pontszámának megkeresésekor #N/A hibaüzenet jelenik meg, mivel a lány nincs jelen az adatkészletben.

A táblázatok felhasználóbarátabbá tétele érdekében testreszabhatja ezt a hibaüzenetet úgy, hogy becsomagolja az INDEX MATCH képletet az IFNA függvénybe:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Fent található az Excel INDEX és MATCH függvényeivel kapcsolatos összes releváns tartalom. Remélem, hasznosnak találja az oktatóanyagot. Ha további Excel-tippeket és trükköket szeretne felfedezni, kérem kattintson ide több ezer oktatóanyagból álló kiterjedt gyűjteményünk eléréséhez.