V dnešním videu máte možnost ověřit si vaše znalosti v Excelu. Všechny příklady jsou založené na sčítání, nicméně při různých podmínkách a jejich kombinací. Dokážete všechny tyto příklady vyřešit v Excelu? Není totiž sčítání jako sčítání… A u několika z těchto příkladů si rovněž ukážeme, proč některé funkce nejsou ideální pro jejich řešení a na co si musíte dát pozor, abyste omylem nedošli ke špatným výsledkům.
Excelový soubor ke stažení:
V dnešním příkladu máme zdrojovou tabulku, ve které máme kromě produktů uvedené i kategorie a typ produktu, výrobce, barvu a tržbu. Naším úkolem je odpovědět na následující otázky.
- Jaké jsou celkové tržby za dámské produkty?
- Jaké jsou celkové tržby za oděvy od společnosti Adidas?
- Jaké jsou celkové tržby za oděvy a obuv?
- Jaké jsou celkové tržby oděvů a produktů Adidas?
- Jaké jsou celkové tržby oděvů od Adidas a obuvi?
- Jaké jsou celkové tržby za oděvy od společnosti Adidas modré barvy a obuv černé barvy?
- Jaké jsou celkové tržby za doplňky nebo produkty od společnosti Nike a Adidas?
Pokud si chcete příklady zkusit vyřešit sami, tak pauzněte video, stáhněte si zdrojový soubor z webu Akademie Excelu, zkuste si příklady vyřešit a následně si vaše řešení zkontrolujte s řešením v tomto videu. Dejte mi vědět v komentáři pod videem, na kolik otázek jste odpověděli, jakou funkci jste k řešení použili a co vám naopak činilo potíže.
Tak jdeme na to.
Jaké jsou celkové tržby za dámské produkty?
I přesto, že se tato otázka může mnohým pokročilým uživatelům Excelu zdát jako triviální, tak stále ještě existuje spousta lidí, kteří by příklad řešili tak, že by jednoduše sčítali tržby podle řádků, na kterých jsou dámské produkty. Tento způsob je, kromě toho, že je velmi pomalý, má mnoho nevýhod. Můžete přehlédnout řádek, uťuknout se, nemluvě o tom, že se změní zdrojová data a váš výpočet zůstane nezměněný. Někdo by možná použil filtr nad tabulkou, sečetl tržby a výsledek jednoduše do buňky opsal. Ani to není správné řešení.
Řešením je nesčítat jednotlivé buňky, ani nefiltrovat tabulku, ale použít funkci SUMIFS. Ve funkci SUMIFS se nejprve označuje sloupec, který chceme sčítat, což je v tomto případě sloupec Tržba ze zdrojové tabulky. Následuje první oblast kritérií, což je sloupec s Typem produktu, jelikož chceme sčítat podle dámských produktů. A jako kritérium označíme vybraný typ produktu. Po potvrzení funkce SUMIFS vrátí správný výsledek na základě součtu podle jedné podmínky.
Jaké jsou celkové tržby za oděvy od společnosti Adidas?
I zde se jedná o jednoduchý příklad sčítání s vícenásobnou podmínkou. Nejjednodušším řešením bude opět použít funkci SUMIFS. Ve funkci SUMIFS opět nejprve označíme sloupec, který chceme sčítat, tedy sloupec Tržba. Následuje první oblast kritérií, což je sloupec Kategorie ze zdrojové tabulky, jelikož chceme sčítat na základě oděvů. A jako kritérium označíme vybranou kategorii produktů. Máme ale ještě druhou podmínku, a to je, že oděvy musí být od společnosti Adidas. Takže vyplníme i druhou oblast kritérií a označíme sloupec Výrobce. A jako druhé kritérium označíme vybraného výrobce. Po potvrzení se vrátí součet tržeb pro zdanou kombinaci podmínek.
Jaké jsou celkové tržby za oděvy a obuv?
V tomto příkladu máme sečíst tržby pro oděvy a obuv dohromady. V tomto případě nemůžeme použít funkci SUMIFS v klasickém vyjádření, jelikož musíme sčítat na základě logické podmínky NEBO. Kritéria, podle kterých chceme sčítat, jsou totiž ve stejném sloupci. Kdybychom použili funkci SUMIFS a označili v ní obě kategorie produktů, tak se vrátí samozřejmě nula, jelikož na žádném řádku nemáme obě kategorie najednou a funkce SUMIFS pracuje s logickou podmínkou A.
Musíme tedy použít dvě funkce SUMIFS, které sečteme. V první funkci SUMIFS nejprve sečteme tržby pro oděvy. A ve druhé funkci sečteme tržby pro obuv. Tím, že tyto dvě funkce SUMIFS sečteme, tak se vrátí správný výsledek, i přesto, že sčítáme podle dvou podmínek na stejném sloupci.
A nebo můžete použít funkci DSUMA. DSUMA je databázovou funkcí, která si poradí i s logickým vyjádřením NEBO na stejném sloupci. Základem je správně vypsat kritéria do pomocné tabulky. Když chceme pracovat s logickým vyjádřením NEBO, tak musí být kritéria vypsaná pod sebou. Ve funkci DSUMA nejprve označujeme databázi, což je celá zdrojová tabulka včetně záhlaví. Následuje sloupec, tedy označení názvu sloupce, ze kterého chceme sčítat. V našem případě se jedná o název sloupce Tržba a buďto můžeme toto slovo v uvozovkách do druhého parametru napsat, anebo ho můžeme označit. A jako poslední se označují kritéria, tedy naše pomocná tabulka, opět včetně záhlaví. Po potvrzení funkce vrátí správný výsledek.
Jaké jsou celkové tržby oděvů a produktů Adidas?
Zde máme další příklad na logické vyjádření NEBO, ale tentokrát na dvou různých sloupcích.
Mohlo by vás napadnout použít funkci SUMIFS, jelikož se jedná o součet na dvou sloupcích. Když funkci SUMIFS potvrdíme, tak se na rozdíl od součtu stejném sloupci vrátí součet. Zdánlivě tedy funkce SUMIFS funguje. Myslíte si ale, že je tento součet správně? Není…Proč není? Protože ve funkci SUMIFS, kde sčítáme produkty Adidas jsou i oděvy, které se započítali u první funkce SUMIFS. To znamená, že v tomto součtu máme některé tržby zahrnuté dvakrát. A to je chyba.
Pokud byste příklad chtěli vyřešit pomocí funkcí SUMIFS, tak byste neprve v první funkci SUMIFS spočítali celkové tržby pro kategorii. A ve druhé funkci SUMIFS byste spočítali součet tržeb pro Adidas, ale bez oděvů. Tyto dvě funkce SUMIFS byste nakonec sečetli.
A nebo opět můžete použít funkci DSUMA, kde nejprve označíte zdrojovo tabulku včetně záhlaví. Následuje opět název sloupce, který chcete sčítat, což je Tržba a jako oslední označíte tabulku s kritérii, opět včetně záhlaví.
Jaké jsou celkové tržby oděvů od Adidas a obuvi?
A co když logická vyjádření A a NEBO zkombinujeme? V tomto případě je nejjednoduší netrápit se s funkcemi SUMIFS, ale rovnou použít funkci DSUMA.
Jaké jsou celkové tržby za oděvy od společnosti Adidas modré barvy a obuv černé barvy?
I v tomto případě můžeme použít dvě funkce SUMIFS, které sečteme, abychom došli ke správnému výsledku.
A nebo opět můžeme použít funkci DSUMA.
Jaké jsou celkové tržby za doplňky nebo produkty od společnosti Nike a Adidas?
V posledním příkladu narazíme na podobný problém jako u čtvrté otázky. Nejsnazší je použít opět databázovou funkci DSUMA, která si s příkladem hravě poradí.
Pokud bychom příklad chtěli řešit pomocí SUMIFS, tak bychom to nejsnáze udělali pomocí pomocných výpočtů. Nejprve bychom spočítali jednotlivé součty pro vybraná kritéria. Poté bychom ale museli ještě spočítat průnik všech těchto kritérií dohromady, jelikož to je část tržeb, která by se nám jinak duplikovala. A nakonec bychom součty SUMIFS pro jednotlivá krtéria sečetli, ale odečetli bychom poslední výpočet průniku, jelikož to jsou tržby, které bychom jinak započítali dvakrát.
