Excel VLOOKUP funkció
A Excel VLOOKUP funkció egy hatékony eszköz, amely segít megkeresni egy megadott értéket úgy, hogy a táblázat vagy egy tartomány első oszlopában függőlegesen illeszkedik, majd visszaadja a megfelelő értéket egy másik oszlopból ugyanabban a sorban. Bár a VLOOKUP hihetetlenül hasznos, a kezdők számára néha nehéz lehet megérteni. Ennek az oktatóanyagnak az a célja, hogy segítsen a VLOOKUP elsajátításában az érvek lépésről lépésre történő magyarázata, hasznos példákat és a megoldások a gyakori hibákra találkozhat a VLOOKUP funkció használatakor.
Kapcsolódó videók
Az érvek lépésről lépésre történő magyarázata
Amint a fenti képernyőképen látható, a VLOOKUP funkció egy e-mail megkeresésére szolgál egy adott azonosítószám alapján. Most részletes magyarázatot adok a VLOOKUP használatára ebben a példában, lépésről lépésre lebontva az egyes argumentumokat.
1. lépés: Indítsa el a VLOOKUP funkciót
Válasszon ki egy cellát (ebben az esetben H6) az eredmény kimenetéhez, majd indítsa el a VLOOKUP funkciót a következő tartalom beírásával Formula Bar.
=VLOOKUP(
2. lépés: Adja meg a keresési értéket
Először adja meg a keresési értéket (ez az, amit keres) a VLOOKUP függvényben. Itt a G6 cellára hivatkozom, amely egy bizonyos 1005-ös azonosítószámot tartalmaz.
=VLOOKUP(G6
3. lépés: Adja meg a táblatömböt
Ezután adjon meg egy cellatartományt, amely tartalmazza mind a keresett, mind a visszaadni kívánt értéket. Ebben az esetben a B6:E12 tartományt választom. A képlet most a következőképpen néz ki:
=VLOOKUP(G6,B6:E12
=VLOOKUP(G6,$B$6:$E$12
4. lépés: Adja meg azt az oszlopot, amelyből értéket szeretne visszaadni
Ezután adja meg azt az oszlopot, amelyből értéket szeretne visszaadni.
Ebben a példában, mivel az e-mailt azonosítószám alapján kell visszaküldenem, itt egy 4-es számot írok be, hogy a VLOOKUP-nak megmondjam, hogy az adattartomány negyedik oszlopából adjon vissza értéket.
=VLOOKUP(G6,B6:E12,4
5. lépés: Keressen hozzávetőleges vagy pontos egyezést
Végül döntse el, hogy közelítő vagy pontos egyezést keres.
- Találni egy pontos egyezés, használnia kell HAMIS utolsó érvként.
- Találni egy hozzávetőleges egyezés, Használata TRUE utolsó argumentumként, vagy hagyja üresen.
Ebben a példában a FALSE-t használom a pontos egyezéshez. A képlet most így néz ki:
=VLOOKUP(G6,B6:E12,4,FALSE
Nyomja meg az Enter billentyűt az eredmény eléréséhez
A fenti példában az egyes argumentumok egyenkénti magyarázatával a VLOOKUP függvény szintaxisa és argumentumai most sokkal könnyebben megérthetők.
Szintaxis és érvek
=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])
- Keresési_érték (kötelező): A keresett érték (valódi érték vagy cellahivatkozás). Ne feledje, hogy ennek az értéknek a table_array első oszlopában kell lennie.
- Táblázat_tömb (kötelező): Egy cellatartomány tartalmazza a keresési érték oszlopát és a visszatérési érték oszlopát is.
- Oszlop_index (kötelező): Az egész szám a visszatérési értéket tartalmazó oszlop számát jelenti. 1-gyel kezdődik a table_array bal szélső oszlopában.
- Tartomány_keresés (opcionális): Logikai érték, amely meghatározza, hogy a VLOOKUP hozzávetőleges vagy pontos egyezést keres-e.
- Hozzávetőleges mérkőzés - Állítsa be ezt az argumentumot TRUE, 1 vagy hagyja üres.
fontos: A hozzávetőleges egyezés megtalálásához a table_array első oszlopában lévő értékeket növekvő sorrendbe kell rendezni arra az esetre, ha a VLOOKUP rossz eredményt ad vissza. - Pontos egyezés - Állítsa be ezt az argumentumot HAMIS or 0.
- Hozzávetőleges mérkőzés - Állítsa be ezt az argumentumot TRUE, 1 vagy hagyja üres.
1. példa: Pontos egyezés és hozzávetőleges egyezés a VLOOKUP-ban
Ha a VLOOKUP használatakor zavarban van a pontos egyezést és a hozzávetőleges egyezést illetően, ez a szakasz segíthet tisztázni ezt a zavart.
Pontos egyezés a VLOOKUP-ban
Ebben a példában az E6:E8 tartományban felsorolt pontszámok alapján fogom megtalálni a megfelelő neveket, ezért beírom a következő képletet az F6 cellába, és lehúzom az Automatikus kitöltés fogantyúját az F8-ra. Ebben a képletben az utolsó argumentum így van megadva HAMIS pontos egyezés keresésének végrehajtásához.
=VLOOKUP(E6,$B$6:$C$12,2,FALSE)
Mivel azonban a 98-as pontszám nem létezik az adattartomány első oszlopában, a VLOOKUP #N/A hibaeredményt ad vissza.
Hozzávetőleges egyezés a VLOOKUP-ban
Továbbra is a fenti példát használva, ha az utolsó argumentumot erre módosítja TRUE, a VLOOKUP hozzávetőleges egyezéskeresést hajt végre. Ha nem található egyezés, akkor megkeresi a következő legnagyobb értéket, amely kisebb, mint a keresési érték, és visszaadja a megfelelő eredményt.
=VLOOKUP(E6,$B$6:$C$12,2,TRUE)
Mivel a 98-as pontszám nem létezik, a VLOOKUP megkeresi a következő legnagyobb értéket, amely kisebb, mint 98, ami 95, és a legközelebbi eredményként a 95-ös pontszám nevét adja vissza.
- Ebben a közelítő egyezési esetben a table_array első oszlopában lévő értékeket növekvő sorrendbe kell rendezni. Ellenkező esetben előfordulhat, hogy a VLOOKUP nem a megfelelő értéket adja vissza.
- Itt zároltam a táblatömböt ($B$6:$C$12) a VLOOKUP függvényben, hogy gyorsan hivatkozhassak egy konzisztens adatkészletre több keresési értékhez képest.
2. példa: Használja a VLOOKUP-ot több feltétellel
Ez a rész bemutatja, hogyan használható a VLOOKUP több feltétellel az Excelben. Az alábbi képernyőképen látható módon, ha egy megadott név (a H5 cellában) és a részleg (a H6 cellában) alapján próbál keresni, kövesse az alábbi lépéseket ennek végrehajtásához.
1. lépés: Adjon hozzá egy segédoszlopot a keresési oszlopokból származó értékek összefűzéséhez
Ebben az esetben létre kell hoznunk egy segítő oszlopot az értékek összefűzéséhez Név oszlop és a osztály oszlop.
- Adjon hozzá egy segédoszlopot az adattartomány bal oldalán, és adjon fejlécet ennek az oszlopnak. Lásd a képernyőképet:
- Ebben a segítő oszlopban válassza ki a fejléc alatti első cellát, és írja be a következő képletet a Formula sáv, és nyomja meg a gombot belép.
=C6&" "&D6
Megjegyzések: Ebben a képletben egy „és” jelet (&) használunk, hogy a szöveget két oszlopban egyesítsük, így egyetlen szövegrészt állítunk elő.- C6 a keresztneve a Név oszlophoz csatlakozni, D6 az első osztálya a osztály oszlophoz csatlakozni.
- E két cella értékeit egy szóköz köti össze.
- Jelölje ki ezt az eredménycellát, majd húzza a AutoFill fogantyú lefelé, hogy alkalmazza ezt a képletet ugyanabban az oszlopban lévő többi cellára.
2. lépés: Alkalmazza a VLOOKUP függvényt a megadott feltételekkel
Jelöljünk ki egy cellát, ahova ki szeretnénk írni az eredményt (itt az I7-et választom), írjuk be a következő képletet a Formula sáv, majd nyomja meg a gombot belép.
=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)
Eredmény
- A segítő oszlopot az adattartomány első oszlopaként kell használni.
- Most a fizetés oszlop az adattartomány ötödik oszlopa, ezért a számot használjuk 5 mint az oszlop indexe a képletben.
- Csatlakoznunk kell a kritériumokhoz I5 és a I6 (I5& " "&I6) ugyanúgy, mint a segítő oszlopban, és az összefűzött értéket használja a keresési_érték argumentum a képletben.
- A két feltételt közvetlenül a lookup_value argumentumban is elhelyezheti, és szóközzel elválaszthatja (ha a feltételek szövegesek, ne felejtse el dupla idézőjelek közé tenni).
=VLOOKUP("Albee IT",B6:F12,5,FALSE)
- Egy jobb alternatíva – keresés több feltétellel másodpercek alatt
A Többfeltételes keresés jellemzője Kutools az Excel számára Segítségével egyszerűen, másodpercek alatt több feltétellel kereshet. 30 napos, teljes értékű ingyenes próbaverzió most!
Gyakori VLOOKUP hibák és megoldások
Ez a szakasz felsorolja azokat a gyakori hibákat, amelyekkel a VLOOKUP használata során találkozhat, és megoldásokat kínál a javításukra.
#N/A hiba jelenik meg
A VLOOKUP leggyakoribb hibája a #N/A hiba, ami azt jelenti, hogy az Excel nem találta a keresett értéket. Íme néhány ok, amiért a VLOOKUP #N/A hibát adhat vissza.
1. ok: A keresési érték nem található a table_array első oszlopában
Az Excel VLOOKUP egyik korlátja, hogy csak balról jobbra nézhet. Tehát a keresési értékeknek a table_array első oszlopában kell lenniük.
Az alábbi képernyőképen látható módon a megadott munkakör alapján szeretnék nevet adni. Itt a keresési érték (értékesítési menedzser) a táblázat_tömb második oszlopában található, a visszatérési érték pedig a keresési oszlop bal oldalán található, így a VLOOKUP #N/A hibát ad vissza.
Megoldások
A hiba kijavításához az alábbi megoldások bármelyikét alkalmazhatja.
- Rendezd át az oszlopokat
Az oszlopokat átrendezheti úgy, hogy a keresőoszlop a table_array első oszlopába kerüljön. - Használja együtt az INDEX és a MATCH függvényeket
Itt az INDEX és a MATCH függvényeket együtt használjuk a VLOOKUP alternatívájaként a probléma megoldására.=INDEX(B6:B12,MATCH(F6,C6:C12,0))
- Használja az XLOOKUP függvényt (elérhető az Excel 365, Excel 2021 és újabb verziókban)
=XLOOKUP(F6,C6:C12,B6:B12)
2. ok: A keresési érték nem található a keresési oszlopban (pontos egyezés)
Az egyik leggyakoribb ok, amiért a VLOOKUP #N/A hibát ad vissza, az az, hogy a keresett érték nem található.
Ahogy az alábbi példában is látható, az E98-ban megadott 6-as pontszám alapján fogjuk megtalálni a nevet. Ez a pontszám azonban nem létezik az adattartomány első oszlopában, ezért a VLOOKUP #N/A hibaeredményt ad vissza.
Megoldások
A hiba kijavításához próbálkozzon az alábbi megoldások egyikével.
- Ha azt szeretné, hogy a VLOOKUP a következő legnagyobb értéket keresse, amely kisebb, mint a keresési érték, módosítsa az utolsó argumentumot HAMIS (pontos egyezés) -hoz TRUE (hozzávetőleges egyezés). További információért lásd: 1. példa: Pontos egyezés és hozzávetőleges egyezés a VLOOKUP használatával.
- Az utolsó argumentum megváltoztatásának elkerülése érdekében, és emlékeztetőt kapjon, ha a keresési érték nem található, beillesztheti a VLOOKUP függvényt az IFERROR függvénybe:
=IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")
3. ok: A keresési érték kisebb, mint a keresési oszlop legkisebb értéke (hozzávetőleges egyezés)
Ahogy az alábbi képernyőképen látható, Ön hozzávetőleges egyezéskeresést hajt végre. A keresett érték (ebben az esetben az 1001-es azonosítószám) kisebb, mint a keresési oszlop legkisebb 1002-es értéke, ezért a VLOOKUP #N/A hibát ad vissza.
Megoldások
Íme két megoldás az Ön számára.
- Győződjön meg arról, hogy a keresési érték nagyobb vagy egyenlő, mint a keresési oszlop legkisebb értéke.
- Ha azt szeretné, hogy az Excel emlékeztesse Önt arra, hogy a keresési érték nem található, csak helyezze be a VLOOKUP függvényt az IFERROR függvénybe az alábbiak szerint:
=IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")
4. ok: A számok szövegként vannak formázva
Amint az alábbi képernyőképen látható, ebben a példában a #N/A hiba az eredeti táblázat keresőcellája (G6) és keresési oszlopa (B6:B12) közötti adattípus-eltérésből adódik. Itt a G6 értéke egy szám, a B6:B12 tartományban lévő értékek pedig szövegként formázott számok.
Megoldások
A probléma megoldásához a keresési értéket vissza kell konvertálnia számmá. Íme két módszer az Ön számára.
- Alkalmazza a Számmá konvertálás funkciót
Kattintson arra a cellára, amelyben a szöveget számmá szeretné alakítani, és válassza ki ezt a gombot a cella mellett, majd válassza ki Átalakítás számra. - Használjon egy praktikus eszközt a szöveg és a szám közötti kötegelt konvertáláshoz
A Konvertálás szöveg és szám között jellemzője Kutools az Excel számára segít könnyen konvertálni egy sor cellát szövegből számmá és fordítva. 30 napos, teljes értékű ingyenes próbaverzió most!
5. ok: A table_array nem állandó, amikor a VLOOKUP képletet más cellákba húzza
Amint az alábbi képernyőképen látható, két keresési érték van az E6-ban és az E7-ben. Miután megkapta az első eredményt az F6-ban, húzza a VLOOKUP képletet az F6 cellából az F7-be, és egy #N/A hibaeredmény jelenik meg. Ennek az az oka, hogy a cellahivatkozások (B6:C12) alapértelmezés szerint relatívak, és a sorokon lefelé haladva módosíthatók. A táblázattömb lejjebb került a B7:C13-ba, amely már nem tartalmazza a 73-as keresési pontszámot.
Megoldás
Zárolnia kell a táblatömböt, hogy állandó maradjon az a hozzáadásával $ jel a sorok és oszlopok előtt a cellahivatkozásokban. Ha többet szeretne megtudni az Excel abszolút hivatkozásáról, tekintse meg ezt az oktatóanyagot: Excel abszolút referencia (hogyan kell elkészíteni és használni).
#VALUE hiba jelenik meg
A következő feltételek miatt a VLOOKUP #VALUE hibaeredményt ad vissza.
1. ok: A keresési érték meghaladja a 255 karaktert
Amint az alábbi képernyőképen látható, a H4 cellában lévő keresési érték meghaladja a 255 karaktert, így a VLOOKUP #VALUE hibaeredményt ad vissza.
Megoldások
A korlátozás megkerüléséhez alkalmazhat egy másik keresési függvényt, amely képes kezelni a hosszabb karakterláncokat. Próbálja ki az alábbi képletek egyikét.
- INDEX és MATCH:
=INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))
- XLOOKUP funkció (elérhető Excel 365, Excel 2021 és újabb verziókban):
=XLOOKUP(H4,B5:B11,E5:E11)
2. ok: A col_index argumentum kisebb, mint 1
Az oszlopindex a visszaadni kívánt értéket tartalmazó táblatömb oszlopszámát adja meg. Ennek az argumentumnak pozitív számnak kell lennie, amely megfelel a táblázattömb egy érvényes oszlopának.
Ha olyan oszlopindexet ad meg, amely kisebb, mint 1 (azaz nulla vagy negatív), a VLOOKUP nem fogja tudni megtalálni az oszlopot a táblázattömbben.
Megoldás
A probléma megoldásához győződjön meg arról, hogy a VLOOKUP képlet oszlopindex argumentuma pozitív szám, amely megfelel a táblatömb egy érvényes oszlopának.
#REF hiba visszaküldve
Ez a szakasz felsorolja az egyik okot, amiért a VLOOKUP #REF hibát ad vissza, és megoldásokat kínál erre a problémára.
Ok: A col_index argumentum nagyobb, mint az oszlopok száma
Amint az alábbi képernyőképen látható, a táblázattömbnek csak 4 oszlopa van. A VLOOKUP képletben megadott oszlopindex azonban 5, ami nagyobb, mint a táblázattömb oszlopainak száma. Ennek eredményeként a VLOOKUP nem tudja megtalálni az oszlopot, és végül #REF hibát ad vissza.
Megoldások
- Adjon meg egy helyes oszlopszámot
Győződjön meg arról, hogy a VLOOKUP képlet oszlopindex argumentuma olyan szám, amely megfelel a táblázattömb egy érvényes oszlopának.
- Az oszlopszám automatikus lekérése a megadott oszlopfejléc alapján
Ha a táblázat sok oszlopot tartalmaz, akkor gondot okozhat a helyes oszlopindexszám meghatározása. Itt beágyazhatja a MATCH függvényt a VLOOKUP függvénybe, hogy megkeresse az oszlop pozícióját egy bizonyos oszlopfejléc alapján.
=VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)
Megjegyzések: A fenti képletben a MATCH("E-mail",B5:E5, 0) A függvény a "E-mail" oszlopban a B6:E12 dátumtartományban. Itt az eredmény 4, amelyet a VLOOKUP függvény col_indexeként használ.
Helytelen értéket adunk vissza
Ha úgy találja, hogy a VLOOKUP nem a megfelelő eredményt adja vissza, azt a következő okok okozhatják
1. ok: A keresési oszlop nincs növekvő sorrendben rendezve
Ha az utolsó argumentumot a TRUE (Vagy üresen hagyta) hozzávetőleges egyezés esetén, és a keresési oszlop nincs növekvő sorrendben rendezve, előfordulhat, hogy a kapott érték helytelen.
Megoldás
Ha a keresési oszlopot növekvő sorrendbe rendezi, az segíthet a probléma megoldásában. Ehhez kövesse az alábbi lépéseket:
- Jelölje ki az adatcellákat a keresési oszlopban, lépjen a dátum fülre kattintva Rendezés a legkisebbtől a legnagyobbig a Rendezés és szűrés csoport.
- A Rendezés figyelmeztetés párbeszédpanelen válassza a ikont Bontsa ki a választékot opciót, és kattintson rá OK.
2. ok: Egy oszlop beillesztésre vagy eltávolításra került
Ahogy az alábbi képernyőképen is látható, az eredetileg visszaadni kívánt érték a táblázattömb negyedik oszlopában található, ezért a col_index számot 4-ben adom meg. Új oszlop beszúrásakor az eredmény oszlop a táblázat ötödik oszlopa lesz. tömböt, aminek következtében a VLOOKUP rossz oszlopból adja vissza az eredményt.
Megoldások
Íme két megoldás az Ön számára.
- Manuálisan módosíthatja az oszlop indexszámát, hogy megfeleljen a visszatérési oszlop pozíciójának. Az itt található képletet a következőre kell módosítani:
=VLOOKUP(H6,B6:F12,5,FALSE)
- Ha mindig egy certia oszlopból szeretné visszaadni az eredményt, például ebben a példában az E-mail oszlopból. Az alábbi képlet segíthet az oszlopindex automatikus egyeztetésében az adott oszlopfejléc alapján, függetlenül attól, hogy az oszlopokat beszúrjuk vagy eltávolítjuk a táblázattömbből.
=VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)
Egyéb funkciók megjegyzései
- A VLOOKUP csak balról jobbra haladva keresi az értéket.
A keresési érték a bal szélső oszlopban található, az eredményértéknek pedig a keresési oszloptól jobbra lévő bármely oszlopban kell lennie. - Ha az utolsó argumentumot üresen hagyja, a VLOOKUP alapértelmezés szerint hozzávetőleges egyezést használ.
- A VLOOKUP kis- és nagybetűket nem érzékeny keresést hajt végre.
- Több egyezés esetén a VLOOKUP csak a táblázattömbben talált első egyezést adja vissza, a táblatömbben lévő sorok sorrendje alapján.
Kapcsolódó cikkek
20+ VLOOKUP példa kezdő és haladó Excel felhasználóknak
Ez az oktatóanyag lépésről lépésre bemutatja a Vlookup funkció használatát az Excelben, több tucat alapvető és haladó példával.
VLOOKUP jobbról balra
Ha meg szeretne keresni egy adott értéket bármely másik oszlopban, és vissza szeretné adni a relatív értéket a bal oldalra, az oktatóanyagban található módszerek segíthetnek ennek a feladatnak a végrehajtásában.
Vlookup alulról felfelé
Ez az oktatóanyag két módszert kínál az egyező érték alulról felfelé történő megkeresésére.
Végezzen kis- és nagybetűk közötti vlookup-ot
Ha kis- és nagybetűket érzékeny VLOOKUP-t szeretne végezni az Excelben, az oktatóanyagban szereplő módszer jót tehet.
A VLOOKUP megtartja a forrás formázását
Ez az oktatóanyag olyan módszert kínál, amely segít megőrizni az eredményül kapott cella összes formázását, amikor a Vlookup programot Excelben végez.
A legjobb irodai hatékonyságnövelő eszközök
Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz. Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...
Az Office lap füles felületet hoz az Office-ba, és sokkal könnyebbé teszi a munkáját
- Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
- Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
- 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!
Tartalomjegyzék
- Kapcsolódó videók
- Az érvek lépésről lépésre történő magyarázata
- Szintaxis és érvek
- VLOOKUP Példák
- Pontos egyezés vs. hozzávetőleges egyezés
- VLOOKUP több feltétellel
- Gyakori hibák és megoldások
- #N/A hiba
- #VALUE hiba
- #REF hiba
- Helytelen érték
- Egyéb funkciók megjegyzései
- Kapcsolódó cikkek
- A legjobb irodai termelékenységi eszközök
- Hozzászólások