Töltse le a példa munkafüzetet
Ez a bemutató bemutatja, hogyan kell használni Excel VLOOKUP funkció az Excelben az érték megkereséséhez.
A VLOOKUP funkció áttekintése
A VLOOKUP funkció Vlookup a függőleges keresést jelenti. Értéket keres a táblázat bal szélső oszlopában. Ezután a megadott értékű oszlop értékét adja vissza a talált érték jobb oldalán. Ez ugyanaz, mint a hlookup, kivéve, hogy vízszintesen, hanem függőlegesen keresi az értékeket.
(Figyelje meg, hogyan jelennek meg a képletbemenetek)
VLOOKUP funkció szintaxisa és érvei:
| 1 | = VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) |
lookup_value - A keresni kívánt érték.
table_array - Az az adattartomány, amely a keresni kívánt értéket és a vlookup által visszaadni kívánt értéket egyaránt tartalmazza. A keresési értékeket tartalmazó oszlopnak a bal szélső oszlopnak kell lennie.
col_index_num - Annak az adattartománynak az oszlopszáma, amelyből értéket szeretne visszaadni.
range_lookup - Igaz vagy hamis. A HAMIS pontos egyezést keres. A TRUE a legközelebbi egyezést keresi, amely kisebb vagy egyenlő a lookup_value értékkel. Ha az IGAZ lehetőséget választja, akkor a bal szélső oszlopot (a keresési oszlopot) növekvő módon kell rendezni (a legalacsonyabbtól a legnagyobbig).
Mi a VLOOKUP funkció?
A táblázatok világának egyik régebbi funkciójaként a VLOOKUP funkciót használják Vertikus Keresések. Van néhány korlátozása, amelyeket gyakran más funkciók, például INDEX/MATCH, leküzdenek. Ennek az előnye azonban az, hogy egyetlen funkció, amely önmagában képes pontos egyezési keresést végezni. Ez általában az egyik első funkció, amelyet az emberek megtanulnak.
Alapvető példa
Nézzünk egy adatmintát egy évfolyamkönyvből. Számos példával foglalkozunk bizonyos diákok információinak kinyerésére.
Ha meg akarjuk találni, hogy Bob melyik osztályba tartozik, akkor a következő képletet írjuk fel:
| 1 | = VLOOKUP ("Bob", A2: C5, 2, FALSE) |
Fontos megjegyezni, hogy a keresett elemnek (Bob) a keresési tartományunk első oszlopában kell lennie (A2: C5). Megmondtuk a függvénynek, hogy vissza akarunk adni egy értéket a 2 -bőlnd oszlop, amely jelen esetben a B. oszlop. Végül jeleztük, hogy an pontos egyezés az utolsó argumentumként a Hamis beállítást. Itt a válasz az „Olvasás” lesz.
Oldalsó tipp: A hamis helyett a 0 számot is használhatja végső érvként, mivel ezek értéke azonos. Vannak, akik ezt preferálják, mert gyorsabb az írás. Csak tudd, hogy mindkettő elfogadható.
Váltott adatok
Ahhoz, hogy az első példánkhoz némi magyarázatot adjunk, a keresési elemnek nem kell szerepelnie a táblázat A oszlopában, csak a keresési tartomány első oszlopában. Használjuk ugyanazt az adathalmazt:
Most keressük a természettudományok osztályzatát. A képletünk az lenne
| 1 | = VLOOKUP ("Tudomány", B2: C5, 2, HAMIS) |
Ez még mindig érvényes képlet, mivel keresési tartományunk első oszlopa a B oszlop, ahol a „Tudomány” keresési kifejezés található. Visszaadunk egy értéket a 2 -bőlnd a keresési tartomány oszlopa, amely jelen esetben a C oszlop. A válasz ekkor „A-”.
Helyettesítő karakter használata
A VLOOKUP funkció támogatja a „*” és „?” Helyettesítő karakterek használatát keresések során. Tegyük fel például, hogy elfelejtettük Frank nevét írni, és csak olyan nevet akartunk keresni, amely „F” betűvel kezdődik. Írhatnánk a képletet
| 1 | = VLOOKUP ("F*", A2: C5, 2, FALSE) |
Ez képes lenne megtalálni a Frank nevet az 5. sorban, majd visszaadni az értéket a 2 -bőlnd relatív oszlop. Ebben az esetben a válasz „Tudomány” lesz.
Nem pontos egyezés
Legtöbbször meg kell győződnie arról, hogy a VLOOKUP utolsó érve hamis (vagy 0), hogy pontos egyezést kapjon. Előfordulhat azonban néhány olyan eset, amikor nem pontos egyezést keres. Ha rendezett adatok listája van, akkor a VLOOKUP használatával visszaadhatja az elem eredményét, amely vagy ugyanaz, vagy a következő legkisebb. Ezt gyakran használják növekvő számtartományok kezelésekor, például adótáblázatban vagy jutalékbónuszokban.
Tegyük fel, hogy szeretné megtalálni a D2 cellába beírt jövedelem adókulcsát. A D4 képlet a következő lehet:
| 1 | = VLOOKUP (D2, A2: B6, 2, TRUE) |
A különbség ebben a képletben az, hogy utolsó érvünk „igaz”. Konkrét példánkban azt láthatjuk, hogy amikor egyénünk 45 000 dollár bevételt ír elő, akkor 15%-os adókulcsot kell fizetniük.
Jegyzet: Bár általában pontos egyezést szeretnénk, ha a hamis argumentum lenne, elfelejted megadni a 4 -etth argumentum a VLOOKUP -ban, az alapértelmezett True. Ez váratlan eredményeket eredményezhet, különösen szöveges értékek kezelésekor.
Dinamikus oszlop
A VLOOKUP megköveteli, hogy adjon meg egy érvet, hogy melyik oszlopból szeretne értéket visszaadni, de előfordulhat olyan alkalom, amikor nem tudja, hol lesz az oszlop, vagy engedélyezni szeretné a felhasználó számára, hogy módosítsa, melyik oszlopból térjen vissza. Ezekben az esetekben hasznos lehet a MATCH függvény használata az oszlopszám meghatározásához.
Tekintsük újra az évfolyamkönyvi példánkat, néhány bemenettel az E2 -ben és az E4 -ben. Az oszlopszám megszerzéséhez írhatunk egy képletet
| 1 | = MATCH (E2, A1: C1, 0) |
Ez megpróbálja megtalálni a „Grade” pontos pozícióját az A1: C1 tartományban. A válasz 3 lesz. Ennek ismeretében csatlakoztathatjuk a VLOOKUP függvényhez, és írhatunk egy képletet az E6 -ba, így:
| 1 | = VLOOKUP (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Tehát a MATCH függvény 3 -ra értékel, és ez azt mondja a VLOOKUP -nak, hogy adjon vissza eredményt a 3 -bólrd oszlop az A2: C5 tartományban. Összességében megkapjuk a kívánt „C” eredményt. A képletünk most dinamikus, mivel megváltoztathatjuk az oszlopot vagy a keresendő nevet.
VLOOKUP korlátozások
Amint azt a cikk elején említettük, a VLOOKUP legnagyobb bukása az, hogy megköveteli, hogy a keresési kifejezés megtalálható legyen a keresési tartomány bal bal oszlopában. Bár van néhány divatos trükk, amellyel leküzdheti ezt, a közös alternatíva az INDEX és a MATCH használata. Ez a kombináció nagyobb rugalmasságot biztosít, és néha még gyorsabb számítás is lehet.
VLOOKUP a Google Táblázatokban
A VLOOKUP funkció pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben:
További megjegyzések
Használja a VLOOKUP funkciót VERTICAL kereséshez. A VLOOKUP pontos egyezést keres (range_lookup = FALSE) vagy a legközelebbi egyezés, amely egyenlő vagy kevesebb, mint a lookup_value (range_lookup = TRUE, csak numerikus értékek) a table_array első sorában. Ezután egy megfelelő értéket ad vissza, n számú sorral az egyezés alatt.
Ha a VLOOKUP segítségével pontos egyezést keres, először definiáljon egy azonosító értéket, amelyet keresni szeretne lookup_value. Ez az azonosító érték lehet SSN, munkavállalói azonosító, név vagy más egyedi azonosító.
Ezután határozza meg a tartományt (az úgynevezett table_array), amely tartalmazza az azonosítókat a felső sorban, és azokat az értékeket, amelyeket végül keresni szeretne az alatta lévő sorokban. FONTOS: Az egyedi azonosítóknak a felső sorban kell lenniük. Ha nem, akkor vagy mozgassa a sort a tetejére, vagy használja a MATCH / INDEX -et a VLOOKUP helyett.
Harmadszor határozza meg a sor számát (sor_index) table_array hogy vissza akar térni. Ne feledje, hogy az egyedi sorokat tartalmazó első sor az 1. sor. A második sor a 2. sor stb.
Végül meg kell adnia, hogy pontos egyezést (HAMIS) vagy legközelebbi egyezést (IGAZ) kell keresnie a range_lookup. Ha a pontos egyezés opció van kiválasztva, és nem talál pontos egyezést, akkor hibaüzenet jelenik meg (#N/A). Ahhoz, hogy a képlet üres vagy „nem található”, vagy bármely más értéket adjon vissza a hibaérték (#N/A) helyett, használja az IFERROR függvényt a VLOOKUP funkcióval.
A VLOOKUP funkció használatával hozzávetőleges egyezési készletet adhat vissza: range_lookup = IGAZ. Ez az opció csak numerikus értékeknél érhető el. Az értékeket növekvő sorrendben kell rendezni.
Vízszintes kereséshez használja inkább a HLOOKUP funkciót.
VLOOKUP Példák a VBA -ban
A VLOOKUP funkciót a VBA -ban is használhatja. Típus:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
A függvény argumentumokhoz (lookup_value, stb.) Megadhatja azokat közvetlenül a függvénybe, vagy definiálhat helyettük változókat.
Térjen vissza az Excel összes funkciójának listájához
