Stiahnite si ukážkový zošit
Tento tutoriál predvedie, ako vypočítať „medzisúčet ak“, pričom počíta iba viditeľné riadky s kritériami.
SUBTOTAL Funkcia
Funkcia SUBTOTAL môže vykonávať rôzne výpočty na celom rade údajov (počet, súčet, priemer atď.). Najdôležitejšie je, že ho možno použiť na výpočet iba na viditeľných (filtrovaných) riadkoch. V tomto prípade budeme používať funkciu na počítanie (COUNTA) viditeľných riadkov nastavením argumentu SUBTOTAL function_num na 3 (Úplný zoznam možných funkcií nájdete tu.)
= SÚČET (3, $ D $ 2: $ D $ 14)
Všimnite si, ako sa výsledky menia, pretože ručne filtrujeme riadky.
SUBTOTAL IF
Na vytvorenie „Medzisúčtu If“ použijeme vo vzorci poľa kombináciu SUMPRODUCT, SUBTOTAL, OFFSET, ROW a MIN. Použitím tejto kombinácie môžeme v zásade vytvoriť generickú funkciu „SUBTOTAL IF“. Poďme sa pozrieť na príklad.
Máme zoznam členov a ich stav účasti na každom podujatí:
Predpokladáme, že sme požiadaní, aby sme dynamicky spočítali počet členov, ktorí sa zúčastnili udalosti, pretože zoznam manuálne filtrujeme takto:
Aby sme to dosiahli, môžeme použiť tento vzorec:
= SUMPRODUCT ((=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))))
= SUMPRODUCT ((D2: D14 = "Attended")*(SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0)))))
Pri použití Excelu 2022 a staršieho musíte zadať vzorec poľa stlačením CTRL + SHIFT + ENTER povedať Excelu, že zadávate vzorec poľa. Budete vedieť, že vzorec bol zadaný správne ako vzorec poľa, keď sa okolo vzorca zobrazia zložené zátvorky (pozri obrázok vyššie).
Ako funguje vzorec?
Vzorec funguje tak, že vynásobí dve polia vo vnútri SUMPRODUCT, kde prvé pole sa zaoberá našimi kritériami a druhé pole filtruje iba do viditeľných riadkov:
= SUMPRODUCT (*)
Pole kritérií
Pole kritérií vyhodnotí každý riadok v našom rozsahu hodnôt (v tomto prípade stav „Attended“) a vygeneruje pole takto:
=(=)
= (D2: D14 = "Zúčastnený")
Výkon:
{PRAVDA; NEPRAVDA; NEPRAVDA; PRAVDA; NEPRAVDA; TURE; TURE; TURE; NEPRAVDA; NEPRAVDA; PRAVDA; NEPRAVDA; TRUE}
Všimnite si toho, že výstup v prvom poli v našom vzorci ignoruje, či je riadok viditeľný alebo nie, kde nám pomôže druhé pole.
Pole viditeľnosti
Použitím SUBTOTAL na vylúčenie neviditeľných riadkov v našom rozsahu môžeme vygenerovať naše pole viditeľnosti. SUBTOTAL však vráti jednu hodnotu, zatiaľ čo SUMPRODUCT očakáva množstvo hodnôt. Aby sme to vyriešili, používame OFFSET na prechod po jednom riadku. Táto technika vyžaduje podávanie OFFSETU poľa, ktoré obsahuje vždy jedno číslo. Druhé pole vyzerá takto:
= SÚČET (3, OFFSET (, ROW ()-MIN (ROW ()), 0))
= SÚČET
Výkon:
{1;1;0;0;1;1}
Spojenie dvoch dohromady:
= SUMPRODUCT ({TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4
SUBTOTAL IF s viacerými kritériami
Ak chcete pridať viacero kritérií, v rámci SUMPRODUKTU jednoducho zoskupte viac ďalších kritérií takto:
= SUMPRODUCT ((=)*(=)*(SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))))
= SUMPRODUCT ((E2: E14 = "Attended")*(B2: B14 = 2019)*(SUBTOTAL (3, OFFSET (E2, ROW (E2: E14) -MIN (ROW (E2: E14)), 0)) )))
SUBTOTAL IF v Tabuľkách Google
Funkcia SUBTOTAL IF funguje v Tabuľkách Google úplne rovnako ako v programe Excel:
