Tento návod ukazuje, ako používaťVýbornáel COUNTIF a COUNTIFS funkciationov v programe Excel počítať údaje, ktoré spĺňajú určité kritériá.
Prehľad funkcií COUNTIF
Funkciu COUNTIF v Exceli môžete použiť na počítanie buniek obsahujúcich konkrétnu hodnotu, počítanie buniek väčších alebo rovných hodnote atď.
(Všimnite si, ako sa zobrazujú vstupy vzorcov)
Syntax a argumenty funkcie COUNTIF:
= COUNTIF (rozsah, kritériá)
rozsah - Rozsah buniek, ktoré sa majú počítať.
kritériá - Kritériá, ktoré určujú, ktoré bunky sa majú započítať.
Čo je funkcia COUNTIF?
Funkcia COUNTIF je jednou zo starších funkcií používaných v tabuľkách. Jednoducho povedané, je skvelé skenovať rozsah a povedať vám, koľko buniek spĺňa túto podmienku. Pozrime sa, ako funkcia funguje s textom, číslami a dátumami; ako aj niektoré ďalšie situácie, ktoré môžu nastať.
Základný príklad
Začnime tým, že sa pozrieme na tento zoznam náhodných položiek. Máme niekoľko čísel, prázdne bunky a niekoľko textových reťazcov.
Ak ste chceli vedieť, koľko položiek presne zodpovedá kritériám, môžete ako druhý argument zadať, čo chcete hľadať. Príklad tohto vzorca môže vyzerať
= COUNTIF (A2: A9, „Apple“)
Tento vzorec by vrátil číslo 3, pretože v našom rozsahu sú 3 bunky, ktoré zodpovedajú týmto kritériám. Alternatívne môžeme namiesto pevného zadania hodnoty použiť odkaz na bunku. Ak by sme do bunky G2 napísali „Apple“, vzorec by sme mohli zmeniť na
= COUNTIF (A2: A9, G2)
Pri práci s číslom je dôležité rozlišovať medzi číslami a číslami, ktoré sú uložené ako text. Pri písaní vzorcov spravidla nedávate úvodzovky k číslam. Ak teda chcete napísať vzorec, ktorý kontroluje číslo 5, napíšte
= COUNTIF (A2: A9, 5)
Nakoniec by sme mohli tiež skontrolovať prázdne bunky pomocou reťazca nulovej dĺžky. Tento vzorec by sme zapísali ako
= COUNTIF (A2: A9, "")
Poznámka: Tento vzorec bude počítať bunky, ktoré sú skutočne prázdne, ako aj bunky, ktoré sú prázdne v dôsledku vzorca, ako napríklad funkcia IF.
Čiastočné zápasy
Funkcia COUNTIF podporuje v kritériách použitie zástupných znakov „*“ alebo „?“. Pozrime sa na tento zoznam chutných pekárskych výrobkov:
Aby sme našli všetky položky, ktoré začínajú na Apple, mohli by sme napísať „Apple*“. Aby sme dostali odpoveď na 3, náš vzorec v D2 je
= COUNTIF (A2: A5, "Apple*")
Poznámka: Funkcia COUNTIF nerozlišuje veľké a malé písmena, takže môžete tiež napísať „jablko*“, ak chcete.
Späť na naše pečivo, možno by sme tiež chceli zistiť, koľko koláčov máme v zozname. Môžeme to zistiť tak, že umiestnime zástupný znak na začiatok hľadaného výrazu a napíšeme
= COUNTIF (A2: A5, "*koláč")
Tento vzorec dáva výsledok 2.
Môžeme tiež použiť zástupné znaky na kontrolu akýchkoľvek buniek s textom. Vráťme sa k nášmu pôvodnému zoznamu údajov.
Ak chcete spočítať počet buniek, ktoré majú aspoň nejaký text, teda nepočítajúc čísla alebo prázdnu bunku, môžeme písať
= COUNTIF (A2: A9, "*")
Môžete vidieť, že náš vzorec správne vracia výsledok 4.
Porovnávacie operátory v COUNTIF
Pri doterajšom písaní kritérií sme naznačovali, že náš operátor porovnávania je „=“. V skutočnosti sme mohli napísať toto:
= COUNTIF (A2: A9, "= Apple")
Je to však ďalší znak na písanie, takže sa zvyčajne vynecháva. To však znamená, že môžete použiť aj iné operátory, napríklad väčšie ako, menšie ako alebo nie rovnaké. Pozrime sa na tento zoznam zaznamenaných vekových skupín:
Ak by sme chceli vedieť, koľko detí má najmenej 5 rokov, mohli by sme napísať porovnanie „väčšie alebo rovné“ takto:
= COUNTIF (A2: A8, "> = 5")
Poznámka: Operátor porovnania je vždy uvedený ako textový reťazec, a preto musí byť v úvodzovkách.
Podobne môžete tiež skontrolovať položky, ktoré sú nižšie ako daná hodnota. Ak potrebujeme zistiť, koľko ich je menej ako 8, môžeme napísať
= COUNTIF (A2: A8, "<8")
To nám dáva požadovaný výsledok 5. Teraz si predstavme, že všetky 6-ročné deti idú na výlet. Koľko detí zostane? Môžeme to zistiť pomocou porovnania „nerovná sa“ takto:
= COUNTIF (A2: A8, "6")
Teraz môžeme rýchlo vidieť, že máme 6 detí, ktoré nemajú 6 rokov.
V týchto porovnávacích príkladoch sme doteraz tvrdo kódovali hodnoty, ktoré sme chceli. Môžete tiež použiť odkaz na bunku. Ide o to, že operátor porovnania musíte spojiť s odkazom na bunku. Povedzme, že do bunky C2 dáme číslo 7 a chceme, aby náš vzorec v D2 ukázal, koľko detí má menej ako 7 rokov.
Náš vzorec v D2 musí vyzerať takto:
= COUNTIF (A2: A8, "<" a C2)
Poznámka: Pri písaní týchto vzorcov dbajte na to, aby ste položku uviedli v úvodzovkách alebo mimo nich. Operátory sú vždy v úvodzovkách, odkazy na bunky sú vždy mimo úvodzoviek. Čísla sú zvonku, ak robíte presnú zhodu, ale vnútri, ak robíte porovnávací operátor.
Práca s dátumami
Videli sme, ako môžete ako kritérium zadať text alebo číslo, ale čo keď musíme pracovať s dátumami? Tu je rýchly ukážkový zoznam, s ktorým môžeme pracovať:
Aby sme spočítali, koľko dátumov je po 4. máji, musíme byť opatrní. Počítače ukladajú dátumy ako čísla, preto sa musíme uistiť, že počítač používa správne číslo. Ak by sme napísali tento vzorec, získali by sme správny výsledok?
= COUNTIF (A2: A9, "
Odpoveď je „možno“. Pretože sme z našich kritérií vynechali rok, počítač bude predpokladať, že máme na mysli aktuálny rok. Ak sú všetky dátumy, s ktorými pracujeme, platné pre aktuálny rok, dostaneme správnu odpoveď. Ak však existujú nejaké dátumy, ktoré sú v budúcnosti, dostali by sme nesprávnu odpoveď. Akonáhle začne ďalší rok, tento vzorec vráti iný výsledok. Preto by sa tejto syntaxi malo pravdepodobne vyhnúť.
Pretože môže byť ťažké zapísať dátumy správne do vzorca, je osvedčené napísať dátum, ktorý chcete použiť do bunky, a potom môžete použiť odkaz na bunku vo svojom vzorci COUNTIF. Napíšte teda dátum 7. máj 2020 do bunky C2 a potom môžeme vzorec vložiť do C4.
Vzorec v C4 je
= COUNTIF (A2: A9, "<" a C2)
Teraz vieme, že výsledok 7 je správny, a odpoveď sa nezmení neočakávane, ak túto tabuľku otvoríme niekedy v budúcnosti.
Predtým, ako opustíme túto sekciu, je pri práci s dátumami bežné používať funkciu DNES. Môžeme to použiť rovnako ako odkaz na bunku. Predchádzajúci vzorec by sme napríklad mohli zmeniť na tento:
= COUNTIF (A2: A9, "<" A DNES ())
Teraz sa náš vzorec bude stále aktualizovať v reálnom čase a budeme mať počet položiek, ktoré sú menšie ako dnes.
Viac kritérií a COUNTIFS
Pôvodná funkcia COUNTIF sa zlepšila v roku 2007, keď vyšla aplikácia COUNTIFS. Syntax medzi týmito dvoma je veľmi podobná, pričom druhá vám umožňuje zadať ďalšie rozsahy a kritériá. COUNTIFS môžete ľahko použiť v akejkoľvek situácii, keď COUNTIF existuje. Je dobré vedieť, že obe funkcie existujú.
Pozrime sa na túto tabuľku údajov:
Ak chcete zistiť, koľko ľudí je v úrovniach platov 1 až 2, môžete napísať súhrn funkcií COUNTIF takto:
= COUNTIF (B2: B7, "> = 1")-COUNTIF (B2: B7, "> 2")
Tento vzorec bude fungovať, pretože nachádzate všetko, čo je vyššie ako 1, ale potom odpočítate počet záznamov, ktoré sú mimo vášho medzného bodu. Alternatívne môžete použiť COUNTIFS takto:
= COUNTIFS (B2: B7, "> = 1", B2: B7, "<= 2")
Čítanie druhého textu je intuitívnejšie, takže by ste možno chceli použiť túto trasu. COUNTIFS je tiež výkonnejší, keď potrebujete vziať do úvahy viac stĺpcov. Povedzme, že chceme vedieť, koľko ľudí je v manažmente a v platovej úrovni 1. Nemôžete to urobiť iba pomocou COUNTIF; budeš musieť napísať
= COUNTIFS (A2: A7, „Manažment“, B2: B7, 1)
Tento vzorec by vám poskytol správny výsledok 2. Pred opustením tejto časti sa pozrime na logiku typu Or. Čo keby sme chceli zistiť, koľko ľudí je v manažmente alebo? Budete musieť pridať niekoľko COUNTIFS, ale existujú dva spôsoby, ako to urobiť. Najjednoduchší spôsob je napísať to takto:
= COUNTIF (A2: A7, "HR")+COUNTIF (A2: A7, "manažment")
Môžete tiež použiť pole a napísať tento vzorec poľa:
= SUM (COUNTIF (A2: A7, {"HR", "manažment"}))
Poznámka: Vzorce polí je potrebné potvrdiť pomocou klávesov „Ctrl+Shift+Enter“, nielen „Enter“.
Ako bude tento vzorec fungovať, uvidíte, že ste ako vstup zadali pole. Vypočíta teda výsledok do dvoch rôznych funkcií COUNTIF a uloží ich do poľa. Funkcia SUMA potom spojí všetky výsledky v našom poli a vytvorí jeden výstup. Náš vzorec bude teda vyhodnotený takto:
= SUM (COUNTIF (A2: A7, {"HR", "Management"})) = SUM ({2, 3}) = 5
Spočítajte jedinečné hodnoty
Teraz, keď sme videli, ako používať pole s funkciou COUNTIF, môžeme urobiť krok ďalej, aby nám pomohlo spočítať, koľko jedinečných hodnôt je v rozsahu. Najprv sa pozrime znova na náš zoznam oddelení.
= SUM (1/COUNTIF (A2: A7, A2: A7))
Vidíme, že existuje 6 buniek v hodnote údajov, ale existujú iba 3 rôzne položky. Na to, aby sme matematiku zvládli, potrebujeme, aby každá položka mala hodnotu 1/N, kde N je počet opakovaní položky. Ak by napríklad každý HR mal hodnotu iba 1/2, potom by ste ho po ich sčítaní dostali do počítadla 1 za 1 jedinečnú hodnotu.
Späť na náš COUNTIF, ktorý je navrhnutý tak, aby zistil, koľkokrát sa položka objaví v rozsahu. V D2 napíšeme vzorec poľa
= SUM (1/COUNTIF (A2: A7, A2: A7))
Ako tento vzorec bude fungovať, je pre každú bunku v rozsahu A2: A7, skontroluje sa, koľkokrát sa objaví. S našou ukážkou to prinesie množstvo
{2, 2, 3, 3, 3, 1}
Potom všetky tieto čísla premeníme na zlomky tak, že urobíme nejaké delenie. Teraz naše pole vyzerá
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Keď to všetko spočítame, dostaneme požadovaný výsledok 3.
Countif s dvoma alebo viacerými podmienkami - funkcia Countifs
Doteraz sme pracovali iba s funkciou COUNTIF. Funkcia COUNTIF môže naraz spracovať iba jedno kritérium. Na COUNTIF s viacerými kritériami musíte použiť funkciu COUNTIFS. COUNTIFS sa správa úplne rovnako ako COUNTIF. Stačí pridať ďalšie kritériá. Pozrime sa na nižšie uvedený príklad.
= COUNTIFS (B2: B7, "= 130")
COUNTIF a COUNTIFS v Tabuľkách Google
Funkcia COUNTIF a COUNTIFS funguje v Tabuľkách Google úplne rovnako ako v programe Excel:
