See õpetus näitab, kuidasExcel COUNTIF ja COUNTIFS Functioonid Excelis teatud kriteeriumidele vastavate andmete loendamiseks.
Funktsiooni COUNTIF ülevaade
Funktsiooni COUNTIF saate Excelis kasutada teatud väärtust sisaldavate lahtrite loendamiseks, väärtusest suuremate või sellega võrdsete lahtrite loendamiseks jne.
(Pange tähele, kuidas valemisisendid ilmuvad)
Funktsiooni COUNTIF süntaks ja argumendid:
= COUNTIF (vahemik, kriteeriumid)
vahemik - Loendatavate rakkude vahemik.
kriteeriumid - kriteeriumid, mis kontrollivad, milliseid lahtreid loendada tuleks.
Mis on funktsioon COUNTIF?
Funktsioon COUNTIF on üks vanemaid arvutustabelites kasutatavaid funktsioone. Lihtsamalt öeldes sobib see suurepäraselt vahemiku skannimiseks ja ütleb teile, kui palju lahtreid sellele tingimusele vastab. Vaatame, kuidas funktsioon töötab teksti, numbrite ja kuupäevadega; samuti mõned muud olukorrad, mis võivad tekkida.
Põhinäide
Alustuseks vaatame seda juhuslike üksuste loendit. Meil on mõned numbrid, tühjad lahtrid ja mõned tekstistringid.
Kui soovite teada, kui palju üksusi kriteeriumidele täpselt vastab, saate teise argumendina otsida, mida soovite otsida. Selle valemi näide võib välja näha
= COUNTIF (A2: A9, "Apple")
See valem tagastaks numbri 3, kuna meie vahemikus on 3 lahtrit, mis vastavad nendele kriteeriumidele. Teise võimalusena võime väärtuse kõvakodeerimise asemel kasutada lahtriviidet. Kui kirjutasime lahtrisse G2 „Apple”, võiksime valemi muuta
= COUNTIF (A2: A9, G2)
Numbriga tegelemisel on oluline teha vahet numbritel ja tekstina salvestatud numbritel. Üldiselt ei pane valemite kirjutamisel jutumärke numbrite ümber. Niisiis, et kirjutada valem, mis kontrollib numbrit 5, kirjutaksite
= COUNTIF (A2: A9, 5)
Lõpuks võiksime tühjade lahtrite olemasolu kontrollida ka nullpikkusega stringi abil. Kirjutame selle valemi järgmiselt
= COUNTIF (A2: A9, "")
Märge: See valem loeb nii tõeliselt tühjad lahtrid kui ka need, mis on valemi tulemusel tühjad, näiteks IF -funktsioon.
Osalised vasted
Funktsioon COUNTIF toetab kriteeriumides metamärkide „*” või „?” Kasutamist. Vaatame seda maitsvate pagaritoodete loendit:
Kõigi Apple'iga algavate üksuste leidmiseks võiksime kirjutada "Apple*". Seega, vastuse 3 saamiseks on meie valem D2 -s järgmine
= COUNTIF (A2: A5, "Apple*")
Märge: Funktsioon COUNTIF ei ole tõstutundlik, seega võite soovi korral kirjutada ka õuna*.
Tagasi meie küpsetiste juurde, võiksime ka teada saada, kui palju pirukaid meie nimekirjas on. Leiame selle, kui asetate metamärgi otsingutermini algusesse ja kirjutate
= COUNTIF (A2: A5, "*pirukas")
See valem annab tulemuseks 2.
Samuti võime metamärke kasutada tekstiga lahtrite leidmiseks. Läheme tagasi meie esialgse andmete loendi juurde.
Vähemalt teksti sisaldavate lahtrite arvu loendamiseks, jättes seega numbrid või tühjad lahtrid lugemata, võime kirjutada
= COUNTIF (A2: A9, "*")
Näete, et meie valem tagastab õigesti tulemuse 4.
Võrdlusoperaatorid COUNTIF -is
Siiani kriteeriume kirjutades oleme vihjanud, et meie võrdlusoperaator on "=". Tegelikult oleksime võinud kirjutada nii:
= COUNTIF (A2: A9, "= Apple")
Kirjutamine on siiski lisamärk, nii et see jäetakse tavaliselt välja. See aga tähendab, et saate kasutada ka teisi operaatoreid, nagu suurem, väiksem või mitte võrdne. Vaatame seda registreeritud vanuste loendit:
Kui tahame teada, kui palju lapsi on vähemalt 5 -aastased, võime kirjutada võrdluse „suurem või võrdne”:
= COUNTIF (A2: A8, "> = 5")
Märge: Võrdlusoperaator on alati antud tekstistringina ja seega peab see olema jutumärkides.
Samamoodi saate kontrollida ka objekte, mis on antud väärtusest väiksemad. Kui meil on vaja teada saada, kui palju on vähem kui 8, võime välja kirjutada
= COUNTIF (A2: A8, "<8")
See annab meile soovitud tulemuse 5. Nüüd kujutagem ette, et kõik 6-aastased lapsed lähevad väljasõidule. Mitu last jääb alles? Me saame selle välja mõelda, kasutades võrdlust "mitte võrdne":
= COUNTIF (A2: A8, "6")
Nüüd näeme kiiresti, et meil on 6 last, kes pole 6 -aastased.
Nendes võrdlusnäidetes oleme siiani kõvasti kodeerinud soovitud väärtusi. Võite kasutada ka lahtriviidet. Trikk on selles, et peate võrdlusoperaatori ühendama lahtri viitega. Oletame, et panime lahtrisse C2 numbri 7 ja soovime, et meie valem D2 -s näitaks, kui palju lapsi on alla 7 -aastased.
Meie D2 valem peab välja nägema selline:
= COUNTIF (A2: A8, "<" ja C2)
Märge: Pöörake nende valemite kirjutamisel erilist tähelepanu sellele, kas peate eseme panema jutumärkidesse või väljapoole. Operaatorid on alati jutumärkides, lahtriviited on alati väljaspool jutumärke. Numbrid on väljaspool, kui teete täpset vastet, kuid sees, kui teete võrdlusoperaatorit.
Töö kuupäevadega
Oleme näinud, kuidas saate kriteeriumina teksti või numbri anda, aga kuidas on siis, kui peame kuupäevadega töötama? Siin on kiire näidisloend, millega saame töötada:
Et lugeda, kui palju kuupäevi on pärast 4. maid, peame olema ettevaatlikud. Arvutid salvestavad kuupäevad numbritena, seega peame veenduma, et arvuti kasutab õiget numbrit. Kui me selle valemi kirjutaksime, kas saaksime õige tulemuse?
= COUNTIF (A2: A9, "
Vastus on "võimalik". Kuna jätsime aasta kriteeriumidest välja, eeldab arvuti, et peame silmas jooksvat aastat. Kui kõik kuupäevad, millega töötame, on jooksva aasta kohta, saame õige vastuse. Kui aga tulevikus on kuupäevi, saame vale vastuse. Samuti, kui järgmine aasta algab, annab see valem teistsuguse tulemuse. Sellisena tuleks seda süntaksi ilmselt vältida.
Kuna valemisse võib olla raske kuupäevi õigesti kirjutada, on hea tava kirjutada lahtrisse kuupäev, mida soovite kasutada, ja seejärel saate seda lahtriviidet kasutada valemis COUNTIF. Niisiis, kirjutame lahtrisse C2 kuupäeva 7. mai 2020 ja siis saame oma valemi sisestada lahtrisse C4.
C4 valem on
= COUNTIF (A2: A9, "<" ja C2)
Nüüd teame, et tulemus 7 on õige ja vastus ei muutu ootamatult, kui selle arvutustabeli millalgi tulevikus avame.
Enne sellest jaotisest lahkumist on kuupäevadega töötamisel tavaline kasutada funktsiooni TODAY. Saame seda kasutada täpselt nagu lahtriviidet. Näiteks võiksime eelmise valemi muuta selliseks:
= COUNTIF (A2: A9, "<" & TODAY ())
Nüüd värskendatakse meie valemit reaalajas, kui meil on, ja meil on üksusi vähem kui praegu.
Mitu kriteeriumi ja COUNTIFS
Algne funktsioon COUNTIF paranes 2007. aastal, kui COUNTIFS ilmus. Nende kahe süntaks on väga sarnane, viimane võimaldab teil anda täiendavaid vahemikke ja kriteeriume. Saate hõlpsasti kasutada COUNTIFS -i igas COUNTIF -i olemasolus. On lihtsalt hea mõte teada, et mõlemad funktsioonid on olemas.
Vaatame seda andmetabelit:
Et teada saada, kui palju inimesi on palgatasemel 1–2, võite kirjutada COUNTIF -funktsioonide kokkuvõtte järgmiselt:
= COUNTIF (B2: B7, "> = 1")-COUNTIF (B2: B7, "> 2")
See valem töötab, kuna leiate kõik, mis on üle 1, kuid lahutate seejärel kirjete arvu, mis ületavad teie piirpunkti. Teise võimalusena võite kasutada järgmist COUNTIFS -i:
= COUNTIFS (B2: B7, "> = 1", B2: B7, "<= 2")
Viimast on intuitiivsem lugeda, seega võiksite seda marsruuti kasutada. Lisaks on COUNTIFS võimsam, kui peate arvestama mitme veeruga. Oletame, et tahame teada, kui palju inimesi on halduses ja palgatasemel 1. Te ei saa seda teha ainult COUNTIF -iga; peate välja kirjutama
= COUNTIFS (A2: A7, "Haldamine", B2: B7, 1)
See valem annaks teile õige tulemuse 2. Enne selle jaotise lahkumist kaalume või tüüpi loogikat. Mis siis, kui tahaksime teada, kui palju inimesi on juhtkonnas või? Peaksite COUNTIFS -i kokku lisama, kuid selleks on kaks võimalust. Lihtsam viis on kirjutada see järgmiselt:
= COUNTIF (A2: A7, "HR")+COUNTIF (A2: A7, "Haldus")
Võite kasutada ka massiivi ja kirjutada selle massiivi valemi:
= SUMMA (COUNTIF (A2: A7, {"HR", "Management"})))
Märge: Massiivivalemid tuleb kinnitada klahvidega Ctrl+Tõstuklahv+Enter, mitte ainult sisestusklahvi.
Kuidas see valem töötab, näeb see, et olete sisendiks andnud massiivi. See arvutab tulemuse kahele erinevale COUNTIF -funktsioonile ja salvestab need massiivi. Funktsioon SUM liidab seejärel kõik meie massiivi tulemused kokku, et saada üks väljund. Seega hinnatakse meie valemit järgmiselt:
= SUMMA (COUNTIF (A2: A7, {"HR", "Management"}))) = SUM ({2, 3}) = 5
Loendage ainulaadseid väärtusi
Nüüd, kui oleme näinud, kuidas massiivi kasutada funktsiooniga COUNTIF, saame astuda selle sammu edasi, et aidata meil loendada, mitu unikaalset väärtust vahemikus on. Kõigepealt vaatame uuesti meie osakondade loendit.
= SUM (1/COUNTIF (A2: A7, A2: A7))
Näeme, et andmeid on 6 lahtri väärtuses, kuid on ainult 3 erinevat üksust. Matemaatika toimimiseks vajame iga üksuse väärtust 1/N, kus N on üksuse korduste arv. Näiteks kui iga HR oli väärt ainult 1/2, siis kui te need kokku liitsite, saate ühe ainulaadse väärtuse kohta numbri 1.
Tagasi meie COUNTIF -i juurde, mille eesmärk on välja selgitada, mitu korda üksus vahemikus ilmub. D2 -s kirjutame massiivi valemi
= SUM (1/COUNTIF (A2: A7, A2: A7))
Kuidas see valem töötab, on iga lahtri vahemikus A2: A7, see kontrollib, mitu korda see ilmub. Meie proovi abil toodetakse sellest hulgaliselt
{2, 2, 3, 3, 3, 1}
Seejärel muudame kõik need numbrid murdudeks, jagades neid. Nüüd näeb meie massiiv välja
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Kui need kõik kokku liita, saame soovitud tulemuse 3.
Countif kahe või mitme tingimusega - funktsioon Countifs
Siiani oleme töötanud ainult funktsiooniga COUNTIF. Funktsioon COUNTIF saab korraga hakkama ainult ühe kriteeriumiga. Mitme kriteeriumiga COUNTIF -i kasutamiseks peate kasutama funktsiooni COUNTIFS. COUNTIFS käitub täpselt nagu COUNTIF. Lisage lihtsalt lisakriteeriumid. Vaatame allpool toodud näidet.
= COUNTIFS (B2: B7, "= 130")
COUNTIF ja COUNTIFS Google'i arvutustabelites
Funktsioon COUNTIF & COUNTIFS töötab Google'i arvutustabelites täpselt samamoodi nagu Excelis:
