Přeskočit navigaci | Přeskočit na novinky

Nový videokurz na Seduo: Excel pro začátečníky

     

Jste zde: Úvodní stránka » excel » funkce » countifs-funkce-statisticke-excel

COUNTIFS - funkce statistická - Excel


COUNTIFS zjistí počet hodnot odpovídající požadavkům v daných oblastech (v dané oblasti). Nejen teoreticky, ale hlavně praktické příklady včetně sešitu ke stažení zdarma.

Doplněno: 22.8.2.2014

Úvodem

Microsoft Excel - funkce COUNTIFS - logo

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:


Hledáte-li funkci COUNTIF doporučuji článek COUNTIF - Funkce statistická.

Teoretický úvod COUNTIFS

Funkce COUNTIFS zjistí počet hodnot odpovídající požadavkům v dané oblasti (v daných oblastech).

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.

COUNTIFS - funkce - zdroj

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 soubor ve formátu *.xlsx 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í).

COUNTIFS - funkce - zdroj

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 soubor ve formátu *.xlsx 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 soubor ve formátu *.xlsx ke stažení zdarma.

2)

Soubor Pokročilé COUNFIFS v Microsoft Excel soubor ve formátu *.xlsx ke stažení zdarma.

3)

Soubor Možné chyby ve funkcích ¨COUNTIFS soubor ve formátu *.xlsx 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í).

COUNTIFS - funkce - chyby - MS Excel 2010

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 soubor ve formátu *.xlsx 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.

COUNTIFS - funkce - zdroj

Ř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í

COUNTIFS - funkce - hotovo

Sešit ke stažení

Soubor COUNFIFS - SUMA - maticově v Microsoft Excel soubor ve formátu *.xlsx 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:

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

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 *, ? ~ soubor ve formátu *.xlsx 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:

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: 01.11.2014 09:32

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, PowerPoint) se věnuji od roku 2006. Své vědomosti a zkušenosti dávám k dispozici na různých školeních a konzultacích, ale také na tomto webu. K dispozici na tomto webu je mnoho návodu, tipů a triků včetně desítek různých šablon.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil

Doporučte tento článek přátelům

Pokud vám článek pomohl, případně si myslíte, že může pomoci i někomu dalšímu, budu rád když jej sdílením doporučíte přátelům - děkuji:



Komentáře


Michal

Přidáno: 01.02.13 09:10

Hoj, mám dotaz, pokud mám trochu složitější kalkulačku s použitím funkce COUNTIFS. Tak mi skvěle běží na Office 2007 ale mám problém s přečtením vzorce v Office 2003 ptože jej nezná. Je nějaká relevantní náhrada v Office 2003 nebo jakým způsobem by se to dalo obejít když opominu aktualizaci na MS 2007. Děkuji

Pavel Lasák

Přidáno: 01.02.13 16:25

To Michal: zkusit to přes funkci =SOUČIN.SKALÁRNÍ (SUMPRODUCT)

Michal

Přidáno: 03.02.13 21:28

To Pavel : no tak jsem ozkoušel, ale bohužel mi to hází jen hodnotu VALUE v případě kdy pouze zaměním funkci .... :/

Michal

Přidáno: 10.02.13 20:00

pro příklad přikládám vzorec : =_xlfn.COUNTIFS('denní přehled'!$M$2:$M$1000;G1;'denní přehled'!$K$2:$K$1000;TOTAL!A2) jakým způsobem je možné toto převést na součin.skalarní ? Děkuji

Pavel Lasák

Přidáno: 10.02.13 20:51

To Michal: pošli ukázku na lasak@centrum.cz

Lukas

Přidáno: 12.05.13 11:32

Ahoj, mám dotaz jak bys řešil toto? 1)Zjistěte počet žen, které byly narozeny od 1. Září 1991 do 31.srpna 1993 nebo od 1.září 1994 do 31. Srpna 1996???. ...Bohužel, ať zkoušim co zkoušim, tak mi to hází blbosti... :-/ Dělá se to asi ze sloupce, kde je 100 různých datumů a ze sloupce žena, muž:-) Předem moc díky.

Pavel Lasák

Přidáno: 12.05.13 18:37

To Lukas: Použít: =COUNTIFS(F5:F15;">=2.1.2013";F5:F15;"<=4.1.2013") nebo použit COUNTIF - spočítat dvě rozmezí např. první =COUNTIF(F4:F20; ">=41278")- COUNTIF(F4:F20; ">41284") podobně druhé a ty dvě čísla sečetl dohoromady. Datum zadávat jako číslo např 4.1.2013 = 41278.

Jana T

Přidáno: 12.08.13 13:02

Dobry den, mam dotaz jak u teto funkce zajistit, aby ignorovala skyte radky? Mám velký soubor dat a potřebuji zjistit kolikrát se mi daná služba - z filtru objevuje v urcitem intervalu. ( napr. kolik lidi si koupilo košily v ceně od 1000 do 1500 a potom kolik lidí si koupilo svetr v hodnotě od 800 - 1200. intervaly si muzu ruzne menit, ale pokud si dam filtr na kosile, tak mi to bere cely sloupec, kde jsou vsechny sluzby. můzete mi, prosim, poradit, jestli se da napsat pred funkci nejaka dalsi funkce, ktera by mi zajistila, ze bude countifs brat jen data z filtru. dekuji moc Jana T

Pavel Lasák

Přidáno: 12.08.13 16:59

To Jana T: Například ve spojení s funkcí SUBTOTAL, více v článku: Unikátní filtrovaná data počet nebo Zavislá filtrovaná data suma - Excel

docturek

Přidáno: 05.01.14 20:21

Ahoj. Používám funkci COUNTIFS, ale nefunguje mi správně. Mám zadané 4 podmínky. =COUNTIFS(Evidence!$D:$D;"M";Evidence!$K:$K;"ANO";'Karta uživatele'!$BW:$BW;">15";'Karta uživatele'!$BW:$BW;"<26") Nepočítá mi to správně. První dvě podmínky ano, ale jakmile přidám 3. a čtvrtou podmínku, tak mi dá výsledek nula. Kde je chyba?? Děkuji za případné reakce.

Pavel Lasák

Přidáno: 05.01.14 22:32

To docturek: Pokud nemám k dispozici zdrojový sešit tak se těžko radí. Zkusit funkci rozdělit zda 3 a 4 podmínku spočte pokud jsou samostatně správně?

docturek

Přidáno: 06.01.14 10:26

Už vím kde je chyba. Mám na listu "Karta uživatele" čísla (věk uživatele), který je vypočten z datumu narození a upraven v buňce pomocí formátu (rr). Counifs nefunguje, protože excel převede roky na vysoká čísla a dál s nimi počítá. Dá se toto nějak obejít??

Pavel Lasák

Přidáno: 06.01.14 19:29

To docturek: Funkce ROK.

docturek

Přidáno: 06.01.14 21:19

Děkuji. Funguje :)

pp

Přidáno: 07.01.14 13:32

zdravim, chtel bych se poradit ohledne vhodne fce obdobne klasickemu COUNTIF, ktera ale umi pracovat s nestejnorodou nespojitou oblasti. np.: #1 spocitej vsechny neprazdne bunky z vyberu A1, C1, E1, G1. #2 secti hodnoty bunek z tehoz vyberu. lamu si s tim delsi dobu a ne a ne na to prijit; COUNTIFS nepomohlo. nezni to nejak slozite, ale zasekl jsem se radne:) dik za reakce! pp

ssimon

Přidáno: 13.01.14 20:24

Jak sestavit spravne fungujici countif či countifs v pripade ze v oblasti, nad kterou sleduji cetnost je * (hvezdicka)? Priklad ve sloupci A mam bunky s ruznym obsahem a s duplicitami. Chci zjistit cetnost jednotlivych polozek. Prekopiruji je treba do sloupce C a necham odstranit duplicity. Problm nastane kdyz chi spocitat cetnost vyskytu * (hvezdicky). Rekneme ze v bunce C4 bude hvezdicka. Vzorec bude =countifs(A:A;C4) Ale nedostanu pocet vyskytu hvezdicek, ale pocet bunek obsahujicih cokoliv. Jak to napravit? Predme diky za pomoc / radu

ssimon

Přidáno: 13.01.14 20:32

To pp prvni dotaz - funkce POČET2 (=počet2(A1;C1;E1;G1)) druhy dotaz - funkce SUMA (=suma(A1;C1;E1;G1))

Jojo

Přidáno: 25.08.14 09:30

To ssimon: =COUNTIF(A1:A10;"~*") -- použiť "vlnku" pred hviezdičkou (je na klávese ceľkom vľavo hore)(EN klávesnica)

Pavel Lasák

Přidáno: 29.08.14 14:55

To PP: Po4et s COUNTIFS se musí pro nestejnorodou oblast jednotlivé oblasti sčítat: =COUNTIFS(B4;">0")+COUNTIFS(B6;">0")+COUNTIFS(B8:B9;">0")

David

Přidáno: 17.12.14 15:04

Dobrý den, kdyz pouziji Vasi prvni tabulku "Zakladni priklad", tak jak dostanu do B3 pocet znacek aut na sklade? Mate v seznamu Ford, Skoda a BMW, takze by tam mela byt 3. Jak toto udelat? :) Diky, D.

Pavel Lasák

Přidáno: 06.01.15 19:50

To David: Využít funkce INDEX a POZVYHLEDAT

David

Přidáno: 08.01.15 10:16

Diky, zkusim to... Sice me to reseni napadlo, ale nefungovalo to. Jeste jednou se s tim poperu. Jo a preji hodne zdravi, stesti a uspechu v roce 2015!!! :)

Marek

Přidáno: 29.01.15 13:43

Dobrý den, mám vytvořený seznam hráčů (jmen). Některá se mi záměrně opakují, takže je mám ve sloupci třeba 10x. Jak ale zjistím jejich celkový počet? Když dám sečíst celý sloupec, tak je součet zkreslený, protože mám jednoho hráče napočítaného 10x. Děkuji za odpověď.

Marek

Přidáno: 29.01.15 13:57

Jsem slepej, je to v příspěvku nademnou. Omlouvám se za spamování :-(

Jirka

Přidáno: 23.09.15 16:30

Dobrý den, chci se zeptat, jestli lze pomocí COUNTIFS vyřešit následující problém. V sloupci A a B mám čísla a pokud jejich součet je větší než 2, tak bych je potřeboval započítat. Takto mám stovky řádků a potřebuji zjistit, na kolika z nich je součet čísel v sloupcích A a B větší než 2. Ale funkce =COUNTIFS(A:B;(A+B)>2) nefunguje. Snad je to alespoň trochu pochopitelné. Děkuji.

Tomina

Přidáno: 12.05.16 09:03

Dobrý den, existuje nějaký postup na součet hodnot z vícero vzorců? Konkrétně: Při každém transportu mám tabulku, ve které je vzorec "POČET2", který mi ukazuje počet odeslaných vzorků. Chtěla bych, aby se pak někde do jedné buňky sečetly všechny hodnoty ze všech vzorců POČET2. Jde to? :) Děkuji za odpověď.


Přidání nového komentáře

Poznámka: Z časových důvodu není v mých sílách zodpovědět všechny dotazy. Děkuji za pochopení.




(vlož číslo padesátpět)

Z bezpečnostních důvodu dočasně nemůžete použít HTML značky, kromě <code> a </code> (pro vkládání VBA kódu). Děkuji za pochopení.
Děkuji, za Vaše komentáře.

Za obsah komentářů neodpovídám, jelikož jsou komentáře publikovány ihned po jejich napsání čtenářem. Toto nemohu nijak ovlivnit. Přesto si vyhrazuji možnost jakýkoli neslušný komentář smazat bez udání důvodu. V komentářích si tykáme. ;-)





Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Newsletter

Přihlaste se odběru novinek a získejte:
Ebook zdarma -
10 kroků ke zvládnutí (Word, Excel, PowerPoint)
Šipka Kniha 3D Více o ebooku ...

TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2016 | 24848

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.