Stiahnite si ukážkový zošit
Tento tutoriál ukáže, ako vypočítať medzisúčty podľa skupín pomocou funkcie SUMIFS v Exceli a Tabuľkách Google.
Medzisúčet tabuľky podľa kategórie alebo skupiny
Najprv si ukážeme, ako vytvoriť dynamickú medzisúčetnú súhrnnú tabuľku z rozsahu údajov v programe Excel 365 alebo novšom.
Na automatické medzisúčet používame funkciu UNIQUE a funkciu SUMIFS Počet produktov od Skupina produktov:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Na vytvorenie tejto medzisúčetovej tabuľky používame na sčítanie štandardnú aplikáciu funkcie SUMIFS Počet produktov ktoré sa zhodujú s každým Produktová skupina. Kým to však bude možné, musíme vytvoriť zoznam jedinečných Skupiny produktov. Používatelia programov Microsoft Excel 365 a Tabuľky Google majú prístup k funkcii UNIQUE na vytvorenie dynamického zoznamu jedinečných hodnôt z rozsahu buniek. V tomto prípade pridáme do bunky E3 nasledujúci vzorec:
| 1 | = JEDINEČNÉ (B3: B11) |
Po zadaní tohto vzorca sa pod bunkou automaticky vytvorí zoznam, v ktorom sa zobrazia všetky jedinečné hodnoty nachádzajúce sa v súbore Produktová skupina dátový rozsah. V tomto prípade sa zoznam rozšíril na E3: E5 a ukázal všetky 3 jedinečné Produktová skupina hodnoty.
Jedná sa o funkciu dynamického poľa, kde nie je potrebné definovať veľkosť zoznamu výsledkov a automaticky sa zmenší a bude rásť so zmenou hodnôt vstupných údajov.
Všimnite si toho, že v aplikácii Excel 365 funkcia UNIQUE nerozlišuje malé a veľké písmená, ale v Tabuľkách Google áno. Zvážte zoznam {„A“; „A“; „B“; „C“}. UNIKÁTNY výstup funkcie závisí od programu:
- {„A“; „B“; „C“} v programe Excel 365
- {„A“; „A“; „B“; „C“} v Tabuľkách Google
Ak používate verziu programu Excel pred Excelom 365, budete musieť zvoliť iný prístup. Toto je prediskutované v ďalšej časti.
Tabuľka medzisúčtov podľa kategórie alebo skupiny - Pre Excel 365
Ak používate verziu programu Excel pred Excel 365, funkciu UNIQUE nie je možné použiť. Na replikáciu rovnakého správania môžete skombinovať funkciu INDEX a funkciu MATCH s funkciou COUNTIF a vytvoriť vzorec poľa na vytvorenie zoznamu jedinečných hodnôt z radu buniek:
| 1 | {= INDEX ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Aby tento vzorec fungoval, je potrebné starostlivo zapísať odkazy na pevné bunky, pričom funkcia COUNTIF odkazuje na rozsah $ E $ 2: E2, čo je rozsah začínajúci od E2 do bunky nad bunkou obsahujúcou vzorec.
Vzorec je tiež potrebné zadať ako vzorec poľa stlačením klávesov CTRL + SHIFT + ENTER po jeho napísaní. Tento vzorec je a 1-bunkový vzorec poľa, ktoré je potom možné skopírovať do buniek E4, E5 atď. Nezadávajte to ako vzorec poľa pre celý rozsah E3: E5 v jednej akcii.
Rovnakým spôsobom ako v predchádzajúcom príklade sa potom funkcia SUMIFS použije na medzisúčet Počet produktov od Produktová skupina:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Suma podľa kategórie alebo skupiny - medzisúčty v údajových tabuľkách
Ako alternatívu k vyššie uvedenej metóde súhrnnej tabuľky môžeme pridať medzisúčty priamo do dátovej tabuľky. Ukážeme to pomocou IF funkcií spolu s funkciou SUMIFS na pridanie a Medzisúčet podľa skupín do pôvodnej dátovej tabuľky.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Tento príklad používa funkciu SUMIFS vnorenú do funkcie IF. Rozoberme si príklad na kroky:
Na pridanie súhrnnej štatistiky priamo do dátovej tabuľky môžeme použiť funkciu SUMIFS. Začíname sčítaním Počet produktov ktoré zodpovedajú príslušnému Produktová skupina:
| 1 | = SUMIFS (C3: C11, B3: B11, B3) |
Tento vzorec vytvára medzisúčet pre každý riadok údajov. Zobraziť medzisúčty iba v prvom riadku údajov každého z nich Produktová skupina, používame funkciu IF. Údaje už musia byť zoradené podľa Produktová skupina aby sa zaistilo správne zobrazenie medzisúčtov.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Funkcia IF porovnáva jednotlivé riadky údajov Produktová skupina hodnotu s riadkom údajov nad ním, a ak majú rovnakú hodnotu, vypíše prázdnu bunku („“).
Ak Produktová skupina hodnoty sú rôzne, zobrazí sa súčet. Takto každý Produktová skupina súčet sa zobrazí iba raz (v riadku jeho prvej inštancie).
Zoradenie množín údajov podľa skupín
Ak údaje už nie sú zoradené, pre medzisúčet môžeme stále použiť rovnaký vzorec.
Vyššie uvedená množina údajov nie je zoradená podľa Produktová skupina, takže Medzisúčet podľa skupín stĺpci zobrazuje každý medzisúčet viac ako raz. Aby sme dostali údaje do požadovaného formátu, môžeme vybrať tabuľku údajov a kliknúť na „Zoradiť od A do Z“.
Zamknutie odkazov na bunky
Aby sme uľahčili čítanie našich vzorcov, ukázali sme niektoré vzorce bez odkazov na uzamknuté bunky:
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Tieto vzorce však nebudú fungovať správne, keď ich skopírujete a vložíte na iné miesto v súbore. Namiesto toho by ste mali použiť uzamknuté odkazy na bunky takto:
| 1 | = IF (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Ak sa chcete dozvedieť viac, prečítajte si náš článok o uzamknutí odkazov na bunky.
Použitie kontingenčných tabuliek na zobrazenie medzisúčtov
S cieľom odstrániť požiadavku na predbežné zoradenie údajov podľa Produktová skupina, namiesto toho môžeme na zhrnutie údajov využiť silu kontingenčných tabuliek. Kontingenčné tabuľky automaticky vypočítavajú medzisúčty a zobrazujú súčty a medzisúčty v niekoľkých rôznych formátoch.
Súčet podľa kategórie alebo skupiny v Tabuľkách Google
Tieto vzorce fungujú v Tabuľkách Google rovnako ako v programe Excel. V Tabuľkách Google však funkcia UNIQUE rozlišuje veľké a malé písmená.
