Funkce SOUČIN.SKALÁRNÍ je všestranná funkce, která má dalekosáhlé využití. Funkce SOUČIN.SKALÁRNÍ je o hodně flexibilnější než si uživatelé Excelu většinou myslí. Potřebujete při sčítání použít více podmínek, ale nejste předplatiteli služby Office 365, takže nemůžete použít novou funkci SUMIFS? Nezoufejte, protože ji nepotřebujete. Dnešní video je nabyté triky, jak použít ve funkci SUMIF více podmínek a zároveň, jak to celé elegantně obejít mnohem jednodušeji s funkcí SOUČIN.SKALÁRNÍ.
Excelový soubor ke stažení:
Funkce SOUČIN.SKALÁRNÍ dva praktické příklady
Příklad č. I
V prvním příkladu máme velmi malou tabulku dat, ve které máme uvedené datumy prodeje, produkt, který se v daný den prodal a počet kusů produktu, který se ten den prodal. Na začátek chceme zjistit, kolik produktu A se prodalo v roce 2021. Na první pohled to vypadá jako příklad, který by šel vyřešit pomocí funkce SUMIF, jelikož chceme sčítat na základě podmínky. Ale…potřebujeme použít dvě podmínky, což funkce SUMIF neumí. Nebo umí?
Příklad se pokusíme vyřešit třemi různými způsoby.
Řešení pomocí funkce SUMIFS
Nejprve si ukážeme nejlehčí verzi, což je použití funkce SUMIFS, pro ty z vás, kteří jsou předplatiteli služby Office 365. Písmeno S ve funkci SUMIFS znamená, že tato funkce umí pracovat s více podmínkami. První podmínkou je, že se produkt ve sloupci produkt (B12:B19) musí rovnat produktu A (B3). Druhou podmínkou je, že se rok v datumu (A12:A19) musí shodovat s rokem uvedeným v buňce B2. Jak si ale můžeme všimnout, tak zatímco v buňce B2 máme uvedený pouze rok, tak v tabulce ve sloupci A12:A19 máme uvedené celé datum. Určitě vás na tomto místě napadne použít k vytáhnutí roku z datumu datumovou funkci ROK. Problémem ovšem je, že ve funkci SUMIFS ani SUMIF nemůžeme funkci ROK přímo použít. Nevěříte? Zkuste si to.
Jediné, co tedy musíme vyřešit je to, že ve funkci SUMIFS nemůžeme použít funkci ROK. Musíme si tedy pomoci nejprve vytvořením pomocného sloupce, který nazveme třeba rok, a ve kterém za pomocí funkce ROK vytáhneme rok z datumu v tabulce. Když máme pomocný sloupec připravený, tak je použití funkce SUMIFS jednoduché. Do buňky B6 napíšeme funkci SUMIFS a jako první parametr je oblast součtu, to je sloupec počet (C12:C19). Hodnoty nemusíme fixovat, jelikož se nechystáme vzorec nikam stahovat. Dalším polem je oblast kritérií, to je sloupec s produkty (B12:B19). Následuje první kritérium, což je Produkt A (B3), další oblast kritérií, tentokrát se ovšem musíme odkázat na pomocný sloupec s roky (E12:E19) a následuje poslední kritérium, čímž je vybraný rok (B2).
No jo, ale ne všichni jsou předplatiteli služby Office 365. Tak to zkusíme obejít a použít k tomu výpočtu funkci SUMIF, tedy funkci, kterou máme v Excelu všichni.
Řešení pomocí funkce SUMIF
U funkce SUMIF máme o jeden problém více než u funkce SUMIFS. První problém je totožný, a to je to, že nemůžeme použít funkci ROK přímo ve funkci SUMIF. Druhým problémem je, že musíme nějak obejít to, že funkce SUMIF neumí v základu počítat s více podmínkami.
Musíme tedy opět použít pomocného sloupce. Tentokrát ale trochu jinak.
Stejně jako ve funkci SVYHLEDAT jde několik podmínek nebo kritérií obejít pomocným sloupcem, kde z několika kritérií uděláme jedno. Vytvoříme tedy pomocný sloupec, a v něm spojíme rok a produkt na každém řádku. Začneme s rokem, použijeme funkci ROK, pomocí které vytáhneme rok z datumu, ampersand a produkt. V pomocném sloupci vznikne spojení roku a produktu. Tím, že jsme kritéria spojili do jednoho sloupce jsme vlastně vytvořili jedno velmi specifické kritérium. Teď už stačí jen použít funkci SUMIF. Do buňky B7 napíšeme funkci SUMIF, kde prvním parametrem je oblast, což je pomocný sloupec, který jsme vytvořili (F12:F19). Druhý parametr je kritérium, zde musíme opět spojit rok s produktem dohromady (B2&B3), a to za pomocí ampersandu, posledním parametrem je oblast součtu, tedy sloupec počet (C12:C19).
Řešení pomocí funkce SOUČIN.SKALÁRNÍ
A nebo to celé do třetice můžeme vyřešit jednoduše, elegantně s jednou funkcí a bez pomocných mezi výpočtů. A to za pomocí funkce SOUČIN.SKALÁRNÍ.
Do buňky B8, kde chceme mít uvedený součet produktů napíšeme funkci SOUČIN.SKALÁRNÍ, otevřeme závorku a jelikož plánujeme ve funkci použít logickou podmínku, tak otevřeme rovnou ještě jednu závorku. Naše logická podmínka je, zda se rok v této oblasti (A12:A19) rovná vybranému roku 2021 (B2). Na rozdíl od předchozích funkcí, ve funkci SOUČIN.SKALÁRNÍ můžeme použít funkci ROK. Do funkce SOUČIN.SKALÁRNÍ tedy napíšeme funkci ROK, otevřeme závorku, označíme oblast s datumy (A12:A19), ukončíme závorku a rovná se vybraný rok (B2), ukončíme závorku. Z předešlého videa o funkci SOUČIN.SKALÁRNÍ víme, že logickou hodnotu ve funkci musíme převést na číslo, což můžeme udělat buď dvojitým negativem nebo vynásobením číslem 1. Vynásobíme tedy celou podmínku číslem jedna. Napíšeme středník a uplatníme druhou podmínku, znovu se jedná o logickou podmínku, která musí být v závorkách, otevřeme závorku a označíme sloupec s produkty (B12:B19), konec závorky, rovná se a označíme buňku s produktem A (B3). Opět musíme logické hodnoty PRAVDA a NEPRAVDA převést na čísla, takže celou druhou podmínku zabalíme do závorek a vynásobíme jedničkou. Zbývá označit jen sloupec s počtem kusů (C12:C19). Uzavřeme závorku a potvrdíme. Díky jedné funkci se vám vrátil správný výsledek a to bez použití několika pomocných sloupců.
Příklad č. II.
Funkce SOUČIN.SKALÁRNÍ se vám bude hodit i tehdy, pokud potřebujete použít logickou podmínku NEBO. Funkce SUMIFS a COUNTIFS totiž pracují pouze s logickou podmínkou A. Všechny podmínky, které do těchto funkcí zapíšete musí platit zároveň. Pokud chcete použít logickou podmínku NEBO s funkcemi SUMIF a COUNTIF tak musíte pro každou podmínku použít samostatné funkce včetně pomocných sloupců, které nakonec sečtete.
Řešení pomocí funkce SUMIF
Rychle si ukážeme, jak bychom tento příklad řešili s funkcí SUMIF. V tabulce máme opět datumy, produkty a počty kusů. Tentokrát nás zajímá, kolik se v roce 2021 prodalo kusů produktu A (B25) nebo produktu B (C25). U výpočtu pomocí funkce SUMIF bychom nejprve museli vytvořit jeden pomocný sloupec (E31:E44), kde bychom opět spojili rok a produkt dohromady. Nakonec bychom použili funkci SUMIF, kde bychom jako oblast označili pomocný sloupec (E31:E44), jako kritérium spojíme zase rok a produkt pomocí ampersandu dohromady (B24&B25) a jako oblast součtu označíme sloupec s počtem (C31:C44). Ukončíme závorku pro první funkci SUMIF a bude následovat znaménko plus pro vyjádření logické podmínky NEBO. Za znaménkem plus bude druhá funkce SUMIF, zase oblast hodnot (E31:E44), kritérium rok a produkt dohromady (B24&C25) a oblast součtu je sloupec s počtem (C31:C44). Hotovo.
Řešení pomocí funkce SOUČIN.SKALÁRNÍ
Funkce SOUČIN.SKALÁRNÍ to opět zvládne v jedné buňce.
Nejprve do buňky B28 napíšeme funkci SOUČIN.SKALÁRNÍ, otevřeme závorku pro funkci, a otevřeme hned druhou závorku pro první logickou podmínku. První logická podmínka je, zda se produkty ve sloupci B31:B44 rovnají produktu A (B25), uzavřeme závorku. A teď musí přijít nebo. Logická podmínka nebo se v Excelu vyjádří znaménkem plus. Napíšeme tedy plus a otevřeme další dvě závorky pro druhou podmínku. Označíme zase sloupec s produkty (B31:B44) a zeptám se, zda se rovnají produktu B (C25). Konec závorky. Celá podmínka nebo musí být ještě ve svých vlastních závorkách, dopíšeme tedy závorky a zkusíme potvrdit, abychom viděli, co se vrátí za výsledek. Vrátilo se číslo 9. To znamená, že produkt A a produkt B by se dohromady měly v tabulce vyskytovat celkem 9krát. Ověříme to…a je to tak.
Základ máme tedy správně. Funkce SOUČIN.SKALÁRNÍ prošla každý řádek tabulky a porovnala, zda se produkt v tabulce rovná zadání. Pokud funkce SOUČIN.SKALÁRNÍ našla produkt A nebo B napsala jedničku, nakonec tyto hodnoty na konci sečetla. Vrátíme se tedy k funkci a budeme pokračovat. Dále nás zajímá poslední podmínka, kterou je, zda se produkt prodal v roce 2021. Napíšeme tedy středník a otevřeme závorku pro logickou podmínku. Napíšeme funkci ROK, otevřeme závorku, označíme sloupec s datumy (A31:A44), rovná se, vybraný rok (B24), zavřeme závorku a budeme pokračovat středníkem, poslední sloupec, který musíme označit je počet prodaných kusů (C31:C44), jelikož nás zajímá součet. Ukončíme závorkou a potvrdíme.
Vrátila se nám nula. Víte proč?
Ve vzorci máme chybu. Vrátíme se tedy do příkazového řádku a pokusíme se chybu najít. Víme, že první část s produkty máme správně, jelikož se nám vrátil správný počet. Druhou možností, kde by tedy mohla být chyba je v logické podmínce s rokem. Když si tuto část označíme a zmáčkneme klávesu F9, tak se objeví série pravd a nepravd. A jak víme z předchozích videí, tak excel neumí textové hodnoty násobit s číselnými. Musíte tedy tyto pravdy a nepravdy převést na číslo. Takže se k funkci vrátíme stisknutím CTRL+Z a chybu opravíme tak, že tuto logickou podmínku vynásobíme jedničkou. Potvrdíme. Vrátil se součet 100. Ověříme si to v naší zdrojové tabulce. A opravdu součet prodaných produktů A nebo B je dohromady 100.
2 komentáře
Dobrý den!
Funkce které zatím hojně používám: SUMA, IFERRO, IFS, SVYHLEDAT, COUNTIF, MIN, MAX, PRŮMĚR, SUBTOTAL, KDYŽ, HODONOTA.NA.TEXT, POČET, CONCATENATE, SUMIF, ROK, DNES, A, WEEKNUM, XLOOKUP, QUARTUL.INC.
Svoboda Oldřich, senior
Dobrý den,
mě sumifs funguje, i když office 365 nemám (Microsoft Office 2013 pro podnikatele). Ale možná to je tím, že mám nainstalované doplňky “analitické nástroje”, resp. “analitické nástroje – vba”