Inhalter
Dës Lektioun erkläert wéi séier mat enger Situatioun ëmzegoen, wou eng Funktioun VPR (VLOOKUP) wëll net an Excel 2013, 2010, 2007 an 2003 schaffen, a wéi een gemeinsame Feeler z'identifizéieren an ze fixéieren an Aschränkungen ze iwwerwannen VPR.
A verschiddene fréieren Artikelen hu mir déi verschidde Facette vun der Funktioun exploréiert VPR an Excel. Wann Dir se virsiichteg gelies hutt, sollt Dir elo en Expert an dësem Beräich sinn. Wéi och ëmmer, et ass net ouni Grond datt vill Excel Experten gleewen VPR ee vun de méi komplex Fonctiounen. Et huet eng Rëtsch Aschränkungen a Featuren déi d'Quell vu ville Probleemer a Feeler ginn.
An dësem Artikel fannt Dir einfach Erklärunge vu Feeler #AT (#N/A), #NAME? (#NUMM?) an #WAARDE! (#VALUE!) déi schéngen wann Dir mat der Funktioun schafft VPR, souwéi Techniken a Methoden fir mat hinnen ëmzegoen. Mir fänken un mat den heefegste Fäll an déi offensichtlechst Grënn firwat. VPR funktionnéiert net, also ass et besser d'Beispiller an der Reiefolleg ze studéieren an där se am Artikel uginn sinn.
Fixéieren #N/A Feeler an der VLOOKUP Funktioun an Excel
An Formulen mat VPR Feeler Message #AT (#N/A) heescht nët accessible (keng Donnéeën) - erschéngt wann Excel de Wäert net fënnt deen Dir sicht. Dëst kann aus verschiddene Grënn geschéien.
1. De gewënschte Wäert gëtt falsch geschriwwe
Gutt Iddi dëst Element als éischt ze kontrolléieren! Tippfeeler geschéien dacks wann Dir mat ganz grousse Quantitéiten un Daten schafft, déi aus Dausende vu Linnen besteet, oder wann de Wäert deen Dir sicht an eng Formel geschriwwe gëtt.
2. #N/A Feeler beim Sich no engem geschätzte Match mat VLOOKUP
Wann Dir eng Formel mat engem geschätzte Match Sich Conditioun benotzt, dh Argument range_lookup (range_lookup) ass TRUE oder net spezifizéiert, Är Formel kann e Feeler mellen # N / A an zwee Fäll:
- De Wäert fir nozekucken ass manner wéi de klengste Wäert an der Array déi gekuckt gëtt.
- D'Sich Kolonn ass net an opsteigend Uerdnung zortéiert.
3. #N/A Feeler wann Dir no engem genaue Match mat VLOOKUP sicht
Wann Dir sicht eng genee Match, dh Argument range_lookup (range_lookup) ass FALSE an de genaue Wäert gouf net fonnt, d'Formel mellt och e Feeler # N / A. Léiert méi iwwer wéi Dir no genauen an ongeféiere Matcher mat enger Funktioun sicht VPR.
4. Sich Kolonn ass net lénks
Wéi Dir wahrscheinlech wësst, eng vun de bedeitendsten Aschränkungen VPR et ass datt et net no lénks kënnt, dofir muss d'Sichkolonne an Ärer Tabelle lénks sinn. An der Praxis vergiesse mir dacks iwwer dëst, wat zu enger net funktionnéierender Formel an e Feeler féiert. # N / A.
Entscheedung: Wann et net méiglech ass d'Datenstruktur z'änneren, sou datt d'Sichkolonne am lénksen ass, kënnt Dir eng Kombinatioun vu Funktiounen benotzen INDEX (INDEX) an MÉI ausgesat (MATCH) als méi flexibel Alternativ fir VPR.
5. Zuelen sinn als Text formatéiert
Eng aner Quell vu Feeler # N / A an Formulen mat VPR sinn Zuelen am Text Format an der Haaptrei Dësch oder Bléck Dësch.
Dëst geschitt normalerweis wann Dir Informatioun aus externen Datenbanken importéiert, oder wann Dir en Apostroph virun enger Nummer tippt fir déi féierend Null ze halen.
Déi offensichtlechst Zeeche vun enger Zuel am Textformat ginn an der Figur hei ënnen gewisen:
Zousätzlech kënnen d'Zuelen am Format gespäichert ginn allgemeng (General). An dësem Fall gëtt et nëmmen eng bemierkenswäert Feature - d'Zuelen sinn op de lénksen Rand vun der Zell ausgeriicht, während se par défaut op de richtege Rand ausgeriicht sinn.
Entscheedung: Wann et en eenzege Wäert ass, klickt einfach op de Feeler Ikon a wielt Konvertéieren an Nummer (Konvertéieren op Nummer) aus dem Kontextmenü.
Wann dëst d'Situatioun mat villen Zuelen ass, wielt se a klickt op de gewielte Beräich. Am Kontextmenü deen erschéngt, wielt Format Zellen (Format Zellen) > Tab Zuel (Nummer) > Format Zuel (Numeresch) an dréckt OK.
6. Et gëtt eng Plaz am Ufank oder um Enn
Dëst ass de mannst offensichtleche Grond fir de Feeler. # N / A an Funktioun VPR, well et visuell schwéier ass dës extra Plazen ze gesinn, besonnesch wann Dir mat groussen Dëscher schafft, wann déi meescht vun den Donnéeën off-screen sinn.
Léisung 1: Extra Plazen an der Haapttabell (wou d'VLOOKUP Funktioun ass)
Wann extra Plazen an der Haaptrei Dësch schéngen, Dir kënnt suergen, datt d'Formelen richteg Aarbecht vun der Argument ëmklammen lookup_value (lookup_value) an eng Funktioun TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Léisung 2: Extra Plazen an der Lookup Tabell (an der Lookup Kolonn)
Wann extra Plazen an der Sich Kolonn sinn - einfach Weeër # N / A an der Formel mat VPR kann net vermeide ginn. Amplaz vun VPR Dir kënnt eng Arrayformel mat enger Kombinatioun vu Funktiounen benotzen INDEX (INDEX), MÉI ausgesat (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))
Well dëst eng Arrayformel ass, vergiesst net ze drécken Ctrl + Umeldung + Enter amplaz vum üblechen gittfir d'Formel richteg anzeginn.
Feeler #VALUE! an Formulen mat VLOOKUP
An deene meeschte Fäll mellt Microsoft Excel e Feeler #WAARDE! (#VALUE!) Wann de Wäert, deen an der Formel benotzt gëtt, net mam Datetyp entsprécht. Betreffend VPR, da ginn et normalerweis zwee Grënn fir de Feeler #WAARDE!.
1. De Wäert deen Dir sicht ass méi laang wéi 255 Zeechen
Opgepasst: Funktioun VPR kann net no Wäerter sichen déi méi wéi 255 Zeechen enthalen. Wann de Wäert deen Dir sicht dës Limit iwwerschreift, kritt Dir eng Fehlermeldung. #WAARDE!.
Entscheedung: Benotzt eng Rëtsch Features INDEX+MATCH (INDEX + MATCH). Drënner ass eng Formel déi gutt fir dës Aufgab mécht:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. De komplette Wee fir d'Sich Aarbechtsbuch gëtt net uginn
Wann Dir Daten aus engem aneren Aarbechtsbuch recuperéiert, musst Dir de komplette Wee fir dës Datei uginn. Méi spezifesch, musst Dir den Numm vum Aarbechtsbuch (inklusiv d'Extensioun) a véiereckege Klammeren enthalen [ ], gefollegt vum Blatnumm, gefollegt vun engem Ausrufezeeche. All dës Konstruktioun muss an Apostrophen zougemaach ginn, am Fall wou d'Buch oder d'Blat Numm Plazen enthält.
Hei ass déi komplett Struktur vun der Funktioun VPR fir an engem anere Buch ze sichen:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Déi richteg Formel kéint esou ausgesinn:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Dës Formel wäert den Zellwäert kucken A2 an enger Kolonn B op der Blat Blieder1 am Aarbechtsbuch Nei Präisser an Extrait de entspriechende Wäert aus der Kolonn D.
Wann en Deel vum Dësch Wee ewechgelooss ass, Är Funktioun VPR wäert net schaffen a wäert e Feeler Rapport #WAARDE! (och wann d'Aarbechtsbuch mat der Lookup Tabelle momentan op ass).
Fir méi Informatiounen iwwert d'Funktioun VPRReferenz op eng aner Excel-Datei, kuckt d'Lektioun: Eng aner Aarbechtsbuch sichen mat VLOOKUP.
3. Argument Column_num ass manner wéi 1
Et ass schwéier eng Situatioun virzestellen wou een e Wäert manner agitt wéi 1fir d'Kolonn unzeginn, aus där de Wäert extrahéiert gëtt. Och wann et méiglech ass wann de Wäert vun dësem Argument vun enger anerer Excel Funktioun berechent gëtt, déi bannent niddergelooss gëtt VPR.
Also, wann et geschitt, datt d'Argument col_index_num (column_number) manner wéi 1Funktioun VPR wäert och e Feeler mellen #WAARDE!.
Wann d'Argument col_index_num (column_number) ass méi grouss wéi d'Zuel vun de Kolonnen an der bestëmmter Array, VPR wäert e Feeler mellen #REF! (#SSYL!).
Feeler #NAME? an VLOOKUP
Deen einfachste Fall ass e Feeler #NAME? (#NAME?) - Erscheint wann Dir zoufälleg e Funktiounsnumm mat engem Feeler schreift.
D'Léisung ass offensichtlech - kontrolléiert Är Schreifweis!
VLOOKUP funktionnéiert net (Aschränkungen, Viraussetzungen an Entscheedungen)
Zousätzlech zu der zimlech komplizéierter Syntax, VPR huet méi Aschränkungen wéi all aner Excel Funktioun. Wéinst dëse Aschränkungen, scheinbar einfach Formulen mat VPR dacks zu onerwaarte Resultater féieren. Drënner fannt Dir Léisunge fir verschidde gemeinsam Szenarie wou VPR ass falsch.
1. VLOOKUP ass net case sensibel
Funktioun VPR ënnerscheet net tëscht Fall an akzeptéiert kleng Buschtawen an grouss Buschtawen als selwecht. Dofir, wann et e puer Elementer an der Tabell sinn, déi nëmmen am Fall ënnerscheeden, gëtt d'VLOOKUP Funktioun dat éischt fonnt Element zréck, egal vu Fall.
Entscheedung: Benotzt eng aner Excel Funktioun déi eng vertikal Sich kann ausféieren (LOOKUP, SUMPRODUCT, INDEX, a MATCH) a Kombinatioun mat EXAKTA datt Fall ënnerscheet. Fir méi Detailer, kënnt Dir aus der Lektioun léieren - 4 Weeër fir VLOOKUP Case-sensibel an Excel ze maachen.
2. VLOOKUP gëtt den éischte Wäert fonnt zréck
Wéi Dir scho wësst, VPR gëtt de Wäert vun der gegebene Kolonn zréck, entsprécht dem éischte fonnte Match. Wéi och ëmmer, Dir kënnt et extrahéieren den 2., 3., 4. oder all aner Widderhuelung vum Wäert deen Dir wëllt. Wann Dir all duplizéiert Wäerter extrahiere musst, braucht Dir eng Kombinatioun vu Funktiounen INDEX (INDEX), LESCHT (KLEIN) an LINN (ROER).
3. Eng Kolonn gouf derbäigesat oder op den Dësch geläscht
Leider, d'Formelen VPR ophalen ze schaffen all Kéier wann eng nei Kolonn derbäigesat oder ewechgeholl gëtt an de Lookuptabel. Dëst geschitt well d'Syntax VPR erfuerdert datt Dir déi ganz Palette vun der Sich an déi spezifesch Kolonnnummer fir d'Datenextraktioun spezifizéiert. Natierlech ännere souwuel de gegebene Beräich wéi och d'Kolonnnummer wann Dir eng Kolonn läscht oder eng nei asetzt.
Entscheedung: An erëm Funktiounen sinn presséiert ze hëllefen INDEX (INDEX) an MÉI ausgesat (MATCH). An der Formel INDEX+MATCH Dir definéiert separat Sich- an Erhuelungskolonnen, an als Resultat kënnt Dir esou vill Kolonnen läschen oder aginn wéi Dir wëllt ouni Iech Suergen ze maachen datt Dir all verbonne Sichformelen muss aktualiséieren.
4. Zell Referenze sinn garbled wann Dir eng Formel kopéiert
Dës Rubrik erkläert d'Essenz vum Problem ustrengend, richteg?
Entscheedung: Benotzt ëmmer absolut Zellreferenzen (mat dem Symbol $) op records der Rei, zum Beispill $A$2:$C$100 or $A:$C. An der Formelbar kënnt Dir séier de Linktyp wiesselen andeems Dir klickt F4.
VLOOKUP - schafft mat de Funktiounen IFERROR an ISERROR
Wann Dir d'Benotzer net mat Fehlermeldungen erschrecke wëllt # N / A, #WAARDE! or #NAME?, Dir kënnt eng eidel Zell oder Ären eegene Message weisen. Dir kënnt dat maachen andeems Dir plazéiert VPR an eng Funktioun IFERROR (IFERROR) an Excel 2013, 2010 an 2007 oder benotzt eng Rëtsch Funktiounen WANN + FEELER (IF+ISERROR) a fréiere Versiounen.
VLOOKUP: schafft mat der IFERROR Funktioun
Funktioun Syntax IFERROR (IFERROR) ass einfach a schwätzt fir sech:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Dat ass, fir dat éischt Argument gitt Dir de Wäert, deen op e Feeler gepréift gëtt, a fir dat zweet Argument spezifizéiert Dir wat fir zréckzekommen wann e Feeler fonnt gëtt.
Zum Beispill gëtt dës Formel eng eidel Zelle zréck wann de Wäert deen Dir sicht net fonnt gëtt:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Wann Dir Ären eegene Message wëllt weisen anstatt de Standardfehlermeldung vun der Funktioun VPR, setzt et an Zitaten, sou wéi:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: schafft mat der ISERROR Funktioun
Zënter der Funktioun IFERROR an Excel 2007 erschéngt, wann Dir a fréiere Versioune schafft, musst Dir d'Kombinatioun benotzen IF (IF) an EOSHIBKA (ISERROR) esou:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Zum Beispill, d'Formel WANN+FEELER+VLOOKUP, ähnlech wéi d'Formel IFERROR+VLOOKUPuewen ugewisen:
=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;ЛОЖЬ))
Dat ass alles fir haut. Ech hoffen dëse kuerzen Tutorial hëlleft Iech mat all méigleche Feeler ze këmmeren. VPR a maachen Är Formulen richteg Aarbecht.