Contents [show]
Hierdie les verduidelik hoe om vinnig 'n situasie te hanteer waar 'n funksie VPR (VLOOKUP) wil nie in Excel 2013, 2010, 2007 en 2003 werk nie, en hoe om algemene foute te identifiseer en reg te stel en beperkings te oorkom VPR.
In verskeie vorige artikels het ons die verskillende fasette van die funksie ondersoek VPR in Excel. As jy dit noukeurig gelees het, behoort jy nou 'n kenner op hierdie gebied te wees. Dit is egter nie sonder rede dat baie Excel-kundiges glo nie VPR een van die meer komplekse kenmerke. Dit het 'n klomp beperkings en kenmerke wat die bron van baie probleme en foute word.
In hierdie artikel vind u eenvoudige verduidelikings van foute #BY (#N/A), #NAME? (#NAAM?) en Fout # WAARDE! (#VALUE!) wat verskyn wanneer met die funksie gewerk word VPR, asook tegnieke en metodes om dit te hanteer. Ons begin met die mees algemene gevalle en die mees voor die hand liggende redes waarom. VPR werk nie, daarom is dit beter om die voorbeelde te bestudeer in die volgorde waarin hulle in die artikel gegee word.
Regstelling van #N/A-fout in VLOOKUP-funksie in Excel
In formules met VPR foutboodskap #BY (#N/A) beteken nie beskikbaar (geen data) – verskyn wanneer Excel nie die waarde kan vind waarna jy soek nie. Dit kan om verskeie redes gebeur.
1. Die verlangde waarde is verkeerd gespel
Goeie idee om eers hierdie item na te gaan! Tikfoute kom dikwels voor wanneer jy met baie groot hoeveelhede data werk, wat uit duisende reëls bestaan, of wanneer die waarde waarna jy soek, in 'n formule geskryf word.
2. #N/A fout wanneer gesoek word vir 'n benaderde passing met VLOOKUP
As jy 'n formule gebruik met 'n benaderde passing soekvoorwaarde, dws argument reeks_opsoek (reeks_soek) is WAAR of nie gespesifiseer nie, jou formule kan dalk 'n fout rapporteer # N / A in twee gevalle:
- Die waarde om na te kyk is minder as die kleinste waarde in die skikking wat opgesoek word.
- Die soekkolom is nie in stygende volgorde gesorteer nie.
3. #N/A fout wanneer jy soek na 'n presiese passing met VLOOKUP
As jy op soek is na 'n presiese passing, dws argument reeks_opsoek (range_lookup) is ONWAAR en die presiese waarde is nie gevind nie, die formule sal ook 'n fout rapporteer # N / A. Kom meer te wete oor hoe om presiese en benaderde passings met 'n funksie te soek VPR.
4. Soekkolom is nie heel links nie
Soos u waarskynlik weet, een van die belangrikste beperkings VPR dit is dat dit nie na links kan wys nie, daarom moet die opsoekkolom in jou tabel heel links wees. In die praktyk vergeet ons dikwels hiervan, wat lei tot 'n nie-werkende formule en 'n fout. # N / A.
besluit: As dit nie moontlik is om die datastruktuur te verander sodat die soekkolom heel links is nie, kan jy 'n kombinasie van funksies gebruik INDEKS (INDEKS) en MEER BLOOTSTELLING (MATCH) as 'n meer buigsame alternatief vir VPR.
5. Getalle word as teks geformateer
Nog 'n bron van fout # N / A in formules met VPR is getalle in teksformaat in die hooftabel of opsoektabel.
Dit gebeur gewoonlik wanneer jy inligting vanaf eksterne databasisse invoer, of wanneer jy 'n apostrof voor 'n nommer tik om die voorste nul te hou.
Die mees voor die hand liggende tekens van 'n getal in teksformaat word in die figuur hieronder getoon:
Daarbenewens kan getalle in die formaat gestoor word algemene (Algemeen). In hierdie geval is daar net een merkbare kenmerk - die getalle is in lyn met die linkerrand van die sel, terwyl hulle by verstek met die regterrand in lyn is.
besluit: As dit 'n enkele waarde is, klik net op die foutikoon en kies Skakel oor na nommer (Skakel om na nommer) vanaf die konteks kieslys.
As dit die situasie is met baie getalle, kies hulle en klik met die rechtermuisknop op die geselekteerde area. Kies in die kontekskieslys wat verskyn Formateer selle (Formateer selle) > oortjie Nommer (Getal) > formaat Nommer (Numeries) en druk OK.
6. Daar is 'n spasie aan die begin of aan die einde
Dit is die minste ooglopende rede vir die fout. # N / A in funksie VPR, aangesien dit visueel moeilik is om hierdie ekstra spasies te sien, veral wanneer daar met groot tafels gewerk word, wanneer die meeste van die data van die skerm af is.
Oplossing 1: Ekstra spasies in die hooftabel (waar die VLOOKUP-funksie is)
As ekstra spasies in die hooftabel voorkom, kan jy verseker dat die formules reg werk deur die argument in te sluit lookup_value (soekwaarde) in 'n funksie TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Oplossing 2: Ekstra spasies in die opsoektabel (in die opsoekkolom)
As ekstra spasies in die soekkolom is – eenvoudige maniere # N / A in die formule met VPR kan nie vermy word nie. In plaas van VPR Jy kan 'n skikkingsformule met 'n kombinasie van funksies gebruik INDEKS (INDEKS), MEER BLOOTSTELLING (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))
Aangesien dit 'n skikkingsformule is, moenie vergeet om te druk nie Ctrl + Shift + Enter in plaas van die gewone Tikom die formule korrek in te voer.
Fout #VALUE! in formules met VLOOKUP
In die meeste gevalle rapporteer Microsoft Excel 'n fout Fout # WAARDE! (#VALUE!) wanneer die waarde wat in die formule gebruik word nie ooreenstem met die datatipe nie. Betreffende VPR, dan is daar gewoonlik twee redes vir die fout Fout # WAARDE!.
1. Die waarde waarna jy soek is langer as 255 karakters
Wees versigtig: funksie VPR kan nie soek vir waardes wat meer as 255 karakters bevat nie. As die waarde waarna jy soek hierdie limiet oorskry, sal jy 'n foutboodskap ontvang. Fout # WAARDE!.
besluit: Gebruik 'n klomp kenmerke INDEX+MATCH (INDEX + MATCH). Hieronder is 'n formule wat goed sal werk vir hierdie taak:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Die volledige pad na die soekwerkboek word nie gespesifiseer nie
As jy data van 'n ander werkboek af haal, moet jy die volle pad na daardie lêer spesifiseer. Meer spesifiek moet jy die werkboeknaam (insluitend die uitbreiding) tussen vierkantige hakies [ ], gevolg deur die bladnaam, gevolg deur 'n uitroepteken insluit. Al hierdie konstruksie moet in apostrofe ingesluit word, ingeval die boek of bladnaam spasies bevat.
Hier is die volledige struktuur van die funksie VPR om in 'n ander boek te soek:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Die werklike formule kan soos volg lyk:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Hierdie formule sal die selwaarde naslaan A2 in 'n kolom B op die blad Sheet1 in die werkboek Nuwe pryse en onttrek die ooreenstemmende waarde uit die kolom D.
As enige deel van die tabelpad weggelaat word, jou funksie VPR sal nie werk nie en sal 'n fout rapporteer Fout # WAARDE! (selfs al is die werkboek met die opsoektabel tans oop).
Vir meer inligting oor die funksie VPRverwys na 'n ander Excel-lêer, sien die les: Soek 'n ander werkboek met VLOOKUP.
3. Argument Kolom_getal is minder as 1
Dit is moeilik om 'n situasie voor te stel waar iemand 'n waarde wat minder is as 1om die kolom aan te dui waaruit die waarde onttrek moet word. Alhoewel dit moontlik is as die waarde van hierdie argument bereken word deur 'n ander Excel-funksie wat binne geneste is VPR.
Dus, as dit gebeur dat die argument col_index_num (kolom_nommer) minder as 1funksie VPR sal ook 'n fout rapporteer Fout # WAARDE!.
As die argument col_index_num (kolom_nommer) is groter as die aantal kolomme in die gegewe skikking, VPR sal 'n fout rapporteer #REF! (#SSYL!).
Fout #NAME? in VLOOKUP
Die eenvoudigste geval is 'n fout #NAME? (#NAAM?) – sal verskyn as jy per ongeluk 'n funksienaam met 'n fout skryf.
Die oplossing is voor die hand liggend - kontroleer jou spelling!
VLOOKUP werk nie (beperkings, waarskuwings en besluite)
Benewens die taamlik ingewikkelde sintaksis, VPR het meer beperkings as enige ander Excel-funksie. As gevolg van hierdie beperkings, skynbaar eenvoudige formules met VPR lei dikwels tot onverwagte resultate. Hieronder vind u oplossings vir verskeie algemene scenario's waar VPR is verkeerd.
1. VLOOKUP is nie hooflettersensitief nie
funksie VPR onderskei nie tussen hoofletters nie en aanvaar kleinletters en hoofletters as dieselfde. Daarom, as daar verskeie elemente in die tabel is wat slegs in geval verskil, sal die VLOOKUP-funksie die eerste element wat gevind is, terugstuur, ongeag die geval.
besluit: Gebruik 'n ander Excel-funksie wat 'n vertikale soektog kan uitvoer (LOOKUP, SUMPRODUCT, INDEX, en MATCH) in kombinasie met PRESIESA wat saak onderskei. Vir meer besonderhede kan jy uit die les leer – 4 maniere om VLOOKUP hooflettergevoelig te maak in Excel.
2. VLOOKUP gee die eerste waarde wat gevind is terug
Soos u reeds weet, VPR gee die waarde van die gegewe kolom terug wat ooreenstem met die eerste passing wat gevind is. U kan dit egter die 2de, 3de, 4de of enige ander herhaling van die waarde wat u wil laat onttrek. As jy alle duplikaatwaardes moet onttrek, sal jy 'n kombinasie van funksies nodig hê INDEKS (INDEKS), MINSTENS (KLEIN) en LINE (RY).
3. 'n Kolom is by die tabel gevoeg of verwyder
Ongelukkig, die formules VPR hou op werk elke keer as 'n nuwe kolom by die opsoektabel gevoeg of verwyder word. Dit gebeur omdat die sintaksis VPR vereis dat jy die volle omvang van die soektog en die spesifieke kolomnommer vir data-onttrekking spesifiseer. Natuurlik verander beide die gegewe reeks en die kolomnommer wanneer jy 'n kolom uitvee of 'n nuwe een invoeg.
besluit: En weer is funksies haastig om te help INDEKS (INDEKS) en MEER BLOOTSTELLING (WEDSTRYD). In die formule INDEX+MATCH Jy definieer soek- en herwinningkolomme afsonderlik, en as gevolg daarvan kan jy soveel kolomme uitvee of invoeg as wat jy wil sonder om bekommerd te wees dat jy alle verwante soekformules moet bywerk.
4. Selverwysings is verdraai wanneer 'n formule gekopieer word
Hierdie opskrif verduidelik die essensie van die probleem volledig, nie waar nie?
besluit: Gebruik altyd absolute selverwysings (met die simbool $) op rekords die reeks, byvoorbeeld $A$2:$C$100 or $A:$C. In die formulebalk kan jy vinnig die skakeltipe verander deur te klik F4.
VLOOKUP – werk met die funksies IFERROR en ISERROR
As jy nie gebruikers wil skrik met foutboodskappe nie # N / A, Fout # WAARDE! or #NAME?, kan jy 'n leë sel of jou eie boodskap wys. Jy kan dit doen deur te plaas VPR in 'n funksie IFERROR (IFERROR) in Excel 2013, 2010 en 2007 of gebruik 'n klomp funksies IF+FOUT (IF+ISERROR) in vroeëre weergawes.
VLOOKUP: werk met die IFERROR-funksie
Funksie sintaksis IFERROR (IFERROR) is eenvoudig en spreek vanself:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Dit wil sê, vir die eerste argument voeg jy die waarde in wat vir 'n fout gekontroleer moet word, en vir die tweede argument spesifiseer jy wat om terug te gee as 'n fout gevind word.
Byvoorbeeld, hierdie formule gee 'n leë sel terug as die waarde waarna jy soek nie gevind word nie:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
As jy jou eie boodskap wil vertoon in plaas van die funksie se standaard foutboodskap VPR, sit dit tussen aanhalingstekens, soos so:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: werk met die IERROR-funksie
Sedert die funksie IFERROR verskyn in Excel 2007, wanneer jy in vroeër weergawes werk, sal jy die kombinasie moet gebruik IF (IF) en EOSHIBKA (ISERROR) soos volg:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Byvoorbeeld, die formule IF+FOUT+VLOOKUP, soortgelyk aan die formule IFERROR+VLOOKUPhierbo getoon:
=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;ЛОЖЬ))
Dis al vir vandag. Ek hoop dat hierdie kort tutoriaal jou sal help om alle moontlike foute te hanteer. VPR en laat jou formules reg werk.