Splošna formula
=SUMPRODUCT(costs,--(range="x"))
Povzetek
Za izračun cen svežnjev izdelkov s preprostim znakom "x" za vključitev ali izključitev izdelka lahko uporabite formulo, ki temelji na funkciji SUMPRODUCT. V prikazanem primeru je formula v D11:
=SUMPRODUCT($C$5:$C$9,--(D5:D9="x"))
Pojasnilo
Funkcija SUMPRODUCT pomnoži obsege ali nize in vrne vsoto izdelkov. To se sliši dolgočasno, toda SUMPRODUCT je elegantna in vsestranska funkcija, kar ta primer lepo ponazarja.
V tem primeru je SUMPRODUCT konfiguriran z dvema nizoma. Prva matrika je obseg, ki drži cene izdelkov:
$C$5:$C$9
Upoštevajte, da je referenca absolutna za preprečevanje sprememb, saj je formula kopirana v desno. Ta obseg se ovrednoti z naslednjo matriko:
(99;69;129;119;49)
Druga matrika se ustvari s tem izrazom:
--(D5:D9="x")
Rezultat D5: D9 = "x" je niz TRUE FALSE vrednosti, kot je ta:
(TRUE;TRUE;FALSE;FALSE;FALSE)
Dvojni negativ (-) pretvori te TRUE FALSE vrednosti v 1s in 0s:
(1;1;0;0;0)
Torej, znotraj SUMPRODUCT imamo:
=SUMPRODUCT((99;69;129;119;49),(1;1;0;0;0))
Nato funkcija SUMPRODUCT pomnoži ustrezne elemente v vsaki matriki skupaj:
=SUMPRODUCT((99;69;0;0;0))
in vrne vsoto izdelkov, v tem primeru 168.
Druga matrika deluje kot filter za vrednosti v prvi matriki. Ničle v array2 prekličejo elemente v array1, 1s v array2 pa omogočajo, da vrednosti iz array1 prehajajo v končni rezultat.
Z enim nizom
SUMPRODUCT je nastavljen tako, da sprejema več nizov, vendar lahko to formulo nekoliko poenostavite tako, da na začetku navedete eno matriko:
=SUMPRODUCT($C$5:$C$9*(D5:D9="x"))
Matematična operacija (množenje) samodejno prisili vrednosti TRUE FALSE v drugem izrazu na enote in ničle, pri čemer ni potreben dvojni negativ.
