Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak vypočítat mezisoučty podle skupin pomocí funkce SUMIFS v Excelu a Google Sheets.
Mezisoučet tabulka podle kategorie nebo skupiny
Nejprve si předvedeme, jak vytvořit dynamickou souhrnnou tabulku mezisoučtu z rozsahu dat v Excelu 365 nebo dále v Tabulkách Google.
K automatickému mezisoučtu používáme funkci UNIQUE a funkci SUMIFS Počet produktů podle Skupina produktů:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
K vytvoření této tabulky mezisoučtů používáme k součtu standardní aplikaci funkce SUMIFS Počet produktů které se shodují s každým Skupina produktů. Než to však bude možné, musíme vytvořit seznam jedinečných Skupiny produktů. Uživatelé aplikací Microsoft Excel 365 a Tabulky Google mají přístup k funkci UNIQUE, která umožňuje vytvořit dynamický seznam jedinečných hodnot z oblasti buněk. V tomto příkladu přidáme do buňky E3 následující vzorec:
| 1 | = JEDINEČNÉ (B3: B11) |
Po zadání tohoto vzorce se pod buňkou automaticky vytvoří seznam, který zobrazí všechny jedinečné hodnoty nalezené v souboru Produktová skupina datový rozsah. V tomto případě se seznam rozšířil na E3: E5, aby ukázal všechny 3 jedinečné Produktová skupina hodnoty.
Toto je funkce dynamického pole, kde není nutné definovat velikost seznamu výsledků, a automaticky se zmenší a zvětší se změnou hodnot vstupních dat.
Všimněte si, že v aplikaci Excel 365 funkce UNIQUE nerozlišuje velká a malá písmena, ale v Tabulkách Google ano. Zvažte seznam {„A“; "A"; „B“; "C"}. Výstup funkce UNIQUE závisí na programu:
- {"A"; „B“; „C“} v aplikaci Excel 365
- {"A"; "A"; „B“; „C“} v Tabulkách Google
Pokud používáte verzi aplikace Excel před aplikací Excel 365, budete muset zvolit jiný přístup. O tom pojednává následující část.
Mezisoučet tabulka podle kategorie nebo skupiny - před Excel 365
Pokud používáte verzi Excelu před Excel 365, funkce UNIQUE není k dispozici k použití. Chcete -li replikovat stejné chování, můžete kombinovat funkci INDEX a funkci MATCH s funkcí COUNTIF a vytvořit vzorec pole pro vytvoření seznamu jedinečných hodnot z řady buněk:
| 1 | {= INDEX ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Aby tento vzorec fungoval, je třeba pečlivě napsat pevné odkazy na buňky, přičemž funkce COUNTIF odkazuje na rozsah $ E $ 2: E2, což je rozsah začínající od E2 do buňky nad buňkou obsahující vzorec.
Vzorec je také nutné zadat jako vzorec pole stisknutím CTRL + SHIFT + ENTER po jeho napsání. Tento vzorec je a 1-buňkový maticový vzorec, které pak lze zkopírovat do buněk E4, E5 atd. Nezadávejte to jako maticový vzorec pro celý rozsah E3: E5 v jedné akci.
Stejným způsobem jako v předchozím příkladu se pak funkce SUMIFS použije k mezisoučtu Počet produktů podle Produktová skupina:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Součet podle kategorie nebo skupiny - mezisoučty v datových tabulkách
Jako alternativu k výše uvedené metodě souhrnné tabulky můžeme přidat mezisoučty přímo do datové tabulky. Ukážeme to pomocí IF funkcí spolu s funkcí SUMIFS pro přidání a Mezisoučet podle skupiny do původní datové tabulky.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Tento příklad používá funkci SUMIFS vnořenou do funkce IF. Rozdělme si příklad na kroky:
K přidání souhrnné statistiky přímo do datové tabulky můžeme použít funkci SUMIFS. Začneme součtem Počet produktů které odpovídají příslušnému Skupina produktů:
| 1 | = SUMIFS (C3: C11, B3: B11, B3) |
Tento vzorec vytváří mezisoučet hodnotu pro každý řádek dat. Chcete -li zobrazit mezisoučty pouze v prvním datovém řádku každého z nich Produktová skupina, používáme funkci IF. Data již musí být seřazena podle Produktová skupina aby se zajistilo správné zobrazení mezisoučtů.
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Funkce IF porovnává jednotlivé datové řádky Skupina produktů hodnotu s datovým řádkem nad ním, a pokud mají stejnou hodnotu, vypíše prázdnou buňku („“).
Pokud Skupina produktů hodnoty jsou různé, zobrazí se součet. Tímto způsobem každý Skupina produktů součet se zobrazí pouze jednou (na řádku jeho první instance).
Třídění datových sad podle skupiny
Pokud data již nejsou seřazena, můžeme pro mezisoučet použít stále stejný vzorec.
Výše uvedená datová sada není řazena podle Skupina produktů, takže Mezisoučet podle skupiny sloupec zobrazuje každý mezisoučet více než jednou. Abychom data dostali do požadovaného formátu, můžeme vybrat datovou tabulku a kliknout na „Seřadit od A do Z“.
Zamykání odkazů na buňky
Aby byly naše vzorce čitelnější, ukázali jsme některé vzorce bez uzamčených odkazů na buňky:
| 1 | = IF (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Tyto vzorce ale nebudou správně fungovat, když je zkopírujete a vložíte jinam do souboru. Místo toho byste měli použít uzamčené odkazy na buňky takto:
| 1 | = IF (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
Další informace najdete v našem článku o zamykání odkazů na buňky.
Použití kontingenčních tabulek k zobrazení mezisoučtů
Aby bylo možné odstranit požadavek na předběžné řazení dat podle Skupina produktů, místo toho můžeme použít sílu kontingenčních tabulek ke shrnutí dat. Kontingenční tabulky automaticky vypočítávají mezisoučty a zobrazují součty a mezisoučty v několika různých formátech.
Součet podle kategorie nebo skupiny v Tabulkách Google
Tyto vzorce fungují v Tabulkách Google stejně jako v Excelu. Funkce UNIQUE však v Tabulkách Google rozlišuje velká a malá písmena.
