Excel VLOOKUP függvény

A VLOOKUP egy Excel függvény, amely függőlegesen rendezett táblákból szerez adatokat. A keresési értékeknek a táblázat első oszlopában kell megjelenniük, amelyet átadtak a VLOOKUP fájlba. A VLOOKUP támogatja a hozzávetőleges és pontos egyeztetést, a helyettesítő karaktereket (*?) A részleges egyezésekhez.

Függőleges adatok | Oszlopok száma Csak jól néz ki | Megfelelő módok Pontos egyezés | Hozzávetőleges egyezés | Első mérkőzés | Helyettesítőmeccs | Kétirányú keresés | Több kritérium | # N / A hibák | Videók

V a függőleges

A VLOOKUP célja, hogy információkat szerezzen egy ilyen rendszerű táblázatokból:

A B oszlopban található rendelési számot keresési értékként használva a VLOOKUP bármely megrendeléshez megszerezheti az ügyfél-azonosítót, az összeget, a nevet és az állapotot. Például az 1004 rendelés ügyfélnevének megszerzéséhez a képlet a következő:

=VLOOKUP(1004,B5:F9,4,FALSE) // returns "Sue Martin"

 Vízszintes adatokhoz használhatja a HLOOKUP, az INDEX és a MATCH, vagy az XLOOKUP. 
 A VLOOKUP oszlopok számain alapszik 
 A VLOOKUP használatakor képzelje el, hogy a táblázat minden oszlopa balról kezdve számozott. Ha egy adott oszlopból szeretne értéket kapni, adja meg a megfelelő számot “oszlopindexként”. Például a keresztnév lekérésére szolgáló oszlopindex 2: 
  
 A vezetéknév és az e-mail oszlopokkal tölthető le 3 és 4: 

=VLOOKUP(H3,B4:E13,2,FALSE) // first name=VLOOKUP(H3,B4:E13,3,FALSE) // last name=VLOOKUP(H3,B4:E13,4,FALSE) // email address

 A VLOOKUP csak jól néz ki 
 A VLOOKUP csak jobbra nézhet. A visszakeresni kívánt adatok (eredményértékek) bármely oszlopban megjelenhetnek a keresési értékek jobb oldalán: 
  
 Ha balra kell keresnie az értékeket, lásd: INDEX és MATCH, vagy XLOOKUP. 
 Pontos és hozzávetőleges egyezés 
 A VLOOKUP kétféle egyezési módot kínál, pontos és közelítő. Az egyezést vezérlő argumentum neve: “range_lookup”. Ez egy zavaros név, mert úgy tűnik, hogy köze van az A1: A10 cellatartományokhoz. Valójában a “tartomány” szó ebben az esetben az “értéktartományra” utal – ha a tartomány_keresés IGAZ, akkor a VLOOKUP értéktartománynak felel meg, nem pedig pontos értéknek. Jó példa erre a VLOOKUP használata az osztályzatok kiszámításához. 
 Fontos megérteni, hogy a range_lookup alapértelmezés szerint IGAZ, ami azt jelenti, hogy a VLOOKUP alapértelmezés szerint közelítő egyezést használ, ami veszélyes lehet. Állítsa a range_lookup értéket FALSE értékre a pontos egyezés kikényszerítéséhez: 
 Megjegyzés: A FALSE helyett nulla (0) értéket is megadhat egy pontos egyezéshez. 

 Pontos egyezés 
 A legtöbb esetben valószínűleg a VLOOKUP-ot szeretné használni pontos egyezési módban. Ennek akkor van értelme, ha van egy egyedi kulcs, amelyet keresési értékként használhat, például a film címe ezekben az adatokban: 
  
 A képlet a H6-ban az Év megtalálásához, a film címe pontos egyezése alapján: 
=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact match

 Hozzávetőleges egyezés 
 Azokban az esetekben, amikor a legjobb egyezést akarja, nem feltétlenül pontos egyezést, akkor hozzávetőleges módot szeretne használni. Például az alábbiakban a G5: H10 táblázatban szeretnénk utána nézni a jutalék mértékének. A keresési értékek a C. oszlopból származnak. Ebben a példában a VLOOKUP-ot kell használnunk hozzávetőleges egyezési módban, mert a legtöbb esetben pontos egyezést soha nem találunk. A D5-ben található VLOOKUP képlet hozzávetőleges egyezésre van konfigurálva úgy, hogy az utolsó argumentumot IGAZra állítja: 
  

=VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = approximate match

 A VLOOKUP a G oszlop értékeit fogja keresni a keresési értékre. Ha pontos egyezést talál, a VLOOKUP fogja használni. Ha nem, akkor a VLOOKUP “visszalép” és megegyezik az előző sorral. 
 Megjegyzés: az adatokat növekvő sorrendben kell rendezni a keresési érték szerint, ha a VLOOKUP hozzávetőleges egyezési módot használ. 
 Első egyezés 
 Ismétlődő értékek esetén a VLOOKUP megtalálja az első egyezést, amikor az egyezési mód pontos. Az alábbi képernyőn a VLOOKUP úgy van beállítva, hogy megtalálja a “zöld” szín árát. Három zöld színű bejegyzés van, és a VLOOKUP visszaadja az első bejegyzés árát, 17 dollárt. Az F5 cellában található képlet a következő: 

=VLOOKUP(E5,B5:C11,2,FALSE) // returns 17

  
 Helyettesítő karakterek egyezése 
 A VLOOKUP funkció támogatja a helyettesítő karaktereket, ami lehetővé teszi a keresési érték részleges egyezésének végrehajtását. Például a VLOOKUP használatával lekérheti az értékeket a táblából, miután csak egy keresési érték egy részét írta be. A VLOOKUP helyettesítő karakterek használatához meg kell adnia a pontos egyezési módot úgy, hogy FALSE vagy 0 értéket ad meg az utolsó argumentumhoz, a range_lookup. A H7-ben szereplő képlet lekéri a “Michael” keresztnevet, miután beírta az “Aya” szót a H4 cellába: 

=VLOOKUP($H$4&"*",$B$5:$E$104,2,FALSE)

  
 Itt olvashat részletesebb magyarázatot. 
 Kétirányú keresés 
 A VLOOKUP függvényben az oszlop Az index argumentumot általában statikus számként keményen kódolják.Létrehozhat azonban egy dinamikus oszlopindexet is a MATCH függvény segítségével a jobb oldali oszlop megkereséséhez. Ez a technika lehetővé teszi dinamikus, kétirányú keresés létrehozását, amely egyezik mind a sorokban, mind az oszlopokban. Az alábbi képernyőn a VLOOKUP úgy van konfigurálva, hogy a név és a hónap alapján végezzen keresést. A H6 képlete a következő: 


=VLOOKUP(H4,B5:E13,MATCH(H5,B4:E4,0),0)

  
 További részletekért lásd ezt a példát. 
 Megjegyzés: Általában az INDEX és a MATCH rugalmasabb módszer a kétirányú keresések végrehajtására. 
 Több feltétel 
 A VLOOKUP függvény nem kezel több feltételt natív módon. Segítő oszlop segítségével azonban több mezőt összekapcsolhat, és ezeket a mezőket több feltételként is használhatja a VLOOKUP-on belül. Az alábbi példában a B oszlop egy segítő oszlop, amely összefűzi az utó- és vezetékneveket ezzel a képlettel együtt: 

=C5&D5 // helper column

 VLOOKUP úgy van beállítva, hogy ugyanazt tegye a keresési érték létrehozására. A H6 képlete a következő: 

=VLOOKUP(H4&H5,B5:E13,4,0)

  
 További részletekért lásd ezt a példát. 
 Megjegyzés: Az INDEX és a MATCH, valamint az XLOOKUP erőteljesebb módszer a több feltétel alapján történő keresések kezelésére. 
 VLOOKUP és # N / A hibák 
 A VLOOKUP használatakor elkerülhetetlenül belefut a # N / A hibába. A # N / A hiba csak azt jelenti, hogy “nem található”. Például az alábbi képernyőn a “Toy Story 2” keresési érték nem létezik a keresési táblázatban, és mindhárom VLOOKUP képlet # N / A-t ad vissza: 
  
 Az NA hiba “csapdázásának” egyik módja az IFNA függvény ilyen használata: 
  
 A H6 képlete a következő: 

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"Not found")

 Az üzenet tetszés szerint testreszabható. Ha nem ad vissza semmit (azaz üres eredményt jelenít meg), amikor a VLOOKUP visszaadja a # N / A értéket, használhat egy ilyen üres karakterláncot: 

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"") // no message

 A # N / A hiba azért hasznos, mert elmondja, hogy valami nincs rendben. A gyakorlatban számos oka lehet ennek a hibának, például: 

 A keresési érték nem létezik a táblázatban 
 A keresési érték hibásan van megadva, vagy tartalmazza extra szóköz 
 Az egyezési mód pontos, de hozzávetőlegesnek kell lennie 
 A táblázat tartománya nincs megfelelően megadva 
 A VLOOKUP fájlt másolja, és a táblázat hivatkozása nem zárolva 

 További információ: VLOOKUP # N / A hiba nélkül 
 További információ a VLOOKUP-ról 

 További VLOOKUP példák 
 VLOOKUP videók 
 23 tipp a VLOOKUP használatához 

 Egyéb megjegyzések 

 A Range_lookup vezérli, hogy az értéknek pontosan meg kell-e egyeznie vagy sem. Az alapértelmezett érték: IGAZ = engedélyezze a nem pontos egyezést. 
 Állítsa a range_lookup értéket FALSE értékre, hogy pontos egyezést igényeljen, és az TRUE értéket a nem pontos egyezés engedélyezéséhez. alapértelmezett beállítás), a nem pontos egyezés hatására a VLOOKUP függvény megegyezik a táblázat legközelebbi értékével, amely még mindig kisebb, mint az érték. 
 Ha a range_lookup el van hagyva, a VLOOKUP funkció lehetővé teszi a nem pontos egyezést, de pontos egyezést fog használni, ha létezik ilyen. 
 Ha a range_lookup értéke IGAZ (az alapértelmezett beállítás), győződjön meg arról, hogy a táblázat első sorában található keresési értékek növekvő sorrendben vannak rendezve. Ellenkező esetben a VLOOKUP helytelen vagy váratlan értéket adhat vissza. 
 Ha a range_lookup HAMIS (pontos egyezést igényel), a táblázat első oszlopában szereplő értékeket nem kell rendezni.