Denna handledning visar hur du använderExklel COUNTIF och COUNTIFS Functioner i Excel för att räkna data som uppfyller vissa kriterier.
COUNTIF Funktionsöversikt
Du kan använda COUNTIF -funktionen i Excel för att räkna celler som innehåller ett visst värde, räkna celler som är större än eller lika med ett värde, etc.
(Lägg märke till hur formelinmatningarna ser ut)
COUNTIF Funktionssyntax och argument:
= COUNTIF (intervall, kriterier)
intervall - Räckvidden av celler att räkna.
kriterier - Kriterierna som styr vilka celler som ska räknas.
Vad är COUNTIF -funktionen?
COUNTIF -funktionen är en av de äldre funktionerna som används i kalkylblad. Enkelt uttryckt är det bra att skanna ett område och berätta hur många av cellerna som uppfyller det villkoret. Vi ska titta på hur funktionen fungerar med text, siffror och datum; liksom några av de andra situationerna som kan uppstå.
Grundläggande exempel
Låt oss börja med att titta på den här listan över slumpmässiga objekt. Vi har några siffror, tomma celler och några textsträngar.
Om du ville veta hur många objekt en exakt matchning till kriterierna är kan du ange vad du vill leta efter som det andra argumentet. Ett exempel på denna formel kan se ut
= RÄTTA (A2: A9, "Apple")
Denna formel skulle returnera siffran 3, eftersom det finns 3 celler i vårt sortiment som uppfyller dessa kriterier. Alternativt kan vi använda en cellreferens istället för att hårdkoda ett värde. Om vi skrev "Apple" i cell G2 kan vi ändra formeln till
= RÄKTA (A2: A9, G2)
När det gäller nummer är det viktigt att skilja mellan siffror och nummer som lagras som text. I allmänhet sätter du inte citattecken runt siffror när du skriver formler. Så, för att skriva en formel som kontrollerar siffran 5, skulle du skriva
= RÄKTA (A2: A9, 5)
Slutligen kan vi också leta efter tomma celler med hjälp av en sträng med noll längd. Vi skulle skriva den formeln som
= RÄKTA (A2: A9, "")
Notera: Denna formel räknar både celler som verkligen är tomma, liksom de som är tomma som ett resultat av en formel, som en IF -funktion.
Delvisa matcher
COUNTIF -funktionen stöder användningen av jokertecken, "*" eller "?", I kriterierna. Låt oss titta på denna lista med läckra bagerivaror:
För att hitta alla artiklar som börjar med Apple kan vi skriva "Apple*". Så, för att få ett svar på 3, är vår formel i D2
= RÄTTA (A2: A5, "Apple*")
Notera: COUNTIF-funktionen är inte skiftlägeskänslig, så du kan också skriva "apple*" om du vill.
Tillbaka till våra bakverk, vi kanske också vill ta reda på hur många pajer vi har i vår lista. Vi kan hitta det genom att placera jokertecken i början av vår sökterm och skriva
= RÄKTA (A2: A5, "*paj")
Denna formel ger resultatet av 2.
Vi kan också använda jokertecken för att kontrollera om det finns celler med text. Låt oss gå tillbaka till vår ursprungliga lista med data.
För att räkna antalet celler som har åtminstone lite text, alltså inte räkna tal eller tom cell, kan vi skriva
= RÄTTA (A2: A9, "*")
Du kan se att vår formel korrekt returnerar ett resultat av 4.
Jämförelseoperatörer i COUNTIF
När vi skrev kriterierna hittills har vi antytt att vår jämförelseoperator är "=". Faktum är att vi kunde ha skrivit detta:
= RÄTTA (A2: A9, "= Apple")
Det är dock en extra karaktär att skriva ut, så det brukar utelämnas. Detta betyder dock att du kan använda de andra operatörerna, till exempel större än, mindre än eller inte lika med. Låt oss titta på den här listan över registrerade åldrar:
Om vi ville veta hur många barn som är minst 5 år kan vi skriva ut en "större än eller lika med" jämförelse så här:
= RÄKTA (A2: A8, "> = 5")
Notera: Jämförelseoperatorn ges alltid som en textsträng och måste därför vara inom citattecken.
På samma sätt kan du också söka efter artiklar som är mindre än ett visst värde. Om vi behöver ta reda på hur många som är färre än 8 kan vi skriva ut
= RÄKTA (A2: A8, "<8")
Detta ger oss det önskade resultatet av 5. Låt oss nu föreställa oss att alla 6-åriga barn ska ut på en utflykt. Hur många barn blir det kvar? Vi kan räkna ut detta genom att använda en "inte lika med" jämförelse så här:
= RÄKTA (A2: A8, "6")
Nu kan vi snabbt se att vi har 6 barn som inte är 6 år gamla.
I dessa jämförelseexempel hittills har vi svårt att koda de värden vi ville ha. Du kan också använda en cellreferens. Tricket är att du måste sammanfoga jämförelseoperatören med cellreferensen. Låt oss säga att vi satte siffran 7 i cell C2, och vi vill att vår formel i D2 ska visa hur många barn som är mindre än 7 år.
Vår formel i D2 måste se ut så här:
= RÄKTA (A2: A8, "<" och C2)
Notera: Var särskilt uppmärksam när du skriver dessa formler om du behöver sätta ett objekt inom citattecken eller utanför. Operatörerna är alltid inne i citat, cellreferenser är alltid utanför citat. Siffror är utanför om du gör en exakt matchning, men inne om du gör en jämförelseoperatör.
Arbetar med datum
Vi har sett hur du kan ge en text eller ett nummer som kriterier, men vad sägs om när vi behöver arbeta med datum? Här är en snabb exempellista vi kan arbeta med:
För att räkna hur många datum som är efter den 4 maj måste vi vara försiktiga. Datorer lagrar datum som nummer, så vi måste se till att datorn använder rätt nummer. Om vi skrev den här formeln, skulle vi få det korrekta resultatet?
= RÄKTA (A2: A9, "
Svaret är "möjligen". Eftersom vi utelämnade året från våra kriterier antar datorn att vi menar det aktuella året. Om alla datum vi arbetar med är för innevarande år får vi det rätta svaret. Men om det finns några datum i framtiden får vi fel svar. När nästa år börjar kommer denna formel också att ge ett annat resultat. Som sådan bör denna syntax förmodligen undvikas.
Eftersom det kan vara svårt att skriva datum korrekt inom en formel, är det bäst att skriva det datum du vill använda i en cell, och sedan kan du använda den cellreferensen i din COUNTIF -formel. Så, låt oss skriva datumet 7-maj-2020 i cell C2, och sedan kan vi sätta vår formel i C4.
Formeln i C4 är
= RÄKTA (A2: A9, "<" och C2)
Nu vet vi att resultatet av 7 är korrekt, och svaret kommer inte att förändras oväntat om vi öppnar detta kalkylblad någon gång i framtiden.
Innan vi lämnar det här avsnittet är det vanligt att använda funktionen TODAY när vi arbetar med datum. Vi kan använda det precis som vi skulle använda en cellreferens. Till exempel kan vi ändra den tidigare formeln till följande:
= RÄTTA (A2: A9, "<" & TODAY ())
Nu kommer vår formel fortfarande att uppdateras när realtid fortskrider, och vi kommer att ha ett antal artiklar som är färre än idag.
Flera kriterier och COUNTIFS
Den ursprungliga COUNTIF -funktionen förbättrades 2007 när COUNTIFS kom ut. Syntaxen mellan de två är mycket lika, med den senare kan du ge ytterligare intervall och kriterier. Du kan enkelt använda COUNTIFS i alla situationer som COUNTIF finns. Det är bara en bra idé att veta att båda funktionerna finns.
Låt oss titta på denna datatabell:
För att ta reda på hur många som är i lönenivåerna 1 till 2 kan du skriva en summering av COUNTIF -funktioner så här:
= RÄTTA (B2: B7, "> = 1")-RÄKTA (B2: B7, "> 2")
Denna formel fungerar, eftersom du hittar allt som är över 1, men sedan subtraherar antalet poster som ligger utanför din avstängningspunkt. Alternativt kan du använda COUNTIFS så här:
= RÄKNINGAR (B2: B7, "> = 1", B2: B7, "<= 2")
Det senare är mer intuitivt att läsa, så du kanske vill använda den rutten. COUNTIFS är också mer kraftfull när du behöver överväga flera kolumner. Låt oss säga att vi vill veta hur många personer som befinner sig i ledningen och på lönenivå 1. Du kan inte göra det med bara en RÄTTA; du måste skriva ut
= RÄKNINGAR (A2: A7, "Management", B2: B7, 1)
Denna formel skulle ge dig det korrekta resultatet av 2. Innan vi lämnar det här avsnittet, låt oss överväga en Or -typlogik. Vad händer om vi ville ta reda på hur många personer som är i ledningen eller? Du skulle behöva lägga till några COUNTIFS tillsammans, men det finns två sätt att göra detta. Det enklaste sättet är att skriva det så här:
= RÄTTA (A2: A7, "HR")+RÄKNING (A2: A7, "Hantering")
Du kan också använda en matris och skriva den här matrisformeln:
= SUMMA (RÄKNING (A2: A7, {"HR", "Management"}))
Notera: Arrayformler måste bekräftas med "Ctrl+Shift+Enter", inte bara "Enter".
Hur denna formel kommer att fungera är att det kommer att se att du har angett en array som ingång. Det kommer således att beräkna resultatet till två olika COUNTIF -funktioner och lagra dem i en array. SUMM -funktionen lägger sedan till alla resultat i vår array för att skapa en enda utmatning. Således kommer vår formel att utvärderas så här:
= SUMME (RÄKNING (A2: A7, {"HR", "Management"})) = SUMMA ({2, 3}) = 5
Räkna unika värden
Nu när vi har sett hur man använder en array med funktionen COUNTIF kan vi ta det ett steg längre för att hjälpa oss att räkna hur många unika värden som finns i ett intervall. Låt oss först titta på vår lista över avdelningar igen.
= SUMMA (1/RÄTTA (A2: A7, A2: A7))
Vi kan se att det finns 6 celler värda data, men det finns bara 3 olika objekt. För att få matematiken att fungera behöver vi att varje objekt är värt 1/N, där N är antalet gånger ett objekt upprepas. Till exempel, om varje HR bara var värt 1/2, när du lägger till dem får du en räkning av 1, för 1 unikt värde.
Tillbaka till vårt COUNTIF, som är utformat för att räkna ut hur många gånger ett objekt visas i ett sortiment. I D2 skriver vi matrisformeln
= SUMMA (1/RÄTTA (A2: A7, A2: A7))
Hur denna formel kommer att fungera är för varje cell i intervallet A2: A7, den kommer att kontrollera hur många gånger den visas. Med vårt urval kommer detta att producera en rad
{2, 2, 3, 3, 3, 1}
Sedan gör vi alla dessa siffror till bråk genom att göra en delning. Nu ser vår uppsättning ut
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
När vi lägger till allt detta får vi det önskade resultatet 3.
Countif med två eller flera villkor - Countifs -funktionen
Hittills har vi bara arbetat med COUNTIF -funktionen. COUNTIF -funktionen kan bara hantera ett kriterium åt gången. För att COUNTIF med flera kriterier måste du använda COUNTIFS -funktionen. COUNTIFS beter sig exakt som COUNTIF. Du lägger bara till extra kriterier. Låt oss titta på exemplet nedan.
= RÄKNINGAR (B2: B7, "= 130")
COUNTIF & COUNTIFS i Google Kalkylark
COUNTIF & COUNTIFS -funktionen fungerar exakt samma i Google Kalkylark som i Excel:
