Excel on monia toimintoja, joissa käyttäjä tarvitsee määrittää yhden tai useita perusteita saada tulosta. Jos esimerkiksi haluat laskea soluja useiden kriteerien perusteella, voit käyttää Excelissä COUNTIF-tai COUNTIFS-toimintoja.
Tämä opetusohjelma kattaa erilaisia tapoja käyttää yhtä tai useampaa kriteeriä VASTATOIMINNASSA ja VASTATOIMINNASSA Excelissä.,
Vaikka en ensisijaisesti keskitytään COUNTIF ja COUNTIFS toiminnot tässä opetusohjelmassa, kaikki nämä esimerkit voidaan käyttää myös muita Excel-toimintoja, jotka ottaa useita kriteerejä, kuten tuotantopanosten (kuten SUMIF, SUMIFS, AVERAGEIF, ja AVERAGEIFS).
Tämä Opetusohjelma Kattaa:
Johdanto Excel COUNTIF ja COUNTIFS Toiminnot
katsotaanpa ensin saada otetta käyttäen COUNTIF ja COUNTIFS toimintoja Excelissä.,
Excel COUNTIF Toiminto (vie Yhden Kriteerit)
Excel COUNTIF toiminto sopii parhaiten tilanteisiin, kun haluat laskea soluja, joka perustuu yhden kriteerin. Jos haluat laskea useiden kriteerien perusteella, käytä COUNTIFS-toimintoa.
Syntax:
=COUNTIF(range, criteria)
Input Väitteitä
- alue – solualue, josta haluat laskea.
- kriteerit – kriteerit, jotka on arvioitava erilaisia soluja solun voidaan laskea.,
Excel COUNTIFS Toiminto (kestää Useita Kriteerit)
Excel COUNTIFS toiminto sopii parhaiten tilanteisiin, kun haluat laskea soluja, joka perustuu useita kriteerejä.
Syntax:
=COUNTIFS(criteria_range1, criteria1, …)
Input Väitteitä
- criteria_range1 – solualue, jonka haluat arvioida vastaan criteria1.
- criteria1 – kriteerit, jotka haluat arvioida varten criteria_range1 määrittää, mitkä solut lasketaan.
- – niiden solujen valikoima, joita haluat arvioida kriteerien vastaisesti2.,
- – kriteerit, jotka haluat arvioida criteria_range2-kriteerien perusteella, mitkä solut lasketaan.
nyt katsotaan joitakin esimerkkejä useiden kriteerien käytöstä Vastatoiminnoissa Excelissä.
Käyttämällä USEITA Kriteerejä Excel COUNTIF Toiminnot
#1 Laskea Soluja, kun Kriteerit on TASA-Arvo
saada laskea soluja, joissa kriteerit argumentti on yhtä suuri kuin määritelty arvo, voit joko kirjoittaa suoraan perusteista tai käyttää solun viittaus, joka sisältää kriteerit.,
Alla on esimerkki, jossa lasketaan solut, jotka sisältävät numero 9 (mikä tarkoittaa, että kriteerit väite on sama kuin 9). Tässä on kaava:
=COUNTIF($B$2:$B$11,D3)
yllä olevassa esimerkissä (kuva), kriteerit, on soluun D3. Kriteerit voi myös syöttää suoraan kaavaan., Esimerkiksi, voit käyttää myös:
=COUNTIF($B$2:$B$11,9)
#2 Laskea Soluja, kun Kriteerit on SUUREMPI KUIN Arvo
saada laskea soluja, joiden arvo on suurempi kuin määritetty arvo, käytämme suurempi kuin-operaattori (”>”). Voisimme joko käyttää sitä suoraan kaavassa tai käyttää soluviittausta, jossa on kriteerit.
Aina kun käytämme operaattori perusteet, Excel, meidän täytyy laittaa se sisällä lainausmerkkejä., Esimerkiksi, jos kriteerit on suurempi kuin 10, sitten meidän täytyy syöttää ”>10”, kuten kriteerit (ks. kuva alla):
Tässä on kaava:
=COUNTIF($B$2:$B$11,”>10″)
Voit myös olla perusteet, solu ja käyttää solun viittaus, koska kriteerit. Tässä tapauksessa, sinun EI tarvitse laittaa kriteerit lainausmerkkeihin:
=COUNTIF($B$2:$B$11,D3)
voisi olla myös silloin, kun haluat kriteerit solussa, mutta en halua sitä operaattorin kanssa., Voit esimerkiksi haluta, että solulla D3 on numero 10 eikä 10.
Tässä tapauksessa, sinun täytyy luoda kriteerit argumentti, joka on yhdistelmä operaattori ja solun viittaus (ks. kuva alla):
=COUNTIF($B$2:$B$11,”>”&D3)
HUOMAUTUS: Kun yhdistää lentotoiminnan harjoittajan ja solun viite, operaattori on aina lainausmerkkeihin. Operaattoriin ja soluviittaukseen liittyy ampersand (&).,
#3 Laskea Soluja, kun Kriteerit on VÄHEMMÄN KUIN Arvo
saada laskea soluja, joiden arvo on alle tietyn arvon, käytämme vähemmän kuin-operaattori (”<”). Voisimme joko käyttää sitä suoraan kaavassa tai käyttää soluviittausta, jossa on kriteerit.
Aina kun käytämme operaattori perusteet, Excel, meidän täytyy laittaa se sisällä lainausmerkkejä., Esimerkiksi, jos kriteeri on, että määrä olisi vähemmän kuin 5, niin meidän täytyy syöttää ”<5”, kuten kriteerit (ks. kuva alla):
=COUNTIF($B$2:$B$11,”<5″)
Voit myös olla perusteet, solu ja käyttää solun viittaus, koska kriteerit., Tässä tapauksessa, sinun EI tarvitse laittaa kriteerit lainausmerkkeihin (ks. kuva alla):
=COUNTIF($B$2:$B$11,D3)
Myös, siellä voisi olla kyse, kun haluat kriteerit solussa, mutta en halua sitä operaattorin kanssa. Voit esimerkiksi haluta, että solulla D3 on numero 5 eikä <5.,
Tässä tapauksessa, sinun täytyy luoda kriteerit argumentti, joka on yhdistelmä operaattori ja solun viite:
=COUNTIF($B$2:$B$11,”<”&D3)
HUOMAUTUS: Kun yhdistää lentotoiminnan harjoittajan ja solun viite, operaattori on aina lainausmerkkeihin. Operaattoriin ja soluviittaukseen liittyy ampersand (&).,
#4 Laskea Soluja, joissa on Useita Kriteerejä – Kahden arvon Välillä
saada laskea arvojen välillä kaksi arvoa, meidän täytyy käyttää useita kriteerejä COUNTIF toiminto.
Tässä on kaksi tapaa tehdä tämä:
MENETELMÄ 1: Käyttämällä COUNTIFS toiminto
COUNTIFS toiminto voi käsitellä useita kriteerejä, kuten perusteluja ja laskee solut vain silloin, kun kaikki kriteerit ovat TOTTA., Laskea soluja, joiden arvot kahden määritelty arvot (5 ja 10), voimme käyttää seuraavia COUNTIFS toiminto:
=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)
HUOMAUTUS: edellä esitetty kaava ei lasketa solut, jotka sisältävät 5 tai 10. Jos haluat sisällyttää nämä solut, käyttää suurempi tai yhtä suuri kuin (>=) ja pienempi tai yhtä suuri kuin (<=) toimijat., Tässä on kaava:
=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″)
Voit myös on näiden kriteerien soluissa ja käyttää solun viittaus, koska kriteerit. Tässä tapauksessa, sinun EI tarvitse laittaa kriteerit lainausmerkkeihin (ks. kuva alla):
Voit myös käyttää yhdistelmää solujen viitteet ja toimijoiden (toimija on merkitty suoraan kaava). Kun yhdistät operaattorin ja soluviittauksen, operaattori on aina kaksinkertaisissa sitaateissa., Operaattoriin ja soluviittaukseen liittyy ampersand (&).
MENETELMÄ 2: Käyttämällä kahta COUNTIF toiminnot
Jos sinulla on useita kriteerejä, voit joko käyttää COUNTIFS tai luoda yhdistelmä COUNTIF toimintoja., Alla oleva kaava olisi myös tehdä sama asia:
=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)
edellä olevassa kaavassa, meidän on ensin löytää solujen määrän, joiden arvo on suurempi kuin 5 ja vähennämme laskea soluja, joiden arvo on suurempi kuin 10. Tämä antaisi meille tuloksen 5 (joka on määrä soluja, joiden arvot ovat yli 5 ja alle 10).,3c219a21b4″>
Jos haluat kaava sisältää sekä 5 ja 10, käytä seuraavaa kaavaa sijaan:
=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)
Jos haluat kaavan ulkopuolelle sekä ’5’ ja ’10’ päässä laskenta, käytä seuraavaa kaavaa:
=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)
Sinulla voi olla nämä kriteerit soluissa ja käyttää solujen viittaukset, tai voit käyttää yhdistelmä toimijoiden ja solujen viitteet.,
TEKSTIN Kriteerit Excel Toiminnot
#1 Laskea Soluja, kun Kriteerit on YHTÄ suuri kuin Määritelty teksti
laskea soluja, jotka sisältävät tarkka ottelu määritetyn tekstin, voimme yksinkertaisesti käyttää, että tekstin kriteerit. Esimerkiksi, käyttöoikeus (alla kuva), jos haluan laskea kaikki solut jonka nimi on Joe, jota voi käyttää alla kaava:
=COUNTIF($B$2:$B$11,”Joe”)
Koska tämä on merkkijono, minun täytyy laittaa tekstin kriteerit lainausmerkkeihin.,
Voit myös olla perusteet, solu-ja sitten käyttää, että soluviittaus (kuten alla):
=COUNTIF($B$2:$B$11,E3)
HUOMAUTUS: Voit saada vääriä tuloksia, jos on olemassa johtava/perään välilyönnit kriteerit tai arviointiperusteet vaihtelevat. Varmista, että puhdistat tiedot ennen näiden kaavojen käyttöä.
#2 Laskea Soluja, kun Perusteita ei OLE YHTÄ tiettyä tekstiä
Samanlainen kuin mitä näimme yllä olevassa esimerkissä, voit myös laskea soluja, jotka sisältävät tietyn tekstin., Tätä varten on käytettävä ei-yhtä-operaattoria (<).
Oletetaan, että haluat laskea kaikki solut, jotka eivät sisällä nimi JOE, tässä on kaava, joka tekee sen:
=COUNTIF($B$2:$B$11,”<>Joe”)
Voit myös olla perusteet, solu ja käyttää solun viittaus, koska kriteerit., Tässä tapauksessa, sinun EI tarvitse laittaa kriteerit lainausmerkkeihin (ks. kuva alla):
=COUNTIF($B$2:$B$11,E3)
voisi olla myös silloin, kun haluat kriteerit solussa, mutta eivät halua sen operaattorin kanssa. Esimerkiksi, et ehkä halua soluun D3 on nimi Joe ja ei <>Joe.,
Tässä tapauksessa, sinun täytyy luoda kriteerit argumentti, joka on yhdistelmä operaattori ja solun viittaus (ks. kuva alla):
=COUNTIF($B$2:$B$11,”<>”&E3)
Kun yhdistää lentotoiminnan harjoittajan ja solun viite, operaattori on aina lainausmerkkeihin. Operaattoriin ja soluviittaukseen liittyy ampersand (&).
Käyttämällä PÄIVÄMÄÄRÄ Perusteet Excel COUNTIF ja COUNTIFS Toiminnot
Excel tallentaa päivämäärän ja kellonajan numeroina., Voimme käyttää sitä samalla tavalla kuin numeroita.
#1 Laskea Soluja, kun Kriteerit on YHTÄ suuri kuin Määritelty Päivämäärä
saada laskea soluja, jotka sisältävät määritetyn päivämäärän, me käyttäisimme yhtä suuri kuin-operaattori (=) sekä päivämäärä.
päivämäärän käyttämiseksi suosittelen käyttämään PÄIVÄMÄÄRÄFUNKTIOTA, sillä se pääsee eroon päivämääräarvossa mahdollisesti tapahtuvasta virheestä. Joten, esimerkiksi, jos haluat käyttää päivämäärä syys 1, 2015, en voi käyttää PÄIVÄMÄÄRÄ-toiminto, kuten alla:
=DATE(2015,9,1)
Tämä kaava palauttaa samana päivänä, vaikka alueellisia eroja., Esimerkiksi 01-09-2015 olisi syyskuun 1, 2015 mukaan US date syntaksi ja tammikuu 09, 2015 mukaan UK date syntaksi. Tämä kaava palaisi kuitenkin aina 1.syyskuuta 2105.
Tässä on kaava laskea, kuinka monta solut, jotka sisältävät päivämäärän 02-09-2015:
=COUNTIF($A$2:$A$11,PÄIVÄMÄÄRÄ(2015,9,2))
#2 Laskea Soluja, kun Kriteerit on ENNEN tai JÄLKEEN tiettyä Päivämäärää
laskea soluja, jotka sisältävät päivämäärän ennen tai tietyn päivämäärän jälkeen, voimme käyttää vähemmän kuin/suurempi kuin operaattoreilla.,
esimerkiksi, jos haluan laskea kaikki solut, jotka sisältävät päivämäärän, joka on sen jälkeen, kun syyskuu 02, 2015, en voi käyttää kaavaa:
=COUNTIF($A$2:$A$11,”>”&PÄIVÄMÄÄRÄ(2015,9,2))
Vastaavasti voit myös laskea solujen lukumäärä ennen tiettyä päivämäärää. Jos haluat sisällyttää päivämäärän laskenta, Käytä ja ”yhtä” operaattori sekä ”suurempi / vähemmän kuin” operaattori.
voit käyttää myös soluviittausta, joka sisältää päivämäärän., Tässä tapauksessa, sinun täytyy yhdistää operaattorin (sisällä lainausmerkkejä) päivämäärä käytät et-merkki (&).
Katso esimerkki alla:
=COUNTIF($A$2:$A$11,”>”&F3)
#3 Laskea Soluja, joissa on Useita Kriteerejä – Kahden Päivämääriä
saada laskea arvojen välillä kaksi arvoa, meidän täytyy käyttää useita kriteerejä COUNTIF toiminto.
voimme tehdä tämän kahdella menetelmällä – yhdellä VASTATOIMINNOLLA tai kahdella VASTATOIMINNOLLA.,
MENETELMÄ 1: Käyttämällä COUNTIFS toiminto
COUNTIFS toiminto voi kestää useita kriteerejä, kuten perusteluja ja laskee solut vain silloin, kun kaikki kriteerit ovat TOTTA. Laskea soluja, joiden arvot kahden määrättyinä päivinä (eli 2. syyskuuta ja 7. syyskuuta), voimme käyttää seuraavia COUNTIFS toiminto:
=COUNTIFS($A$2:$A$11,”>”&PÄIVÄMÄÄRÄ(2015,9,2),$A$2:$A$11,”<”&PÄIVÄMÄÄRÄ(2015,9,7))
edellä olevan kaavan ei lasketa solut, jotka sisältävät määritelty päivämääriä., Jos haluat sisällyttää myös nämä päivämäärät, käytä enemmän kuin yhtä suuri (=) ja vähemmän kuin yhtä suuri (<=) operaattoreille. Tässä on kaava:
=COUNTIFS($A$2:$A$11,”>=”&PÄIVÄMÄÄRÄ(2015,9,2),$A$2:$A$11,”<=”&PÄIVÄMÄÄRÄ(2015,9,7))
Voit myös päivämäärät solussa ja käyttää solun viittaus, koska kriteerit. Tässä tapauksessa, et voi olla operaattori päivämäärä soluissa., Sinun täytyy manuaalisesti lisätä toimijoiden kaava (lainausmerkkeihin) ja lisätä solun viittaus käytät et-merkki (&). Ks. kuva alla:
=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<”&G3)
MENETELMÄ 2: Käyttämällä COUNTIF toiminnot
Jos sinulla on useita kriteerejä, voit käyttää joko yksi COUNTIFS toiminto tai luoda yhdistelmä kaksi COUNTIF toimintoja., Alla oleva kaava olisi myös tehdä temppu:
=COUNTIF($A$2:$A$11,”>”&PÄIVÄMÄÄRÄ(2015,9,2))-COUNTIF($A$2:$A$11,”>”&PÄIVÄMÄÄRÄ(2015,9,7))
edellä olevassa kaavassa, meidän on ensin löytää joukko soluja, jotka ovat päivämäärän jälkeen 2. syyskuuta ja vähennämme laskea solujen päivämäärät jälkeen 7. syyskuuta. Tämä antaisi meille tuloksen 7 (joka on määrä soluja, joilla on päivämäärät jälkeen syyskuun 2 ja tai ennen syyskuuta 7).,>DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&PÄIVÄMÄÄRÄ(2015,9,7))
Jos haluat jättää sekä vuodelta laskenta, käytä seuraavaa kaavaa:
=COUNTIF($A$2:$A$11,”>”&PÄIVÄMÄÄRÄ(2015,9,2))-COUNTIF($A$2:$A$11,”>”&PÄIVÄMÄÄRÄ(2015,9,7)-COUNTIF($A$2:$A$11,PÄIVÄMÄÄRÄ(2015,9,7)))
voit Myös olla perusteet, päivämäärät soluissa ja käyttää solujen viitteet (yhdessä toimijoiden lainausmerkkeihin liittyi käyttää et-merkki).,
Käyttämällä yleismerkkejä Kriteerit COUNTIF & COUNTIFS Toiminnot
On olemassa kolme yleismerkkejä Excel:
Voit käyttää COUNTIF toiminto yleismerkkejä laskea soluja, kun muut sisäänrakennettu count toiminto epäonnistuu. Esimerkiksi, oletetaan, että sinulla on tietojen joukko, kuten alla:
katsotaanpa Nyt useita esimerkkejä:
#1 Count Solut, jotka sisältävät Tekstiä
laskea solujen tekstiä, voimme käyttää yleismerkki * (tähti)., Koska asteriski edustaa mitä tahansa merkkien määrää, se laskisi kaikki solut,joissa on mitä tahansa tekstiä. Tässä on kaava:
=COUNTIFS($C$2:$C$11,”*”)
Huomautus: edellä esitetyn kaavan ohittaa solut, jotka sisältävät numeroita, tyhjiä soluja, ja loogisia arvoja, mutta voisi laskea solut sisältävät heittomerkki (ja siten esiintyä tyhjä) tai soluja, jotka sisältävät tyhjän merkkijonon (=””) joka voi olla palasi osana kaava.
Tässä on yksityiskohtainen opetusohjelma käsittelyn tapauksissa, joissa on tyhjä merkkijono tai heittomerkki.,
tässä on yksityiskohtainen opetusohjelma käsittelystä tapauksissa, joissa on tyhjiä merkkijonoja tai heittomerkkejä.
alla on video, jossa selitetään erilaisia skenaarioita solujen laskemisesta tekstillä.
#2 Laske ei-tyhjiä soluja
Jos suunnittelet COUNTAN funktion käyttöä, mieti uudelleen.
kokeile sitä ja se saattaa pettää. COUNTA myös laskea solu, joka sisältää tyhjän merkkijonon (usein palautetaan kaavoja kuin =”” tai kun ihmiset tulevat vain heittomerkki solussa). Tyhjät merkkijonot sisältävät solut näyttävät tyhjiltä, mutta niitä ei ole, ja siten ne lasketaan COUNTAN funktiolla.,
COUNTA myös laskea solu, joka sisältää tyhjän merkkijonon (usein palautetaan kaavoja kuin =”” tai kun ihmiset tulevat vain heittomerkki solussa). Tyhjät merkkijonot sisältävät solut näyttävät tyhjiltä, mutta niitä ei ole, ja siten ne lasketaan COUNTAN funktiolla.
jos et käytä kaavaa =COUNTA(A1:A11), se palaa 11, vaikka se olisi paluu 10.
Here is the fix:
= COUNTIF ($1:$A$11,”?*”)+LASKE($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))
katsotaanpa ymmärrä tätä kaavaa rikkomalla se alas:
- COUNTIF($N$8:$N$18,”?,*”) – Tämä osa formula_5. Tämä sisältää minkä tahansa solun, jossa on tekstihahmo. A ? edustaa yhtä merkkiä ja * edustaa mitä tahansa merkkiä. Siis yhdistelmä ?* kriteereissä pakottaa excel laskea soluja, joilla on vähintään yksi tekstimerkki siinä.
- kreivi($1:$A$11) – Tämä laskee kaikki numerot sisältävät solut. Yllä olevassa esimerkissä se palauttaa 3.
- SUMPRODUCT(–ISLOGICAL($A$1:$A$11) – Tämä lasketaan kaikki solut, jotka sisältävät loogisia arvoja. Yllä olevassa esimerkissä se palauttaa 2.,
#3 Laskea Soluja, jotka sisältävät tiettyä tekstiä
oletetaan, että haluamme laskea kaikki solut, joissa myyntiedustajan nimi alkaa J. Tämä voidaan helposti saavuttaa käyttämällä jokerimerkkiä vuonna COUNTIF toiminto. Tässä on kaava:
=COUNTIFS($C$2:$C$11,”J*”)
kriteerit J* määrittää, että solun tekstin pitäisi alkaa J ja voi sisältää minkä tahansa määrän merkkejä.
Jos haluat laskea solut, jotka sisältävät aakkoset missä tahansa tekstissä, sivusta tähdellä molemmin puolin., Esimerkiksi, jos haluat laskea soluja, jotka sisältävät aakkoset ”a” siinä, käytä *a* kriteereinä.