Excel har många funktioner där en användare måste ange ett eller flera kriterier för att få resultatet. Till exempel, om du vill räkna celler baserat på flera kriterier, kan du använda funktionerna RÄTTA eller RÄTTA i Excel.
Denna handledning täcker olika sätt att använda ett eller flera kriterier i funktionen COUNTIF och COUNTIFS i Excel.
Medan jag främst kommer att fokusera på COUNTIF- och COUNTIFS -funktioner i den här självstudien, kan alla dessa exempel också användas i andra Excel -funktioner som tar flera kriterier som inmatningar (som SUMIF, SUMIFS, AVERAGEIF och AVERAGEIFS).
En introduktion till Excel COUNTIF och COUNTIFS -funktioner
Låt oss först få grepp om att använda funktionerna COUNTIF och COUNTIFS i Excel.
Excel COUNTIF -funktion (tar enstaka kriterier)
Excel COUNTIF -funktionen är bäst lämpad för situationer när du vill räkna celler baserat på ett enda kriterium. Om du vill räkna baserat på flera kriterier, använd funktionen RÄTTA.
Syntax
= COUNTIF (intervall, kriterier)
Inmatningsargument
- intervall - intervallet av celler som du vill räkna.
- kriterier - kriterierna som måste utvärderas mot cellintervallet för att en cell ska räknas.
Excel COUNTIFS -funktion (tar flera kriterier)
Excel COUNTIFS -funktionen är bäst lämpad för situationer när du vill räkna celler baserat på flera kriterier.
Syntax
= COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2]…)
Inmatningsargument
- kriterier_område1 - Det cellintervall som du vill utvärdera mot kriterier1.
- kriterier1 - kriterierna som du vill utvärdera för criteria_range1 för att avgöra vilka celler som ska räknas.
- [criteria_range2] - Det cellintervall som du vill utvärdera mot kriterier2.
- [kriterier2] - de kriterier som du vill utvärdera för criteria_range2 för att avgöra vilka celler som ska räknas.
Låt oss nu titta på några exempel på att använda flera kriterier i COUNTIF -funktioner i Excel.
Använda NUMBER -kriterier i Excel COUNTIF -funktioner
#1 Räkna celler när kriterier är lika med ett värde
För att få antalet celler där kriterie -argumentet är lika med ett visst värde kan du antingen ange kriterierna direkt eller använda cellreferensen som innehåller kriterierna.
Nedan följer ett exempel där vi räknar cellerna som innehåller siffran 9 (vilket innebär att kriterie -argumentet är lika med 9). Här är formeln:
= RÄKTA ($ B $ 2: $ B $ 11, D3)
I exemplet ovan (på bilden) är kriterierna i cell D3. Du kan också ange kriterierna direkt i formeln. Till exempel kan du också använda:
= RÄKTA ($ B $ 2: $ B $ 11,9)
#2 Räkna celler när kriterierna är större än ett värde
För att få antalet celler med ett värde större än ett angivet värde använder vi operatorn större än "(">). Vi kan antingen använda den direkt i formeln eller använda en cellreferens som har kriterierna.
När vi använder en operatör i kriterier i Excel måste vi lägga den inom dubbla citattecken. Till exempel, om kriterierna är större än 10, måste vi ange "> 10" som kriterier (se bild nedan):
Här är formeln:
= RÄKTA ($ B $ 2: $ B $ 11, ”> 10 ″)
Du kan också ha kriterierna i en cell och använda cellreferensen som kriterier. I det här fallet behöver du INTE sätta kriterierna i dubbla citattecken:
= RÄKTA ($ B $ 2: $ B $ 11, D3)
Det kan också finnas ett fall när du vill att kriterierna ska finnas i en cell, men inte vill ha det med operatören. Till exempel kanske du vill att cellen D3 ska ha siffran 10 och inte> 10.
I så fall måste du skapa ett kriterie -argument som är en kombination av operator och cellreferens (se bild nedan):
= RÄKTA ($ B $ 2: $ B $ 11, ”>” & D3)
OBS: När du kombinerar en operator och en cellreferens, är operatören alltid i dubbla citattecken. Operatören och cellreferensen förenas av ett ampersand (&).
#3 Räkna celler när kriterierna är mindre än ett värde
För att få antalet celler med ett värde mindre än ett angivet värde använder vi operatorn mindre än ("<"). Vi kan antingen använda den direkt i formeln eller använda en cellreferens som har kriterierna.
När vi använder en operatör i kriterier i Excel måste vi lägga den inom dubbla citattecken. Till exempel, om kriteriet är att antalet ska vara mindre än 5, måste vi ange “<5” som kriterier (se bild nedan):
= RÄKTA ($ B $ 2: $ B $ 11, ”<5 ″)
Du kan också ha kriterierna i en cell och använda cellreferensen som kriterier. I det här fallet behöver du INTE sätta kriterierna i dubbla citattecken (se bild nedan):
= RÄKTA ($ B $ 2: $ B $ 11, D3)
Det kan också finnas ett fall när du vill att kriterierna ska finnas i en cell, men inte vill ha det med operatören. Till exempel kanske du vill att cellen D3 ska ha siffran 5 och inte <5.
I så fall måste du skapa ett kriterie -argument som är en kombination av operator- och cellreferens:
= RÄKTA ($ B $ 2: $ B $ 11, "<" & D3)
OBS: När du kombinerar en operator och en cellreferens, är operatören alltid i dubbla citattecken. Operatören och cellreferensen förenas av ett ampersand (&).
#4 Räkna celler med flera kriterier - mellan två värden
För att få ett antal värden mellan två värden måste vi använda flera kriterier i funktionen RÄNTA.
Här är två metoder för att göra detta:
METOD 1: Använda COUNTIFS -funktionen
COUNTIFS -funktionen kan hantera flera kriterier som argument och räknar cellerna endast när alla kriterier är Sanna. För att räkna celler med värden mellan två angivna värden (säg 5 och 10) kan vi använda följande COUNTIFS -funktion:
= RÄKNINGAR ($ B $ 2: $ B $ 11, ”> 5 ″, $ B $ 2: $ B $ 11,” <10 ″)
OBS: Formeln ovan räknar inte celler som innehåller 5 eller 10. Om du vill inkludera dessa celler, använd större än lika med (> =) och mindre än lika med (<=) operatorer. Här är formeln:
= RÄKNINGAR ($ B $ 2: $ B $ 11, ”> = 5 ″, $ B $ 2: $ B $ 11,” <= 10 ″)
Du kan också ha dessa kriterier i celler och använda cellreferensen som kriterier. I det här fallet behöver du INTE sätta kriterierna i dubbla citattecken (se bild nedan):
Du kan också använda en kombination av cellreferenser och operatorer (där operatören anges direkt i formeln). När du kombinerar en operator och en cellreferens är operatören alltid i dubbla citattecken. Operatören och cellreferensen förenas av ett ampersand (&).
METOD 2: Använda två COUNTIF -funktioner
Om du har flera kriterier kan du antingen använda COUNTIFS eller skapa en kombination av COUNTIF -funktioner. Formeln nedan skulle också göra samma sak:
= COUNTIF ($ B $ 2: $ B $ 11, ”> 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
I formeln ovan hittar vi först antalet celler som har ett värde större än 5 och vi subtraherar antalet celler med ett värde större än 10. Detta skulle ge oss resultatet som 5 (vilket är antalet celler som har värden mer än 5 och mindre än lika med 10).
Om du vill att formeln ska inkludera både 5 och 10 använder du istället följande formel:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
Om du vill att formeln ska utesluta både '5' och '10' från räkningen använder du följande formel:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)-COUNTIF ($ B $ 2: $ B $ 11,10)
Du kan ha dessa kriterier i celler och använda cellreferenser, eller så kan du använda en kombination av operatorer och cellreferenser.
Använda TEXT -kriterier i Excel -funktioner
#1 Räkna celler när kriterier är lika med en specificerad text
För att räkna celler som innehåller en exakt matchning av den angivna texten kan vi helt enkelt använda den texten som kriterier. Till exempel, i datamängden (visas nedan på bilden), om jag vill räkna alla celler med namnet Joe i, kan jag använda nedanstående formel:
= RÄTTA ($ B $ 2: $ B $ 11, ”Joe”)
Eftersom detta är en textsträng måste jag sätta textkriterierna i dubbla citattecken.
Du kan också ha kriterierna i en cell och sedan använda den cellreferensen (som visas nedan):
= RÄKTA ($ B $ 2: $ B $ 11, E3)
OBS: Du kan få fel resultat om det finns ledande/bakre mellanslag i kriterierna eller kriterierna. Se till att du rensar data innan du använder dessa formler.
#2 Räkna celler när kriterier INTE är lika med en specificerad text
I likhet med vad vi såg i exemplet ovan kan du också räkna celler som inte innehåller en angiven text. För att göra detta måste vi använda inte lika med operator ().
Anta att du vill räkna alla celler som inte innehåller namnet JOE, här är formeln som gör det:
= RÄTTA ($ B $ 2: $ B $ 11, ”Joe”)
Du kan också ha kriterierna i en cell och använda cellreferensen som kriterier. I det här fallet behöver du INTE sätta kriterierna i dubbla citattecken (se bild nedan):
= RÄKTA ($ B $ 2: $ B $ 11, E3)
Det kan också finnas ett fall när du vill att kriterierna ska finnas i en cell men inte vill ha det med operatören. Till exempel kanske du vill att cellen D3 ska ha namnet Joe och inte Joe.
I så fall måste du skapa ett kriterie -argument som är en kombination av operator och cellreferens (se bild nedan):
= RÄKTA ($ B $ 2: $ B $ 11, ”” ”och E3)
När du kombinerar en operator och en cellreferens är operatören alltid i dubbla citattecken. Operatören och cellreferensen förenas av ett ampersand (&).
Använda DATE -kriterier i Excel COUNTIF- och COUNTIFS -funktioner
Datum och tid i Excel lagras som siffror. Så vi kan använda det på samma sätt som vi använder siffror.
#1 Räkna celler när kriterier är lika med ett specificerat datum
För att få antalet celler som innehåller det angivna datumet använder vi lika med operatoren (=) tillsammans med datumet.
För att använda datumet rekommenderar jag att du använder DATE -funktionen, eftersom det blir av med eventuella fel i datumvärdet. Så, till exempel, om jag vill använda datumet 1 september 2015, kan jag använda DATE -funktionen enligt nedan:
= DATUM (2015,9,1)
Denna formel skulle returnera samma datum trots regionala skillnader. Till exempel, 01-09-2015 skulle vara 1 september 2015 enligt amerikansk datum syntax och 09 januari 2015 enligt brittisk datum syntax. Denna formel skulle dock alltid återkomma 1 september 2105.
Här är formeln för att räkna antalet celler som innehåller datumet 2015-02-09:
= RÄKTA ($ A $ 2: $ A $ 11, DATUM (2015,9,2))
#2 Räkna celler när kriterier är FÖRE eller EFTER till ett specificerat datum
För att räkna celler som innehåller datum före eller efter ett visst datum kan vi använda operatörerna mindre än/större än.
Om jag till exempel vill räkna alla celler som innehåller ett datum efter den 2 september 2015 kan jag använda formeln:
= RÄKTA ($ A $ 2: $ A $ 11, ”>” & DATUM (2015,9,2))
På samma sätt kan du också räkna antalet celler före ett visst datum. Om du vill inkludera ett datum i räkningen, använd och "lika med" -operatorn tillsammans med "större än/mindre än" -operatorn.
Du kan också använda en cellreferens som innehåller ett datum. I det här fallet måste du kombinera operatören (inom dubbla citattecken) med datumet med hjälp av ett tecken (&).
Se exemplet nedan:
= RÄKTA ($ A $ 2: $ A $ 11, ”>” & F3)
#3 Räkna celler med flera kriterier - mellan två datum
För att få ett antal värden mellan två värden måste vi använda flera kriterier i funktionen RÄNTA.
Vi kan göra detta med två metoder - En enda COUNTIFS -funktion eller två COUNTIF -funktioner.
METOD 1: Använda COUNTIFS -funktionen
COUNTIFS -funktionen kan ta flera kriterier som argument och räknar cellerna endast när alla kriterier är Sanna. För att räkna celler med värden mellan två angivna datum (säg 2 september och 7 september) kan vi använda följande RÄTTA -funktion:
= RÄKNINGAR ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<“ & DATE (2015,9,7))
Ovanstående formel räknar inte celler som innehåller de angivna datumen. Om du också vill inkludera dessa datum, använd större än lika med (> =) och mindre än lika med (<=) operatörer. Här är formeln:
= RÄKNINGAR ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<=” & DATE (2015,9,7))
Du kan också ha datumen i en cell och använda cellreferensen som kriterier. I det här fallet kan du inte ha operatören med datumet i cellerna. Du måste manuellt lägga till operatörer i formeln (i dubbla citattecken) och lägga till cellreferens med hjälp av ett tecken (&). Se bilden nedan:
= RÄKNINGAR ($ A $ 2: $ A $ 11, ”>” & F3, $ A $ 2: $ A $ 11, ”<“ & G3)
METOD 2: Använda COUNTIF -funktioner
Om du har flera kriterier kan du antingen använda en COUNTIFS -funktion eller skapa en kombination av två COUNTIF -funktioner. Formeln nedan skulle också göra susen:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7))
I formeln ovan hittar vi först antalet celler som har ett datum efter den 2 september och vi subtraherar antalet celler med datum efter den 7 september. Detta skulle ge oss resultatet som 7 (vilket är antalet celler som har datum efter den 2 september och före den 7 september).
Om du inte vill att formeln ska räkna både 2 september och 7 september, använd följande formel istället:
= COUNTIF ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7))
Om du vill utesluta båda datumen från att räkna använder du följande formel:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7) -COUNTIF ($ A $ 2: $ A $ 11, DATUM (2015,9,7)))
Du kan också ha kriteriedatum i celler och använda cellreferenser (tillsammans med operatorer i dubbla citattecken sammanfogade med ampersand).
Använda WILDCARD -TECKEN i kriterier i COUNTIF & COUNTIFS -funktioner
Det finns tre jokertecken i Excel:
- * (asterisk) - Det representerar valfritt antal tecken. Ex* kan till exempel betyda excel, excel, exempel, expert, etc.
- ? (frågetecken) - Det representerar en enda karaktär. Till exempel kan Tr? Mp betyda Trump eller Tramp.
- ~ (tilde) - Det används för att identifiera ett jokertecken (~, *,?) I texten.
Du kan använda COUNTIF -funktionen med jokertecken för att räkna celler när andra inbyggda räknefunktioner misslyckas. Anta till exempel att du har en datamängd som visas nedan:
Låt oss nu ta olika exempel:
#1 Räkna celler som innehåller text
För att räkna celler med text i kan vi använda jokertecknet * (asterisk). Eftersom asterisk representerar valfritt antal tecken räknar den alla celler som har någon text i den. Här är formeln:
= RÄKNINGAR ($ C $ 2: $ C $ 11, ”*”)
Obs: Formeln ovan ignorerar celler som innehåller siffror, tomma celler och logiska värden, men skulle räkna att cellerna innehåller en apostrof (och därmed visas tomma) eller celler som innehåller tom sträng (= ””) som kan ha returnerats som en del av en formel.
Här är en detaljerad handledning om hantering av fall där det finns en tom sträng eller apostrof.
Här är en detaljerad handledning om hantering av fall där det finns tomma strängar eller apostrofer.
Nedan finns en video som förklarar olika scenarier för att räkna celler med text i.
#2 Räkna icke-tomma celler
Om du funderar på att använda COUNTA -funktionen, tänk om.
Prova det och det kan misslyckas med dig. COUNTA kommer också att räkna en cell som innehåller en tom sträng (returneras ofta med formler som = ”” eller när människor bara skriver in en apostrof i en cell). Celler som innehåller tomma strängar ser tomma ut men är inte, och räknas därmed av funktionen RÄNTA.
COUNTA kommer också att räkna en cell som innehåller en tom sträng (returneras ofta med formler som = ”” eller när människor bara skriver in en apostrof i en cell). Celler som innehåller tomma strängar ser tomma ut men är inte, och räknas därmed av COUNTA -funktionen.
Så om du använder formeln = COUNTA (A1: A11) returnerar den 11, medan den ska returnera 10.
Här är fixen:
= RÄTTA ($ A $ 1: $ A $ 11, "?*")+RÄKNING ($ A $ 1: $ A $ 11)+SUMPRODUKT (-ISLOGISK ($ A $ 1: $ A $ 11))
Låt oss förstå denna formel genom att bryta ner den:
- RÄTTA ($ N $ 8: $ N $ 18, ”?*”) - Denna del av formeln returnerar 5. Detta inkluderar alla celler som har ett texttecken i sig. A? representerar ett tecken och * representerar valfritt antal tecken. Därför tvingar kombinationen?* I kriterierna att räkna celler som har minst ett texttecken i det.
- COUNT ($ A $ 1: $ A $ 11) - Detta räknar alla celler som innehåller siffror. I exemplet ovan returnerar det 3.
- SUMPRODUCT (-ISLOGISK ($ A $ 1: $ A $ 11) - Detta räknar alla celler som innehåller logiska värden. I exemplet ovan returnerar det 2.
#3 Räkna celler som innehåller specifik text
Låt oss säga att vi vill räkna alla celler där säljarens namn börjar med J. Detta kan enkelt uppnås genom att använda ett jokertecken i COUNTIF -funktionen. Här är formeln:
= RÄKNINGAR ($ C $ 2: $ C $ 11, ”J*”)
Kriterierna J* anger att texten i en cell ska börja med J och kan innehålla valfritt antal tecken.
Om du vill räkna celler som innehåller alfabetet var som helst i texten, flankera den med en asterisk på båda sidor. Till exempel, om du vill räkna celler som innehåller alfabetet "a" i det, använd * a * som kriterier.
Denna artikel är ovanligt lång jämfört med mina andra artiklar. Hoppas du har haft det. Låt mig veta dina tankar genom att lämna en kommentar.
Du kan också hitta följande Excel -självstudier användbara:
- Räkna antalet ord i Excel.
- Räkna celler baserat på bakgrundsfärg i Excel.
- Hur man summerar en kolumn i Excel (5 riktigt enkla sätt)
