Obecný vzorec
=SUMPRODUCT(expression,range)
souhrn
Chcete-li filtrovat výsledky SUMPRODUCT podle konkrétních kritérií, můžete namísto použití funkce IF použít jednoduché logické výrazy přímo na pole ve funkci. V zobrazeném příkladu jsou vzorce v H5: H7:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
kde jsou definovány následující pojmenované rozsahy:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Pokud se raději vyhnete pojmenovaným rozsahům, použijte výše uvedené rozsahy jako absolutní odkazy. Logické výrazy v H6 a H7 lze kombinovat, jak je vysvětleno níže.
Vysvětlení
Tento příklad ilustruje jednu z klíčových silných stránek funkce SUMPRODUCT - schopnost filtrovat data pomocí základních logických výrazů místo funkce IF. Uvnitř SUMPRODUCT je první pole logickým výrazem, který se má filtrovat na barvu „červená“:
--(color="red")
To má za následek pole nebo TRUE FALSE hodnoty, které jsou vynuceny do jedniček a nul s dvojitou zápornou (-) operací. Výsledkem je toto pole:
(1;0;1;0;0;0;1;0;0;0)
Všimněte si, že pole obsahuje 10 hodnot, jednu pro každý řádek. Jedna označuje řádek, kde je barva „červená“, a nula označuje řádek s jakoukoli jinou barvou.
Dále máme další dvě pole: jedno pro množství a jedno pro cenu. Spolu s tímto výsledkem z prvního pole máme:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Při rozšiřování 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 chováním SUMPRODUCT je znásobení a poté součet polí. Vzhledem k tomu, pracujeme se třemi poli, můžeme představit operaci, jak je ukázáno v následující tabulce, kde je kolona výsledek je výsledkem násobení array1 * array2 * array3 :
| pole1 | pole2 | pole3 | výsledek |
|---|---|---|---|
| 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šimněte si, že array1 funguje jako filtr - nulové hodnoty zde „vynulovávají“ hodnoty v řádcích, kde barva není „červená“. Vrácením výsledků zpět do SUMPRODUCT máme:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Což vrací konečný výsledek 480.
Přidání dalších kritérií
Kritéria můžete rozšířit přidáním dalšího logického výrazu. Například pro zjištění celkového prodeje, kde je barva „červená“ a stav „TX“, obsahuje H6:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Poznámka: SUMPRODUCT nerozlišuje velká a malá písmena.
Zjednodušení pomocí jediného pole
Profesionálové v aplikaci Excel často trochu zjednoduší syntaxi uvnitř SUMPRODUCT vynásobením polí přímo uvnitř pole1 takto:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Funguje to, protože matematická operace (násobení) automaticky vynucuje hodnoty PRAVDA a NEPRAVDA z prvních dvou výrazů do jedniček a nul.
