Stáhněte si ukázkový sešit
Tento výukový program ukazuje, jak používat Excel SUMPRODUCT Funkce v Excelu.
Přehled funkcí SUMPRODUCT
Funkce SUMPRODUCT znásobí pole čísel a sečte výsledné pole.
Chcete -li použít funkci listu SUMPRODUCT Excel, vyberte buňku a zadejte:
(Všimněte si, jak se zobrazují vstupy vzorců)
Funkce SUMPRODUCT Syntaxe a vstupy:
| 1 | = SUMPRODUCT (pole1, pole2, pole3) |
pole 1 - Pole čísel.
Co je funkce SUMPRODUCT?
Funkce SUMPRODUCT je jednou z výkonnějších funkcí v Excelu. Jeho název vás může vést k přesvědčení, že je určen pouze pro základní matematické výpočty, ale lze jej použít k mnohem více.
Pole
SUMPRODUCT vyžaduje vstupy polí.
Nejprve tedy, co rozumíme pojmem „pole“? Pole je jednoduchá skupina položek (např. Čísel) uspořádaných v určitém pořadí, stejně jako řada buněk. Pokud byste tedy měli v buňkách A1: A3 čísla 1, 2, 3, Excel by to přečetl jako pole {1,2,3}. Ve skutečnosti můžete zadat {1,2,3} přímo do vzorců aplikace Excel a rozpozná pole.
Níže si povíme více o polích, ale nejprve se podívejme na jednoduchý příklad.
Základní matematika
Podívejme se na základní příklad SUMPRODUCT, který jej používá k výpočtu celkových tržeb.
Máme tabulku produktů a chceme vypočítat celkové tržby. Budete v pokušení přidat nový sloupec, vzít prodanou cenu * cenu a poté nový sloupec sečíst. Místo toho však můžete jednoduše použít funkci SUMPRODUCT. Pojďme si projít vzorec:
| 1 | = SUMPRODUCT (A2: A4, B2: B4) |
Funkce načte rozsahy čísel do polí, vynásobí je proti sobě a poté sečte výsledky:
| 1234 | = SUMPRODUCT ({100, 50, 10}, {6, 7, 5})= SUMPRODUCT ({100 * 6, 50 * 7, 10 * 5})= SUMPRODUCT ({600, 350, 50}= 1000 |
Funkce SUMPRODUCT Funciton pro nás dokázala znásobit všechna čísla a provést součet.
Vážený průměr
Další případ, kdy je užitečné použít SUMPRODUCT, je, když potřebujete vypočítat vážený průměr. K tomu nejčastěji dochází při řešení školních úkolů, pojďme se proto podívat na následující tabulku.
Můžeme vidět, jak moc kvízy, testy a domácí úkoly stojí za celkovou známku, a také to, jaký je aktuální průměr pro každou konkrétní položku. Celkovou známku pak můžeme vypočítat psaním
| 1 | = SUMPRODUKT (B2: B4, C2: C4) |
Naše funkce znovu vynásobí každou položku v polích před součtem součtu. Takhle to funguje
| 123 | = SUMPRODUCT ({30%, 50%, 20%}, {73%, 90%, 95%})= SUMPRODUKT ({22%, 45%, 19%})= 86% |
Více sloupců
Další místo, které bychom mohli použít SUMPRODUCT, je ještě více sloupců, které je třeba všechny znásobit. Podívejme se na příklad, kde potřebujeme vypočítat objem v kusech řeziva.
Spíše než vytvářet pomocný sloupec pro výpočet celkového prodeje pro každý řádek, můžeme to udělat pomocí jediného vzorce. Náš vzorec bude
| 1 | = SUMPRODUKT (B2: B5, C2: C5, D2: D5) |
První položky každého pole se budou množit proti sobě (např. 4 * 2 * 1 = 8). Poté 2. (4 * 2 * 2 = 16) a 3rdatd. Celkově se tím vytvoří řada produktů, které vypadají jako {8, 16, 16, 32). Pak by celkový objem byl součtem tohoto pole, 72.
Jedno kritérium
Dobře, přidejme další vrstvu složitosti. Viděli jsme, že SUMPRODUCT zvládne pole čísel, ale co když chceme zkontrolovat kritéria? Můžete také vytvořit pole pro booleovské hodnoty (booleovské hodnoty jsou hodnoty, které jsou PRAVDA nebo NEPRAVDA).
Vezměte si například základní pole {1, 2, 3}. Vytvoříme odpovídající pole, které indikuje, zda je každé číslo větší než 1. Toto pole by vypadalo jako {FALSE, TRUE, TRUE}.
To je ve vzorcích velmi užitečné, protože můžeme snadno převést TRUE / FALSE na 1 / 0. Podívejme se na příklad.
Pomocí níže uvedené tabulky chceme vypočítat „Kolik prodaných kusů bylo červených?“
Můžeme to udělat pomocí tohoto vzorce:
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "červená")) |
"Vydrž! Co je tam se symbolem dvojitého mínusu? “ říkáš. Pamatujete si, jak jsem řekl, že můžeme převést z True/False na 1/0? Děláme to tak, že počítač donutíme provést matematickou operaci. V tomto případě říkáme „vezměte zápornou hodnotu a pak znovu vezměte zápornou hodnotu“. Když to napíšeme, naše pole se změní takto:
| 123 | {Pravda, Pravda, Nepravda}{-1, -1, 0}{1, 1, 0} |
Takže zpět k úplnému vzorci SUMPRODUCT se načte do našich polí a poté se množí takto
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0})= SUMPRODUCT ({100, 50, 0})= 150 |
Všimněte si, jak 3rd položka se stala 0, protože cokoli vynásobené 0 se stane nulou.
Více kritérií
Do naší funkce můžeme načíst až 255 polí, takže určitě můžeme načíst více kritérií. Podívejme se na tuto větší tabulku, kde jsme přidali prodaný měsíc.
Pokud chceme vědět, kolik prodaných položek bylo červených a byly v měsíci únoru, mohli bychom napsat náš vzorec jako
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "červená"), -(C2: C4 = "únor")) |
Počítač by pak vyhodnotil naše pole a rozmnožil se napříč. Už jsme se zabývali tím, jak se pole True/False mění na 1/0, takže tento krok prozatím přeskočím.
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= SUMPRODUCT ({0, 50, 0})= 50 |
V našem příkladu jsme měli pouze jeden řádek, který odpovídal všem kritériím, ale s reálnými daty jste mohli mít více řádků, které jste potřebovali, sčítat dohromady.
Složitá kritéria
Dobře, až do tohoto bodu na vás nemusí udělat dojem, protože všechny naše příklady bylo možné provést pomocí jiných funkcí, jako je SUMIF nebo COUNTIF. Nyní uděláme něco pro tyto další funkce nemůže dělat. Dříve měl náš sloupec Měsíc skutečné názvy měsíců. Co kdyby místo toho měla data?
Nyní nemůžeme provést SUMIF, protože SUMIF nedokáže zvládnout kritéria, která potřebujeme. SUMPRODUCT však zvládne manipulaci s polem a provedení hlubšího testu. Už jsme manipulovali s poli, když jsme přeložili True/False do 1/0. S tímto polem budeme manipulovat pomocí funkce MONTH. Zde je úplný vzorec, který použijeme
| 1 | = SUMPRODUKT (A2: A4, -(B2: B4 = "červená"), -(MĚSÍC (C2: C4) = 2)) |
Podívejme se na 3rd pole blíže. Nejprve náš vzorec extrahuje číslo měsíce z každého data v C2: C4. Tím získáme {1, 2, 2}. Dále zkontrolujeme, zda se tato hodnota rovná 2. Nyní naše pole vypadá jako {False, True, True}. Znovu uděláme dvojnásobek mínus a máme {0, 1, 1}. Nyní jsme zpět na podobném místě, jaké jsme měli v příkladu 3, a náš vzorec nám bude schopen říci, že v únoru bylo prodáno 50 kusů, které byly červené.
Dvojité mínus vs. násobení
Pokud jste dříve viděli funkci SUMPRODUCT, možná jste viděli trochu jiný zápis. Místo dvojitého mínusu můžete psát
| 1 | = SUMPRODUKT (A2: A4*(B2: B4 = "červená")*(MĚSÍC (C2: C4) = 2)) |
Vzorec bude stále fungovat stejným způsobem, pouze ručně řekneme počítači, že chceme znásobit pole. SUMPRODUCT to každopádně udělá, takže na tom, jak matematika funguje, se nic nemění. Provedením matematické operace se naše True/False převede na 1/0 stejné. Proč tedy ten rozdíl?
Většinou na tom příliš nezáleží a záleží na uživatelských preferencích. Existuje však alespoň jeden případ, kdy je potřeba znásobení.
Když používáte SUMPRODUCT, počítač očekává, že všechny argumenty (pole1, pole2 atd.) Budou mít stejnou velikost. To znamená, že mají stejný počet řádků nebo sloupců. Můžete však udělat to, co je známo jako výpočet dvourozměrného pole, pomocí SUMPRODUCT, který uvidíme v dalším příkladu. Když to uděláte, pole mají různé velikosti, takže musíme tuto kontrolu „stejné velikosti“ obejít.
Dva rozměry
Všechny předchozí příklady vedly naše pole stejným směrem. Jak uvidíme v následující tabulce, SUMPRODUCT zvládne věci probíhající dvěma směry.
Zde je naše tabulka prodaných jednotek, ale data jsou přeskupena tam, kde kategorie jdou přes vrchol. Pokud chceme zjistit, kolik položek bylo červených a v kategorii A, můžeme psát
| 1 | = SUMPRODUCT ((A2: A4 = "červená")*(B1: C1 = "A")*B2: C4) |
Co se to tu děje?? Ukazuje se, že se budeme množit ve dvou různých směrech. Vizualizovat to je těžší udělat jen pomocí napsané věty, takže máme pár obrázků, které nám pomohou. Za prvé, naše kritéria řádků (je červená?) Se budou množit v každém řádku v poli.
| 1 | = SUMPRODUCT ((A2: A4 = "ČERVENÁ")*B2: C4) |
Dále se kritéria sloupců (je to kategorie A?) Budou v každém sloupci znásobovat
| 1 | = SUMPRODUCT ((A2: A4 = "červená")*(B1: C1 = "A")*B2: C4) |
Poté, co obě tato kritéria odvedou svou práci, zbývají pouze nuly 5 a 10. SUMPRODUCT nám pak dá celkem 15 jako naši odpověď.
Pamatujete si, jak jsme mluvili o polích, která musí mít stejnou velikost, pokud neděláte dvě dimenze? To bylo částečně správné. Znovu se podívá na pole, která jsme použili v našem vzorci. The výška ze dvou našich polí je stejná a šířka ze dvou našich polí jsou stejná. Stále se však musíte ujistit, že věci budou správně uspořádány, ale můžete to udělat v různých dimenzích.
Dva rozměry a komplex
Mnohokrát se nám zobrazí data, která nejsou v nejlepším rozložení vhodném pro naše vzorce. Mohli bychom to zkusit ručně přeskupit, nebo můžeme být chytřejší s našimi vzorci. Podívejme se na následující tabulku.
Zde máme data pro naše položky a tržby smíchaná pro každý měsíc. Jak bychom měli zjistit, kolik položek Bob prodal za celý rok?
K tomu použijeme dvě další funkce: HLEDAT a ISNUMBER. Funkce HLEDAT nám umožní vyhledat klíčové slovo „položky“ v buňkách záhlaví. Výstup z této funkce bude buď číslem, nebo chybou (pokud klíčové slovo není nalezeno). Poté použijeme k převodu ISNUMBER že výstup do našich booleovských hodnot. Náš vzorec bude vypadat níže.
Už byste měli být s prvním polem docela obeznámeni. Vytvoří výstup jako {0, 1, 0, 1}. Další pole kritérií, o kterém jsme právě mluvili. Vytvoří číslo pro všechny buňky s „položkami“ a pro ostatní chybu {5, #N/A !, 5, #N/A!}. ISNUMBER to pak převede na logickou hodnotu {True, False, True, False}. Když se pak rozmnožíme, zachová pouze hodnoty z prvního a třetího sloupce. Poté, co se všechna pole proti sobě rozmnoží, budeme mít k dispozici pouze nenulová čísla, která jsou zde zvýrazněna:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob")*(ISNUMBER (SEARCH ("Items", B1: E1))*B2: E5)) |
SUMPRODUCT je poté sečte a získáme konečný výsledek 29.
SUMPRODUCT Or
Mnoho situací nastává tam, kde bychom chtěli mít možnost shrnout hodnoty, pokud má náš sloupec kritérií jednu hodnotu NEBO jinou hodnotu. Toho můžete v SUMPRODUCT dosáhnout přidáním dvou polí kritérií proti sobě.
V tomto příkladu chceme sečíst jednotky prodané za červenou i modrou.
Náš vzorec bude vypadat takto
| 1 | = SUMPRODUCT (A2: A7, (B2: B7 = "červená")+(B2: B7 = "modrá")) |
Podívejme se na pole červených kritérií. Vytvoří pole, které vypadá takto: {1, 1, 0, 0, 0, 0}. Modré pole kritérií bude vypadat jako {0, 0, 1, 0, 1, 0}. Když je sečtete, bude nové pole vypadat jako {1, 1, 1, 0, 1, 0}. Vidíme, jak se obě pole spojila do jednoho pole kritérií. Funkce to pak znásobí naším prvním polem a dostaneme {100, 50, 10, 0, 75, 0}. Všimněte si, že hodnoty pro Green byly vynulovány. Posledním krokem SUMPRODUKTU je sečtení všech čísel, aby bylo dosaženo našeho řešení 235.
Tady jedno varování. Dávejte si pozor, když se pole kritérií vzájemně nevylučují. V našem příkladu mohly být hodnoty ve sloupci B buď červené nebo modré, ale věděli jsme, že to nikdy nemůže být obojí. Zvažte, zda bychom napsali tento vzorec:
| 1 | = SUMPRODUCT (A2: A7, (A2: A7> = 50)+(B2: B7 = "modrá")) |
Naším záměrem je najít modré položky, které byly prodány nebo byly v množství větším než 50. Tyto podmínky však nejsou exkluzivní, protože v jednom řádku může být ve sloupci A více než 50 a být modrý. To by mělo za následek, že první pole kritérií bude vypadat jako {1, 1, 0, 1, 1, 0}, druhé pole kritérií bude {0, 0, 1, 0, 1, 0}. Sečtením se vytvoří {1, 1, 1, 1, 2, 0}. Vidíte, jak tam teď máme dvojku? Pokud by zůstal SUMPRODUCT, zdvojnásobil by hodnotu v tomto řádku, změnil by 75 na 150 a dostali bychom špatný výsledek. Abychom to napravili, umístíme na naše pole kontrolu vnějších kritérií, například:
| 1 | = SUMPRODUKT (A2: A7, -((A2: A7> = 50)+(B2: B7 = "modrá")> 0)) |
Nyní, když jsou obě pole vnitřních kritérií sečtena, zkontrolujeme, zda je výsledek větší než 0. Tím se zbavíme 2, které jsme měli dříve, a místo toho budeme mít pole jako {1, 1, 1 , 1, 1, 0}, které vytvoří správný výsledek.
SUMPRODUCT Přesný
Většina funkcí v Excelu nerozlišuje velká a malá písmena, ale někdy musíme být schopni provést vyhledávání s ohledem na velká a malá písmena. Když je požadovaný výsledek numerický, můžeme toho dosáhnout pomocí funkce EXACT uvnitř funkce SUMPRODUCT. Zvažte následující tabulku:
Chceme najít skóre pro položku „ABC123“. Normálně funkce EXACT porovná dvě položky a vrátí logický výstup s uvedením, zda tyto dvě položky jsou přesně stejný. Jelikož jsme však v SUMPRODUKTU, náš počítač bude vědět, že máme co do činění s poli, a bude schopen porovnat jednu položku s každou položkou v poli. Náš vzorec bude vypadat takto
| 1 | = SUMPRODUCT (-PŘESNĚ ("ABC123", A2: A5), B2: B5) |
PŘESNÁ funkce pak zkontroluje každou položku v formátu A2: A5 a zjistí, zda odpovídá hodnotě a velikosti písmen. Tím se vytvoří pole, které vypadá jako {0, 1, 0, 0}. Při vynásobení proti B2: B5 se pole změní na {0, 2, 0, 0}. Po závěrečném součtu dostaneme řešení 2.
SUMPRODUCT v Tabulkách Google
Funkce SUMPRODUCT funguje v Tabulkách Google úplně stejně jako v Excelu:
SUMPRODUCT Příklady ve VBA
Můžete také použít funkci SUMPRODUCT ve VBA. Typ: application.worksheetfunction.sumproduct (pole1, pole2, pole3)
Provedení následujících příkazů VBA
| 1 | Rozsah ("B10") = Application.WorksheetFunction.SumProduct (rozsah ("A2: A7"), rozsah ("B2: B7")) |
přinese následující výsledky
Argumenty funkce (pole1 atd.) Můžete buď zadat přímo do funkce, nebo definovat proměnné, které se místo toho použijí.
