Exceli COUNTIF-funktsioon võimaldab teil loendada lahtrite arvu, mis vastavad teatud kriteeriumidele või tingimustele antud vahemikus.
Funktsioon COUNTIF on üks Exceli statistilistest funktsioonidest, mis on funktsioonide COUNT ja IF või funktsiooni COUNTA kombinatsioon. Kui seda kasutatakse valemis, loendab funktsioon lahtrite arvu, mis vastavad konkreetsetele kriteeriumidele või tingimustele samas või mitmes vahemikus. Funktsioon COUNTIF aitab loendada lahtreid, mis sisaldavad konkreetsetele kriteeriumidele vastavat teksti, numbreid või kuupäevi.
Lahtreid saate lugeda Exceli funktsioonide COUNTIF või COUNTIFS abil. Funktsioonide COUNTIF ja COUNTIFS erinevus seisneb selles, et COUNTIF-i kasutatakse lahtrite loendamiseks, mis vastavad ühele kriteeriumile ühes vahemikus, samas kui COUNTIFS loendab rakke, mis vastavad mitmele tingimusele samas või mitmes vahemikus.
See artikkel näitab teile, kuidas kasutada Excelis kahte funktsiooni COUNTIF ja COUNTIFS.
Exceli COUNTIF-funktsioon
Funktsioon COUNTIF võimaldab teil sooritada andmete loendamist konkreetse kriteeriumi või tingimuse alusel. Funktsioonis kasutatav tingimus töötab osalise sobitamise jaoks loogiliste operaatoritega (, , =, >=, <=) ja metamärkidega (*, ?).
Funktsiooni COUNTIF süntaks
Funktsiooni COUNTIF struktuur on järgmine:
=COUNTIF(vahemik,kriteeriumid)Parameetrid:
ulatus– Loendatavate rakkude vahemik.kriteeriumid– Tingimus määrab, millised lahtrid tuleks määratud vahemikus loendisse kaasata. Kriteeriumid võivad olla arvväärtus, tekst, viide lahtri aadressile või võrrand.
Funktsiooni COUNTIF kasutamine arvväärtuste loendamiseks
Nagu eespool arutasime, määratlevad funktsiooni COUNTIF kriteeriumid (teine argument) tingimuse, mis ütleb funktsioonile, milliseid rakke loendada.
See funktsioon aitab teil lugeda lahtrite arvu väärtustega, mis vastavad loogilistele tingimustele, nagu võrdne, suurem, väiksem või mitte võrdne määratud väärtusega jne.
Allolevas näites loendab valem lahtreid, mis sisaldavad väärtust 5 (kriteeriumid). Saate valemisse otse sisestada '5 või kasutada viidet lahtri aadressile, millel on väärtus (allolevas näites lahter D2).
=LOENDIF(B2:B11,D2)
Ülaltoodud valem loendab lahtrite arvu lahtrivahemikus (B2:B11), mis sisaldavad väärtust, mis on võrdne lahtri D2 väärtusega.
Järgmine valem loeb lahtrid, mille väärtus on väiksem kui 5.
=LOENDIF(B2:B11,"<5")Tehja vähem kui (<) käsib valemil loendama lahtrid, mille väärtus on väiksem kui 5 vahemikus B2:B11. Kui kasutate tehtemärki tingimusel, lisage see jutumärkidesse (“”).
Mõnikord, kui soovite lahtreid loendada, uurides neid lahtris oleva kriteeriumi (väärtuse) alusel. Sellistel juhtudel määrake kriteerium, ühendades operaatori ja lahtri viite. Kui teete seda, peate lisama võrdlustehise jutumärkidesse ("") ja seejärel panema võrdlustehitaja ja lahtri viite vahele märgi (&).
=COUNTIF(B2:B11,">="&D2)
Alloleval pildil on mõned näidisvalemid ja nende tulemus.
Funktsiooni COUNTIF kasutamine tekstiväärtuste loendamiseks
Teatud tekstistringe sisaldavate lahtrite loendamiseks kasutage seda tekstistringi kriteeriumi argumendina või lahtrit, mis sisaldab tekstistringi. Näiteks kui tahame allolevas tabelis loendada kõik lahtrid vahemikus (B21:D27), mille tekstiväärtus on lahtris B21 (sam), saame kasutada järgmist valemit:
=LOENDIF(B21:D27,B21)
Nagu me varem arutasime, võiksime kasutada valemis otse teksti „sam” või kasutada lahtriviidet, millel on kriteeriumid (B21). Kui tekstistringi kasutatakse Exceli valemis, tuleb see alati lisada jutumärkidesse (“”).
=LOENDIF(B21:D27"sam")
Lahtrite loendamiseks, mis ei sisalda määratud teksti, kasutage allolevat valemit:
=COUNTIF(B21:D27,""&B21)Lisage kindlasti sõna "ei ole võrdne" "" operaator jutumärkides.
Kui kasutate valemis otse teksti "sam", peate ühendama operaatori "" ja tekstistringi ("sam") jutumärkides.
=LOENDIF(B21:D27"sam") 
Metamärkide kasutamine Exceli funktsioonis COUNTIF (osaline sobitamine)
Konkreetset sõna, fraasi või tähti sisaldavate lahtrite loendamiseks saate kasutada metamärke sisaldavat valemit COUNTIF. Exceli funktsioonis COUNTIF saate kasutada kolme metamärki.
*(tärn) – seda kasutatakse suvalise arvu algus- ja lõpumärkide/tähtedega lahtrite loendamiseks. (nt St* võib tähendada Starki, Toonekurge, Stacks jne.?(küsimärk) – seda kasutatakse mis tahes ühe märgiga lahtrite leidmiseks. (nt St?rk võib tähendada Starki või Toonekurge.~(tilde) – seda kasutatakse küsimärki või tärni (~, *, ?) sisaldavate lahtrite arvu leidmiseks ja loendamiseks tekstis.
Lahtrite loendamine, mis algavad või lõppevad teatud tähemärkidega
Lahtrite loendamiseks, mis algavad või lõpevad konkreetse tekstiga ja lahtris suvalise arvu teiste tähemärkidega, kasutage funktsiooni COUNTIF teises argumendis metamärki (*).
Kasutage järgmist näidisvalemit:
=LOENDIF(A1:A10,"A*") – A-ga algavate rakkude loendamiseks.
=COUNTIF(A19:A28,"*er") – lahtrite loendamiseks, mis lõpevad tähtedega "er".
=COUNTIF(A2:A12,"*QLD*") – lahtrite loendamiseks, mis sisaldavad teksti "QLD" mis tahes tekstistringis.
A ? tähistab täpselt ühte märki, kasutage seda metamärki allolevas funktsioonis COUNTIF, et loendada lahtrite arv, mis sisaldavad täpselt +1 märki, kus "?' kasutatakse.
=COUNTIF(A1:A10,"Par?s")
Tühjade ja mittetühjade lahtrite loendamine funktsiooniga COUNTIF
Valem COUNTIF on abiks ka tühjade või mittetühjade lahtrite arvu loendamisel antud vahemikus.
Loendage mittetühjad rakud
Kui soovite loendada ainult lahtreid, mis sisaldavad "teksti" väärtusi, kasutage allolevat valemit. See valem käsitleb kuupäevade ja numbritega lahtreid tühjana ega kaasa neid loendusse.
=COUNTIF(A1:B12"*")Metamärk * sobib ainult tekstiväärtustega ja tagastab kõigi antud vahemikus olevate tekstiväärtuste arvu.
Kui soovite loendada kõik mittetühjad lahtrid antud vahemikus, proovige seda valemit:
=COUNTIF(A1:B12"")
Loendage tühjad rakud
Kui soovite loendada tühje lahtreid teatud vahemikus, kasutage funktsiooni COUNTIF koos * metamärk ja operaator kriteeriumi argumendis tühjade lahtrite loendamiseks.
See valem loeb lahtrid, mis ei sisalda tekstiväärtusi:
=COUNTIF(A1:B12,"&"*")Alates * metamärk sobib mis tahes tekstiväärtusega, loeb ülaltoodud valem kõik lahtrid, mis ei ole võrdsed *. See loeb ka kuupäevade ja numbritega lahtrid tühjaks.
Kõigi tühikute loendamiseks (kõik väärtustüübid):
=COUNTIF(A1:B12"")See funktsioon loendab ainult tühje lahtreid vahemikus.
Funktsiooni COUNTIF kasutamine kuupäevade loendamiseks
Saate lugeda kuupäevadega lahtreid (sama, mis arvukriteeriumide puhul), mis vastavad loogilisele tingimusele või viitelahtris määratud kuupäevale või kuupäevale.
Määratud kuupäeva (05-05-2020) sisaldavate lahtrite loendamiseks kasutaksime järgmist valemit:
=COUNTIF(B2:B10,"05-05-2020")
Saate määrata ka kuupäeva erinevates vormingutes kriteeriumina funktsioonis COUNTIF, nagu allpool näidatud:
Kui soovite loendada lahtreid, mis sisaldavad kuupäevi enne või pärast teatud kuupäeva, kasutage tehteid vähem kui (enne) või suurem kui (pärast) koos konkreetse kuupäeva või lahtri viitega.
=COUNTIF(B2:B10,">=05/05/2020")
Saate kasutada ka kuupäeva sisaldavat lahtriviidet, kombineerides selle operaatoriga (jutumärkides).
Lahtrite arvu loendamiseks vahemikus A2:A14, mille kuupäev on E3-s enne kuupäeva, kasutage allolevat valemit, kus operaator suurem kui (<) tähendab enne kuupäeva E3-s.
=COUNTIF(A2:A14,"<"&E3)
Mõned näidisvalemid ja nende tulemus:
Loendamiskuupäev praegusel kuupäeval
Praeguse kuupäevaga lahtrite loendamiseks saate kombineerida funktsiooni COUNTIF konkreetsete Exceli kuupäevafunktsioonidega, nt TODAY().
=COUNTIF(A2:A14,">"&TÄNA())See funktsioon loeb kõik kuupäevad alates tänasest vahemikus (A2:A14).
Loendage kuupäevi konkreetse kuupäevavahemiku vahel
Kui soovite lugeda kõiki kuupäevi kahe kuupäeva vahel, peate valemis kasutama kahte kriteeriumi.
Seda saame teha kahe meetodi abil: funktsioonid COUNTIF ja COUNTIFS.
Exceli COUNTIF funktsiooni kasutamine
Kõigi kahe määratud kuupäeva vahele jäävate kuupäevade loendamiseks peate kasutama kahte funktsiooni COUNTIF.
Kuupäevade loendamiseks vahemikus '09-02-2020' ja '20-08-2021' kasutage järgmist valemit:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">20-08-2021")See valem leiab esmalt lahtrite arvu, mille kuupäev on pärast 2. veebruari, ja lahutab lahtrite arvu, mille kuupäev on pärast 20. augustit. Nüüd saame numbri. lahtritest, mille kuupäevad on pärast 2. veebruari ja 20. augustil või varem (arv on 9).
Kui te ei soovi, et valem arvestaks nii 2. veebruari kui ka 20. augustit, kasutage selle asemel järgmist valemit:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">=20-08-2021")Lihtsalt asendage operaator ">" teise kriteeriumiga ">=".
Exceli funktsiooni COUNTIFS kasutamine
Funktsioon COUNTIFS toetab ka mitut kriteeriumi ja erinevalt funktsioonist COUNTIF loeb see rakke alles pärast seda, kui kõik tingimused on täidetud. Kui soovite loendada lahtreid kõigi kahe määratud kuupäeva vahelise kuupäevaga, sisestage järgmine valem:
=COUNTIFS(A2:A14,">"&A11,A2:A14,"<"&A10)
Kui soovite loendusse lisada ka määratud kuupäevad, kasutage tehteid „>=” ja „<=”. Minge selle valemiga:
=COUNTIFS(A2:A14,">=09-02-2020",A2:A14,"<=20-08-2021")Selle näite puhul kasutasime lahtri viite asemel kuupäeva otse kriteeriumides.
COUNTIF-i ja COUNTIFS-i käsitlemine mitme kriteeriumiga Excelis
Funktsiooni COUNTIF kasutatakse enamasti ühe kriteeriumiga (tingimusega) lahtrite loendamiseks ühes vahemikus. Kuid saate siiski kasutada COUNTIF-i, et loendada lahtreid, mis vastavad mitmele sama vahemiku tingimusele. Funktsiooni COUNTIFS saab aga kasutada rakkude loendamiseks, mis vastavad mitmele tingimusele samas või erinevates vahemikes.
Kuidas loendada numbreid vahemikus
Saate lugeda kahe määratud arvu vahelisi numbreid sisaldavaid lahtreid, kasutades kahte funktsiooni: COUNTIF ja COUNTIFS.
COUNTIF kahe numbri vahel olevate numbrite loendamiseks
Üks mitme kriteeriumiga funktsiooni COUNTIF levinumaid kasutusviise on kahe määratud arvu vahel olevate arvude loendamine, nt. 10-st suuremate, kuid 50-st väiksemate arvude loendamiseks. Vahemikus olevate arvude loendamiseks ühendage kaks või enam funktsiooni COUNTIF ühes valemis. Näitame teile, kuidas.
Oletame, et soovite loendada lahtreid vahemikus B2:B9, kus väärtus on suurem kui 10 ja väiksem kui 21 (välja arvatud 10 ja 21), kasutage järgmist valemit:
=LOENDIF(B2:B14,">10")-COUNTIF(B2:B14,">=21")Kahe arvu erinevus leitakse, lahutades ühe valemi teisest. Esimene valem loendab numbreid, mis on suuremad kui 10 (mis on 7), teine valem tagastab 21-st suuremate või sellega võrdsete arvude arvu (mis on 4) ja teise valemi tulemus lahutatakse esimesest valemist (7) -4), et saada kahe arvu (3) vahele jäävate arvude arv.
Kui soovite loendada lahtreid, mille arv on suurem kui 10 ja väiksem kui 21 vahemikus B2:B14, sealhulgas numbrid 10 ja 21, kasutage järgmist valemit:
=LOENDIF(B2:B14,">=10")-COUNTIF(B2:B14,">21")
COUNTIFS numbrite loendamiseks 2 numbri vahel
Lahtrites B2 kuni B9 olevate arvude loendamiseks vahemikus 10 kuni 21 (välja arvatud 10 ja 21), kasutage järgmist valemit:
=COUNTIFS(B2:B14,">10",B2:B14,"<21")
10 ja 21 loendusse kaasamiseks kasutage valemites lihtsalt operaatorite "suurem kui" asemel "suurem või võrdne" (>=) ja "väiksem või võrdne" (<=) .
COUNTIFS mitme kriteeriumiga lahtrite loendamiseks (JA kriteeriumid)
Funktsioon COUNTIFS on COUNTIF-funktsiooni mitmuse vaste, mis loendab rakke kahe või enama kriteeriumi alusel samas või mitmes vahemikus. Seda nimetatakse AND-loogikaks, kuna funktsioon on loodud rakkude loendamiseks ainult siis, kui kõik antud tingimused on TRUE.
Näiteks tahame teada saada, mitu korda (rakkude arv) seda leiba (väärtus veerus A) on müüdud alla 5 (väärtus veerus C).
Saame kasutada seda valemit:
=COUNTIFS(A2:A14,"leib",C2:C14,"<5")
COUNTIF mitme kriteeriumiga lahtrite loendamiseks (VÕI kriteeriumid)
Kui soovite loendada lahtrite arvu, mis vastavad mitmele kriteeriumile samas vahemikus, ühendage kaks või enam funktsiooni COUNTIF kokku. Näiteks kui soovite teada saada, mitu korda korratakse "leib" või "juust" määratud vahemikus (A2:A14), kasutage allolevat valemit:
=LOENDIF(A2:A14"leib")+COUNTIF(A2:A14,"juust")See valem loendab rakke, mille puhul vähemalt üks tingimus on TÕENE. Sellepärast nimetatakse seda "OR-loogikaks".
Kui soovite igas funktsioonis hinnata rohkem kui ühte kriteeriumi, on parem kasutada COUNTIFS-i asemel COUNTIFS. Allolevas näites tahame leiva jaoks saada olekute „Tellitud” ja „Tarnitud” arvu, seega kasutaksime järgmist valemit:
=COUNTIFS(A2:A14,"Leib",C2:C14"Tellitud")+COUNTIFS(A2:A14"Leib",C2:C14"Tarnitud")
Loodame, et see lihtne, kuid üsna pikk õpetus annab teile ülevaate COUNTIF- ja COUNTIF-funktsioonide kasutamisest Excelis.