Note: The other languages of the website are Google-translated. Back to English
Belépek  \/ 
x
or
x
Regisztráció  \/ 
x

or

Az INDEX és a MATCH együttes használata Excelben

Amikor Excel-táblázatokkal dolgozik, folyamatosan találhat olyan helyzeteket, amelyekben egy érték megkereséséhez szüksége van. Ebben az oktatóanyagban bemutatjuk, hogyan alkalmazhatja az INDEX és a MATCH függvények kombinációját vízszintes és függőleges keresések, kétirányú keresések, kis- és nagybetűk megkülönböztetésére, valamint több feltételnek megfelelő keresések létrehozására.

Mit csinálnak az INDEX és a MATCH függvények az Excelben?

Az INDEX és a MATCH függvények együttes használata


Mit csinálnak az INDEX és a MATCH függvények az 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.

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ő:

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

Például tudni Jeff záróvizsga pontszáma, a 6. tanuló a listán, az INDEX függvényt így használhatja:

=INDEX(E2:E11; 6) >>> visszatér 60

excel index egyezés 01

√ Megjegyzés: A tartomány E2: E11 ahol a záróvizsga szerepel, míg a szám 6 megkeresi a vizsgapontszámot 6th diák.

Tegyünk egy kis tesztet. A képlethez =INDEX(B2:E2,3;XNUMX), milyen értéket ad vissza? --- Igen, vissza fog térni Kína, a 3rd érték az adott tartományban.

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 az ország, ahonnan Emily származik Az INDEX segítségével megkereshetjük a 8-as sorszámú és 3-as oszlopszámú értéket a B2-től E11-ig terjedő cellákban, így:

=INDEX(B2:E11,8,3;XNUMX) >>> visszatér Kína

excel index egyezés 02

A fenti példák szerint Az Excel INDEX függvényével kapcsolatban tudnia kell, hogy:

  • Az INDEX funkció függőleges és vízszintes tartományokkal is működhet.
  • Az INDEX függvény nem tesz különbséget a kis- és nagybetűk között.
  • A sorszám az oszlopszám elé kerül (ha mindkét számra szüksége van) az INDEX képletben.

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.

Most először ismerkedjünk meg a MATCH függvény alapjaival.


A MATCH függvény 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ő:

=MATCH(lookup_value, lookup_array, [match_type])
  • 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",C2:C11,0;XNUMX) >>> visszatér 4

excel index egyezés 03

√ Megjegyzés: A MATCH funkció nem tesz különbséget a kis- és nagybetűk között. A „4” eredmény azt jelzi, hogy a „Vera” név a lista 4. pozíciójában található. A képletben a „0” az az egyezési típus, amely megtalálja a keresési tömb első értékét, amely pontosan megegyezik a „Vera” keresési értékkel.

Tudni a „96” pont helyzete a B2-től E2-ig terjedő sorban, használhatja a MATCH-t így:

=MATCH(96;B2:E2,0;XNUMX) >>> visszatér 4

excel index egyezés 04

☞ Amit tudnunk kell az Excel MATCH függvényéről:

  • A MATCH függvény a keresési érték pozícióját adja vissza a keresési tömbben, nem magát az értéket.
  • A MATCH függvény ismétlődés esetén az első egyezést adja vissza.
  • Az INDEX funkcióhoz hasonlóan a MATCH funkció függőleges és vízszintes tartományokkal is működhet.
  • A MATCH szintén nem különbözteti meg a kis- és nagybetűket.
  • Ha a MATCH képlet keresési értéke szöveges formában van, tegye idézőjelbe.

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 függvények együttes használata

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.

Példa az INDEX és a MATCH kombinálására

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

Például tudni Evelyn záróvizsga pontszáma, a következő képletet kell használnunk:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> visszatér 90

excel index egyezés 05

Nos, mivel a képlet bonyolultnak tűnhet, nézzük végig annak minden részét.

excel index egyezés 06

Mint fentebb látható, a nagy INDEX a képlet három argumentumot tartalmaz:

  • sor: A2: D11 azt mondja az INDEX -nek, hogy adja vissza az egyező értéket a cellákon keresztül A2-től D11-ig.
  • sor_szám: MATCH ("evelyn", B2: B11,0) megmondja az INDEX-nek az érték pontos sorát.
  • A MATCH képletről a következőképpen magyarázható: visszaadni az első olyan érték pozícióját, amely pontosan megegyezik az „evelyn” értékkel a B2-től B11-ig terjedő cellákban numerikus értékben, ami 5.
  • oszlop_száma: MATCH("záróvizsga",A1:D1,0;XNUMX) megmondja az INDEX-nek az érték pontos oszlopát.
  • A MATCH képletről ezt a következőképpen magyarázhatjuk: az A1-től D1-ig terjedő cellákban az első olyan érték pozícióját visszaadni, amely pontosan megegyezik a „záróvizsga” értékkel, számértékben, 4.

Tehát láthatja a nagy képletet olyan egyszerűen, mint az alábbiakban bemutatott:

= INDEX (A2: D11,5,4)

A példában keménykódolt értékeket használtunk, az "evelyn" és a "záróvizsga". Azonban egy ilyen nagy képletben nem akarunk kemény kódolt értékeket, mivel ezeket minden alkalommal módosítanunk kell, amikor valami újat keresünk. Ilyen körülmények között cellahivatkozások segítségével dinamikussá tehetjük a képletet:

= INDEX (A2: D11,MÉRKŐZÉS(G2,B2:B11,0),MÉRKŐZÉS(F3,A1:D1,0))

excel index egyezés 07


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

Tegyük fel, hogy ismerned kell Evelyn osztályát, hogyan használhatjuk az INDEX-et és a MATCH-t, hogy megtudjuk a választ? Ha odafigyelt, észre kell vennie, hogy az osztályoszlop a névoszlop bal oldalán található, és ez meghaladja egy másik Excel hatékony keresőfunkciója, a VLOOKUP képességét.

Valójában a bal oldali keresési képesség az egyik olyan szempont, ahol az INDEX és a MATCH kombinációja jobb, mint a VLOOKUP.

Tudni Evelyn osztálya, mindössze annyit kell tennie, hogy az F3 cellában lévő értéket „Class”-ra módosítja, és ugyanazt a képletet használja, mint fent, az INDEX és a MATCH függvények azonnal megadják a választ:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> visszatér A

excel index egyezés 08

Ha van telepítveLED Kutools az Excel számára, csapatunk által kifejlesztett professzionális Excel bővítmény, melynek segítségével bal oldali keresést is végezhet a megadott értékekre KERESÉS jobbról balra funkció néhány kattintással. A funkció megvalósításához nyissa meg a Kutools lapon az Excelben, keresse meg a Képlet csoportot, majd kattintson a gombra KERESÉS jobbról balra legördülő listáján Szuper keresés. Ekkor megjelenik egy ilyen felugró párbeszédpanel:

excel index egyezés 09

Kattintson ide a konkrét lépésekért a bal oldali keresési funkció alkalmazásához a Kutools for Excel programban.


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

Mostantól el tudja készíteni az INDEX és a MATCH kombinációs képletet dinamikus keresési értékekkel a kétirányú keresések alkalmazásához? Gyakoroljuk a képletek készítését a G3, G4 és G5 cellákban az alábbiak szerint:

excel index egyezés 10

Íme a válaszok:

G3 cella: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
G4 cella: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
G5 cella: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ Megjegyzés: A képletek alkalmazása után a G2 cella nevének megváltoztatásával könnyen beszerezheti bármely tanuló információit.


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

A fenti példákból tudjuk, hogy az INDEX és a MATCH függvény nem különbözteti meg a kis- és nagybetűket. Azokban az esetekben azonban, amikor a képletre van szüksége a kis- és nagybetűk megkülönböztetéséhez, hozzáadhat PONTOS függvényt a következő képletekhez:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ Megjegyzés: Ez egy tömbképlet, amellyel be kell írnia Ctrl + Shift + Enter. Ezután egy pár göndör zárójel jelenik meg a képletsorban.

Például tudni JIMMY vizsgapontszáma, használja a következő függvényeket:

excel index egyezés 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> visszatér 86

Vagy használhat cellahivatkozásokat:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> visszatér 86
Megjegyzés: Ne felejtsen el beírni a -val Ctrl + Shift + Enter.


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

Ha egy nagy, több oszlopot és sorfeliratot tartalmazó adatbázissal foglalkozunk, mindig nehéz olyasmit találni, amely több feltételnek is megfelel. Ebben az esetben tekintse meg az alábbi képletet több feltétel kereséséhez:

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
Megjegyzés: Ez egy tömbképlet, amelyet a következővel kell megadni Ctrl + Shift + Enter. Ezután egy pár göndör zárójel jelenik meg a képletsorban.

Például, hogy megtalálja a Az Indiából származó A osztályú Coco záróvizsga pontszáma, a képlet a következő:

excel index egyezés 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> visszatér 88
√ Megjegyzés: Ne felejtsen el beírni a gombbal Ctrl + Shift + Enter.

Nos, mi van, ha állandóan elfelejti használni Ctrl + Shift + Enter befejezni a képletet úgy, hogy a képlet helytelen eredményeket adjon vissza? Itt van egy összetettebb képlet, amellyel egyszerűen kiegészítheti belép kulcs:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

A fenti példához, hogy megtalálja a Az Indiából származó A osztályú Coco záróvizsga pontszáma, a képlet, amelyhez csak egy szokásos belép a találat a következő:

excel index egyezés 13

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

Itt nem használunk kemény kódolt értékeket, mivel több kritérium esetén univerzális képletet akarunk. Csak így a fenti példában a G2, G3, G4 cellákban lévő értékek megváltoztatásával könnyen elérhetjük a kívánt eredményt.

Val vel Kutools az Excel számára"s Több feltétellel rendelkező keresési funkció, néhány kattintással megkeresheti az adott értékeket, több feltétellel. A funkció megvalósításához nyissa meg a Kutools lapon az Excelben, keresse meg a Képlet csoportot, majd kattintson a gombra Többfeltételes keresés legördülő listáján Szuper keresés. Ekkor megjelenik egy felugró párbeszédpanel, az alábbiak szerint:

excel index egyezés 14

Kattintson ide a konkrét lépésekért a többfeltételes keresési funkció alkalmazásához a Kutools for Excel programban.


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

Ha van egy Excel-táblázatunk, amelynek különböző oszlopai egy feliratot osztanak meg, ahogy az alább látható, hogyan tudjuk az egyes tanulók nevét az INDEX és MATCH segítségével összehozni az osztályával?

excel index egyezés 15

Itt hadd mutassam meg Önnek a feladat elvégzésének módját professzionális eszközünkkel Kutools az Excel számára. A saját Képlet segítő, gyorsan összeillesztheti a tanulókat az osztályaikkal az alábbi lépések szerint:

1. Válassza ki azt a célcellát, amelybe a függvényt alkalmazni kívánja.

2. Alatt Kutools lap, menjen Formula Segítőkettyenés Formula Segítő a legördülő listán.

excel index egyezés 16

3. Választ Keresés a képlettípusból, majd kattintson a gombra Tárgymutató és egyezés több oszlopon.

excel index egyezés 17

4. a. Kattintson az 1 Excel index egyezési ikongombot a jobb oldalon Look_col kijelöli azokat a cellákat, amelyekből értéket szeretne visszaadni, azaz az osztályneveket. (Itt csak egyetlen oszlopot vagy sort választhat ki.)
    b. Kattintson a 2 Excel index egyezési ikongombot a jobb oldalon Table_rng a cellák kiválasztásához, hogy megfeleljenek a kiválasztott értékeknek Look_col, azaz a tanulók nevei.
    c. Kattintson a 3 Excel index egyezési ikongombot a jobb oldalon Keresési_érték a kikeresendő cella kiválasztásához, azaz a tanuló nevének, amelyet az osztályához kíván egyeztetni.

excel index egyezés 18

5. Kattintson az OK gombra, ekkor Jimmy osztályneve megjelenik a célcellában.

excel index egyezés 19

6. Most a kitöltő fogantyúját lefelé húzva kitöltheti más tanulók óráit.

excel index egyezés 20

Kattintson ide a Kutools for Excel letöltéséhez egy 30 napos ingyenes próbaverzióhoz.



  • Szuper Formula Bár (könnyedén szerkeszthet több szöveget és képletet); Olvasás elrendezés (könnyen olvasható és szerkeszthető nagyszámú cella); Beillesztés a Szűrt tartományba...
  • Cellák / sorok / oszlopok egyesítése és az adatok megőrzése; Osztott cellák tartalma; Kombinálja az ismétlődő sorokat és az Összeg / Átlagot... megakadályozza az ismétlődő cellákat; Hasonlítsa össze a tartományokat...
  • Válassza a Másolat vagy az Egyedi lehetőséget Sorok; Válassza az Üres sorok lehetőséget (az összes cella üres); Super Find és Fuzzy Find sok munkafüzetben; Véletlenszerű kiválasztás ...
  • Pontos másolás Több cella a képletreferencia megváltoztatása nélkül; Automatikus referenciák létrehozása több lapra; Helyezze be a golyókat, Jelölőnégyzetek és még sok más ...
  • Kedvenc és gyorsan beszúrható képletek, Tartományok, diagramok és képek; Cellák titkosítása jelszóval; Levelezőlista létrehozása és e-maileket küldeni ...
  • Kivonat szöveg, Szöveg hozzáadása, Eltávolítás pozíció szerint, Hely eltávolítása; Hozz létre és nyomtasson személyhívó részösszegeket; Konvertálás a cellatartalom és a megjegyzések között...
  • Szuper szűrő (mentse el és alkalmazza a szűrősémákat más lapokra); Haladó rendezés hónap / hét / nap, gyakoriság és egyebek szerint; Speciális szűrő félkövér, dőlt betűvel ...
  • Kombinálja a munkafüzeteket és a munkalapokat; Táblázatok egyesítése kulcsoszlopok alapján; Az adatok felosztása több lapra; Kötegelt konvertálás xls, xlsx és PDF...
  • Pivot tábla csoportosítás hét száma, a hét napja és egyebek ... Mutassa a Feloldott, Zárt cellákat különböző színekkel; Jelölje ki azokat a cellákat, amelyeknek képlete / neve van...
kte lap 201905
  • Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
  • Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
  • 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!
officetab alja
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.