Miért jobb az INDEX és a MATCH, mint a VLOOKUP az Excelben?

Korábban a kezdőknek elmagyaráztuk a VLOOKUP (angolul VLOOKUP, a „vertikális keresési funkció” rövidítése) alapvető funkcióinak használatát. A tapasztalt felhasználóknak pedig számos bonyolultabb képletet mutattak be.

Ebben a cikkben megpróbálunk információkat adni a vertikális keresés egy másik módszeréről.

Felmerülhet benned a kérdés: „Miért van erre szükség?”. És ez szükséges az összes lehetséges keresési módszer megjelenítéséhez. Ezenkívül számos VLOOKUP-korlátozás gyakran megakadályozza a kívánt eredmény elérését. Ebből a szempontból az INDEX( ) MATCH( ) sokkal funkcionálisabb és változatosabb, és kevesebb korlátozást is tartalmaz.

Alapok INDEX MATCH

Mivel ennek az útmutatónak az a célja, hogy bemutassa, milyen jó ez a funkció, mi Nézzük meg az alapvető információkat a működési elveiről. És mutatunk példákat, és azt is megfontoljuk, hogy miért, jobb, mint a VLOOKUP ().

INDEX A függvény szintaxisa és használata

Ez a funkció segít megtalálni a kívánt értéket a megadott keresési területek között az oszlop- vagy sorszám alapján. Syntax:

=INDEX(tömb, sorszám, oszlopszám):

• tömb – az a terület, ahol a keresés megtörténik;
• sorszám – a keresendő sor száma a megadott tömbben. Ha a sorszám ismeretlen, meg kell adni az oszlop számát;
• oszlop száma – a megadott tömbben található oszlop száma. Ha az érték ismeretlen, sorszámot kell megadni.

Példa egy egyszerű képletre:

=INDEX(A1:S10,2,3;XNUMX;XNUMX)

A funkció az A1 és C10 közötti tartományban keres. A számok azt mutatják, hogy melyik sorból (2) és oszlopból (3) jelenítse meg a kívánt értéket. Az eredmény a C2 cella lesz.

Elég egyszerű, igaz? De ha valódi dokumentumokkal dolgozik, valószínűleg nem lesz információja az oszlopszámokról vagy cellákról. Erre való a MATCH() függvény.

MATCH függvény szintaxisa és használata

A MATCH() függvény megkeresi a kívánt értéket, és ennek hozzávetőleges számát mutatja a megadott keresési területen.

A searchpos() szintaxis így néz ki:

=MATCH(érték a kereséshez, tömb a kereséshez, egyezés típusa)

• keresési érték – a keresendő szám vagy szöveg;
• keresett tömb – az a terület, ahol a keresés történik;
• egyezés típusa – megadja, hogy a pontos vagy a legközelebbi értéket kell-e keresni:
  • 1 (vagy nincs megadva érték) – a legnagyobb értéket adja vissza, amely egyenlő vagy kisebb a megadott értéknél;
  • 0 – pontos egyezést mutat a keresett értékkel. Az INDEX() MATCH() kombinációban szinte mindig pontos egyezésre lesz szükség, ezért 0-t írunk;
  • -1 – A legkisebb értéket mutatja, amely nagyobb vagy egyenlő, mint a képletben megadott érték. A rendezés csökkenő sorrendben történik.

Például a B1:B3 tartományban New York, Párizs, London szerepel. Az alábbi képlet a 3-as számot mutatja, mivel London a harmadik a listán:

=EXPOSE(London,B1:B3,0)

Az INDEX MATCH funkció használata 

Valószínűleg már elkezdte megérteni azt az elvet, amely alapján ezeknek a funkcióknak a közös munkája felépül. Röviden akkor Az INDEX() megkeresi a kívánt értéket a megadott sorok és oszlopok között. A MATCH() pedig ezeknek az értékeknek a számát mutatja:

=INDEX(oszlop, amelyből az értéket visszaadja, MATCH(keresendő érték, keresendő oszlop, 0))

Még mindig nehéz megérteni, hogyan működik? Talán egy példa jobban megmagyarázza. Tegyük fel, hogy van egy listája a világ fővárosairól és lakosságukról:

Egy adott főváros, például Japán fővárosának lakosságszámának meghatározásához a következő képletet használjuk:

=INDEX(C2:C10, MATCH(Japán, A2:A10,0))

Magyarázat:

• A MATCH() függvény a „Japán” értéket keresi az A2:A10 tömbben, és a 3-as számot adja vissza, mivel Japán a harmadik érték a listában. 
• Ez a szám asorszám” az INDEX() képletben, és megmondja a függvénynek, hogy nyomtatjon ki egy értéket ebből a sorból.

Így a fenti képlet lesz a standard képlet INDEX(C2:C10,3). A képlet C2-től C10-ig keres, és a tartomány harmadik cellájából, azaz a C4-ből ad vissza adatokat, mert a visszaszámlálás a második sortól kezdődik.

Nem akarja előírni a város nevét a képletben? Ezután írja be bármelyik cellába, mondjuk az F1-et, és használja referenciaként a MATCH() képletben. És a végén egy dinamikus keresési képletet kap:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Fontos! A sorok száma sor Az INDEX()-nek meg kell egyeznie a sorok számával figyelembe vett tömb a MATCH()-ban, különben rossz eredményt kap.

Várj egy percet, miért nem csak a VLOOKUP() képletet használod?

=VKERESÉS(F1, A2:C10, 3, hamis)

 Mi értelme van időt vesztegetni az INDEX MATCH bonyolultságának kiderítésére?

Ebben az esetben nem mindegy, hogy melyik funkciót használja. Ez csak egy példa, hogy megértsük, hogyan működnek együtt az INDEX() és MATCH() függvények. Más példák bemutatják, mire képesek ezek a funkciók olyan helyzetekben, amikor a VLOOKUP tehetetlen. 

INDEX MATCH vagy VLOOKUP

Amikor eldöntik, hogy melyik keresési képletet használják, sokan egyetértenek abban, hogy az INDEX() és a MATCH() sokkal jobb, mint a VLOOKUP. Sokan azonban még mindig a VLOOKUP()-ot használják. Először is, a VLOOKUP() egyszerűbb, másodszor pedig a felhasználók nem értik teljesen az INDEX() és MATCH() használatának minden előnyét. E tudás nélkül senki sem hajlandó egy komplex rendszer tanulmányozására fordítani az idejét.

Íme az INDEX() és MATCH() fő előnyei a VLOOKUP()-hoz képest:

• Keresés jobbról balra. A VLOOKUP() nem tud jobbról balra keresni, ezért a keresett értékeknek mindig a táblázat bal szélső oszlopaiban kell lenniük. De az INDEX() és a MATCH() ezt gond nélkül kezeli. Ez a cikk megmondja, hogyan néz ki a gyakorlatban: hogyan lehet megtalálni a kívánt értéket a bal oldalon.

1. Oszlopok biztonságos hozzáadása vagy eltávolítása. A VLOOKUP() képlet hibás eredményeket mutat oszlopok eltávolításakor vagy hozzáadásakor, mert a VLOOKUP()-nak a pontos oszlopszámra van szüksége a sikeres működéshez. Természetesen az oszlopok hozzáadásakor vagy eltávolításakor a számuk is megváltozik. 

Az INDEX() és MATCH() képletekben pedig egy oszloptartomány van megadva, nem pedig az egyes oszlopok. Ennek eredményeként biztonságosan hozzáadhat és eltávolíthat oszlopokat anélkül, hogy minden alkalommal frissítenie kellene a képletet.

1. Nincs korlátozás a keresési mennyiségben. A VLOOKUP() használatakor a keresési feltételek teljes száma nem haladhatja meg a 255 karaktert, különben #ÉRTÉK! Tehát ha az adatok nagyszámú karaktert tartalmaznak, az INDEX() és a MATCH() a legjobb választás.
2. Nagy feldolgozási sebesség. Ha az asztalok viszonylag kicsik, akkor nem valószínű, hogy észreveszi a különbséget. De ha a táblázat több száz vagy több ezer sort tartalmaz, és ennek megfelelően több száz és ezer képlet van, az INDEX () és a MATCH () sokkal gyorsabban megbirkózik, mint a VLOOKUP (). Az a tény, hogy az Excel csak a képletben megadott oszlopokat dolgozza fel, a teljes táblázat feldolgozása helyett. 

A VLOOKUP() teljesítményre gyakorolt ​​hatása különösen akkor lesz észrevehető, ha a munkalap nagyszámú képletet tartalmaz, például VLOOKUP() és SUM(). A VLOOKUP() függvények külön ellenőrzése szükséges a tömb minden egyes értékének elemzéséhez. Tehát az Excelnek hatalmas mennyiségű információt kell feldolgoznia, és ez jelentősen lelassítja a munkát.

Képletes példák 

Már rájöttünk ezeknek a függvényeknek a hasznosságára, így áttérhetünk a legérdekesebb részre: az ismeretek gyakorlati alkalmazására.

Képlet a jobbról balra történő kereséshez

Mint már említettük, a VLOOKUP nem tudja végrehajtani ezt a keresési formát. Tehát, ha a kívánt értékek nincsenek a bal szélső oszlopban, a VLOOKUP() nem ad eredményt. Az INDEX() és MATCH() függvények sokoldalúbbak, és az értékek elhelyezkedése nem játszik nagy szerepet a működésükben.

Például a táblázatunk bal oldalához adunk egy rangoszlopot, és megpróbáljuk kitalálni, hogy hazánk fővárosa népesség szempontjából milyen rangot foglal el.

A G1 cellába írjuk a keresendő értéket, majd a következő képlettel keresünk a C1:C10 tartományban, és visszaadjuk a megfelelő értéket az A2:A10-ből:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Gyors. Ha ezt a képletet több cellára szeretné használni, győződjön meg róla, hogy a tartományokat abszolút címzéssel javította (például 2 USD: 10 USD és 2 USD: 4 USD 10).

INTÉZKEDÉS TÖBB EXPOZÍCIÓ TÖBB EXPOZÍCIÓS  oszlopokban és sorokban való kereséshez

A fenti példákban ezeket a függvényeket a VLOOKUP() helyettesítésére használtuk, hogy értékeket adjunk vissza egy előre meghatározott sortartományból. De mi van akkor, ha mátrixos vagy kétoldalas keresést kell végeznie?

Bonyolultnak hangzik, de az ilyen számítások képlete hasonló a szabványos INDEX() MATCH() képlethez, csak egy különbséggel: a MATCH() formulát kétszer kell használni. Az első alkalommal a sorszám, a második alkalommal az oszlop számának lekéréséhez:

=INDEX(tömb, MATCH(függőleges keresési érték, keresési oszlop, 0), MATCH(vízszintes keresési érték, keresési sor, 0))

Nézzük meg az alábbi táblázatot, és próbáljunk meg egy képletet összeállítani INDEX() EXPRESS() EXPRESS() egy adott ország demográfiai adatainak megjelenítéséhez egy kiválasztott évre vonatkozóan.

A célország a G1 cellában (függőleges keresés), a célév pedig a G2 cellában (vízszintes keresés) található. A képlet így fog kinézni:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Hogyan működik ez a képlet

Mint minden más összetett képletet, ezeket is könnyebb megérteni, ha egyedi egyenletekre bontja őket. És akkor megértheti, hogy az egyes funkciók mit csinálnak:

• EGYEZÉS(G1;A2:A11,0;XNUMX) – keres egy értéket (G1) az A2:A11 tartományban, és ennek az értéknek a számát mutatja, esetünkben ez 2;
• KERESÉS(G2;B1:D1,0;XNUMX) – értéket (G2) keres a B1:D1 tartományban. Ebben az esetben 3 lett az eredmény.

A talált sor- és oszlopszámok az INDEX() képlet megfelelő értékére kerülnek:

=INDEX(B2:D11,2,3;XNUMX;XNUMX)

Ennek eredményeként olyan értéket kapunk, amely a B2:D3 tartomány 2 sorának és 11 oszlopának metszéspontjában van egy cellában. A képlet pedig a kívánt értéket mutatja, ami a D3 cellában van.

Keresés több feltétel alapján az INDEX és a MATCH segítségével

Ha elolvasta a VLOOKUP() című útmutatónkat, valószínűleg több keresési képletet is kipróbált. Ennek a keresési módszernek azonban van egy jelentős korlátja – egy segédoszlop hozzáadásának szükségessége.

De a jó hír az Az INDEX() és MATCH() segítségével több feltételt is kereshet anélkül, hogy szerkesztenie vagy módosítania kellene a munkalapot.

Íme az INDEX() MATCH() általános többfeltételes keresési képlete:

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

A jegyzet: ezt a képletet a billentyűkóddal együtt kell használni CTRL+SHIFT+ENTER.

Tegyük fel, hogy meg kell találnia a keresett értéket 2 feltétel alapján: Vevő и Termék.

Ehhez a következő képlet szükséges:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Ebben a képletben a C2:C10 az a tartomány, amelyben a keresés megtörténik, F1 - ez a feltétel, A2:A10 — az állapot összehasonlításának tartománya, F2 – 2. feltétel, V2:V10 – tartomány a 2. állapot összehasonlításához.

Ne felejtse el megnyomni a kombinációt a képlettel végzett munka végén CTRL+SHIFT+ENTER – Az Excel automatikusan bezárja a képletet kapcsos zárójelekkel, amint az a példában látható:

Ha nem szeretne tömbképletet használni a munkájához, akkor adjon hozzá egy másik INDEX()-et a képlethez, és nyomja meg az ENTER billentyűt, így fog kinézni a példában:

Hogyan működnek ezek a képletek

Ez a képlet ugyanúgy működik, mint a szabványos INDEX() MATCH() képlet. Több feltétel kereséséhez egyszerűen hozzon létre több hamis és igaz feltételt, amelyek a helyes és helytelen egyedi feltételeket képviselik. És akkor ezek a feltételek a tömb összes megfelelő elemére vonatkoznak. A képlet a False és True argumentumokat 0-ra, illetve 1-re konvertálja, és egy tömböt ad ki, ahol 1 a karakterláncban talált egyező értékek. A MATCH() megkeresi az első 1-nek megfelelő értéket, és átadja az INDEX() képletnek. És ez viszont visszaadja a már kívánt értéket a megadott sorban a kívánt oszlopból.

A tömb nélküli képlet attól függ, hogy az INDEX() képes-e önállóan kezelni őket. A képlet második INDEX() értéke hamis (0) egyezik, így a teljes tömböt ezekkel az értékekkel átadja a MATCH() képletnek. 

Ez a képlet mögött rejlő logika meglehetősen hosszadalmas magyarázata. További információért olvassa el a cikket "INDEX MATCH több feltétellel".

AVERAGE, MAX és MIN az INDEX-ben és a MATCH-ban

Az Excelnek saját speciális funkciói vannak az átlagok, maximumok és minimumok keresésére. De mi van akkor, ha adatokat szeretne kapni az értékekhez társított cellából? Ebben az esetben Az AVERAGE, MAX és MIN értékeket az INDEX és a MATCH paraméterekkel együtt kell használni.

INDEX MATCH és MAX

A legnagyobb érték megtalálásához a D oszlopban és a C oszlopban való megjelenítéséhez használja a következő képletet: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH és MIN

A legkisebb érték megtalálásához a D oszlopban és a C oszlopban való megjelenítéséhez használja a következő képletet:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

KERESÉS INDEX és KÍGYÓ

Ha meg szeretné keresni az átlagos értéket a D oszlopban, és megjeleníteni ezt az értéket C-ben:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Az adatok írásának módjától függően a MATCH() harmadik argumentuma 1, 0 vagy -1:

• ha az oszlopok növekvő sorrendben vannak rendezve, állítsa be az 1-et (akkor a képlet kiszámítja a maximális értéket, amely kisebb vagy egyenlő, mint az átlagérték);
• ha a rendezés csökkenő, akkor -1 (a képlet az átlagnál nagyobb vagy azzal egyenlő minimális értéket ad ki);
• ha a keresési tömb olyan értéket tartalmaz, amely pontosan megegyezik az átlaggal, akkor állítsa 0-ra. 

 Példánkban a sokaság csökkenő sorrendben van rendezve, ezért -1-et teszünk. Az eredmény pedig Tokió, mivel a lakossági érték (13,189 000) áll a legközelebb az átlagértékhez (XNUMX).

A VLOOKUP() is tud ilyen számításokat végezni, de csak tömbképletként: VLOOKUP AVERAGE, MIN és MAX paraméterekkel.

INDEX MATCH és ESND/IFERROR

Valószínűleg már észrevette, hogy ha a képlet nem találja a kívánt értéket, akkor hibát dob # N / A. A szokásos hibaüzenetet lecserélheti valami informatívabbra. Például állítsa be az argumentumot a képletben A XNUMX:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Ezzel a képlettel, ha olyan adatokat ad meg, amelyek nem szerepelnek a táblázatban, az űrlap a megadott üzenetet adja.

Ha az összes hibát el akarja fogni, akkor kivéve a A XNUMX lehet használni IFROROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), „Valami hiba történt!”)

De ne feledje, hogy a hibák ilyen módon történő elfedése nem jó ötlet, mert a standard hibák a képlet megsértését jelentik.

Reméljük, hasznosnak találta az INDEX MATCH() függvény használatára vonatkozó útmutatónkat.