COUNTIFS - funkce statistická - Excel

Úvodem

V tomto článku je popsána funkce COUNTIFS anglický název (COUNTIFS). Pro přehlednost a rychlejší nalezení hledaných informací je článek rozdělen do těchto kapitol:

• Syntaxe COUNTIFS - matematické operátory
  • Použití matematických operátoru (větší, menší, < >)
  • Použití zástupných znaků (?, *)
• Základní příklady jedna, dvě podmínky
• Interaktivní příklad - tabulka v on-line Excelu WebApp
• Složitější příklady
  • Použití matematických operátoru hrátky s datem (větší, menší, < >)
  • Použití zástupných znaků hrátky s textem (?, *)
  • Ke stažení
• Sešity ke stažení - s praktickými ukázkami zdarma
• Chyby a komplikace s funkcí COUNTIFS
• Použití COUNTIFS, SUMA v maticovém počtu
• Počet buněk se speciálními znaky - *, ? ~
• Související články

Teoretický úvod COUNTIFS

Syntaxe CZ:

COUNTIFS(oblast_1; kriterium_1 [;oblast_2; kriterium_2])

Syntaxe EN:

COUNTIFS(oblast_1, kriterium_1 [,oblast_2, kriterium_2] )

Funkce COUNTIFS má tyto argumenty:

• oblast_1 - oblast ze které se bude hledat hodnoty
• kriterium_1 - kritérium které se bude prověřovat
• oblast_2 - nepovinný argument - další oblast ze které se bude hledat hodnoty
• kriterium_2 - nepovinný argument - další kritérium které se bude prověřovat (pro oblast2)

Oblastí a kriterií až 127.

Poznámky:

• Oblast a kritérium spolu souvisí, maximální počet oblasti a jí příslušného kritéria je 127.
• Oblasti musí mít stejný rozměr (stejný počet řádků a sloupců) a nesmí se překrývat. V jedné funkci musí být velikost oblasti oblast_1 shodná s velikosti oblasti oblast_2 atd.
• Pro kritéria lze používat zástupné znaky podobně jako v COUNTIF, tj. (*) - libovolný řetězec, (?) - libovolný znak a matematické operace (<,>,=)
• Pozor prázdná buňka v oblasti se bere jako nula (to když budete hledat chybu e ne ji najít).

Použití matematických operátoru

Ve funkci COUNTIFS 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

Zpět na seznam kapitol o funkci COUNTIFS

Základní příklad

Na těchto jednoduchých příkladech ukázují použití (pro lépší pochopení), jak funkce pracuje. Ukázková data v příkladech jsou náhodná a jakákoli podobnost se skutečnosti je čistě náhodná.

Zdrojová tabulka

V dalším textu používám pro demonstraci tuto zdrojovou tabulku.

Počet požadovaných hodnot v tabulce (jedna podmínka)

Autobazar má k dispozici seznam automobilů. Z tabulky potřebujete zjistit kolik aut je (například) z automobilky FORD.

Pro výše uvedenou tabulku lze využít tento vzorec:

=COUNTIFS(B5:B15;"Ford")

Poznámka

Tohle jde vyřešit i funkcí COUNTIF doporučuji samostatný článek COUNTIF - Funkce statistická.

=COUNTIF(B5:B15;"Ford")

Počet požadovaných hodnot v tabulce (dvě podmínky)

Počet hodnot z jedné oblasti spočítat umíte. Jako další kolik je aut značky FORD používající palivo Benzín?

Opět použijeme data z předchozí tabulky.

=COUNTIFS(B5:B15;"Ford";D5:D15;"benzín")

Ke stažení

Soubor Základy COUNFIFS v Microsoft Excel ke stažení zdarma.

Zpět na seznam kapitol o funkci COUNTIFS

Interaktivní příklad

Tabulka se načíta (prosím o strpení, načtení může chvíli trvat) z webové aplikace Excel WebApp, praktickou ukázku vidíte on-line.

Složitější příklady

V těchto příkladech použijete aritmetické operátory (<,>,=) a také zástupné znaky (?,*) pro text.

Zdrojová tabulka

Pro testování doporučuji vytvořit takovouto tabulku. Případně si ji na konci kapitoly stáhnout včetně řešení).

Hrátky s datem - Použití matematických operátoru

Potřebujete-li získat počet hodnot v intervalu:

=COUNTIFS(F5:F15;">=2.1.2013";F5:F15;"<=4.1.2013")

Pokud je odkaz na zadané datum v nějaké buňce

=COUNTIFS(F5:F15;">="&D34;F5:F15;"<="&D36)

Hrátky s textem - Použití zástupných znaků

Slova začínající Stov a dále jakýkoliv text. Tento představuje hvězdička (*).

=COUNTIFS(D5:D15;"Stov*")

Slova začínající Stovk a dále jen jeden jakýkoliv znak. Tento představuje otazník (?).

=COUNTIFS(D5:D15;"Stovk?")

Ke stažení

Soubor Pokročilé COUNFIFS v Microsoft Excel ke stažení zdarma.

Zpět na seznam kapitol o funkci COUNTIFS

Sešit ke stažení

Probíraná problematika je zpracována v sešitu Microsoft Excelu 2010 a je k dispozici ke stažení zdarma.

1)

Soubor Základy COUNFIFS v Microsoft Excel ke stažení zdarma.

2)

Soubor Pokročilé COUNFIFS v Microsoft Excel ke stažení zdarma.

3)

Soubor Možné chyby ve funkcích ¨COUNTIFS ke stažení zdarma.

Zpět na seznam kapitol o funkci COUNTIFS

Chyby ve funkci

Chyby, které mohou nastat při zápisu funkce COUNTIFS. Většinou jde o mé postřehy, když zápis nefungoval (nebo výsledný počet neodpovídal přání).

Syntaktické chyby

Jednodušší jsou vidět na první pohled, není zobrazená požadovaná hodnota, ale chyba. Proto se opravují vcelku jednoduše. Může nastat například u nestejně velkých oblasti.

Různá velikost oblasti

Logické chyby

Zapomenuté znaménko - pokud kopírujete funkce, můžete zapomenout matematický operátor (přitom výsledek to spočítá), ale ten je chybný.

Jiná oblast - vkládáte, kopírujete vzorce a nemáte správně nastaveno absolutní a relativní odkazování.

Chyby v datech

Záleží odkud, a jak data získáváte. Většina správně navržených databází kontroluje jejich zápis, ale pokud jde o nekontrolovaná data, která vkládají uživatelé. Doporučuji kontrolovat!

Špatný druh místo druhu hodnoty "DATUM" je text, který je naformátován jako datum. Funkce je napsána správně, ale výsledek je chybný, jelikož špatně zapsanou hodnotu nespočte.

Poznámka: Narazily jste na nějakou chybu, která Vás potrápila? Můžete zmínit v komentářích a usnadnit život dalším čtenářům.

Ukázky chyb ke stažení:

Soubor Možné chyby ve funkcích ¨COUNTIFS ke stažení zdarma.

Zpět na seznam kapitol o funkci COUNTIFS

Použití SUMA, COUNTIFS v maticovém počtu

Funkci COUNTIFS, která je od Excel 2007 lze nahradit například SUMA maticově.

Předpokládám, že máte tabulku, v jednom sloupci muži, ženy, ve druhém pracující, nepracující. Potřebujeme zjsiti kolik žen pracuje, kolik nepracuje, stejně tak pro muže.

Řešením je využít COUNTIFS, nebo maticově funkci SUMA:

=COUNTIFS($B$13:$B$23;$B5;$C$13:$C$23;C$4)

Maticově využijeme funkci SUMA (nezapomenou stisk Ctrl+Alt+Enter):

{=SUMA(($B$14:$B$24=$B9)*($C$14:$C$24=C$8))}

Řešení

Sešit ke stažení

Soubor COUNFIFS - SUMA - maticově v Microsoft Excel ke stažení zdarma.

Zpět na seznam kapitol o funkci COUNTIFS

Počet buněk se speciálními znaky *, ? ~

Dotaz z komentářů, za který děkuji stejně jako za rychlou odpověď jednoho z čtenářů. Pojďme si, ale ukázat více (když už .

Jak na hledání speciální znaků jako je * (hvězdička), ? (otazník) ~ (tilda, vlnovka). Protože hvězička nahrazuje libovolný poičet znaků, k zjištění počtzu buněk obsahující * (potzažmo ?) se musí použít ~ (vlnovka).

Ukázka:

Nefunkční zadání, které najde všchny buňky, tj. buŇky které mají obsah, viz následující kapitola, nepúrazné buňky.

=COUNTIFS(B4:B11;"*")

Pro nalezení buněk s hvězdičkou nutno použít:

=COUNTIFS(B4:B11;"~*")

Další příklady, například když hledáte onu vlnovku ;) atd.

=COUNTIFS(B4:B11;"~~") =COUNTIFS(B4:B11;"~**") =COUNTIFS(B4:B11;"a~*a") =COUNTIFS(B4:B11;"?~**") =COUNTIFS(B4:B11;"?~?a") =COUNTIFS(B4:B11;"*~~a")

Poznámka: ~ Vlnovka (tilda) se nachází na klavese 1, stačí stitk AltGr + 1

Ke stažení

Soubor Počet buněk se speciálními znaky *, ? ~ ke stažení zdarma. Soubor využívá makra.

Související články

Dále vás ve spojitosti s funkci COUNTIFS může zajímat:

• COUNTIF
• SUMIF
• SUMIFS
• Kontingenční tabulky - seriál - aneb vše od A do Z

Závěrem

Chcete-li doplnit další informace k funkci COUNTIFS? Máte netradiční použití této funkce? Využijte komentáře pro doplnění.

Č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 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). 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 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře