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:
