SUBTOTAL IF Formula - Excel & Google Sheets

Stáhněte si ukázkový sešit

Stáhněte si ukázkový sešit

Tento tutoriál předvede, jak vypočítat „mezisoučet if“, počítající pouze viditelné řádky s kritérii.

SUBTOTAL Funkce

Funkce SUBTOTAL může provádět různé výpočty na řadě dat (počet, součet, průměr atd.). A co je nejdůležitější, lze jej použít k výpočtu pouze na viditelných (filtrovaných) řádcích. V tomto příkladu budeme používat funkci k počítání (COUNTA) viditelných řádků nastavením argumentu SUBTOTAL function_num na 3 (Úplný seznam možných funkcí najdete zde.)

= SUBTOTAL (3, $ D $ 2: $ D $ 14)

Všimněte si, jak se výsledky mění, když ručně filtrujeme řádky.

SUBTOTAL IF

K vytvoření „Mezisoučtu If“ použijeme ve vzorci pole kombinaci SUMPRODUCT, SUBTOTAL, OFFSET, ROW a MIN. Pomocí této kombinace můžeme v zásadě vytvořit obecnou funkci „SUBTOTAL IF“. Pojďme se podívat na příklad.

Pro každou akci máme seznam členů a jejich stav docházky:

Předpokládáme, že jsme požádáni, abychom spočítali počet členů, kteří se zúčastnili události dynamicky, protože ručně filtrujeme seznam takto:

K dosažení tohoto cíle můžeme použít 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)))))

Pokud používáte Excel 2022 a starší, musíte zadat vzorec pole stisknutím CTRL + SHIFT + ENTER říct Excelu, že zadáváte vzorec pole. Budete vědět, že vzorec byl zadán správně jako maticový vzorec, když se kolem vzorce objeví složené závorky (viz obrázek výše).

Jak funguje vzorec?

Vzorec funguje vynásobením dvou polí uvnitř SUMPRODUCT, kde první pole pracuje s našimi kritérii a druhé pole filtruje pouze viditelné řádky:

= SUMPRODUCT (*)

Pole kritérií

Pole kritérií vyhodnotí každý řádek v našem rozsahu hodnot (v tomto případě stav „Attended“) a vygeneruje pole takto:

=(=)
= (D2: D14 = "Zúčastněn")

Výstup:

{SKUTEČNÝ; NEPRAVDIVÉ; NEPRAVDIVÉ; SKUTEČNÝ; NEPRAVDIVÉ; TURE; TURE; TURE; NEPRAVDIVÉ; NEPRAVDIVÉ; SKUTEČNÝ; NEPRAVDIVÉ; SKUTEČNÝ}

Všimněte si toho, že výstup v prvním poli v našem vzorci ignoruje, zda je řádek viditelný nebo ne, což je místo, kde nám pomáhá naše druhé pole.

Pole viditelnosti

Pomocí SUBTOTAL k vyloučení neviditelných řádků v našem rozsahu můžeme vygenerovat naše pole viditelnosti. SUBTOTAL sám však vrátí jednu hodnotu, zatímco SUMPRODUCT očekává řadu hodnot. Chcete -li to obejít, používáme OFFSET k předání jednoho řádku najednou. Tato technika vyžaduje krmení OFFSET pole, které obsahuje vždy jedno číslo. Druhé pole vypadá takto:

= SUBTOTAL (3, OFFSET (, ROW ()-MIN (ROW ()), 0))
= SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))

Výstup:

{1;1;0;0;1;1}

Spojení dvou dohromady:

= SUMPRODUCT ({TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4

SUBTOTAL IF with Multiple Criteria

Chcete -li přidat více kritérií, jednoduše v rámci SUMPRODUKTU spárujte více další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 Tabulkách Google

Funkce SUBTOTAL IF funguje v Tabulkách Google úplně stejně jako v Excelu:

wave wave wave wave wave