Kiek užsakymų atėjo iš Kauno? Kiek sąskaitų vėluoja daugiau nei 30 dienų? Kiek klientų pirko šį mėnesį? Visi šie klausimai prasideda žodžiu „kiek“, ir į juos atsako COUNTIF arba COUNTIFS.
COUNTIF: viena sąlyga
Lape „Užsakymai“ C stulpelyje yra miestas. Kiek užsakymų iš Kauno:
=COUNTIF(Užsakymai!C:C;"Kaunas")
Pirmas argumentas yra intervalas, kuriame tikrinama, antras yra sąlyga. Vietoje žodžio galima nurodyti langelį: =COUNTIF(Užsakymai!C:C;G2). Tada formulę galima nutempti per visą miestų sąrašą.
COUNTIFS: kelios sąlygos
Kiek užsakymų iš Kauno buvo bent už 1000 €:
=COUNTIFS(Užsakymai!C:C;"Kaunas";Užsakymai!D:D;">=1000")
COUNTIFS sudaryta iš porų „kur tikrinti, ko ieškoti“. Eilutė įskaičiuojama tik tada, kai atitinka visas poras. Visi intervalai turi būti vienodo dydžio.
Sąlygų užrašymas
Sąlygos rašomos taip pat kaip SUMIFS funkcijoje:
| Sąlyga | Ką skaičiuoja |
|---|---|
"Kaunas" | Langelius, kuriuose tiksliai „Kaunas“ |
">=1000" | Skaičius nuo 1000 |
">="&H1 | Skaičius ar datas nuo reikšmės langelyje H1 |
"<>" | Netuščius langelius |
"" | Tuščius langelius |
"*kava*" | Tekstą, kuriame yra žodis „kava“ |
"???" | Tekstą, kurį sudaro lygiai trys simboliai |
Didžiosios ir mažosios raidės nesvarbios. Jei reikia ieškoti paties žvaigždutės ar klaustuko simbolio, prieš jį rašykite ~, pavyzdžiui "~*".
Intervalai ir datos
Kiek užsakymų nuo 500 iki 1000 €: tą patį stulpelį nurodykite dvi kartus.
=COUNTIFS(Užsakymai!D:D;">=500";Užsakymai!D:D;"<1000")
Kiek užsakymų per mėnesį, kai H1 yra mėnesio pirma diena:
=COUNTIFS(Užsakymai!A:A;">="&H1;Užsakymai!A:A;"<"&EDATE(H1;1))
Kiek sąskaitų vėluoja daugiau nei 30 dienų, kai B stulpelyje yra apmokėjimo terminas, o C stulpelyje apmokėjimo data, kuri tuščia, jei neapmokėta:
=COUNTIFS(Sąskaitos!B:B;"<"&TODAY()-30;Sąskaitos!C:C;"")
COUNT, COUNTA ir COUNTBLANK
Kartais sąlygos nereikia, tik kiekio:
- COUNT skaičiuoja langelius su skaičiais ir datomis.
- COUNTA skaičiuoja visus netuščius langelius.
- COUNTBLANK skaičiuoja tuščius.
Jei tame pačiame stulpelyje COUNT ir COUNTA rodo skirtingus skaičius, dalis reikšmių yra tekstas. Tai dažnas ženklas, kad skaičiai ar datos atėjo iš eksporto netinkamu formatu. Kaip tai pataisyti, aprašiau straipsnyje kodėl Excel nesumuoja skaičių ir neatpažįsta datų.
Kiek skirtingų reikšmių
COUNTIF skaičiuoja eilutes, todėl klientas, pirkęs penkis kartus, bus įskaičiuotas penkis kartus. Jei reikia sužinoti, kiek buvo skirtingų klientų, Microsoft 365 ir Excel 2021 versijose:
=COUNTA(UNIQUE(Užsakymai!B2:B500))
Senesnėse versijose naudojama formulė =SUMPRODUCT(1/COUNTIF(B2:B500;B2:B500)). Ji veikia tik tada, kai intervale nėra tuščių langelių, ir dideliuose duomenyse yra lėta.
Jei skirtingų klientų skaičius rodo daugiau nei turėtų, greičiausiai tas pats klientas įrašytas keliais variantais. Apie tai straipsnyje kaip rasti ir pašalinti dublikatus, o sąrašą be formulių sutvarkyti galima dublikatų šalinimo įrankiu.
Kodėl COUNTIF rodo 0
Jei esate tikri, kad reikšmė yra, o formulė grąžina 0, dažniausiai kalta viena iš šių priežasčių:
- Tarpas gale. „Kaunas “ su tarpu nesutampa su „Kaunas“. Ieškokite su žvaigždute
"Kaunas*"arba sutvarkykite duomenis su TRIM. - Skaičius ir tekstas. Jei kodai išsaugoti kaip tekstas, sąlyga
">=1000"jų neįskaičiuos. Patikrinkite su=ISNUMBER(D2). - Ilgi kodai. COUNTIF tekstą, kuris atrodo kaip skaičius, lygina kaip skaičių, o Excel skaičius tiksliai saugo tik iki 15 skaitmenų. Ilgi kodai, pavyzdžiui, 16 ar 18 skaitmenų, gali būti suskaičiuoti kaip vienodi. Tokiems kodams naudokite
=SUMPRODUCT(--(A2:A500=G2)). - Data su laiku. Jei datos stulpelyje yra ir laikas, sąlyga
"="&H1ras tik įrašus lygiai vidurnaktį. Naudokite intervalą: nuo H1 iki H1+1.
COUNTIF kitame lape ar faile
Sąlygą galima tikrinti kitame lape: =COUNTIF(Užsakymai!C:C;G2). Kitame faile taip pat, bet COUNTIF ir COUNTIFS rodo #VALUE!, kai tas failas uždarytas. Jei skaičiuoti reikia iš kito failo, kuris dažnai būna uždarytas, naudokite =SUMPRODUCT(--([Užsakymai.xlsx]Lapas1!$C$2:$C$500=G2)) arba duomenis įkelkite per Power Query.
Kai formulių per daug
Viena COUNTIFS formulė yra patogi. Kai ataskaitoje jų dešimtys, kiekvienam miestui ir mėnesiui, tą patį aiškiau ir greičiau padaro suvestinė lentelė: nutempkite miestą į Rows, datą į Columns ir bet kurį lauką į Values su Count.
Google Sheets COUNTIF ir COUNTIFS veikia taip pat.