Všeobecný vzorec
=SUMPRODUCT(expression,range)
Zhrnutie
Ak chcete filtrovať výsledky SUMPRODUCT podľa konkrétnych kritérií, môžete namiesto použitia funkcie IF použiť jednoduché logické výrazy priamo na polia vo funkcii. V zobrazenom príklade sú vzorce v H5: H7:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
kde sú definované nasledujúce pomenované rozsahy:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Ak sa radšej chcete vyhnúť pomenovaným rozsahom, použite vyššie uvedené rozsahy ako absolútne referencie. Logické výrazy v H6 a H7 je možné kombinovať, ako je vysvetlené nižšie.
Vysvetlenie
Tento príklad ilustruje jednu z kľúčových silných stránok funkcie SUMPRODUCT - schopnosť filtrovať údaje pomocou základných logických výrazov namiesto funkcie IF. Vo vnútri SUMPRODUCT je prvé pole logickým výrazom, ktorý sa má filtrovať podľa farby „červená“:
--(color="red")
To má za následok pole alebo TRUE FALSE hodnoty, ktoré sú vynútené do jednotiek a núl s dvojitou zápornou (-) operáciou. Výsledkom je toto pole:
(1;0;1;0;0;0;1;0;0;0)
Všimnite si, že pole obsahuje 10 hodnôt, jednu pre každý riadok. Jedna označuje riadok, kde je farba „červená“ a nula označuje riadok s inou farbou.
Ďalej máme ďalšie dve polia: jedno pre množstvo a jedno pre cenu. Spolu s týmito výsledkami z prvého poľa máme:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Pri rozširovaní polí máme:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),(10;6;14;9;11;10;8;9;11;10),(15;18;15;16;18;18;15;16;18;16))
Základným správaním programu SUMPRODUCT je vynásobenie a potom súčet polí. Pretože pracujeme s tromi poľami, môžeme si operáciu vizualizovať tak, ako je to znázornené v nasledujúcej tabuľke, kde je stĺpec result výsledkom vynásobenia array1 * array2 * array3 :
| pole1 | pole2 | pole3 | výsledok |
|---|---|---|---|
| 1 | 10 | 15 | 150 |
| 0 | 6 | 18 | 0 |
| 1 | 14 | 15 | 210 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 18 | 0 |
| 1 | 8 | 15 | 120 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 16 | 0 |
Všimnite si, že pole1 funguje ako filter - nulové hodnoty tu „vynulovávajú“ hodnoty v riadkoch, kde farba nie je „červená“. Po vrátení výsledkov do SUMPRODUCT máme:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Čo vracia konečný výsledok 480.
Pridanie ďalších kritérií
Kritériá môžete rozšíriť pridaním ďalšieho logického výrazu. Napríklad na vyhľadanie celkového predaja s farbou „červená“ a stavom „TX“ obsahuje kód H6:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Poznámka: SUMPRODUCT nerozlišuje veľké a malé písmená.
Zjednodušenie pomocou jedného poľa
Profesionáli v programe Excel často trochu zjednodušia syntax vo vnútri SUMPRODUCT vynásobením polí priamo vo vnútri poľa1 takto:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Funguje to preto, lebo matematická operácia (násobenie) automaticky vynúti hodnoty TRUE a FALSE z prvých dvoch výrazov do jednotiek a núl.
