SOUČIN.SKALÁRNÍ (SUMPRODUCT) - součty položek Excel

Úvodem do funkce SOUČIN.SKALÁRNÍ

Jak na součin (součet) položek v matici (tabulce) v Excel na základě podmínek, aneb praktické využití funkce SOUČIN.SKALÁRNÍ (v angličtině SUMPRODUCT), jednak její použití v listu, ruční výpočet až po využití funkce SOUČIN.SKALÁRNÍ ve VBA. Nezapomenu zmínit, funkce které funkci SOUČIN.SKALÁRNÍ dokáži nahradit.

Jak na funkci SOUČIN.SKALÁRNÍ prakticky od A do Z ukazuji v tomto článku, pro přehlednost je rozdělen na kapitoly:

• Teorie použití SOUČIN.SKALÁRNÍ - syntaxe
• Základní použití SOUČIN.SKALÁRNÍ
• Ruční zápis matice do funkce SOUČIN.SKALÁRNÍ
• Podmínky pro funkci SOUČIN.SKALÁRNÍ
  • Součet
  • Počet
• Rozsah (více) podmínek SOUČIN.SKALÁRNÍ
  • Součet
  • Počet
• Chyby ve funkci SOUČIN.SKALÁRNÍ
• SOUČIN.SKALÁRNÍ a operátory krát, plus, mínus, děleno
• SOUČIN.SKALÁRNÍ s datum a čas podle měsíců, roků
• SOUČIN.SKALÁRNÍ podmínka na první písmeno - nebo několik ...
• Funkce SOUČIN.SKALÁRNÍ bez vyfiltrovaných řádku
• Použití s funkce SOUČIN.SKALÁRNÍ ve VBA
• Součet využití na součet nahrada SUM
• Počet unikátu jak pomocí funkcí zjisti počet unikátních hodnot ve sloupci
• Související články
• Ke stažení

Teorie použití SOUČIN.SKALÁRNÍ

Funkce SOUČIN.SKALÁRNÍ (v angličtině SUMPRODUCT) vrací součet produktů v zadané oblasti nebo poli. Jako výchozí používá k součtu násobení (jedna oblast krát druhá agregováno po řádcích), ale je tuto funkci využít ke sčítání (někdy i k odčítání a dělení). V dalším textu tuto kostrbatou definici pohopíte snadněji na základě prezentovaných příkladů.

Syntaxe česky:

SOUČIN.SKALÁRNÍ(pole1;[pole2];[pole3];...) SOUČIN.SKALÁRNÍ(pole1*[pole2]*[pole3];...)

Syntaxe anglicky:

SUMPRODUCT(pole1,pole2,pole3,...) SUMPRODUCT(pole1*[pole2]*[pole3],...)

Popis argumentů:

• pole - Povinný argument. První argument ve formě tabulky (sloupce, matice), jejíž jednotlivé položky chcete násobit a poté sečíst.
• pole - nepovinné argumenty - Jedná se o druhý až 255 argument ve formě tabulky (sloupce, matice), jejíž jednotlivé položky chcete násobit a poté sečíst.

Poznámky:

• Oblasti tabulek, matic musí mít stejný rozměr.
.
• Nečíselné typy jsou jako nuly
• Někde v nápovědách je pole označeno jako matice (jde jen o jiný název).

Základní použití funkce SOUČIN.SKALÁRNÍ

Příklad 1

Potřebuji zjistit celkové nálady, když mám tabulku se sloupci počet ks a cena (neboli vynásobit cena * kusy a pak všechny položky sečíst - lze udělat pomocným sloupem a využít funkce SUMA) - potřebuji-li jen toto číslo mohu využít funkci SOUČIN.SKALÁRNÍ a ušetřím pomocný sloupec.

=SOUČIN.SKALÁRNÍ(B6:B8;C6:C8)

Neboli funkce provede

3*10+2*5+1*1 30 + 10 + 1 = 41

Příklad 2

Potřebuji zjistit celkové nálady, když mám tabulku se sloupci počet ks a cena a počet prodejů (neboli vynásobit cena * kusy * počet prodejů a pak všechny položky sečíst) - potřebuji-li jen toto číslo tak opět využiji funkci SOUČIN.SKALÁRNÍ.

=SOUČIN.SKALÁRNÍ(B20:B22;C20:C22;D20:D22)

Neboli funkce provede

3*10*2 + 2*5*3 + 1*1*5 60 + 30 + 5 = 95

V angličtině funkce SUMPRODUCT

Ruční zápis matice do funkce SOUČIN.SKALÁRNÍ

Ukázka že SOUČIN.SKALÁRNÍ může pracovat i s maticemi.

=SOUČIN.SKALÁRNÍ({1\2;3\4;5\6})

nebo

=SOUČIN.SKALÁRNÍ({1;3;5};{2;4;6})

Poznámka

Zápis se může lišit podle verze Excelu, ale o maticích jsem psal více zde: Jak na matice v Excel. Takže zde jen pro dokreslení situace a souvislostí.

Podmínky pro funkci SOUČIN.SKALÁRNÍ

Funkci SOUČIN.SKALÁRNÍ využijeme jako podmínkovou, která nám bude sčítat (neboli náhrada funkce SUMIFS) nebo počítat počet (neboli náhrada funkce COUNTIF) na základě podmínek.

Budete mít tabulku, kde jsou ve třech sloupcích tato data Pracovník, Měsíc, a Počet. Jedná se o fiktivní data prodejů na prodejně. V dalších ukázkách se hodnoty ve zdrojové tabulce mohou lišit (v rámci procvičení zkuste vypočíst i jinak, využitím jiných funkcí a zkontrolovat, že vaše úvahy jsou správné / funkce počítá, tak jak potřebujete).

Součet

Začnete součtem, kdy položky v součtu musí odpovídat podmínkám:

• Pracovník Pepa
• Měsíc Leden
• Pracovník Pepa a Měsíc Leden

Řešením:

=SOUČIN.SKALÁRNÍ((B6:B17="pepa")*D6:D17) =SOUČIN.SKALÁRNÍ((C6:C17="leden")*D6:D17) =SOUČIN.SKALÁRNÍ((B6:B17="pepa")*(C6:C17="leden")*D6:D17)

Poslední řešení pro Pepu a leden, lze provést i jinak, například:

=SOUČIN.SKALÁRNÍ(--(B6:B17="pepa");--(C6:C17="leden");D6:D17) =SOUČIN.SKALÁRNÍ(0+(B6:B17="pepa");0+(C6:C17="leden");D6:D17) =SOUČIN.SKALÁRNÍ(1*(B6:B17="pepa");1*(C6:C17="leden");D6:D17)

Tak co už se vám funkce SOUČIN.SKALÁRNÍ začíná líbit? Nahradit ji můžete SUMIFS:

=SUMIFS(D6:D17;B6:B17;"pepa";C6:C17;"leden")

Pozor: Jde o logické funkce, takže nelze mít jen středník, nevypočte:

=SOUČIN.SKALÁRNÍ((B6:B17="pepa");(C6:C17="leden");D6:D17)

O chybách ještě píšu v další kapitole.

Ukázka:

Počet

Součet máte za sebou a podívejte se ještě jak na počet (řádků splňujících podmínku).

Z důvodů porovnání bude mít úkol stejné podmínky, jen nepůjde o součet ale o počet, podmínky zůstávají:

• Pracovník Pepa
• Měsíc Leden
• Pracovník Pepa a Měsíc Leden

Řešením:

=SOUČIN.SKALÁRNÍ((B6:B17="pepa")*1) =SOUČIN.SKALÁRNÍ((C6:C17="leden")*1) =SOUČIN.SKALÁRNÍ((B6:B17="pepa")*(C6:C17="leden"))

Poslední řešení pro Pepu a leden, lze provést i jinak, například:

=SOUČIN.SKALÁRNÍ(--(B6:B17="pepa");--(C6:C17="leden")) =SOUČIN.SKALÁRNÍ(0+(B6:B17="pepa");0+(C6:C17="leden")) =SOUČIN.SKALÁRNÍ(1*(B6:B17="pepa");1*(C6:C17="leden"))

SOUČIN.SKALÁRNÍ lze nahradit funkce COUNTIF:

=COUNTIFS(B6:B17;"pepa";C6:C17;"leden")

Ukázka:

Rozsah (více) podmínek SOUČIN.SKALÁRNÍ

Mějte podobnou tabulku jako v předchozím případě. Kdy máte ale několik požadavku co potřebujete aby výpočet splňoval:

• Musí jít o Ivu (Iva) a Pepu (Pepa).
• Hodnoty v prodej větší než 2
• Hodnoty v prodej větší než 11

Samozřejmě jako v předchozím potřebuji mít vypočten součet i počet.

Jako domací cvičení si můžete vyzkoušet využití funkcí SUMIFS a COUNTIFS.

Součet

Pro součet, kdy v jednom sloupci mám více nezávislých podmínek použijete + (plus) jelikož potřebujete sčítat řádky, která vyhovují podmínce Pepa nebo Iva (pro rozsah je * (krát) musí splňovat větší a zároveň menší):

=SOUČIN.SKALÁRNÍ((B6:B18="Pepa")+(B6:B18="Iva");(C6:C18>2)*(C6:C18<11);C6:C18)

nebo

=SOUČIN.SKALÁRNÍ((B6:B18="Pepa")+(B6:B18="Iva");--(C6:C18>2);--(C6:C18<11);C6:C18)

Počet

=SOUČIN.SKALÁRNÍ((B6:B18="Pepa")+(B6:B18="Iva");--(C6:C18>2);--(C6:C18<11))

Ukázka:

Chyby SOUČIN.SKALÁRNÍ

Co zobrazí SOUČIN.SKALÁRNÍ u chybové hodnoty, textu místo čísla, nesprávné oblasti atd. Zatím rozebírám tyto chyby (časem doplním i další):

• Logická
• Nesourodá oblast
• Hodnota místo čísla
• Chybová hodnota v buňce

Logická

Je potřeba pochopit jak funkce funguje, doporučuji si projít příklady, kdy zjistíte a budete vědět bez dlouhého přemýšlení, v které situaci použijete +, kdy středník, atd.

Nesourodá oblast

Funkce nemá ráda pokud jsou oblasti nesourodé. Pokud se tak stane obdržíte chybu #HODNOTA!

=SOUČIN.SKALÁRNÍ(B7:B9;C7:C8)

Hodnota místo čísla

Jak jsem psal u syntaxe, pokud místo čísla budete mít text (pozor i číslo může být textem!) Tak se tato hodnota bere jako nula.

=SOUČIN.SKALÁRNÍ(B16:B18;C16:C18)

Chybová hodnota v buňce

Pokud pole (matice) obsahuje chybovou hodnotu, vrátí funkce také chybovou hodnotu:

=SOUČIN.SKALÁRNÍ(B25:B27;C25:C27)

SOUČIN.SKALÁRNÍ a operátory

Využití operátoru ve funkci SOUČIN.SKALÁRNÍ, myslím tím krát, plus, mínus, děleno. V základní ukázce bez dalších podmínek. =SOUČIN.SKALÁRNÍ(D5:D8+E5:E8) =SOUČIN.SKALÁRNÍ(D5:D8-E5:E8) =SOUČIN.SKALÁRNÍ(D5:D8*E5:E8) =SOUČIN.SKALÁRNÍ(D5:D8/E5:E8)

SOUČIN.SKALÁRNÍ s datum a čas

Funkce SOUČIN.SKALÁRNÍ (SUMPRODUCT) může mít podmínky nad bunkama s datum a čas. Prostě využijete funkce DEN, MĚSIC, ROK.

=SOUČIN.SKALÁRNÍ((ROK($B$5:$B$14)=B19)*$E$5:$E$14*$F$5:$F$14) =SOUČIN.SKALÁRNÍ((MĚSÍC($B$5:$B$14)=B21)*$E$5:$E$14*$F$5:$F$14)

Poznámka u samotné funkce MĚSÍC je jasné, že se nebere ohled na rok, pokud nepřidáte další podmínku týkajíci se roku ;) Ono někdy je vhodné mít všechny dobny pohromadě, někdy naopak potřebujete mít duben a příslušný rok. Jak ale spojit rok a měsíc již nechám na vás.

SOUČIN.SKALÁRNÍ podmínka na první písmeno

Ve funkci se dá odkazovat i na první (x-té) písmeno /písmena/, bohužel to nelze jednoduše využítím * a ? jako v jiných funkcích například SVYHLEDAT, POZVYHLEDAT. Když umíte použít funkce ZLEVA/ZPRAVA/ČAST tak již máte řešení. Případně přes funkci HLEDAT pokud hledáte zda dané slova obsahují hledané písmeno. V příkladu písmeno *e*

Jak na to? Funkci zleva asi použít umíte:

=ZLEVA(B12;1)="I"

Proto stačí jen přidat do "podmínky":

=SOUČIN.SKALÁRNÍ((ZLEVA($B$5:$B$12;1)=B17)*$E$5:$E$12*$F$5:$F$12)

Druhý případ hledané písmno ve slově přes funkci HLEDAT je složitější.

Pokud nenajde tak získate chybu, zda jde o chybu (slovo hledané písmeno/znaky neobsahuje) tak přes funkci JE.CHYBA získate logickou hodnotu PRAVDA, což ale potřebujete negovat tak zbýbvá funkce NE a opět máte hotovou podmínku.

=NE(JE.CHYBA(HLEDAT("P";B12)))

Výsledkem je pak funkce:

=SOUČIN.SKALÁRNÍ((NE(JE.CHYBA(HLEDAT(B20;$B$5:$B$12))))*$E$5:$E$12*$F$5:$F$12)

No řekněte není ten Excel krásný ;)

Ukázka

Tipy

Nemusí jít jen o jedno písmeno, může jicch být více, můžete počítak o podle funkce DELKA, kdy zjistíte kolik produktu má nevalidní kód (má více znaků atd.).

Funkce SOUČIN.SKALÁRNÍ s bez filtrovaných řádku

Tip jak využít funkci SOUČIN.SKALÁRNÍ, kdy ve výsledku jsou zahrnuty vyfiltrované hodnoty.

Využívá funkci SUBTOTAL a funkci POSUN a ŘADEK.

=SOUČIN.SKALÁRNÍ( E5:E16; SUBTOTAL(109; POSUN(F5:F16; ŘÁDEK(F5:F16) - MIN(ŘÁDEK(F5:F16) );0;1)))

Pokud chci jen počet hodnot.

=SOUČIN.SKALÁRNÍ(SUBTOTAL(109; POSUN(F5:F16; ŘÁDEK(F5:F16) - MIN(ŘÁDEK(F5:F16) );0;1 )))

Použití s funkce SOUČIN.SKALÁRNÍ s využitím VBA

Jak využít funkce SOUČIN.SKALÁRNÍ ve VBA

Dim Pocet, Cena As Range Set Pocet = Range("B5:B7") Set Cena = Range("C5:C7") Hodnota = Application.SumProduct(Pocet, Cena) MsgBox (Hodnota)

Zápis funkce do buňky pomocí VBA:

Range("B9").Formula = "=SUMPRODUCT(B5:B7,C5:C7)" Range("B10").FormulaLocal = "=SOUČIN.SKALÁRNÍ(B5:B7;C5:C7)"

Součet - náhrada funkce SUMA (SUM)

Potřebuji součet nad sloupce. Vím že lze použít funkci SUMA, ale proč nevyužít SOUČIN.SKALÁRNÍ (SUMPRODUCT).

=SUMPRODUCT(C8:C10)

Počet unikátu - náhrada funkce SUMA (SUM)

Potřebuji počet unikátu. Mohu využít možnost zjistit přes COUNTIFS počet stejných hodnot. Pokud jedničku vydělým výsledým číslem počtu unikátu. Mohu následně sečíst. Případně mohu využít i ke součtuu SUMA (SUM).

=SUMPRODUCT(1/COUNTIF(B4:B10;B4:B10))

Související články s funkci SOUČIN.SKALÁRNÍ

Další články související s matematickými funkcemi:

• Přehled matematických funkcí
• SUMIFS
• COUNTIFS
• Matice úvod

Ke stažení

Soubor v přípravě.

Závěrem

Využíváte nějaký trik s funkcí SOUČIN.SKALÁRNÍ, můžete zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 10:59

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.