Příklad počtu hodnot se zadanou podmínkou (COUNTIF) přes listy

Pro informace o výuce Excelu klikněte zde

Minule jsem uvedl příklad součtu hodnot se zadanou podmínkou (SUMIF) přes listy. Toto je pokračování s obdobnou funkcí COUNTIF, která nám nevrací součet, ale počet buňek.

Nápověda nám o funkci COUNTIF říká:

Spočítá buňky v oblasti, které odpovídají zadaným kritériím.

Syntaxe

COUNTIF(oblast;kritéria)

Oblast   je oblast buněk, ve které chcete spočítat buňky.

Kritéria   definuje, které buňky se budou počítat. Udává se jako čísla, výrazy nebo text. Například 32, „32“, „>32“, „jablka“.

Komentář

Aplikace Microsoft Excel poskytuje další funkce, které slouží k analýze dat na základě podmínky. Chcete-li například vypočítat součet na základě textového řetězce nebo čísla v určité oblasti, použijte funkci SUMIF. Pokud chcete, aby vzorec vrátil jednu nebo dvě hodnoty na základě podmínky, například sleva při prodeji na základě prodaného množství, použijte funkci KDYŽ.

Příklad:

Vzorec ve tvaru =COUNTIF(A2:A7;“a*“) nám vrátí 3.

Kde:

Oblast je A2:A7

Kritéria: „a*“ – počet všech buněk, které začínají na písmeno a

Další příklady: data = pojmenovaná oblast buněk

=COUNTIF(data; 21) počet buněk obsahující hodnotu 21
=COUNTIF(data; 10) + COUNTIF(data;20) počet buněk obsahující hodnotu 10 nebo 20
=COUNTIF(data; „<0“) počet buněk obsahujících záporné číslo
=COUNTIF(data; „<>0“) počet nenulových hodnot
=COUNTIF(data; „>=1“)- COUNTIF(data; „>10“) počet buněk, které obsahují hodnotu mezi 1 a 10
=COUNTIF(data; „ano“) počet buněk, které obsahují slovo ano( nerozlišuje se velikost písmen)
=COUNTIF(data; „*“) počet buněk, které obsahují nějaký libovolný text
=COUNTIF(data; „*s*“) počet buněk, které ve svém textu obsahují písmo s( nerozlišuje se velikost)
=COUNTIF(data; „????“) počet slov dlouhých čtyři znaky
=COUNTIF(data;DNES()) počet buněk osahujících aktuální datum
=COUNTIF(data;“>=“&PRŮMĚR(data)) počet buněk s hodnotou větší než je průměr

Pozn.: pokud se kritérium neodkazuje na buňku, musí být v uvozovkách.
Pokud máme hodnotu v buňce, zadáme kritérium jako „>=“&C1.

Problém opět nastává, pokud bychom chtěli použít funkci COUNTIF přes více listů, např. přes listy List1, List2, List3

Vzorec ve tvaru =COUNTIF(List1:List3!A2:A7;“a*“) nám vrátí chybu #HODNOTA!, stejně jako funkce SUMIF

Funkce COUNTIF taky „neumí“ počítat přes více listů, ale opět to jde obejít pomocí maticového vzorce nebo funkce SOUČIN.SKALÁRNÍ.

Příklady:

1. S odkazem na listy List1 až List3 pomocí funkce ŘÁDEK a NEPŘÍMÝ.ODKAZ tj. „‚List“&ŘÁDEK(NEPŘÍMÝ.ODKAZ(„1:3″))&“‚!A2:A7“

 funkce SOUČIN.SKALÁRNÍ:
=SOUČIN.SKALÁRNÍ(COUNTIF(NEPŘÍMÝ.ODKAZ(„‚List“&ŘÁDEK(NEPŘÍMÝ.ODKAZ(„1:3″))&“‚!A2:A7″);“a*“))

maticový vzorec
{=SUMA(COUNTIF(NEPŘÍMÝ.ODKAZ(„‚List“&ŘÁDEK(NEPŘÍMÝ.ODKAZ(„1:3″))&“‚!A2:A7″);“a*“))}

Tento příklad lze použít pro součet listů, které mají stejný počáteční název a liší se pouze číslem na konci názvu.

 2. S odkazem na listy pomocí maticové konstanty tj. „List“&{1;2;3}&“!A2:A7″

 funkce SOUČIN.SKALÁRNÍ:
=SOUČIN.SKALÁRNÍ(COUNTIF(NEPŘÍMÝ.ODKAZ(„List“&{1;2;3}&“!A2:A7″);“a*“))

maticový vzorec
{=SUMA(COUNTIF(NEPŘÍMÝ.ODKAZ(„List“&{1;2;3}&“!A2:A7″);“a*“))}

3. S pomocí maticové konstanty definované jako název

Často potřebujeme spočítat součet z listů pojmenované názvy měsíců. Pro náš vzorec jsi připravíme maticovou konstantu s názvem Mesice.
Definujeme název: Vložit – Název – Definovat, do názvu napíšeme Mesice a do Odkaz na zapíšeme maticovou konstantu ve tvaru : ={„Leden“;“Únor“;“Březen“;“Duben“;“Květen“;“Červen“;“Červenec“;“Srpen“;“Září“;“Říjen“;“Listopad“;“Prosinec“}

a zapíšeme vzorec

funkce SOUČIN.SKALÁRNÍ:
=SOUČIN.SKALÁRNÍ(COUNTIF(NEPŘÍMÝ.ODKAZ(„‚“&Mesice&“‚!A2:A7″);“a*“))

maticový vzorec
{=SUMA(COUNTIF(NEPŘÍMÝ.ODKAZ(„‚“&Mesice&“‚!A2:A7″);“a*“))}

Pozn.: Závorky {} na začátku a na konci se u maticových vzorců nepíšou, ale zadávají se pomocí kl. zkratky CTRL+SHIFT+ENTER
Ale naopak u maticových konstant se zadat musí!

4 Comments

  1. Dobrý den, lze nějak definovat kritérium tak, abych nemusel používat více COUNTIF? Např. ve vzorci
    =COUNTIF(A:A;“CZ“)+COUNTIF(A:A;“SK“)+…
    Děkuji za odpověď

    • Dobrý den,
      můžete zkusit použít funkci =SOUČIN.SKALÁRNÍ((Oblast=“CZ“)+(Oblast=“SK“))

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *