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