A VLOOKUP képlet használatával adatokat nyerhetünk egy Excel listából. A VLOOKUP hibaelhárítása, ha a dolgok rosszra fordulnak. Lépésről lépésre videók és Excel munkafüzetek VLOOKUP példákkal.
VLOOKUP Video
Nézze meg a VLOOKUP képlet létrehozásának lépéseit a Product Price keresési videóban. Az írásbeli utasítások a videó alatt találhatók. Szerezze be a termékár-keresési mintafájlt, amelyet a videóval együtt követhet (4. fájl letöltése).
Válasszon helyet a keresési táblázat számára
Jó ötlet minden keresést tárolni táblázat egy külön munkalapon a munkafüzetben. Ezután a keresési táblák sorainak hozzáadásakor és törlésénél nem véletlenül ad hozzá vagy töröl semmilyen más táblázatot.
Ebben a példában a keresőtábla Termékek nevű lapon található.
Keresőtábla létrehozása
A keresési képletek függőlegesen működhetnek, értékeket keresve egy oszlopban, vagy vízszintesen működve, értékeket keresve egy sorban . Ebben a példában az információkat függőlegesen tároljuk, az értékek egy oszlop alatt vannak, majd később egy VLookup képletet használunk függőleges kereséshez.
- Írja be a fejléceket az első sorba.
- Az első oszlopnak tartalmaznia kell azokat az egyedi kulcsértékeket, amelyekre a keresést alapozza. Ebben a példában megtalálja a ár egy adott termékkódhoz.
- Ha más adatok is vannak a munkalapon, hagyjon legalább egy üres sort a táblázat alján, és egy üres oszlopot a táblázat jobb oldalán, hogy elkülönítse a keresési táblázat a többi adatból.
Megjegyzés: A táblázat hivatkozásának megkönnyítése érdekében megnevezheti a tartományt. Itt vannak útmutatások: Tartomány elnevezése ▲ TOP
VLOOKUP Function Arguments
A VLOOKUP függvénynek négy argumentuma van:
- look__érték : Milyen értéket szeretne keresni? Ebben a példában a termék kódja az A7 cellában található, és meg szeretné találni annak pr oduct neve.
- table_array: Hol van a keresőtábla? Ha abszolút referenciát ($ A $ 2: $ C $ 5) használ, relatív referencia (A2: C5) helyett könnyebb másolni a képletbe más cellákba. Vagy nevezze el a keresőtáblát, és név szerint hivatkozjon rá.
- col_index_num: Melyik oszlopnak van a visszaadni kívánt értéke? Ebben a példában a terméknevek a keresési táblázat második oszlopában találhatók.
- : Pontos egyezést szeretne? A hozzávetőleges meccs rendben van?
Ha a TRUE-t használja utolsó argumentumként, vagy elhagyja az utolsó argumentumot, hozzávetőleges egyezést adhatunk vissza. Ebben a példában a FALSE az utolsó argumentum, tehát ha a termék kódja nem található, az eredmény # N / A lesz. (Megjegyzés: Az Excel meglehetősen elnéző, és 0-t fogad el FALSE helyett, és 1-et TRUE helyett.) ▲ TOP
Hozzon létre egy VLookup képletet
A keresőtábla létrehozása után más képleteket is létrehozhat a munkafüzetben, és információkat gyűjthet a terméklistából. Például egy számla elkészítéséhez megadhat egy termékkódot, és a képletek megkapják a termék nevét vagy árát a terméktáblázatból.
Ebben a példában a számla egy Számla nevű munkalapon jön létre. A VLOOKUP képletnek pontosan meg kell találnia a termékkódot, és vissza kell adnia a termék nevét.
A termék árát kiszámító VLOOKUP képlet létrehozásához kövesse az alábbi lépéseket:
- Válassza ki a Számla lapot
- Írja be az A23 termékkódot az A7 cellába
- A B7 cellába indítsa el a VLOOKUP képletet:
= VLOOKUP ( - Kattintson a az A7 cellába a hivatkozás megadásához.
- Írjon vesszőt az első argumentum befejezéséhez
- Válassza ki a Termékek lapot
- Jelölje ki az A2: C5 cellákat (a terméklista )
- Nyomja meg az F4 billentyűt a cellahivatkozások abszolútra váltásához. A képletnek most így kell kinéznie:
= VLOOKUP (A7, Termékek! $ A $ 2: $ C $ 5 - Írja be a vesszőt a második argumentum befejezéséhez.
- Írjon be egy 2-es számot, amely a keresési tábla oszlopa, amely tartalmazza a Termék nevét.
- Írja be a vesszőt a harmadik argumentum.
- Írja be a FALSE parancsot, hogy meghatározza a termékkód pontos egyezését, és adja hozzá a zárójelet.
- A képletnek most l így:
= VLOOKUP (A7, Termékek! $ A $ 2: $ C $ 5,2, HAMIS) - A képlet kiegészítéséhez nyomja meg az Enter billentyűt. Megjelenik a termék neve.
Megjegyzés: A termék árának visszatérítéséhez hozzon létre egy VLOOKUP képletet, amely a keresőtábla 3. oszlopára utal. Írja be például a következő képletet a C7 cellába:
= VLOOKUP (A7, Termékek! $ A $ 2: $ C $ 5,3, HAMIS) ▲ FEL
VLOOKUP képlet az értéktartományhoz
Bizonyos helyzetekben a hozzávetőleges egyezés előnyös, ezért több érték ugyanazt az eredményt adja vissza. Például a hallgatói munkák osztályozásakor minden 85 vagy annál magasabb osztályzatú munkának A osztályzatot kell kapnia.
A képlet létrehozásának lépéseinek megtekintéséhez kérjük, nézze meg az alább látható VLOOKUP videót. Az írásos utasítások a videó alatt találhatók.
Ebben a példában a keresési tábla a Grades nevű lapon jön létre. A keresőtábla létrehozásához az A oszlopba írja be az egyes évfolyamok minimális pontszámát. A B. oszlopba írja be a megfelelő osztályzatot. Rendezze a pontszámokat növekvő sorrendbe.
Az A2: B6 cellák GradeList nevet kapták.
A pontszámok a Jelentéskártya nevű lapra kerülnek, ahol egy VLOOKUP képlet kiszámítja az osztályzatot.
A az alábbi képernyőképen a képletet lemásolták a 6. sorba, és a képletet a C6 cellában láthatja.
A VLOOKUP kombinálása a MATCH-szal
Az oszlop számának VLOOKUP képletbe történő beírása helyett a MATCH függvény segítségével keresse meg a megfelelő oszlopot a keresési táblázatban. Ennek néhány előnye van:
- rugalmassá teszi a képletet, így könnyebb a képletet átmásolni egy munkalapra.
- Megakadályozhatja a problémákat, ha új oszlopokat adnak hozzá. a keresési táblázatban, vagy ha a keresési oszlopok átrendeződnek.
Ez a videó bemutatja a lépéseket, és írásos utasítások találhatók egy másik példához a videó alatt.
Böngészője nem tudja megjeleníteni ezt a keretet. Itt található egy hivatkozás az oldalra.
VLOOKUP MATCH-tal a rendelés részleteihez
Ebben a példában egy VLOOKUP képlet visszaadja a rendelés részleteit egy keresési táblából, a rendelés azonosító száma alapján. . Itt található a tblOrders nevű keresőtábla.
MEGJEGYZÉS: Ez a példa az 1. számú munkafüzetben található, az OrdersMATCH nevű lapon.
Itt található a VLOOKUP képleteket tartalmazó munkalap. Minden megrendeléshez szeretnénk a Régiót, a Rendelés dátumát és a Rendelés összegét, ezért 3 VLOOKUP képletre van szükség.
Ha az oszlopok számai be vannak írva a képletbe, akkor minden oszlopban más és más képlet szükséges:
- Régió: = VLOOKUP ($ B6, tblOrdersALL, 2,0)
- OrderDate: = VLOOKUP ($ B6, tblOrdersALL, 3,0)
- OrderAmt: = VLOOKUP ($ B6, tblOrdersALL, 4,0)
A MATCH függvény
A VLOOKUP képletbe az oszlop számának beírása helyett a MATCH függvényt is használhatjuk. A MATCH függvény megtalálja az elem helyét a listában, és visszaadja a pozíció számát.
Az alábbi képernyőképen a MATCH képlet 2-t ad vissza a “Régió” pozícióként a keresőtábla fejléc celláiban (A1: D1).
= MATCH ( C5, Orders_ALL! $ A $ 1: $ D $ 1, 0)
MEGJEGYZÉS: A technika megfelelő működéséhez a A VLOOKUP lap fejlécének pontosan meg kell egyeznie a keresőtábla fejlécével. A pontos egyezés biztosítása érdekében a VLOOKUP fejléc cellák összekapcsolódnak a keresőtábla fejléc celláival.
MATCH hozzáadása a VLOOKUP fájlhoz
A MATCH függvény hozzáadásához a VLOOKUP képlethez egyszerűen cserélje ki beírta az oszlopszámot
= VLOOKUP ($ B6, tblOrdersALL, 2,0)
a MATCH képlettel:
= VLOOKUP ($ B6, tblOrdersALL, MATCH (C5, Rendelések_ALL! $ A $ 1: $ D $ 1, 0), 0)
Másolja át a VLOOKUP képletet keresztbe
Most ahelyett, hogy mindegyik oszlopban más és más képletre lenne szükség, átmásolhatja a képletet, és ugyanazt a képletet használhatja az összes oszlopban. Minden oszlopban hivatkozni fog az oszlop fejléc cellájára, és megtalálja annak helyét a keresőtáblában.
MEGJEGYZÉS: Ha a képletet különböző formázású oszlopokban tölti ki, kövesse az alábbi lépéseket:
- Jelölje ki a másolni kívánt képletű cellát
- Mutasson a kijelölt cellán lévő kitöltő fogantyúra (a jobb alsó sarokban található kis négyzet)
- Nyomja meg a JOBB egérgombot, és húzza át az utolsó cellát, amelyre képletre van szükség.
- Engedje el az egérgombot, majd kattintson a Kitöltés formázás nélkül gombra.
IF és VLOOKUP kombinálása
Használhat IF képletet VLookup képlettel, ha pontos értékeket ad meg, és egy üres karakterláncot ha nem található.
Ha meg szeretné tekinteni az IF és a VLOOKUP képlet beállításának lépéseit, megnézheti ezt a rövid videót. Az írásbeli utasítások a videó alatt találhatók.
A hibák elrejtéséhez az IF és a VLOOKUP kombinálásával hajtsa végre az alábbi lépéseket:
- A Számla lap A8 cellájában írja be a terméket kód A28. Ha a B8 cellában található VLookup képlet FALSE a negyedik argumentum, az eredmény # N / A, mert a keresési táblázatban nincs pontos egyezés a termékkód számára.
- Csomagolja be a VLookup képletet egy IF képlet (ebben a példában a terméklistát elnevezték), az ISNA függvény segítségével # N / A hiba ellenőrzésére:
- Nyomja meg az Enter billentyűt, és a cella üresen jelenik meg. Mivel nem találtunk pontos egyezést, a VLookup képlet # N / A értéket adott, így az ISNA függvény eredménye IGAZ. Az IF képlet ezt üres karakterlánccá alakította.
= IF (ISNA (VLOOKUP (A8, ProductList, 2, FALSE) ), “”, VLOOKUP (A8, ProductList, 2, FALSE))
Kombinálja az IFERROR és a VLOOKUP
Köszönet Chip Pearsonnak, hogy ezt a képletet javasolta.
Az Excel 2007-ben új funkciót, az IFERROR-t vezettek be. . Használhat IFERROR képletet a VLookup programmal, hogy több táblában ellenőrizze az értéket, és ha megtalálta, adja vissza a kapcsolódó információkat. Ebben a példában három régiónak, Nyugatnak, Keletnek és Középnek van rendelési lapja. Minden lapon szerepel egy megnevezett tartomány – OrdersW, OrdersE és OrdersC.
A Rendelések nevű lapon megadhatja a megrendelés azonosítóját, majd a VLOOKUP segítségével az IFERROR segítségével ellenőrizheti az egyes megnevezett tartományokat , és megtekintheti a kiválasztott megrendeléssel kapcsolatos információkat.
- A Rendelési lap B6 cellájában írja be a 4 értéket OrderID néven. Ezt a sorrendet a központi régióba helyezték.
- A keleti régió táblázatának egyszerű ellenőrzéséhez a VLOOKUP képlet a C6 cellában a következő lesz:
= VLOOKUP (B6, OrdersE, 2, FALSE) - Nyomja meg az Enter billentyűt, és a VLOOKUP képlet # N / A értéket ad vissza, mert a 4. rendelésazonosító nincs a keleti régiók rendelési táblázatában.
- Mivel egy rendelés a három régió bármelyikébe került, szüksége van egy képletre, amely ellenőrzi az egyes táblázatokat.
Ha a megrendelés azonosítója nem található az első táblázatban, akkor a képletnek ellenőriznie kell a második táblázatot. Ha a rendelés azonosítója nincs a második táblában táblázatnak ellenőriznie kell a harmadik táblázatot. Ha a rendelésazonosító nem “t” a harmadik táblázatban, akkor a “Nem található” üzenetnek kell megjelennie a cellában. - Az IFERROR képlet lehetővé teszi egy érték ellenőrzését, majd adja meg, mit kell tennie, ha hibát talál. Ha a meglévő képlettel IFERROR-t használ, akkor a # N / A hiba helyett a “Nem található” elemet jelenítheti meg:
= IFERROR (VLOOKUP (B6, OrdersE, 2, FALSE), “Nem található”) - Mindhárom tábla ellenőrzéséhez az IFERROR és a VLOOKUP képleteket követheti:
Ez ellenőrzi az OrdersE táblázatot, és ha hibát talál, akkor az OrdersW táblát, majd az OrdersC-t. Ha a OrderID nem található a három táblázat egyikében sem, akkor a Nem található üzenet jelenik meg a cellában.
Több táblázatot is ellenőrizhet az Excel régebbi verzióiban, ahol az IFERROR nem érhető el, a hosszabb képlet:
VLOOKUP kombinált értékekhez
Egyes táblázatokban előfordulhat, hogy nincsenek egyedi értékek a keresőtábla bármely oszlopa. Például az alábbi táblázatban a Jacket kétszer szerepel az A. oszlopban. Mindazonáltal minden kabát és méretkombináció esetében csak egy rekord van – a Jacket Medium a 4. sorban és a Jacket Large az 5. sorban.
Ha meg kell találnia egy nagy kabát árát, a csak az A oszlopon alapuló VLOOKUP megadná az első felsorolt kabát árát (közepes ). Alábecsülné a dzsekit – 65,00 helyett 60,00-ért adja el.
Egyedi keresési értékek létrehozásához beilleszthet egy új oszlopot a táblázat bal oldalába, és képlet segítségével kombinálhatja a terméket és a méretet. Az A2 cellában a képlet ötvözi a B2 értéket és a cső karaktert, valamint a C2 értéket.
= B2 & “|” & C2
Másolja le ezt a képletet az utolsó adatsorra, így minden sor egyedi értékkel rendelkezik az A oszlopban.
Megjegyzés : A pip karakter helyett használhat egy másik karaktert, amelyet “nem tartalmaz az adatai.
Ezután a egy VLOOKUP képlet, vegye össze a szorzatot és a méretet a keresési_értékként. A H1 cellában a képlet egyesíti az F1 értéket és a pipa karaktert, valamint a G1 értéket.
= VLOOKUP (F1 &” | “& G1, $ A $ 2: $ D $ 5,4, FALSE)
Megjegyzés: Az ár most szerepel 4. oszlop, a 3. oszlop helyett. ▲ TOP
A VLOOKUP képlet hibaelhárítása
A VLOOKUP képlet # N / A értéket adhat vissza, annak ellenére, hogy a keresett érték a keresőtáblában található. Ennek leggyakoribb okai:
Szöveg és szám
Szóköz karakterek
Egyéb karakterek
Szöveg és szám
A hiba gyakori oka, hogy az egyik érték szám, a másik pedig szöveg. Például a keresőtábla tartalmazhatja “123 (szöveg), és a keresendő érték 123 (szám).
- Ha lehetséges, konvertálja a szöveget számokká az egyik az itt bemutatott módszerek: Szöveg konvertálása számokká
- Ha nem tudja konvertálni az adatokat, akkor a VLOOKUP képleten belül konvertálhatja a keresési értéket az alábbi megoldások egyikével:
- Keresési értékek a szöveg, a táblázat számai
- A keresési értékek számok, a táblázat szöveget tartalmaz
- Annak kiderítéséhez, hogy mely értékek szövegek, és melyek számok, lásd: a részletes szám vagy szöveges hibaelhárítási lépések
1. A keresési értékek a Szöveg, és a táblázat számokat tartalmaz
Ha a keresőtábla számokat tartalmaz, és a keresendő érték szöveg, használjon a következőhöz hasonló képletet:
= VLOOKUP (–A7, Termékek!$ A $ 2: $ C $ 5,3, HAMIS)
A kettős unary (-) a szöveget számokká alakítja, és akkor is megfelelően fog működni, ha a keresési értékek számok.
2. A keresési értékek a Számok, és a táblázat tartalmaz szöveget
Ha a keresőtábla szöveget tartalmaz, és a keresendő érték numerikus, használjon a következőhöz hasonló képletet:
= VLOOKUP (A7 & “”), Termékek! $ A $ 2: $ C $ 5,3, HAMIS)
VAGY
= VLOOKUP (TEXT (A7, “00000”) , Termékek! $ A $ 2: $ C $ 5,3, HAMIS)
A TEXT függvény egy számot szöveggé alakít, és akkor is megfelelően fog működni, ha a keresési értékek szövegek. Az első példában a & operátor formázatlan számból készít szöveges karakterláncot. A második példában egy vezető nullákkal formázott szám (pl. 00123) megegyezik egy “szám” szöveggel a vezető nullákkal.
A VLOOKUP probléma kijavításának lépéseinek megtekintéséhez, amikor a keresési táblázat szöveges értékekkel rendelkezik, nézze meg ezt a rövid oktatóvideót.
Az egyik érték szóköze, a másiké nem.
Egy másik lehetséges oka annak, hogy nem található megfelelő érték, a szóközök közötti különbség. Az egyik érték tartalmazhat vezető szóközöket (vagy záró vagy beágyazott szóközöket), a másik pedig nem “t”. Az értékek teszteléséhez használhatja a LEN függvényt, hogy ellenőrizze az egyes értékek hosszát.
Például: = LEN (A7) az A7 cellában lévő karakterek számát adja vissza. Ennek meg kell egyeznie a keresőtábla megfelelő cellájában lévő karakterek számával.
Ha lehetséges, távolítsa el a feleslegeset szóközöket, és a VLOOKUP képletnek megfelelően kell működnie. Ha nem tudja eltávolítani a szóközöket, akkor a VLOOKUP TRIM funkciójával távolítsa el a vezető, a záró vagy az ismétlődő szóközöket. Például:
= VLOOKUP (TRIM (A7), ProductList, 2, FALSE)
Egyéb karakterek
Ha a TRIM funkció önmagában nem oldja meg a problémát , kipróbálhatja a következő javaslatok egyikét:
- SUBSTITUTE függvény
- CLEAN funkció
- Makró a webes karakterek eltávolításához
SUBSTITUTE függvény
A SUBSTITUTE funkcióval távolítsa el a nem kívánt karaktereket. A Contextures blogon található egy példa: Excel adatok tisztítása TRIM-mel és SUBSTITUTE
CLEAN funkció
A VLOOKUP problémák megoldásának másik módja a TISZTÍTÁS funkció, amely eltávolíthat néhány nem kívánt karaktert a szövegből. A TISZTÍTÁS funkcióról ebben a Contextures blogbejegyzésben olvashat bővebben: 30 Excel funkció 30 nap alatt: 29 – TISZTÍTÁS
Webes karakterek az egyik értékben, a másikban nem,
Ha adatokat másolt egy weboldalról, akkor HTML-t nem törő helyet tartalmazhat (& nbsp) karaktereket. David McRitchie makrót írt az eltávolításhoz, az o-val együtt szóközök
Problémák a VLOOKUP képlet rendezésénél
A VLOOKUP képlet először a helyes eredményeket adja vissza, de az elemek listájának rendezése esetén helytelen eredményeket mutat. Ez akkor fordulhat elő, ha a Lookup értékre való hivatkozás tartalmaz egy lap nevét. Például:
= VLOOKUP (“Megrendelőlap”! B5, Termékek! $ B $ 2: $ C $ 6,2, HAMIS)
MEGJEGYZÉS: Ez a probléma más funkciókkal is előfordulhat is, például egy INDEX / MATCH keresési képlet.
Nézze meg ezt a videót, hogy megismerje a probléma megoldásának lépéseit, és töltse le a VLOOKUP Sorting problem minta fájlt, amelyet követni kell. Az írásbeli utasítások a videó alatt találhatók.
Lapnevek a referenciában
Ez a típusú hivatkozás akkor jön létre, ha a képlet felépítése közben másik lapra kattint. Amint ezt megtette, az Excel hozzáadja a lap nevét a képlet későbbi hivatkozásaihoz.
A képernyőképen A fenti ruha a B9 cellában van, a C9 cella pedig a helyes 30 dolláros árat mutatja.
Az AZ termékek rendezése után azonban a ruha feljebb lép a B5 cellába, de a C5 cellában található képlet továbbra is hivatkozik a B9 cellába. A hivatkozásokban szereplő lapnevek miatt az Excel megtartja az eredeti hivatkozásokat, ahelyett, hogy az aktuális sorra utalna. A Cell C5 egy pulóver árát mutatja, ruha helyett. ▲ TOP
A probléma megoldása
A probléma megoldásához távolítsa el a felesleges lapneveket a VLOOKUP cellahivatkozások. Itt van a C5 cella átdolgozott képlete:
= VLOOKUP (B5, Termékek! $ B $ 2: $ C $ 6,2, HAMIS)
A felesleges lapnevek eltávolítása után a listát biztonságosan lehet rendezni, és minden elemnél megjelennek a helyes eredmények.
Töltse le a mintafájlokat
1. Szerezze be a VLOOKUP munkafüzet mintáját (Excel 2007 és újabb). A tömörített fájl xlsx formátumú, és nem tartalmaz makrókat.
2. Az Excel 2003 alkalmazáshoz szerezze be a VLOOKUP mintafájl ezen verzióját. A tömörített fájl xls formátumú, és nem tartalmaz makrókat.
3. A VLOOKUP segítségével történő rendezéskor felmerülő probléma megtekintéséhez szerezze be ezt a VLOOKUP Sorting problem minta fájlt. A tömörített fájl xlsx formátumú, és nem tartalmaz makrókat.
4. Töltse le a videojátékban használt termékár-keresési munkafüzetet.A tömörített fájl xlsx formátumú, és nem tartalmaz makrókat. ▲ TOP
További oktatóanyagok
VLOOKUP szám / szöveges hibaelhárítás
VLOOKUP egy másik munkafüzetből
HLOOKUP
KIVÁLASZT Funkció
Keresés – 2 feltétel / p>