Az Excel VLOOKUP függvénye egy cellatartományban keres egy értéket, majd egy olyan értéket ad vissza, amely ugyanabban a sorban van, mint a keresett érték.
A VLOOKUP, amely a „Függőleges keresés” rövidítése, egy olyan keresési funkció, amely a tartomány bal szélső oszlopában (első oszlopában) keres egy értéket, és visszaadja a párhuzamos értéket a jobb oldali oszlopból. A VLOOKUP funkció csak felfelé (fentről lefelé) tekinti meg az értéket egy függőlegesen elrendezett táblázatban.
Tegyük fel például, hogy van egy készletlistánk egy munkalapon egy táblázattal, amely tartalmazza a cikkek nevét, a vásárlás dátumát, a mennyiséget és az árat. Ezután a VLOOKUP segítségével egy másik munkalapon kinyerhetjük egy bizonyos cikknév mennyiségét és árát a készletmunkalapról.
A VLOOKUP funkció elsőre ijesztőnek tűnhet, de valójában meglehetősen könnyen használható, ha megérti, hogyan működik. Ebben az oktatóanyagban bemutatjuk, hogyan kell használni a VLOOKUP függvényt az Excelben.
VLOOKUP Szintaxis és argumentumok
Ha a VLOOKUP függvényt kívánja használni, ismernie kell a szintaxisát és az argumentumait.
A VLOOKUP függvény szintaxisa:
=KERESÉS(keresési_érték,tábla_tömb,oszlopindex_szám,[tartomány_keresése])Ez a függvény 4 paraméterből vagy argumentumból áll:
- lookup_value: Ez adja meg azt az értéket, amelyet az adott táblatömb első oszlopában keres. A Lookup értéknek mindig a bal szélsőben kell lennie (a keresési táblázat oszlopában.
- table_array: Ez az a táblázat (cellatartomány), amelyben meg szeretne keresni egy értéket. Ez a táblázat (keresőtábla) lehet ugyanabban a munkalapban vagy különböző munkalapon, de akár egy másik munkafüzetben is.
- col_index_num: Ez adja meg a kivonni kívánt értékkel rendelkező táblatömb oszlopszámát.
- [range_lookup]: Ez a paraméter határozza meg, hogy pontos egyezést vagy közelítő egyezést kíván-e kivonni. IGAZ vagy HAMIS, írja be a „FALSE” értéket, ha pontos értéket szeretne, vagy írja be az „IGAZ” értéket, ha a hozzávetőleges értékkel rendben van.
A VLOOKUP függvény használata Excelben
Fedezzük fel, hogyan használhatjuk a VLOOKUP-ot a Microsoft Excel programban.
Alap példa
A VLOOKUP használatához először létre kell hoznia adatbázisát vagy tábláját (lásd alább).
Ezután hozzon létre egy táblázatot vagy tartományt, ahonnan keresni szeretne, és kinyerje ki az értékeket a keresési táblázatból.
Ezután jelölje ki azt a cellát, ahonnan a kinyert értéket szeretné kivonni, és írja be a következő VLOOKUP képletet. Például meg akarjuk keresni az 'Ena' telefonszámát, majd meg kell adnunk a keresési értéket B13-ként, A2:E10-et táblázattömbként, 5-öt a telefonszám oszlopszámaként, és FALSE-t a pontos visszaadásához. érték. Ezután nyomja meg az „Enter” gombot a képlet befejezéséhez.
=KERESÉS(B13;A2:E10;5;HAMIS)Nem kell manuálisan beírnia a tábla tartományát, egyszerűen kiválaszthatja a tartományt vagy táblát az egérrel a table_array argumentumhoz. És automatikusan hozzáadódik az argumentumhoz.
Ne feledje, hogy ez működjön, a Lookup-értéknek a keresési táblázatunk bal szélén kell lennie (A2:E10). Ezenkívül a Lookup_value-nak nem kell feltétlenül a munkalap A oszlopában lennie, csak a keresni kívánt tartomány bal szélső oszlopában kell lennie.
A Vlookup jól néz ki
A VLOOKUP függvény csak a táblázat jobb oldalán nézhet. Értéket keres egy táblázat vagy egy tartomány első oszlopában, és kivonja a megfelelő értéket a jobb oldali oszlopból.
Pontos egyezés
Az Excel VLOOKUP funkciójának két módszere van az egyeztetésre, ezek a következők: pontos és közelítő. A VLOOKUP függvény 'range_lookup' paramétere határozza meg, hogy milyen típust keres, pontos vagy hozzávetőleges.
Ha a range_lookup értéket „HAMIS” vagy „0”-ként adja meg, a képlet olyan értéket keres, amely pontosan megegyezik a lookup_value értékkel (ez lehet szám, szöveg vagy dátum).
=KERESÉS(A9;A2:D5;3;HAMIS)
Ha a táblázatban nem található pontos egyezés, akkor #N/A hibát ad vissza. Amikor megpróbáltuk megkeresni a „Japán” kifejezést, és visszaadni a megfelelő értéket a 4. oszlopban, az #N/A hibaüzenet jelenik meg, mivel a táblázat első oszlopában nincs „Japán” szó.
A végső argumentumban „0” vagy „HAMIS” számot is megadhat. Mindkettő ugyanazt jelenti az Excelben.
Hozzávetőleges egyezés
Néha nem feltétlenül kell pontos egyezés, elég a legjobb egyezés. Ilyen esetekben használhatja a hozzávetőleges egyeztetési módot. A hozzávetőleges egyezés megtalálásához állítsa a függvény végső argumentumát „TRUE” értékre. Az alapértelmezett érték TRUE, ami azt jelenti, hogy ha nem adja hozzá az utolsó argumentumot, a függvény alapértelmezés szerint közelítő egyezést fog használni.
=KERESÉS(B10;A2:B7;2;IGAZ)Ebben a példában nincs szükségünk pontos pontszámra a megfelelő osztályzat megtalálásához. Csak a pontokra van szükségünk, hogy ebben a tartományban legyünk.
Ha a VLOOKUP pontos egyezést talál, akkor azt az értéket adja vissza. A fenti példában, ha a képlet nem találja a look_up 89 értéket az első oszlopban, akkor a következő legnagyobb értéket (80) adja vissza.
Első meccs
Ha a táblázat bal szélső oszlopa ismétlődéseket tartalmaz, a VLOOKUP megkeresi és visszaadja az első egyezést.
Például a VLOOKUP úgy van beállítva, hogy megkeresse a „Mia” keresztnév vezetéknevét. Mivel 2 bejegyzés van „Mia” keresztnévvel, így a függvény az első bejegyzés vezetéknevét adja vissza, „Bena”.
Helyettesítő egyezés
A VLOOKUP funkció lehetővé teszi, hogy részleges egyezést találjon egy megadott értékhez helyettesítő karakterek használatával. Ha olyan értéket szeretne megkeresni, amely bármely pozícióban tartalmazza a keresési értéket, adjon hozzá egy „és” jelet (&), hogy a keresési értékenket a helyettesítő karakterrel (*) csatlakoztassa. Használjon „$” jeleket abszolút cellahivatkozások létrehozásához, és adjon hozzá helyettesítő „*” jelet a keresési érték elé vagy után.
A példában a keresési értéknek (Vin) csak egy része van a B13 cellában. Tehát az adott karakterek részleges egyezése érdekében a cellahivatkozás után egy „*” helyettesítő karaktert fűzzünk össze.
=KERESÉS($B$13&"*",$A$2:$E$10,3,HAMIS)
Több keresés
A VLOOKUP funkció lehetővé teszi egy dinamikus kétirányú keresés létrehozását, amely megfelel a soroknak és az oszlopoknak egyaránt. A következő példában a VLOOKUP úgy van beállítva, hogy a keresztnév (Mayra) és a város alapján végezzen keresést. A B14 szintaxisa a következő:
=KERESÉS(B13;A2:E10;MATCH(A14;A1:E1;0);0)
Hogyan lehet VLOOKUP-ot keresni egy másik munkalapról az Excelben
Általában a VLOOKUP függvényt egy külön munkalapról származó egyező értékek visszaadására használják, és ritkán használják ugyanazon a munkalapon lévő adatokkal.
Ha egy másik Excel-lapról szeretne Vlookup-ot keresni, de ugyanabban a munkafüzetben, írja be a lap nevét a table_array elé egy felkiáltójellel (!).
Ha például meg szeretné keresni a „Termékek” munkalap A2-es cellájának értékét az A2:B8 tartományban a „Cikkárak” munkalapon, és a megfelelő értéket visszaadni a B oszlopból:
=KERESÉS(A2,Tételárak!$A$2:$C$8,2,HAMIS)Az alábbi képen egy táblázat látható a „Cikkárak” munkalapon.
Amikor a „Termékek” munkalap C oszlopába beírjuk a VLOOKUP képletet, akkor a „Termékárak” munkalapról húzza le a megfelelő adatokat.
Hogyan lehet VLOOKUP-t felvenni egy másik munkafüzetből az Excelben
Az értéket egy teljesen más munkafüzetben is megkeresheti. Ha egy másik munkafüzetből szeretne VLOOKUP-ot keresni, akkor a munkafüzet nevét szögletes zárójelbe kell írnia, majd a munkalap nevét a table_array előtt felkiáltójellel (!) kell megadnia (lásd alább).
Használja ezt a képletet például egy másik munkalap A2 cellaértékének megkereséséhez az „ItemPrices” nevű munkalapon az „Item.xlsx” munkafüzetben:
=VELKERESÉS(A2,[Elem.xls]Tételárak!$A$2:$B$8,2,HAMIS)Először nyissa meg mindkét munkafüzetet, majd kezdje el beírni a képletet egy munkalap C2 cellájába (Termék munkalap), és amikor eléri a table_array argumentumot, lépjen a fő adatmunkafüzetbe (Item.xlsx), és válassza ki a táblázat tartományát. Így nem kell kézzel beírnia a munkafüzet és a munkalap nevét. Írja be a többi argumentumot, és nyomja meg az „Enter” billentyűt a funkció befejezéséhez.
Még ha bezárja is a keresőtáblát tartalmazó munkafüzetet, a VLOOKUP képlet továbbra is működni fog, de most már láthatja a bezárt munkafüzet teljes elérési útját, ahogy az a következő képernyőképen látható.
Használja a VLOOKUP függvényt az Excel szalagról
Ha nem emlékszik képletekre, bármikor elérheti a VLOOKUP függvényt az Excel szalagról. A VLOOKUP eléréséhez lépjen az Excel szalag „Képletek” lapjára, és kattintson a „Keresés és hivatkozás” ikonra. Ezután válassza ki a „VLOOKUP” opciót a legördülő menü alján.
Ezután adjon meg argumentumokat a „Funkcióargumentumok” párbeszédpanelen. Ezután kattintson az „OK” gombra.
A példában a „Sherill” keresztnévre kerestünk a táblázatban, hogy visszaadjuk a megfelelő állapotot a D oszlopban.
Reméljük, ebből a cikkből megtanulta a VLOOKUP függvény használatát az Excelben. Ha többet szeretne megtudni az Excel használatáról, tekintse meg további Excellel kapcsolatos cikkeinket.