Excel

23 dolog, amit tudnod kell a VLOOKUP -ról

23 Things You Should Know About Vlookup

Ha információkat szeretne lekérni egy táblázatból, az Excel VLOOKUP funkció nagyszerű megoldás. Az a képesség, hogy dinamikusan megkeressük és lekérjük az információkat egy táblázatból, sok felhasználó számára megváltoztatja a játékot, és a VLOOKUP mindenhol megtalálható.





És bár a VLOOKUP viszonylag könnyen használható, rengeteg hiba történhet. Az egyik ok az, hogy a VLOOKUP -nak jelentős tervezési hibája van - alapértelmezés szerint azt feltételezi, hogy rendben van a hozzávetőleges egyezéssel. Ami valószínűleg nem vagy.

Ez olyan eredményeket eredményezhet teljesen normálisan nézzen ki , pedig azok teljesen helytelen . Hidd el, ezt NEM szeretnéd elmagyarázni a főnöködnek, miután már elküldte a táblázatot a vezetőségnek :)





Olvassa el alább, hogy megtudja, hogyan kezelje ezt a kihívást, és fedezzen fel további tippeket az elsajátításhoz Excel VLOOKUP funkció .

1. A VLOOKUP működése

A VLOOKUP egy olyan funkció, amely megkeresi és visszakeresi az adatokat egy táblázatban. A VLOOKUP „V” jelentése függőleges, ami azt jelenti, hogy a táblázat adatait függőlegesen kell elhelyezni, az adatokat pedig sorokban kell elhelyezni. (A vízszintesen strukturált adatokért lásd HLOOKUP ).



Ha van egy jól strukturált táblája, függőlegesen elrendezett információkkal, és bal oldali oszlopa, amellyel sorokat illeszthet, akkor valószínűleg használhatja a VLOOKUP funkciót.

A VLOOKUP megköveteli, hogy a táblázatot úgy kell felépíteni, hogy a keresési értékek a bal szélső oszlopban jelenjenek meg. A lekérni kívánt adatok (eredményértékek) bármely jobb oldali oszlopban megjelenhetnek. A VLOOKUP használatakor képzelje el, hogy a táblázat minden oszlopa balról indulva számozott. Ha egy adott oszlopból szeretne értéket kapni, egyszerűen adja meg a megfelelő számot „oszlopindexként”. Az alábbi példában szeretnénk megkeresni az e -mail címet, ezért a 4 -es számot használjuk az oszlopindexhez:

A VLOOKUP működésének áttekintése

A fenti táblázatban a munkavállalói azonosítók a bal oldali 1. oszlopban, az e -mail címek pedig a 4. oszlopban találhatók.

A VLOOKUP használatához 4 információt vagy „érvet” kell megadnia:

  1. Az általad keresett érték ( lookup_value )
  2. A táblázatot alkotó cellatartomány ( table_array )
  3. Az oszlop száma, ahonnan lekérhető az eredmény ( oszlop_index )
  4. A mérkőzés mód ( range_lookup , IGAZ = hozzávetőleges, HAMIS = pontos)

Videó: A VLOOKUP használata (3 perc)

Ha még mindig nem érti a VLOOKUP alapötletét, Jon Acampora az Excel Campuson nagyszerű magyarázat a Starbucks kávé menüje alapján.

2. A VLOOKUP csak jól néz ki

A VLOOKUP talán legnagyobb korlátja, hogy csak jobbra nézhet az adatok lekérésére.

Ez azt jelenti, hogy a VLOOKUP csak a táblázat első oszlopától jobbra lévő oszlopokból szerezhet adatokat. Amikor a keresési értékek az első (bal szélső) oszlopban jelennek meg, ez a korlátozás nem jelent sokat, mivel az összes többi oszlop már jobbra van. Ha azonban a keresési oszlop megjelenik valahol a táblázaton belül, akkor csak az oszloptól jobbra lévő oszlopokból tud keresni értékeket. Ezenkívül egy kisebb táblázatot kell biztosítania a VLOOKUP számára, amely a keresési oszloppal kezdődik.

A VLOOKUP csak a keresési érték oszloptól jobbra nézhet

Ezt a korlátozást leküzdheti az INDEX és a MATCH használatával a VLOOKUP helyett.

3. A VLOOKUP megtalálja az első egyezést

Pontos egyezés módban, ha a keresési oszlop ismétlődő értékeket tartalmaz, a VLOOKUP csak az első értékkel fog megegyezni. Az alábbi példában a VLOOKUP használatával keressük a keresztnevet, a VLOOKUP pedig pontos egyezésre van beállítva. Bár a listában két Janet szerepel, a VLOOKUP csak az elsővel egyezik:

A VLOOKUP mindig megtalálja az első egyezést

Megjegyzés: a viselkedés megváltozhat, ha a VLOOKUP funkciót hozzávetőleges egyezési módban használja. Ez a cikk részletesen ismerteti a témát.

4. A VLOOKUP nem különbözteti meg a kis- és nagybetűket

Érték keresésekor a VLOOKUP nem dolgozza fel a kis- és nagybetűs szöveget eltérően. A VLOOKUP számára a „PQRF” termékkód azonos a „pqrf” kóddal. Az alábbi példában nagybetűs „JANET” betűt keresünk, de a VLOOKUP nem különbözteti meg a kis- és nagybetűket, így egyszerűen illeszkedik a „Janet” betűhöz, mivel ez az első találat:

A VLOOKUP NEM különbözteti meg a kis- és nagybetűket

Mi is kínálunk fizetett képzés VLOOKUP és INDEX/MATCH

5. A VLOOKUP két egyező üzemmóddal rendelkezik

A VLOOKUP két üzemmóddal rendelkezik: pontos egyezés és hozzávetőleges egyezés. A legtöbb esetben valószínűleg a VLOOKUP funkciót szeretné használni pontos egyezési módban. Ennek akkor van értelme, ha valamilyen egyedi kulcs alapján szeretne információt keresni, például termékkódon alapuló termékinformációkat vagy filmadatokat a film címe alapján:

VLOOKUP pontos egyezési példa - megfelelő filmek

A H6 képlet a keresési évre a filmcím pontos egyezése alapján:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

Érdemes azonban hozzávetőleges módot használni azokban az esetekben, amikor nem egyedi azonosítóval egyezik, hanem a „legjobb egyezést” vagy a „legjobb kategóriát” keresi. Például, ha a postaköltséget a súly alapján keresi, az adókulcsot a jövedelem alapján, vagy a jutalék mértékét a havi értékesítési szám alapján. Ezekben az esetekben valószínűleg nem fogja megtalálni a pontos keresési értéket a táblázatban. Ehelyett azt szeretné, ha a VLOOKUP a lehető legjobban megfelelne egy adott keresési értéknek.

VLOOKUP hozzávetőleges egyezési példa - jutalékok

A D5 képlet hozzávetőlegesen illeszkedik a megfelelő jutalék lekéréséhez:

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

6. Vigyázat: A VLOOKUP alapértelmezés szerint hozzávetőleges egyezést használ

A VLOOKUP pontos és hozzávetőleges egyezését a 4. argumentum vezérli, az úgynevezett „tartománykeresés”. Ez a név nem intuitív, ezért meg kell jegyeznie, hogyan működik.

A pontos egyezéshez használja a HAMIS vagy a 0 értéket. A hozzávetőleges egyezéshez állítsa a range_lookup értéket IGAZ -ra vagy 1 -re:

 
= VLOOKUP (value,table,column,TRUE) // approximate match = VLOOKUP (value,table,column,FALSE) // exact match

Sajnos a 4. argumentum, a range_lookup opcionális, és alapértelmezett értéke TRUE, ami azt jelenti, hogy a VLOOKUP alapértelmezés szerint hozzávetőleges egyezést fog végezni. Amikor hozzávetőleges egyezést végez, a VLOOKUP feltételezi, hogy a táblázat rendezett, és bináris keresést hajt végre. Ha egy bináris keresés során a VLOOKUP pontos egyezési értéket talál, akkor az adott sorból ad vissza értéket. Ha azonban a VLOOKUP a keresési értéknél nagyobb értéket talál, akkor az előző sorból ad vissza értéket.

Ez veszélyes alapértelmezés, mivel sokan akaratlanul is elhagyják a VLOOKUP alapértelmezett módját, ami helytelen eredmény amikor a táblázat nincs rendezve.

A probléma elkerülése érdekében feltétlenül használja a HAMIS vagy a nulla értéket a 4. argumentumként, ha pontos egyezést szeretne.

7. Kényszerítheti a VLOOKUP -ot, hogy pontos egyezést végezzen

Ahhoz, hogy a VLOOKUP pontos egyezést találjon, győződjön meg róla, hogy a 4 argumentumot (tartománynézés) HAMIS vagy nulla értékre állítja. Ez a két képlet egyenértékű:

 
= VLOOKUP (value, data, column, FALSE) = VLOOKUP (value, data, column, 0)

Pontos egyezés módban, ha a VLOOKUP nem talál értéket, akkor a #N/A értéket adja vissza. Ez egyértelműen jelzi, hogy az érték nem található a táblázatban.

8. Mondhatja a VLOOKUP -nak, hogy végezzen hozzávetőleges egyezést

A VLOOKUP hozzávetőleges egyezési módban való használatához hagyja ki a 4. argumentumot (range_lookup), vagy adja meg az IGAZ vagy az 1 értéket. Ez a 3 képlet egyenértékű:

 
= VLOOKUP (value, data, column) = VLOOKUP (value, data, column, 1) = VLOOKUP (value, data, column, TRUE)

Javasoljuk, hogy mindig határozottan állítsa be a range_lookup argumentumot, annak ellenére, hogy a VLOOKUP nem igényli ezt. Így mindig vizuális emlékeztetője van a várt mérkőzésmódról.

Videó: A VLOOKUP használata a hozzávetőleges egyezésekhez

9. A hozzávetőleges egyezésekhez az adatokat rendezni kell

Ha hozzávetőleges módú egyezést használ, akkor az adatai rendezni kell növekvő sorrendben a keresési érték szerint. Ellenkező esetben kaphat egy helytelen eredmények . Ne feledje, hogy néha szöveges adatok is előfordulhatnak néz rendezett, pedig nem az.

Felienne Hermans van erre egy remek példa , egy hűvös elemzésből, amit a ténylegesről készített Enron táblázatok!

10. A VLOOKUP egyesítheti az adatokat különböző táblázatokban

A VLOOKUP egyik gyakori esete, hogy két vagy több táblából származó adatokat egyesít. Például előfordulhat, hogy az egyik táblázatban vannak a rendelési adatok, a másikban pedig az ügyfelek adatai, és néhány ügyféladatot szeretne bevinni a rendelési táblázatba elemzés céljából:

A VLOOKUP egyesíti az adatokat a táblázatok összekapcsolásával

Mivel az ügyfélazonosító mindkét táblában megtalálható, ezzel az értékkel húzhatja be a kívánt adatokat a VLOOKUP segítségével. Csak konfigurálja a VLOOKUP -ot, hogy használja az azonosítót az első táblázatban, és a 2 -es táblázat adatait a kívánt oszlopindexszel. Az alábbi példában két VLOOKUP képletet használunk. Az egyik az ügyfél nevének beírása, a másik az ügyfél állapotának behúzása.

A VLOOKUP egyesíti az adatokat a táblázatok összekapcsolásával

Link: Példa a VLOOKUP -tal való egyesítésre .

Videó: A VLOOKUP használata a táblák egyesítéséhez .

11. A VLOOKUP osztályozhatja vagy kategorizálhatja az adatokat

Ha valaha tetszőleges kategóriákat kell alkalmaznia az adatrekordokra, akkor ezt könnyedén megteheti a VLOOKUP segítségével, egy táblázat segítségével, amely kulcsként szolgál a kategóriák hozzárendeléséhez.

Klasszikus példa az érdemjegyek, ahol pontszám alapján kell osztályzatot rendelni:

A VLOOKUP kategorizálni szokott - osztályzatok kiosztása

Ebben az esetben a VLOOKUP hozzávetőleges egyezésre van beállítva, ezért fontos, hogy a táblázat növekvő sorrendben legyen rendezve.

De a VLOOKUP segítségével tetszőleges kategóriákat is hozzárendelhet. Az alábbi példában a VLOOKUP segítségével kiszámítjuk a csoportokat minden osztályra egy kis táblázat („kulcs”) segítségével, amely meghatározza a csoportosítást.

A VLOOKUP kategorizálásra használt - tetszőleges csoportok hozzárendelése

12. Az abszolút hivatkozások hordozhatóbbá teszik a VLOOKUP -ot

Azokban a helyzetekben, amikor egy táblázat több oszlopából kíván információt lekérni, vagy ha a VLOOKUP másolására és beillesztésére van szüksége, időt és súlyosbodást takaríthat meg, ha abszolút hivatkozásokat használ a keresési értékhez és a táblázat tömbhöz. Ez lehetővé teszi a képlet másolását, majd csak az oszlopindexszám módosítását, hogy ugyanazt a keresést használja egy másik oszlopból származó érték lekéréséhez.

Például, mivel a keresési érték és a tábla tömb abszolút, másolhatjuk a képletet az oszlopok között, majd visszatérhetünk, és szükség szerint módosíthatjuk az oszlopindexet.

Az abszolút hivatkozások hordozhatóbbá teszik a VLOOKUP képleteket

13. A megnevezett tartományok megkönnyítik a VLOOKUP olvasását (és hordozhatóbbá teszik)

Az abszolút tartományok meglehetősen csúnyán néznek ki, így sokkal tisztábbá és könnyebben olvashatóvá tehetik VLOOKUP képleteit, ha az abszolút hivatkozásokat nevezett tartományokra cserélik, amelyek automatikusan abszolút értékűek.

Például a fenti alkalmazotti adatokra vonatkozó példában elnevezheti a bemeneti cellát „id”, majd a „data” táblázat adatait, és a következőképpen írhatja be a képletet:

A megnevezett tartományok megkönnyítik a VLOOKUP képletek olvasását

Ez a képlet nemcsak könnyebben olvasható, hanem hordozhatóbb is, mivel a megnevezett tartományok automatikusan abszolút értékűek.

14. Egy oszlop beszúrása megszakíthatja a meglévő VLOOKUP képleteket

Ha meglévő VLOOKUP képletek vannak a munkalapon, akkor a képletek összetörhetnek, ha egy oszlopot szúr be a táblázatba. Ennek oka az, hogy a kódolt oszlopindex-értékek nem változnak automatikusan az oszlopok beszúrása vagy törlése esetén.

Ebben a példában a rang és az értékesítés keresései megszakadtak, amikor új oszlopot illesztettek be az év és a rang közé. Az év tovább működik, mert a beillesztett oszlop bal oldalán található:

Oszlop beszúrása a táblázatba megszakíthatja a VLOOKUP funkciót

A probléma elkerülése érdekében kiszámíthatja az oszlopindexet a következő két tippben leírtak szerint.

15. A ROW vagy COLUMN gombokkal kiszámíthatja az oszlopindexet

Ha Ön az a típus, akit bármilyen mennyiségű szerkesztés zavar egy képlet másolása után, akkor a ROW vagy a COLUMN használatával dinamikus oszlopindexeket hozhat létre. Ha egymást követő oszlopokból kap adatokat, ez a trükk lehetővé teszi egy VLOOKUP képlet beállítását, majd másolás nélkül történő másolását.

Például az alábbi munkavállalói adatokkal a COLUMN függvény segítségével dinamikus oszlopindexet állíthatunk elő. A C3 cella első képletéhez a COLUMN önmagában 3 -at ad vissza (mivel a C oszlop a harmadik a munkalapon), így egyszerűen ki kell vonnunk egyet, és át kell másolnunk a képletet:

Példa a COLUMN használatára a VLOOKUP oszlopindexének kiszámításához

Minden képlet azonos, nincs szükség utólagos szerkesztésre.

Az általunk használt képlet a következő:

 
= VLOOKUP (id,data, COLUMN ()-1,0)

16. Használja a VLOOKUP + MATCH billentyűt egy teljesen dinamikus oszlopindexhez

Ha a fenti tippet egy lépéssel továbbviszi, a MATCH használatával megkeresheti az oszlop pozícióját a táblázatban, és visszaadhat egy teljesen dinamikus oszlopindexet.

Ezt néha kétirányú keresésnek is nevezik, mivel mind a sort, mind az oszlopot keresi.

Példa lehet egy adott hónap értékesítőinek keresése egy adott hónapban, vagy az adott termék árának megkeresése egy adott szállítótól.

Tegyük fel például, hogy havi értékesítései vannak értékesítő szerint:

VLOOKUP kétirányú keresés - hogyan kereshető a hónap?

A VLOOKUP könnyen megtalálja az értékesítési személyt, de nem tudja automatikusan kezelni a hónap nevét. A trükk az, hogy a MATCH függvényt használja a statikus oszlopindex helyett.

VLOOKUP kétirányú keresés a MATCH használatával az oszlopindex lekéréséhez

Figyeljük meg, hogy a VLOOKUP által használt oszlopszámok 'szinkronizálása' érdekében egy olyan tartományt adunk meg, amely tartalmazza a táblázat összes oszlopát.

 
= VLOOKUP (H2,data, MATCH (H3,months,0),0)

Megjegyzés: gyakran láthat kétirányú keresést az INDEX és a MATCH segítségével, amely rugalmasságot és nagyobb teljesítményt kínál nagy adathalmazokon. Nézze meg, hogyan lehet ebben a gyors videóban: Hogyan lehet kétirányú keresést végezni az INDEX és a MATCH használatával? .

17. A VLOOKUP lehetővé teszi a helyettesítő karakterek részleges egyezését

Bármikor, amikor a VLOOKUP funkciót használja pontos egyezési módban, lehetősége van helyettesítő karakterek használatára a keresési értékben. Intuitívnak tűnhet, de a helyettesítő karakterek lehetővé teszik a pontos egyezést egy részleges egyezés alapján :)

Az Excel két helyettesítő karaktert biztosít: a csillag (*) egy vagy több karakternek felel meg, a kérdőjel (?) Pedig egy karakternek.

Például egy csillagot közvetlenül beírhat egy cellába, és a VLOOKUP segítségével keresési értékként hivatkozhat rá. Az alábbi képernyőn a „Mon*” értéket a H3 -ba írtuk, ami egy „val” nevű tartomány. Ez azt eredményezi, hogy a VLOOKUP illeszkedik a 'Monet' névhez.

VLOOKUP helyettesítő karakterekkel - közvetlenül egy csillag használatával

A képlet ebben az esetben egyszerű:

 
= VLOOKUP (val,data,1,0)

Ha úgy tetszik, beállíthatja a VLOOKUP képletet, hogy beépített helyettesítő karaktert használjon, például az alábbi példát, ahol egyszerűen összekapcsoljuk a H3-ban szereplő értéket egy csillaggal.

VLOOKUP helyettesítő karakterekkel - a csillag össze van kapcsolva a keresési értékkel

Ebben az esetben a csillagot a VLOOKUP függvényben lévő keresési értékhez fűzzük:

 
= VLOOKUP (val&'*',data,1,0)

Megjegyzés: Legyen óvatos a helyettesítő karakterek és a VLOOKUP használatával. Egyszerű módot kínálnak a „lusta mérkőzés” létrehozására, de megkönnyítik a rossz pár megtalálását is.

18. Megfoghatja a #N/A hibákat, és barátságos üzenetet jeleníthet meg

Pontos egyezés módban a VLOOKUP megjeleníti a #N/A hibát, ha nem talál egyezést. Egyrészt ez hasznos, mert véglegesen megmondja, hogy nincs egyezés a keresési táblázatban. A #N/A hibákat azonban nem túl szórakoztató nézni, ezért többféleképpen is lezárhatja ezt a hibát, és helyette mást jeleníthet meg.

A VLOOKUP használatának megkezdése után a #N/A hiba lép fel, amely akkor fordul elő, ha a VLOOKUP nem talál egyezést.

mikor kell használni az abszolút referenciát az excelben

Ez hasznos hiba, mert a VLOOKUP egyértelműen jelzi, hogy nem találja a keresési értéket. Ebben a példában a „Latte” nem létezik italként a táblázatban, ezért a VLOOKUP #N/A hibát jelez

VLOOKUP: #N/A hiba

A képlet ebben az esetben egy teljesen szabványos egyezés:

 
= VLOOKUP (E6,data,2,0)

A #N/A hibákat azonban nem túl szórakoztató nézni, ezért érdemes észrevenni ezt a hibát, és barátságosabb üzenetet megjeleníteni.

A legegyszerűbb módja annak, hogy a VLOOKUP hibákat elfogja, ha a VLOOKUP -ot az IFERROR függvénybe csomagolja. Az IFERROR lehetővé teszi bármilyen hiba „elkapását”, és az Ön által választott eredmény visszaadását.

Ha ezt a hibát meg akarja jeleníteni, és a hiba helyett egy „nem található” üzenetet jelenít meg, egyszerűen tekerje be az eredeti formulát az IFERROR belsejébe, és állítsa be a kívánt eredményt:

VLOOKUP #N/Hiba az IFERROR miatt

Ha megtalálja a keresési értéket, nem történik hiba, és a VLOOKUP függvény normál eredményt ad vissza. Íme a képlet:

 
= IFERROR ( VLOOKUP (E6,data,2,0),'Not found')

19. A számok szövegként egyezési hibát okozhatnak

Előfordulhat, hogy a VLOOKUP -ban dolgozó táblázat szöveges számokat tartalmaz. Ha egyszerűen szöveget kér le a számokból egy táblázat oszlopából, nem számít. De ha a táblázat első oszlopa szövegként megadott számokat tartalmaz, akkor #N/A hibaüzenetet kap, ha a keresési érték nem szöveg.

A következő példában a bolygótábla azonosítói számok szövegként beírva , ami miatt a VLOOKUP hibát ad vissza, mivel a keresési érték a szám 3:

Szövegként beírt számok VLOOKUP hiba példa

A probléma megoldásához meg kell győződnie arról, hogy a keresési érték és a táblázat első oszlopa egyaránt ugyanazt az adattípust (vagy mindkét számot, vagy mindkét szöveget) tartalmazza.

Ennek egyik módja a keresőoszlop értékeinek számokká alakítása. Ennek egyszerű módja a nulla hozzáadása a paste special használatával.

Ha nem tudja egyszerűen irányítani a forrás táblát, akkor a VLOOKUP képletet úgy is módosíthatja, hogy a keresési értéket szöveggé alakítsa át, ha a következő módon összekapcsolja a (z) értéket:

 
= VLOOKUP (id&'',planets,2,0)

Szöveges VLOOKUP hibamegoldásként megadott számok

Ha nem lehet biztos abban, hogy mikor lesznek számok és mikor lesz szöveg, akkor mindkét lehetőséget kielégítheti úgy, hogy a VLOOKUP -t IFERROR -ba csomagolja, és mindkét képlet kezelésére képes képletet ír:

 
= IFERROR ( VLOOKUP (id,planets,3,0), VLOOKUP (id&'',planets,3,0))

20. A VLOOKUP használatával helyettesítheti a beágyazott IF utasításokat

A VLOOKUP egyik érdekesebb felhasználási módja a beágyazott IF utasítások cseréje. Ha valaha is egymásba ágyazott IF -k sorozatát építette, akkor tudja, hogy jól működnek, de egy kis zárójelet igényelnek. Óvatosnak kell lennie a sorrendben is, amelyben dolgozik, nehogy logikai hibát okozzon.

Például a beágyazott IF -k gyakori használata az, hogy valamilyen pontszám alapján osztályoznak. Az alábbi példában láthatja, hogy egy képletet építettek be egymásba ágyazott IF -kkel, hogy ezt meg is tegyék, útmutatásként a jobb oldali osztályozó billentyűt használva.

Pontszámok hozzárendelése hosszú beágyazott IF képlet segítségével

A teljes beágyazott IF képlet így néz ki:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Ez jól működik, de vegye figyelembe, hogy mind a logika, mind a tényleges pontszámok bele vannak írva a képletbe. Ha a pontozás valamilyen okból megváltozik, akkor gondosan frissítenie kell egy képletet, majd le kell másolnia a teljes táblázatban.

Ezzel szemben a VLOOKUP ugyanazt a fokozatot rendelheti hozzá egy egyszerű képlettel. Mindössze annyit kell tennie, hogy megbizonyosodik arról, hogy a besorolási kulcs tábla be van állítva a VLOOKUP (azaz a legtöbbet pontszámok szerint kell rendezni, és zárójeleket kell tartalmaznia az összes pontszám kezelésére).

Miután megadta a megnevezett tartomány „kulcsát” a fokozatkulcs -táblázathoz, a VLOOKUP képlet nagyon egyszerű, és ugyanazokat a minősítéseket generálja, mint az eredeti beágyazott IF -képlet:

Pontszámok hozzárendelése egyszerű VLOOKUP képlet segítségével

A 'kulcs' nevű osztályozótábla -val egy nagyon egyszerű VLOOKUP képletünk van:

 
= VLOOKUP (C5,key,2,TRUE)

Ennek a módszernek egy szép bónusza, hogy mind a logika, mind a pontszámok közvetlenül az osztályozótáblázatba vannak beépítve. Ha bármi változik, egyszerűen frissítheti a táblázatot, és a VLOOKUP képletek automatikusan frissülnek - nincs szükség szerkesztésre.

Videó: Hogyan lehet a beágyazott IF -ket VLOOKUP -ra cserélni

21. A VLOOKUP csak egyetlen feltételt képes kezelni

A tervezés szerint a VLOOKUP csak egyetlen feltétel alapján képes megtalálni az értékeket, amelyeket keresési értékként ad meg a táblázat első oszlopában (a keresési oszlop).

Ez azt jelenti, hogy nem tehet könnyen olyan dolgokat, mint a „Számvitel” részben a „Smith” vezetéknévvel rendelkező alkalmazott megkeresése, vagy a munkavállaló külön -külön oszlopokban történő megkeresése a kereszt- és vezetéknevek alapján.

Ennek a korlátozásnak azonban vannak módjai. Az egyik megoldás az, hogy létrehoz egy segítő oszlopot, amely összekapcsolja a különböző oszlopok értékeit, hogy több feltételhez hasonlóan viselkedő keresési értékeket hozzon létre. Például itt szeretnénk megtalálni az alkalmazott osztályát és csoportját, de a kereszt- és vezetéknév külön oszlopokban jelenik meg. Hogyan kereshetjük egyszerre mindkettőt?

VLOOKUP több kritériummal kapcsolatos probléma - hogyan lehet megkeresni mind a kereszt-, mind a vezetéknevet?

Először adjon hozzá egy segítő oszlopot, amely egyszerűen összefűzi a kereszt- és vezetékneveket:

VLOOKUP több feltétel 2. lépés - adjon hozzá egy segítő oszlopot, amely több feltételt egyesít

Ezután konfigurálja a VLOOKUP -ot, hogy használja ezt a táblázatot, amely tartalmazza ezt az új oszlopot, és csatlakoztassa a kereszt- és vezetéknevet a keresési értékhez:

VLOOKUP több feltétel 3. lépés - egyesítse a feltételeket a keresési érték létrehozásához

A végső VLOOKUP képlet megkeresi a kereszt- és vezetékneveket, a segítő oszlopot használva kulcsként:

 
= VLOOKUP (C3&D3,data,4,0)

22. Két VLOOKUPS gyorsabb, mint egy VLOOKUP

Lehet, hogy teljesen őrültnek tűnik, de ha nagy adathalmaza van, és pontos egyezést kell végeznie, akkor nagyon gyorsíthatja a VLOOKUP -ot, ha hozzáad egy másik VLOOKUP -ot a képlethez!

A háttér: képzelje el, hogy sok rendelési adat áll rendelkezésére, mondjuk több mint 10 000 rekord, és a VLOOKUP használatával keresse meg a rendelés végösszegét a rendelési azonosító alapján. Tehát valami ilyesmit használsz:

 
= VLOOKUP (order_id,order_data, 5, FALSE)

A végén lévő HAMIS arra kényszeríti a VLOOKUP -ot, hogy pontos egyezést végezzen. Pontos egyezést szeretne, mert fennáll annak az esélye, hogy nem talál egy rendelési számot. Ebben az esetben a pontos egyezés beállítása miatt a VLOOKUP #N/A hibát ad vissza.

A probléma az, hogy a pontos egyezések nagyon lassúak, mert az Excelnek lineárisan kell haladnia minden értéken, amíg meg nem találja az egyezést.

Ezzel szemben a hozzávetőleges egyezések villámgyorsak, mert az Excel képes az a bináris keresés .

A probléma a bináris keresésekkel (azaz a VLOOKUP hozzávetőleges egyezési módban) azonban az, hogy a VLOOKUP rossz eredményt adhat vissza, ha az érték nem található. Ami még rosszabb, az eredmény teljesen normálisnak tűnhet, ezért nagyon nehéz észrevenni.

A megoldás az, hogy kétszer használja a VLOOKUP -ot, mindkét alkalommal hozzávetőleges egyezési módban. Az első példány egyszerűen ellenőrzi, hogy az érték valóban létezik -e. Ha igen, akkor egy másik VLOOKUP fut (ismét, hozzávetőleges egyezési módban) a kívánt adatok lekéréséhez. Ha nem, akkor bármilyen értéket visszaadhat, ha azt szeretné jelezni, hogy a találat nem található.

A végső képlet így néz ki:

 
= IF ( VLOOKUP (order_id,order_data,1,TRUE)=order_id,  VLOOKUP (order_id,order_data,5,TRUE), 'Missing')

Ezt a megközelítést Charles Williams -től tanultam a FastExcel -től, akinek fantasztikus, részletes cikke van itt: Miért jobb 2 VLOOKUPS, mint 1 VLOOKUP? .

Megjegyzés: A trükk használatához az adatokat rendezni kell. Ez egyszerűen egy módja annak, hogy megvédje magát a hiányzó keresési érték ellen, miközben fenntartja a gyors keresést.

23. Az INDEX és a MATCH együtt mindent megtehet, amit a VLOOKUP és még sok más

Ha online követi az Excel -t, akkor valószínűleg a VLOOKUP kontra INDEX/MATCH vita. Meglepően heves lehet a vita :)

A lényeg a következő: Az INDEX + MATCH mindent megtehet, amit a VLOOKUP (és a HLOOKUP), sokkal nagyobb rugalmassággal, egy kicsit összetettebb áron. Tehát azok, akik az INDEX + MATCH mellett állnak, vitatkozni fognak (nagyon józanul), hogy akár az INDEX és a MATCH tanulását is elkezdheti, mivel ez végül jobb eszközkészletet biztosít.

Az INDEX + MATCH elleni érv az, hogy egy helyett két funkciót igényel, így a felhasználók (különösen az új felhasználók) eleve összetettebbek a tanuláshoz és elsajátításhoz.

Két centem az, hogy ha gyakran használja az Excel programot, akkor meg szeretné tanulni az INDEX és a MATCH használatát. Ez egy nagyon erős kombináció.

De azt is gondolom, hogy meg kell tanulnia a VLOOKUP -ot, amivel mindenhol találkozni fog, gyakran a másoktól örökölt munkalapokon. Egyszerű helyzetekben a VLOOKUP minden gond nélkül elvégzi a munkát.

Ha többet szeretne megtudni az INDEX -ről és a MATCH -ról, lásd ezt a cikket .

Szerző: Dave Bruns


^