Innhold
Denne leksjonen forklarer hvordan du raskt kan håndtere en situasjon der en funksjon VPR (VLOOKUP) ønsker ikke å fungere i Excel 2013, 2010, 2007 og 2003, og hvordan identifisere og fikse vanlige feil og overvinne begrensninger VPR.
I flere tidligere artikler har vi utforsket funksjonens ulike fasetter VPR i Excel. Hvis du har lest dem nøye, bør du nå være en ekspert på dette feltet. Det er imidlertid ikke uten grunn at mange Excel-eksperter tror VPR en av de mer komplekse funksjonene. Den har en haug med begrensninger og funksjoner som blir kilden til mange problemer og feil.
I denne artikkelen finner du enkle forklaringer på feil #AT (#N/A), # NAVN? (#NAVN?) og #VERDI! (#VERDI!) som vises når du arbeider med funksjonen VPR, samt teknikker og metoder for å håndtere dem. Vi starter med de vanligste tilfellene og de mest åpenbare årsakene. VPR fungerer ikke, så det er bedre å studere eksemplene i den rekkefølgen de er gitt i artikkelen.
Retter #N/A-feil i VLOOKUP-funksjonen i Excel
I formler med VPR feilmelding #AT (#N/A) betyr ikke tilgjengelig (ingen data) – vises når Excel ikke finner verdien du leter etter. Dette kan skje av flere årsaker.
1. Den ønskede verdien er feilstavet
God idé å sjekke denne varen først! Skrivefeil oppstår ofte når du arbeider med svært store datamengder, bestående av tusenvis av linjer, eller når verdien du leter etter skrives inn i en formel.
2. #N/A feil ved søk etter et omtrentlig samsvar med VLOOKUP
Hvis du bruker en formel med en omtrentlig søkebetingelse, dvs. argument range_lookup (range_lookup) er TRUE eller ikke spesifisert, kan formelen din rapportere en feil # N / A i to tilfeller:
- Verdien som skal slås opp er mindre enn den minste verdien i matrisen som slås opp.
- Søkekolonnen er ikke sortert i stigende rekkefølge.
3. #N/A feil når du leter etter et eksakt samsvar med VLOOKUP
Hvis du leter etter en eksakt match, dvs. argument range_lookup (range_lookup) er FALSE og den eksakte verdien ble ikke funnet, formelen vil også rapportere en feil # N / A. Finn ut mer om hvordan du søker etter eksakte og omtrentlige treff med en funksjon VPR.
4. Søkekolonnen er ikke lengst til venstre
Som du sikkert vet, en av de viktigste begrensningene VPR det er at den ikke kan vende mot venstre, derfor må oppslagskolonnen i tabellen din være lengst til venstre. I praksis glemmer vi ofte dette, noe som fører til en ikke-fungerende formel og en feil. # N / A.
Beslutning: Hvis det ikke er mulig å endre datastrukturen slik at søkekolonnen er lengst til venstre, kan du bruke en kombinasjon av funksjoner INDEX (INDEKS) og MER UTSATT (MATCH) som et mer fleksibelt alternativ for VPR.
5. Tall er formatert som tekst
Enda en feilkilde # N / A i formler med VPR er tall i tekstformat i hovedtabellen eller oppslagstabellen.
Dette skjer vanligvis når du importerer informasjon fra eksterne databaser, eller når du skriver en apostrof foran et tall for å beholde den innledende null.
De mest åpenbare tegnene på et tall i tekstformat er vist i figuren nedenfor:
I tillegg kan tall lagres i formatet general (Generell). I dette tilfellet er det bare en merkbar funksjon - tallene er justert til venstre kant av cellen, mens de som standard er justert til høyre kant.
Beslutning: Hvis det er en enkelt verdi, klikker du bare på feilikonet og velger Konverter til tall (Konverter til nummer) fra hurtigmenyen.
Hvis dette er situasjonen med mange tall, velg dem og høyreklikk på det valgte området. Velg i kontekstmenyen som vises Formater celler (Formater celler) > kategori Nr (Tall) > format Nr (Numerisk) og trykk OK.
6. Det er et mellomrom i begynnelsen eller på slutten
Dette er den minst åpenbare årsaken til feilen. # N / A i funksjon VPR, siden det er visuelt vanskelig å se disse ekstra plassene, spesielt når du arbeider med store tabeller, når de fleste dataene er utenfor skjermen.
Løsning 1: Ekstra mellomrom i hovedtabellen (hvor VLOOKUP-funksjonen er)
Hvis det vises ekstra mellomrom i hovedtabellen, kan du sørge for at formlene fungerer riktig ved å legge ved argumentet søkeverdi (oppslagsverdi) inn i en funksjon TRIM (LISTVERK):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Løsning 2: Ekstra mellomrom i oppslagstabellen (i oppslagskolonnen)
Hvis det er ekstra mellomrom i søkekolonnen – enkle måter # N / A i formelen med VPR kan ikke unngås. I stedet for VPR Du kan bruke en matriseformel med en kombinasjon av funksjoner INDEX (INDEKS), MER UTSATT (MATCH) и TRIM (LISTVERK):
=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))
Siden dette er en matriseformel, ikke glem å trykke Ctrl + Shift + Enter i stedet for det vanlige Enterfor å angi formelen riktig.
Feil #VALUE! i formler med VLOOKUP
I de fleste tilfeller rapporterer Microsoft Excel en feil #VERDI! (#VERDI!) når verdien brukt i formelen ikke samsvarer med datatypen. Angående VPR, så er det vanligvis to årsaker til feilen #VERDI!.
1. Verdien du ser etter er lengre enn 255 tegn
Vær forsiktig: funksjon VPR kan ikke søke etter verdier som inneholder mer enn 255 tegn. Hvis verdien du ser etter overskrider denne grensen, vil du motta en feilmelding. #VERDI!.
Beslutning: Bruk en haug med funksjoner INDEKS+SAMLING (INDEX + MATCH). Nedenfor er en formel som vil fungere fint for denne oppgaven:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Den fullstendige banen til søkearbeidsboken er ikke spesifisert
Hvis du henter data fra en annen arbeidsbok, må du spesifisere hele banen til den filen. Mer spesifikt må du inkludere arbeidsboknavnet (inkludert utvidelsen) i hakeparenteser [ ], etterfulgt av arknavnet, etterfulgt av et utropstegn. All denne konstruksjonen må omsluttes av apostrof, i tilfelle bok- eller arknavnet inneholder mellomrom.
Her er den fullstendige strukturen til funksjonen VPR for å søke i en annen bok:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Den virkelige formelen kan se slik ut:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Denne formelen vil slå opp celleverdien A2 i en kolonne B på arket Sheet1 i arbeidsboka Nye priser og trekk ut den tilsvarende verdien fra kolonnen D.
Hvis noen del av tabellbanen er utelatt, er funksjonen din VPR vil ikke fungere og vil rapportere en feil #VERDI! (selv om arbeidsboken med oppslagstabellen er åpen for øyeblikket).
For mer informasjon om funksjonen VPRrefererer til en annen Excel-fil, se leksjonen: Søke i en annen arbeidsbok ved å bruke VLOOKUP.
3. Argument Column_num er mindre enn 1
Det er vanskelig å forestille seg en situasjon der noen angir en verdi mindre enn 1for å angi kolonnen som verdien skal trekkes ut fra. Selv om det er mulig hvis verdien av dette argumentet beregnes av en annen Excel-funksjon nestet i VPR.
Så, hvis det skjer at argumentet col_index_num (kolonne_nummer) mindre enn 1funksjon VPR vil også rapportere en feil #VERDI!.
Hvis argumentet col_index_num (kolonne_nummer) er større enn antall kolonner i den gitte matrisen, VPR vil rapportere en feil #REF! (#SSYL!).
Feil #NAME? i VLOOKUP
Det enkleste tilfellet er en feil # NAVN? (#NAME?) – vises hvis du ved et uhell skriver et funksjonsnavn med en feil.
Løsningen er åpenbar – sjekk stavemåten din!
VLOOKUP fungerer ikke (begrensninger, forbehold og beslutninger)
I tillegg til den ganske kompliserte syntaksen, VPR har flere begrensninger enn noen annen Excel-funksjon. På grunn av disse begrensningene, tilsynelatende enkle formler med VPR fører ofte til uventede resultater. Nedenfor finner du løsninger for flere vanlige scenarier hvor VPR er feil.
1. VLOOKUP skiller ikke mellom store og små bokstaver
Funksjon VPR skiller ikke mellom store og små bokstaver og godtar små og store bokstaver som det samme. Derfor, hvis det er flere elementer i tabellen som avviker bare i kasus, vil VLOOKUP-funksjonen returnere det første elementet som ble funnet, uavhengig av kasus.
Beslutning: Bruk en annen Excel-funksjon som kan utføre et vertikalt søk (LOOKUP, SUMPRODUCT, INDEX og MATCH) i kombinasjon med NØYAKTIGEn som skiller sak. For flere detaljer kan du lære av leksjonen – 4 måter å gjøre VLOOKUP skille-sensitive i Excel.
2. VLOOKUP returnerer den første verdien som ble funnet
Som du allerede vet, VPR returnerer verdien fra den gitte kolonnen som tilsvarer det første treffet som ble funnet. Du kan imidlertid få den til å trekke ut den andre, tredje, fjerde eller en hvilken som helst annen repetisjon av verdien du ønsker. Hvis du trenger å trekke ut alle dupliserte verdier, trenger du en kombinasjon av funksjoner INDEX (INDEKS), MINST (LITEN) og LINE (RAD).
3. En kolonne ble lagt til eller fjernet i tabellen
Dessverre, formlene VPR slutte å virke hver gang en ny kolonne legges til eller fjernes i oppslagstabellen. Dette skjer fordi syntaksen VPR krever at du spesifiserer hele søkeområdet og det spesifikke kolonnenummeret for datautvinning. Naturligvis endres både det gitte området og kolonnenummeret når du sletter en kolonne eller setter inn en ny.
Beslutning: Og igjen har funksjoner det travelt med å hjelpe INDEX (INDEKS) og MER UTSATT (KAMP). I formelen INDEKS+SAMLING Du definerer søke- og gjenfinningskolonner separat, og som et resultat kan du slette eller sette inn så mange kolonner du vil uten å bekymre deg for å måtte oppdatere alle relaterte søkeformler.
4. Cellereferanser blir forvansket når du kopierer en formel
Denne overskriften forklarer essensen av problemet uttømmende, ikke sant?
Beslutning: Bruk alltid absolutte cellereferanser (med symbolet $) på poster rekkevidden, for eksempel $ A $ 2: $ C $ 100 or $A:$C. I formellinjen kan du raskt bytte koblingstype ved å klikke F4.
VLOOKUP – arbeider med funksjonene IFERROR og ISERROR
Hvis du ikke vil skremme brukere med feilmeldinger # N / A, #VERDI! or # NAVN?, kan du vise en tom celle eller din egen melding. Du kan gjøre dette ved å plassere VPR inn i en funksjon FEIL (IFERROR) i Excel 2013, 2010 og 2007 eller bruk en haug med funksjoner HVIS+FEIL (HVIS+FEIL) i tidligere versjoner.
VLOOKUP: arbeider med IFERROR-funksjonen
Funksjonssyntaks FEIL (IFERROR) er enkel og taler for seg selv:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Det vil si at for det første argumentet setter du inn verdien som skal sjekkes for en feil, og for det andre argumentet angir du hva som skal returneres hvis en feil blir funnet.
For eksempel returnerer denne formelen en tom celle hvis verdien du leter etter ikke finnes:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Hvis du ønsker å vise din egen melding i stedet for funksjonens standard feilmelding VPR, sett det i anførselstegn, slik:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: arbeider med FEIL-funksjonen
Siden funksjonen FEIL dukket opp i Excel 2007, når du arbeider i tidligere versjoner, må du bruke kombinasjonen IF (IF) og EOSHIBKA (ISERROR) slik:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
For eksempel formelen HVIS+FEIL+VLOOKUP, lik formelen 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åper denne korte opplæringen vil hjelpe deg med å takle alle mulige feil. VPR og få formlene til å fungere riktig.