Az Excelnek számos funkciója van - körülbelül 450+.
És ezek közül sokan egyszerűen fantasztikusak. A néhány képlettel elvégezhető munka mennyisége még mindig meglep (még az Excel több mint 10 éves használata után is).
És ezek közül a csodálatos funkciók közül kiemelkedik az INDEX MATCH funkciók kombinációja.
Nagy rajongója vagyok az INDEX MATCH kombinációnak, és sokszor világossá tettem.
Még egy cikket is írtam az Index Match VS VLOOKUP -ról, ami egy kis vitát váltott ki (nézd meg a megjegyzések rovatot, hogy van -e tűzijáték).
És ma ezt a cikket kizárólag az Index egyezésre összpontosítva írom, hogy néhány egyszerű és fejlett forgatókönyvet mutassak be, amelyek segítségével használhatja ezt a hatékony képlet kombinációt, és elvégezheti a munkát.
Jegyzet: Az Excelben más keresési képletek is léteznek - például a VLOOKUP és a HLOOKUP, és ezek nagyszerűek. Sokan úgy találják, hogy a VLOOKUP használata könnyebb (és ez igaz is). Úgy gondolom, hogy az INDEX MATCH sok esetben jobb megoldás. De mivel az emberek nehezen viselik, kevesebbet szoknak. Tehát megpróbálom leegyszerűsíteni ezt az oktatóanyagot.Mielőtt megmutatnám, hogyan változtatja meg az INDEX MATCH kombinációja az elemzők és az adattudósok világát, hadd mutassam be először az egyes részeket - az INDEX és a MATCH függvényeket.
INDEX függvény: Koordináták alapján megkeresi az értéket
A legegyszerűbb módja annak, hogy megértsük az Index funkció működését, ha GPS műholdként gondolunk rá.
Amint megmondja a műholdnak a szélességi és hosszúsági koordinátákat, az pontosan tudja, hová kell mennie, és megtalálja ezt a helyet.
Tehát annak ellenére, hogy elképesztően sok lat-hosszú kombinációja van, a műhold pontosan tudná, merre kell keresnie.
Gyorsan megkerestem a munkahelyemet, és ezt kaptam.
Egyébként elég a földrajzból.
Ahogy a műholdaknak szélességi és hosszúsági koordinátákat igényelnek, az Excel INDEX funkciójának szüksége lesz a sorok és oszlopok számára, hogy megtudja, melyik cellára hivatkozik.
Ez pedig az Excel INDEX függvény dióhéjban.
Tehát hadd határozzam meg egyszerű szavakkal az Ön számára.
Az INDEX függvény a sorszám és az oszlopszám alapján megkeresi a cellát az adott tartományban, és visszaadja a benne lévő értéket.
Az INDEX önmagában egy nagyon egyszerű funkció, amely nem rendelkezik segédprogramokkal. Végül is a legtöbb esetben valószínűleg nem ismeri a sorok és oszlopok számát.
De…
Az a tény, hogy más funkciókkal is használható (tipp: MATCH), amelyek megtalálják a sorszámot és az oszlopszámot, rendkívül hatékony Excel -funkcióvá teszi az INDEX -et.
Az alábbiakban az INDEX függvény szintaxisa látható:
= INDEX (tömb, sorszám, [oszlopszám]) = INDEX (tömb, sorszám, [oszlopszám], [területszám])
- tömb - a cellatartomány vagy tömbállandó.
- sor_száma - a sorszám, amelyből az értéket le kell kérni.
- [col_num] - az oszlop száma, amelyből le kell kérni az értéket. Bár ez nem kötelező argumentum, de ha a sor_száma nincs megadva, akkor meg kell adni.
- [terület_szám] - (Nem kötelező) Ha a tömb argumentum több tartományból áll, akkor ezt a számot használjuk a referencia kiválasztására az összes tartományból.
Az INDEX függvénynek 2 szintaxisa van (csak FYI).
Az elsőt a legtöbb esetben használják. A másodikat csak haladó esetekben használják (például háromirányú keresést végeznek), amelyet az oktatóanyag későbbi példáiban tárgyalunk.
De ha még nem ismeri ezt a funkciót, ne feledje az első szintaxist.
Az alábbiakban egy videó ismerteti az INDEX funkció használatát
MATCH függvény: megkeresi a pozíciót a keresési érték alapján
Visszatérve a hosszúság és szélesség korábbi példájára, a MATCH az a funkció, amely megtalálja ezeket a pozíciókat (az Excel táblázatkezelő világában).
Egyszerű nyelven az Excel MATCH függvény megtalálja a cella pozícióját egy tartományban.
És mi alapján találná meg a sejt pozícióját?
A keresési érték alapján.
Például, ha van egy listája az alábbiak szerint, és szeretné megtalálni a „Mark” név pozícióját, akkor használhatja a MATCH funkciót.
A függvény 3 -at ad vissza, mivel ez a cella pozíciója a Mark névvel.
A MATCH függvény felülről lefelé kezdi keresni a megadott tartományban lévő keresési értéket (ez a „Mark”) (ebben a példában A1: A9). Amint megtalálja a nevet, visszaadja a pozíciót az adott tartományban.
Az alábbiakban az MATCH függvény szintaxisa látható az Excelben.
= MATCH (lookup_value, lookup_array, [match_type])
- lookup_value - Az az érték, amelyhez egyezést keres a lookup_array -ben.
- lookup_array - Az a cellatartomány, amelyben a keresési_értéket keresi.
- [match_type] - (Nem kötelező) Ez határozza meg, hogy az Excel hogyan keresse meg az egyező értéket. Három értéket vehet fel: -1, 0 vagy 1.
Az egyezéstípus érvelésének megértése a MATCH függvényben
Van még egy dolog, amit tudnia kell a MATCH függvényről, és arról, hogyan megy keresztül az adatokon, és hogyan találja meg a cella pozícióját.
A MATCH függvény harmadik argumentuma 0, 1 vagy -1 lehet.
Az alábbiakban bemutatjuk, hogyan működnek ezek az érvek:
- 0 - ez az érték pontos egyezését fogja keresni. Ha pontos egyezést talál, a MATCH függvény visszaadja a cella pozícióját. Különben hibát ad vissza.
- 1 - ez a legnagyobb érték, amely kisebb vagy egyenlő a keresési értékkel. Ennek működéséhez az adattartományt növekvő sorrendbe kell rendezni.
- -1 - ez megtalálja a legkisebb értéket, amely nagyobb vagy egyenlő a keresési értékkel. Ennek működéséhez az adattartományt csökkenő sorrendbe kell rendezni.
Az alábbiakban egy videó található, amely elmagyarázza a MATCH függvény használatát (az egyezés típusa argumentummal együtt)
Összefoglalva és egyszerű szavakkal fogalmazva:
- Az INDEX -nek szüksége van a cella pozíciójára (sor és oszlop száma), és megadja a cella értékét.
- A MATCH megkeresi a pozíciót egy keresési érték használatával.
Kombináljuk őket egy erőmű létrehozásához (INDEX + MATCH)
Most, hogy alapvető ismeretekkel rendelkezik arról, hogy az INDEX és a MATCH funkciók hogyan működnek egyénileg, egyesítsük ezt a kettőt, és ismerjünk meg minden csodálatos dolgot.
Ennek jobb megértése érdekében van néhány példa, amelyek az INDEX MATCH kombinációt használják.
Kezdem egy egyszerű példával, majd mutatok néhány fejlett használati esetet is.
Kattintson ide a mintafájl letöltéséhez
1. példa: Egyszerű keresés az INDEX MATCH kombináció használatával
Végezzünk egyszerű keresést az INDEX/MATCH használatával.
Az alábbiakban egy táblázat található, ahol tíz diák jegyei vannak.
Ebből a táblázatból szeretném megtalálni Jim jelzéseit.
Az alábbi képlet segítségével könnyen elvégezhető:
= INDEX ($ A $ 2: $ B $ 11, MATCH ("Jim", $ A $ 2: $ A $ 11,0), 2)
Ha most úgy gondolja, hogy ez könnyen elvégezhető a VLOOKUP funkció használatával, akkor igaza van! Ez nem a legjobb módja az INDEX MATCH félelmetességnek. Annak ellenére, hogy az INDEX MATCH rajongója vagyok, ez egy kicsit nehezebb, mint a VLOOKUP. Ha csak a jobb oldali oszlopból szeretné lekérni az adatokat, javasoljuk a VLOOKUP használatát.
Azért mutattam be ezt a példát, amely szintén könnyen elvégezhető a VLOOKUP segítségével, hogy megmutassam, hogyan működik az INDEX MATCH egyszerű környezetben.
Most hadd mutassam be az INDEX MATCH előnyeit.
Tegyük fel, hogy ugyanazokkal az adatokkal rendelkezik, de ahelyett, hogy oszlopokban szerepelne, hanem sorokban (az alábbi ábra szerint).
Tudod mit, továbbra is használhatod az INDEX MATCH kombinációt, hogy megszerezd Jim jegyeit.
Az alábbi képlet megadja az eredményt:
= INDEX ($ B $ 1: $ K $ 2,2, MATCH ("Jim", $ B $ 1: $ K $ 1,0))
Ne feledje, hogy módosítania kell a tartományt, és váltania kell a sor/oszlop részeket, hogy ez a képlet a vízszintes adatoknál is működjön.
Ezt a VLOOKUP segítségével nem lehet megtenni, de a HLOOKUP segítségével mégis könnyen megteheti.
Az INDEX MATCH kombináció könnyen kezelheti a vízszintes és a függőleges adatokat is.Kattintson ide a mintafájl letöltéséhez
2. példa: Keresés balra
Gyakoribb, mint gondolnád.
Sokszor előfordulhat, hogy az adatokat a lekérdezési értékkel rendelkező oszloptól balra lévő oszlopból kell lekérni.
Valami az alábbiak szerint:
Ahhoz, hogy megtudja Michael eladásait, a bal oldalon kell keresnie.
Ha a VLOOKUP -ra gondol, hadd állítsam meg.
A VLOOKUP nem a bal oldali értékek keresésére és lekérésére szolgál.
Meg tudod csinálni a VLOOKUP használatával?
Igen tudsz!
De ez hosszú és csúnya képletté alakulhat.
Tehát ha keresést szeretne végezni, és adatokat szeretne lekérni a bal oldali oszlopokból, akkor jobb, ha az INDEX MATCH kombinációt használja.
Az alábbiakban a képlet alapján kapjuk meg Michael eladási számát:
= INDEX ($ A $ 2: $ C $ 11, MATCH ("Michael", C2: C11,0), 2)
3. példa: Kétirányú keresés
Eddig láthattunk olyan példákat, amikor az adatokat le akartuk szerezni a keresési értékkel rendelkező oszlop melletti oszlopból.
De a való életben az adatok gyakran több oszlopon keresztül terjednek.
Az INDEX MATCH könnyen kezelheti a kétirányú keresést.
Az alábbiakban a tanuló három különböző tantárgyból elért jegyeinek adatállománya található.
Ha gyorsan szeretné lekérni a tanuló jegyeit mindhárom tantárgyból, akkor ezt megteheti az INDEX MATCH segítségével.
Az alábbi képlet megadja a Jim jelöléseit mind a három alany esetében (másolja és illessze be egy cellába, és húzza a többi cellát, vagy másolja és illessze be más cellákba).
= INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))
Hadd magyarázzam el gyorsan ezt a képletet is.
Az INDEX képlet a B2: D11 tartományt használja.
Az első MATCH a nevet használja (Jim az F3 cellában), és lekéri annak pozícióját a nevek oszlopban (A2: A11). Ez lesz az a sorszám, amelyből le kell tölteni az adatokat.
A második MATCH képlet az alany nevét használja (a G2 cellában), hogy megkapja az adott tárgynév pozícióját a B1: D1 -ben. Például a matematika 1, a fizika 2 és a kémia 3.
Mivel ezek a MATCH pozíciók bekerülnek az INDEX függvénybe, a tanuló neve és a tantárgy neve alapján visszaadja a pontszámot.
Ez a képlet dinamikus, ami azt jelenti, hogy ha megváltoztatja a tanuló nevét vagy a tantárgy nevét, akkor is működik, és lekéri a helyes adatokat.
Egy nagyszerű dolog az INDEX/MATCH használatával kapcsolatban az, hogy még ha fel is cseréli az alanyok nevét, továbbra is a helyes eredményt adja.4. példa: Keresési érték több oszlopból/kritériumból
Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és le szeretné kérni a „Mark Long” jelzéseket.
Mivel az adatok két oszlopban vannak, nem tudom megkeresni Markot és lekérni az adatokat.
Ha így teszem, akkor a Mark Frost jelzéseket fogom megkapni, és nem Mark Long -ot (mert a MATCH függvény megadja az eredményt a MARK -nak, amellyel találkozik).
Ennek egyik módja a segítő oszlop létrehozása és a nevek kombinálása. Miután megvan a segítő oszlop, használhatja a VLOOKUP funkciót, és lekérheti a jelölések adatait.
Ha szeretné megtudni, hogyan kell ezt megtenni, olvassa el ezt az oktatóanyagot a VLOOKUP használatáról több feltétel mellett.
De az INDEX/MATCH kombinációval nincs szüksége segítő oszlopra. Létrehozhat olyan képletet, amely több feltételt is kezel a képletben.
Az alábbi képlet megadja az eredményt.
= INDEX ($ C $ 2: $ C $ 11, MATCH ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))
Hadd magyarázzam el gyorsan, mit tesz ez a képlet.
A képlet MATCH része egyesíti a keresési értéket (Mark és Long), valamint a teljes keresési tömböt. Ha $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 keresési tömbként szolgál, akkor valójában ellenőrzi a keresési értéket a kereszt- és vezetéknév kombinált karakterláncával (a cső szimbólummal elválasztva).
Ez biztosítja, hogy a segítő oszlopok használata nélkül a megfelelő eredményt kapja.
Ezt a fajta lekérdezést (ahol több oszlop/feltétel van) a VLOOKUP segítségével is elvégezheti, de segítő oszlopot kell használnia. Az INDEX MATCH kombináció kissé megkönnyíti ezt segítő oszlopok nélkül.5. példa: Értékek lekérése a teljes sorból/oszlopból
A fenti példákban az INDEX függvényt használtuk, hogy értéket kapjunk egy adott cellából. Megadja a sor- és oszlopszámot, és az adott cellában lévő értéket adja vissza.
De többre is képes.
Az INDEX függvénnyel az egész sor vagy oszlop értékeit is lekérheti.
És hogyan lehet ez hasznos, kérdezed!
Tegyük fel, hogy szeretné megtudni Jim összes pontszámát mindhárom témában.
Az INDEX függvénnyel először lekérheti a Jim összes jelet, majd a SUM funkcióval összegeket kaphat.
Lássuk, hogyan kell ezt megtenni.
Az alábbiakban három tantárgy összes tanulójának pontszámát találom.
Az alábbi képlet megadja a Jim összes pontszámát mindhárom témában.
= SUM (INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))
Hadd magyarázzam meg, hogyan működik ez a képlet.
A trükk itt az, hogy a 0 -t használja oszlopszámként.
Ha 0 -t használ oszlopszámként az INDEX függvényben, akkor az összes sorértéket visszaadja. Hasonlóképpen, ha a 0 -t használja sorszámként, akkor visszaadja az oszlop összes értékét.
Tehát a képlet alábbi része egy tömb értéket ad vissza - {97, 70, 73}
INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)
Ha csak beírja ezt a fenti képletet egy Excel cellába, és nyomja meg az Enter billentyűt, akkor megjelenik egy #ÉRTÉK! hiba. Ez azért van, mert nem egyetlen értéket ad vissza, hanem egy értéktömböt.
De ne aggódjon, az értékek továbbra is megvannak. Ezt a képlet kiválasztásával és az F9 billentyű megnyomásával ellenőrizheti. Megmutatja a képlet eredményét, amely ebben az esetben három értékű tömb - {97, 70, 73}
Most, ha ezt az INDEX képletet a SUM függvénybe csomagolja, akkor megadja a Jim összes pontszámának összegét.
Ugyanezt használhatja Jim legmagasabb, legalacsonyabb és átlagos pontszámának megszerzésére is.
Ahogy ezt egy hallgató esetében megtettük, ezt megteheti egy tantárgy esetében is. Például, ha szeretné az átlagpontszámot egy tárgyban, akkor a sorszámot 0 -ként tarthatja az INDEX képletben, és megadja az adott tárgy összes oszlopértékét.
Kattintson ide a mintafájl letöltéséhez
6. példa: Keresse meg a diák fokozatát (hozzávetőleges egyezési technika)
Eddig a MATCH képletet használtuk a keresési érték pontos egyezéséhez.
De arra is használhat hozzávetőleges egyezést.
Most akkor mi a fene az Approximate Match?
Hadd magyarázzam.
Ha olyan dolgokat keres, mint a nevek vagy azonosítók, akkor pontos egyezést keres. Néha azonban ismernie kell a keresési értékek tartományát. Ez általában a számokkal van így.
Osztályfőnökként például érdemes tudni, hogy egy -egy tantárgyban melyik tanuló milyen osztályzatot kap, és az osztályzatot a pontszám alapján határozzák meg.
Az alábbiakban egy példa látható, ahol az összes diák osztályzatát akarom, és az osztályozás a jobb oldali táblázat alapján dől el.
Tehát ha egy tanuló 33 -nál kevesebbet kap, az osztályzat F, és ha 50 -nél kevesebb, de 33 -nál több, akkor E, és így tovább.
Az alábbiakban látható a képlet, amely ezt megteszi.
= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8,1), 1)
Hadd magyarázzam meg, hogyan működik ez a képlet.
A MATCH függvényben 1 -et használtunk [match_type] argumentumként. Ez az argumentum a legnagyobb értéket adja vissza, amely kisebb vagy egyenlő a keresési értékkel.
Ez azt jelenti, hogy a MATCH képlet átmegy a jelölések tartományán, és amint talál egy olyan tartományt, amely egyenlő vagy kisebb, mint a keresési jelek értéke, ott megáll, és visszaadja pozícióját.
Tehát ha a keresési jel értéke 20, akkor a MATCH függvény 1 -et ad vissza, és ha 85 -öt, akkor 5 -öt.
Az INDEX függvény pedig ezt a pozícióértéket használja a pontszám megszerzéséhez.
FONTOS: Ahhoz, hogy ez működjön, az adatokat növekvő sorrendbe kell rendezni. Ha nem, akkor rossz eredményeket kaphat.Ne feledje, hogy a fentiek az alábbi VLOOKUP képlet segítségével is elvégezhetők:
= VLOOKUP (B2, $ E $ 3: $ F $ 8,2, TRUE)
De a MATCH funkció egy lépéssel tovább léphet, ha hozzávetőleges egyezésről van szó.
Emellett csökkenő adatokkal is rendelkezhet, és az INDEX MATCH kombinációval megkeresheti az eredményt. Például, ha megváltoztatom az érdemjegy táblázat sorrendjét (az alábbiak szerint), akkor is megtalálom a tanulók érdemjegyeit.
Ehhez csak a [match_type] argumentumot kell -1 -re módosítanom.
Az alábbiakban a képletet használom:
= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8, -1), 1)A VLOOKUP hozzávetőleges egyezést is végezhet, de csak akkor, ha az adatok növekvő sorrendben vannak rendezve (de nem működik, ha az adatok csökkenő sorrendben vannak rendezve).
7. példa: Kis- és nagybetűk érzékeny keresések
Eddig minden keresésünk kis- és nagybetűket nem érzékeny.
Ez azt jelenti, hogy a keresési érték Jim, JIM vagy jim volt, nem számított. Ugyanazt az eredményt kapja.
De mi van akkor, ha azt szeretné, hogy a keresés megkülönböztesse a kis- és nagybetűket.
Ez általában akkor fordul elő, ha nagy adathalmazokkal rendelkezik, és lehetőség van az ismétlésre vagy a különböző nevekre/azonosítókra (az egyetlen különbség az)
Tegyük fel például, hogy a következő adathalmazom van a diákokról, ahol két diák van Jim névvel (az egyetlen különbség az, hogy az egyik Jim, a másik pedig Jim néven van megadva).
Vegye figyelembe, hogy két tanuló van azonos nevű - Jim (A2 és A5 cella).
Mivel a normál keresés nem működik, kis- és nagybetűket kell keresnie.
Az alábbi képlet megadja a megfelelő eredményt. Mivel ez egy tömbképlet, a Control + Shift + Enter billentyűkombinációt kell használnia.
= INDEX ($ B $ 2: $ B $ 11, MATCH (IGAZ, PONTOS (D3, A2: A11), 0), 1)
Hadd magyarázzam meg, hogyan működik ez a képlet.
Az EXACT függvény ellenőrzi, hogy pontosan egyezik -e a keresési érték (ami ebben az esetben „jim”). Átmegy az összes névre, és hamisat ad vissza, ha nem egyezés, és IGAZ, ha egyezés.
Tehát a PONTOS függvény kimenete ebben a példában: - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Ne feledje, hogy csak egy IGAZ van, amikor az EXACT funkció tökéletes egyezést talált.
A MATCH függvény ezután megtalálja az IGAZ pozícióját a PONTOS függvény által visszaadott tömbben, ami ebben a példában 4.
Ha megvan a pozíció, az INDEX függvény megkeresi a jeleket.
8. példa: Keresse meg a legközelebbi egyezést
Most haladjunk egy kicsit.
Tegyük fel, hogy rendelkezik olyan adatkészlettel, ahol azt a személyt szeretné megtalálni, akinek a munkatapasztalata a legközelebb áll a szükséges tapasztalathoz (a D2 cella szerepel).
Bár a keresési képleteket nem erre a célra készítették, ezt kombinálhatja más funkciókkal (például MIN és ABS).
Az alábbiakban az a képlet található, amely megkeresi az előírthoz legközelebb álló személyt, és visszaadja a személy nevét. Ne feledje, hogy a tapasztalatnak a legközelebb kell lennie (ez lehet kevesebb vagy több).
= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15))), ABS (D2- $ B $ 2: $ B $ 15), 0))
Mivel ez egy tömbképlet, a Control + Shift + Enter billentyűkombinációt kell használnia.
Ennek a képletnek az a trükkje, hogy módosítsa a keresési értéket és a keresési tömböt, hogy megtalálja a minimális tapasztalati különbséget a szükséges és a tényleges értékekben.
Mielőtt elmagyaráznám a képletet, értsük meg, hogyan kell ezt manuálisan csinálni.
Végignézi a B oszlop minden celláját, és megtalálja a különbségtételt a szükséges és az adott személy közötti tapasztalat között. Ha megvan az összes különbség, megtalálja a minimálisat, és lehívja az adott személy nevét.
Ezzel a formulával pontosan ezt tesszük.
Hadd magyarázzam.
A MATCH képlet keresési értéke MIN (ABS (D2-B2: B15)).
Ez a rész megadja a minimális különbséget az adott tapasztalat (ami 2,5 év) és az összes többi tapasztalat között. Ebben a példában 0,3 -at ad vissza
Ne feledje, hogy az ABS -t használtam annak biztosítására, hogy a legközelebbit keresem (ami lehet több vagy kevesebb, mint az adott tapasztalat).
Most ez a minimális érték lesz a keresési értékünk.
A MATCH funkció keresési tömbje ABS (D2- $ B $ 2: $ B $ 15).
Ezzel számokból álló tömböt kapunk, amelyekből 2,5 (a szükséges tapasztalat) kivonásra került.
Tehát most van egy keresési érték (0,3) és egy keresési tömb ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})
A MATCH függvény megtalálja a 0.3 pozíciót ebben a tömbben, ami egyben a legközelebbi tapasztalattal rendelkező személy neve is.
Ezt a pozíciószámot az INDEX függvény használja a személy nevének visszaadására.
Kapcsolódó olvasmány: Keresse meg a legközelebbi egyezést az Excelben (példák keresési képletek segítségével)
Kattintson ide a mintafájl letöltéséhez
9. példa: INDEX MATCH használata helyettesítő karakterekkel
Ha részleges egyezés esetén szeretne keresni egy értéket, akkor helyettesítő karaktereket kell használnia.
Például az alábbiakban a vállalat nevének és piaci kapitalizációjának adatkészlete található, és szeretné megkapni a piaci sapkát. a jobb oldali három vállalat adatai.
Mivel ezek nem pontos egyezések, ebben az esetben nem végezhet rendszeres keresést.
A megfelelő adatokat azonban csillag (*) használatával kaphatja meg, ami helyettesítő karakter.
Az alábbiakban az a képlet található, amely megadja az adatokat a vállalati neveknek a fő oszlopból való megfeleltetésével, és a piaci sapka számának lekérésével.
= INDEX ($ B $ 2: $ B $ 10, MATCH (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)
Hadd magyarázzam meg, hogyan működik ez a képlet.
Mivel nincs pontos egyezés a keresési értékekkel, ezt használtam D2 és "*" keresési értékként a MATCH függvényben.
A csillag egy helyettesítő karakter, amely tetszőleges számú karaktert jelent. Ez azt jelenti, hogy az Apple* a képletben bármilyen szöveges karakterláncot jelentene, amely az Apple szóval kezdődik, és tetszőleges számú karakter lehet utána.
Így amikor Alma* keresési értékként használják, és a MATCH képlet az A oszlopban keresi, az „Apple Inc.” pozíciót adja vissza, mivel az Apple szóval kezdődik.
A helyettesítő karakterek segítségével szöveges karakterláncokat is kereshet, ahol a keresési érték között van. Ha például az * Apple * szót használja keresési értékként, akkor minden olyan karakterláncot megtalál, amelyben az alma szó szerepel.
Megjegyzés: Ez a technika akkor működik jól, ha csak egy egyezési példánya van. De ha több egyezési példánya van (például az Apple Inc és az Apple Corporation, akkor a MATCH függvény csak az első egyező példány pozícióját adja vissza.
10. példa: Háromirányú keresés
Ez az INDEX MATCH fejlett használata, de továbbra is lefedem, hogy megmutassam ennek a kombinációnak az erejét.
Ne feledje, mondtam, hogy az INDEX függvénynek két szintaxisa van:
= INDEX (tömb, sorszám, [oszlopszám]) = INDEX (tömb, sorszám, [oszlopszám], [területszám])
Eddig minden példánkban csak az elsőt használtuk.
A háromirányú kereséshez azonban a második szintaxist kell használnia.
Először hadd magyarázzam meg, mit jelent a háromirányú megjelenés.
Kétirányú keresésben az INDEX MATCH képletet használjuk, hogy megkapjuk a jegyeket, amikor megvan a tanuló neve és a tantárgy neve. Például Jim jegyeinek lekérése a matematikában kétirányú keresés.
A háromirányú megjelenés újabb dimenziót adna hozzá. Tegyük fel például, hogy rendelkezik egy adathalmazzal, amint az alább látható, és szeretné tudni Jim pontszámát a matematikából félidős vizsgán, akkor ez háromirányú keresés lenne.
Az alábbi képlet megadja az eredményt.
= INDEX (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH (G $ 4 , $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "Unit Test", 1, IF (G $ 3 = "Mid Term", 2,3))))
A fenti képlet három dolgot vizsgált - a tanuló nevét, a tantárgyat és a vizsgát. Miután megtalálta a megfelelő értéket, visszaadja azt a cellában.
Hadd magyarázzam meg, hogyan működik ez a képlet a képlet részekre bontásával.
- tömb - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): Ebben az esetben egyetlen tömb használata helyett három tömböt használtam zárójelben.
- row_num - MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0): A MATCH függvénnyel megkeresheti a diák nevének pozícióját a $ F $ 5 cellában a diákok névsorában.
- col_num - MATCH (G $ 4, B $ 2: $ D $ 2,0): A MATCH függvénnyel megtalálható az alany neve a $ B $ 2 cellában az alany nevének listájában.
- [terület_szám] - HA (G $ 3 = ”Unit Test”, 1, IF (G $ 3 = ”Mid Term”, 2,3)): A terület szám értéke megmondja az INDEX függvénynek, hogy a három tömb közül melyiket használja az érték lekéréséhez. Ha a vizsga Unit Term, az IF függvény 1 -et ad vissza, és az INDEX függvény az első tömböt használja az érték lekéréséhez. Ha a vizsga félidős, a HA képlet 2-t ad vissza, ellenkező esetben 3-at.
Ez egy fejlett példa az INDEX MATCH használatára, és valószínűleg nem talál olyan helyzetet, amikor ezt használni kell. De még mindig jó tudni, hogy mire képesek az Excel képletei.
Kattintson ide a mintafájl letöltéséhez
Miért jobb az INDEX/MATCH, mint a VLOOKUP?
Vagy ez?
Igen, az - a legtöbb esetben.
Egy idő múlva bemutatom az esetemet.
De mielőtt ezt tenném, hadd mondjam el ezt … A VLOOKUP rendkívül hasznos funkció, és szeretem. Az Excelben sok mindenre képes, és én is időnként használom. Ennek ellenére ez nem jelenti azt, hogy nem lehet jobb, és az INDEX/MATCH (nagyobb rugalmassággal és funkcionalitással) jobb.
Tehát ha alapvető keresést szeretne végezni, akkor jobb, ha használja a VLOOKUP -ot.
Az INDEX/MATCH a VLOOKUP a szteroidokról. És ha már megtanulta az INDEX/MATCH programot, akkor mindig szívesebben használja (különösen a rugalmasság miatt).
Anélkül, hogy túl messzire nyúlnánk, hadd mondjam el gyorsan, hogy miért jobb az INDEX/MATCH, mint a VLOOKUP.
Az INDEX/MATCH a keresési érték balra (valamint jobbra) nézhet
A fenti példa egyikében kitértem rá.
Ha van olyan értéke, amely a keresési érték bal oldalán található, akkor ezt nem teheti meg a VLOOKUP használatával
Legalábbis nem csak a VLOOKUP segítségével.
Igen, kombinálhatja a VLOOKUP -ot más képletekkel, és elvégezheti, de bonyolult és zavaros lesz.
Az INDEX/MATCH viszont mindenhol kereshető (legyen az bal, jobb, fel vagy le)
Az INDEX/MATCH függőleges és vízszintes tartományokkal is használható
Ismételten, a VLOOKUP teljes tiszteletben tartásával nem erre készült.
Végül is a V in VLOOKUP függőleges.
A VLOOKUP csak függőleges adatokon megy keresztül, míg az INDEX/MATCH függőlegesen és vízszintesen is.
Természetesen a HLOOKUP funkció gondoskodik a vízszintes keresésről, de akkor ez nem VLOOKUP … igaz?
Tetszik, hogy az INDEX MATCH kombináció elég rugalmas ahhoz, hogy függőleges és vízszintes adatokkal is dolgozzon.
A VLOOKUP nem működik csökkenő adatokkal
Ami a hozzávetőleges egyezést illeti, a VLOOKUP és az INDEX/MATCH azonos szinten vannak.
De az INDEX MATCH a lényeget veszi figyelembe, mivel csökkenő sorrendben lévő adatokat is képes kezelni.
Ezt mutatom be az oktatóanyag egyik példájában, ahol meg kell találnunk a tanulók osztályzatát az osztályozó táblázat alapján. Ha a táblázat csökkenő sorrendben van rendezve, a VLOOKUP nem működik (de az INDEX MATCH igen).
Az INDEX/MATCH valamivel gyorsabb lehet
Őszinte leszek. Ezt a tesztet nem magam hajtottam végre.
Bízom egy Excel mester - Charley Kyd - bölcsességében.
A sebességkülönbség a VLOOKUP és az INDEX/MATCH között alig észrevehető, ha kis adatkészlete van. De ha több ezer sora és sok oszlopa van, akkor ez döntő tényező lehet.
Charley Kyd cikkében ezt írja:
„A legrosszabb esetben az INDEX-MATCH módszer körülbelül olyan gyors, mint a VLOOKUP; a legjobb esetben sokkal gyorsabb. ”
Az INDEX/MATCH független a tényleges oszloppozíciótól
Ha rendelkezik az alább látható adatkészlettel, amikor lekéri a fizika Jim pontszámát, akkor ezt a VLOOKUP használatával teheti meg.
Ehhez pedig megadhatja az oszlop számát 3 -nak a VLOOKUP -ban.
Minden rendben.
De mi van, ha törlöm a Matematika oszlopot?
Ebben az esetben a VLOOKUP képlet megszakad.
Miért? - Mivel a harmadik oszlop használata nehézkódos volt, és amikor törlök egy oszlopot, a harmadik oszlop lesz a második oszlop.
Az INDEX/MATCH használata ebben az esetben jobb, mivel az oszlopszámot dinamikussá teheti a MATCH használatával. Tehát az oszlopszám helyett ellenőrzi a tárgy nevét, és ezt használja az oszlopszám visszaadására.
Biztosan megteheti ezt a VLOOKUP és a MATCH kombinálásával, de ha mégis kombinál, miért ne tegye meg az INDEX -el, amely sokkal rugalmasabb.
Az INDEX/MATCH használatakor biztonságosan beilleszthet/törölhet oszlopokat az adathalmazba.
Mindezek ellenére a VLOOKUP olyan népszerű.
És ez nagy ok.
A VLOOKUP használata egyszerűbb
A VLOOKUP legfeljebb négy érvet tartalmaz. Ha e négy körül körbe tudja hajtani a fejét, akkor jó.
És mivel az alapvető keresési esetek többségét a VLOOKUP is kezeli, gyorsan a legnépszerűbb Excel funkcióvá vált.
Én Excel királyainak hívom.
Az INDEX/MATCH viszont kicsit nehezebben használható. Előfordulhat, hogy megreked, ha elkezdi használni, de egy kezdő számára a VLOOKUP sokkal könnyebben magyarázható és tanulható.
És ez nem egy nulla összegű játék.
Tehát, ha új vagy a keresési világban, és nem tudod, hogyan kell használni a VLOOKUP -ot, akkor ezt inkább tanuld meg.
Részletes útmutatóm van a VLOOKUP használatához Excelben (sok példával)
Ebben a cikkben nem az a szándékom, hogy két félelmetes funkciót egymással szemben állítsak. Meg akartam mutatni nektek az INDEX MATCH kombináció erejét és minden nagyszerű dolgot, amire képes.
Remélem, hasznosnak találta ezt a cikket.
Mondja el véleményét a megjegyzések részben, és ha bármilyen hibát talál ebben az oktatóanyagban, kérjük, tudassa velem.
