VLOOKUP függvény az Excelben – Útmutató kezdőknek: Szintaxis és példák

tartalom

VLOOKUP függvény Excelben – általános leírás és szintaxis
- A VLOOKUP függvény szintaxisa
VLOOKUP Példák
Pontos vagy hozzávetőleges egyezés a VLOOKUP függvényben
- 1. példa: Pontos egyezés keresése a VLOOKUP segítségével
- 2. példa: A VLOOKUP használata hozzávetőleges egyezés megtalálásához
VLOOKUP Excelben – ezt emlékeznie kell!

Ma egy cikksorozatot indítunk, amely az Excel egyik leghasznosabb funkcióját ismerteti VPR (VLOOKUP). Ez a funkció ugyanakkor az egyik legösszetettebb és legkevésbé értett funkció.

Ebben az oktatóanyagban a VPR Igyekszem az alapokat a lehető legegyszerűbben lefektetni, hogy a tanulási folyamat a tapasztalatlan felhasználók számára a lehető legvilágosabb legyen. Ezenkívül számos példát tanulmányozunk Excel képletekkel, amelyek bemutatják a függvény leggyakoribb használati eseteit VPR.

VLOOKUP függvény Excelben – általános leírás és szintaxis

Tehát mi az VPR? Nos, először is, ez egy Excel függvény. Amit csinál? Megkeresi a megadott értéket, és visszaadja a megfelelő értéket a másik oszlopból. Technikailag szólva, VPR megkeresi az adott tartomány első oszlopában lévő értéket, és ugyanabban a sorban adja vissza az eredményt egy másik oszlopból.

A leggyakoribb alkalmazásban a függvény VPR megkeresi az adatbázisban az adott egyedi azonosítót, és az adatbázisból kinyeri az ezzel kapcsolatos információkat.

A függvény nevének első betűje VPR (VLOOKUP) azt jelenti Вfüggőleges (Vfüggőleges). Ez alapján meg lehet különböztetni VPR ból ből GPR (HLOOKUP), amely egy értéket keres egy tartomány felső sorában − Гvízszintes (Hvízszintesen).

Funkció VPR elérhető az Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP és Excel 2000 verziókban.

A VLOOKUP függvény szintaxisa

Funkció VPR (VLOOKUP) a következő szintaxissal rendelkezik:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Mint látható, egy függvény VPR a Microsoft Excelben 4 opció (vagy argumentum) van. Az első három kötelező, az utolsó nem kötelező.

keresési_érték (lookup_value) – A keresendő érték. Ez lehet egy érték (szám, dátum, szöveg) vagy cellahivatkozás (amely tartalmazza a keresési értéket), vagy egy más Excel-függvény által visszaadott érték. Például ez a képlet megkeresi az értéket 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Ha a keresési érték kisebb, mint a keresett tartomány első oszlopának legkisebb értéke, a függvény VPR hibát fog jelenteni #NÁL NÉL (#N/A).

tábla_tömb (tábla) – két vagy több adatoszlop. Ne feledje, a funkció VPR mindig az argumentumban megadott tartomány első oszlopában keresi az értéket tábla_tömb (asztal). A látható tartomány különféle adatokat tartalmazhat, például szöveget, dátumokat, számokat, logikai értékeket. A függvény nem érzékeny a kis- és nagybetűkre, vagyis a kis- és nagybetűk azonosnak minősülnek. Tehát a képletünk meg fogja keresni az értéket 40 től származó cellákban A2 nak nek A15, mert A az argumentumban megadott A2:B15 tartomány első oszlopa tábla_tömb (asztal):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

oszlopszám (oszlop_szám) annak az oszlopnak a száma az adott tartományban, amelyből a talált sorban lévő értéket adjuk vissza. Az adott tartomány bal szélső oszlopa a 1, a második oszlop az 2, a harmadik oszlop az 3 stb. Most elolvashatja a teljes képletet:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
A képlet értéket keres 40 határon belül A2: A15 és a megfelelő értéket adja vissza a B oszlopból (mivel B az A2:B15 tartomány második oszlopa).

Ha az érv értéke oszlopszám (oszlop_száma) kisebb, mint 1akkor VPR hibát fog jelenteni #ÉRTÉK! (#ÉRTÉK!). És ha több, mint a tartományban lévő oszlopok száma tábla_tömb (tábla), a függvény hibát ad vissza #REF! (#LINK!).

range_lookup (range_lookup) – meghatározza, hogy mit kell keresni:
- pontos egyezés, az argumentumnak egyenlőnek kell lennie HAMIS (HAMIS);
- közelítő egyezés, argumentum egyenlő IGAZ KÓD (IGAZ) vagy egyáltalán nincs megadva.
Ez a paraméter nem kötelező, de nagyon fontos. Később ebben az oktatóanyagban VPR Mutatok néhány példát, amelyek elmagyarázzák, hogyan kell képleteket írni a pontos és közelítő egyezések megtalálásához.

VLOOKUP Példák

Remélem a funkció VPR kicsit világosabb lesz számodra. Most nézzünk meg néhány felhasználási esetet VPR képletekben valós adatokkal.

Hogyan lehet a VLOOKUP segítségével keresni egy másik Excel munkalapon

A gyakorlatban függvényt tartalmazó képletek VPR ritkán használják adatok keresésére ugyanazon a munkalapon. Leggyakrabban meg fog keresni és lekérni a megfelelő értékeket egy másik lapról.

Annak érdekében, hogy használni VPR, keressen egy másik Microsoft Excel munkalapon, Az argumentumban kell tábla_tömb (tábla) adja meg a lap nevét egy felkiáltójellel, majd egy cellatartománnyal. Például a következő képlet azt mutatja, hogy a tartomány A2: B15 nevű lapon van Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Természetesen a lap nevét nem kell kézzel megadni. Csak kezdje el beírni a képletet, és amikor az érvről van szó tábla_tömb (táblázat), váltson a kívánt lapra, és válassza ki az egérrel a kívánt cellatartományt.

Az alábbi képernyőképen látható képlet a „Termék 1” szöveget keresi az A oszlopban (ez az A1:B2 tartomány 9. oszlopa) egy munkalapon. Áraink.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Kérjük, ne feledje, hogy amikor szöveges értéket keres, idézőjelbe („”) kell tennie, ahogy az Excel képletekben általában történik.

Érvelésre tábla_tömb (táblázat) célszerű mindig abszolút hivatkozásokat használni ($ jellel). Ebben az esetben a keresési tartomány változatlan marad, amikor a képletet más cellákba másolja.

Keresés egy másik munkafüzetben a VLOOKUP segítségével

Funkcionálni, működtetni VPR két Excel-munkafüzet között dolgozott, a munkafüzet nevét szögletes zárójelben kell megadnia a munkalap neve előtt.

Például az alábbiakban egy képlet található, amely az értéket keresi 40 a lapon Sheet2 a könyvben Számok.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Ez a legegyszerűbb módja a képlet létrehozásának Excelben VPRamely egy másik munkafüzetre hivatkozik:

Nyissa meg mindkét könyvet. Ez nem kötelező, de egyszerűbb így képletet készíteni. Ugye nem akarod kézzel beírni a munkafüzet nevét? Ezenkívül megvédi Önt a véletlen elírásoktól.
Kezdjen el beírni egy függvényt VPRés ha az érvről van szó tábla_tömb (tábla), váltson át egy másik munkafüzetre, és válassza ki benne a kívánt keresési tartományt.

Az alábbi képernyőképen látható a képlet úgy, hogy a keresés egy tartományra van állítva a munkafüzetben Árlista.xlsx a lapon Áraink.

Funkció VPR akkor is működik, ha bezárja a keresett munkafüzetet, és a munkafüzetfájl teljes elérési útja megjelenik a képletsorban, az alábbiak szerint:

Ha a munkafüzet vagy munkalap neve szóközt tartalmaz, akkor azt aposztrófok közé kell tenni:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Megnevezett tartomány vagy táblázat használata képletekben a VLOOKUP segítségével

Ha ugyanazt a keresési tartományt több funkcióban kívánja használni VPR, létrehozhat egy elnevezett tartományt, és argumentumként beírhatja a nevét a képletbe tábla_tömb (asztal).

Elnevezett tartomány létrehozásához egyszerűen jelölje ki a cellákat, és írja be a megfelelő nevet a mezőbe Keresztnév, a képletsor bal oldalán.

Most felírhatja a következő képletet egy termék árának meghatározásához 1 termék:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

A legtöbb tartománynév a teljes Excel-munkafüzetre vonatkozik, így nincs szükség az argumentum lapnevének megadására tábla_tömb (tábla), akkor is, ha a képlet és a keresési tartomány különböző munkalapokon található. Ha különböző munkafüzetekben vannak, akkor a tartomány neve előtt meg kell adnia a munkafüzet nevét, például így:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Tehát a képlet sokkal világosabbnak tűnik, egyetértesz? Ezenkívül az elnevezett tartományok használata jó alternatíva az abszolút hivatkozásokkal szemben, mivel a megnevezett tartomány nem változik, amikor a képletet más cellákba másolja. Ez azt jelenti, hogy biztos lehet benne, hogy a képletben a keresési tartomány mindig helyes marad.

Ha a parancs segítségével egy cellatartományt teljes értékű Excel-táblázattá konvertál Táblázat (Táblázat) fülre beszúrás (Beszúrás), majd amikor kiválaszt egy tartományt az egérrel, a Microsoft Excel automatikusan hozzáadja a képlethez az oszlopneveket (vagy a táblázat nevét, ha a teljes táblázatot választja).

A kész képlet így fog kinézni:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Vagy akár így is:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Elnevezett tartományok használatakor a hivatkozások ugyanazokra a cellákra mutatnak, függetlenül attól, hogy hova másolja a függvényt VPR a munkafüzeten belül.

Helyettesítő karakterek használata a VLOOKUP képletekben

Sok más funkcióhoz hasonlóan VPR A következő helyettesítő karaktereket használhatja:

Kérdőjel (?) – bármely karaktert helyettesít.
Csillag (*) – bármilyen karaktersorozatot helyettesít.

Helyettesítő karakterek használata a függvényekben VPR sok esetben hasznos lehet, pl.

Ha nem emlékszik pontosan a szövegre, amelyet meg kell találnia.
Ha olyan szót szeretne találni, amely egy cella tartalmának részét képezi. Tudjuk, hogy VPR a cella egészének tartalma alapján keres, mintha az opció engedélyezve lenne Egyezik a teljes cellatartalommal (Teljes cella) a szabványos Excel-keresésben.
Amikor egy cella extra szóközt tartalmaz a tartalom elején vagy végén. Ilyen helyzetben sokáig törheti az agyát, próbálva kitalálni, miért nem működik a képlet.

1. példa: Olyan szöveg keresése, amely bizonyos karakterekkel kezdődik vagy végződik

Tegyük fel, hogy egy adott ügyfelet szeretne keresni az alábbi adatbázisban. Nem emlékszel a vezetéknevére, de tudod, hogy „ack”-vel kezdődik. Íme egy képlet, amely tökéletesen elvégzi a munkát:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Most, hogy biztos benne, hogy megtalálta a megfelelő nevet, ugyanezt a képletet használhatja az ügyfél által fizetett összeg megkeresésére. Ehhez csak módosítsa a függvény harmadik argumentumát VPR a kívánt oszlopszámra. Esetünkben ez a C oszlop (3. a tartományban):

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Íme néhány további példa helyettesítő karakterekkel:

~ Keressen egy „man” végződésű nevet:

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Keressen egy nevet, amely „ad” szóval kezdődik és „fia”-ra végződik:

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Az 5 karakterből álló listában megtaláljuk a keresztnevet:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Funkcionálni, működtetni VPR a helyettesítő karakterekkel megfelelően működött, mint a negyedik argumentum, amelyet mindig használjon HAMIS (HAMIS). Ha a keresési tartomány egynél több olyan értéket tartalmaz, amely megfelel a helyettesítő karakterekkel ellátott keresési kifejezéseknek, akkor a rendszer az első talált értéket adja vissza.

2. példa: Kombinálja a helyettesítő karaktereket és a cellahivatkozásokat a VLOOKUP képletekben

Most nézzünk egy kicsit összetettebb példát a függvény használatával történő keresésre VPR érték szerint egy cellában. Képzelje el, hogy az A oszlop a licenckulcsok listája, a B oszlop pedig a licencet birtokló nevek listája. Ezenkívül a C1-es cellában van egy része (több karakter) valamilyen licenckulcsból, és szeretné megkeresni a tulajdonos nevét.

Ezt a következő képlet segítségével lehet megtenni:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Ez a képlet az adott tartományban lévő C1 cellából keresi ki az értéket, és a megfelelő értéket adja vissza a B oszlopból. Vegye figyelembe, hogy az első argumentumban a cellahivatkozás előtt és után egy „és” karaktert (&) használunk a szöveges karakterlánc összekapcsolásához.

Amint az alábbi ábrán látható, a funkció VPR „Jeremy Hill”-t adja vissza, mert a licenckulcs a C1 cellából származó karaktersorozatot tartalmazza.

Vegye figyelembe, hogy az érv tábla_tömb (táblázat) a fenti képernyőképen a táblázat nevét (7. táblázat) tartalmazza ahelyett, hogy cellatartományt adna meg. Ezt tettük az előző példában.

Pontos vagy hozzávetőleges egyezés a VLOOKUP függvényben

Végül pedig nézzük meg közelebbről a függvényhez megadott utolsó argumentumot VPR - range_lookup (intervallum_nézet). Ahogy a lecke elején említettük, ez az érv nagyon fontos. Értékével ugyanabban a képletben teljesen eltérő eredményeket kaphat IGAZ KÓD (IGAZ) ill HAMIS (HAMIS).

Először is nézzük meg, mit ért a Microsoft Excel a pontos és közelítő egyezések alatt.

Ha az érv range_lookup (tartomány_keresése) egyenlő HAMIS (HAMIS), a képlet pontos egyezést keres, azaz pontosan az argumentumban megadott értéket keresési_érték (keresési_érték). Ha a tartomány első oszlopában tképes_tömb (tábla) két vagy több olyan értékkel találkozik, amelyek megfelelnek az argumentumnak keresési_érték (keresési_érték), akkor az első kerül kiválasztásra. Ha nem található egyezés, a funkció hibát jelez #NÁL NÉL (#N/A). Például a következő képlet hibát jelez #NÁL NÉL (#N/A), ha nincs érték az A2:A15 tartományban 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Ha az érv range_lookup (tartomány_keresése) egyenlő IGAZ KÓD (IGAZ), a képlet hozzávetőleges egyezést keres. Pontosabban először a függvény VPR keresi a pontos egyezést, és ha nem található, kiválaszt egy hozzávetőlegeset. A közelítő egyezés az a legnagyobb érték, amely nem haladja meg az argumentumban megadott értéket. keresési_érték (keresési_érték).

Ha az érv range_lookup (tartomány_keresése) egyenlő IGAZ KÓD (TRUE) vagy nincs megadva, akkor a tartomány első oszlopában lévő értékeket növekvő sorrendben kell rendezni, azaz a legkisebbtől a legnagyobbig. Ellenkező esetben a funkció VPR hibás eredményt adhat.

Hogy jobban megértsük a választás fontosságát IGAZ KÓD (IGAZSÁG) ill HAMIS (HAMIS), nézzünk még néhány képletet a függvénnyel VPR és nézd meg az eredményeket.

1. példa: Pontos egyezés keresése a VLOOKUP segítségével

Ahogy emlékszel, a pontos egyezés kereséséhez a függvény negyedik argumentuma VPR számítania kell HAMIS (HAMIS).

Térjünk vissza a táblázathoz a legelső példától, és derítsük ki, melyik állat tud nagy sebességgel mozogni 50 mérföld per óra. Úgy gondolom, hogy ez a képlet nem okoz nehézséget:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Vegye figyelembe, hogy keresési tartományunk (A oszlop) két értéket tartalmaz 50 – sejtekben A5 и A6. A képlet értéket ad vissza a cellából B5. Miért? Mert ha pontos egyezést keresünk, a függvény VPR az első talált értéket használja, amely megfelel a keresettnek.

2. példa: A VLOOKUP használata hozzávetőleges egyezés megtalálásához

Amikor a funkciót használja VPR közelítő egyezés kereséséhez, azaz amikor az argumentum range_lookup (tartomány_keresése) egyenlő IGAZ KÓD (IGAZ) vagy kihagyott, akkor az első dolog, amit tennie kell, hogy a tartományt az első oszlop szerint növekvő sorrendben rendezze.

Ez nagyon fontos, mert a funkció VPR az adott után következő legnagyobb értéket adja vissza, majd a keresés leáll. Ha elhanyagolja a helyes rendezést, akkor nagyon furcsa eredményeket vagy hibaüzenetet kap. #NÁL NÉL (#N/A).

Most használhatja a következő képletek egyikét:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Amint látja, szeretném megtudni, hogy az állatok közül melyiknek van a legközelebbi sebessége 69 mérföld per óra. És itt van az eredmény, amit a függvény visszaadott nekem VPR:

Amint látja, a képlet eredményt adott vissza Antilop (Antelope), akinek a sebessége 61 mérföld per óra, bár a listán az is szerepel Cheetah (gepárd), aki nagy sebességgel fut 70 mérföld per óra, és a 70 közelebb van a 69-hez, mint a 61-hez, nem igaz? Miért történik ez? Mivel a funkció VPR közelítő egyezés keresésekor a legnagyobb értéket adja vissza, amely nem nagyobb, mint a keresett.

Remélem, ezek a példák megvilágítják a funkcióval való munkát VPR Excelben, és többé nem nézel rá kívülállóként. Most nem árt röviden megismételni a tanulmányozott anyag legfontosabb pontjait, hogy jobban rögzítsük a memóriában.

VLOOKUP Excelben – ezt emlékeznie kell!

Funkció VPR Az Excel nem nézhet balra. Mindig az argumentum által megadott tartomány bal szélső oszlopában keresi az értéket tábla_tömb (asztal).
Funkcióban VPR minden érték nem tesz különbséget a kis- és nagybetűk között, azaz a kis és nagy betűk egyenértékűek.
Ha a keresett érték kisebb, mint a keresett tartomány első oszlopában lévő minimális érték, akkor a függvény VPR hibát fog jelenteni #NÁL NÉL (#N/A).
Ha 3. érv oszlopszám (oszlop_száma) kisebb, mint 1funkció VPR hibát fog jelenteni #ÉRTÉK! (#ÉRTÉK!). Ha nagyobb, mint a tartományban lévő oszlopok száma tábla_tömb (tábla), a függvény hibát jelez #REF! (#LINK!).
Használjon abszolút cellahivatkozásokat az argumentumban tábla_tömb (tábla), hogy a képlet másolásakor a helyes keresési tartomány megmaradjon. Alternatív megoldásként próbáljon megnevezett tartományokat vagy táblázatokat használni az Excelben.
A hozzávetőleges találati keresés során ne feledje, hogy a keresett tartomány első oszlopát növekvő sorrendben kell rendezni.
Végül emlékezzen a negyedik érv fontosságára. Használjon értékeket IGAZ KÓD (IGAZSÁG) ill HAMIS (HAMIS) szándékosan, és sok fejfájástól megszabadul.

Funkció-oktatóanyagunk következő cikkeiben VPR Excelben haladóbb példákat tanulunk meg, például különféle számítások végrehajtását a segítségével VPR, értékek kinyerése több oszlopból és így tovább. Köszönöm, hogy elolvasta ezt a bemutatót, és remélem, jövő héten újra találkozunk!