Všeobecný vzorec
=IF(A2=A1,"",SUMIF(A:A,A2,B:B))
Zhrnutie
Na medzisúčty údajov podľa skupín alebo štítkov priamo v tabuľke môžete použiť vzorec založený na funkcii SUMIF.
V zobrazenom príklade je vzorec v D5:
=IF(B5=B4,"",SUMIF(B:B,B5,C:C))
Poznámka: Aby ste dosiahli rozumné výsledky, musíte údaje zoradiť podľa stĺpca zoskupenia.
Vysvetlenie
Rámec tohto vzorca je založený na IF, ktorý testuje každú hodnotu v stĺpci B, aby zistil, či je rovnaká ako hodnota v „bunke vyššie“. Keď sa hodnoty zhodujú, vzorec nevráti nič („“). Ak sú hodnoty odlišné, funkcia IF volá SUMIF:
SUMIF(B:B,B5,C:C)
V každom riadku, kde SUMIF spúšťa IF, SUMIF počíta súčet všetkých zhodných riadkov v stĺpci C (C: C). Kritériá, ktoré používa SUMIF, je aktuálna hodnota riadku stĺpca B (B5), testovaná proti všetkým stĺpcom B (B: B).
Celé odkazy na tento stĺpec sú super a elegantné, pretože sa nemusíte obávať toho, kde dáta začínajú a končia, ale musíte si byť istí, že nad alebo pod tabuľkou nie sú žiadne ďalšie údaje, ktoré by SUMIF mohol zachytiť.
Výkon
Môže sa zdať, že použitie odkazu na celý stĺpec je zlý nápad, pretože súčasné verzie programu Excel obsahujú viac ako 1 m riadkov. Testovanie však ukázalo, že program Excel s týmto typom vzorca hodnotí iba údaje v „použitom rozsahu“ (A1 na adresu priesečníka naposledy použitého stĺpca a naposledy použitého riadku).
Charles Williams, ktorý pracoval vo Fast Excel, má na túto tému dobrý článok s kompletnou sadou výsledkov načasovania.
Prečo kontingenčné tabuľky?
Tento príklad je určený na to, aby ukázal, ako fungujú odkazy na celé stĺpce a ako ich možno použiť priamo v tabuľke údajov. Kontingenčné tabuľky zostávajú vynikajúcim spôsobom zoskupovania a sumarizácie údajov.
