Useiden ehtojen käyttäminen Excelissä COUNTIF ja COUNTIFS

Excelissä on monia toimintoja, joissa käyttäjän on määritettävä yksi tai useampi kriteeri saadakseen tuloksen. Jos haluat esimerkiksi laskea solut useiden ehtojen perusteella, voit käyttää Excelissä COUNTIF- tai COUNTIFS -toimintoja.

Tämä opetusohjelma kattaa useita tapoja käyttää yhtä tai useampaa ehtoa Excelin COUNTIF- ja COUNTIFS -toiminnoissa.

Vaikka keskityn tässä opetusohjelmassa ensisijaisesti COUNTIF- ja COUNTIFS -toimintoihin, kaikkia näitä esimerkkejä voidaan käyttää myös muissa Excel -toiminnoissa, jotka käyttävät useita ehtoja syötteinä (kuten SUMIF, SUMIFS, AVERAGEIF ja AVERAGEIFS).

Johdanto Excelin COUNTIF- ja COUNTIFS -toimintoihin

Otetaan ensin käsiin COUNTIF- ja COUNTIFS -toimintojen käyttäminen Excelissä.

Excel COUNTIF -toiminto (ottaa yhden kriteerin)

Excel COUNTIF -toiminto sopii parhaiten tilanteisiin, joissa haluat laskea solut yhden kriteerin perusteella. Jos haluat laskea useiden kriteerien perusteella, käytä COUNTIFS -funktiota.

Syntaksi

= COUNTIF (alue, ehdot)

Syöttöargumentit

• alue - solualue, jonka haluat laskea.
• kriteeri - kriteerit, jotka on arvioitava solualueen perusteella, jotta solu voidaan laskea.

Excel COUNTIFS -toiminto (ottaa useita ehtoja)

Excel COUNTIFS -toiminto sopii parhaiten tilanteisiin, joissa haluat laskea solut useiden ehtojen perusteella.

Syntaksi

= COUNTIFS (ehtoalue_alue1, ehdot1, [kriteerit_alue2, ehdot2]…)

Syöttöargumentit

• kriteerit_alue 1 - Solualue, jota haluat arvioida kriteerien perusteella1.
• kriteerit 1 - kriteerit, jotka haluat arvioida kriteereille_alue1 määrittääksesi, mitkä solut lasketaan.
• [ehto_alue2] - Solualue, jota haluat arvioida ehtojen perusteella2.
• [kriteerit2] - kriteerit, jotka haluat arvioida ehtojen_alue2 määrittämiseksi, mitkä solut lasketaan.

Katsotaanpa nyt esimerkkejä useiden ehtojen käyttämisestä Excelin COUNTIF -funktioissa.

NUMBER kriteerin käyttäminen Excel COUNTIF -funktioissa

#1 Laske solut, kun kriteerit ovat yhdenvertaisia ​​arvoon

Jos haluat laskea solujen määrän, joissa ehto -argumentti on sama kuin määritetty arvo, voit joko syöttää ehdot suoraan tai käyttää ehtoja sisältävää soluviittausta.

Alla on esimerkki, jossa laskemme solut, jotka sisältävät numeron 9 (mikä tarkoittaa, että ehto -argumentti on 9). Tässä on kaava:

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

Yllä olevassa esimerkissä (kuvassa) kriteerit ovat solussa D3. Voit myös syöttää ehdot suoraan kaavaan. Voit esimerkiksi käyttää myös:

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

#2 Laske solut, kun kriteerit ovat suurempia kuin arvo

Saadaksemme solujen määrän, joiden arvo on suurempi kuin määritetty arvo, käytämme operaattoria suurempi (">"). Voisimme joko käyttää sitä suoraan kaavassa tai käyttää soluviittausta, jolla on kriteerit.

Aina kun käytämme operaattoria Excelin kriteereissä, meidän on lisättävä se lainausmerkkeihin. Jos esimerkiksi kriteerit ovat suurempia kuin 10, meidän on syötettävä kriteereiksi "> 10" (katso kuva alla):

Tässä on kaava:

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

Voit myös käyttää ehtoja solussa ja käyttää soluviittausta kriteerinä. Tässä tapauksessa EI tarvitse laittaa ehtoja lainausmerkkeihin:

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

Voi myös olla tapaus, jossa haluat ehtojen olevan solussa, mutta et halua sitä operaattorin kanssa. Voit esimerkiksi haluta, että solussa D3 on numero 10 eikä> 10.

Siinä tapauksessa sinun on luotava ehtoargumentti, joka on yhdistelmä operaattorin ja soluviittauksen (katso kuva alla):

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

HUOMAUTUS: Kun yhdistät operaattorin ja soluviittauksen, operaattori on aina lainausmerkeissä. Operaattori ja soluviittaus yhdistetään merkillä (&).

#3 Laske solut, kun kriteerit ovat pienempiä kuin arvo

Saadaksemme solujen määrän, joiden arvo on pienempi kuin määritetty arvo, käytämme vähemmän kuin operaattoria ("<"). Voisimme joko käyttää sitä suoraan kaavassa tai käyttää soluviittausta, jolla on kriteerit.

Aina kun käytämme operaattoria Excelin kriteereissä, meidän on lisättävä se lainausmerkkeihin. Jos esimerkiksi kriteeri on, että luvun tulee olla pienempi kuin 5, meidän on syötettävä kriteeriksi "<5" (katso kuva alla):

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

Voit myös käyttää ehtoja solussa ja käyttää soluviittausta kriteerinä. Tässä tapauksessa sinun EI tarvitse laittaa kriteereitä lainausmerkkeihin (katso kuva alla):

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

Lisäksi voi olla tapaus, jossa haluat ehtojen olevan solussa, mutta et halua sitä operaattorin kanssa. Voit esimerkiksi haluta, että solussa D3 on numero 5 eikä <5.

Siinä tapauksessa sinun on luotava ehtoargumentti, joka on yhdistelmä operaattorin ja soluviittauksen:

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

HUOMAUTUS: Kun yhdistät operaattorin ja soluviittauksen, operaattori on aina lainausmerkeissä. Operaattori ja soluviittaus yhdistetään merkillä (&).

#4 Laske soluja useilla kriteereillä - kahden arvon välillä

Jotta saisimme laskea arvoja kahden arvon välillä, meidän on käytettävä useita ehtoja COUNTIF -funktiossa.

Tässä on kaksi tapaa tehdä tämä:

MENETELMÄ 1: COUNTIFS -toiminnon käyttäminen

COUNTIFS -funktio voi käsitellä useita ehtoja argumentteina ja laskee solut vain, kun kaikki ehdot ovat TOSI. Jos haluat laskea solut, joiden arvot ovat kahden määritetyn arvon välillä (esimerkiksi 5 ja 10), voimme käyttää seuraavaa COUNTIFS -funktiota:

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

HUOMAUTUS: Yllä oleva kaava ei laske soluja, jotka sisältävät 5 tai 10. Jos haluat sisällyttää nämä solut, käytä suurempia kuin yhtä kuin (> =) ja pienempiä kuin (<=) operaattoreita. Tässä on kaava:

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

Voit myös käyttää näitä ehtoja soluissa ja käyttää soluviittausta kriteerinä. Tässä tapauksessa sinun ei tarvitse laittaa kriteereitä lainausmerkkeihin (katso kuva alla):

Voit myös käyttää soluviittausten ja operaattoreiden yhdistelmää (jossa operaattori syötetään suoraan kaavaan). Kun yhdistät operaattorin ja soluviittauksen, operaattori on aina lainausmerkeissä. Operaattori ja soluviittaus yhdistetään merkillä (&).

MENETELMÄ 2: Kahden COUNTIF -toiminnon käyttäminen

Jos sinulla on useita ehtoja, voit käyttää COUNTIFS -toimintoa tai luoda COUNTIF -toimintojen yhdistelmän. Alla oleva kaava tekisi myös saman:

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

Yllä olevasta kaavasta löydämme ensin niiden solujen lukumäärän, joiden arvo on suurempi kuin 5, ja vähennämme niiden solujen määrän, joiden arvo on suurempi kuin 10. Tämä antaisi meille tuloksen 5 (mikä on solujen lukumäärä, joilla on arvot yli 5 ja alle 10).

Jos haluat, että kaava sisältää sekä 5 että 10, käytä seuraavaa kaavaa:

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

Jos haluat, että kaava sulkee sekä 5 että 10 pois laskennasta, käytä seuraavaa kaavaa:

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

Voit käyttää näitä ehtoja soluissa ja käyttää soluviittauksia tai voit käyttää operaattoreiden ja soluviittausten yhdistelmää.

TEKSTIKriteerien käyttäminen Excel -toiminnoissa

#1 Laske solut, kun kriteerit ovat TASAISET määritetylle tekstille

Voimme laskea solut, jotka sisältävät tarkan vastaavuuden määritetystä tekstistä, voimme käyttää kyseistä tekstiä kriteerinä. Esimerkiksi jos haluan laskea kaikki solut, joissa on nimi Joe, tietojoukossa (näkyy alla kuvassa), voin käyttää alla olevaa kaavaa:

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

Koska tämä on tekstijono, minun on laitettava tekstiehdot lainausmerkkeihin.

Voit myös asettaa kriteerit soluun ja käyttää sitten soluviittausta (kuten alla):

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

HUOMAUTUS: Voit saada vääriä tuloksia, jos ehto- tai kriteerialueella on edessä/takana välilyöntejä. Muista puhdistaa tiedot ennen näiden kaavojen käyttöä.

#2 Laske solut, kun kriteerit EIVÄT OLE Tietyllä tekstillä

Kuten edellä olevassa esimerkissä, voit myös laskea solut, jotka eivät sisällä määritettyä tekstiä. Tätä varten meidän on käytettävä funktiota, joka ei ole yhtä kuin operaattori ().

Oletetaan, että haluat laskea kaikki solut, jotka eivät sisällä nimeä JOE, tässä on kaava, joka tekee sen:

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

Voit myös asettaa kriteerit soluun ja käyttää soluviittausta kriteerinä. Tässä tapauksessa sinun EI tarvitse laittaa kriteereitä lainausmerkkeihin (katso kuva alla):

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

Voi myös olla tapaus, jossa haluat ehtojen olevan solussa, mutta et halua sitä operaattorin kanssa. Voit esimerkiksi haluta, että solulla D3 on nimi Joe eikä Joe.

Siinä tapauksessa sinun on luotava ehtoargumentti, joka on yhdistelmä operaattorin ja soluviittauksen (katso kuva alla):

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

Kun yhdistät operaattorin ja soluviittauksen, operaattori on aina lainausmerkeissä. Operaattori ja soluviittaus yhdistetään merkillä (&).

DATE -ehtojen käyttäminen Excelissä COUNTIF- ja COUNTIFS -funktioissa

Excel tallentaa päivämäärän ja kellonajan numeroina. Joten voimme käyttää sitä samalla tavalla kuin numeroita.

#1 Laske solut, kun kriteerit ovat TASAISIA tietylle päivämäärälle

Määritetyn päivämäärän sisältävien solujen lukumäärän saamiseksi käytämme yhtä kuin operaattoria (=) päivämäärän kanssa.

Päivämäärän käyttämiseksi suosittelen käyttämään DATE -funktiota, koska se poistaa kaikki mahdolliset virheet päivämäärän arvossa. Jos esimerkiksi haluan käyttää päivämäärää 1.9.2015, voin käyttää DATE -funktiota alla olevan kuvan mukaisesti:

= PÄIVÄYS (2015,9,1)

Tämä kaava palauttaisi saman päivämäärän alueellisista eroista huolimatta. Esimerkiksi 1.9.2015 olisi 1. syyskuuta 2015 Yhdysvaltain päivämäärän syntaksin mukaan ja 9. tammikuuta 2015 Yhdistyneen kuningaskunnan päivämäärän syntaksin mukaan. Tämä kaava palauttaa kuitenkin aina 1. syyskuuta 2105.

Tässä on kaava päivämäärää sisältävien solujen lukumäärän laskemiseksi: 02-09-2015:

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

#2 Laske solut, kun kriteerit ovat ENNEN tai JÄLKEEN tiettyä päivämäärää

Voimme laskea solut, jotka sisältävät päivämäärän ennen tai jälkeen tietyn päivämäärän, voimme käyttää operaattoreita pienempää/suurempaa.

Jos haluan esimerkiksi laskea kaikki solut, jotka sisältävät päivämäärän, joka on 2. syyskuuta 2015 jälkeen, voin käyttää kaavaa:

= COUNTIF ($ A $ 2: $ A $ 11, ">" & PÄIVÄYS (2015,9,2))

Voit myös laskea solujen määrän ennen tiettyä päivämäärää. Jos haluat sisällyttää laskemiseen päivämäärän, käytä operaattoria "yhtä kuin" ja "suurempi/pienempi" -operaattoria.

Voit myös käyttää soluviittausta, joka sisältää päivämäärän. Tässä tapauksessa sinun on yhdistettävä operaattori (lainausmerkeissä) päivämäärään käyttämällä & -merkkiä (&).

Katso esimerkki alla:

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

#3 Laske soluja useilla kriteereillä - kahden päivämäärän välillä

Saadaksemme arvojen lukumäärän kahden arvon välillä meidän on käytettävä useita ehtoja LASKE -funktiossa.

Voimme tehdä tämän käyttämällä kahta menetelmää - yhtä COUNTIFS -toimintoa tai kahta COUNTIF -toimintoa.

MENETELMÄ 1: COUNTIFS -toiminnon käyttäminen

COUNTIFS -funktio voi ottaa argumentteina useita ehtoja ja laskee solut vain, kun kaikki ehdot ovat TOSI. Jos haluat laskea solut, joiden arvot ovat kahden määritetyn päivämäärän välillä (esimerkiksi 2. syyskuuta ja 7. syyskuuta), voimme käyttää seuraavaa LASKELLA -funktiota:

= LASKUT ($ A $ 2: $ A $ 11, ">" & PÄIVÄMÄÄRÄ (2015,9,2), $ A $ 2: $ A $ 11, "<" & PÄIVÄYS (2015,9,7))

Yllä oleva kaava ei laske soluja, jotka sisältävät määritetyt päivämäärät. Jos haluat sisällyttää myös nämä päivämäärät, käytä suurempia kuin yhtä kuin (> =) ja pienempiä kuin (<=) operaattoreita. Tässä on kaava:

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

Voit myös merkitä päivämäärät soluun ja käyttää soluviittausta kriteerinä. Tässä tapauksessa et voi asettaa operaattoria päivämäärään soluissa. Sinun on lisättävä manuaalisesti operaattorit kaavaan (lainausmerkeissä) ja lisättävä soluviittaus käyttämällä & -merkkiä (&). Katso alla oleva kuva:

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

MENETELMÄ 2: COUNTIF -toimintojen käyttäminen

Jos sinulla on useita ehtoja, voit joko käyttää yhtä COUNTIFS -funktiota tai luoda kahden COUNTIF -funktion yhdistelmän. Myös alla oleva kaava tekisi tempun:

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

Yllä olevasta kaavasta löydetään ensin niiden solujen määrä, joiden päivämäärä on 2. syyskuuta, ja vähennämme solujen lukumäärän, joiden päivämäärät ovat syyskuun 7. päivän jälkeen. Tämä antaa meille tuloksen 7 (mikä on solujen määrä, joilla on päivämäärät) 2. syyskuuta jälkeen ja 7. syyskuuta tai sitä ennen).

Jos et halua, että kaava lasketaan sekä 2. syyskuuta että 7. syyskuuta, käytä sen sijaan seuraavaa kaavaa:

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

Jos haluat sulkea molemmat päivämäärät pois laskemisesta, käytä seuraavaa kaavaa:

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

Voit myös määrittää ehtojen päivämäärät soluissa ja käyttää soluviittauksia (yhdessä lainausmerkkien operaattoreiden kanssa, jotka on liitetty käyttämällä ampersandia).

WILDCARD -CHARACTERS -funktion käyttäminen COUNTIF & COUNTIFS -toimintojen ehdoissa

Excelissä on kolme jokerimerkkiä:

1. * (tähti) - Se edustaa mitä tahansa määrää merkkejä. Esimerkiksi ex* voi tarkoittaa Excel, Excel, esimerkki, asiantuntija jne.
2. ? (kysymysmerkki) - Se edustaa yhtä hahmoa. Esimerkiksi Tr? Mp voi tarkoittaa Trumpia tai Trampia.
3. ~ (tilde) - Sitä käytetään tunnistamaan tekstissä yleismerkki (~, *,?).

Voit käyttää COUNTIF -toimintoa yleismerkkien kanssa solujen laskemiseen, kun muu sisäänrakennettu laskutoiminto epäonnistuu. Oletetaan esimerkiksi, että sinulla on alla esitetyn kaltainen tietojoukko:

Otetaan nyt erilaisia ​​esimerkkejä:

#1 Laske tekstiä sisältävät solut

Jos haluat laskea soluja, joissa on tekstiä, voimme käyttää jokerimerkkiä * (tähti). Koska tähti edustaa mitä tahansa määrää merkkejä, se laskee kaikki solut, joissa on tekstiä. Tässä on kaava:

= LASKETTA ($ C $ 2: $ C $ 11, ”*”)

Huomautus: Yllä oleva kaava jättää huomiotta solut, jotka sisältävät numeroita, tyhjiä soluja ja loogisia arvoja, mutta laskisi, että solut sisältävät heittomäärän (ja näyttävät siis tyhjiltä) tai solut, jotka sisältävät tyhjän merkkijonon (= ””), jotka on ehkä palautettu osa kaavaa.

Tässä on yksityiskohtainen opetus tapausten käsittelystä, joissa on tyhjä merkkijono tai heittomerkki.

Tässä on yksityiskohtainen opetus tapausten käsittelystä, joissa on tyhjiä merkkijonoja tai heittomerkkejä.

Alla on video, joka selittää erilaisia ​​skenaarioita solujen laskemisesta, joissa on tekstiä.

#2 Laske ei-tyhjät solut

Jos aiot käyttää COUNTA -toimintoa, mieti uudelleen.

Kokeile ja se saattaa epäonnistua. COUNTA laskee myös solun, joka sisältää tyhjän merkkijonon (usein kaavojen palauttama muodossa = ”” tai kun ihmiset kirjoittavat soluun vain heittomerkinnän). Solut, jotka sisältävät tyhjiä merkkijonoja, näyttävät tyhjiltä, ​​mutta eivät ole, ja näin COUNTA -funktio laskee ne.

COUNTA laskee myös solun, joka sisältää tyhjän merkkijonon (usein kaavojen palauttama muodossa = ”” tai kun ihmiset kirjoittavat soluun vain heittomerkinnän). Solut, jotka sisältävät tyhjiä merkkijonoja, näyttävät tyhjiltä, ​​mutta eivät ole, ja näin COUNTA -funktio laskee ne.

Joten jos käytät kaavaa = COUNTA (A1: A11), se palauttaa 11 ja sen pitäisi palauttaa 10.

Tässä on korjaus:

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

Ymmärrämme tämän kaavan rikkomalla sen:

• COUNTIF ($ N $ 8: $ N $ 18, ”?*”) - Tämä kaavan osa palauttaa 5. Tämä sisältää kaikki solut, joissa on tekstimerkki. A? edustaa yhtä merkkiä ja * edustaa mitä tahansa merkkiä. Siksi ehtojen yhdistelmä?* Pakottaa Excelin laskemaan solut, joissa on vähintään yksi tekstimerkki.
• COUNT ($ A $ 1: $ A $ 11) - Tämä laskee kaikki solut, jotka sisältävät numeroita. Yllä olevassa esimerkissä se palauttaa arvon 3.
• SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11) - Tämä laskee kaikki solut, jotka sisältävät loogisia arvoja. Yllä olevassa esimerkissä se palauttaa 2.

#3 Laske solut, jotka sisältävät tiettyä tekstiä

Oletetaan, että haluamme laskea kaikki solut, joissa myyntiedustajan nimi alkaa J: llä. Tämä voidaan helposti saavuttaa käyttämällä yleismerkkiä COUNTIF -funktiossa. Tässä on kaava:

= LASKETTA ($ C $ 2: $ C $ 11, ”J*”)

Ehto J* määrittää, että solun tekstin tulee alkaa J: llä ja se voi sisältää minkä tahansa määrän merkkejä.

Jos haluat laskea solut, jotka sisältävät aakkoset missä tahansa tekstin kohdassa, täytä se tähdellä molemmilla puolilla. Jos haluat esimerkiksi laskea solut, joissa on aakkoset ”a”, käytä * a * kriteerinä.

Tämä artikkeli on epätavallisen pitkä verrattuna muihin artikkeleihini. Toivottavasti olet nauttinut siitä. Kerro mielipiteesi jättämällä kommentti.

Saatat myös pitää hyödyllisenä seuraavia Excel -opetusohjelmia:

• Laske sanojen määrä Excelissä.
• Laske solut taustavärin perusteella Excelissä.
• Sarakkeen summaaminen Excelissä (5 todella helppoa tapaa)