COUNTIF - Funkce statistické - Excel

Úvodem do funkce COUNTIF

V tomto článku je popsána funkce COUNTIF v Microsoft Excelu. Tato statistická funkce vám umožní spočítat počet buněk v oblasti, které splňují požadovanou podmínku.

Přehled kapitol o COUNTIF

• Trocha teorie COUNTIF (Syntaxe, operátory, zástupné znaky)
• Zdrojová tabulka pro výpočty
• Příklad na COUNTIF - zjisti počet Karlů
• COUNTIF větší/menší
• COUNTIF práce s texty využití *, ?
• COUNTIF - spočítat rozmezí
• COUNTIF - Práce s datum (čas)
• Složitější příklady na COUNTIF - dynamický rozsah, duplicitní hodnoty
• COUNTIF - řazení - využití k řazení
• COUNTIF pro více listů
• Ke stažení - v přípravě

Trocha teorie COUNTIF (Syntaxe, operátory, zástupné znaky)

Základní teoretický úvod do funkce COUNTIF.

Syntaxe

• oblast porovnávaných dat - kde budete hledat, sloupec (A1:A2367), celá tabulka (B10:BMW12345), lze použít i pojmenovanou oblast (TabulkaMoje) nebo odkaz na tabulku obsahující styl: Tabulka1[Částka]
• kritérium výběru - které data chceme spočítat (tj. muž, žena, číslo,...)

Použití matematických operátoru

Ve funkci COUNTIF lze pro kritérium použít i logické operátory:

• = rovná se
• < menší než
• > větší než
• <= menší nebo rovno
• >= větší nebo rovno
• <> různé od

Použití zástupných znaků

Při hledání textu lze použít i zástupné znaky (podobně jako když hledáte v databázích). V Excelu se používá hvězdička a otazník.

• * - zastupuje libovolný řetězec
• ? - zastupuje právě jeden libovolný znak

Základní teorii o funkci COUNTIF máte za sebou. Dále se podíváme na praktické použití této zajímavé funkce.

Zdrojová tabulka pro výpočty

V ukázkových příkladech budu používat tuto zdrojovou tabulku, na které ukážu praktické použití funkce COUNTIF.

Syntaxe COUNTIF

Tabulku nemusíte vytvářet je k dispozici soubor COUNTIF - zdrojová tabulka v MS Excel ke stažení zdarma.

První příklad na COUNTIF

Jednoduchý příklady pro začátek, jistěte kolikrát se v sloupci Částka objevuje hodnota 1.000:

1. hodnotu 1000 přímo zapíšeme

=COUNTIF(E6:E16;1000)

2. hodnota kritéria je přes buňku

=COUNTIF(E6:E16;E6)

3. Jak se odkazovat pokud tabulka obsahuje pojmenovaný sloupec tabulkaMoje

=COUNTIF(tabulkaMoje;E6)

4. Pokud je tabulka naformátována pomocí stylu, bude vzorec vypadat:

=COUNTIF(Tabulka1[Částka];1000)

Podobné příklady:

Pro jinou tabulku, která obsahuje seznam zaměstnanců. Potřebujete spočítat kolik jich má jméno "Karel". Opět lze použít funkci COUNTIF. Stačí použít:

=COUNTIF(E2:E12000;"Karel")

A máte hotovo :)

Poznámka: některé příklady se dají řešit i jinak například použitím filtru o kterých jsem psal v článku: Filtry v MS Excel

COUNTIF větší/menší

Jak spočítat počet čísel větších než 1000.

1. hodnotu 1000 přímo zapíšeme

=COUNTIF(E6:E16;">1000")

2. hodnota kritéria je přes buňku

=COUNTIF(E6:E16;">"&E6)

3. Pokud kriteriální hodnota je v buňce s rozsahem >1000

=COUNTIF(E6:E16;C29)

COUNTIF práce s texty

1. kolikrát se v tabulce objevuje slovo koruna

=COUNTIF(C6:C16;"Koruna")

2. kolikrát se v tabulce objevuje slovo které obsahuje text koruna (takže třeba i stokoruna)

=COUNTIF(C6:C16;"*koruna*")

3. kolikrát slov má 6 znaků

=COUNTIF(C6:C16; "??????")

Poznámka: Opět se dá využít odkazů na buňku, využít pojmenované oblasti a styly aplikované na danou tabulku. (v C38 je ?????? v C39 je *koruna*

=COUNTIF(C6:C16; C38) =COUNTIF(C6:C16; C39)

Další možností 3 znaky písmena CZ a další dva znaky:

=COUNTIF(B2:B12000;"???CZ??")

COUNTIF - spočítat rozmezí

Spočtěte z prvního sloupce počet čísel větších/rovno 4 a menší rovno než 8.

=COUNTIF(tabulkaMoje; ">=4")- COUNTIF(tabulkaMoje; ">8")

Prakticky provedete tak, že sečtete všechny hodnoty od 4 (včetně) nahoru a od toho odečtete hodnoty nad 8.

Poznámka: Doplněno na základě dotazů v komentářích. Jinak pozor na znaménka =<> (zálaží na požadovaném intervalu, který požadujete).

COUNTIF - Práce s datum (čas)

Kolikrát se objevuje datum 6.1.2013 (neboli 41280 - tomuto číslu odpovídá 6.1.2013)

=COUNTIF(D6:D16;41280) =COUNTIF(D6:D16;"6.1.2013") =COUNTIF(D6:D16;D11)

Pokud půjde v datum v požadovaném rozmezí (větší/menší):

=COUNTIF(D6:D16;">="&D11) =COUNTIF(D6:D16;">=6.1.2013") =COUNTIF(D6:D16;">=41280")

Kolikrát se datum shoduje s dnešním dnem?

=COUNTIF(D6:D16;DNES())

Poznámka: Podrobněji v článku: Datum a čas teoretický úvod.

Složitější příklady na COUNTIF

Potřebujeme zjistit kolik Adamu a Ev je v oblasti? Opět nic složitého:

=COUNTIF(E2:E12000;"Adam")+COUNTIF(E2:E12000;"Eva")

Podobně pokud budete chtít spočítat počet čísel 666, 777, 888

=COUNTIF(A2:A12000;666)+COUNTIF(A2:A12000;777)+COUNTIF(A2:A12000;888)

Rozsah (větší a rovno 10 a menší než 100)

=COUNTIF(A2:A12000; ">=10")- COUNTIF(A2:A12000; ">100")

Dynamický rozsah

Rozsah dynamicky (větší než průměr v dané oblasti)

=COUNTIF(data;">="&PRŮMĚR(data))

Jedinečné hodnoty

Funkci COUNTIF lze použít pro zjištění, zda se v tabulce nacházejí duplicitní hodnoty. Výsledek TRUE (ano jsou duplicitní)

=COUNTIF($E$6:$E$16;E7)>1

Více oblastí COUNTIFS

Pokud potřebujete kontrolovat více oblastí doporučuji použít funkci: COUNTIFS

COUNTIF - řazení

Funkci COUNTIF lze využít i k určení pořadní, například potřebuji seřadit jména abecedně (abecedu), Pokud mám čísla lze využít funkci RANK, ale u písmen mohu využít funkci COUNTIF a zjisti počet hodnot větších rovno než a mám hotovo.

=COUNTIF($B$7:$B$11;"<="&B7) =COUNTIF($B$7:$B$11;"<="&B8)

nebo opačně

=COUNTIF(B15:B19;">="&B15) =COUNTIF(B16:B20;">="&B16)

UKázka

COUNTIF pro více listů

První možnost je zkopírovat data na jeden list a použít výše uvedené postupy. V případě, že chcete ponechat na svých místech je také možnost. Viz dále.

Pokud máte data na více litech, musí se použít k výpočtu funkce SOUČIN.SKALÁRNÍ, NEPŘÍMÝ.ODKAZ, data se nachází na dvou listech List1, List2:

=SOUČIN.SKALÁRNÍ(COUNTIF(NEPŘÍMÝ.ODKAZ("List"&{1;2}&"!C6:C16");"Koruna"))

Ke stažení příklady na COUNTIF

Hotová tabulka je hotova COUNTIF - hotova tabulka v MS Excel ke stažení zdarma.

Závěrem ke COUNTIF

Jak používáte funkci COUNTIF v Microsoft Excelu. V komentářích se můžete zmínit, zda tuto funkci používáte nebo využíváte jiné postupy (filtry, kontingenční tabulky, VBA kódy)?

Článek byl aktualizován: 19.09.2020 10:59

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.