Pri zozname objednávok, požiadaviek alebo úloh často nepotrebujete sčítať hodnoty, ale zistiť počet riadkov s určitou vlastnosťou. Funkcia COUNTIF v Exceli počíta bunky, ktoré spĺňajú jednu podmienku. Je to iná otázka než súčet peňazí cez SUMIF. Na malej modelovej tabuľke si ukážeme presný vzorec, kontrolu výsledku aj chyby, pre ktoré môže počet vyzerať správne, hoci vzorec sleduje nesprávny rozsah.
Čo COUNTIF skutočne počíta
COUNTIF má dva argumenty: rozsah, v ktorom hľadá, a kritérium, podľa ktorého rozhoduje. Zápis vo vzorcoch Microsoftu má podobu =COUNTIF(range,criteria). Slovenské nastavenie Excelu môže používať iné názvy funkcií alebo oddeľovače argumentov podľa verzie a miestneho nastavenia. Preto pri prepisovaní príkladu sledujte nápovedu svojho Excelu; princíp dvoch argumentov zostáva rovnaký.
Výstupom je počet buniek, nie súčet čísel ani počet eur. Ak máte šesť požiadaviek a tri sú označené „Otvorené“, COUNTIF v stĺpci Stav vráti 3. Neznamená to, že otvorené požiadavky majú hodnotu tri eurá alebo že sú všetky rovnako dôležité. Ak chcete naopak sčítať sumy len pre vybranú kategóriu, pozrite si postup so SUMIF v Exceli. Rozlíšenie otázky „koľko“ a „aká suma“ je základom správneho výpočtu.
Pripravte zoznam s jedným významom v každom stĺpci
V modelovom hárku je v stĺpci A identifikátor požiadavky a v stĺpci B stav. Hlavička je v prvom riadku; dáta sú od riadku 2 po riadok 7. Každý riadok predstavuje jednu samostatnú požiadavku. Nie je to vzorka skutočného podnikania, ale vlastný príklad, na ktorom sa dá výsledok ručne overiť. Ak jeden riadok obsahuje dve rôzne požiadavky, počet buniek nebude odpovedať počtu požiadaviek.
| Riadok | Požiadavka | Stav |
|---|---|---|
| 2 | A1 | Otvorené |
| 3 | A2 | Hotové |
| 4 | A3 | Otvorené |
| 5 | A4 | Čaká |
| 6 | A5 | Hotové |
| 7 | A6 | Otvorené |
Vidno tri riadky so stavom „Otvorené“, dva „Hotové“ a jeden „Čaká“. Pred zápisom vzorca ich spočítajte očami. Tento krok sa môže zdať zbytočný, no pri učebnom príklade dáva kontrolný výsledok. V reálnom zozname stovky riadkov kontrolujte aspoň menšiu vzorku a definíciu stavu. Uistite sa, že položka „Čaká“ nemá byť podľa vašich pravidiel zahrnutá medzi otvorené prípady.
Vložte vzorec pre jednu presnú hodnotu
Kliknite do voľnej bunky mimo dát a zadajte =COUNTIF(B2:B7,"Otvorené"). Rozsah B2:B7 označuje šesť buniek so stavmi, text v úvodzovkách je kritérium. V tomto modeli očakávate výsledok 3. Ak Excel odmietne oddeľovač argumentov alebo anglický názov funkcie, pozrite sa na zobrazenú nápovedu funkcie vo vašej inštalácii; nevymýšľajte dodatočné znaky naslepo. Najprv potrebujete správne dva argumenty, až potom miestny zápis.
Kritérium nemusíte vždy písať priamo do vzorca. Ak do bunky D2 zadáte „Otvorené“, môžete použiť odkaz na D2 namiesto reťazca v úvodzovkách. Tento spôsob sa hodí pre jednoduchý prehľad, v ktorom meníte sledovaný stav bez úpravy samotného vzorca. Dávajte však pozor, aby sa pri kopírovaní vzorca nepresunul rozsah mimo údajov. Po zmene kritéria znovu skontrolujte očakávaný počet.
Ako čítať rozsah a predísť chybe o jeden riadok
Rozsah B2:B7 začína prvou dátovou bunkou a končí poslednou. Ak omylom zvolíte B3:B7, vynecháte prvú požiadavku. Ak použijete iba B2:B6, vynecháte poslednú. Na malej tabuľke je rozdiel viditeľný hneď; pri dlhom zozname sa chyba ľahko skryje. Pred potvrdením vzorca kliknite na zvýraznený rozsah a overte, že zahŕňa práve dáta, ktoré chcete hodnotiť.
Samotná hlavička v bunke B1 pri kritériu „Otvorené“ výsledok v tomto modeli nezmení, no rozsah s hlavičkou je menej jasný a pri inom kritériu môže zavádzať. Vlastná kontrola by mala odpovedať na otázku: má každý riadok v rozsahu rovnaký typ údaja? Ak v jednom stĺpci miešate stavy, poznámky a čísla, výpočet prestáva byť jednoduchým počtom prípadov.
Kritériá s porovnaním a hranicou
COUNTIF vie porovnávať aj čísla. Microsoft uvádza napríklad kritérium ">55" pre hodnoty väčšie než 55. Ak si spravíte stĺpec s počtom dní od prijatia požiadavky, môžete zrátať bunky, v ktorých hodnota presiahla stanovený limit. Textové porovnávacie kritérium patrí do úvodzoviek. Pri dynamickej hranici v inej bunke sa operátor spája s odkazom na bunku; kontrolujte výsledok na dvoch či troch známych riadkoch.
Nepleťte si hodnotu väčšiu než 5 s hodnotou aspoň 5. Znak > vylučuje presne 5, kombinácia >= ho zahŕňa. Pri reportovaní čakajúcich dní to môže zmeniť číslo na hranici termínu. Do názvu výslednej bunky preto napíšte konkrétne pravidlo, napríklad „Viac ako päť dní“, a nie iba „Staré požiadavky“. Dobré pomenovanie je súčasťou kontroly, nie dekorácia tabuľky.
Hviezdička, otáznik a podobné texty
Zástupné znaky umožňujú hľadať približný textový vzor. Hviezdička zastupuje ľubovoľnú postupnosť znakov, otáznik jeden znak. Napríklad kritérium "*urgent*" by mohlo spočítať poznámky obsahujúce slovo urgent uprostred ďalšieho textu. Použite ho len vtedy, keď takýto široký záber skutočne chcete. Presný stav „Otvorené“ je v našom modeli lepšie počítať presným kritériom, pretože podobný komentár v bunke by mal iný význam.
COUNTIF pri textových kritériách nerozlišuje veľké a malé písmená. „Otvorené“ a „OTVORENÉ“ sa teda zhodujú, čo je praktické pri nejednotnom písaní. Neodstráni to však všetky rozdiely v dátach. Úvodné či koncové medzery, neviditeľné znaky alebo odlišná diakritika môžu spôsobiť neočakávaný výsledok. Microsoft v návode výslovne upozorňuje na chybné znaky a odporúča údaje čistiť. Najprv opravte zdrojový stĺpec, až potom interpretujte číslo.
Jedna podmienka verzus viac podmienok
Funkcia COUNTIF vyhodnocuje jednu podmienku. Ak chcete počet otvorených požiadaviek iba od konkrétneho oddelenia, potrebujete ďalší stĺpec a vhodnú funkciu pre viac kritérií, napríklad COUNTIFS, alebo zrozumiteľný pomocný prehľad. Nepredstierajte, že kritérium „Otvorené“ samo vie rozlišovať oddelenie. Pri analýze je vždy nutné povedať, ktorý stĺpec sa sledoval a ktoré riadky sa zahrnuli.
Podobný omyl vzniká pri farebnom označení buniek. COUNTIF podľa Microsoftu nepočíta na základe farby pozadia či farby písma. Ak červená farba znamená „urgentné“, zapíšte tento stav aj ako údaj v samostatnom stĺpci. Potom ho možno spočítať kontrolovateľným vzorcom. Farba pomáha človeku orientovať sa, ale sama nie je spoľahlivou definíciou hodnoty v tomto výpočte. Vizuálne upozornenia vytvoríte aj pomocou podmieneného formátovania v Exceli, ktoré rieši inú úlohu.
Kontrolný postup pred zdieľaním výsledku
Najprv napíšte slovami, čo číslo reprezentuje: „Počet buniek so stavom Otvorené v riadkoch 2 až 7.“ Potom ručne skontrolujte aspoň niekoľko záznamov, rozsah a kritérium. V našom modeli je výsledok 3 a dá sa odčítať priamo z tabuľky. Pri zmenách zoznamu overte, či nové riadky spadajú do vzorca. Statický rozsah sa sám nemusí rozšíriť podľa vášho očakávania; po doplnení údajov skontrolujte posledný riadok.
Ak má počet rozhodovať o práci ľudí, pripojte dátum aktualizácie alebo aspoň popis zdroja. Vyhnete sa tomu, že starý výpočet bude vyzerať ako dnešný stav. COUNTIF je jednoduchá a užitočná funkcia, keď je stĺpec dobre pripravený a otázka jednoznačná. Najviac chýb nevzniká vo výpočte, ale vo výbere nesprávnych dát, nejasnom názve stavu a v predpoklade, že číslo vysvetľuje viac, než skutočne počíta.
Keď počet nesedí s tým, čo vidíte
Ak očami napočítate tri otvorené požiadavky, ale vzorec vráti dve, nezačínajte prepisovať výsledok ručne. Označte rozsah B2:B7 a postupne sa pozrite na každú hodnotu. Porovnajte presný text, prípadné medzery a to, či niektorý stav nie je iba vizuálnym komentárom v inom stĺpci. Na druhej strane, ak vzorec vráti štyri, overte, či v rozsahu nie je starý riadok, ktorý už nemá byť v súčasnom reporte. COUNTIF nepozná vašu obchodnú definíciu „aktívnej“ požiadavky; počíta len zhodné bunky.
Pri kopírovaní vzorca do ďalšieho riadku sledujte, či sa rozsah posunul. Ak má zostať stále rovnaký, nastavte odkaz tak, aby sa pri kopírovaní nemenil, alebo vzorec radšej vložte len raz do jasne pomenovanej výslednej bunky. Neskôr pri pridávaní nových údajov skontrolujte, či sú vo výpočte zahrnuté. Zvolený postup si opíšte krátkou poznámkou, aby ďalší človek vedel, ktorý zoznam a ktorá verzia stavu sú použité.
Ako výsledok zrozumiteľne odovzdať
Namiesto samostatnej číslice „3“ napíšte napríklad „Tri otvorené požiadavky zo šiestich v modelovom zozname“. Pri pracovnom reporte pridajte dátum stavu a informáciu, či sa počítajú všetky riadky alebo iba vybraná časť. Ak je výsledok 0, napíšte, či naozaj neexistuje žiadny záznam, alebo ešte nemáte vyplnené dáta. Číslo bez popisu môže vyzerať autoritatívne, hoci zahŕňa nesprávny rozsah. Krátka veta o metodike zvyšuje použiteľnosť výpočtu viac než ďalší dekoratívny graf.
Ak výsledok prezentujete vedľa percenta, jasne určite menovateľa. Tri otvorené prípady zo šiestich predstavujú polovicu modelového zoznamu, ale len vtedy, keď všetkých šesť riadkov patrí do porovnania. Vylúčené, zrušené alebo duplicitné položky môžu meniť interpretáciu. COUNTIF vám dá čitateľnú časť výpočtu; rozhodnutie o tom, ktoré riadky patria do celku, musíte urobiť vy. Pri dôležitom reporte uložte aj kópiu zdrojových údajov použitých pri výpočte.
FAQ: COUNTIF v Exceli
Čo znamenajú dva argumenty COUNTIF?
Prvý určuje rozsah buniek, druhý kritérium. Funkcia vráti počet buniek v zadanom rozsahu, ktoré kritériu vyhovujú.
Aký je rozdiel medzi COUNTIF a SUMIF?
COUNTIF počíta zhodné bunky. SUMIF sčíta číselné hodnoty podľa podmienky. Na otázku o počte otvorených riadkov použite počítanie.
Prečo mi vzorec vracia nulu?
Overte rozsah, skutočný text v bunkách, medzery a neviditeľné znaky. Porovnajte niekoľko položiek ručne.
Rozlišuje COUNTIF veľké písmená?
Pri textových kritériách veľké a malé písmená nerozlišuje. Iné rozdiely v údajoch však môžu výsledok zmeniť.
Môžem jedným COUNTIF počítať dve podmienky?
COUNTIF má jedno kritérium. Pre viac súčasných podmienok zvážte COUNTIFS a jasne stanovte sledované stĺpce.
Počíta COUNTIF bunky podľa farby?
Nie podľa farby pozadia alebo písma. Dôležitý stav zapíšte ako hodnotu, ktorú možno vo vzorci vyhodnotiť.
Oficiálne podklady
Ďalšie súvisiace návody nájdete v rozcestníku Práca a začiatky podnikania.

