Funkce SOUČIN SKALÁRNÍ neboli v angličtině funkce SUMPRODUCT je jednou z nejlepších funkcí v Excelu, a to i přesto, že je to občas přehlížená. Funkce SOUČIN SKALÁRNÍ vrátí součet součinů v zadané oblasti. Hodí se tak nejen pro počítání vážených průměrů, ale kdekoliv, kde potřebujete nejdříve násobit a následně výsledky sečíst. Tato funkce v jedné buňce zvládne to, k čemu obvykle vytváříme pomocné sloupce a mezisoučty. A nejen to, funkce SOUČIN SKALÁRNÍ si poradí i s podmínkami. Umí tak s podmínkami nejen sčítat, ale i počítat. A to mnohdy mnohem lépe než funkce SUMIF a COUNTIF. Další výhodou je, že si poradí i s více podmínkami, což je výhodou pro ty, kteří nemají ve svých Officech dostupné verze SUMIFS a COUNTIFS.
Excelový soubor ke stažení:
Funkce SOUČIN SKALÁRNÍ
Funkce SOUČIN SKALÁRNÍ, v angličtině funkce SUMPRODUCT, je excelová funkce, která vrátí součet součinů v zadané oblasti. To jak ve své základní podobě funguje funkce SOUČIN SKALÁRNÍ si ukážeme na následujícím jednoduchém příkladu.
V prvním příkladu máme jednoduchou tabulku, ve které máme uvedené produkty, počet prodaných kusů a cenu za kus. Úkolem je zjistit, jaké jsou celkové tržby za prodané produkty. Základním způsobem, jak tento příklad vyřešit je, že můžeme vytvořit pomocný sloupec, v tomto případě sloupec D, a na každém řádku můžeme u každého produktu vynásobit počet prodaných kusů s jejich cenou. Nakonec tento sloupec v buňce D10 sečteme.
A nebo můžeme použít funkci SOUČIN SKALÁRNÍ, v angličtině je to funkce SUMPRODUCT.
=SOUČIN.SKALÁRNÍ (pole 1; [pole 2]; [pole 3]…)
Funkce SOUČIN SKALÁRNÍ má jako parametry pole. Ve své nejjednodušší podobě stačí této funkci pouze jeden parametr oblast buněk. Co funkce SOUČIN SKALÁRNÍ dělá?
Zkusíme si to vysvětlit na stejném příkladu. Místo toho abychom násobili cenu a počet kusů na každém řádku a nakonec to sčítali, tak celou operaci provedeme díky jedné funkci a v jedné buňce D11. Do buňky D11 napíšeme funkci SOUČIN SKALÁRNÍ a jako první parametr chce funkce SOUČIN SKALÁRNÍ pole. Prvním polem je počet kusů, označíme tedy celý sloupec počet (B4:B9) a jako druhé pole označíme sloupec s cenami (C4:C9). To je celé. V buňce D11 jsme dostali úplně stejný výsledek jako u manuálního postupu, a to bez mezi výpočtů a pomocného sloupce.
SOUČIN SKALÁRNÍ základy
Funkce SOUČIN SKALÁRNÍ vrací součet součinů v zadané oblasti nebo v poli. Využívá k tomu násobení jednotlivých oblastí, které nakonec sečte. V našem příkladu tedy funkce SOUČIN SKALÁRNÍ postupuje po jednotlivých řádcích. Na prvním řádku vynásobí číslo 5 kusů číslem 100 Kč, výsledek je 500 Kč, na druhém řádku vynásobí funkce 10 kusů krát 150 Kč a výsledkem bude 1 500 Kč, takto postupuje funkce po všech zadaných řádcích a když dojde nakonec, tak výsledky sečte. Tato funkce tak vlastně postupuje stejně jako v první ukázce, akorát místo toho, aby vytvořila pomocný sloupec, tak si mezivýsledky pamatuje a nakonec je sečte.
V dalším příkladu máme tři sloupce, k počtu kusů a ceně za kus přibyl ještě údaj za náklad za kus produktu. Klasickým způsobem bychom postupovali například tak, že bychom od ceny za kus odečetli náklad za kus a výsledek vynásobili počtem kusů, tak jak je to vidět ve sloupci Celkem. Celková tržba je vidět v buňce E21.
Zkusíme k tomu samému využít funkce SOUČIN.SKALÁRNÍ. Jelikož potřebujeme sloupec s náklady odčítat, tak nemůžeme jednoduše do funkce SOUČIN.SKALÁRNÍ uvést všechny tři sloupce. Pokud bychom to udělali, tak by funkce SOUČIN.SKALÁRNÍ vynásobila na prvním řádku hodnoty 5 x 100 x 15. Což není to, co chceme. Máme dva způsoby jak si s tímto úkolem můžeme poradit.
Prvním způsobem je, že využijeme kombinace dvou funkcí SOUČIN.SKALÁRNÍ. Napíšeme nejprve SOUČIN.SKALÁRNÍ, jako první pole označíme sloupec počet kusů (B15:B20) a jako druhé pole cenu za kus (C15:C20). Následně napíšeme mínus a použijeme druhou funkci SOUČIN.SKALÁRNÍ. V této druhé funkci označíme opět sloupec počet kusů (B15:B20) a sloupec náklad na kus (D15:D20). První funkce SOUČIN.SKLÁRNÍ vynásobí počet kusů a cenu a druhá funkce vynásobí počet kusů a náklad a nakonec tyto dvě hodnoty od sebe odečte, a to pro každý řádek a výsledné hodnoty sečte. Tak jak vidíme v buňce E22.
Druhý způsob je ten, že napíšeme do buňky E23 funkci SOUČIN.SKALÁRNÍ, otevřeme závorku pro funkci SOUČIN.SKALÁRNÍ a rovnou otevřeme závorku ještě jednou. Druhu závorku otevíráme proto, že se chystáme ve funkci SOUČIN.SKALÁRNÍ udělat mezivýpočet. Jako první pole označíme oblast ceny (C15:C20), napíšeme mínus a označíme sloupec nákladů (D15:D20), ukončíme závorku. Celá tato závorka je prvním polem funkce SOUČIN.SKALÁRNÍ. Budeme ve funkci pokračovat druhým polem, kterým je sloupec počet (B15:B20). Jak je vidět z buněk E21-E23, tak se všechny tři výsledky shodují.
SOUČIN SKALÁRNÍ a vážený průměr
SOUČIN.SKALÁRNÍ se vám bude hodit i u počítání váženého průměru, tak jako máme v dalším příkladu. Máme zde uvedené tržby za jednotlivé produkty a podíl na celkových tržbách a zajímalo by nás, jaké jsou naše průměrné tržby. Jelikož máme poměrně značné rozdíly v tržbách i podílech na celkových tržbách, tak by použití prostého průměru nebylo zcela vhodné. V tomto případě by se nám spíše hodil vážený průměr. Nejdříve ho spočítáme ručně. Vážený průměr se spočítá tak, že hodnotu vynásobíme podílem, k ní přičteme hodnotu vynásobenou podílem u druhého produktu a takto pokračujeme pro všechny produkty. Když dorazíme nakonec, tak potvrdíme a máme hodnotu váženého průměru. Tento výpočet je vidět v buňce B35.
Jelikož jednotlivé hodnoty mezi sebou násobíme a nakonec je sčítáme, tak je jasné, že je to skvělý případ pro SOUČIN.SKALÁRNÍ. Místo označování jednotlivých buněk, napíšeme do buňky B36 funkci SOUČIN.SKALÁRNÍ a jako první oblast označíme tržby (B27:B32) a jako druhou oblast označíme podíly (C27:C32). Pomocí funkce SOUČIN.SKALÁRNÍ jsme došli ke stejnému výsledku, a to desetkrát rychleji.
SOUČIN SKALÁRNÍ a podmínky
To ovšem není všechno, co funkce SOUČIN.SKALÁRNÍ dovede. Tato funkce si umí poradit i s podmínkami. Ukážeme si to na dalším příkladu. Máme tabulku s prodejem produktů a rádi bychom zjistili, kolik se prodalo produktu A. Pokud bychom neznali funkci SOUČIN.SKALÁRNÍ tak bychom k tomu použili nejspíše funkci SUMIF. Nejprve bychom si ale museli vytvořit opět pomocný sloupec, v našem případě sloupec D, ve kterém bychom spočítali celkové tržby po jednotlivých produktech. Nakonec bychom použili funkci SUMIF, tak jak to máme v buňce D50.
My ale známe funkci SOUČIN.SKALÁRNÍ, tak to zkusíme vyřešit s její pomocí. Do buňky D51 napíšeme funkci SOUČIN.SKALÁRNÍ a otevřeme první závorku. Jelikož máme v plánu vytvořit logickou podmínku přímo ve funkci SOUČIN.SKALÁRNÍ, tak musíme tuto podmínku uvést do samostatných uvozovek. Otevřeme proto rovnou ještě jednu závorku.
Naše logická podmínka je, zda se některý z produktů ve sloupci A rovná Produktu A. Označíme proto celý sloupec (A40:A49), ukončíme závorku a zeptáme se, zda se tato závorka rovná Produkt A. Jelikož se u podmínky Produkt A neodkazujeme na buňku, ale píšeme ho jako text do podmínky, tak ho nesmíme zapomenou uvést do uvozovek. Logická podmínka je hotová, napíšeme středník a budeme pokračovat druhým polem. Jako druhé pole označíme sloupec s počtem kusů (B40:B49), středník a jako poslední pole označíme sloupec s cenou (D40:D49). Funkce SOUČIN.SKALÁRNÍ teď půjde řádek po řádku a nejprve zjistí, zda se produkt na daném řádku rovná Produktu A, pokud ano, tak vynásobí počet kusů cenou produktu a výsledek si zapamatuje, pokud se produkt nebude rovnat produktu A, tak řádek nebude počítat.
Pokud bychom to teď ovšem takto potvrdili, tak se nám vrátí chyba. A je to proto, že logická podmínka v prvním poli nám vrátila sérii PRAVD a NEPRAVD. Toto si můžete ověřit tak, že si v příkazovém řádku označíte logickou podmínku a zmáčknete klávesu F9. Tím se vám rozbalí výsledek označené funkce. Jen se nezapomeňte do vzorce zase vrátit zmáčknutím klávesové kombinace CTRL+Z.
Po zmáčknutí klávesy F9 vidíme, že na řádku, kde máme Produkt A máme PRAVDU a na řádku, kde se nám produkt nerovná Produktu A máme nepravdu. Až sem to máme správně. Důvodem proč se nám vrátila chyba je to, že se funkce SOUČIN.SKALÁRNÍ snaží vynásobit textovou hodnotu PRAVDA a NEPRAVDA počtem kusů a cenou. A jelikož se násobí textovou hodnotou, tak excel vrátí chybu.
Řešením tedy je, převést textové hodnoty Pravda a Nepravda na číslo. Máme opět dva způsoby, jak to udělat.
SOUČIN SKALÁRNÍ a dvojitý negativ
Tím prvním způsobem je použít dvojitý negativ. Celou logickou podmínku uvedeme do vlastních závorek a před ní napíšeme dvakrát mínus. Tím se textové hodnoty převedou na čísla. V Excelu je PRAVDA vždy uvedena jako jednička a NEPRAVDA jako nula. Funkci potvrdíme a vrátil se správný výsledek. Na řádku, kde máme Produkt A se pravda změnila na jedničku, funkce SOUČIN.SKALÁRNÍ tak vynásobila jedničku počtem kusů a cenou. Tam, kde se produkt neshoduje se násobil počet a cena nulou. Takže byl výsledek nula a funkce ho tedy ignorovala.
Druhý způsob, jak můžete logické hodnoty převést na číslo je ten, že logickou podmínku vynásobíte jedničkou. V takovém případě bychom logickou podmínku opět uvedli do vlastních závorek a vynásobili jedničkou. Následně bychom pokračovali jako v předešlém příkladu.
SOUČIN SKALÁRNÍ a počítání
Funkce SOUČIN.SKALÁRNÍ se dá využít i pro počítání, nejen sčítání. V našem posledním příkladu zkusíme spočítat, kolik Produktu A jsme prodali. Využijeme funkci SOUČIN.SKALÁRNÍ. Do buňky B54 napíšeme funkci SOUČIN.SKALÁRNÍ a jelikož zase potřebujeme použít logickou podmínku, tak otevřeme ještě jednu závorku. Označíme sloupec s produkty (A40:A49), uzavřeme závorku a zeptá se, zda se závorka rovná se Produktu A v uvozovkách (A40:A49)=“Produkt A“. Uzavřeme závorku v logické podmínce a celou logickou podmínku vynásobíme jedničkou nebo před ní uvedeme dvojitý negativ, abychom hodnoty pravda a nepravda převedli na hodnoty 1 a 0. Napíšeme středník a označíme sloupec s počtem prodaných kusů (B40:B49).
2 komentáře
Dost dobré. Palec nahoru
Děkuji, v plánu je ještě navazující video s příklady z praxe 🙂