Excelin COUNTIF-toiminnon avulla voit laskea solujen määrän, jotka täyttävät tietyt kriteerit tai ehdot tietyllä alueella.
COUNTIF-funktio on yksi Excelin tilastofunktioista, joka on COUNT- ja IF-funktioiden tai COUNTA-funktion yhdistelmä. Kun funktiota käytetään kaavassa, se laskee niiden solujen määrän, jotka vastaavat tiettyjä kriteerejä tai ehtoja samalla tai usealla alueella. COUNTIF-funktio auttaa laskemaan solut, jotka sisältävät tekstiä, numeroita tai päivämäärät, jotka täyttävät tietyt ehdot.
Voit laskea soluja Excelin COUNTIF- tai COUNTIFS-funktioilla. Ero COUNTIF- ja COUNTIFS-funktioiden välillä on se, että COUNTIF-funktiota käytetään yhden ehdon täyttävien solujen laskemiseen yhdellä alueella, kun taas COUNTIFS laskee solut, jotka täyttävät useita ehtoja samalla tai usealla alueella.
Tämä artikkeli näyttää, kuinka kahta funktiota COUNTIF ja COUNTIFS käytetään Excelissä.
Excelin COUNTIF-toiminto
COUNTIF-toiminnon avulla voit suorittaa datalaskennan tietyn kriteerin tai ehdon perusteella. Funktiossa käytetty ehto toimii loogisten operaattoreiden (, , =, >=, <=) ja yleismerkkien (*, ?) kanssa osittaista vastaavuutta varten.
COUNTIF-funktion syntaksi
COUNTIF-funktion rakenne on:
=LASKEJOS(väli,kriteerit)Parametrit:
alue– Laskettavien solujen alue.kriteeri– Ehto määrittää, mitkä solut tulee sisällyttää määrään määritetyllä alueella. Kriteerit voivat olla numeerinen arvo, teksti, viittaus solun osoitteeseen tai yhtälö.
COUNTIF-funktion käyttäminen numeeristen arvojen laskemiseen
Kuten edellä mainittiin, COUNTIF-funktion kriteerit (toinen argumentti) määrittelevät ehdon, joka kertoo funktiolle, mitkä solut laskea.
Tämä toiminto auttaa sinua laskemaan niiden solujen määrän, joiden arvot täyttävät loogiset ehdot, kuten yhtä suuri, suurempi, pienempi tai ei yhtä suuri kuin määritetty arvo jne.
Alla olevassa esimerkissä kaava laskee solut, jotka sisältävät arvon, joka on yhtä suuri kuin 5 (kriteerit). Voit lisätä suoraan '5:n kaavaan tai käyttää viittausta solun osoitteeseen, jolla on arvo (solu D2 alla olevassa esimerkissä).
=LASKEJOS(B2:B11;D2)
Yllä oleva kaava laskee niiden solujen lukumäärän solualueella (B2:B11), jotka sisältävät arvon, joka on yhtä suuri kuin solun D2 arvo.
Seuraava kaava laskee solut, joiden arvo on pienempi kuin 5.
=LASKEJOS(B2:B11,"<5")Pienempi kuin -operaattori (<) käskee kaavan laskemaan solut, joiden arvo on pienempi kuin 5 alueella B2:B11. Aina kun käytät operaattoria kunnossa, muista laittaa se lainausmerkkeihin ("").
Joskus, kun haluat laskea solut tutkimalla niitä solussa olevaa kriteeriä (arvoa) vastaan. Tällaisissa tapauksissa tee kriteeri yhdistämällä operaattori ja soluviittaus. Kun teet tämän, sinun on lisättävä vertailuoperaattori lainausmerkkeihin ("") ja asetettava sitten et-merkki (&) vertailuoperaattorin ja soluviittauksen väliin.
=LASKEJOS(B2:B11,">="&D2)
Alla olevassa kuvassa on muutamia esimerkkikaavoja ja niiden tulos.
COUNTIF-funktion käyttäminen tekstiarvojen laskemiseen
Jos haluat laskea tiettyjä tekstimerkkijonoja sisältävät solut, käytä kyseistä tekstimerkkijonoa kriteeriargumenttina tai solua, joka sisältää tekstimerkkijonon. Jos esimerkiksi alla olevassa taulukossa haluamme laskea kaikki alueen (B21:D27) solut, joissa on tekstiarvo solussa B21 (sam), voimme käyttää seuraavaa kaavaa:
=LASKEJOS(B21:D27;B21)
Kuten aiemmin keskustelimme, voisimme joko käyttää tekstiä "sam" suoraan kaavassa tai käyttää soluviittausta, jolla on kriteerit (B21). Tekstimerkkijono tulee aina sulkea lainausmerkkien ("") sisällä, kun sitä käytetään Excelin kaavassa.
=LASKEJOS(B21:D27"sam")
Laskeaksesi solut, jotka eivät sisällä määritettyä tekstiä, käytä alla olevaa kaavaa:
=LASKEJOS(B21:D27,""&B21)Muista liittää mukaan "ei ole yhtä suuri" "" operaattori lainausmerkeissä.
Jos käytät tekstiä "sam" suoraan kaavassa, sinun on liitettävä ""-operaattori ja tekstimerkkijono yhteen ("sam") lainausmerkeissä.
=LASKEJOS(B21:D27"sam") 
Jokerimerkkien käyttäminen Excelin COUNTIF-funktiossa (osittainen vastaavuus)
Voit käyttää COUNTIF-kaavaa yleismerkkien kanssa laskeaksesi tietyn sanan, lauseen tai kirjaimia sisältävät solut. Excelin COUNTIF-funktiossa voit käyttää kolmea jokerimerkkiä:
*(tähti) – Sitä käytetään laskemaan soluja, joissa on mikä tahansa määrä alku- ja loppumerkkejä/kirjaimia. (esim. St* voi tarkoittaa Stark, Stork, Stacks jne.?(kysymysmerkki) – Sitä käytetään etsimään soluja, joissa on yksittäinen merkki. (esim. St?rk voisi tarkoittaa Stark tai Stork.~(tilde) – Sitä käytetään etsimään ja laskemaan tekstissä kysymysmerkin tai tähtimerkin (~, *, ?) sisältävien solujen lukumäärä.
Tietyillä merkeillä alkavien tai päättyvien solujen laskeminen
Jos haluat laskea solut, jotka alkavat tai päättyvät tietyllä tekstillä ja joilla on useita muita merkkejä solussa, käytä tähtimerkkiä (*) COUNTIF-funktion toisessa argumentissa.
Käytä tätä esimerkkikaavaa:
=LASKEJOS(A1:A10"A*") – A:lla alkavien solujen laskeminen.
=LASKEJOS(A19:A28"*er") – laskea niiden solujen lukumäärän, jotka päättyvät "er"-merkkeihin.
=LASKEJOS(A2:A12"*QLD*") – niiden solujen laskemiseen, jotka sisältävät tekstin "QLD" missä tahansa tekstijonossa.
A ? edustaa täsmälleen yhtä merkkiä, käytä tätä jokerimerkkiä alla olevassa COUNTIF-funktiossa laskeaksesi solujen lukumäärän, jotka sisältävät täsmälleen +1 merkin, jossa "?' käytetään.
=LASKEJOS(A1:A10"Par?s")
Tyhjien ja ei-tyhjien solujen laskeminen COUNTIF-toiminnolla
COUNTIF-kaava on hyödyllinen myös määritettäessä tyhjien tai ei-tyhjien solujen määrää tietyllä alueella.
Laske ei-tyhjät solut
Jos haluat laskea vain solut, jotka sisältävät "teksti"-arvoja, käytä alla olevaa kaavaa. Tämä kaava pitää päivämääriä ja numeroita sisältäviä soluja tyhjinä soluina, eikä niitä lasketa mukaan.
=LASKEJOS(A1:B12"*")Jokerimerkki * vastaa vain tekstiarvoja ja palauttaa kaikkien tekstiarvojen määrän annetulla alueella.
Jos haluat laskea kaikki ei-tyhjät solut tietyllä alueella, kokeile tätä kaavaa:
=LASKEJOS(A1:B12,"")
Laske tyhjät solut
Jos haluat laskea tyhjiä soluja tietyllä alueella, käytä COUNTIF-funktiota * jokerimerkki ja -operaattori kriteeriargumentissa tyhjien solujen laskemiseksi.
Tämä kaava laskee solut, jotka eivät sisällä tekstiarvoja:
=LASKEJOS(A1:B12,""&"*")Siitä asti kun * jokerimerkki vastaa mitä tahansa tekstiarvoa, yllä oleva kaava laskee kaikki solut, jotka eivät ole yhtä suuria *. Se laskee myös solut, joissa on päivämäärät ja numerot.
Kaikkien välien laskeminen (kaikki arvotyypit):
=LASKEJOS(A1:B12,"")Tämä funktio laskee vain alueen tyhjät solut.
COUNTIF-funktion käyttäminen päivämäärien laskemiseen
Voit laskea soluja, joissa on päivämäärät (samalla tavalla kuin numerokriteerit), jotka täyttävät loogisen ehdon tai määritetyn päivämäärän tai päivämäärän viitesolussa.
Laskeaksemme solut, jotka sisältävät määritetyn päivämäärän (05-05-2020), käytämme tätä kaavaa:
=LASKEJOS(B2:B10"05-05-2020")
Voit myös määrittää päivämäärän eri muodoissa kriteereiksi COUNTIF-funktiossa, kuten alla on esitetty:
Jos haluat laskea solut, jotka sisältävät päivämäärät ennen tai jälkeen tiettyä päivämäärää, käytä pienempi kuin (ennen) tai suurempi kuin (jälkeen) -operaattoreita yhdessä tietyn päivämäärän tai soluviittauksen kanssa.
=LASKEJOS(B2:B10,">=05/05/2020")
Voit myös käyttää päivämäärän sisältävää soluviittausta yhdistämällä sen operaattoriin (lainausmerkeissä).
Laskeaksesi solujen määrän alueella A2:A14, jonka päivämäärä on ennen E3:n päivämäärää, käytä alla olevaa kaavaa, jossa suurempi kuin (<)-operaattori tarkoittaa ennen E3:n päivämäärää.
=LASKEJOS(A2:A14,"<"&E3)
Muutama esimerkkikaava ja niiden tulos:
Laskentapäivämäärä nykyisen päivämäärän perusteella
Voit yhdistää COUNTIF-funktion tiettyihin Excelin päivämäärätoimintoihin, kuten TODAY() laskeaksesi solut, joissa on nykyinen päivämäärä.
=COUNTIF(A2:A14,">"&TÄNÄÄN())Tämä toiminto laskee kaikki päivämäärät tästä päivästä alkaen alueella (A2:A14).
Laske päivämäärät tietyn ajanjakson välillä
Jos haluat laskea kaikki päivämäärät kahden päivämäärän välillä, sinun on käytettävä kaavassa kahta kriteeriä.
Voimme tehdä tämän kahdella menetelmällä: COUNTIF- ja COUNTIFS-funktioilla.
Excelin COUNTIF-funktion käyttö
Sinun on käytettävä kahta COUNTIF-funktiota laskeaksesi kaikki päivämäärät kahden määritetyn päivämäärän välillä.
Laske päivämäärät välillä '09-02-2020' ja '20-08-2021' käyttämällä tätä kaavaa:
=LASKEJOS(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">20-08-2021")Tämä kaava etsii ensin niiden solujen lukumäärän, joiden päivämäärä on helmikuun 2. päivän jälkeen, ja vähentää niiden solujen lukumäärän, joiden päivämäärä on 20. elokuuta jälkeen. Nyt saadaan numero. soluista, joiden päivämäärät ovat helmikuun 2. päivän jälkeen ja 20. elokuuta tai sitä ennen (luku on 9).
Jos et halua, että kaava laskee sekä 2. helmikuuta että 20. elokuuta, käytä sen sijaan tätä kaavaa:
=LASKEJOS(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">=20-08-2021")Korvaa vain ">"-operaattori toisessa ehdossa ">=".
Excelin COUNTIFS-funktion käyttö
COUNTIFS-funktio tukee myös useita ehtoja ja toisin kuin COUNTIF-funktio, se laskee solut vasta, kun kaikki ehdot täyttyvät. Jos haluat laskea solut, joissa on kaikki päivämäärät kahden määritetyn päivämäärän välillä, syötä tämä kaava:
=COUNTIFS(A2:A14,">"&A11,A2:A14,"<"&A10)
Jos haluat sisällyttää laskelmaan myös määritetyt päivämäärät, käytä '>=' ja '<=' operaattoreita. Mene tällä kaavalla:
=COUNTIFS(A2:A14,">=09-02-2020",A2:A14,"<=20-08-2021")Käytimme päivämäärää suoraan ehdoissa soluviittauksen sijaan tässä esimerkissä.
Kuinka käsitellä COUNTIF- ja COUNTIFS-lukuja useilla ehdoilla Excelissä
COUNTIF-toimintoa käytetään enimmäkseen solujen laskemiseen yhdellä kriteerillä (ehto) yhdellä alueella. Voit silti käyttää COUNTIF-funktiota solujen laskemiseen, jotka vastaavat useita ehtoja samalla alueella. COUNTIFS-funktiota voidaan kuitenkin käyttää sellaisten solujen laskemiseen, jotka täyttävät useita ehtoja samalla tai eri alueella.
Kuinka laskea lukuja alueen sisällä
Voit laskea soluja, jotka sisältävät numeroita kahden määritetyn luvun välillä, käyttämällä kahta funktiota: COUNTIF ja COUNTIFS.
COUNTIF laskea numerot kahden numeron välillä
Yksi yleisimmistä COUNTIF-funktion käyttötavoista useilla ehdoilla on numeroiden laskeminen kahden määritetyn luvun välillä, esim. laskea numeroita, jotka ovat suurempia kuin 10 mutta pienempiä kuin 50. Jos haluat laskea lukuja alueella, yhdistä kaksi tai useampia COUNTIF-funktioita yhteen kaavaan. Anna meidän näyttää sinulle kuinka.
Oletetaan, että haluat laskea solut alueella B2:B9, jossa arvo on suurempi kuin 10 ja pienempi kuin 21 (ei sisällä 10 ja 21), käytä tätä kaavaa:
=LASKEJOS(B2:B14,">10")-LASKEJOS(B2:B14,">=21")Kahden luvun välinen ero saadaan vähentämällä yksi kaava toisesta. Ensimmäinen kaava laskee luvut, jotka ovat suurempia kuin 10 (joka on 7), toinen kaava palauttaa numeroiden määrän, jotka ovat suurempia tai yhtä suuria kuin 21 (joka on 4), ja toisen kaavan tulos vähennetään ensimmäisestä kaavasta (7) -4) saadaksesi numeroiden määrän kahden luvun välillä (3).
Jos haluat laskea solut, joiden luku on suurempi kuin 10 ja pienempi kuin 21 alueella B2:B14, mukaan lukien luvut 10 ja 21, käytä tätä kaavaa:
=LASKEJOS(B2:B14,">=10")-LASKEJOS(B2:B14,">21")
COUNTIFS numeroiden laskemiseen 2 numeron välillä
Laskeaksesi luvut 10 ja 21 välillä (pois lukien 10 ja 21), jotka sisältyvät soluihin B2-B9, käytä tätä kaavaa:
=LASKUJAT(B2:B14,">10",B2:B14,"<21")
Jos haluat sisällyttää lukuihin 10 ja 21, käytä kaavoissa "suurempi tai yhtä suuri" (>=) "suurempi kuin" sijaan ja "pienempi tai yhtä suuri" (<=) "pienempi kuin"-operaattorien sijaan. .
COUNTIFS solujen laskemiseen useilla kriteereillä (JA kriteerit)
COUNTIFS-funktio on COUNTIF-funktion monikkovastine, joka laskee solut kahden tai useamman kriteerin perusteella samalla tai usealla alueella. Se tunnetaan nimellä "AND-logiikka", koska funktio on tehty solujen laskemiseen vain, kun kaikki annetut ehdot ovat TOSI.
Haluamme esimerkiksi selvittää, kuinka monta kertaa (solujen määrä) kyseistä leipää (arvo sarakkeessa A) on myyty alle 5 (arvo sarakkeessa C).
Voimme käyttää tätä kaavaa:
=COUNTIFS(A2:A14"leipä",C2:C14,"<5")
COUNTIF useiden kriteerien (OR Criteria) solujen laskemiseen
Jos haluat laskea niiden solujen määrän, jotka täyttävät useita ehtoja samalla alueella, liitä kaksi tai useampia COUNTIF-funktioita yhteen. Jos esimerkiksi haluat selvittää, kuinka monta kertaa "leipä" tai "juusto" toistetaan määritetyllä alueella (A2:A14), käytä alla olevaa kaavaa:
=LASKEJOS(A2:A14"Leipä")+LASKEJOS(A2:A14"Juusto")Tämä kaava laskee solut, joille ainakin yksi ehdoista on TOSI. Siksi sitä kutsutaan "OR-logiikaksi".
Jos haluat arvioida useamman kuin yhden kriteerin kussakin funktiossa, on parempi käyttää COUNTIFS-arvoa COUNTIF:n sijaan. Alla olevassa esimerkissä haluamme saada "Leipää" -tilan "tilattu" ja "toimitettu" määrän, joten käyttäisimme tätä kaavaa:
=COUNTIFS(A2:A14"Leipä",C2:C14"Tilattu")+COUNTIFS(A2:A14"Leipä",C2:C14"Toimitettu")
Toivomme, että tämä helppo, mutta melko pitkä opetusohjelma antaa sinulle käsityksen COUNTIF- ja COUNTIF-toimintojen käytöstä Excelissä.