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

Pro informace o výuce Excelu klikněte zde

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

Sečte buňky určené zadanou podmínkou.

Syntaxe

SUMIF(oblast;kritéria;součet)

Oblast   je vyhodnocovaná oblast buněk.

Kritéria   udávají podmínku, která definuje sčítané buňky. Vyjadřuje se číslem, výrazem nebo textem. Podmínka může mít například tvar: 32, “32”, “>32”, “jablka”.

Součet   obsahuje přímo sčítané buňky.

Komentář

  • Tyto buňky se sčítají pouze tehdy, pokud vyhovují podmínce zadané v argumentu kritéria.

  • Pokud je argument součet vynechán, sečtou se buňky v oblasti.

  • Aplikace Microsoft Excel poskytuje další funkce, které lze použít k analýze dat na základě podmínek. Například k výpočtu počtu výskytů textového řetězce v oblasti buněk můžete použít funkci COUNTIF. Chcete-li, aby vzorec vrátil jednu ze dvou hodnot na základě podmínky, například množstevní slevu na základě prodaného množství, použijte funkci KDYŽ.

Příklad:

Vzorec ve tvaru =SUMIF(A2:A7;”a”;B2:B7) nám vrátí 38.

Kde:

Oblast kritérií je A2:A7

Kritéria: “a” – součet všech buněk, které mají v textu přesně a (velikost nerozhoduje)

případně  “a*” – součet všech buněk, které začínají na písmeno a

Součet je oblast B2:B7

Další příklady: (zde není zadán nepovinný argument funkce Součet, pokud není zadán jsou použity buňky oblasti)

vzorec vrácená hodnota popis
=SUMIF(B2:B7;”>=14″) 29 vrátí součet buněk, ve kterých je hodnota větší než 14
=SUMIF(B2:B7;”<=10″)+SUMIF(B2:B7;”>=15″) 25 vrátí součet buněk, jejichž hodnota je menší než 10 nebo větší než 15

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 nastává, pokud bychom chtěli použít funkci SUMIF přes více listů, např. přes listy List1, List2, List3

Logicky by jsme předpokládali vzorec ve tvaru =SUMIF(List1:List3!A2:A7;”a”;List1:List3!B2:B7), ale ten nám vrátí chybu #HODNOTA!

Funkce SUMIF totiž normálně “neumí” počítat přes více listů, ale jde to 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Í(SUMIF(NEPŘÍMÝ.ODKAZ(“‘List”&ŘÁDEK(NEPŘÍMÝ.ODKAZ(“1:3″))&”‘!A2:A7″);”a”;
NEPŘÍMÝ.ODKAZ(“‘List”&ŘÁDEK(NEPŘÍMÝ.ODKAZ(“1:3″))&”‘!B2:B7”)))

maticový vzorec
{=SUMA(SUMIF(NEPŘÍMÝ.ODKAZ(“‘List”&ŘÁDEK(NEPŘÍMÝ.ODKAZ(“1:3″))&”‘!A2:A7″);”a”;
NEPŘÍMÝ.ODKAZ(“‘List”&ŘÁDEK(NEPŘÍMÝ.ODKAZ(“1:3″))&”‘!B2:B7”)))}

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Í(SUMIF(NEPŘÍMÝ.ODKAZ(“List”&{1;2;3}&”!A2:A7″);”a”;
NEPŘÍMÝ.ODKAZ(“List”&{1;2;3}&”!B2:B7″)))

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

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Í(SUMIF(NEPŘÍMÝ.ODKAZ(“‘”&Mesice&”‘!A2:A7″);”a”;NEPŘÍMÝ.ODKAZ(“‘”&Mesice&”‘!B2:B7”)))

maticový vzorec
{=SUMA(SUMIF(NEPŘÍMÝ.ODKAZ(“‘”&Mesice&”‘!A2:A7″);”a”;NEPŘÍMÝ.ODKAZ(“‘”&Mesice&”‘!B2:B7”)))}

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

Napsat komentář

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