tartalom
Ez a lecke elmagyarázza, hogyan lehet gyorsan kezelni egy olyan helyzetet, amikor egy funkció VPR (VLOOKUP) nem akar működni az Excel 2013-ban, 2010-ben, 2007-ben és 2003-ban, és hogyan lehet azonosítani és kijavítani a gyakori hibákat, valamint leküzdeni a korlátokat VPR.
Számos korábbi cikkünkben megvizsgáltuk a funkció különböző aspektusait VPR Excelben. Ha figyelmesen elolvasta ezeket, akkor most szakértőnek kell lennie ezen a területen. Nem ok nélkül gondolja azonban sok Excel-szakértő VPR az egyik összetettebb funkció. Számos korlátozással és funkcióval rendelkezik, amelyek számos probléma és hiba forrásává válnak.
Ebben a cikkben egyszerű magyarázatokat talál a hibákra #NÁL NÉL (#N/A), # NÉV? (#NÉV?) és #ÉRTÉK! (#ÉRTÉK!), amelyek a függvénnyel való munka során jelennek meg VPR, valamint a kezelésük technikái és módszerei. Kezdjük a leggyakoribb esetekkel és a legnyilvánvalóbb okokkal. VPR nem működik, ezért jobb a példákat abban a sorrendben tanulmányozni, ahogyan a cikkben szerepelnek.
#N/A hiba javítása az Excel VLOOKUP funkciójában
Képletekben -val VPR hibaüzenet #NÁL NÉL (#N/A) azt jelenti nem elérhető (nincs adat) – akkor jelenik meg, ha az Excel nem találja a keresett értéket. Ennek több oka is lehet.
1. A kívánt érték hibásan van írva
Jó ötlet először ellenőrizni ezt az elemet! Az elírási hibák gyakran előfordulnak, amikor nagyon nagy mennyiségű adattal dolgozik, amely több ezer sorból áll, vagy amikor a keresett érték egy képletbe van írva.
2. #N/A hiba a VLOOKUP hozzávetőleges egyezésének keresésekor
Ha egy hozzávetőleges egyezési keresési feltétellel, azaz argumentummal rendelkező képletet használ range_lookup (tartomány_keresése) értéke IGAZ vagy nincs megadva, a képlet hibát jelezhet # N / A két esetben:
- A kikeresendő érték kisebb, mint a keresett tömb legkisebb értéke.
- A keresőoszlop nincs növekvő sorrendbe rendezve.
3. #N/A hiba a VLOOKUP-val való pontos egyezés keresésekor
Ha pontos egyezést, azaz érvet keresel range_lookup (tartomány_keresése) HAMIS, és a pontos érték nem található, a képlet szintén hibát jelez # N / A. Tudjon meg többet arról, hogyan kereshet pontos és közelítő egyezéseket a függvényekkel VPR.
4. A keresőoszlop nem a bal szélső
Mint valószínűleg tudja, az egyik legjelentősebb korlátozás VPR ez az, hogy nem nézhet balra, ezért a táblázatban a keresőoszlopnak bal szélsőnek kell lennie. A gyakorlatban erről gyakran megfeledkezünk, ami nem működő képlethez és hibához vezet. # N / A.
Döntés: Ha nem lehetséges úgy módosítani az adatszerkezetet, hogy a keresőoszlop bal szélső legyen, használhatja a függvények kombinációját. INDEX (INDEX) és KIÉPÍTETTebb (MATCH) rugalmasabb alternatívájaként VPR.
5. A számok szövegként vannak formázva
Egy másik hibaforrás # N / A képletekben -val VPR szöveges formátumú számok a főtáblázatban vagy a keresőtáblázatban.
Ez általában akkor fordul elő, amikor információkat importál külső adatbázisokból, vagy ha egy szám elé aposztrófot ír be, hogy a kezdő nulla maradjon.
A szöveges formátumú számok legnyilvánvalóbb jelei az alábbi ábrán láthatók:
Ezenkívül a számok formátumban is tárolhatók általános (Tábornok). Ebben az esetben csak egy észrevehető jellemző van – a számok a cella bal széléhez igazodnak, míg alapértelmezés szerint a jobb széléhez igazodnak.
Döntés: Ha egyetlen értékről van szó, egyszerűen kattintson a hiba ikonra, és válassza ki Átalakítás számra (Konvertálás számmá) a helyi menüből.
Ha ez a helyzet sok számmal, jelölje ki őket, és kattintson a jobb gombbal a kiválasztott területre. A megjelenő helyi menüben válassza a lehetőséget Cellák formázása (Cellák formázása) > fülre Szám (Szám) > formátum Szám (Numerikus), és nyomja meg OK.
6. Az elején vagy a végén szóköz van
Ez a hiba legkevésbé nyilvánvaló oka. # N / A funkcióban VPR, mivel vizuálisan nehéz észrevenni ezeket az extra tereket, különösen nagy táblák használatakor, amikor az adatok nagy része a képernyőn kívül van.
1. megoldás: További szóközök a főtáblában (ahol a VLOOKUP függvény van)
Ha extra szóközök jelennek meg a főtáblázatban, az argumentum bezárásával biztosíthatja a képletek helyes működését keresési_érték (keresési_érték) függvényt TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
2. megoldás: További szóközök a keresési táblázatban (a keresési oszlopban)
Ha extra szóközök vannak a keresőoszlopban – egyszerű módszerek # N / A a képletben -val VPR nem lehet elkerülni. Ahelyett VPR Használhat tömbképletet függvények kombinációjával INDEX (INDEX), KIÉPÍTETTebb (MATCH) и TRIM (TRIM):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Mivel ez egy tömbképlet, ne felejtse el megnyomni Ctrl + Shift + Enter a megszokott helyett beléphogy helyesen adja meg a képletet.
Hiba: #VALUE! képletekben a VLOOKUP segítségével
A legtöbb esetben a Microsoft Excel hibát jelez #ÉRTÉK! (#ÉRTÉK!), ha a képletben használt érték nem egyezik az adattípussal. Vonatkozó VPR, akkor a hibának általában két oka lehet #ÉRTÉK!.
1. A keresett érték 255 karakternél hosszabb
Legyen óvatos: funkció VPR nem tud olyan értékeket keresni, amelyek 255 karakternél hosszabbak. Ha a keresett érték meghaladja ezt a határértéket, hibaüzenetet fog kapni. #ÉRTÉK!.
Döntés: Használjon egy csomó funkciót INDEX+MATCH (INDEX + MATCH). Az alábbiakban egy képlet látható, amely tökéletesen megfelel ehhez a feladathoz:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. A keresési munkafüzet teljes elérési útja nincs megadva
Ha egy másik munkafüzetből kér le adatokat, meg kell adnia a fájl teljes elérési útját. Pontosabban, szögletes zárójelben [ ] kell megadnia a munkafüzet nevét (a kiterjesztéssel együtt), majd a munkalap nevét, majd egy felkiáltójelet. Mindezeket a konstrukciókat aposztrófok közé kell tenni, ha a könyv vagy lap neve szóközt tartalmaz.
Itt látható a függvény teljes felépítése VPR keresni egy másik könyvben:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
A valódi képlet így nézhet ki:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Ez a képlet megkeresi a cellaértéket A2 oszlopban B a lapon Sheet1 a munkafüzetben Új árak és kivonjuk a megfelelő értéket az oszlopból D.
Ha a tábla elérési útjának bármely része kimarad, az Ön függvénye VPR nem fog működni, és hibát jelez #ÉRTÉK! (még akkor is, ha a keresőtáblát tartalmazó munkafüzet jelenleg nyitva van).
További információ a funkcióról VPRegy másik Excel-fájlra hivatkozva lásd a leckét: Keresés egy másik munkafüzetben a VLOOKUP segítségével.
3. Az argumentum Oszlop_száma kisebb, mint 1
Nehéz elképzelni olyan helyzetet, amikor valaki ennél kisebb értéket ír be 1hogy jelezze azt az oszlopot, amelyből az értéket kinyerni. Bár ez lehetséges, ha ennek az argumentumnak az értékét egy másik, beágyazott Excel-függvény számítja ki VPR.
Tehát, ha megtörténik, hogy az érv oszlopszám (oszlop_száma) kisebb, mint 1funkció VPR hibát is jelez #ÉRTÉK!.
Ha az érv oszlopszám (oszlopszám) nagyobb, mint az adott tömbben lévő oszlopok száma, VPR hibát fog jelenteni #REF! (#SSYL!).
#NAME hiba? a VLOOKUP-ban
A legegyszerűbb eset a hiba # NÉV? (#NAME?) – akkor jelenik meg, ha véletlenül hibás függvénynevet ír be.
A megoldás kézenfekvő – ellenőrizze a helyesírást!
A VLOOKUP nem működik (korlátozások, figyelmeztetések és döntések)
A meglehetősen bonyolult szintaxis mellett VPR több korlátozással rendelkezik, mint bármely más Excel-funkció. Ezen korlátok miatt az egyszerűnek tűnő képletek VPR gyakran nem várt eredményekhez vezet. Az alábbiakban számos olyan gyakori forgatókönyvre talál megoldást, ahol VPR rossz.
1. A VLOOKUP nem különbözteti meg a kis- és nagybetűket
Funkció VPR nem tesz különbséget a kis- és nagybetűk között, és azonosnak fogadja el a kis- és nagybetűket. Ezért, ha a táblázatban több olyan elem is van, amely csak kis- és nagybetűben különbözik, a VLOOKUP függvény az első talált elemet adja vissza, kis- és nagybetűtől függetlenül.
Döntés: Használjon egy másik Excel-függvényt, amely függőleges keresést végezhet (LOOKUP, SUMPRODUCT, INDEX és MATCH) együtt PONTOSEz megkülönbözteti az esetet. További részletekért tanulhat a leckéből – 4 módszer a VLOOKUP kis- és nagybetűk megkülönböztetésére az Excelben.
2. A VLOOKUP az első talált értéket adja vissza
Mint már tudod, VPR az első talált egyezésnek megfelelő értéket adja vissza az adott oszlopból. Azonban megkérheti, hogy kivonja a kívánt érték 2., 3., 4. vagy bármely más ismétlését. Ha az összes ismétlődő értéket ki kell bontania, akkor a függvények kombinációjára lesz szüksége INDEX (INDEX), LEGKEVÉSBÉ (KIS) és LINE (SOR).
3. Egy oszlop hozzáadva vagy eltávolítva a táblázatból
Sajnos a képletek VPR minden alkalommal leállítja a munkát, amikor új oszlopot adnak hozzá vagy eltávolítanak a keresőtáblázatból. Ez azért történik, mert a szintaxis VPR meg kell adnia a keresés teljes tartományát és az adatkinyeréshez az adott oszlopszámot. Természetesen az adott tartomány és az oszlopszám is megváltozik, amikor töröl egy oszlopot vagy újat szúr be.
Döntés: És ismét a funkciók sietnek a segítségére INDEX (INDEX) és KIÉPÍTETTebb (MÉRKŐZÉS). A képletben INDEX+MATCH Külön meghatározhatja a keresési és lekérési oszlopokat, és ennek eredményeként annyi oszlopot törölhet vagy szúrhat be, amennyit csak szeretne, anélkül, hogy aggódnia kellene az összes kapcsolódó keresési képlet frissítése miatt.
4. A cellahivatkozások torzak a képlet másolásakor
Ez a címsor kimerítően elmagyarázza a probléma lényegét, igaz?
Döntés: Mindig használjon abszolút cellahivatkozásokat (a szimbólummal $) rögzíti például a tartományt $ A $ 2: $ C $ 100 or $ A: $ C. A képletsorban gyorsan válthat a hivatkozás típusa között, ha rákattint F4.
VLOOKUP – az IFERROR és ISERROR függvényekkel való munka
Ha nem akarja hibaüzenetekkel ijeszteni a felhasználókat # N / A, #ÉRTÉK! or # NÉV?, megjeleníthet egy üres cellát vagy saját üzenetét. Ezt az elhelyezéssel teheti meg VPR függvénybe IFROROR (IFERROR) az Excel 2013-ban, 2010-ben és 2007-ben, vagy használjon egy csomó függvényt HA+HIBA (HA+HIBA) a korábbi verziókban.
VLOOKUP: az IFERROR funkcióval való munka
Függvény szintaxis IFROROR (IFERROR) egyszerű, és önmagáért beszél:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Ez azt jelenti, hogy az első argumentumhoz be kell illeszteni azt az értéket, amelyet ellenőrizni kell a hiba szempontjából, a második argumentumhoz pedig megadja, hogy mit adjon vissza, ha hibát talál.
Például ez a képlet üres cellát ad vissza, ha a keresett érték nem található:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Ha a saját üzenetét szeretné megjeleníteni a funkció normál hibaüzenete helyett VPR, tegye idézőjelbe, így:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: az ISERROR függvénnyel való munka
Mivel a funkció IFROROR Az Excel 2007-ben jelent meg, amikor a korábbi verziókban dolgozik, ezt a kombinációt kell használnia IF (HA) és EOSHIBKA (ISERROR) így:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Például a képlet IF+ISERROR+VLOOKUP, hasonlóan a képlethez IFERROR+VLOOKUPfent látható:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
Ez minden mára. Remélem, ez a rövid útmutató segít az összes lehetséges hiba kezelésében. VPR és helyesen működjenek képletei.