Ta vadnica prikazuje, kako uporabljatiExcel COUNTIF in COUNTIFS Functionov v Excelu za štetje podatkov, ki izpolnjujejo določena merila.
COUNTIF Pregled funkcije
Funkcijo COUNTIF v Excelu lahko uporabite za štetje celic, ki vsebujejo določeno vrednost, štetje celic, ki so večje ali enake vrednosti itd.
(Upoštevajte, kako so prikazani vnosi formule)
Sintaksa in argumenti funkcije COUNTIF:
= COUNTIF (obseg, merila)
obseg - Obseg celic za štetje.
merila - Merila, ki nadzorujejo, katere celice je treba šteti.
Kaj je funkcija COUNTIF?
Funkcija COUNTIF je ena starejših funkcij, ki se uporabljajo v preglednicah. Preprosto povedano, odlično skenira obseg in vam pove, koliko celic izpolnjuje ta pogoj. Pogledali bomo, kako funkcija deluje z besedilom, številkami in datumi; pa tudi nekatere druge situacije, ki bi se lahko pojavile.
Osnovni primer
Začnimo s pregledom tega seznama naključnih predmetov. Imamo nekaj številk, prazne celice in nekaj besedilnih nizov.
Če želite vedeti, koliko elementov se natančno ujema z merili, lahko kot drugi argument navedete, kaj želite iskati. Primer te formule bi lahko izgledal tako
= COUNTIF (A2: A9, "Apple")
Ta formula bi vrnila številko 3, saj so v našem območju 3 celice, ki izpolnjujejo ta merila. Druga možnost je, da namesto trdega kodiranja vrednosti uporabimo sklic na celico. Če bi v celico G2 zapisali »Apple«, bi lahko formulo spremenili v
= COUNTIF (A2: A9, G2)
Pri obravnavi števil je pomembno razlikovati med številkami in številkami, ki so shranjene kot besedilo. Na splošno pri pisanju formul ne postavljate narekovajev okoli številk. Če želite napisati formulo, ki preverja število 5, bi napisali
= COUNTIF (A2: A9, 5)
Nazadnje bi lahko preverili tudi prazne celice z uporabo niza dolžine. To formulo bi zapisali kot
= COUNTIF (A2: A9, "")
Opomba: Ta formula bo štela tako prazne celice kot tiste, ki so prazne kot rezultat formule, kot je funkcija IF.
Delne tekme
Funkcija COUNTIF podpira uporabo nadomestnih znakov "*" ali "?" V merilih. Poglejmo ta seznam okusnih pekovskih izdelkov:
Če želite poiskati vse postavke, ki se začnejo z Appleom, lahko napišemo »Apple*«. Torej, da dobimo odgovor 3, je naša formula v D2
= COUNTIF (A2: A5, "Apple*")
Opomba: Funkcija COUNTIF ne razlikuje velikih in malih črk, zato lahko po želji napišete tudi »jabolko*«.
Nazaj na našo pecivo bi morda želeli izvedeti tudi, koliko pite imamo na našem seznamu. To lahko ugotovimo tako, da namestimo nadomestni znak na začetek iskalnega izraza in napišemo
= COUNTIF (A2: A5, "*pita")
Ta formula daje rezultat 2.
Z nadomestnimi znaki lahko preverimo tudi celice z besedilom. Vrnimo se k prvotnemu seznamu podatkov.
Če želimo prešteti število celic, ki imajo vsaj nekaj besedila, torej ne štejemo številk ali prazne celice, lahko zapišemo
= COUNTIF (A2: A9, "*")
Vidite, da naša formula pravilno vrne rezultat 4.
Primerjalni operaterji v COUNTIF
Ko smo doslej pisali merila, smo namigovali, da je naš primerjalni operator »=«. Pravzaprav bi lahko zapisali tole:
= COUNTIF (A2: A9, "= Apple")
Kljub temu je to dodaten znak, zato ga običajno izpustimo. Vendar to pomeni, da lahko uporabite druge operaterje, na primer večje od, manjše ali neenako. Poglejmo ta seznam zabeleženih starosti:
Če bi radi vedeli, koliko otrok je starih vsaj 5 let, lahko napišemo primerjavo "več ali enako":
= COUNTIF (A2: A8, "> = 5")
Opomba: Primerjalni operater je vedno naveden kot besedilni niz, zato mora biti v narekovajih.
Podobno lahko preverite tudi elemente, ki so manjši od dane vrednosti. Če moramo ugotoviti, koliko jih je manj kot 8, jih lahko izpišemo
= COUNTIF (A2: A8, "<8")
To nam daje želeni rezultat 5. Zdaj pa si predstavljajmo, da grejo vsi 6-letni otroci na izlet. Koliko otrok bo ostalo? To lahko ugotovimo s primerjavo "ni enako", kot je ta:
= COUNTIF (A2: A8, "6")
Zdaj lahko hitro vidimo, da imamo 6 otrok, ki niso stari 6 let.
V teh dosedanjih primerjavah smo težko kodirali želene vrednosti. Uporabite lahko tudi sklic na celico. Trik je v tem, da morate primerjalni operater združiti z referenco celice. Recimo, da smo v celico C2 vnesli številko 7 in želimo, da naša formula v D2 pokaže, koliko otrok je mlajših od 7 let.
Naša formula v D2 mora izgledati tako:
= COUNTIF (A2: A8, "<" & C2)
Opomba: Pri pisanju teh formul bodite posebno pozorni na to, ali morate element postaviti v narekovaje ali zunaj. Operaterji so vedno znotraj narekovajev, sklice na celice so vedno zunaj narekovajev. Številke so zunaj, če natančno ujemate, znotraj pa, če izvajate primerjalni operater.
Delo z datumi
Videli smo, kako lahko podate besedilo ali številko kot merilo, kaj pa, ko moramo delati z datumi? Tu je kratek seznam vzorcev, s katerimi lahko delamo:
Če želimo šteti, koliko datumov je po 4. maju, moramo biti previdni. Računalniki shranjujejo datume kot številke, zato moramo poskrbeti, da računalnik uporablja pravo številko. Če bi zapisali to formulo, bi dobili pravilen rezultat?
= COUNTIF (A2: A9, "
Odgovor je "mogoče". Ker smo leto izpustili iz naših meril, bo računalnik domneval, da mislimo na tekoče leto. Če so vsi datumi, s katerimi delamo, za tekoče leto, bomo dobili pravilen odgovor. Če pa so datumi v prihodnosti, bi dobili napačen odgovor. Tudi ko se začne naslednje leto, bo ta formula vrnila drugačen rezultat. Zato se je treba tej sintaksi verjetno izogniti.
Ker je težko pravilno vnesti datume v formulo, je najbolje, da v celico napišete datum, ki ga želite uporabiti, nato pa lahko sklic na to celico uporabite v formuli COUNTIF. Zapišimo torej datum 7. maj 2020 v celico C2, nato pa lahko formulo postavimo v C4.
Formula v C4 je
= COUNTIF (A2: A9, "<" & C2)
Zdaj vemo, da je rezultat 7 pravilen, in odgovor se ne bo nepričakovano spremenil, če bomo odprli to preglednico kdaj v prihodnosti.
Preden zapustimo ta razdelek, je pri delu z datumi običajno uporabiti funkcijo TODAY. To lahko uporabimo tako kot referenco celice. Na primer, prejšnjo formulo bi lahko spremenili tako:
= COUNTIF (A2: A9, "<" & TODAY ())
Zdaj se bo naša formula še naprej posodabljala z napredovanjem v realnem času in imeli bomo nekaj elementov, ki so manjši kot danes.
Več meril in COUNTIFS
Prvotna funkcija COUNTIF se je izboljšala leta 2007, ko je izšla COUNTIFS. Sintaksa med obema je zelo podobna, slednja pa vam omogoča podajanje dodatnih obsegov in meril. COUNTIFS lahko preprosto uporabite v vseh okoliščinah, kjer obstaja COUNTIF. Dobro je vedeti, da obe funkciji obstajata.
Poglejmo to tabelo podatkov:
Če želite izvedeti, koliko ljudi ima plačne stopnje 1 do 2, lahko napišete povzetek COUNTIF funkcij, kot je ta:
= COUNTIF (B2: B7, "> = 1")-COUNTIF (B2: B7, "> 2")
Ta formula bo delovala, saj najdete vse, kar je nad 1, nato pa odštejete število zapisov, ki presegajo vašo mejno točko. Druga možnost je, da uporabite COUNTIFS, kot je ta:
= COUNTIFS (B2: B7, "> = 1", B2: B7, "<= 2")
Slednji je bolj intuitiven za branje, zato boste morda želeli uporabiti to pot. COUNTIFS je tudi močnejši, če morate upoštevati več stolpcev. Recimo, da želimo vedeti, koliko ljudi je v upravljanju in na plačni ravni 1. Tega ne morete storiti samo z COUNTIF; bi morali napisati
= COUNTIFS (A2: A7, "Management", B2: B7, 1)
Ta formula bi vam dala pravilen rezultat 2. Preden zapustimo ta razdelek, razmislimo o logiki tipa Or. Kaj pa, če bi želeli izvedeti, koliko ljudi je na vodstvu oz. Morali bi dodati nekaj COUNTIFS skupaj, vendar to lahko storite na dva načina. Preprostejši način je, da to napišete tako:
= COUNTIF (A2: A7, "HR")+COUNTIF (A2: A7, "Management")
Uporabite lahko tudi matriko in napišete to formulo matrike:
= SUM (COUNTIF (A2: A7, {"HR", "Upravljanje"}))
Opomba: Formule niza je treba potrditi s tipkama "Ctrl+Shift+Enter" in ne samo z "Enter".
Kako bo ta formula delovala, bo videlo, da ste kot vhod dali matriko. Tako bo rezultat izračunal za dve različni funkciji COUNTIF in jih shranil v matriko. Funkcija SUM bo nato združila vse rezultate v našem nizu, da bo ustvarila en izhod. Tako bo naša formula ocenjena tako:
= SUM (COUNTIF (A2: A7, {"HR", "Upravljanje"})) = SUM ({2, 3}) = 5
Preštejte edinstvene vrednosti
Zdaj, ko smo videli, kako uporabiti matriko s funkcijo COUNTIF, lahko naredimo še en korak naprej in nam pomagamo pri štetju, koliko edinstvenih vrednosti je v razponu. Najprej poglejmo še enkrat naš seznam oddelkov.
= SUM (1/COUNTIF (A2: A7, A2: A7))
Vidimo lahko, da je 6 celic vrednih podatkov, vendar obstajajo le 3 različne postavke. Da bi matematika uspela, potrebujemo, da je vsak element vreden 1/N, pri čemer je N število ponovitev elementa. Na primer, če bi bil vsak HR vreden le 1/2, potem ko bi jih sešteli, bi dobili štetje 1 za 1 edinstveno vrednost.
Nazaj na naš COUNTIF, ki je zasnovan tako, da ugotovi, kolikokrat se izdelek pojavi v obsegu. V D2 bomo zapisali formulo matrike
= SUM (1/COUNTIF (A2: A7, A2: A7))
Kako bo ta formula delovala, bo za vsako celico v razponu A2: A7 preverila, kolikokrat se pojavi. Z našim vzorcem bo to ustvarilo niz
{2, 2, 3, 3, 3, 1}
Nato vse te številke spremenimo v delčke. Zdaj izgleda naša matrika
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Ko vse to seštejemo, dobimo želeni rezultat 3.
Countif z dvema ali več pogoji - funkcija Countifs
Doslej smo delali samo s funkcijo COUNTIF. Funkcija COUNTIF lahko hkrati obravnava samo eno merilo. Če želite COUNTIF z več merili, morate uporabiti funkcijo COUNTIFS. COUNTIFS se obnaša točno tako kot COUNTIF. Dodate samo dodatna merila. Oglejmo si spodnji primer.
= COUNTIFS (B2: B7, "= 130")
COUNTIF & COUNTIFS v Google Preglednicah
Funkcija COUNTIF & COUNTIFS deluje v Google Preglednicah popolnoma enako kot v Excelu:
