Ši pamoka parodo, kaip naudotisIšskirtinisel COUNTIF ir COUNTIFS Funkcjoms „Excel“ skaičiuoti duomenis, atitinkančius tam tikrus kriterijus.
COUNTIF funkcijų apžvalga
Galite naudoti funkciją „COUNTIF“ programoje „Excel“, kad suskaičiuotumėte ląsteles, kuriose yra tam tikra reikšmė, suskaičiuotumėte ląsteles, kurios yra didesnės arba lygios reikšmei ir pan.
(Atkreipkite dėmesį, kaip atrodo formulės įvestys)
COUNTIF funkcijos sintaksė ir argumentai:
= COUNTIF (diapazonas, kriterijai)
diapazonas - Skaičiuojamų ląstelių diapazonas.
kriterijai - Kriterijai, pagal kuriuos kontroliuojama, kurios ląstelės turėtų būti skaičiuojamos.
Kas yra funkcija COUNTIF?
Funkcija COUNTIF yra viena iš senesnių funkcijų, naudojamų skaičiuoklėse. Paprasčiau tariant, tai puikiai nuskaito diapazoną ir pasako, kiek ląstelių atitinka šią sąlygą. Pažiūrėsime, kaip funkcija veikia su tekstu, skaičiais ir datomis; taip pat kai kurios kitos galimos situacijos.
Pagrindinis pavyzdys
Pradėkime nuo šio atsitiktinių elementų sąrašo. Turime keletą skaičių, tuščių langelių ir kai kurias teksto eilutes.
Jei norite sužinoti, kiek elementų tiksliai atitinka kriterijus, galite nurodyti, ko norite ieškoti kaip antrąjį argumentą. Šios formulės pavyzdys gali atrodyti taip
= COUNTIF (A2: A9, „Apple“)
Ši formulė grąžintų skaičių 3, nes mūsų diapazone yra 3 langeliai, atitinkantys šiuos kriterijus. Arba vietoj reikšmės kodavimo galime naudoti langelio nuorodą. Jei G2 langelyje parašytume „Apple“, formulę galėtume pakeisti į
= COUNTIF (A2: A9, G2)
Kalbant apie skaičių, svarbu atskirti skaičius nuo skaičių, kurie saugomi kaip tekstas. Paprastai rašydami formules aplink kabutes nededate kabučių. Taigi, norėdami parašyti formulę, kuri patikrina skaičių 5, turėtumėte parašyti
= COUNTIF (A2: A9, 5)
Galiausiai, mes taip pat galėtume patikrinti, ar nėra tuščių langelių, naudojant nulinio ilgio eilutę. Tą formulę parašytume kaip
= COUNTIF (A2: A9, "")
Pastaba: Pagal šią formulę bus skaičiuojamos ir tuščios ląstelės, ir tos, kurios yra tuščios dėl formulės, pvz., IF funkcija.
Dalinės rungtynės
Funkcija COUNTIF palaiko pakaitos simbolių „*“ arba „?“ Naudojimą kriterijuose. Pažvelkime į šį skanių kepinių sąrašą:
Norėdami rasti visus elementus, prasidedančius „Apple“, galėtume parašyti „Apple*“. Taigi, norėdami gauti 3 atsakymą, mūsų formulė D2 yra
= COUNTIF (A2: A5, „Apple“*)
Pastaba: Funkcija COUNTIF neskiria didžiųjų ir mažųjų raidžių, todėl, jei norite, taip pat galite parašyti „obuolys*“.
Grįžtant prie mūsų kepinių, galbūt norėtume sužinoti, kiek pyragų yra mūsų sąraše. Tai galime rasti įdėję pakaitos simbolį paieškos termino pradžioje ir parašę
= COUNTIF (A2: A5, "*pyragas")
Ši formulė duoda rezultatą 2.
Mes taip pat galime naudoti pakaitos simbolius, kad patikrintume, ar nėra langelių su tekstu. Grįžkime prie pradinio duomenų sąrašo.
Norėdami suskaičiuoti ląstelių, kuriose yra bent šiek tiek teksto, skaičių, taigi neskaičiuojant skaičių ar tuščio langelio, galime rašyti
= COUNTIF (A2: A9, "*")
Matote, kad mūsų formulė teisingai pateikia 4 rezultatą.
COUNTIF palyginimo operatoriai
Rašydami kriterijus iki šiol turėjome omenyje, kad mūsų palyginimo operatorius yra „=“. Tiesą sakant, mes galėjome parašyti taip:
= COUNTIF (A2: A9, "= Apple")
Tačiau tai yra papildomas simbolis, kurį reikia parašyti, todėl paprastai jis praleidžiamas. Tačiau tai reiškia, kad galite naudoti kitus operatorius, tokius kaip didesnis, mažesnis arba nelygus. Pažvelkime į šį įrašytų amžių sąrašą:
Jei norėtume sužinoti, kiek vaikų yra mažiausiai 5 metų, galime parašyti „didesnį ar lygų“ palyginimą taip:
= COUNTIF (A2: A8, "> = 5")
Pastaba: Palyginimo operatorius visada pateikiamas kaip teksto eilutė, todėl turi būti kabutėse.
Taip pat galite patikrinti, ar nėra elementų, kurių vertė yra mažesnė. Jei mums reikia išsiaiškinti, kiek yra mažiau nei 8, galime parašyti
= COUNTIF (A2: A8, "<8")
Tai suteikia mums norimą rezultatą 5. Dabar įsivaizduokime, kad visi 6 metų vaikai vyksta į išvyką. Kiek vaikų liks? Tai galime išsiaiškinti naudodami „nelygu“ palyginimą:
= COUNTIF (A2: A8, "6")
Dabar galime greitai pamatyti, kad turime 6 vaikus, kuriems nėra 6 metų.
Šiuose palyginimo pavyzdžiuose iki šiol sunkiai kodavome norimas vertes. Taip pat galite naudoti langelio nuorodą. Apgaulė ta, kad palyginimo operatorių reikia sujungti su langelio nuoroda. Tarkime, kad C2 langelyje įrašome skaičių 7 ir norime, kad mūsų formulė D2 parodytų, kiek vaikų yra jaunesni nei 7 metų.
Mūsų formulė D2 turi atrodyti taip:
= COUNTIF (A2: A8, "<" ir C2)
Pastaba: Rašydami šias formules atkreipkite ypatingą dėmesį į tai, ar reikia elementą įdėti į kabutes, ar ne. Operatoriai visada yra kabutėse, ląstelių nuorodos visada yra už kabučių ribų. Skaičiai yra lauke, jei atliekate tikslią atitiktį, bet viduje, jei darote palyginimo operatorių.
Darbas su datomis
Mes matėme, kaip galite pateikti tekstą ar skaičių kaip kriterijų, bet ką daryti, kai turime dirbti su datomis? Čia yra trumpas pavyzdžių sąrašas, su kuriuo galime dirbti:
Norėdami suskaičiuoti, kiek datų yra po gegužės 4 d., Turime būti atsargūs. Kompiuteriai saugo datas kaip skaičius, todėl turime įsitikinti, kad kompiuteris naudoja tinkamą skaičių. Jei parašytume šią formulę, ar gautume teisingą rezultatą?
= COUNTIF (A2: A9, "
Atsakymas yra „galbūt“. Kadangi į savo kriterijus neįtraukėme metų, kompiuteris manys, kad turime omenyje einamuosius metus. Jei visos datos, su kuriomis dirbame, yra einamieji metai, gausime teisingą atsakymą. Tačiau jei yra tam tikrų datų, kurios bus ateityje, gausime neteisingą atsakymą. Be to, prasidėjus kitiems metams, ši formulė duos kitokį rezultatą. Tokios sintaksės turbūt reikėtų vengti.
Kadangi gali būti sunku teisingai įrašyti datas formulėje, geriausia praktikoje įrašyti datą, kurią norite naudoti langelyje, ir tada galite naudoti tą langelio nuorodą savo COUNTIF formulėje. Taigi, į C2 langelį įrašykime 2020 m. Gegužės 7 d. Datą, tada galėsime savo formulę įrašyti į C4.
C4 formulė yra
= COUNTIF (A2: A9, "<" ir C2)
Dabar žinome, kad rezultatas 7 yra teisingas, ir atsakymas nesikeis netikėtai, jei kada nors ateityje atidarysime šią skaičiuoklę.
Prieš išeinant iš šio skyriaus, dirbant su datomis įprasta naudoti funkciją TODAY. Mes galime tai naudoti, kaip ir ląstelių nuorodą. Pavyzdžiui, mes galime pakeisti ankstesnę formulę taip:
= COUNTIF (A2: A9, "<" ir TODAY ())
Dabar mūsų formulė vis tiek bus atnaujinta, kai progresas vyksta realiuoju laiku, ir mes turėsime mažiau elementų nei šiandien.
Keli kriterijai ir COUNTIFS
Pradinė COUNTIF funkcija patobulėjo 2007 m., Kai pasirodė COUNTIFS. Šių dviejų sintaksė yra labai panaši, o pastaroji leidžia nurodyti papildomus diapazonus ir kriterijus. Galite lengvai naudoti COUNTIFS bet kurioje COUNTIF situacijoje. Tiesiog gera mintis žinoti, kad abi funkcijos egzistuoja.
Pažvelkime į šią duomenų lentelę:
Norėdami sužinoti, kiek žmonių turi 1–2 darbo užmokesčio lygius, galite parašyti COUNTIF funkcijų apibendrinimą taip:
= COUNTIF (B2: B7, "> = 1")-COUNTIF (B2: B7, "> 2")
Ši formulė veiks, nes rasite viską, kas yra daugiau nei 1, bet tada atimsite įrašų, kurie yra už jūsų ribinės ribos, skaičių. Arba galite naudoti COUNTIFS taip:
= SKAIČIUS (B2: B7, "> = 1", B2: B7, "<= 2")
Pastarasis yra intuityviau skaitomas, todėl galbūt norėsite naudoti tą maršrutą. Be to, COUNTIFS yra galingesnis, kai reikia atsižvelgti į kelis stulpelius. Tarkime, mes norime žinoti, kiek žmonių yra vadyboje ir 1 darbo užmokesčio lygyje. Jūs negalite to padaryti tik su COUNTIF; jums reikia išrašyti
= COUNTIFS (A2: A7, „Valdymas“, B2: B7, 1)
Ši formulė duos jums teisingą rezultatą 2. Prieš palikdami šį skyrių, apsvarstykime Ar tipo logiką. Ką daryti, jei norėtume sužinoti, kiek žmonių yra vadyboje ar? Turėtumėte pridėti kelis COUNTIFS kartu, tačiau tai galite padaryti dviem būdais. Paprasčiausias būdas yra parašyti taip:
= COUNTIF (A2: A7, „HR“)+COUNTIF (A2: A7, „Management“)
Taip pat galite naudoti masyvą ir parašyti šią masyvo formulę:
= SUMA (COUNTIF (A2: A7, {"HR", "Management"})))
Pastaba: Masyvo formulės turi būti patvirtintos naudojant „Ctrl+Shift+Enter“, o ne tik „Enter“.
Kaip ši formulė veiks, pamatysite, kad įvedėte masyvą. Taigi jis apskaičiuos rezultatą į dvi skirtingas COUNTIF funkcijas ir išsaugos jas masyve. Tada funkcija SUM sujungs visus mūsų masyvo rezultatus ir sudarys vieną išvestį. Taigi, mūsų formulė bus įvertinta taip:
= SUMA (COUNTIF (A2: A7, {"HR", "Management"}))) = SUM ({2, 3}) = 5
Skaičiuokite unikalias vertybes
Dabar, kai pamatėme, kaip naudoti masyvą su funkcija COUNTIF, galime žengti dar vieną žingsnį, kad padėtų mums suskaičiuoti, kiek unikalių reikšmių yra diapazone. Pirma, dar kartą pažvelkime į departamentų sąrašą.
= SUMA (1/COUNTIF (A2: A7, A2: A7))
Matome, kad yra 6 langelių vertės duomenys, tačiau yra tik 3 skirtingi elementai. Kad matematika būtų sėkminga, mums reikia, kad kiekvienas elementas būtų vertas 1/N, kur N yra elemento pakartojimų skaičius. Pvz., Jei kiekvienas HR buvo vertas tik 1/2, tada, kai juos sudėjote, gausite skaičių 1 už 1 unikalią vertę.
Grįžkite į mūsų COUNTIF, kuris skirtas išsiaiškinti, kiek kartų elementas rodomas diapazone. D2 rašysime masyvo formulę
= SUMA (1/COUNTIF (A2: A7, A2: A7))
Kaip ši formulė veiks, kiekviena ląstelė yra diapazone A2: A7, ji patikrins, kiek kartų ji rodoma. Naudojant mūsų pavyzdį, bus sukurta daugybė
{2, 2, 3, 3, 3, 1}
Tada mes visus šiuos skaičius paverčiame trupmenomis, padalydami šiek tiek. Dabar mūsų masyvas atrodo taip
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Susumavus visa tai, gauname norimą rezultatą 3.
Skaičiuokite su dviem ar keliomis sąlygomis - funkcija Countifs
Iki šiol dirbome tik su funkcija COUNTIF. Funkcija COUNTIF vienu metu gali apdoroti tik vieną kriterijų. Norėdami naudoti COUNTIF su keliais kriterijais, turite naudoti funkciją COUNTIFS. COUNTIFS elgiasi lygiai taip pat kaip COUNTIFS. Tiesiog pridėkite papildomų kriterijų. Pažvelkime į žemiau pateiktą pavyzdį.
= COUNTIFS (B2: B7, "= 130")
COUNTIF ir COUNTIFS „Google“ skaičiuoklėse
Funkcija COUNTIF & COUNTIFS „Google“ skaičiuoklėse veikia taip pat, kaip ir „Excel“:
