Formula generică
=SUMPRODUCT(costs,--(range="x"))
rezumat
Pentru a calcula prețul pachetului de produse folosind un „x” simplu pentru a include sau exclude un produs, puteți utiliza o formulă bazată pe funcția SUMPRODUCT. În exemplul prezentat, formula din D11 este:
=SUMPRODUCT($C$5:$C$9,--(D5:D9="x"))
Explicaţie
Funcția SUMPRODUCT multiplică intervalele sau matricile împreună și returnează suma produselor. Sună plictisitor, dar SUMPRODUCT este o funcție elegantă și versatilă, pe care acest exemplu il ilustrează frumos.
În acest exemplu, SUMPRODUCT este configurat cu două tablouri. Prima matrice este gama care deține prețurile produselor:
$C$5:$C$9
Rețineți că referința este absolută pentru a preveni modificările, deoarece formula este copiată în dreapta. Acest interval se evaluează la următoarea matrice:
(99;69;129;119;49)
A doua matrice este generată cu această expresie:
--(D5:D9="x")
Rezultatul lui D5: D9 = "x" este o matrice de valori ADEVĂRATE FALSE ca aceasta:
(TRUE;TRUE;FALSE;FALSE;FALSE)
Dublul negativ (-) convertește aceste valori ADEVĂRATE FALSE în 1s și 0s:
(1;1;0;0;0)
Deci, în interiorul SUMPRODUCT avem:
=SUMPRODUCT((99;69;129;119;49),(1;1;0;0;0))
Funcția SUMPRODUCT multiplică apoi elementele corespunzătoare din fiecare matrice împreună:
=SUMPRODUCT((99;69;0;0;0))
și returnează suma produselor, 168 în acest caz.
Efectiv, a doua matrice acționează ca un filtru pentru valorile din prima matrice. Zero-urile din matrice2 anulează elementele din matrice1, iar 1 din matrice2 permit valorilor din matrice1 să treacă în rezultatul final.
Cu o singură matrice
SUMPRODUCT este configurat pentru a accepta mai multe tablouri, dar puteți simplifica puțin această formulă oferind o singură matrice la început:
=SUMPRODUCT($C$5:$C$9*(D5:D9="x"))
Operația matematică (înmulțire) constrânge automat valorile TRUE FALSE din a doua expresie la unii și zerouri, fără a fi nevoie de un dublu negativ.
