Note: The other languages of the website are Google-translated. Back to English
English English

Hitta saknade värden

Det finns fall då du behöver jämföra två listor för att kontrollera om ett värde på lista A finns i lista B i Excel. Du har till exempel en lista med produkter och du vill kontrollera om produkterna i din lista finns i produktlistan som tillhandahålls av din leverantör. För att utföra denna uppgift, listade vi här tre sätt nedan, välj gärna det du gillar.

hitta saknade värden 1

Hitta saknade värden med MATCH, ISNA och IF
Hitta saknade värden med VLOOKUP, ISNA och IF
Hitta saknade värden med COUNTIF och IF


Hitta saknade värden med MATCH, ISNA och IF

Att få reda på om alla produkter i din lista finns i din leverantörs lista som visas i skärmdumpen ovan kan du först använda MATCH-funktionen för att hämta positionen för en produkt i din lista (värdet på lista A) i leverantörens lista (lista B). MATCH returnerar #N/A-felet när en produkt inte hittas. Sedan kan du mata resultatet till ISNA för att konvertera #N/A-felen till TRUEs, vilket betyder att dessa produkter saknas. OM-funktionen kommer då att returnera det resultat du förväntar dig.

Generisk syntax

=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"Missing","Found")

√ Notera: Du kan ändra "Saknas", "Found" till alla värden som du behöver.

  • lookup_value: Värdet MATCH som används för att hämta sin position om det finns i uppslagningsområde eller #N/A fel om inte. Här hänvisar till produkterna i din lista.
  • lookup_range: Utbudet av celler att jämföra med letauppvärde. Här avses leverantörens produktlista.

Att få reda på om alla produkter i din lista finns i din leverantörs lista, vänligen kopiera eller ange formeln nedan i cellen H6 och tryck på ange för att få resultatet:

=OM(ISNA(MATCH(30002,$ B $ 6: $ B $ 10,0)),"Saknas","Hittad")

Eller använd en cellreferens för att göra formeln dynamisk:

=OM(ISNA(MATCH(G6,$ B $ 6: $ B $ 10,0)),"Saknas","Hittad")

√ Notera: Dollartecknen ($) ovan indikerar absoluta referenser, vilket betyder uppslagningsområde i formeln kommer inte att ändras när du flyttar eller kopierar formeln till andra celler. Det finns dock inga dollartecken lagt till letauppvärde eftersom du vill att den ska vara dynamisk. När du har angett formeln, dra fyllningshandtaget nedåt för att tillämpa formeln på cellerna nedan.

hitta saknade värden 2

Förklaring av formeln

Här använder vi formeln nedan som ett exempel:

=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Missing","Found")

  • MATCH(G8,$B$6:$B$10,0): Matchningstypen 0 tvingar MATCH-funktionen att returnera ett numeriskt värde som indikerar positionen för den första matchningen av 3004, värdet i cellen G8, i arrayen $ B $ 6: $ B $ 10. Men i det här fallet kunde MATCH inte hitta värdet i uppslagsmatrisen, så den kommer att returnera # N / A fel.
  • ISNA(MATCH(G8,$B$6:$B$10,0)) = ISNA(# N / A): ISNA arbetar för att ta reda på om ett värde är "#N/A" fel eller inte. Om ja, kommer funktionen att returnera TURE; Om värdet är något annat än "#N/A"-felet kommer det att returnera FALSE. Så denna ISNA-formel kommer tillbaka TURE.
  • OM(ISNA(MATCH(G8,$B$6:$B$10,0))"Saknad","Found") = OM(SANN"Saknad","Found"): OM-funktionen kommer att returnera Saknas om jämförelsen som gjorts av ISNA och MATCH är TRUE, annars returneras Found. Så formeln kommer tillbaka Saknas.

Hitta saknade värden med VLOOKUP, ISNA och IF

För att ta reda på om alla produkterna i din lista finns i din leverantörs lista kan du ersätta MATCH-funktionen ovan med VLOOKUP, eftersom den fungerar på samma sätt som MATCH att den returnerar #N/A-felet om värdet inte finns i en annan lista, eller så säger vi att den saknas.

Generisk syntax

=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"Missing","Found")

√ Notera: Du kan ändra "Saknas", "Found" till alla värden som du behöver.

  • lookup_value: Värdet VLOOKUP används för att hämta sin position om det finns i uppslagningsområde eller #N/A fel om inte. Här hänvisar till produkterna i din lista.
  • lookup_range: Utbudet av celler att jämföra med letauppvärde. Här avses leverantörens produktlista.

För att ta reda på om alla produkterna i din lista finns i din leverantörs lista, kopiera eller skriv in formeln nedan i cellen H6 och tryck på ange för att få resultatet:

=OM(ISNA(SÖKUP(30002,$ B $ 6: $ B $ 10,1,FALSE)),"Saknas","Hittad")

Eller använd en cellreferens för att göra formeln dynamisk:

=OM(ISNA(SÖKUP(G6,$ B $ 6: $ B $ 10,1,FALSE)),"Saknas","Hittad")

√ Notera: Dollartecknen ($) ovan indikerar absoluta referenser, vilket betyder uppslagningsområde i formeln kommer inte att ändras när du flyttar eller kopierar formeln till andra celler. Det finns dock inga dollartecken lagt till letauppvärde eftersom du vill att den ska vara dynamisk. När du har angett formeln, dra fyllningshandtaget nedåt för att tillämpa formeln på cellerna nedan.

hitta saknade värden 3

Förklaring av formeln

Här använder vi formeln nedan som ett exempel:

=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")

  • SÖKUPPSÖKNING(G8,$B$6:$B$10,1,FALSK): Range_lookup FALSK tvingar funktionen VLOOKUP att slå upp och returnera det värde som exakt matchar 3004, värdet i cellen G8. Om lookup_value 3004 finns i 1st kolumnen i arrayen $ B $ 6: $ B $ 10, VLOOKUP returnerar det värdet; Annars kommer det att returnera felvärdet #N/A. Här finns inte 3004 i arrayen, så resultatet skulle bli # N / A.
  • ISNA(SÖKUPPSÖKNING(G8,$B$6:$B$10,1,FALSK)) = ISNA(# N / A): ISNA arbetar för att ta reda på om ett värde är "#N/A" fel eller inte. Om ja, kommer funktionen att returnera TURE; Om värdet är något annat än "#N/A"-felet kommer det att returnera FALSE. Så denna ISNA-formel kommer tillbaka TURE.
  • OM(ISNA(SÖKUPPSÖKNING(G8,$B$6:$B$10,1,FALSK))"Saknad","Found") = OM(SANN"Saknad","Found"): OM-funktionen kommer att returnera Saknas om jämförelsen som gjorts av ISNA och VLOOKUP är TRUE, annars kommer den att returnera Found. Så formeln kommer tillbaka Saknas.

Hitta saknade värden med COUNTIF och IF

För att ta reda på om alla produkter i din lista finns i din leverantörs lista kan du använda en enklare formel med funktionerna COUNTIF och IF. Formeln drar fördel av det faktum att Excel kommer att utvärdera alla tal utom noll (0) som TRUE. Så om ett värde finns i en annan lista kommer COUNTIF-funktionen att returnera antalet förekomster i den listan, då tar IF talet som en TURE; Om värdet inte finns i listan kommer COUNTIF-funktionen att returnera 0 och IF tar det som FALSE.

Generisk syntax

=IF(COUNTIF("lookup_range",lookup_value),"Found","Missing")

√ Notera: Du kan ändra "Found", "Saknad" till valfria värden som du behöver.

  • lookup_range: Utbudet av celler att jämföra med letauppvärde. Här avses leverantörens produktlista.
  • lookup_value: Värdet COUNTIF som används för att returnera antalet förekomster i uppslagningsområde. Här hänvisar till produkterna i din lista.

För att ta reda på om alla produkterna i din lista finns i din leverantörs lista, kopiera eller skriv in formeln nedan i cellen H6 och tryck på ange för att få resultatet:

=OM(ANTALOM($ B $ 6: $ B $ 10,30002),"Hittad","Saknad")

Eller använd en cellreferens för att göra formeln dynamisk:

=OM(ANTALOM($ B $ 6: $ B $ 10,G6),"Hittad","Saknad")

√ Notera: Dollartecknen ($) ovan indikerar absoluta referenser, vilket betyder uppslagningsområde i formeln kommer inte att ändras när du flyttar eller kopierar formeln till andra celler. Det finns dock inga dollartecken lagt till letauppvärde eftersom du vill att den ska vara dynamisk. När du har angett formeln, dra fyllningshandtaget nedåt för att tillämpa formeln på cellerna nedan.

hitta saknade värden 4

Förklaring av formeln

Här använder vi formeln nedan som ett exempel:

=IF(COUNTIF($B$6:$B$10,G8),"Found","Missing")

  • ANTALIF($B$6:$B$10,G8): Funktionen COUNTIF räknar hur många gånger som gör det 3004, värdet i cellen G8, visas i arrayen $ B $ 6: $ B $ 10. Tydligen finns inte 3004 i arrayen, så resultatet skulle bli 0.
  • OM(ANTALIF($B$6:$B$10,G8)"Found","Saknad") = OM(0"Found","Saknad"): OM-funktionen kommer att utvärdera 0 som FALSK. Så formeln kommer tillbaka Saknas, värdet som ska returneras när den första ökningen utvärderas till FALSE.

Relaterade funktioner

Excel IF-funktion

IF-funktionen är en av de enklaste och mest användbara funktionerna i Excel-arbetsboken. Det utför ett enkelt logiskt test som beror på jämförelseresultatet och returnerar ett värde om ett resultat är SANT, eller ett annat värde om resultatet är FALSKT.

Excel MATCH-funktion

Excel MATCH -funktionen söker efter ett specifikt värde i ett cellintervall och returnerar värdets relativa position.

Excel VLOOKUP-funktion

Excel VLOOKUP-funktionen söker efter ett värde genom att matcha i den första kolumnen i en tabell och returnerar motsvarande värde från en viss kolumn i samma rad.

Excel COUNTIF-funktion

Funktionen COUNTIF är en statistisk funktion i Excel som används för att räkna antalet celler som uppfyller ett kriterium. Den stöder logiska operatorer (<>, =, > och <), och jokertecken (? och *) för partiell matchning.


Relaterade formler

Leta upp ett värde som innehåller specifik text med jokertecken

För att hitta den första matchningen som innehåller en viss textsträng i ett intervall i Excel kan du använda en INDEX- och MATCH-formel med jokertecken - asterisken (*) och frågetecken (?).

Delvis matchning med VLOOKUP

Det finns tillfällen då du behöver Excel för att hämta data baserat på partiell information. För att lösa problemet kan du använda en VLOOKUP-formel tillsammans med jokertecken - asterisken (*) och frågetecken (?).

Ungefärlig matchning med INDEX och MATCH

Det finns tillfällen då vi behöver hitta ungefärliga matchningar i Excel för att utvärdera medarbetares prestanda, betygsätta elevernas poäng, beräkna porto baserat på vikt, etc. I denna handledning kommer vi att prata om hur man använder INDEX- och MATCH -funktionerna för att hämta resultat vi behöver.

Sök upp närmaste matchningsvärde med flera kriterier

I vissa fall kan du behöva leta upp närmaste eller ungefärliga matchningsvärde baserat på mer än ett kriterium. Med kombinationen av INDEX-, MATCH- och IF -funktioner kan du snabbt få det gjort i Excel.


De bästa Office-produktivitetsverktygen

Kutools för Excel - Hjälper dig att sticka ut från mängden

Vill du slutföra ditt dagliga arbete snabbt och perfekt? Kutools för Excel ger 300 kraftfulla avancerade funktioner (kombinera arbetsböcker, summera efter färg, dela cellinnehåll, konvertera datum och så vidare ...) och spara 80% tid åt dig.

  • Designad för 1500 arbetsscenarier, hjälper dig att lösa 80% Excel-problem.
  • Minska tusentals tangentbord och musklick varje dag, lindra dina trötta ögon och händer.
  • Bli en expert på 3 minuter. Behöver inte längre komma ihåg några smärtsamma formler och VBA-koder.
  • 30 dagars obegränsad gratis provperiod. 60-dagars pengarna tillbaka-garanti. Gratis uppgradering och support i 2 år.
Ribbon of Excel (med Kutools för Excel installerat)

Fliken Office - Aktivera läsning och redigering av flikar i Microsoft Office (inkluderar Excel)

  • En sekund att växla mellan dussintals öppna dokument!
  • Minska hundratals musklick åt dig varje dag, säg adjö till mushanden.
  • Ökar din produktivitet med 50% när du tittar på och redigerar flera dokument.
  • Ger effektiva flikar till Office (inkluderar Excel), precis som Chrome, Firefox och ny Internet Explorer.
Skärmdump av Excel (med Office-fliken installerad)
Sortera kommentarer efter
Kommentarer (0)
Inga betyg än. Bli först med att betygsätta!
Det finns inga kommentarer här ännu
Lämna dina kommentarer
Postar som gäst
×
Betygsätt detta inlägg:
0   Tecken
Föreslagna platser