01. 03. sz
Keresse meg az adatok közeledő adatait az Excel VLOOKUP segítségével
A VLOOKUP funkció működése
Az Excel VLOOKUP függvénye , amely a függőleges keresést jelöli, fel lehet keresni az adatok vagy az adatbázis adatbázisain található konkrét információkat.
A VLOOKUP általában egyetlen adatmezőt ad ki kimenetként. Hogyan történik ez:
- Ön megad egy nevet vagy keresési_értéket, amely megmondja a VLOOKUP-nak, hogy melyik sor vagy rekord az adattáblázatban keresi a kívánt adatokat
- A keresett adatok oszlopszámát - az úgynevezett col_index_num - értékét adja meg
- A függvény keresi a lookup_value értéket az adatlap első oszlopában
- A VLOOKUP ezután megtalálja és visszaadja a keresett információt egy azonos rekord másik mezőjéről a mellékelt oszlopszámmal
Az adatok első rendezése
Bár nem mindig feltétlenül szükséges, először is rendezni kell a VLOOKUP által keresett adatmennyiséget növekvő sorrendben a rendezési kulcstartomány első oszlopában.
Ha az adatok nincsenek rendezve, akkor a VLOOKUP helytelen eredményt adhat vissza.
A VLOOKUP függvény szintaxisa és érvei
A függvény szintaxisa a függvény elrendezésére utal, és magában foglalja a függvény nevét, zárójeleit és argumentumait .
A VLOOKUP függvény szintaxisa:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
lookup _value - (kötelező) a keresendő érték - például a fenti képen értékesített mennyiség
table_array - (kötelező) Ez az adatlap, amelyet a VLOOKUP keresi, hogy megtalálja az utána következő adatokat.
- A táblaterületnek legalább két oszlopadatot kell tartalmaznia
- Az első oszlop általában a lookup_value értéket tartalmazza
col_index_num - (szükséges) a talált érték oszlopszáma .
- A számozás az 1. oszlopban a keresési kulcs oszlopban kezdődik
- Ha a col_index_num értéke nagyobb, mint a table_array argumentumban kiválasztott oszlopok száma, a #REF! a funkció visszaadja a hibát
range_lookup - (opcionális) jelzi, hogy a tartomány növekvő sorrendbe rendezve van-e.
- Az első oszlopban lévő adatokat rendezési kulcsként használják
- Egy logikai érték - TRUE vagy FALSE az egyetlen elfogadható érték
- Ha kihagyja, az érték alapértelmezés szerint TRUE értékre van állítva
- Ha TRUE értékre van állítva vagy kihagyott, és a tartomány első oszlopa nem növekvő sorrendben van rendezve, akkor a helytelen eredmény
- Ha TRUE értékre van állítva vagy kihagyott, és nem találta meg a keresési _ értéket, akkor a keresett kulcsként a legközelebbi méretet vagy értéket választja ki
- Ha FALSE értékre van állítva, a VLOOKUP csak a keresési _value pontos egyezését fogadja el. Ha több egyezési érték van, akkor az első illeszkedő érték visszaadódik
- Ha FALSE értékre van állítva, és a kereső kulcsnak nincs megfelelő értéke, a függvény visszaküldi a # N / A hibát
Példa: Keresse meg a megvásárolt mennyiségre vonatkozó kedvezményt
A fenti képen látható példa a VLOOKUP függvényt használja a diszkontráta megállapításához, amely a vásárolt tételek mennyiségétől függően változik.
A példa azt mutatja, hogy a 19 termék megvásárlásának kedvezménye 2%. Ez azért van, mert a Mennyiség oszlop értékeinek tartományát tartalmazza. Ennek eredményeként a VLOOKUP nem talál pontos találatot. Ehelyett egy hozzávetőleges egyezést kell találni a helyes diszkontráta visszaadásához.
Megközelítő találatok megkeresése:
- rendezze a táblázatban szereplő adatokat növekvő sorrendben;
- állítsa a range_lookup argumentumot TRUE értékre
A példában a VLOOKUP függvényt tartalmazó alábbi képletet használják a megvásárolt áruk mennyiségének kedvezményére.
= FKERES (C2, $ C $ 5: $ D $ 8,2, TRUE)
Bár ez a képlet csak beilleszthető egy munkalap cellába, egy másik lehetőség az alább felsorolt lépések végrehajtásához használja a függvény párbeszédablakát az argumentumok beírásához.
- A párbeszédpanel használata gyakran megkönnyíti a függvények argumentumainak beírását.
A VLOOKUP párbeszédpanel megnyitása
A fenti képen a VLOOKUP függvénybe a B2 cellába beírt lépések a következők:
- Kattintson a B2 cellára, hogy az aktív cellát létrehozza - a hely, ahol a VLOOKUP funkció eredményei jelennek meg
- Kattintson a Formulák lapra.
- Válassza a Keresés és hivatkozás lehetőséget a szalagból a funkció legördülő listájának megnyitásához
- Kattintson a VLOOKUP elemre a listában, hogy megjelenítse a funkció párbeszédablakát
02. 03. sz
Az Excel VLOOKUP függvény argumentumainak bevitele
A Cell Referenciákra mutat
A VLOOKUP függvény argumentumai a párbeszédpanel külön soraiba kerülnek, ahogyan a fenti képen látható.
Az argumentumként használt cella hivatkozásokat beírhatja a megfelelő sorba, vagy az alábbi lépések szerint a mutató, amely magában foglalja a kívánt cellatartomány kijelölését az egérmutatóval, felhasználható a párbeszédablakba való belépéshez .
A mutató használatának előnyei a következők:
- Gyorsabb, mint a gépelés;
- Kevesebb hiba történik a helyes referenciák beírásával.
Relatív és abszolút sejtes referenciák használata érvekkel
Nem ritka, hogy a VLOOKUP többszörös másolatát felhasználva ugyanarra az adatlapra vonatkozó különböző információkat tér vissza. Ennek megkönnyítése érdekében a VLOOKUP gyakran átmásolható egyik celláról a másikra. Ha a funkciókat más cellákra másolja, ügyelni kell arra, hogy az eredményül kapott sejtes hivatkozások helyesek legyenek a funkció új helyén.
A fenti képen dollárjelek ( $ ) foglalják el a table_array argumentum cell referenciáit , jelezve, hogy ezek abszolút cellahivatkozások , ami azt jelenti, hogy nem változnak meg, ha a függvényt más cellába másolja. Ez kívánatos, mivel a VLOOKUP több példánya ugyanazt az adatlapot említi, mint az információforrás.
A lookup_value-hoz használt cellahivatkozást viszont nem dollárjelek veszik körül, ami relatív cellahivatkozást tesz lehetővé. A relatív cellahivatkozások megváltoztatják a másolást, hogy az új helyüket az általuk hivatkozott adatok helyéhez viszonyítva tükrözzék.
A funkcióérvek bevitele
- Kattintson a Lookup _value vonalra a VLOOKUP párbeszédpanelen
- Kattintson a C2 cellára a munkalapon, hogy ezt a cellahivatkozást a kereső kulcs- argumentumként adja meg
- Kattintson a párbeszédpanel Table_array sorára
- Jelölje ki a munkalapon a C5-D8 cellákat, hogy ezt a tartományt a Table_array argumentumként adja meg - az asztalfejezetek nem szerepelnek
- Nyomja meg az F4 billentyűt a billentyűzeten, hogy megváltoztassa a tartományt az abszolút cellahivatkozásokra
- Kattintson a párbeszédpanel Col_index_num sorára
- A Col_index_num argumentumot írja be ebben a sorban, mivel a diszkontráták a Table_array argumentum 2. oszlopában találhatók
- Kattintson a párbeszédpanel Range_lookup sorára
- Írja be a True argumentumot a Range_lookup argumentumként
- Nyomja meg az Enter gombot a billentyűzeten, hogy bezárja a párbeszédpanelt, és térjen vissza a munkalaphoz
- A válasz 2% (a megvásárolt mennyiség diszkontrátája) a munkalap D2 cellájában jelenik meg
- Amikor a D2 cellára kattint, a teljes függvény = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) jelenik meg a munkalapon található képletsávban
Miért érkezett vissza VLOOKUP 2% eredményként?
- A példában a Mennyiség oszlop nem tartalmaz pontosan a 19 keresési kulcs értékét.
- Mivel az is_sorted argumentum TRUE, a VLOOKUP megközelítőleg egyezik a keresési kulcs értékével.
- A legközelebbi, 19-nél kisebb keresési kulcsnál kisebb méretű érték 11.
- A VLOOKUP tehát a 11-et tartalmazó sorban a kedvezményes százalékot keresi, ennek következtében 2% -os diszkontrátát ad vissza.
03. 03. sz
Excel VLOOKUP nem működik: # N / A és #REF hibák
VLOOKUP hibaüzenetek
A következő hibaüzenetek társulnak a VLOOKUP programmal.
A # N / A ("érték nem áll rendelkezésre") Hiba jelenik meg Ha:
- A keresési_érték nem található az argumentum első oszlopában
- A Table_array argumentum pontatlan. Például az argumentum tartalmazhat üres oszlopokat a tartomány bal oldalán
- A Range_lookup argumentum FALSE értékre van állítva, és a keresés kulcsának pontos egyezése nem található a tartomány első oszlopában
- A Range_lookup argumentum értéke TRUE, és a tartomány első oszlopában lévő összes érték nagyobb, mint a keresési kulcs
A #REF! ("tartományon kívüli referencia") Hiba jelenik meg Ha:
- A Col_index_num argumentum nagyobb, mint a Table_array oszlopok száma