Stáhněte si ukázkový sešit
Tjeho tutoriál ukazuje, jak pomocí vzorců COUNTIF a SUMPRODUCT počítat s více kritérii v aplikacích Microsoft Excel a Google Sheets.
Funkce COUNTIFS
Funkce COUNTIFS vám umožňuje počítat hodnoty, které splňují více kritérií. Základní struktura vzorce je:
1 | = COUNTIFS (rozsah 1, podmínka 1, rozsah 2, podmínka 2) |
Podívejme se na příklad. Níže uvidíte seznam obsahující známky pro studenty z angličtiny a matematiky. Počítejme všechny studenty s výsledky testů nad 60 v matematice.
V tomto případě testujeme dvě kritéria:
- Pokud jsou výsledky testu vyšší než 60 (rozsah B2: B11)
- Pokud je předmět „matematický“ (rozsah je C2: C11)
Níže vidíte, že existují 4 studenti, kteří v matematice dosáhli skóre nad 60:
Další podmínky můžete vyzkoušet přidáním dalšího rozsahu a kritérií. Pokud byste například chtěli spočítat počet studentů, kteří v matematice dosáhli skóre 60 až 80, použili byste toto kritérium:
- Pokud jsou výsledky testu vyšší než 60
- Pokud jsou výsledky testu nižší než 80
- Pokud je předmětem „matematika“
Zde jsou zvýrazněné části, kde se tyto příklady testují.
Výsledkem jsou v tomto případě 3.
Funkce SUMPRODUCT
Funkce COUNTIFS je poměrně přímočará, ale stejného úkolu můžete dosáhnout pomocí pokročilejší funkce SUMPRODUCT. Funkce SUMPRODUCT otevírá svět možností se složitými vzorci (viz návod SUMPRODUCT). I když je funkce COUNTIFS nejjednodušším způsobem počítání na základě více kritérií, možná budete chtít začít se s touto výkonnou funkcí seznamovat. Jeho syntaxe je:
1 | = SUMPRODUCT (Rozsah 1 = Podmínka 1*Rozsah 2 = Podmínka 2) |
Pomocí stejného příkladu výše můžete pro počítání všech studentů s výsledky testů nad 60 v předmětu „Matematika“ použít tento vzorec:
1 | = SUMPRODUCT ((B3: B13> 60)*(C3: C13 = ”Math”)) |
První podmínka testuje, které hodnoty v tomto rozsahu jsou větší než 60, a druhá podmínka testuje, které hodnoty v tomto rozsahu se rovnají „Math“. To nám dává řadu booleovských hodnot 1 (TRUE) nebo 0 (FALSE):
<>
Funkce SUMPRODUCT pak sečte všechny položky v poli, což nám dává počet studentů, kteří získali v matematice asi 60 bodů.
COUNTIFS a SUMPRODUCT pro více kritérií v Tabulkách Google
K počítání více kritérií můžete použít stejné struktury vzorců jako výše v Tabulkách Google.