Mitme kriteeriumi kasutamine Excelis COUNTIF ja COUNTIFS

Excelil on palju funktsioone, kus kasutaja peab tulemuse saamiseks määrama ühe või mitu kriteeriumi. Näiteks kui soovite loendada lahtreid mitme kriteeriumi alusel, saate Excelis kasutada funktsioone COUNTIF või COUNTIFS.

See õpetus hõlmab erinevaid viise ühe või mitme kriteeriumi kasutamiseks Exceli funktsioonides COUNTIF ja COUNTIFS.

Kuigi ma keskendun selles õpetuses peamiselt funktsioonidele COUNTIF ja COUNTIFS, saab kõiki neid näiteid kasutada ka muudes Exceli funktsioonides, mille sisendiks on mitu kriteeriumi (nt SUMIF, SUMIFS, AVERAGEIF ja AVERAGEIFS).

Exceli COUNTIF ja COUNTIFS funktsioonide sissejuhatus

Esmalt võtame käsu funktsioonide COUNTIF ja COUNTIFS kasutamisest Excelis.

Funktsioon Excel COUNTIF (võtab ühe kriteeriumi)

Funktsioon Excel COUNTIF sobib kõige paremini olukordades, kus soovite loendada lahtreid ühe kriteeriumi alusel. Kui soovite loendada mitme kriteeriumi alusel, kasutage funktsiooni COUNTIFS.

Süntaks

= COUNTIF (vahemik, kriteeriumid)

Sisendargumendid

• vahemik - lahtrite vahemik, mida soovite loendada.
• kriteeriumid - kriteeriumid, mida tuleb raku loendamiseks hinnata rakkude vahemiku alusel.

Funktsioon Excel COUNTIFS (võtab mitu kriteeriumi)

Funktsioon Excel COUNTIFS sobib kõige paremini olukordadeks, kui soovite loendada lahtreid mitme kriteeriumi alusel.

Süntaks

= COUNTIFS (kriteeriumivahemik1, kriteerium1, [kriteeriumivahemik2, kriteerium2]…)

Sisendargumendid

• kriteeriumide_vahemik1 - Lahtrite vahemik, mille kriteeriume soovite hinnata1
• kriteeriumid1 - kriteeriumid, mida soovite kriteeriumi_vahemik1 jaoks hinnata, et määrata, milliseid lahtreid loendada.
• [kriteeriumivahemik2] - Lahtrite vahemik, mille kriteeriume soovite hinnata2.
• [kriteerium2] - kriteeriumid, mida soovite kriteeriumi_vahemik2 jaoks hinnata, et määrata, milliseid lahtreid loendada.

Nüüd vaatame mõningaid näiteid mitme kriteeriumi kasutamisest Exceli COUNTIF -funktsioonides.

NUMBER kriteeriumi kasutamine Excelis COUNTIF -funktsioonides

#1 Loendage lahtrid, kui kriteeriumid on väärtusele VÕRDISED

Lahtrite arvu arvutamiseks, kus argumentide kriteeriumid on võrdsed määratud väärtusega, saate kriteeriumid otse sisestada või kasutada kriteeriume sisaldavat lahtriviidet.

Allpool on näide, kus loendame lahtreid, mis sisaldavad numbrit 9 (mis tähendab, et kriteeriumi argument on võrdne 9 -ga). Siin on valem:

= COUNTIF ($ B $ 2: $ B $ 11, D3)

Ülaltoodud näites (pildil) on kriteeriumid lahtris D3. Kriteeriumid saate sisestada ka otse valemisse. Näiteks võite kasutada ka järgmist:

= COUNTIF ($ B $ 2: $ B $ 11,9)

#2 Loendage lahtreid, kui kriteeriumid on suuremad kui väärtus

Määratud väärtusest suurema väärtusega lahtrite arvu saamiseks kasutame operaatorist suuremat (“>”). Võiksime seda kasutada otse valemis või kasutada lahtrite viiteid, millel on kriteeriumid.

Kui kasutame Exceli kriteeriumides operaatorit, peame selle topeltjutumärkide vahele panema. Näiteks kui kriteeriumid on suuremad kui 10, peame kriteeriumiks sisestama “> 10” (vt pilti allpool):

Siin on valem:

= COUNTIF ($ B $ 2: $ B $ 11, "> 10")

Teil võib olla ka kriteeriumid lahtris ja kasutada kriteeriumina lahtri viidet. Sellisel juhul ei pea te kriteeriume topeltjutumärkidesse panema:

= COUNTIF ($ B $ 2: $ B $ 11, D3)

Samuti võib juhtuda, kui soovite, et kriteeriumid oleksid lahtris, kuid te ei soovi seda operaatoriga. Näiteks võite soovida, et lahtris D3 oleks number 10 ja mitte> 10.

Sel juhul peate looma kriteeriumiargumendi, mis koosneb operaatori ja lahtri viitest (vt allolevat pilti):

= COUNTIF ($ B $ 2: $ B $ 11, ">" & D3)

MÄRKUS. Operaatori ja lahtri viite kombineerimisel on operaator alati jutumärkides. Operaator ja lahtri viide on ühendatud tähemärgiga (&).

#3 Loendage rakke, kui kriteeriumid on väärtusest väiksemad

Määratud väärtusest väiksema väärtusega lahtrite arvu saamiseks kasutame vähem kui operaatorit („<“). Võiksime seda kasutada otse valemis või kasutada lahtrit, millel on kriteeriumid.

Kui kasutame Exceli kriteeriumides operaatorit, peame selle topeltjutumärkide vahele panema. Näiteks kui kriteeriumiks on see, et arv peaks olema väiksem kui 5, peame kriteeriumiks sisestama “<5” (vt pilti allpool):

= COUNTIF ($ B $ 2: $ B $ 11, "<5")

Teil võib olla ka kriteeriumid lahtris ja kasutada kriteeriumina lahtri viidet. Sellisel juhul ei pea te kriteeriume topeltjutumärkidesse panema (vt pilti allpool):

= COUNTIF ($ B $ 2: $ B $ 11, D3)

Samuti võib juhtuda, et soovite, et kriteeriumid oleksid lahtris, kuid te ei soovi seda operaatoriga. Näiteks võite soovida, et lahtris D3 oleks number 5 ja mitte <5.

Sel juhul peate looma kriteeriumi argumendi, mis koosneb operaatori ja lahtri viitest:

= COUNTIF ($ B $ 2: $ B $ 11, "<" & D3)

MÄRKUS. Operaatori ja lahtri viite kombineerimisel on operaator alati jutumärkides. Operaator ja lahtri viide on ühendatud tähemärgiga (&).

#4 Loendage lahtreid mitme kriteeriumiga - kahe väärtuse vahel

Väärtuste loendamiseks kahe väärtuse vahel peame funktsioonis COUNTIF kasutama mitmeid kriteeriume.

Siin on kaks meetodit selle tegemiseks.

MEETOD 1: COUNTIFS funktsiooni kasutamine

Funktsioon COUNTIFS saab käsitleda argumendina mitut kriteeriumi ja loeb lahtrid ainult siis, kui kõik kriteeriumid on tõesed. Lahtrite loendamiseks, mille väärtused on kahe määratud väärtuse vahel (näiteks 5–10), saame kasutada järgmist funktsiooni COUNTIFS:

= COUNTIFS ($ B $ 2: $ B $ 11, "> 5", $ B $ 2: $ B $ 11, "<10")

MÄRKUS. Ülaltoodud valem ei arvesta lahtreid, mis sisaldavad 5 või 10. Kui soovite need lahtrid lisada, kasutage operaatoreid, mis on suuremad kui (> =) ja väiksemad kui (<=). Siin on valem:

= COUNTIFS ($ B $ 2: $ B $ 11, "> = 5", $ B $ 2: $ B $ 11, "<= 10")

Neid kriteeriume saate ka lahtrites kasutada ja kasutada kriteeriumina lahtri viidet. Sellisel juhul ei pea te kriteeriume topeltjutumärkidesse panema (vt allolevat pilti):

Võite kasutada ka lahtriviidete ja operaatorite kombinatsiooni (kus operaator sisestatakse otse valemisse). Kui ühendate operaatori ja lahtri viite, on operaator alati topeltjutumärkides. Operaator ja lahtri viide on ühendatud tähemärgiga (&).

MEETOD 2: Kahe COUNTIF -funktsiooni kasutamine

Kui teil on mitu kriteeriumi, saate kasutada COUNTIFS -i või luua COUNTIF -funktsioonide kombinatsiooni. Allolev valem teeks samuti sama:

= COUNTIF ($ B $ 2: $ B $ 11, "> 5")-COUNTIF ($ B $ 2: $ B $ 11, "> 10")

Ülaltoodud valemist leiame kõigepealt rakkude arvu, mille väärtus on suurem kui 5, ja lahutame lahtrite arvu, mille väärtus on suurem kui 10. See annaks meile tulemuseks 5 (mis on rakkude arv, millel on väärtused üle 5 ja väiksemad kui 10).

Kui soovite, et valem sisaldaks nii 5 kui ka 10, kasutage selle asemel järgmist valemit:

= COUNTIF ($ B $ 2: $ B $ 11, "> = 5")-COUNTIF ($ B $ 2: $ B $ 11, "> 10")

Kui soovite, et valem jätaks loendamisest välja nii 5 kui ka 10, kasutage järgmist valemit:

= COUNTIF ($ B $ 2: $ B $ 11, "> = 5")-COUNTIF ($ B $ 2: $ B $ 11, "> 10")-COUNTIF ($ B $ 2: $ B $ 11,10)

Saate need kriteeriumid lahtrites kasutada ja kasutada lahtrite viiteid või kasutada operaatorite ja lahtriviidete kombinatsiooni.

TEXT -kriteeriumide kasutamine Exceli funktsioonides

#1 Loendage lahtrid, kui kriteeriumid on määratud tekstile VÕRDNE

Määratud teksti täpse vaste sisaldavate lahtrite loendamiseks saame seda teksti lihtsalt kriteeriumidena kasutada. Näiteks kui tahan andmestikus (näidatud pildil allpool), kui tahan loendada kõik lahtrid nimega Joe, saan kasutada järgmist valemit:

= COUNTIF ($ B $ 2: $ B $ 11, "Joe")

Kuna see on tekstistring, pean teksti kriteeriumid topelt jutumärkidesse panema.

Teil võib olla ka kriteeriumid lahtris ja seejärel kasutada seda lahtri viidet (nagu allpool näidatud):

= COUNTIF ($ B $ 2: $ B $ 11, E3)

MÄRKUS. Kui kriteeriumide või kriteeriumide vahemikus on esi-/lõppruume, võite saada valesid tulemusi. Enne nende valemite kasutamist puhastage andmed kindlasti.

#2 Loendage lahtrid, kui kriteeriumid EI OLE võrdsed määratud tekstiga

Sarnaselt sellele, mida nägime ülaltoodud näites, saate loendada ka lahtreid, mis ei sisalda määratud teksti. Selleks peame kasutama mitte võrdset operaatoriga ().

Oletame, et soovite loendada kõik lahtrid, mis ei sisalda nime JOE, siin on valem, mis seda teeb:

= COUNTIF ($ B $ 2: $ B $ 11, "Joe")

Teil võib olla ka kriteeriumid lahtris ja kasutada kriteeriumina lahtri viidet. Sellisel juhul ei pea te kriteeriume topeltjutumärkidesse panema (vt allolevat pilti):

= COUNTIF ($ B $ 2: $ B $ 11, E3)

Samuti võib juhtuda, kui soovite, et kriteeriumid oleksid lahtris, kuid ei soovi seda operaatoriga. Näiteks võite soovida, et lahter D3 kannab nime Joe ja mitte Joe.

Sellisel juhul peate looma kriteeriumiargumendi, mis koosneb operaatori ja lahtri viitest (vt allolevat pilti):

= COUNTIF ($ B $ 2: $ B $ 11, ”” ja E3)

Kui ühendate operaatori ja lahtri viite, on operaator alati topeltjutumärkides. Operaator ja lahtri viide on ühendatud tähemärgiga (&).

DATE kriteeriumide kasutamine Excelis COUNTIF ja COUNTIFS funktsioonides

Excel salvestab kuupäeva ja kellaaja numbritena. Seega saame seda kasutada samamoodi nagu numbreid.

#1 Loendage lahtreid, kui kriteeriumid on määratud kuupäevaks võrdsed

Määratud kuupäeva sisaldavate lahtrite arvu saamiseks kasutaksime kuupäevaga võrdset operaatorit (=).

Kuupäeva kasutamiseks soovitan kasutada funktsiooni KUUPÄEV, kuna see vabastab kuupäeva väärtuse kõikidest võimalustest. Näiteks kui soovin kasutada kuupäeva 1. september 2015, võin kasutada funktsiooni DATE, nagu allpool näidatud.

= KUUPÄEV (2015,9,1)

See valem tagastaks sama kuupäeva vaatamata piirkondlikele erinevustele. Näiteks 01.09.2015 oleks USA kuupäeva süntaksi järgi 1. september 2015 ja Ühendkuningriigi kuupäeva süntaksi järgi 9. jaanuar 2015. See valem naaseks aga alati 1. septembril 2105.

Siin on valem kuupäeva sisaldavate lahtrite arvu loendamiseks 02-09-2015:

= COUNTIF ($ A $ 2: $ A $ 11, DATE (2015,9,2))

#2 Loendage rakke, kui kriteeriumid on enne või pärast määratud kuupäeva

Lahtrite loendamiseks, mis sisaldavad kuupäeva enne või pärast määratud kuupäeva, saame kasutada operaatoritest väiksemat/suuremat.

Näiteks kui ma tahan loendada kõik lahtrid, mis sisaldavad kuupäeva, mis on pärast 02. septembrit 2015, saan kasutada järgmist valemit:

= COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,2))

Samamoodi saate loendada ka lahtrite arvu enne määratud kuupäeva. Kui soovite loendusse lisada kuupäeva, kasutage operaatorit „võrdne” koos operaatoriga „suurem/väiksem”.

Võite kasutada ka kuupäeva sisaldavat lahtriviidet. Sellisel juhul peate operaatori (kahekordse jutumärgi sees) kombineerima kuupäevaga, kasutades tähemärki (&).

Vaadake allpool näidet:

= COUNTIF ($ A $ 2: $ A $ 11, ">" & F3)

#3 Loendage lahtreid mitme kriteeriumiga - kahe kuupäeva vahel

Väärtuste loendamiseks kahe väärtuse vahel peame funktsioonis COUNTIF kasutama mitmeid kriteeriume.

Seda saame teha kahe meetodi abil - üks COUNTIFS -funktsioon või kaks COUNTIF -funktsiooni.

MEETOD 1: COUNTIFS funktsiooni kasutamine

Funktsioon COUNTIFS võib argumentidena võtta mitu kriteeriumi ja loendab lahtrid ainult siis, kui kõik kriteeriumid on tõesed. Kahe määratud kuupäeva (nt 2. september ja 7. september) vaheliste lahtrite loendamiseks saame kasutada järgmist funktsiooni COUNTIFS.

= COUNTIFS ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,2), $ A $ 2: $ A $ 11, "<" & DATE (2015,9,7))

Ülaltoodud valem ei loe lahtreid, mis sisaldavad määratud kuupäevi. Kui soovite lisada ka need kuupäevad, kasutage operaatoreid, mis on suuremad kui (> =) ja väiksemad kui (<=). Siin on valem:

= COUNTIFS ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<=” & DATE (2015,9,7))

Samuti saate kuupäevad lahtrisse sisestada ja kasutada kriteeriumina lahtri viidet. Sellisel juhul ei saa operaatoril olla kuupäeva lahtrites. Peate valemisse käsitsi lisama operaatoreid (topeltjutumärkides) ja lisama lahtri viite, kasutades & -märki (&). Vaata allolevat pilti:

= COUNTIFS ($ A $ 2: $ A $ 11, ">" & F3, $ A $ 2: $ A $ 11, "<" & G3)

MEETOD 2: COUNTIF -funktsioonide kasutamine

Kui teil on mitu kriteeriumi, saate kasutada ühte funktsiooni COUNTIFS või luua kahe COUNTIF -funktsiooni kombinatsiooni. Allolev valem aitaks ka asja:

= COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,7))

Ülaltoodud valemist leiame kõigepealt rakkude arvu, mille kuupäev on pärast 2. septembrit, ja lahutame lahtrite arvu kuupäevadega pärast 7. septembrit. See annaks meile tulemuse 7 (mis on kuupäevadega lahtrite arv) pärast 2. septembrit ja 7. septembril või enne seda).

Kui te ei soovi, et valemit arvestatakse nii 2. septembril kui ka 7. septembril, kasutage selle asemel järgmist valemit:

= COUNTIF ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7))

Kui soovite mõlemad kuupäevad loendamisest välja jätta, kasutage järgmist valemit:

= COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,7) -COUNTIF ($ A $ 2: $ 11, DATE (2015,9,7)))

Samuti saate lahtrites määrata kriteeriumide kuupäevad ja kasutada lahtrite viiteid (koos kahekordse jutumärgiga operaatoritega, mis on ühendatud ampersandi abil).

WILDCARDI CHARACTERS kasutamine kriteeriumides funktsioonides COUNTIF & COUNTIFS

Excelis on kolm metamärki:

1. * (tärn) - See tähistab suvalist arvu tähemärke. Näiteks ex* võib tähendada silmapaistvust, silmapaistvust, eeskuju, ekspertiisi jne.
2. ? (küsimärk) - See kujutab endast ühte tegelast. Näiteks Tr? Mp võib tähendada Trumpi või Trampi.
3. ~ (tilde) - Seda kasutatakse tekstis metamärgi (~, *,?) Tuvastamiseks.

Funktsiooni COUNTIF saab kasutada metamärkidega, et loendada lahtreid, kui muu sisseehitatud loendamisfunktsioon ebaõnnestub. Oletame näiteks, et teil on allpool näidatud andmekogum.

Võtame nüüd erinevaid näiteid:

#1 Loendage teksti sisaldavaid lahtreid

Teksti sisaldavate lahtrite loendamiseks võime kasutada metamärki * (tärn). Kuna tärn tähistab suvalist arvu tähemärke, loeks see kõik lahtrid, mis sisaldavad teksti. Siin on valem:

= COUNTIFS ($ C $ 2: $ C $ 11, "*")

Märkus. Ülaltoodud valem eirab lahtreid, mis sisaldavad numbreid, tühje lahtreid ja loogilisi väärtusi, kuid loendab, et lahtrid sisaldavad apostroofi (ja seega tühjaks) või lahtreid, mis sisaldavad tühja stringi (= ””), mis võidakse tagastada valemi osa.

Siin on üksikasjalik õpetus juhtumite käsitlemiseks, kus on tühi string või apostroof.

Siin on üksikasjalik õpetus juhtumite käsitlemiseks, kus on tühjad stringid või apostrofid.

Allpool on video, mis selgitab lahtrite loendamise erinevaid stsenaariume koos tekstiga.

#2 Loendage mittetühje lahtreid

Kui mõtlete COUNTA funktsiooni kasutamisele, mõelge uuesti.

Proovige ja see võib teile ebaõnnestuda. COUNTA loeb ka lahtri, mis sisaldab tühja stringi (valemitega tagastatakse sageli kui "" "või kui inimesed sisestavad lahtrisse ainult apostroofi). Lahtrid, mis sisaldavad tühje stringe, näevad tühjad välja, kuid ei ole, ja seega loendatakse funktsioon COUNTA.

COUNTA loeb ka lahtri, mis sisaldab tühja stringi (valemitega tagastatakse see sageli kui "" "või kui inimesed sisestavad lahtrisse ainult apostroofi). Lahtrid, mis sisaldavad tühje stringe, näevad tühjad välja, kuid ei ole, ja seega loendatakse funktsioon COUNTA.

Nii et kui kasutate valemit = COUNTA (A1: A11), tagastab see 11, samas kui see peaks tagastama 10.

Siin on parandus:

= COUNTIF ($ A $ 1: $ A $ 11, ”?*”)+COUNT ($ A $ 1: $ A $ 11)+SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11))

Mõistame seda valemit, jaotades selle:

• COUNTIF ($ N $ 8: $ N $ 18, ”?*”) - see valemi osa tagastab 5. See hõlmab kõiki lahtreid, milles on tekstimärk. A? tähistab ühte märki ja * tähistab suvalist arvu märke. Seega sunnib kriteeriumide kombinatsioon?* Excelit loendama lahtreid, milles on vähemalt üks tekstimärk.
• COUNT ($ A $ 1: $ A $ 11) - see loendab kõik lahtrid, mis sisaldavad numbreid. Ülaltoodud näites tagastab see 3.
• SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11) - loendab kõik lahtrid, mis sisaldavad loogilisi väärtusi. Ülaltoodud näites tagastab see 2.

#3 Loendage lahtreid, mis sisaldavad konkreetset teksti

Oletame, et tahame loendada kõik lahtrid, kus müügiesindaja nimi algab tähega J. Seda on lihtne saavutada, kasutades funktsioonis COUNTIF metamärki. Siin on valem:

= COUNTIFS ($ C $ 2: $ C $ 11, "J*")

Kriteerium J* määrab, et lahtri tekst peaks algama J -ga ja see võib sisaldada suvalist arvu märke.

Kui soovite tähestikku sisaldavaid lahtreid lugeda kõikjal tekstis, lisage see mõlemale poolele tärniga. Näiteks kui soovite loendada lahtreid, mis sisaldavad tähestikku „a”, kasutage kriteeriumina * a *.

See artikkel on minu teiste artiklitega võrreldes ebatavaliselt pikk. Loodetavasti olete seda nautinud. Andke mulle oma mõtetest teada, jättes kommentaari.

Kasulikud võivad olla ka järgmised Exceli õpetused:

• Loendage Excelis sõnade arv.
• Lahtrite loendamine Exceli taustavärvi alusel.
• Kuidas Excelis veergu kokku võtta (5 tõesti lihtsat viisi)