Generisk formel
=SUMPRODUCT(costs,--(range="x"))
Sammendrag
For å beregne produktpakkepriser ved å bruke et enkelt "x" for å inkludere eller ekskludere et produkt, kan du bruke en formel basert på SUMPRODUCT-funksjonen. I eksemplet vist er formelen i D11:
=SUMPRODUCT($C$5:$C$9,--(D5:D9="x"))
Forklaring
SUMPRODUCT-funksjonen multipliserer områder eller arrays sammen og returnerer summen av produkter. Dette høres kjedelig ut, men SUMPRODUCT er en elegant og allsidig funksjon, som dette eksemplet illustrerer pent.
I dette eksemplet er SUMPRODUCT konfigurert med to matriser. Den første matrisen er serien som inneholder produktpriser:
$C$5:$C$9
Merk at referansen er absolutt for å forhindre endringer da formelen kopieres til høyre. Dette området evalueres til følgende matrise:
(99;69;129;119;49)
Den andre matrisen genereres med dette uttrykket:
--(D5:D9="x")
Resultatet av D5: D9 = "x" er en matrise med SANNE FALSE verdier som dette:
(TRUE;TRUE;FALSE;FALSE;FALSE)
Dobbeltnegativet (-) konverterer disse SANNE FALSKE verdiene til 1s og 0s:
(1;1;0;0;0)
Så inne i SUMPRODUCT har vi:
=SUMPRODUCT((99;69;129;119;49),(1;1;0;0;0))
SUMPRODUCT-funksjonen multipliserer deretter tilsvarende elementer i hver matrise sammen:
=SUMPRODUCT((99;69;0;0;0))
og returnerer summen av produkter, 168 i dette tilfellet.
Effektivt fungerer den andre matrisen som et filter for verdiene i den første matrisen. Nuller i array2 avbryter elementer i array1, og 1s i array2 lar verdier fra array1 passere gjennom til det endelige resultatet.
Med en enkelt matrise
SUMPRODUCT er satt opp for å godta flere matriser, men du kan forenkle denne formelen litt ved å gi en enkelt matrise i starten:
=SUMPRODUCT($C$5:$C$9*(D5:D9="x"))
Matematikkoperasjonen (multiplikasjon) tvinger automatisk de SANNE FALSE-verdiene i det andre uttrykket til en og null, uten behov for dobbelt negativ.
