Excelova funkcija COUNTIF vam omogoča, da preštejete število celic, ki izpolnjujejo določene kriterije ali pogoje v danem obsegu.
Funkcija COUNTIF je ena od statističnih funkcij v Excelu, ki je kombinacija funkcij COUNT in IF ali funkcije COUNTA. Ko se uporablja v formuli, funkcija šteje število celic, ki ustrezajo določenim merilom ali pogojem v istem ali več obsegih. Funkcija COUNTIF pomaga pri štetju celic, ki vsebujejo besedilo, številke ali datume, ki izpolnjujejo določena merila.
Celice lahko preštejete s funkcijami COUNTIF ali COUNTIFS v Excelu. Razlika med funkcijama COUNTIF in COUNTIFS je v tem, da se COUNTIF uporablja za štetje celic, ki izpolnjujejo en kriterij v enem obsegu, medtem ko COUNTIFS šteje celice, ki izpolnjujejo več pogojev v istem ali več obsegih.
Ta članek vam bo pokazal, kako uporabljati dve funkciji COUNTIF in COUNTIFS v Excelu.
Excelova funkcija COUNTIF
Funkcija COUNTIF vam omogoča izvajanje štetja podatkov na podlagi določenega merila ali pogoja. Pogoj, uporabljen v funkciji, deluje z logičnimi operatorji (, , =, >=, <=) in nadomestnimi znaki (*, ?) za delno ujemanje.
Sintaksa funkcije COUNTIF
Struktura funkcije COUNTIF je:
=COUNTIF(obseg,merila)Parametri:
obseg– Obseg celic za štetje.meril– Pogoj določa, katere celice naj bodo vključene v štetje v določenem obsegu. Merila so lahko številčna vrednost, besedilo, sklic na naslov celice ali enačba.
Uporaba funkcije COUNTIF za štetje številskih vrednosti
Kot smo razpravljali zgoraj, kriterij (drugi argument) v funkciji COUNTIF definira pogoj, ki pove funkciji, katere celice naj šteje.
Ta funkcija vam pomaga prešteti število celic z vrednostmi, ki izpolnjujejo logične pogoje, kot so enaka, večja, manjša ali ne enaka določeni vrednosti itd.
V spodnjem primeru formula šteje celice, ki vsebujejo vrednost 5 (merila). V formulo lahko neposredno vstavite '5 ali uporabite sklic na naslov celice, ki ima vrednost (celica D2 v spodnjem primeru).
=COUNTIF(B2:B11,D2)
Zgornja formula šteje število celic v obsegu celic (B2:B11), ki vsebujejo vrednost, ki je enaka vrednosti v celici D2.
Naslednja formula šteje celice, katerih vrednost je manjša od 5.
=COUNTIF(B2:B11,"<5")Operater manj kot (<) pove formuli, da šteje celice z vrednostjo manjšo od '5' v obsegu B2:B11. Kadar koli uporabljate operator v stanju, ga zaključite v dvojne narekovaje (“”).
Včasih, ko želite prešteti celice tako, da jih pregledate glede na merilo (vrednost) v celici. V takih primerih naredite merilo tako, da združite operaterja in sklic na celico. Ko to storite, morate primerjalni operator zapreti v dvojne narekovaje (“”), nato pa med operatorjem primerjave in sklicem na celico postaviti ampersand (&).
=COUNTIF(B2:B11,">="&D2)
Spodnja slika prikazuje nekaj primerov formul in njihov rezultat.
Uporaba funkcije COUNTIF za štetje besedilnih vrednosti
Če želite prešteti celice, ki vsebujejo določene besedilne nize, uporabite ta besedilni niz kot argument merila ali celico, ki vsebuje besedilni niz. Na primer, v spodnji tabeli, če želimo prešteti vse celice v obsegu (B21:D27) z vrednostjo besedila v celici B21 (sam) v njem, lahko uporabimo naslednjo formulo:
=COUNTIF(B21:D27,B21)
Kot smo že razpravljali, lahko uporabimo besedilo "sam" neposredno v formuli ali uporabimo sklic na celico, ki ima merila (B21). Besedilni niz mora biti vedno obdan v dvojnih narekovajih (“”), ko se uporablja v formuli v excelu.
=COUNTIF(B21:D27,"sam")
Če želite prešteti celice, ki ne vsebujejo določenega besedila, uporabite spodnjo formulo:
=COUNTIF(B21:D27,""&B21)Poskrbite, da boste priložili 'ni enako' "" operater v dvojnih narekovajih.
Če uporabljate besedilo "sam" neposredno v formuli, morate operator "" in besedilni niz priložiti skupaj ("sam") v dvojnih narekovajih.
=COUNTIF(B21:D27,"sam") 
Uporaba nadomestnih znakov v funkciji Excel COUNTIF (delno ujemanje)
Formulo COUNTIF z nadomestnimi znaki lahko uporabite za štetje celic, ki vsebujejo določeno besedo, besedno zvezo ali črke. V funkciji Excel COUNTIF lahko uporabite tri nadomestne znake:
*(zvezdica) – Uporablja se za štetje celic s poljubnim številom začetnih in končnih znakov/črk. (npr. St* lahko pomeni Stark, Štorklja, Stacks itd.?(vprašaj) – Uporablja se za iskanje celic s katerim koli enim znakom. (npr. St?rk bi lahko pomenil Stark ali Štorklja.~(tilda) – Uporablja se za iskanje in štetje celic, ki vsebujejo vprašaj ali znak zvezdice (~, *, ?) v besedilu.
Štetje celic, ki se začnejo ali končajo z določenimi znaki
Če želite prešteti celice, ki se začnejo ali končajo z določenim besedilom s poljubnim številom drugih znakov v celici, uporabite nadomestni znak zvezdice (*) v drugem argumentu funkcije COUNTIF.
Uporabite te vzorčne formule:
=COUNTIF(A1:A10,"A*") – za štetje celic, ki se začnejo z “A”.
=COUNTIF(A19:A28,"*er") – za preštevanje števila celic, ki se končajo z znaki "er".
=COUNTIF(A2:A12,"*QLD*") – za štetje celic, ki vsebujejo besedilo »QLD« kjer koli v besedilnem nizu.
A ? predstavlja natanko en znak, uporabite ta nadomestni znak v spodnji funkciji COUNTIF, da preštejete število celic, ki vsebujejo točno +1 znak, kjer je »?' se uporablja.
=COUNTIF(A1:A10,"Par?s")
Štetje praznih in nepraznih celic s funkcijo COUNTIF
Formula COUNTIF je koristna tudi, ko gre za štetje števila praznih ali nepraznih celic v danem obsegu.
Preštejte neprazne celice
Če želite šteti samo celice, ki vsebujejo kakršne koli vrednosti »besedila«, uporabite spodnjo formulo. Ta formula obravnava celice z datumi in številkami kot prazne celice in jih ne bo vključila v štetje.
=COUNTIF(A1:B12,"*")Nadomestni znak * ujema samo z besedilnimi vrednostmi in vrne število vseh besedilnih vrednosti v danem obsegu.
Če želite prešteti vse neprazne celice v določenem obsegu, poskusite s to formulo:
=COUNTIF(A1:B12,"")
Preštejte prazne celice
Če želite šteti prazne celice v določenem obsegu, uporabite funkcijo COUNTIF z * nadomestni znak in operator v argumentu kriterijev za štetje praznih celic.
Ta formula šteje celice, ki ne vsebujejo nobenih besedilnih vrednosti:
=COUNTIF(A1:B12,""&"*")Od * nadomestni znak se ujema s katero koli besedilno vrednostjo, zgornja formula bo preštela vse celice, ki jim niso enake *. Šteje celice z datumi in številkami tudi kot prazne.
Če želite prešteti vse praznine (vse vrste vrednosti):
=COUNTIF(A1:B12,"")Ta funkcija šteje samo prazne celice v obsegu.
Uporaba funkcije COUNTIF za štetje datumov
Celice lahko preštejete z datumi (enako kot s številskimi merili), ki izpolnjujejo logični pogoj ali določen datum ali datum v referenčni celici.
Za preštevanje celic, ki vsebujejo določen datum (05-05-2020), bi uporabili to formulo:
=COUNTIF(B2:B10,"05-05-2020")
Kot merilo v funkciji COUNTIF lahko določite tudi datum v različnih oblikah, kot je prikazano spodaj:
Če želite šteti celice, ki vsebujejo datume pred ali po določenem datumu, uporabite operatorje manj kot (pred) ali večje kot (potem) skupaj z določenim datumom ali sklicem na celico.
=COUNTIF(B2:B10,">=05/05/2020")
Uporabite lahko tudi sklic na celico, ki vsebuje datum, tako da ga združite z operatorjem (v dvojnih narekovajih).
Če želite prešteti število celic v obsegu A2:A14 z datumom pred datumom v E3, uporabite spodnjo formulo, kjer operator večje od (<) pomeni pred datumom v E3.
=COUNTIF(A2:A14,"<"&E3)
Nekaj primerov formul in njihov rezultat:
Datum štetja na podlagi trenutnega datuma
Funkcijo COUNTIF lahko kombinirate s posebnimi funkcijami za datum v Excelu, to je TODAY(), da preštejete celice, ki imajo trenutni datum.
=COUNTIF(A2:A14,">"&DANES())Ta funkcija šteje vse datume od danes v obsegu (A2:A14).
Preštejte datume med določenim časovnim obdobjem
Če želite šteti vse datume med dvema datumoma, morate v formuli uporabiti dva merila.
To lahko storimo z dvema metodama: funkcijama COUNTIF in COUNTIFS.
Uporaba Excelove funkcije COUNTIF
Za štetje vseh datumov med dvema določenima datumoma morate uporabiti dve funkciji COUNTIF.
Če želite prešteti datume med '09-02-2020' in '20-08-2021', uporabite to formulo:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">20-08-2021")Ta formula najprej poišče število celic, ki imajo datum po 2. februarju, in odšteje število celic z datumi po 20. avgustu. Zdaj dobimo št. celic, ki imajo datume po 2. februarju in 20. avgustu ali pred njim (število je 9).
Če ne želite, da formula šteje tako 2. februar kot 20. avgust, uporabite to formulo:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">=20-08-2021")Samo zamenjajte operator '>' z '>=' v drugem merilu.
Uporaba funkcije Excel COUNTIFS
Funkcija COUNTIFS podpira tudi več kriterijev in za razliko od funkcije COUNTIF šteje celice šele, ko so izpolnjeni vsi pogoji. Če želite prešteti celice z vsemi datumi med dvema določenima datumoma, vnesite to formulo:
=COUNTIFS(A2:A14,">"&A11,A2:A14,"<"&A10)
Če želite v štetje vključiti tudi določene datume, uporabite operatorja '>=' in '<='. Tukaj pojdite s to formulo:
=COUNTIFS(A2:A14,">=09-02-2020",A2:A14,"<=20-08-2021")Za ta primer smo namesto sklicevanja na celico uporabili datum neposredno v merilih.
Kako ravnati s COUNTIF in COUNTIFS z več merili v Excelu
Funkcija COUNTIF se večinoma uporablja za štetje celic z enim merilom (pogojem) v enem obsegu. Še vedno pa lahko uporabite COUNTIF za štetje celic, ki se ujemajo z več pogoji v istem obsegu. Vendar pa lahko funkcijo COUNTIFS uporabite za štetje celic, ki izpolnjujejo več pogojev v enakih ali različnih obsegih.
Kako šteti številke znotraj razpona
Celice, ki vsebujejo številke, lahko preštejete med dvema določenima številkama z dvema funkcijama: COUNTIF in COUNTIFS.
COUNTIF za štetje številk med dvema številkama
Ena od pogostih uporab funkcije COUNTIF z več kriteriji je štetje številk med dvema določenima številkama, npr. za štetje števil, ki so večja od 10, vendar manjša od 50. Za štetje števil v obsegu združite dve ali več funkcij COUNTIF v eno formulo. Naj vam pokažemo, kako.
Recimo, da želite šteti celice v obsegu B2:B9, kjer je vrednost večja od 10 in manjša od 21 (brez 10 in 21), uporabite to formulo:
=COUNTIF(B2:B14,">10")-COUNTIF(B2:B14,">=21")Razliko med dvema številkama najdemo tako, da eno formulo odštejemo od druge. Prva formula šteje števila, večja od 10 (kar je 7), druga formula vrne število števil, večjih ali enakih 21 (kar je 4), rezultat druge formule pa se odšteje od prve formule (7 -4), da dobite število številk med dvema številkama (3).
Če želite prešteti celice s številom, ki je večje od 10 in manjše od 21 v območju B2:B14, vključno s številkama 10 in 21, uporabite to formulo:
=COUNTIF(B2:B14,">=10")-COUNTIF(B2:B14,">21")
COUNTIFS za štetje številk med 2 številkama
Za štetje številk med 10 in 21 (razen 10 in 21) v celicah B2 do B9 uporabite to formulo:
=COUNTIFS(B2:B14,">10",B2:B14,"<21")
Če želite v štetje vključiti 10 in 21, v formulah preprosto uporabite operatorje »večje ali enako« (>=) namesto »večje kot« in »manjše od ali enako« (<=) namesto operatorjev »manj kot«. .
COUNTIFS za štetje celic z več merili (In kriteriji)
Funkcija COUNTIFS je množinski dvojnik funkcije COUNTIF, ki šteje celice na podlagi dveh ali več meril v istem ali več obsegih. Znana je kot logika IN, ker je funkcija narejena za štetje celic samo, če so vsi dani pogoji TRUE.
Na primer, želimo ugotoviti, kolikokrat (število celic) je bil ta kruh (vrednost v stolpcu A) prodan manj kot 5 (vrednost v stolpcu C).
Uporabimo lahko to formulo:
=COUNTIFS(A2:A14,"kruh",C2:C14,"<5")
COUNTIF za štetje celic z več merili (ali merili)
Če želite prešteti število celic, ki izpolnjujejo več meril v istem obsegu, združite dve ali več funkcij COUNTIF. Na primer, če želite izvedeti, kolikokrat se v določenem območju (A2:A14) ponovi 'Kruh' ali 'Sir', uporabite spodnjo formulo:
=COUNTIF(A2:A14,"Kruh")+COUNTIF(A2:A14,"Sir")Ta formula šteje celice, za katere je vsaj eden od pogojev TRUE. Zato se imenuje 'ALI logika'.
Če želite v vsaki funkciji oceniti več kot eno merilo, je bolje uporabiti COUNTIFS namesto COUNTIF. V spodnjem primeru želimo dobiti število statusov »Naročeno« in »Dostavljeno« za »Kruh«, zato bi uporabili to formulo:
=COUNTIFS(A2:A14,"Kruh",C2:C14,"Naročeno")+COUNTIFS(A2:A14,"Kruh",C2:C14,"Dostavljeno")
Upamo, da vam bo ta preprosta, a precej dolga vadnica dala idejo o uporabi funkcij COUNTIF in COUNTIF v Excelu.