Jak používat funkci Excel SUMPRODUCT -

souhrn

Funkce SUMPRODUCT znásobuje rozsahy nebo pole a vrací součet produktů. Zní to nudně, ale SUMPRODUCT je neuvěřitelně všestranná funkce, kterou lze použít k počítání a sčítání jako COUNTIFS nebo SUMIFS, ale s větší flexibilitou. V SUMPRODUCT lze snadno použít další funkce, které ještě více rozšíří funkčnost.

Účel

Vynásobte a poté sečtěte pole

Návratová hodnota

Výsledek vynásobených a sečtených polí

Syntax

= SUMPRODUCT (pole1, (pole2),…)

Argumenty

  • array1 - první pole nebo rozsah, který se má vynásobit, poté přidat.
  • pole2 - (volitelně) Druhé pole nebo rozsah, který se má znásobit, poté přidat.

Verze

Excel 2003

Poznámky k použití

Funkce SUMPRODUCT pracuje s poli, ale pro vstup nevyžaduje běžnou syntaxi pole (Ctrl + Shift + Enter). Účelem funkce SUMPRODUCT je vynásobení a sčítání polí. Pokud je dodáno pouze jedno pole, SUMPRODUCT jednoduše sčítá položky v poli. Lze dodat až 30 polí.

Když se poprvé setkáte s SUMPRODUCT, může se to zdát nudné, složité a dokonce zbytečné. Ale SUMPRODUCT je úžasně univerzální funkce s mnoha využitími. Protože bude pracovat s poli ladně a bez stížností, můžete jej použít k chytrému a elegantnímu zpracování rozsahů buněk (viz odkazy na příklady vzorců na této stránce).

Pro ilustraci toho, jak SUMPRODUCT funguje, uvádíme několik běžných příkladů.

SUMPRODUCT pro podmíněné částky a počty

Předpokládejme, že máte některá data objednávky v A2: B6, ve sloupci A stav, ve sloupci B prodej:

A B
1 Stát Odbyt
2 UT 75
3 CO 100
4 TX 125
5 CO 125
6 TX 150

Pomocí SUMPRODUCT můžete počítat celkový prodej pro Texas („TX“) pomocí tohoto vzorce:

=SUMPRODUCT(--(A2:A6="TX"))

A celkový prodej pro Texas („TX“) můžete sečíst pomocí tohoto vzorce:

=SUMPRODUCT(--(A2:A6="TX"),B2:B6)

Poznámka: Nenechte se zmást dvojitým negativem. Toto je běžný trik používaný v pokročilejších vzorcích aplikace Excel k vynucení TRUE a FALSE hodnot do 1 a 0. Zobrazit více níže …

U výše uvedeného příkladu součtu je zde virtuální reprezentace dvou polí, která byla nejprve zpracována programem SUMPRODUCT:

pole1 pole2
NEPRAVDIVÉ 75
NEPRAVDIVÉ 100
SKUTEČNÝ 125
NEPRAVDIVÉ 125
SKUTEČNÝ 150

Každé pole má 5 položek. První pole obsahuje hodnoty TRUE / FALSE, které jsou výsledkem výrazu A2: A6 = "TX", a druhé pole je obsahem B2: B6. Každá položka v prvním poli bude vynásobena odpovídající položkou ve druhém poli. V aktuálním stavu však bude výsledek SUMPRODUCT nulový, protože hodnoty TRUE a FALSE budou považovány za nulové. Potřebujeme, aby položky v poli1 byly číselné - musí být „vynuceny“ do jednotek a nul. To je místo, kde přichází dvojitý negativní.

Použitím dvojitého záporného čísla - (dvojité unární, pro vás technické typy) jsme schopni vynutit TRUE / FALSE na číselné hodnoty jedna a nula, jak je znázorněno ve virtuální reprezentaci níže. Poslední sloupec „Produkt“ představuje výsledek vynásobení dvou polí dohromady. Souhrnný výsledek, 275, je hodnota, kterou SUMPRODUCT vrací.

pole1 pole2 Produkt
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
Součet 275

Po použití syntaxe složené závorky pro pole vypadá příklad po nátlaku takto:

=SUMPRODUCT((0,0,1,0,1),(75,100,125,125,150))

a takto po násobení:

=SUMPRODUCT((0,0,125,0,150))

Tento příklad rozšiřuje výše uvedené myšlenky podrobněji.

SUMPRODUCT s dalšími funkcemi

SUMPRODUCT může přímo využívat další funkce. Můžete vidět, že se SUMPRODUCT používá s funkcí LEN k počítání celkových znaků v rozsahu, nebo s funkcemi jako ISBLANK, ISTEXT atd. Nejedná se obvykle o funkce pole, ale pokud jim bude dán rozsah, vytvoří „pole výsledků“. Protože je SUMPRODUCT vytvořen pro práci s poli, je schopen provádět výpočty na polích přímo. To může být dobrý způsob, jak ušetřit místo v listu tím, že eliminujete potřebu sloupce „pomocník“.

Předpokládejme například, že máte v A1: A10 10 různých textových hodnot a chcete spočítat celkový počet znaků pro všech 10 hodnot. Do sloupce B můžete přidat pomocný sloupec, který používá tento vzorec: LEN (A1) pro výpočet znaků v každé buňce. Potom můžete pomocí SUMA sečíst všech 10 čísel. Pomocí SUMPRODUCT však můžete napsat vzorec takto:

=SUMPRODUCT(LEN(A1:A10))

Při použití s ​​rozsahem jako A1: A10 vrátí funkce LEN pole 10 hodnot. Pak SUMPRODUCT jednoduše sčítá všechny hodnoty a vrátí výsledek bez nutnosti použití pomocného sloupce.

Níže naleznete příklady mnoha dalších způsobů použití SUMPRODUCT.

Poznámky:

  1. SUMPRODUCT považuje nečíselné položky v polích za nuly.
  2. Argumenty pole musí mít stejnou velikost. Jinak SUMPRODUCT vygeneruje #HODNOTU! chybová hodnota.
  3. Logické testy uvnitř polí vytvoří hodnoty PRAVDA a NEPRAVDA. Ve většině případů je budete chtít donutit k číslům 1 a 0.
  4. SUMPRODUCT může často přímo využívat výsledek jiných funkcí (viz příklady vzorců níže)

Zajímavé články...