Tämä opetusohjelma osoittaa, mitenErinomainenel COUNTIF ja COUNTIFS Toimtioita Excelissä laskemaan tietyt kriteerit täyttävät tiedot.
COUNTIF -toiminnon yleiskatsaus
Excelin COUNTIF -funktion avulla voit laskea solut, jotka sisältävät tietyn arvon, laskea soluja, jotka ovat suurempia tai yhtä suuria kuin arvo jne.
(Huomaa, miten kaavasyötteet näkyvät)
COUNTIF -funktion syntaksi ja argumentit:
= COUNTIF (alue, ehdot)
alue - Laskettavien solujen alue.
kriteeri - Kriteerit, jotka määräävät, mitkä solut tulisi laskea.
Mikä on COUNTIF -toiminto?
COUNTIF -toiminto on yksi vanhimmista laskentataulukoissa käytetyistä toiminnoista. Yksinkertaisesti sanottuna se on hyvä skannata alue ja kertoa, kuinka monta solua täyttää tämän ehdon. Katsomme, miten toiminto toimii tekstin, numeroiden ja päivämäärien kanssa; samoin kuin joitakin muita mahdollisia tilanteita.
Perusesimerkki
Aloitetaan katsomalla tätä satunnaisten kohteiden luetteloa. Meillä on joitakin numeroita, tyhjiä soluja ja joitain tekstimerkkijonoja.
Jos haluat tietää, kuinka monta kohdetta täsmälleen vastaa ehtoja, voit määrittää, mitä haluat etsiä toisena argumenttina. Esimerkki tästä kaavasta voi näyttää
= COUNTIF (A2: A9, "Apple")
Tämä kaava palauttaisi luvun 3, koska valikoimastamme löytyy 3 solua, jotka täyttävät nämä kriteerit. Vaihtoehtoisesti voimme käyttää soluviittausta arvon kovakoodauksen sijasta. Jos kirjoittaisimme "Apple" soluun G2, voisimme muuttaa kaavan muotoon
= LASKE (jos A2: A9, G2)
Numeroita käsiteltäessä on tärkeää erottaa toisistaan numerot ja tekstiksi tallennetut numerot. Yleensä et laita lainausmerkkejä numeroiden ympärille, kun kirjoitat kaavoja. Joten kirjoittaaksesi kaavan, joka tarkistaa numeron 5, kirjoitat
= LASKE (jos A2: A9, 5)
Lopuksi voimme myös tarkistaa tyhjät solut käyttämällä nollapituista merkkijonoa. Kirjoittaisimme tämän kaavan muodossa
= LASKE (jos A2: A9, "")
Huomautus: Tämä kaava laskee sekä todella tyhjät solut että ne, jotka ovat tyhjiä kaavan seurauksena, kuten IF -funktio.
Osittaiset ottelut
COUNTIF -toiminto tukee yleismerkkien, "*" tai "?" Käyttöä ehdoissa. Katsotaanpa tätä luetteloa maukkaista leipomotavaroista:
Löytääksemme kaikki kohteet, jotka alkavat Applelta, voisimme kirjoittaa "Apple*". Joten saadaksesi vastauksen 3, kaava D2 on
= COUNTIF (A2: A5, "Apple*")
Huomautus: COUNTIF-toiminto ei erota isoja ja pieniä kirjaimia, joten voit halutessasi kirjoittaa myös "omena*".
Palataksemme leivonnaisiimme saatamme myös haluta tietää, kuinka monta piirakkaa meillä on luettelossamme. Voimme löytää sen kirjoittamalla yleismerkin hakutermimme alkuun
= COUNTIF (A2: A5, "*piirakka")
Tämä kaava antaa tuloksen 2.
Voimme myös käyttää jokerimerkkejä tekstisolujen löytämiseksi. Palataan alkuperäiseen tietoluetteloon.
Voimme laskea niiden solujen lukumäärän, joissa on ainakin jonkin verran tekstiä, jolloin ei lasketa numeroita tai tyhjää solua
= LASKE (jos A2: A9, "*")
Näet, että kaavamme palauttaa oikein tuloksen 4.
Vertailuoperaattorit COUNTIF: ssa
Kirjoittaessamme kriteereitä tähän mennessä olemme antaneet ymmärtää, että vertailuoperaattorimme on "=". Itse asiassa olisimme voineet kirjoittaa tämän:
= COUNTIF (A2: A9, "= Apple")
Se on kuitenkin ylimääräinen hahmo kirjoitettavaksi, joten se jätetään yleensä pois. Tämä tarkoittaa kuitenkin sitä, että voit käyttää muita operaattoreita, kuten suurempi, pienempi tai ei yhtä suuri kuin. Katsotaanpa tätä luetteloa tallennetuista ikäryhmistä:
Jos haluaisimme tietää, kuinka monta lasta on vähintään 5 -vuotias, voimme kirjoittaa "suurempi tai yhtä suuri" -vertailun seuraavasti:
= LASKE (jos A2: A8, "> = 5")
Huomautus: Vertailuoperaattori annetaan aina tekstimerkkijonona, joten sen on oltava lainausmerkkien sisällä.
Samoin voit myös tarkistaa kohteita, jotka ovat pienempiä kuin annettu arvo. Jos meidän on selvitettävä, kuinka monta on alle 8, voimme kirjoittaa ulos
= LASKE (jos A2: A8, "<8")
Tämä antaa meille halutun tuloksen 5. Kuvitellaan nyt, että kaikki 6-vuotiaat lapset lähtevät retkelle. Kuinka monta lasta jää? Voimme selvittää tämän käyttämällä "ei yhtä kuin" -vertailua seuraavasti:
= LASKE (jos A2: A8, "6")
Nyt voimme nopeasti nähdä, että meillä on 6 lasta, jotka eivät ole 6 -vuotiaita.
Näissä vertailuesimerkeissä tähän mennessä olemme koodaaneet kovasti haluamiamme arvoja. Voit myös käyttää soluviittausta. Temppu on, että sinun on yhdistettävä vertailuoperaattori soluviittaukseen. Oletetaan, että laitamme numeron 7 soluun C2 ja haluamme, että kaavamme D2 näyttää kuinka monta lasta on alle 7 -vuotiaita.
D2 -kaavamme on näytettävä tältä:
= COUNTIF (A2: A8, "<" & C2)
Huomautus: Kiinnitä erityistä huomiota kirjoittaessasi näitä kaavoja siihen, pitääkö kohde laittaa lainausmerkkien sisään vai ulkopuolelle. Operaattorit ovat aina lainausmerkkien sisällä, soluviittaukset ovat aina lainausten ulkopuolella. Numerot ovat ulkopuolella, jos teet tarkan haun, mutta sisällä, jos teet vertailuoperaattorin.
Päivämäärien kanssa työskentely
Olemme nähneet, kuinka voit antaa tekstin tai numeron kriteerinä, mutta entä kun meidän on työskenneltävä päivämäärien kanssa? Tässä on nopea esimerkkiluettelo, jonka kanssa voimme työskennellä:
Jotta voimme laskea, kuinka monta päivämäärää on 4. toukokuuta, meidän on oltava varovaisia. Tietokoneet tallentavat päivämäärät numeroina, joten meidän on varmistettava, että tietokone käyttää oikeaa numeroa. Jos kirjoittaisimme tämän kaavan, saisimmeko oikean tuloksen?
= LASKE (jos A2: A9, "
Vastaus on "mahdollisesti". Koska jätimme vuoden pois kriteereistämme, tietokone olettaa, että tarkoitamme kuluvaa vuotta. Jos kaikki päivämäärät, joiden kanssa työskentelemme, ovat kuluvaa vuotta, saamme oikean vastauksen. Jos kuitenkin jotkut päivämäärät ovat tulevaisuudessa, saamme väärän vastauksen. Lisäksi seuraavan vuoden alkaessa tämä kaava antaa toisen tuloksen. Sellaista syntaksia pitäisi luultavasti välttää.
Koska päivämääriä voi olla vaikea kirjoittaa oikein kaavaan, on paras käytäntö kirjoittaa päivämäärä, jota haluat käyttää soluun, ja voit sitten käyttää kyseistä soluviittausta COUNTIF -kaavassa. Joten kirjoitetaan päivämäärä 7.5.2020 soluun C2, ja sitten voimme laittaa kaavan C4: ään.
C4: n kaava on
= COUNTIF (A2: A9, "<" & C2)
Nyt tiedämme, että tulos 7 on oikea, eikä vastaus muutu odottamatta, jos avaamme tämän laskentataulukon joskus tulevaisuudessa.
Ennen kuin poistumme tästä osiosta, on tavallista käyttää TODAY -toimintoa päivämääriä käsiteltäessä. Voimme käyttää sitä aivan kuten soluviittausta. Voisimme esimerkiksi muuttaa edellisen kaavan seuraavasti:
= LASKE (jos A2: A9, "<" & TODAY ())
Nyt kaavamme päivittyy edelleen reaaliajassa, ja meillä on vähemmän kohteita kuin tänään.
Useita ehtoja ja COUNTIFS
Alkuperäinen COUNTIF -toiminto parani vuonna 2007, kun COUNTIFS julkaistiin. Näiden kahden syntaksi on hyvin samanlainen, ja jälkimmäisen avulla voit antaa lisää alueita ja ehtoja. Voit helposti käyttää COUNTIFS: ää kaikissa tilanteissa, joissa COUNTIF on olemassa. On vain hyvä idea tietää, että molemmat toiminnot ovat olemassa.
Katsotaanpa tätä tietotaulukkoa:
Voit selvittää, kuinka monta ihmistä on palkkatasoilla 1-2, kirjoittamalla yhteenvedon COUNTIF -funktioista seuraavasti:
= LASKE (jos: B2: B7, "> = 1")-LASKEA (B2: B7, "> 2")
Tämä kaava toimii, kun löydät kaiken, joka on yli 1, mutta vähennät sitten raja-arvosi ulkopuolella olevien tietueiden määrän. Vaihtoehtoisesti voit käyttää COUNTIFS: tä seuraavasti:
= LASKUT (B2: B7, "> = 1", B2: B7, "<= 2")
Jälkimmäinen on intuitiivisempi lukea, joten kannattaa käyttää tätä reittiä. Lisäksi COUNTIFS on tehokkaampi, kun sinun on harkittava useita sarakkeita. Oletetaan, että haluamme tietää, kuinka monta ihmistä on hallinnossa ja palkkatasolla 1. Et voi tehdä sitä vain COUNTIF: llä; sinun täytyy kirjoittaa ulos
= LASKUT (A2: A7, "Hallinta", B2: B7, 1)
Tämä kaava antaisi sinulle oikean tuloksen 2. Ennen kuin poistumme tästä osiosta, harkitsemme TAI -logiikkaa. Mitä jos haluaisimme selvittää, kuinka monta ihmistä on johdossa tai? Sinun on lisättävä COUNTIFS -tiedostoa yhteen, mutta voit tehdä sen kahdella tavalla. Yksinkertaisin tapa on kirjoittaa se näin:
= COUNTIF (A2: A7, "HR")+COUNTIF (A2: A7, "Management")
Voit myös käyttää taulukkoa ja kirjoittaa tämän taulukon kaavan:
= SUMMA (LASKEA (A2: A7, {"HR", "Hallinta"})))
Huomautus: Taulukon kaavat on vahvistettava näppäimillä `Ctrl+Vaihto+Enter` eikä vain` `Enter ''.
Kuinka tämä kaava toimii, se näkee, että olet antanut taulukon syötteeksi. Se laskee tuloksen kahteen eri COUNTIF -funktioon ja tallentaa ne taulukkoon. SUM -toiminto yhdistää sitten kaikki taulukon tulokset yhteen, jolloin saadaan yksi tulos. Näin ollen kaavamme arvioidaan seuraavasti:
= SUMMA (LASKEA (A2: A7, {"HR", "Hallinta"}))) = SUMMA ({2, 3}) = 5
Laske ainutlaatuiset arvot
Nyt kun olemme nähneet kuinka käyttää matriisia COUNTIF -toiminnolla, voimme ottaa askeleen pidemmälle auttaaksemme meitä laskemaan kuinka monta ainutlaatuista arvoa alueella on. Katsotaanpa ensin osastolistamme.
= SUMMA (1/LASKE ((A2: A7, A2: A7)))
Voimme nähdä, että dataa on kuuden solun arvoinen, mutta vain kolme eri kohdetta. Jotta matematiikka toimisi, tarvitsemme jokaisen kohteen arvon 1/N, jossa N on kohteen toistojen lukumäärä. Jos esimerkiksi jokainen HR -arvo oli vain 1/2, niin kun laskit ne yhteen, saat lukeman 1, yhdelle ainutlaatuiselle arvolle.
Palaa COUNTIFiin, jonka tarkoituksena on selvittää, kuinka monta kertaa kohde näkyy alueella. Kirjoitamme taulukkoon D2 matriisikaavan
= SUMMA (1/LASKELLA (A2: A7, A2: A7))
Kuinka tämä kaava toimii, on jokaisen solun alueella A2: A7, se tarkistaa, kuinka monta kertaa se näkyy. Näytteemme avulla tämä tuottaa joukon
{2, 2, 3, 3, 3, 1}
Sitten muutamme kaikki nämä luvut murto -osiksi tekemällä jonkin jaon. Nyt valikoimamme näyttää
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Kun laskemme nämä kaikki yhteen, saamme halutun tuloksen 3.
Laskuri kahdella tai usealla ehdolla - Countifs -toiminto
Toistaiseksi olemme työskennelleet vain COUNTIF -toiminnon kanssa. COUNTIF -toiminto voi käsitellä vain yhtä ehtoa kerrallaan. Jos haluat käyttää COUNTIFia useilla kriteereillä, sinun on käytettävä COUNTIFS -toimintoa. COUNTIFS toimii aivan kuten COUNTIF. Lisäät vain lisäkriteerit. Katsotaanpa alla olevaa esimerkkiä.
= LASKUT (B2: B7, "= 130")
COUNTIF & COUNTIFS Google Sheetsissä
COUNTIF & COUNTIFS -toiminto toimii täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä:
