Obecný vzorec
=IF(A2=A1,"",SUMIF(A:A,A2,B:B))
souhrn
Chcete-li mezisoučet dat podle skupiny nebo štítku přímo v tabulce, můžete použít vzorec založený na funkci SUMIF.
V zobrazeném příkladu je vzorec v D5:
=IF(B5=B4,"",SUMIF(B:B,B5,C:C))
Poznámka: Abyste dosáhli rozumných výsledků, je nutné data seřadit podle seskupovacího sloupce.
Vysvětlení
Rámec tohoto vzorce je založen na IF, který testuje každou hodnotu ve sloupci B, zda je stejná jako hodnota v „buňce výše“. Když se hodnoty shodují, vzorec nevrátí nic (""). Pokud jsou hodnoty odlišné, volá funkce IF SUMIF:
SUMIF(B:B,B5,C:C)
V každém řádku, kde je SUMIF spuštěn funkcí IF, SUMIF vypočítá součet všech odpovídajících řádků ve sloupci C (C: C). Kritéria používaná nástrojem SUMIF je aktuální hodnota řádku ve sloupci B (B5), testována proti všem sloupcům B (B: B).
Úplné odkazy na sloupce, jako je tento, jsou skvělé a elegantní, protože si nemusíte dělat starosti s tím, kde data začínají a končí, ale musíte si být jisti, že nad nebo pod tabulkou nejsou další data, která by SUMIF mohla zachytit.
Výkon
Může to vypadat, že použití odkazu na celý sloupec je špatný nápad, protože aktuální verze aplikace Excel obsahují více než 1 m řádků. Testování však ukázalo, že Excel s tímto typem vzorce vyhodnotí pouze data v „použitém rozsahu“ (A1 na adresu průsečíku posledního použitého sloupce a posledního použitého řádku).
Charles Williams ve Fast Excel má na toto téma dobrý článek s úplnou sadou výsledků načasování.
Proč kontingenční tabulky?
Tento příklad má ukázat, jak fungují úplné odkazy na sloupce a jak je lze použít přímo v datové tabulce. Kontingenční tabulky zůstávají vynikajícím způsobem, jak seskupovat a sumarizovat data.
