Innehåll
Den här lektionen förklarar hur man snabbt hanterar en situation där en funktion VPR (VLOOKUP) vill inte fungera i Excel 2013, 2010, 2007 och 2003, och hur man identifierar och åtgärdar vanliga fel och övervinner begränsningar VPR.
I flera tidigare artiklar har vi utforskat funktionens olika aspekter VPR i Excel. Om du har läst dem noga bör du nu vara expert på detta område. Det är dock inte utan anledning som många Excel-experter tror VPR en av de mer komplexa funktionerna. Den har ett gäng begränsningar och funktioner som blir källan till många problem och fel.
I den här artikeln hittar du enkla förklaringar av fel #AT (#N/A), # NAMN? (#NAMN?) och #VÄRDE! (#VALUE!) som visas när man arbetar med funktionen VPR, samt tekniker och metoder för att hantera dem. Vi börjar med de vanligaste fallen och de mest uppenbara anledningarna. VPR fungerar inte, så det är bättre att studera exemplen i den ordning som de ges i artikeln.
Fixar #N/A-fel i VLOOKUP-funktionen i Excel
I formler med VPR felmeddelande #AT (#N/A) betyder ej tillgänglig (inga data) – visas när Excel inte kan hitta värdet du letar efter. Detta kan hända av flera anledningar.
1. Det önskade värdet är felstavat
En bra idé att kontrollera detta föremål först! Typfel uppstår ofta när du arbetar med mycket stora datamängder, bestående av tusentals rader, eller när värdet du letar efter skrivs in i en formel.
2. #N/A fel vid sökning efter en ungefärlig matchning med VLOOKUP
Om du använder en formel med ett ungefärligt matchande sökvillkor, dvs argument range_lookup (range_lookup) är TRUE eller inte specificerat, din formel kan rapportera ett fel # N / A i två fall:
- Värdet att slå upp är mindre än det minsta värdet i arrayen som slås upp.
- Sökkolumnen är inte sorterad i stigande ordning.
3. #N/A-fel när man letar efter en exakt matchning med VLOOKUP
Om du letar efter en exakt matchning, dvs argument range_lookup (range_lookup) är FALSK och det exakta värdet hittades inte, formeln kommer också att rapportera ett fel # N / A. Läs mer om hur du söker efter exakta och ungefärliga matchningar med en funktion VPR.
4. Sökkolumnen är inte längst till vänster
Som du säkert vet, en av de viktigaste begränsningarna VPR det är att den inte kan vara vänd åt vänster, därför måste uppslagskolumnen i din tabell vara längst till vänster. I praktiken glömmer vi ofta bort detta, vilket leder till en formel som inte fungerar och ett fel. # N / A.
Beslut: Om det inte går att ändra datastrukturen så att sökkolumnen är längst till vänster kan du använda en kombination av funktioner INDEX (INDEX) och MER EXPONERAD (MATCH) som ett mer flexibelt alternativ för VPR.
5. Siffror är formaterade som text
Ytterligare en felkälla # N / A i formler med VPR är siffror i textformat i huvudtabellen eller uppslagstabellen.
Detta händer vanligtvis när du importerar information från externa databaser, eller när du skriver en apostrof före ett nummer för att behålla den inledande nollan.
De mest uppenbara tecknen på ett tal i textformat visas i figuren nedan:
Dessutom kan nummer lagras i formatet Allmänt (Allmän). I det här fallet finns det bara en märkbar funktion - siffrorna är justerade till den vänstra kanten av cellen, medan de som standard är justerade till den högra kanten.
Beslut: Om det är ett enskilt värde klickar du bara på felikonen och väljer Konvertera till nummer (Konvertera till nummer) från snabbmenyn.
Om detta är situationen med många nummer, välj dem och högerklicka på det valda området. Välj i snabbmenyn som visas Formatera celler (Formatera celler) > flik Antal (Nummer) > format Antal (Numerisk) och tryck OK.
6. Det finns ett mellanslag i början eller i slutet
Detta är den minst uppenbara orsaken till felet. # N / A i funktion VPR, eftersom det är visuellt svårt att se dessa extra utrymmen, särskilt när man arbetar med stora tabeller, när det mesta av data är utanför skärmen.
Lösning 1: Extra utrymmen i huvudtabellen (där funktionen VLOOKUP finns)
Om extra mellanslag visas i huvudtabellen kan du säkerställa att formlerna fungerar korrekt genom att omsluta argumentet letauppvärde (lookup_value) till en funktion TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Lösning 2: Extra mellanslag i uppslagstabellen (i uppslagskolumnen)
Om extra mellanslag finns i sökkolumnen – enkla sätt # N / A i formeln med VPR kan inte undvikas. Istället för VPR Du kan använda en matrisformel med en kombination av funktioner INDEX (INDEX), MER EXPONERAD (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))
Eftersom detta är en matrisformel, glöm inte att trycka Ctrl + Skift + Enter istället för det vanliga angeför att ange formeln korrekt.
Fel #VALUE! i formler med VLOOKUP
I de flesta fall rapporterar Microsoft Excel ett fel #VÄRDE! (#VALUE!) när värdet som används i formeln inte matchar datatypen. Rörande VPR, då finns det vanligtvis två orsaker till felet #VÄRDE!.
1. Värdet du letar efter är längre än 255 tecken
Var försiktig: funktion VPR kan inte söka efter värden som innehåller mer än 255 tecken. Om värdet du letar efter överskrider denna gräns får du ett felmeddelande. #VÄRDE!.
Beslut: Använd en massa funktioner INDEX+MATCH (INDEX + MATCH). Nedan är en formel som fungerar bra för denna uppgift:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Den fullständiga sökvägen till sökarbetsboken anges inte
Om du hämtar data från en annan arbetsbok måste du ange den fullständiga sökvägen till den filen. Mer specifikt måste du inkludera arbetsbokens namn (inklusive tillägget) inom hakparenteser [ ], följt av arknamnet, följt av ett utropstecken. All denna konstruktion måste omges av apostrof, om bokens eller arkets namn innehåller mellanslag.
Här är den fullständiga strukturen för funktionen VPR för att söka i en annan bok:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Den verkliga formeln kan se ut så här:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Denna formel kommer att slå upp cellvärdet A2 i en kolumn B på arket Sheet1 i arbetsboken Nya priser och extrahera motsvarande värde från kolumnen D.
Om någon del av tabellens sökväg utelämnas, din funktion VPR kommer inte att fungera och kommer att rapportera ett fel #VÄRDE! (även om arbetsboken med uppslagstabellen för närvarande är öppen).
För mer information om funktionen VPRhänvisar till en annan Excel-fil, se lektionen: Söka i en annan arbetsbok med VLOOKUP.
3. Argument Column_num är mindre än 1
Det är svårt att föreställa sig en situation där någon anger ett värde som är mindre än 1för att ange kolumnen från vilken värdet ska extraheras. Även om det är möjligt om värdet av detta argument beräknas av en annan Excel-funktion kapslad i VPR.
Så, om det händer att argumentet kolumnindex (kolumnnummer) mindre än 1fungera VPR kommer också att rapportera ett fel #VÄRDE!.
Om argumentet kolumnindex (kolumnnummer) är större än antalet kolumner i den givna arrayen, VPR kommer att rapportera ett fel #REF! (#SSYL!).
Fel #NAME? i VLOOKUP
Det enklaste fallet är ett misstag # NAMN? (#NAME?) – visas om du av misstag skriver ett funktionsnamn med ett fel.
Lösningen är uppenbar – kontrollera din stavning!
VLOOKUP fungerar inte (begränsningar, varningar och beslut)
Förutom den ganska komplicerade syntaxen, VPR har fler begränsningar än någon annan Excel-funktion. På grund av dessa begränsningar, till synes enkla formler med VPR leder ofta till oväntade resultat. Nedan hittar du lösningar för flera vanliga scenarier där VPR är fel.
1. VLOOKUP är inte skiftlägeskänslig
Funktion VPR skiljer inte på skiftläge och accepterar gemener och versaler som samma. Därför, om det finns flera element i tabellen som endast skiljer sig åt i fallet, kommer funktionen VLOOKUP att returnera det första elementet som hittas, oavsett skiftläge.
Beslut: Använd en annan Excel-funktion som kan utföra en vertikal sökning (LOOKUP, SUMPRODUCT, INDEX och MATCH) i kombination med EXAKTEtt som särskiljer fall. För mer information kan du lära dig av lektionen – 4 sätt att göra VLOOKUP skiftlägeskänslig i Excel.
2. VLOOKUP returnerar det första värdet som hittas
Som du redan vet, VPR returnerar värdet från den givna kolumnen som motsvarar den första matchningen som hittades. Du kan dock låta den extrahera 2:an, 3:an, 4:an eller någon annan upprepning av det värde du vill ha. Om du behöver extrahera alla dubbletter av värden behöver du en kombination av funktioner INDEX (INDEX), MINST (LITEN) och LINJE (RAD).
3. En kolumn lades till eller togs bort i tabellen
Tyvärr formlerna VPR sluta fungera varje gång en ny kolumn läggs till eller tas bort i uppslagstabellen. Detta händer eftersom syntaxen VPR kräver att du anger hela intervallet för sökningen och det specifika kolumnnumret för dataextraktion. Naturligtvis ändras både det givna intervallet och kolumnnumret när du tar bort en kolumn eller infogar en ny.
Beslut: Och återigen har funktioner bråttom att hjälpa INDEX (INDEX) och MER EXPONERAD (MATCH). I formeln INDEX+MATCH Du definierar separat sök- och hämtningskolumner, och som ett resultat kan du ta bort eller infoga så många kolumner du vill utan att behöva oroa dig för att behöva uppdatera alla relaterade sökformler.
4. Cellreferenser är förvrängda när en formel kopieras
Denna rubrik förklarar problemets kärna uttömmande, eller hur?
Beslut: Använd alltid absoluta cellreferenser (med symbolen $) på poster intervallet, till exempel $ A $ 2: $ C $ 100 or $ A: $ C. I formelfältet kan du snabbt byta länktyp genom att klicka F4.
VLOOKUP – arbetar med funktionerna IFERROR och ISERROR
Om du inte vill skrämma användare med felmeddelanden # N / A, #VÄRDE! or # NAMN?, kan du visa en tom cell eller ditt eget meddelande. Du kan göra detta genom att placera VPR till en funktion FEL (IFERROR) i Excel 2013, 2010 och 2007 eller använd en massa funktioner OM+FEL (OM+FEL) i tidigare versioner.
VLOOKUP: arbetar med IFERROR-funktionen
Funktionssyntax FEL (IFERROR) är enkel och talar för sig själv:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Det vill säga, för det första argumentet infogar du värdet som ska kontrolleras för ett fel, och för det andra argumentet anger du vad som ska returneras om ett fel hittas.
Till exempel returnerar den här formeln en tom cell om värdet du letar efter inte hittas:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Om du vill visa ditt eget meddelande istället för funktionens standardfelmeddelande VPR, sätt det inom citattecken, så här:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: arbetar med ISERROR-funktionen
Sedan funktionen FEL dök upp i Excel 2007, när du arbetar i tidigare versioner måste du använda kombinationen IF (IF) och EOSHIBKA (ISERROR) så här:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Till exempel formeln OM+FEL+SÖK UPP, liknande formeln IFERROR+VLOOKUPvisat ovan:
=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 är allt för idag. Jag hoppas att denna korta handledning hjälper dig att hantera alla möjliga misstag. VPR och få dina formler att fungera korrekt.