Indhold
Denne lektion forklarer, hvordan man hurtigt håndterer en situation, hvor en funktion VPR (VLOOKUP) ønsker ikke at arbejde i Excel 2013, 2010, 2007 og 2003, og hvordan man kan identificere og rette almindelige fejl og overvinde begrænsninger VPR.
I flere tidligere artikler har vi udforsket funktionens forskellige facetter VPR i Excel. Hvis du har læst dem grundigt, bør du nu være ekspert på dette område. Det er dog ikke uden grund, at mange Excel-eksperter tror VPR en af de mere komplekse funktioner. Det har en masse begrænsninger og funktioner, der bliver kilden til mange problemer og fejl.
I denne artikel finder du simple forklaringer på fejl #AT (#N/A), #NAVN? (#NAVN?) og #VÆRDI! (#VALUE!), der vises, når du arbejder med funktionen VPR, samt teknikker og metoder til at håndtere dem. Vi starter med de mest almindelige tilfælde og de mest åbenlyse årsager til det. VPR virker ikke, så det er bedre at studere eksemplerne i den rækkefølge, de er givet i artiklen.
Retter #N/A fejl i VLOOKUP-funktionen i Excel
I formler med VPR fejlmeddelelse #AT (#N/A) betyder tilgængelig (ingen data) – vises, når Excel ikke kan finde den værdi, du leder efter. Dette kan ske af flere årsager.
1. Den ønskede værdi er stavet forkert
God idé at tjekke denne vare først! Slåfejl opstår ofte, når du arbejder med meget store mængder data, bestående af tusindvis af linjer, eller når den værdi, du leder efter, er skrevet ind i en formel.
2. #N/A fejl ved søgning efter et omtrentligt match med VLOOKUP
Hvis du bruger en formel med en omtrentlig match søgebetingelse, dvs. argument rækkevidde (range_lookup) er TRUE eller ikke angivet, kan din formel rapportere en fejl # N / A i to tilfælde:
- Værdien, der skal slås op, er mindre end den mindste værdi i det array, der slås op.
- Søgekolonnen er ikke sorteret i stigende rækkefølge.
3. #N/A fejl, når man leder efter et nøjagtigt match med VLOOKUP
Hvis du leder efter et eksakt match, altså argument rækkevidde (range_lookup) er FALSK og den nøjagtige værdi blev ikke fundet, formlen vil også rapportere en fejl # N / A. Lær mere om, hvordan du søger efter nøjagtige og omtrentlige match med en funktion VPR.
4. Søgekolonnen er ikke længst til venstre
Som du sikkert ved, en af de mest markante begrænsninger VPR det er, at det ikke kan vende mod venstre, derfor skal opslagskolonnen i din tabel være længst til venstre. I praksis glemmer vi ofte dette, hvilket fører til en ikke-fungerende formel og en fejl. # N / A.
Afgørelse: Hvis det ikke er muligt at ændre datastrukturen, så søgekolonnen er længst til venstre, kan du bruge en kombination af funktioner INDEX (INDEX) og MERE UDSAT (MATCH) som et mere fleksibelt alternativ til VPR.
5. Tal formateres som tekst
Endnu en fejlkilde # N / A i formler med VPR er tal i tekstformat i hovedtabellen eller opslagstabel.
Dette sker normalt, når du importerer oplysninger fra eksterne databaser, eller når du skriver en apostrof før et tal for at beholde det indledende nul.
De mest tydelige tegn på et tal i tekstformat er vist i figuren nedenfor:
Derudover kan tal gemmes i formatet Generelt (Generel). I dette tilfælde er der kun én mærkbar funktion - tallene er justeret til venstre kant af cellen, mens de som standard er justeret til højre kant.
Afgørelse: Hvis det er en enkelt værdi, skal du blot klikke på fejlikonet og vælge Konverter til nummer (Konverter til nummer) fra kontekstmenuen.
Hvis dette er situationen med mange tal, skal du vælge dem og højreklikke på det valgte område. I kontekstmenuen, der vises, skal du vælge formater celler (Formater celler) > faneblad nummer (Nummer) > format nummer (Numerisk) og tryk OK.
6. Der er et mellemrum i begyndelsen eller i slutningen
Dette er den mindst åbenlyse årsag til fejlen. # N / A i funktion VPR, da det er visuelt svært at se disse ekstra mellemrum, især når man arbejder med store borde, når de fleste data er uden for skærmen.
Løsning 1: Ekstra mellemrum i hovedtabellen (hvor VLOOKUP-funktionen er)
Hvis der vises ekstra mellemrum i hovedtabellen, kan du sikre dig, at formlerne fungerer korrekt ved at omslutte argumentet opslagsværdi (opslagsværdi) ind i en funktion TRIMME (TRIMME):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Løsning 2: Ekstra mellemrum i opslagstabellen (i opslagskolonnen)
Hvis der er ekstra mellemrum i søgekolonnen – enkle måder # N / A i formlen med VPR kan ikke undgås. I stedet for VPR Du kan bruge en matrixformel med en kombination af funktioner INDEX (INDEKS), MERE UDSAT (MATCH) и TRIMME (TRIMME):
=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))
Da dette er en matrixformel, glem ikke at trykke på Ctrl + Skift + Enter i stedet for det sædvanlige Indtastfor at indtaste formlen korrekt.
Fejl #VALUE! i formler med VLOOKUP
I de fleste tilfælde rapporterer Microsoft Excel en fejl #VÆRDI! (#VÆRDI!), når den anvendte værdi i formlen ikke matcher datatypen. Vedrørende VPR, så er der normalt to årsager til fejlen #VÆRDI!.
1. Den værdi, du leder efter, er længere end 255 tegn
Vær forsigtig: funktion VPR kan ikke søge efter værdier, der indeholder mere end 255 tegn. Hvis den værdi, du leder efter, overstiger denne grænse, vil du modtage en fejlmeddelelse. #VÆRDI!.
Afgørelse: Brug en masse funktioner INDEX+MATCH (INDEX + MATCH). Nedenfor er en formel, der vil fungere fint til denne opgave:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Den fulde sti til søgeprojektmappen er ikke angivet
Hvis du henter data fra en anden projektmappe, skal du angive den fulde sti til denne fil. Mere specifikt skal du inkludere projektmappens navn (inklusive udvidelsen) i firkantede parenteser [ ], efterfulgt af arknavnet efterfulgt af et udråbstegn. Al denne konstruktion skal være omgivet af apostrof, hvis bogen eller arkets navn indeholder mellemrum.
Her er den komplette struktur af funktionen VPR for at søge i en anden bog:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Den rigtige formel kan se sådan ud:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Denne formel vil slå celleværdien op A2 i en kolonne B på arket Sheet1 i arbejdsbogen Nye priser og udtræk den tilsvarende værdi fra kolonnen D.
Hvis nogen del af tabelstien er udeladt, er din funktion VPR vil ikke virke og vil rapportere en fejl #VÆRDI! (selvom projektmappen med opslagstabellen i øjeblikket er åben).
For mere information om funktionen VPRhenviser til en anden Excel-fil, se lektionen: Søgning i en anden projektmappe ved hjælp af VLOOKUP.
3. Argument Column_num er mindre end 1
Det er svært at forestille sig en situation, hvor nogen indtaster en værdi mindre end 1for at angive den kolonne, hvorfra værdien skal udtrækkes. Selvom det er muligt, hvis værdien af dette argument beregnes af en anden Excel-funktion indlejret i VPR.
Så hvis det sker, at argumentet col_index_num (kolonne_nummer) mindre end 1funktion VPR vil også rapportere en fejl #VÆRDI!.
Hvis argumentet col_index_num (column_number) er større end antallet af kolonner i den givne matrix, VPR vil rapportere en fejl #REF! (#SSYL!).
Fejl #NAME? i VLOOKUP
Det enkleste tilfælde er en fejl #NAVN? (#NAVN?) – vises, hvis du ved et uheld skriver et funktionsnavn med en fejl.
Løsningen er indlysende - tjek din stavning!
VLOOKUP virker ikke (begrænsninger, forbehold og beslutninger)
Ud over den ret komplicerede syntaks, VPR har flere begrænsninger end nogen anden Excel-funktion. På grund af disse begrænsninger, tilsyneladende simple formler med VPR fører ofte til uventede resultater. Nedenfor finder du løsninger til flere almindelige scenarier, hvor VPR er forkert.
1. VLOOKUP skelner ikke mellem store og små bogstaver
Funktion VPR skelner ikke mellem store og små bogstaver og accepterer små og store bogstaver som det samme. Derfor, hvis der er flere elementer i tabellen, der kun adskiller sig i tilfælde, vil funktionen VOPSLAG returnere det første fundne element, uanset tilfældet.
Afgørelse: Brug en anden Excel-funktion, der kan udføre en vertikal søgning (OPSLAG, SUMPRODUKT, INDEX og MATCH) i kombination med EKSAKTEn der adskiller sag. For flere detaljer kan du lære af lektionen – 4 måder at gøre VLOOKUP skelsættende mellem store og små bogstaver i Excel.
2. VLOOKUP returnerer den første fundne værdi
Som du allerede ved, VPR returnerer værdien fra den givne kolonne svarende til det første fundne match. Du kan dog få det til at udtrække 2., 3., 4. eller enhver anden gentagelse af den værdi, du ønsker. Hvis du skal udtrække alle duplikerede værdier, skal du bruge en kombination af funktioner INDEX (INDEKS), MINDST (LILLE) og LINE (RÆKKE).
3. En kolonne blev tilføjet eller fjernet til tabellen
Desværre formlerne VPR stoppe med at virke, hver gang en ny kolonne tilføjes eller fjernes til opslagstabellen. Dette sker, fordi syntaksen VPR kræver, at du angiver hele rækkevidden af søgningen og det specifikke kolonnenummer for dataudtræk. Naturligvis ændres både det givne område og kolonnenummeret, når du sletter en kolonne eller indsætter en ny.
Afgørelse: Og igen har funktioner travlt med at hjælpe INDEX (INDEX) og MERE UDSAT (MATCH). I formlen INDEX+MATCH Du definerer separat søge- og genfindingskolonner, og som et resultat kan du slette eller indsætte så mange kolonner, du vil, uden at bekymre dig om at skulle opdatere alle relaterede søgeformler.
4. Cellereferencer er forvanskede, når du kopierer en formel
Denne overskrift forklarer essensen af problemet udtømmende, ikke?
Afgørelse: Brug altid absolutte cellereferencer (med symbolet $) på poster rækkevidden, for eksempel $ A $ 2: $ C $ 100 or $ A: $ C. I formellinjen kan du hurtigt skifte linktype ved at klikke F4.
VLOOKUP – arbejder med funktionerne IFERROR og ISERROR
Hvis du ikke vil skræmme brugerne med fejlmeddelelser # N / A, #VÆRDI! or #NAVN?, kan du vise en tom celle eller din egen besked. Du kan gøre dette ved at placere VPR ind i en funktion FEJL (IFERROR) i Excel 2013, 2010 og 2007 eller brug en masse funktioner HVIS+FEJL (HVIS+FEJL) i tidligere versioner.
VLOOKUP: arbejder med IFERROR-funktionen
Funktions syntaks FEJL (IFERROR) er enkel og taler for sig selv:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Det vil sige, at du for det første argument indsætter den værdi, der skal kontrolleres for en fejl, og for det andet argument angiver du, hvad der skal returneres, hvis der findes en fejl.
For eksempel returnerer denne formel en tom celle, hvis den værdi, du leder efter, ikke findes:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Hvis du ønsker at vise din egen besked i stedet for funktionens standard fejlmeddelelse VPR, sæt det i anførselstegn, sådan:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: arbejder med FEJL-funktionen
Siden funktionen FEJL dukkede op i Excel 2007, når du arbejder i tidligere versioner, skal du bruge kombinationen IF (IF) og EOSHIBKA (ISERROR) sådan her:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
For eksempel formlen HVIS+FEJL+VLOOKUP, svarende til formlen IFERROR+VLOOKUPvist ovenfor:
=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;ЛОЖЬ))
Det var alt for i dag. Jeg håber, at denne korte vejledning vil hjælpe dig med at håndtere alle mulige fejl. VPR og få dine formler til at fungere korrekt.