„Excel“ turi daug funkcijų, kai vartotojas turi nurodyti vieną ar kelis kriterijus, kad gautų rezultatą. Pvz., Jei norite suskaičiuoti langelius pagal kelis kriterijus, „Excel“ galite naudoti funkcijas COUNTIF arba COUNTIFS.
Ši pamoka apima įvairius būdus, kaip naudoti vieną ar kelis kriterijus „Excel“ funkcijose COUNTIF ir COUNTIFS.
Nors šioje pamokoje daugiausia dėmesio skirsiu funkcijoms COUNTIF ir COUNTIFS, visi šie pavyzdžiai taip pat gali būti naudojami kitose „Excel“ funkcijose, kurios kaip įvestis naudoja kelis kriterijus (pvz., SUMIF, SUMIFS, AVERAGEIF ir AVERAGEIFS).
„Excel“ COUNTIF ir COUNTIFS funkcijų įvadas
Pirmiausia pažvelkime į funkcijų „COUNTIF“ ir „COUNTIFS“ naudojimą „Excel“.
„Excel COUNTIF“ funkcija (taikoma vieniems kriterijams)
„Excel COUNTIF“ funkcija geriausiai tinka situacijoms, kai norite suskaičiuoti langelius pagal vieną kriterijų. Jei norite skaičiuoti pagal kelis kriterijus, naudokite funkciją COUNTIFS.
Sintaksė
= COUNTIF (diapazonas, kriterijai)
Įvesties argumentai
- diapazonas - ląstelių, kurias norite suskaičiuoti, diapazoną.
- kriterijai - kriterijus, kurie turi būti įvertinti pagal ląstelių diapazoną, kad ląstelė būtų suskaičiuota.
„Excel COUNTIFS“ funkcija (taikoma keliems kriterijams)
„Excel COUNTIFS“ funkcija geriausiai tinka situacijoms, kai norite suskaičiuoti langelius pagal kelis kriterijus.
Sintaksė
= COUNTIFS (kriterijų_diapazonas1, kriterijai1, [kriterijų_diapazonas2, kriterijai2]…)
Įvesties argumentai
- kriterijų_sritis1 - Langelių diapazonas, kurį norite įvertinti pagal kriterijus1.
- kriterijai1 - kriterijus, kuriuos norite įvertinti kriterijų_diapazonas1, kad nustatytumėte, kurias ląsteles reikia skaičiuoti.
- [kriterijų_diapazonas2] - Langelių diapazonas, kurį norite įvertinti pagal kriterijus2.
- [2 kriterijus] - kriterijus, kuriuos norite įvertinti kriterijų_diapazonas2, kad nustatytumėte, kurias ląsteles reikia skaičiuoti.
Dabar pažvelkime į keletą pavyzdžių, kaip naudoti kelis kriterijus „Excel“ COUNTIF funkcijose.
NUMBER kriterijų naudojimas „Excel“ COUNTIF funkcijose
#1 Skaičiuokite ląsteles, kai kriterijai yra lygūs vertei
Jei norite gauti langelių, kuriuose kriterijų argumentas yra lygus nurodytai vertei, skaičių, galite tiesiogiai įvesti kriterijus arba naudoti langelio nuorodą, kurioje yra kriterijai.
Žemiau pateikiamas pavyzdys, kai skaičiuojame langelius, kuriuose yra skaičius 9 (tai reiškia, kad kriterijų argumentas yra lygus 9). Čia yra formulė:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Pirmiau pateiktame pavyzdyje (paveikslėlyje) kriterijai yra ląstelėje D3. Taip pat galite tiesiogiai įvesti kriterijus į formulę. Pavyzdžiui, taip pat galite naudoti:
= COUNTIF ($ B $ 2: $ B $ 11,9)
#2 Skaičiuokite ląsteles, kai kriterijai yra didesni nei vertė
Norėdami gauti ląstelių, kurių vertė didesnė už nurodytą vertę, skaičių, naudojame didesnį nei operatorius („>“). Galėtume jį naudoti tiesiogiai formulėje arba naudoti langelio nuorodą, kuri turi kriterijus.
Kai „Excel“ kriterijuose naudojame operatorių, turime jį įtraukti į dvigubas kabutes. Pavyzdžiui, jei kriterijai yra didesni nei 10, tada kaip kriterijus turime įvesti „> 10“ (žr. Paveikslėlį žemiau):
Čia yra formulė:
= COUNTIF ($ B $ 2: $ B $ 11, "> 10")
Taip pat galite turėti kriterijus langelyje ir kaip kriterijų naudoti langelio nuorodą. Tokiu atveju jums nereikia sudėti kriterijų dvigubose kabutėse:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Taip pat gali būti atvejis, kai norite, kad kriterijai būtų langelyje, bet nenorite, kad tai būtų su operatoriumi. Pvz., Galbūt norėsite, kad ląstelė D3 turėtų skaičių 10, o ne> 10.
Tokiu atveju turite sukurti kriterijų argumentą, kuris yra operatoriaus ir langelio nuorodos derinys (žr. Paveikslėlį žemiau):
= COUNTIF ($ B $ 2: $ B $ 11, ">" & D3)
PASTABA: Kai derinate operatorių ir langelio nuorodą, operatorius visada pateikiamas kabutėse. Prie operatoriaus ir langelio nuorodos prisijungia simbolis (&).
#3 Skaičiuokite ląsteles, kai kriterijai yra mažesni nei vertė
Norėdami gauti langelių, kurių vertė mažesnė už nurodytą vertę, skaičių, naudojame mažesnį nei operatorius („<“). Galėtume jį naudoti tiesiogiai formulėje arba naudoti langelio nuorodą, kuri turi kriterijus.
Kai „Excel“ kriterijuose naudojame operatorių, turime jį įtraukti į dvigubas kabutes. Pavyzdžiui, jei kriterijus yra tas, kad skaičius turėtų būti mažesnis nei 5, tada kaip kriterijų turime įvesti „<5“ (žr. Paveikslėlį žemiau):
= COUNTIF ($ B $ 2: $ B $ 11, "<5")
Taip pat galite turėti kriterijus langelyje ir kaip kriterijų naudoti langelio nuorodą. Tokiu atveju jums nereikia sudėti kriterijų dvigubose kabutėse (žr. Paveikslėlį žemiau):
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Be to, gali būti atvejis, kai norite, kad kriterijai būtų langelyje, bet nenorite, kad tai būtų su operatoriumi. Pvz., Galbūt norėsite, kad ląstelė D3 turėtų skaičių 5, o ne <5.
Tokiu atveju turite sukurti kriterijų argumentą, kuris yra operatoriaus ir langelio nuorodos derinys:
= COUNTIF ($ B $ 2: $ B $ 11, <<& D3)
PASTABA: Kai derinate operatorių ir langelio nuorodą, operatorius visada pateikiamas kabutėse. Prie operatoriaus ir langelio nuorodos prisijungia simbolis (&).
#4 Skaičiuokite langelius su keliais kriterijais - tarp dviejų verčių
Norėdami gauti reikšmių skaičių tarp dviejų verčių, funkcijoje COUNTIF turime naudoti kelis kriterijus.
Štai du būdai, kaip tai padaryti:
1 METODAS: COUNTIFS funkcijos naudojimas
Funkcija COUNTIFS gali tvarkyti kelis kriterijus kaip argumentus ir skaičiuoja langelius tik tada, kai visi kriterijai yra TIKRI. Norėdami suskaičiuoti langelius, kurių reikšmės yra tarp dviejų nurodytų verčių (tarkime, 5 ir 10), galime naudoti šią funkciją COUNTIFS:
= COUNTIFS ($ B $ 2: $ B $ 11, "> 5", $ B $ 2: $ B $ 11, "<10")
PASTABA: aukščiau pateikta formulė neskaičiuoja langelių, kuriuose yra 5 arba 10. Jei norite įtraukti šiuos langelius, naudokite didesnius nei lygus (> =) ir mažesnius nei (<=) operatorius. Čia yra formulė:
= COUNTIFS ($ B $ 2: $ B $ 11, "> = 5", $ B $ 2: $ B $ 11, "<= 10")
Šiuos kriterijus taip pat galite turėti langeliuose ir kaip kriterijų naudoti langelio nuorodą. Tokiu atveju jums nereikia dėti kriterijų dvigubose kabutėse (žr. Paveikslėlį žemiau):
Taip pat galite naudoti langelių nuorodų ir operatorių derinį (kur operatorius įvedamas tiesiai į formulę). Kai sujungiate operatorių ir langelio nuorodą, operatorius visada pateikiamas kabutėse. Prie operatoriaus ir langelio nuorodos prisijungia simbolis (&).
2 METODAS: Naudojant dvi COUNTIF funkcijas
Jei turite kelis kriterijus, galite naudoti COUNTIFS arba sukurti COUNTIF funkcijų derinį. Žemiau pateikta formulė taip pat atliktų tą patį:
= COUNTIF ($ B $ 2: $ B $ 11, "> 5")-COUNTIF ($ B $ 2: $ B $ 11, "> 10")
Aukščiau pateiktoje formulėje pirmiausia randame ląstelių, kurių vertė didesnė nei 5, skaičių ir atimame ląstelių, kurių vertė didesnė nei 10., skaičių. Tai duos mums rezultatą kaip 5 (tai yra ląstelių, turinčių vertės yra didesnės nei 5 ir mažesnės nei lygios 10).
Jei norite, kad formulėje būtų ir 5, ir 10, naudokite šią formulę:
= COUNTIF ($ B $ 2: $ B $ 11, "> = 5")-COUNTIF ($ B $ 2: $ B $ 11, "> 10")
Jei norite, kad formulė neįtrauktų ir „5“, ir „10“, naudokite šią formulę:
= COUNTIF ($ B $ 2: $ B $ 11, "> = 5")-COUNTIF ($ B $ 2: $ B $ 11, "> 10")-COUNTIF ($ B $ 2: $ B $ 11,10)
Šiuos kriterijus galite turėti langeliuose ir naudoti langelių nuorodas, arba galite naudoti operatorių ir langelių nuorodų derinį.
„TEXT“ kriterijų naudojimas „Excel“ funkcijose
#1 Skaičiuokite ląsteles, kai kriterijai yra lygūs nurodytam tekstui
Norėdami suskaičiuoti langelius, kuriuose yra tiksli nurodyto teksto atitiktis, mes tiesiog galime naudoti tą tekstą kaip kriterijų. Pvz., Duomenų rinkinyje (parodyta paveikslėlyje žemiau), jei noriu suskaičiuoti visas ląsteles su vardu Joe, galiu naudoti šią formulę:
= COUNTIF ($ B $ 2: $ B $ 11, „Joe“)
Kadangi tai yra teksto eilutė, teksto kriterijus turiu sudėti dvigubose kabutėse.
Taip pat galite turėti kriterijus langelyje ir tada naudoti tą langelio nuorodą (kaip parodyta žemiau):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
PASTABA: galite gauti neteisingų rezultatų, jei kriterijų ar kriterijų diapazone yra priekinių/galinių tarpų. Prieš naudodami šias formules, būtinai išvalykite duomenis.
#2 Skaičiuokite ląsteles, kai kriterijai NĖRA lygūs nurodytam tekstui
Panašiai, kaip matėme aukščiau pateiktame pavyzdyje, taip pat galite suskaičiuoti langelius, kuriuose nėra nurodyto teksto. Norėdami tai padaryti, turime naudoti nelygų operatoriui ().
Tarkime, kad norite suskaičiuoti visas ląsteles, kuriose nėra JOE pavadinimo, čia yra formulė, kuri tai padarys:
= COUNTIF ($ B $ 2: $ B $ 11, „Joe“)
Taip pat galite turėti kriterijus langelyje ir kaip kriterijų naudoti langelio nuorodą. Tokiu atveju jums nereikia dėti kriterijų dvigubose kabutėse (žr. Paveikslėlį žemiau):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
Taip pat gali būti atvejis, kai norite, kad kriterijai būtų langelyje, bet nenorite, kad tai būtų su operatoriumi. Pavyzdžiui, galite norėti, kad ląstelė D3 būtų pavadinta Joe, o ne Joe.
Tokiu atveju turite sukurti kriterijų argumentą, kuris yra operatoriaus ir langelio nuorodos derinys (žr. Paveikslėlį žemiau):
= COUNTIF ($ B $ 2: $ B $ 11, ”” ir E3)
Kai sujungiate operatorių ir langelio nuorodą, operatorius visada pateikiamas kabutėse. Prie operatoriaus ir langelio nuorodos prisijungia simbolis (&).
DATOS kriterijų naudojimas „Excel“ COUNTIF ir COUNTIFS funkcijose
„Excel“ saugo datą ir laiką kaip skaičius. Taigi mes galime jį naudoti taip pat, kaip ir skaičius.
#1 Skaičiuokite ląsteles, kai kriterijai yra lygūs nurodytai datai
Norėdami gauti ląstelių, kuriose yra nurodyta data, skaičių, kartu su data naudosime operatorių (=).
Norėdami naudoti datą, rekomenduoju naudoti funkciją DATE, nes ji pašalina bet kokią datos vertės klaidos galimybę. Pavyzdžiui, jei noriu naudoti datą 2015 m. Rugsėjo 1 d., Galiu naudoti DATE funkciją, kaip parodyta žemiau:
= DATA (2015,9,1)
Ši formulė grąžintų tą pačią datą, nepaisant regioninių skirtumų. Pavyzdžiui, 2015-09-01 pagal JAV datos sintaksę būtų 2015 m. Rugsėjo 1 d., O pagal JK datos sintaksę-2015 m. Sausio 09 d. Tačiau ši formulė visada grąžintų 2105 m. Rugsėjo 1 d.
Čia yra formulė, skirta skaičiuoti ląstelių, kuriose yra 2015-09-02, skaičių:
= COUNTIF ($ A $ 2: $ A $ 11, DATE (2015,9,2))
#2 Skaičiuokite ląsteles, kai kriterijai yra prieš arba po nurodytos datos
Norėdami skaičiuoti langelius, kuriuose yra data prieš arba po nurodytos datos, galime naudoti mažesnę/didesnę nei operatoriai.
Pavyzdžiui, jei noriu suskaičiuoti visas ląsteles, kuriose yra data, kuri yra po 2015 m. Rugsėjo 02 d., Galiu naudoti formulę:
= COUNTIF ($ A $ 2: $ A $ 11, ">" ir DATA (2015,9,2))
Panašiai taip pat galite suskaičiuoti langelių skaičių iki nurodytos datos. Jei į skaičiavimą norite įtraukti datą, naudokite operatorių „lygus“ ir „didesnis nei/mažesnis“ operatorių.
Taip pat galite naudoti langelio nuorodą, kurioje yra data. Tokiu atveju turite sujungti operatorių (su dvigubomis kabutėmis) su data, naudodami simbolį „&“.
Žr. Pavyzdį žemiau:
= COUNTIF ($ A $ 2: $ A $ 11, ">" ir F3)
#3 Skaičiuokite ląsteles su keliais kriterijais - tarp dviejų datų
Norėdami gauti reikšmių skaičių tarp dviejų verčių, funkcijoje COUNTIF turime naudoti kelis kriterijus.
Tai galime padaryti dviem būdais - viena COUNTIFS funkcija arba dvi COUNTIF funkcijos.
1 METODAS: COUNTIFS funkcijos naudojimas
Funkcija COUNTIFS gali priimti kelis kriterijus kaip argumentus ir skaičiuoja langelius tik tada, kai visi kriterijai yra TIKRI. Norėdami skaičiuoti langelius, kurių reikšmės yra tarp dviejų nurodytų datų (tarkime, rugsėjo 2 d. Ir rugsėjo 7 d.), Galime naudoti šią funkciją COUNTIFS:
= COUNTIFS ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,2), $ A $ 2: $ A $ 11, "<" & DATE (2015,9,7))
Aukščiau pateikta formulė neskaičiuoja langelių, kuriuose yra nurodytos datos. Jei norite įtraukti ir šias datas, naudokite didesnius nei lygus (> =) ir mažesnius nei (<=) operatorius. Čia yra formulė:
= COUNTIFS ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<=” ir DATA (2015,9,7))
Taip pat langelyje galite turėti datas ir kaip kriterijų naudoti langelio nuorodą. Tokiu atveju operatorius negali nurodyti datos langeliuose. Turite rankiniu būdu įtraukti operatorius į formulę (dvigubomis kabutėmis) ir pridėti langelio nuorodą naudodami simbolį „&“. Žiūrėkite paveikslėlį žemiau:
= COUNTIFS ($ A $ 2: $ A $ 11, ">" & F3, $ A $ 2: $ A $ 11, <"& G3)
2 METODAS: COUNTIF funkcijų naudojimas
Jei turite kelis kriterijus, galite naudoti vieną funkciją COUNTIFS arba sukurti dviejų COUNTIF funkcijų derinį. Žemiau pateikta formulė taip pat padės:
= COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,7))
Anksčiau pateiktoje formulėje pirmiausia randame ląstelių, kurių data yra po rugsėjo 2 d., Skaičių ir atimame ląstelių skaičių su datomis po rugsėjo 7 d. Tai duotų mums rezultatą kaip 7 (tai yra ląstelių, turinčių datas, skaičius po rugsėjo 2 d. ir iki rugsėjo 7 d.).
Jei nenorite, kad formulė būtų skaičiuojama ir rugsėjo 2 d., Ir rugsėjo 7 d., Naudokite šią formulę:
= COUNTIF ($ A $ 2: $ A $ 11, "> =" & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,7))
Jei norite neįtraukti abiejų datų, naudokite šią formulę:
= COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,7) -COUNTIF ($ A 2 USD: 11 USD, DATE (2015,9,7)))
Be to, langeliuose galite nurodyti kriterijų datas ir naudoti langelių nuorodas (kartu su operatoriais dvigubose kabutėse, sujungtose naudojant „ampersand“).
WILDCARD CHARACTERS naudojimas kriterijuose COUNTIF & COUNTIFS funkcijose
„Excel“ yra trys pakaitos simboliai:
- * (žvaigždutė) - Tai reiškia bet kokį simbolių skaičių. Pavyzdžiui, ex* gali reikšti „excel“, „excels“, „example“, „expert“ ir kt.
- ? (Klaustukas) - Tai vienas simbolis. Pavyzdžiui, Tr? Mp gali reikšti Trumpą ar Trampą.
- ~ (tildė) - Jis naudojamas tekste identifikuoti pakaitos simbolį (~, *,?).
Galite naudoti funkciją COUNTIF su pakaitos simboliais, kad suskaičiuotumėte ląsteles, kai kitos vidinės skaičiavimo funkcijos nepavyksta. Pavyzdžiui, tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:
Dabar paimkime įvairius pavyzdžius:
#1 Skaičiuokite langelius, kuriuose yra teksto
Norėdami suskaičiuoti langelius su tekstu, galime naudoti pakaitos simbolį * (žvaigždutė). Kadangi žvaigždutė reiškia bet kokį simbolių skaičių, ji skaičiuotų visas ląsteles, kuriose yra bet koks tekstas. Čia yra formulė:
= COUNTIFS ($ C $ 2: $ C $ 11, "*")
Pastaba: aukščiau pateikta formulė ignoruoja langelius, kuriuose yra skaičių, tuščių langelių ir loginių verčių, tačiau būtų skaičiuojama, kad langeliuose yra apostrofas (taigi jie atrodo tušti) arba langeliai, kuriuose yra tuščia eilutė (= ““), kurie galėjo būti grąžinti kaip formulės dalis.
Čia yra išsami pamoka, kaip tvarkyti atvejus, kai tuščia eilutė arba apostrofas.
Čia yra išsami pamoka, kaip tvarkyti atvejus, kai yra tuščių eilučių ar apostrofų.
Žemiau yra vaizdo įrašas, kuriame paaiškinami skirtingi ląstelių skaičiavimo scenarijai su tekstu.
#2 Skaičiuokite tuščias ląsteles
Jei galvojate naudoti funkciją COUNTA, pagalvokite dar kartą.
Išbandykite ir jums gali nepavykti. COUNTA taip pat suskaičiuos langelį, kuriame yra tuščia eilutė (dažnai pateikiama formulėmis kaip = "" arba kai žmonės langelyje įveda tik apostrofą). Ląstelės, kuriose yra tuščių eilučių, atrodo tuščios, bet ne, todėl jas skaičiuoja funkcija COUNTA.
„COUNTA“ taip pat skaičiuos langelį, kuriame yra tuščia eilutė (dažnai pateikiama formulėmis kaip = ““ arba kai žmonės langelyje įveda tik apostrofą). Ląstelės, kuriose yra tuščių eilučių, atrodo tuščios, bet ne, todėl jas skaičiuoja funkcija COUNTA.
Taigi, jei naudojate formulę = COUNTA (A1: A11), ji grąžina 11, o turėtų grąžinti 10.
Štai pataisymas:
= COUNTIF ($ A $ 1: $ A $ 11, "?*")+COUNT ($ A $ 1: $ A $ 11)+SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11))
Supraskime šią formulę suskaidydami:
- COUNTIF ($ N $ 8: $ N $ 18, “?*”) - ši formulės dalis grąžina 5. Tai apima bet kurį langelį, kuriame yra teksto simbolis. A? reiškia vieną simbolį, o * - bet kokį simbolių skaičių. Taigi kriterijų derinys?* Verčia „Excel“ skaičiuoti ląsteles, kuriose yra bent vienas teksto simbolis.
- COUNT ($ A $ 1: $ A $ 11) - skaičiuojamos visos ląstelės, kuriose yra skaičių. Pirmiau pateiktame pavyzdyje jis grąžina 3.
- SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11) - tai skaičiuoja visas ląsteles, kuriose yra loginių verčių. Pirmiau pateiktame pavyzdyje pateikiama 2.
#3 Skaičiuokite langelius, kuriuose yra konkretaus teksto
Tarkime, kad norime suskaičiuoti visas ląsteles, kuriose pardavimo atstovo pavadinimas prasideda J. Tai galima lengvai pasiekti naudojant pakaitos simbolį funkcijoje COUNTIF. Čia yra formulė:
= COUNTIFS (2 USD: 2 USD: 11 USD, „J*“)
Kriterijus J* nurodo, kad langelio tekstas turi prasidėti J ir jame gali būti bet koks simbolių skaičius.
Jei norite bet kurioje teksto vietoje suskaičiuoti langelius, kuriuose yra abėcėlė, abiejose pusėse pažymėkite žvaigždute. Pavyzdžiui, jei norite suskaičiuoti langelius, kuriuose yra abėcėlė „a“, kaip kriterijų naudokite * a *.
Šis straipsnis yra neįprastai ilgas, palyginti su kitais mano straipsniais. Tikimės, kad jums patiko. Praneškite man savo mintis palikdami komentarą.
Taip pat gali būti naudingos šios „Excel“ pamokos:
- „Excel“ skaičiuokite žodžių skaičių.
- Skaičiuokite ląsteles pagal fono spalvą „Excel“.
- Kaip apibendrinti stulpelį „Excel“ (5 tikrai paprasti būdai)
