Az Excel függvény alkalmazása és meghatározása
A VLOOKUP egy Excel funkció, amellyel a felhasználó keresheti és értékelheti a táblázat tartalmát. Ez a funkció az Excel 2007 for Windows és Mac verzióiban érhető el.
Mi az a VLOOKUP?
A VLOOKUP lehetséges felhasználási módjait itt egy példával magyarázzuk: Ebben az irodalom nagy rajongója vagy, és ezért elkészítetted saját Excel táblázatodat, amelyben gondosan válogathatod az összegyűjtött könyveket. Minden művet a következő kategóriákra vonatkozó információkkal kell megadni:
-
szerző
-
cím
-
Oldalszám
-
Kiadási év
Most szeretne adni egy barátjának egy könyvtippet, amelyet magával vihet a következő találkozón. Sajnos csak a szerzőre lehet gondolni, a könyv címére nem. Itt jön szóba a VLOOKUP, mivel ezzel a bemeneti értékkel egy csapásra kidobhatja a keresett információt.
Hogyan kell használni a VLOOKUP -ot?
Mielőtt a képletek megfogalmazására gondolnánk, meg kell határozni, hogy a beviteli mező és a különböző kimeneti mezők hol lesznek később. Ehhez érdemes létrehozni egy külön táblázatot, amely kezdetben üres, és így helyet biztosít az említett információknak. Ha ezt az új táblázatot a meglévő tábla példája alapján tervezi, akkor később időtakarékos előnyt élvez.
Ennek alapján a VLOOKUP képlet manuálisan vagy Excel segítségével automatikusan létrehozható. A kezdők számára érdemes az utóbbi megközelítést alkalmazni, hogy fokozatosan megismerjék a képlet szerkezetét és hatását. Ehhez válassza ki a „Beszúrás funkció” gombot a „Képletek” lapon. A VLOOKUP rejtve van a megnyíló ablakban. A megerősítés után ismét megnyílik egy ablak, amelyben a képlet négy paramétere kitölthető. Ezek:
-
Keresési feltétel
-
mátrix
-
Oszlopindex
-
Area_reference
A képlet nyers tervezete így néz ki:
= VLOOKUP (keresési feltétel, mátrix, oszlopindex, tartomány_link)
és egy ilyen lehetséges alkalmazásban:
= VLOOKUP (H3; A3: E40; 5)
Keresési feltétel
Annak érdekében, hogy a függvény tudja, melyik értéket kell használni kiindulási pontként, a két lépéssel korábban beviteli mezőként kiválasztott sort a "Keresési feltétel" mező jelzi. Példánkban a könyv szerzőjének „Phillip Pulmann” nevét írjuk be. Ez rugalmassá teszi a képletet, és nem kell újra beállítani, amint a megadott érték megváltozik.
mátrix
A "Mátrix" beviteli mező azt a táblázatot írja le, amelyben a kimeneti információ megtalálható. Ez a speciális mátrix tehát a könyv címét, az oldalszámot és a megjelenés évét tartalmazó oszlopokat is tartalmazza.
A mátrixot egyszer teljesen kiválasztják a címsorok nélkül a bal felső saroktól a jobb alsó margóig. Ily módon az Excel tudja, hogy mely tartalmakat kell figyelembe venni az értékelés során.
Oszlopindex
Az "oszlopindex" beviteli mezője arra kéri a felhasználót, hogy határozza meg a mátrix azon oszlopát, amelyben csak a keresett érték szerepel. Az oszlopok kiosztása időrendben van számozva. Ez azt jelenti, hogy a táblázat első oszlopa az 1 -es, a második a 2 -es értéket kapja meg. Példánkban ez megfelel a szerző 1 oszlopindexének, a cím 2 oszlopindexének, az oldalszám 3 -as indexének és a 4. oszlopmutató a közzététel évére.
Annak érdekében, hogy a táblázat a lehető legrugalmasabb legyen, a szám helyett az oszlop fejléce összekapcsolható. Ennek az az előnye, hogy a képlet gond nélkül átvihető más sorokba is, mivel az oszlopfejléc minden alkalommal rugalmasan alakítható.
Figyelem: A VLOOKUP balról jobbra olvassa a mátrixot, ezért az oszlopindexet az oszlop jobb oldalán kell elhelyezni a keresési feltételhez, hogy a függvény figyelembe vegye!
Area_reference
A "Range_Lookup" paraméter kiegészíti a VLOOKUP képletet a táblázat kiértékelésének pontosságával. Ez azonban eltér a képlet korábban említett összetevőitől, mivel nem kötelező. Ha a "0" értéket "helytelen" -ként adja meg, az Excel csak a keresési feltételként megadott értéket keresi. Az "igaz" 1 értékkel azonban a nyilvánvaló értékek keresése folytatódik, ha a pontos érték nem található.
Ennek a paraméternek a megadása nem kötelező, mert az 1 érték alapértelmezés szerint be van állítva. Ez a beállítás később hasznos lesz a több keresési feltétellel rendelkező speciális VLOOKUP -ban.
Az egyesülés
Amint minden szükséges paramétert beállított, a VLOOKUP használható. A keresési feltétel megadása és a funkció megerősítése után a keresett érték megjelenik a kimeneti mezőként definiált sorban.
Példánkban most megjelenik az „Arany iránytű” könyv címe, amely megfelel a megadott szerzőnek. Annak érdekében, hogy gyorsan megtudja az oldalszámot és a megjelenés évét, semmi mást nem kell tennie, mint a meglévő VLOOKUP képletet a következő cellákba húzni. Ez olyan egyszerű, mert a VLOOKUP oszlopindexe össze van kapcsolva az első táblázat oszlopfejlécével, és a második táblázat is ugyanabban a sorrendben van felépítve.
Abban az esetben, ha a táblázatok eltérnek egymástól, vagy ha mindennek ellenére hiba lép fel, a VLOOKUP képlet manuálisan is megváltoztatható. Ehhez az oszlopindex utolsó előtti számjegyét egyeztetni kell a kiadandó új érték oszlopával.
VLOOKUP több keresési feltétellel
Gyakran előfordul, hogy egyetlen keresési feltétel nem elegendő egy nagy Excel -táblázat pontos értékeléséhez. Ekkor érdemes több keresési feltétellel futtatni a VLOOKUP -ot. Ehhez a meglévő képletet ki kell egészíteni egy további IF függvénnyel. Ily módon akár nyolc különböző keresési feltétel is figyelembe vehető az alkalmazás során.
VLOOKUP több Excel táblázatban
Ha a keresési feltétel nem csak az egyik táblázatban, hanem esetleg egy másik táblázatban is megtalálható, akkor a VLOOKUP képlet ennek megfelelően módosítható. Ehhez az if függvényt és az ISERROR függvényt is a meglévő képlet elé kell helyezni. Ehhez öt paraméterre van szükség:
-
Keresési feltétel
-
Mátrix1 és Mátrix 2
-
Oszlopindex1 és oszlopindex2
Az eredmény így néz ki:
= HA (ISERROR (VLOOKUP (keresési feltétel, mátrix1, oszlopindex1, 0));
VLOOKUP (keresési feltétel; mátrix2; oszlopindex2,0); VLOOKUP (keresési feltétel; mátrix1; oszlopindex1;))
és egy ilyen lehetséges alkalmazásban:
= HA (ISERROR (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
A keresési feltétel a keresendő érték beillesztésére szolgál a két táblázatba. A Matrix1 és a Matrix2 határozza meg a két táblázat megfelelő cellaterületeit. Az index1 és az oszlopindex2 oszlopok részletesebben határozzák meg, hogy a megfelelő táblázatok mely oszlopaiban kell keresni.
Ha a keresett érték mindkét táblában megtalálható, az Excel az eredményt az első táblázatból adja ki. Ha azonban az érték a két táblázat egyikében sem található, hibaüzenet jelenik meg. A képlet előnye, hogy a két listának nem kell azonos szerkezetűnek vagy azonos méretűnek lennie.
Rendeljen értékeket a kategóriákhoz a VLOOKUP használatával
A VLOOKUP egy további funkciója lehetővé teszi a felsorolt értékek automatikus felosztását tetszés szerinti betűkre és predikátumokra. Korábbi példánkban egy további táblázat oszlopot kell beszúrni a könyvtípushoz. A legfeljebb 50 oldalas könyveknek a novella műfajába kell tartozniuk, míg az 51-150 oldalas könyvek a novellához, 151 oldal pedig a regényhez tartoznak. Ennek lehetővé tétele érdekében a VLOOKUP -ban nincs szükség további képletre, csak a „{}” göndör zárójelek használatára. A kész képlet így néz ki:
= VLOOKUP (B1; {1. "Novella"; 51. "Novella"; 151. "Regény"}; 2)
A göndör zárójelek tartalma egy mátrixot jelöl, amely meghatározza az adott könyvtípus területét. Az oldalhossz hozzárendelése a megfelelő nemzetséghez ezért a göndör zárójelek között található. A képlet értékpárokat használ, mindegyiket egy pont választja el. A mátrix {1. "Novella"; 51. "Novella"; 151. "Novel"} a következőképpen olvasható:
"1 -ből egy novella, 51 -ből egy novella, 151 -ből egy regény."
Ez a mátrix könnyen hozzáigazítható a különböző feladatokhoz. Ez egyrészt a mátrixok méretére és számára, valamint azok megjelölésére vonatkozik. Tehát lehetséges karakterláncokat vagy számokat eredményezni az egyes betűk helyett. Mindössze annyit kell tennie, hogy módosítja a képlet betűit.
VLOOKUP több munkalapon
A VLOOKUP egy másik funkciója lehetővé teszi a felhasználók számára, hogy különböző táblázatokban található tartalmakat linkeljenek. Példánkban ez a lehetőség akkor lehet hasznos, ha az információkat először különböző munkalapokra rendezzük, majd összefoglaló táblázatban frissítjük.
Képzelje el, hogy a könyvei mellett Excel -táblázatban is felsorolja az összegyűjtött filmjeit. Ezután mindkét gyűjteményt egyetlen nagy táblázatba egyesíti.
Ennek az eljárásnak az előnye nemcsak a megnövekedett sorrendben rejlik, hanem a lehetséges hibák elkerülésében is. Ha új bejegyzést szeretne létrehozni vagy meglévőt frissíteni, akkor nem a nagy táblázatban kell keresnie, hanem hozzáférhet a kisebbekhez. Az értékek ezután automatikusan átkerülnek az összesítő Excel táblába. Ez feleslegessé teszi a nagy táblázat újratelepítését, ami a legjobb esetben is elkerüli a szerencsétlen lépést és a hibaüzenetek későbbi láncolását.
Hogyan néz ki a képlet?
Ezt a funkciót egy másik képlet beszúrása teszi lehetővé. Míg a több feltétellel történő kereséshez további IF képletre volt szükség, a több munkalappal való munkához INDIRECT képlet szükséges. Ez lehetővé teszi egy másik táblázatból származó tartomány megadását a VLOOKUP mátrixhoz.
= VLOOKUP (keresési feltétel; KÖZVETLEN (mátrix); oszlopindex; tartomány_link)
Figyelem: Ez a képlet csak akkor működik, ha a különböző lapok egyes táblái ugyanazokkal a nevekkel rendelkeznek, mint az általános táblázat oszlopfejlécei. A teljes táblázatok elnevezhetők a "Név mezőben" a bal felső sarokban a cellarács felett. A már megnevezett táblázatok a Ctrl + F3 billentyűkombinációval tekinthetők meg.
A felmerülő hibaüzenetek kezelése
A kapcsolt Excel táblázatokkal való munka nem kívánt problémákhoz vezethet. Ide tartozik különösen a hibás értékek kiadása. Abban az esetben, ha rossz 0 értéket ad ki, van egy kis probléma az Excel beállításaiban, amely gyorsan kijavítható.
Az #NV gyakori hibaüzenet viszont a VLOOKUP szándékos funkciója, amely jelzi a felhasználó számára, hogy a szükséges érték nem érhető el. Ezt a jegyzetet egy képlet segítségével lehet másképp megtervezni.
VLOOKUP - áttekintés
A VLOOKUP egy hasznos Excel funkció, amellyel táblázatokat kereshet és értékelhet. Előnyei nyilvánvalóak a felhasználóbarát és rugalmas alkalmazásban. Ily módon mindenki, aki rendszeresen dolgozik Excel táblákkal, részesülhet a funkcióból. Legyen az a magángyűjtő, aki saját kis táblákat készít, vagy a nagyvállalat, amely lényegesen jelentősebb adatkészleteket dolgoz fel.
Ha viszont továbbra is vannak megválaszolatlan kérései, amelyeknek a VLOOKUP nem tudott eleget tenni, akkor várhat egy további Excel-opcióra: a Microsoft 2022-2023 eleje óta kínálja az Excel 365 felhasználóknak az új XLOOKUP-ot. Ez a VLOOKUP kompetenciáira épít, és kiegészíti azokat további, néha még egyszerűbb funkciókkal. Ezért ezen a ponton új rutin nyílik meg az adatok kiértékelésében is.
