Hoppa till huvudinnehåll

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

🤖 Kutools AI Aide: Revolutionera dataanalys baserat på: Intelligent utförande   |  Generera kod  |  Skapa anpassade formler  |  Analysera data och generera diagram  |  Anropa Kutools funktioner.
Populära funktioner: Hitta, markera eller identifiera dubbletter  |  Ta bort tomma rader  |  Kombinera kolumner eller celler utan att förlora data  |  Rund utan formel ...
Super VLookup: Flera kriterier  |  Multipelt värde  |  Över flera ark  |  Fuzzy Lookup...
Adv. Rullgardinslista: Enkel rullgardinslista  |  Beroende rullgardinslista  |  Flervals-rullgardinslista...
Kolumnhanterare: Lägg till ett specifikt antal kolumner  |  Flytta kolumner  |  Växla synlighetsstatus för dolda kolumner  Jämför kolumner med Välj Samma och olika celler ...
Utvalda funktioner: Rutnätsfokus  |  Designvy  |  Stor formelbar  |  Arbetsbok & Bladhanterare | Resursbibliotek (Automatisk text)  |  Datumväljare  |  Kombinera arbetsblad  |  Kryptera/Dekryptera celler  |  Skicka e-postmeddelanden efter lista  |  Superfilter  |  Specialfilter (filtrera fet/kursiv/genomstruken...) ...
Topp 15 verktygssatser12 text verktyg (lägga till text, Ta bort tecken ...)  |  50+ Diagram Typer (Gantt Chart ...)  |  40+ Praktiskt Formler (Beräkna ålder baserat på födelsedag ...)  |  19 Införande verktyg (Infoga QR-kod, Infoga bild från sökväg ...)  |  12 Konvertering verktyg (Siffror till ord, Valutaväxling ...)  |  7 Slå ihop och dela verktyg (Avancerade kombinera rader, Dela upp Excel-celler ...)  |  ... och mer

Kutools för Excel har över 300 funktioner, Se till att det du behöver bara är ett klick bort...

Beskrivning


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 visar och redigerar flera dokument.
  • Ger effektiva flikar till Office (inklusive Excel), precis som Chrome, Edge och Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations