Üldine valem
=SUMPRODUCT(costs,--(range="x"))
Kokkuvõte
Toote komplekti hinnakujunduse arvutamiseks toote lisamiseks või välistamiseks lihtsa "x" abil saate kasutada funktsiooni SUMPRODUCT põhinevat valemit. Näidatud näites on valem D11-s:
=SUMPRODUCT($C$5:$C$9,--(D5:D9="x"))
Selgitus
Funktsioon SUMPRODUCT korrutab vahemikud või massiivid kokku ja tagastab toodete summa. See kõlab igavalt, kuid SUMPRODUCT on elegantne ja mitmekülgne funktsioon, mida see näide kenasti illustreerib.
Selles näites on SUMPRODUCT konfigureeritud kahe massiiviga. Esimene massiiv on vahemik, mis hoiab toote hinnakujundust:
$C$5:$C$9
Pange tähele, et muutuste vältimiseks on viide absoluutne, kuna valem kopeeritakse paremale. Selles vahemikus hinnatakse järgmist massiivi:
(99;69;129;119;49)
Teine massiiv genereeritakse selle avaldisega:
--(D5:D9="x")
D5: D9 = "x" tulemus on massiivi TRUE FALSE väärtusi nagu see:
(TRUE;TRUE;FALSE;FALSE;FALSE)
Topeltnegatiiv (-) teisendab need TRUE FALSE väärtused väärtusteks 1 ja 0:
(1;1;0;0;0)
Seega on meil SUMPRODUCTis:
=SUMPRODUCT((99;69;129;119;49),(1;1;0;0;0))
Funktsioon SUMPRODUCT korrutab seejärel iga massiivi vastavad elemendid kokku:
=SUMPRODUCT((99;69;0;0;0))
ja tagastab toodete summa, antud juhul 168.
Tegelikult toimib teine massiiv esimese massiivi väärtuste filtrina. Massiivis 2 olevad nullid tühistavad massiivi 1 üksused ja massiivi 2 sekundid võimaldavad massiivi 1 väärtustel läbida lõpptulemuse.
Ühe massiiviga
SUMPRODUCT on seadistatud aktsepteerima mitut massiivi, kuid saate seda valemit natuke lihtsustada, pakkudes alguses ühe massiivi:
=SUMPRODUCT($C$5:$C$9*(D5:D9="x"))
Matemaatikaoperatsioon (korrutamine) sunnib teise avaldise TÕELISED VALE väärtused automaatselt ühedeks ja nullideks, topeltnegatiivi pole vaja.
