Tæl manglende værdier
Hvis du antager, at du har to lister, for at tælle det samlede antal værdier på en liste, som ikke findes på en anden liste, kan du bruge en SUMPRODUCT formel ved hjælp af MATCH og ISNA-funktioner eller TÆL.HVIS funktion.
Tæl manglende værdier med SUMPRODUCT, MATCH og ISNA
Tæl manglende værdier med SUMPRODUCT og COUNTIF
Tæl manglende værdier med SUMPRODUCT, MATCH og ISNA
At tælle det samlede antal værdier på liste B, der mangler på liste A som vist ovenfor, kan du først bruge MATCH-funktionen til at returnere en matrix af den relative placering af værdierne fra liste B i liste A. Hvis en værdi ikke findes i liste A, vil en #N/A fejl blive returneret. ISNA-funktionen vil derefter identificere #N/A fejlene, og SUMPRODUCT vil tælle det samlede antal fejl.
Generisk syntaks
=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))
- range_to_count: Det interval, hvorfra man tæller manglende værdier. Her refererer til liste B.
- opslag_range: Udvalget at sammenligne med range_to_count. Her refererer til listen A.
- 0: match_type 0 tvinger MATCH til at udføre et nøjagtigt match.
At tælle det samlede antal værdier på liste B, der mangler på liste A, kopier eller indtast formlen herunder i cellen H6, og tryk på Indtast for at få resultatet:
=SUMPRODUKT(--ISNA(MATCH(F6: F8,B6: B10,0)))
Forklaring af formlen
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
- MATCH(F6:F8;B6:B10,0;XNUMX): match_type 0 tvinger MATCH-funktionen til at returnere numeriske værdier, der angiver de relative positioner af værdierne gennem celler F6 til F8 i rækken B6: B10. Hvis en værdi ikke findes i liste A, vil en #N/A fejl blive returneret. Så resultaterne vil være i et array som dette: {2;3;#N/A}.
- ISNA(MATCH(F6:F8;B6:B10,0;XNUMX)) = ISNA({2;3;#N/A}): ISNA arbejder på at finde ud af, om en værdi er en "#N/A" fejl eller ej. Hvis ja, vil funktionen returnere en TURE; Hvis ikke, vil det returnere en FALSK. Så ISNA-formlen vender tilbage {FALSE;FALSE;TRUE}.
- SUMPRODUKT(--ISNA(MATCH(F6:F8;B6:B10,0;XNUMX))) = SUMPRODUKT(--{FALSE;FALSE;TRUE}): Det dobbelte negative fortegn konverterer TRUEs til 1s og FALSEs til 0s: {0; 1; 0}. Så returnerer funktionen SUMPRODUKT summen: 1.
Tæl manglende værdier med SUMPRODUCT og COUNTIF
At tælle det samlede antal værdier på liste B, der mangler på liste A, kan du også bruge COUNTIF-funktionen til at fortælle, om en værdi findes i liste A eller ej med betingelsen "=0", da et 0 vil generere, hvis en værdi mangler. SUMPRODUKTET vil derefter tælle det samlede antal manglende værdier.
Generisk syntaks
=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))
- opslag_range: Udvalget at sammenligne med range_to_count. Her refererer til listen A.
- range_to_count: Det interval, hvorfra man tæller manglende værdier. Her refererer til liste B.
- 0: match_type 0 tvinger MATCH til at udføre et nøjagtigt match.
At tælle det samlede antal værdier på liste B, der mangler på liste A, kopier eller indtast formlen herunder i cellen H6, og tryk på Indtast for at få resultatet:
=SUMPRODUKT(--(ANTALHVIS(B6: B10,F6: F8)=0))
Forklaring af formlen
=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))
- ANTAL.HVIS(B6:B10;F6:F8): Funktionen COUNTIF tæller antallet af cellers forekomst igennem F6 til F8 i rækken B6: B10. Resultatet vil være i et array som dette: {1; 1; 0}.
- - (ANTAL.HVIS(B6:B10;F6:F8)= 0) = - ({1; 1; 0}= 0): Uddraget {1;1;0}=0 giver en SAND og FALSK matrix {FALSE;FALSE;TRUE}. Det dobbelte negative fortegn ændrer derefter TRUEs til 1s og FALSEs til 0s. Det endelige array er sådan her: {0; 0; 1}.
- SUMPRODUKT(- (ANTAL.HVIS(B6:B10;F6:F8)= 0)) = SUMPRODUKT({0; 0; 1}): SUMPRODUKTET returnerer derefter summen: 1.
Relaterede funktioner
I Excel kan SUMPRODUCT-funktionen bruges til at gange to eller flere kolonner eller arrays sammen og derefter få summen af produkter. Faktisk er SUMPRODUCT en nyttig funktion, som kan hjælpe med at tælle eller summere celleværdier med flere kriterier som COUNTIFS eller SUMIFS-funktionen. Denne artikel introducerer funktionssyntaxen og nogle eksempler på denne SUMPRODUCT-funktion.
Funktionen Excel MATCH søger efter en bestemt værdi i et celleområde og returnerer værdiens relative position.
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
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.
Denne artikel vil fokusere på Excel-formlerne til optælling af celler, der er nøjagtigt lig med en tekststreng, du angiver, eller delvis svarer til den givne tekststreng som vist nedenfor. For det første vil det forklare formelsyntaks og argument, det giver eksempler, som du bedre kan forstå.
Tæl antal celler ikke mellem to givne tal
At tælle antallet af celler mellem to tal er en almindelig opgave for os i Excel, men i visse tilfælde vil du måske tælle cellerne ikke mellem to givne tal. For eksempel har jeg en produktliste med salget fra mandag til søndag, nu skal jeg hente antallet af celler, der ikke er mellem de specifikke lave og høje tal som vist nedenfor. I denne artikel vil jeg introducere nogle formler til håndtering af denne opgave 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.