Find manglende værdier
Der er tilfælde, hvor du skal sammenligne to lister for at kontrollere, om en værdi af liste A findes i liste B i Excel. Du har f.eks. en liste over produkter, og du vil tjekke, om produkterne på din liste findes i produktlisten fra din leverandør. For at udføre denne opgave har vi listet tre måder nedenfor, og du er velkommen til at vælge den, du kan lide.
Find manglende værdier med MATCH, ISNA og IF
Find manglende værdier med VLOOKUP, ISNA og IF
Find manglende værdier med COUNTIF og IF
Find manglende værdier med MATCH, ISNA og IF
At finde ud af hvis alle produkterne på din liste findes på din leverandørs liste som vist på skærmbilledet ovenfor, kan du først bruge MATCH-funktionen til at hente placeringen af et produkt på din liste (værdien af liste A) i leverandørens liste (liste B). MATCH returnerer #N/A-fejlen, når et produkt ikke findes. Derefter kan du sende resultatet til ISNA for at konvertere #N/A fejlene til TRUEs, hvilket betyder, at disse produkter mangler. HVIS-funktionen vil derefter returnere det resultat, du forventer.
Generisk syntaks
=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"Missing","Found")
√ Bemærk: Du kan ændre "Mangler", "Fundet" til alle værdier, som du har brug for.
- opslagsværdi: Værdien MATCH bruges til at hente sin position, hvis den findes i opslag_område eller #N/A fejl, hvis ikke. Her henviser til produkterne på din liste.
- opslag_range: Cellernes rækkevidde at sammenligne med opslagsværdi. Her henvises til leverandørens produktliste.
At finde ud af hvis alle produkterne på din liste findes på din leverandørs liste, kopier eller indtast formlen herunder i cellen H6, og tryk på Indtast for at få resultatet:
=HVIS(ISNA(MATCH(30002,$ B $ 6: $ B $ 10,0)),"Mangler","Fundet")
Eller brug en cellereference til at gøre formlen dynamisk:
=HVIS(ISNA(MATCH(G6,$ B $ 6: $ B $ 10,0)),"Mangler","Fundet")
√ Bemærk: Dollartegnene ($) ovenfor angiver absolutte referencer, hvilket betyder opslag_område i formlen ændres ikke, når du flytter eller kopierer formlen til andre celler. Der er dog ingen dollartegn tilføjet til opslagsværdi da du vil have det dynamisk. Når du har indtastet formlen, skal du trække udfyldningshåndtaget ned for at anvende formlen på nedenstående celler.
Forklaring af formlen
Her bruger vi formlen herunder som et eksempel:
=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Missing","Found")
- MATCH(G8,$B$6:$B$10,0): Match_type 0 tvinger MATCH-funktionen til at returnere en numerisk værdi, der angiver positionen for den første match af 3004, værdien i cellen G8, i arrayet $ B $ 6: $ B $ 10. Men i dette tilfælde kunne MATCH ikke finde værdien i opslagsarrayet, så det vil returnere # N / A fejl.
- ISNA(MATCH(G8,$B$6:$B$10,0)) = ISNA(# N / A): ISNA arbejder for at finde ud af, om en værdi er "#N/A" fejl eller ej. Hvis ja, vil funktionen returnere TURE; Hvis værdien er andet end "#N/A" fejl, vil den returnere FALSK. Så denne ISNA-formel vender tilbage TUR.
- HVIS(ISNA(MATCH(G8,$B$6:$B$10,0))"Mangler","Fundet") = HVIS(TRUE"Mangler","Fundet"): HVIS-funktionen vil returnere Mangler, hvis sammenligningen foretaget af ISNA og MATCH er TRUE, ellers vil den returnere Fundet. Så formlen vender tilbage Manglende.
Find manglende værdier med VLOOKUP, ISNA og IF
For at finde ud af, om alle produkterne på din liste findes på din leverandørs liste, kan du erstatte MATCH-funktionen ovenfor med VLOOKUP, da den fungerer på samme måde som MATCH, at den returnerer #N/A-fejlen, hvis værdien ikke findes i en anden liste, eller vi siger, at den mangler.
Generisk syntaks
=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"Missing","Found")
√ Bemærk: Du kan ændre "Mangler", "Fundet" til alle værdier, som du har brug for.
- opslagsværdi: Værdien VLOOKUP bruges til at hente sin position, hvis den findes i opslag_område eller #N/A fejl, hvis ikke. Her henviser til produkterne på din liste.
- opslag_range: Cellernes rækkevidde at sammenligne med opslagsværdi. Her henvises til leverandørens produktliste.
For at finde ud af, om alle produkterne på din liste findes på din leverandørs liste, skal du kopiere eller indtaste formlen nedenfor i cellen H6 og trykke på Indtast for at få resultatet:
=HVIS(ISNA(OPSLAG(30002,$ B $ 6: $ B $ 10,1,FALSK)),"Mangler","Fundet")
Eller brug en cellereference til at gøre formlen dynamisk:
=HVIS(ISNA(OPSLAG(G6,$ B $ 6: $ B $ 10,1,FALSK)),"Mangler","Fundet")
√ Bemærk: Dollartegnene ($) ovenfor angiver absolutte referencer, hvilket betyder opslag_område i formlen ændres ikke, når du flytter eller kopierer formlen til andre celler. Der er dog ingen dollartegn tilføjet til opslagsværdi da du vil have det dynamisk. Når du har indtastet formlen, skal du trække udfyldningshåndtaget ned for at anvende formlen på nedenstående celler.
Forklaring af formlen
Her bruger vi formlen herunder som et eksempel:
=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")
- OPSLAG(G8,$B$6:$B$10,1,FALSK): Range_lookup FALSK tvinger VLOOKUP-funktionen til at slå op og returnere den værdi, der matcher nøjagtigt 3004, værdien i cellen G8. Hvis opslagsværdien 3004 findes i 1st kolonne i arrayet $ B $ 6: $ B $ 10, vil VLOOKUP returnere denne værdi; Ellers returnerer den #N/A fejlværdien. Her eksisterer 3004 ikke i arrayet, så resultatet ville være # N / A.
- ISNA(OPSLAG(G8,$B$6:$B$10,1,FALSK)) = ISNA(# N / A): ISNA arbejder for at finde ud af, om en værdi er "#N/A" fejl eller ej. Hvis ja, vil funktionen returnere TURE; Hvis værdien er andet end "#N/A" fejl, vil den returnere FALSK. Så denne ISNA-formel vender tilbage TUR.
- HVIS(ISNA(OPSLAG(G8,$B$6:$B$10,1,FALSK))"Mangler","Fundet") = HVIS(TRUE"Mangler","Fundet"): HVIS-funktionen vil returnere Mangler, hvis sammenligningen foretaget af ISNA og VLOOKUP er TRUE, ellers vil den returnere Fundet. Så formlen vender tilbage Manglende.
Find manglende værdier med COUNTIF og IF
For at finde ud af, om alle produkterne på din liste findes på din leverandørs liste, kan du bruge en enklere formel med funktionerne COUNTIF og IF. Formlen udnytter det faktum, at Excel vil evaluere ethvert tal undtagen nul (0) som SAND. Så hvis der findes en værdi i en anden liste, vil COUNTIF-funktionen returnere antallet af forekomster i den liste, så tager IF tallet som en TURE; Hvis værdien ikke findes på listen, vil COUNTIF-funktionen returnere 0, og HVIS vil tage den som FALSK.
Generisk syntaks
=IF(COUNTIF("lookup_range",lookup_value),"Found","Missing")
√ Bemærk: Du kan ændre "Fundet", "Mangler" til alle værdier, som du har brug for.
- opslag_range: Cellernes rækkevidde at sammenligne med opslagsværdi. Her henvises til leverandørens produktliste.
- opslagsværdi: Værdien COUNTIF bruges til at returnere antallet af dets forekomster i opslag_område. Her henviser til produkterne på din liste.
For at finde ud af, om alle produkterne på din liste findes på din leverandørs liste, skal du kopiere eller indtaste formlen nedenfor i cellen H6 og trykke på Indtast for at få resultatet:
=HVIS(ANTALHVIS($ B $ 6: $ B $ 10,30002),"Fundet","Mangler")
Eller brug en cellereference til at gøre formlen dynamisk:
=HVIS(ANTALHVIS($ B $ 6: $ B $ 10,G6),"Fundet","Mangler")
√ Bemærk: Dollartegnene ($) ovenfor angiver absolutte referencer, hvilket betyder opslag_område i formlen ændres ikke, når du flytter eller kopierer formlen til andre celler. Der er dog ingen dollartegn tilføjet til opslagsværdi da du vil have det dynamisk. Når du har indtastet formlen, skal du trække udfyldningshåndtaget ned for at anvende formlen på nedenstående celler.
Forklaring af formlen
Her bruger vi formlen herunder som et eksempel:
=IF(COUNTIF($B$6:$B$10,G8),"Found","Missing")
- ANTAL.HVIS($B$6:$B$10,G8): Funktionen COUNTIF tæller, hvor mange gange der gør 3004, værdien i cellen G8, vises i arrayet $ B $ 6: $ B $ 10. Tilsyneladende eksisterer 3004 ikke i arrayet, så resultatet ville være 0.
- HVIS(ANTAL.HVIS($B$6:$B$10,G8)"Fundet","Mangler") = HVIS(0"Fundet","Mangler"): HVIS-funktionen vil vurdere 0 som FALSK. Så formlen vender tilbage Manglende, den værdi, der skal returneres, når den første forøgelse evalueres til FALSK.
Relaterede funktioner
IF-funktionen er en af de enkleste og mest nyttige funktioner i Excel-projektmappe. Den udfører en simpel logisk test, som afhænger af sammenligningsresultatet, og den returnerer en værdi, hvis et resultat er SAND, eller en anden værdi, hvis resultatet er FALSK.
Funktionen Excel MATCH søger efter en bestemt værdi i et celleområde og returnerer værdiens relative position.
Excel VLOOKUP-funktionen søger efter en værdi ved at matche den første kolonne i en tabel og returnerer den tilsvarende værdi fra en bestemt kolonne i samme række.
COUNTIF-funktionen er en statistisk funktion i Excel, som bruges til at tælle antallet af celler, der opfylder et kriterium. Det understøtter logiske operatorer (<>, =, > og <), og jokertegnene (? og *) til delvis matchning.
Relaterede formler
Slå en værdi, der indeholder specifik tekst, med jokertegn
For at finde det første match, der indeholder en bestemt tekststreng i et område i Excel, kan du bruge en INDEX- og MATCH-formel med jokertegn - stjernen (*) og spørgsmålstegn (?).
Der er tidspunkter, hvor du har brug for Excel til at hente data baseret på delvise oplysninger. For at løse problemet kan du bruge en VLOOKUP-formel sammen med jokertegn - stjernen (*) og spørgsmålstegn (?).
Omtrentlig match med INDEX og MATCH
Der er tidspunkter, hvor vi skal finde omtrentlige kampe i Excel for at evaluere medarbejdernes præstationer, bedømme elevernes score, beregne porto baseret på vægt osv. I denne vejledning vil vi tale om, hvordan man bruger INDEX- og MATCH -funktionerne til at hente resultater, vi har brug for.
Opslag nærmeste matchværdi med flere kriterier
I nogle tilfælde skal du muligvis søge den nærmeste eller omtrentlige matchværdi baseret på mere end ét kriterium. Med kombinationen af INDEX-, MATCH- og IF -funktioner kan du hurtigt få det gjort i Excel.
De bedste Office-produktivitetsværktøjer
Kutools til Excel - Hjælper dig med at skille dig ud fra mængden
Kutools til Excel kan prale af over 300 funktioner, At sikre, at det, du har brug for, kun er et klik væk...
Fanen Office - Aktiver læsning og redigering af faner i Microsoft Office (inkluderer Excel)
- Et sekund til at skifte mellem snesevis af åbne dokumenter!
- Reducer hundreder af museklik for dig hver dag, og farvel med musens hånd.
- Øger din produktivitet med 50 %, når du ser og redigerer flere dokumenter.
- Giver effektive faner til Office (inkluderer Excel), ligesom Chrome, Edge og Firefox.
