Inhoud
- Fix #N/A-fout in VERT.ZOEKEN-functie in Excel
- 1. De gewenste waarde is verkeerd gespeld
- 2. #N/A-fout bij het zoeken naar een geschatte overeenkomst met VERT.ZOEKEN
- 3. #N/A-fout bij het zoeken naar een exacte overeenkomst met VERT.ZOEKEN
- 4. Zoekkolom is niet meest links
- 5. Getallen zijn opgemaakt als tekst
- 6. Er is een spatie aan het begin of aan het einde
- Fout #WAARDE! in formules met VERT.ZOEKEN
- Fout #NAME? in VERT.ZOEKEN
- VERT.ZOEKEN werkt niet (beperkingen, voorbehouden en beslissingen)
- VERT.ZOEKEN – werken met de functies IFERROR en ISERROR
In deze les wordt uitgelegd hoe u snel kunt omgaan met een situatie waarin een functie VPR (VERT.ZOEKEN) niet wil werken in Excel 2013, 2010, 2007 en 2003, en hoe u veelvoorkomende fouten kunt identificeren en oplossen en beperkingen kunt overwinnen VPR.
In verschillende eerdere artikelen hebben we de verschillende facetten van de functie onderzocht VPR in Excel. Als je ze aandachtig hebt gelezen, zou je nu een expert op dit gebied moeten zijn. Het is echter niet voor niets dat veel Excel-experts geloven VPR een van de meer complexe functies. Het heeft een heleboel beperkingen en functies die de bron worden van veel problemen en fouten.
In dit artikel vindt u eenvoudige uitleg van fouten #AT (#N.v.t.), #NAME? (#Naam en #WAARDE! (#VALUE!) die verschijnen bij het werken met de functie VPR, evenals technieken en methoden om ermee om te gaan. We beginnen met de meest voorkomende gevallen en de meest voor de hand liggende redenen waarom. VPR werkt niet, dus het is beter om de voorbeelden te bestuderen in de volgorde waarin ze in het artikel worden gegeven.
Fix #N/A-fout in VERT.ZOEKEN-functie in Excel
In formules met VPR foutmelding #AT (#N/A) betekent niet beschikbaar (geen gegevens) – verschijnt wanneer Excel de waarde die u zoekt niet kan vinden. Dit kan om verschillende redenen gebeuren.
1. De gewenste waarde is verkeerd gespeld
Goed idee om dit item eerst te controleren! Typefouten komen vaak voor wanneer u werkt met zeer grote hoeveelheden gegevens, bestaande uit duizenden regels, of wanneer de waarde die u zoekt in een formule wordt geschreven.
2. #N/A-fout bij het zoeken naar een geschatte overeenkomst met VERT.ZOEKEN
Als u een formule gebruikt met een zoekvoorwaarde bij benadering, bijv. argument bereik_opzoeken (range_lookup) WAAR is of niet gespecificeerd, uw formule kan een fout rapporteren # N / A in twee gevallen:
- De waarde die moet worden opgezocht, is kleiner dan de kleinste waarde in de array die wordt opgezocht.
- De zoekkolom is niet in oplopende volgorde gesorteerd.
3. #N/A-fout bij het zoeken naar een exacte overeenkomst met VERT.ZOEKEN
Als u op zoek bent naar een exacte overeenkomst, dwz argument bereik_opzoeken (range_lookup) is ONWAAR en de exacte waarde is niet gevonden, de formule geeft ook een fout weer # N / A. Meer informatie over het zoeken naar exacte en geschatte overeenkomsten met een functie VPR.
4. Zoekkolom is niet meest links
Zoals u waarschijnlijk weet, is een van de belangrijkste beperkingen VPR het is dat het niet naar links mag wijzen, daarom moet de opzoekkolom in uw tabel uiterst links zijn. In de praktijk vergeten we dit vaak, wat leidt tot een niet werkende formule en een fout. # N / A.
Besluit: Als het niet mogelijk is om de gegevensstructuur te wijzigen zodat de zoekkolom uiterst links is, kunt u een combinatie van functies gebruiken INDEX (INDEX) en MEER BLOOTGESTELD (MATCH) als een flexibeler alternatief voor VPR.
5. Getallen zijn opgemaakt als tekst
Een andere bron van fouten # N / A in formules met VPR zijn getallen in tekstformaat in de hoofdtabel of opzoektabel.
Dit gebeurt meestal wanneer u informatie uit externe databases importeert, of wanneer u een apostrof typt voor een getal om de voorloopnul te behouden.
De meest voor de hand liggende tekens van een getal in tekstformaat worden weergegeven in de onderstaande afbeelding:
Bovendien kunnen getallen worden opgeslagen in het formaat Algemeen (Algemeen). In dit geval is er maar één opvallend kenmerk: de cijfers zijn uitgelijnd met de linkerrand van de cel, terwijl ze standaard zijn uitgelijnd met de rechterrand.
Besluit: Als het een enkele waarde is, klikt u gewoon op het foutpictogram en selecteert u Converteren naar nummer (Converteren naar nummer) in het contextmenu.
Als dit het geval is met veel nummers, selecteert u deze en klikt u met de rechtermuisknop op het geselecteerde gebied. In het contextmenu dat verschijnt, selecteer Cellen opmaken (Cellen opmaken) > tab Telefoon Nummer (Getal) > formaat Telefoon Nummer (Numeriek) en druk op OK.
6. Er is een spatie aan het begin of aan het einde
Dit is de minst voor de hand liggende reden voor de fout. # N / A in functie VPR, omdat het visueel moeilijk is om deze extra spaties te zien, vooral bij het werken met grote tabellen, wanneer de meeste gegevens zich buiten het scherm bevinden.
Oplossing 1: extra spaties in de hoofdtabel (waar de functie VERT.ZOEKEN is)
Als er extra spaties in de hoofdtabel verschijnen, kunt u ervoor zorgen dat de formules correct werken door het argument in te sluiten opzoekwaarde (lookup_value) in een functie TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Oplossing 2: extra spaties in de opzoektabel (in de opzoekkolom)
Als er extra spaties in de zoekkolom staan – eenvoudige manieren # N / A in de formule met VPR niet kan worden vermeden. In plaats van VPR U kunt een matrixformule gebruiken met een combinatie van functies INDEX (INHOUDSOPGAVE), MEER BLOOTGESTELD (WEDSTRIJD) и 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))
Aangezien dit een matrixformule is, vergeet niet op . te drukken Ctrl + Shift + Enter in plaats van het gebruikelijke Enterom de formule correct in te voeren.
Fout #WAARDE! in formules met VERT.ZOEKEN
In de meeste gevallen meldt Microsoft Excel een fout #WAARDE! (#VALUE!) wanneer de waarde die in de formule wordt gebruikt niet overeenkomt met het gegevenstype. Met betrekking tot VPR, dan zijn er meestal twee redenen voor de fout: #WAARDE!.
1. De waarde die u zoekt is langer dan 255 tekens
Let op: functie VPR kan niet zoeken naar waarden die meer dan 255 tekens bevatten. Als de waarde die u zoekt deze limiet overschrijdt, krijgt u een foutmelding. #WAARDE!.
Besluit: Gebruik een heleboel functies INDEX+WEDSTRIJD (INDEX + WEDSTRIJD). Hieronder is een formule die prima werkt voor deze taak:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Het volledige pad naar de zoekwerkmap is niet opgegeven
Als u gegevens ophaalt uit een andere werkmap, moet u het volledige pad naar dat bestand opgeven. Meer specifiek moet u de naam van de werkmap (inclusief de extensie) tussen vierkante haken [ ] opnemen, gevolgd door de bladnaam, gevolgd door een uitroepteken. Al deze constructie moet tussen apostrofs staan, voor het geval de boek- of bladnaam spaties bevat.
Hier is de volledige structuur van de functie: VPR zoeken in een ander boek:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
De echte formule kan er als volgt uitzien:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Deze formule zoekt de celwaarde op A2 in een kolom B op het blad Sheet1 in het werkboek Nieuwe prijzen en extraheer de bijbehorende waarde uit de kolom D.
Als een deel van het tabelpad wordt weggelaten, is uw functie VPR zal niet werken en zal een fout rapporteren #WAARDE! (zelfs als de werkmap met de opzoektabel momenteel geopend is).
Voor meer informatie over de functie VPRverwijzend naar een ander Excel-bestand, zie de les: Een andere werkmap zoeken met VERT.ZOEKEN.
3. Argument Column_num is kleiner dan 1
Het is moeilijk om je een situatie voor te stellen waarin iemand een waarde invoert die kleiner is dan 1om de kolom aan te geven waaruit de waarde moet worden geëxtraheerd. Hoewel het mogelijk is als de waarde van dit argument wordt berekend door een andere Excel-functie die is genest in VPR.
Dus, als het gebeurt dat het argument kolomindex_getal (kolom_nummer) kleiner dan 1functie VPR zal ook een fout melden #WAARDE!.
Als het argument kolomindex_getal (column_number) groter is dan het aantal kolommen in de gegeven array, VPR zal een fout melden #REF! (#SSYL!).
Fout #NAME? in VERT.ZOEKEN
Het eenvoudigste geval is een vergissing #NAME? (#NAAM?) – verschijnt als u per ongeluk een functienaam met een fout schrijft.
De oplossing ligt voor de hand: controleer je spelling!
VERT.ZOEKEN werkt niet (beperkingen, voorbehouden en beslissingen)
Naast de nogal gecompliceerde syntaxis, VPR heeft meer beperkingen dan elke andere Excel-functie. Vanwege deze beperkingen, ogenschijnlijk eenvoudige formules met VPR leiden vaak tot onverwachte resultaten. Hieronder vindt u oplossingen voor verschillende veelvoorkomende scenario's waarbij: VPR is fout.
1. VERT.ZOEKEN is niet hoofdlettergevoelig
Functie VPR maakt geen onderscheid tussen hoofdletters en accepteert kleine letters en hoofdletters als hetzelfde. Daarom, als er meerdere elementen in de tabel zijn die alleen verschillen in hoofdletters, zal de functie VERT.ZOEKEN het eerste gevonden element retourneren, ongeacht hoofdletters.
Besluit: Gebruik een andere Excel-functie die een verticale zoekopdracht kan uitvoeren (LOOKUP, SUMPRODUCT, INDEX en MATCH) in combinatie met EXACTEen dat geval onderscheidt. Voor meer details kunt u van de les leren - 4 manieren om VERT.ZOEKEN hoofdlettergevoelig te maken in Excel.
2. VERT.ZOEKEN retourneert de eerste gevonden waarde
Zoals je al weet, VPR retourneert de waarde uit de gegeven kolom die overeenkomt met de eerste gevonden overeenkomst. U kunt het echter de 2e, 3e, 4e of elke andere herhaling van de gewenste waarde laten extraheren. Als u alle dubbele waarden moet extraheren, hebt u een combinatie van functies nodig INDEX (INHOUDSOPGAVE), MINST (KLEIN) en LINE (RIJ).
3. Er is een kolom aan de tabel toegevoegd of verwijderd
Helaas, de formules VPR stoppen met werken telkens wanneer een nieuwe kolom wordt toegevoegd of verwijderd aan de opzoektabel. Dit gebeurt omdat de syntaxis VPR vereist dat u het volledige zoekbereik specificeert en het specifieke kolomnummer voor gegevensextractie. Uiteraard veranderen zowel het opgegeven bereik als het kolomnummer wanneer u een kolom verwijdert of een nieuwe invoegt.
Besluit: En opnieuw hebben functies haast om te helpen INDEX (INDEX) en MEER BLOOTGESTELD (WEDSTRIJD). In de formule INDEX+WEDSTRIJD U definieert afzonderlijk zoek- en ophaalkolommen en als resultaat kunt u zoveel kolommen verwijderen of invoegen als u wilt zonder dat u zich zorgen hoeft te maken over het bijwerken van alle gerelateerde zoekformules.
4. Celverwijzingen zijn onleesbaar bij het kopiëren van een formule
Deze kop legt de essentie van het probleem uitputtend uit, toch?
Besluit: Gebruik altijd absolute celverwijzingen (met het symbool $) op records het bereik, bijvoorbeeld $ A $ 2: $ C $ 100 or $ A: $ C. In de formulebalk kunt u snel van type koppeling wisselen door op . te klikken F4.
VERT.ZOEKEN – werken met de functies IFERROR en ISERROR
Als je gebruikers niet bang wilt maken met foutmeldingen # N / A, #WAARDE! or #NAME?, kunt u een lege cel of uw eigen bericht weergeven. Dit doe je door het plaatsen van VPR in een functie IFFOUT (IFERROR) in Excel 2013, 2010 en 2007 of gebruik een heleboel functies ALS+ISFOUT (IF+ISERROR) in eerdere versies.
VERT.ZOEKEN: werken met de IFERROR-functie
Functiesyntaxis: IFFOUT (IFERROR) is eenvoudig en spreekt voor zich:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Dat wil zeggen, voor het eerste argument voert u de waarde in die op een fout moet worden gecontroleerd, en voor het tweede argument geeft u op wat moet worden geretourneerd als er een fout wordt gevonden.
Deze formule retourneert bijvoorbeeld een lege cel als de waarde die u zoekt niet wordt gevonden:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Als u uw eigen bericht wilt weergeven in plaats van het standaardfoutbericht van de functie VPR, zet het tussen aanhalingstekens, zoals zo:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VERT.ZOEKEN: werken met de ISERROR-functie
Omdat de functie IFFOUT verscheen in Excel 2007, als u in eerdere versies werkt, moet u de combinatie gebruiken IF (ALS) en EOSHIBKA (ISERROR) als volgt:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Bijvoorbeeld de formule IF+ISERROR+VERT.ZOEKEN, vergelijkbaar met de formule IFERROR+VERT.ZOEKENhierboven weergegeven:
=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 is alles voor vandaag. Ik hoop dat deze korte tutorial je zal helpen om met alle mogelijke fouten om te gaan. VPR en zorg ervoor dat uw formules correct werken.