Ako používať funkciu Excel SUMPRODUCT

Zhrnutie

Funkcia SUMPRODUCT znásobí rozsahy alebo polia a vráti súčet produktov. Znie to nudne, ale SUMPRODUCT je neuveriteľne všestranná funkcia, ktorú je možné použiť na počítanie a sčítanie ako COUNTIFS alebo SUMIFS, ale s väčšou flexibilitou. Vo vnútri SUMPRODUCT možno ľahko použiť ďalšie funkcie na ďalšie rozšírenie funkcií.

Účel

Vynásobte a potom sčítajte polia

Návratová hodnota

Výsledok vynásobeného a sčítaného poľa

Syntax

= SUMPRODUCT (pole1, (pole2), …)

Argumenty

  • pole1 - prvé pole alebo rozsah, ktorý sa má vynásobiť, potom pridať.
  • pole2 - (voliteľné) Druhé pole alebo rozsah, ktorý sa má vynásobiť, a potom pridať.

Verzia

Excel 2003

Poznámky k použitiu

Funkcia SUMPRODUCT pracuje s poliami, ale na vstup nevyžaduje normálnu syntax poľa (Ctrl + Shift + Enter). Účelom funkcie SUMPRODUCT je vynásobiť a potom sčítať polia. Ak je dodané iba jedno pole, SUMPRODUCT jednoducho sčíta položky v poli. Môže byť dodaných až 30 polí.

Keď sa prvýkrát stretnete s SUMPRODUCT, môže sa to zdať nudné, zložité a dokonca nezmyselné. Ale SUMPRODUCT je úžasne všestranná funkcia s mnohými možnosťami použitia. Pretože bude s poľami zaobchádzať ladne a bez sťažností, môžete ho použiť na inteligentné a elegantné spracovanie rozsahov buniek (odkazy na príklady vzorcov nájdete na tejto stránke).

Na ilustráciu fungovania programu SUMPRODUCT uvádzam niekoľko bežných príkladov.

SUMPRODUCT pre podmienené sumy a počty

Predpokladajme, že máte nejaké údaje o objednávke v A2: B6, so stavom v stĺpci A, tržbami v stĺpci B:

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

Pomocou SUMPRODUCT môžete spočítať celkové tržby pre Texas („TX“) pomocou tohto vzorca:

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

A celkový predaj z Texasu („TX“) môžete sčítať pomocou tohto vzorca:

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

Poznámka: Nenechajte sa zmiasť dvojitým negatívom. Toto je bežný trik používaný v pokročilejších vzorcoch programu Excel na vynútenie PRAVÝCH a NEPRAVÝCH hodnôt do 1 a 0. Zobraziť viac nižšie …

V príklade súčtu vyššie je tu virtuálna reprezentácia dvoch polí, ktoré ako prvé spracoval SUMPRODUCT:

pole1 pole2
NEPRAVDA 75
NEPRAVDA 100
PRAVDA 125
NEPRAVDA 125
PRAVDA 150

Každé pole má 5 položiek. Prvé pole obsahuje hodnoty TRUE / FALSE, ktoré sú výsledkom výrazu A2: A6 = "TX", a druhé pole je obsahom B2: B6. Každá položka v prvom poli sa vynásobí zodpovedajúcou položkou v druhom poli. Avšak v súčasnom stave bude výsledok SUMPRODUCT nulový, pretože hodnoty TRUE a FALSE sa budú považovať za nulové. Potrebujeme, aby položky v poli1 boli číselné - je potrebné ich „vynútiť“ do jednotiek a núl. Tu prichádza na rad dvojitý negatív.

Použitím dvojitého záporného čísla - (dvojité unárne, pre vás technické typy) dokážeme vynútiť hodnotu TRUE / FALSE na číselné hodnoty jedna a nula, ako je to znázornené vo virtuálnom znázornení nižšie. Posledný stĺpec „Produkt“ predstavuje výsledok vynásobenia obidvoch polí dohromady. Sčítaný výsledok, 275, je hodnota, ktorú vráti SUMPRODUCT.

pole1 pole2 Výrobok
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
Súčet 275

Po použití syntaxe zloženej zátvorky pre polia vyzerá príklad po donútení takto:

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

a takto po násobení:

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

Tento príklad podrobnejšie rozširuje vyššie uvedené nápady.

SUMPRODUCT s ďalšími funkciami

SUMPRODUCT môže priamo využívať ďalšie funkcie. Možno uvidíte SUMPRODUCT, ktorý sa používa s funkciou LEN na spočítanie celkového počtu znakov v rozsahu, alebo s funkciami ako ISBLANK, ISTEXT atď. Toto nie sú zvyčajne funkcie poľa, ale ak dostanú rozsah, vytvoria „pole výsledkov“. Pretože program SUMPRODUCT je navrhnutý na prácu s poliami, je schopný priamo vykonávať výpočty na poliach. Môže to byť dobrý spôsob, ako ušetriť miesto v hárku, pretože eliminujete potrebu stĺpca „pomocník“.

Predpokladajme napríklad, že máte v A1: A10 10 rôznych textových hodnôt a chcete spočítať celkový počet znakov pre všetkých 10 hodnôt. Do stĺpca B môžete pridať pomocný stĺpec, ktorý používa tento vzorec: LEN (A1) na výpočet znakov v každej bunke. Potom môžete pomocou SUMA spočítať všetkých 10 čísel. Pomocou programu SUMPRODUCT však môžete napísať nasledujúci vzorec:

=SUMPRODUCT(LEN(A1:A10))

Ak sa použije v rozsahu ako A1: A10, funkcia LEN vráti pole s 10 hodnotami. Potom SUMPRODUCT jednoducho sčíta všetky hodnoty a vráti výsledok bez potreby stĺpca pomocníka.

Nižšie nájdete príklady mnohých ďalších spôsobov použitia SUMPRODUCT.

Poznámky:

  1. SUMPRODUCT považuje nečíselné položky v poliach za nuly.
  2. Argumenty poľa musia mať rovnakú veľkosť. V opačnom prípade SUMPRODUCT vygeneruje #HODNOTU! chybová hodnota.
  3. Logické testy vo vnútri polí vytvoria PRAVÉ a NEPRAVÉ hodnoty. Vo väčšine prípadov ich budete chcieť prinútiť k číslam 1 a 0.
  4. SUMPRODUCT môže často priamo využiť výsledok iných funkcií (pozri príklady vzorcov nižšie)

Zaujímavé články...