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 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.
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.
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.
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
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 -funktionen söker efter ett specifikt värde i ett cellintervall och returnerar värdets relativa position.
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.
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 (?).
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 för Excel har över 300 funktioner, Se till att det du behöver bara är ett klick bort...
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.