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

2 komentáře

Napsat komentář

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