Všeobecný vzorec
=SUMPRODUCT(costs,--(range="x"))
Zhrnutie
Ak chcete vypočítať cenu balíka produktu pomocou jednoduchého znaku „x“ na zahrnutie alebo vylúčenie produktu, môžete použiť vzorec založený na funkcii SUMPRODUCT. V zobrazenom príklade je vzorec v D11:
=SUMPRODUCT($C$5:$C$9,--(D5:D9="x"))
Vysvetlenie
Funkcia SUMPRODUCT znásobí rozsahy alebo polia a vráti súčet produktov. Znie to nudne, ale SUMPRODUCT je elegantná a všestranná funkcia, ktorú tento príklad pekne ilustruje.
V tomto príklade je SUMPRODUCT nakonfigurovaný s dvoma poľami. Prvým poľom je rozsah, ktorý drží ceny produktov:
$C$5:$C$9
Upozorňujeme, že odkaz je absolútny, aby sa zabránilo zmenám pri kopírovaní vzorca vpravo. Tento rozsah sa vyhodnocuje do nasledujúceho poľa:
(99;69;129;119;49)
Druhé pole je generované s týmto výrazom:
--(D5:D9="x")
Výsledkom D5: D9 = "x" je pole TRUE FALSE hodnôt, ako je tento:
(TRUE;TRUE;FALSE;FALSE;FALSE)
Dvojitý zápor (-) prevádza tieto SKUTOČNÉ FALSE hodnoty na 1 s a 0 s:
(1;1;0;0;0)
Vo vnútri SUMPRODUCT teda máme:
=SUMPRODUCT((99;69;129;119;49),(1;1;0;0;0))
Funkcia SUMPRODUCT potom znásobí zodpovedajúce položky v každom poli spoločne:
=SUMPRODUCT((99;69;0;0;0))
a vráti súčet produktov, v tomto prípade 168.
Druhé pole účinne funguje ako filter pre hodnoty v prvom poli. Nula v poli2 zruší položky v poli1 a 1s v poli2 umožní, aby hodnoty z poľa1 prešli do konečného výsledku.
S jedným poľom
SUMPRODUCT je nastavený na prijímanie viacerých polí, ale tento vzorec môžete trochu zjednodušiť poskytnutím jedného poľa na začiatku:
=SUMPRODUCT($C$5:$C$9*(D5:D9="x"))
Matematická operácia (násobenie) automaticky vynúti hodnoty TRUE FALSE v druhom výraze na jednotky a nuly, bez potreby dvojitého záporného čísla.
