Descărcați exemplul de registru de lucru
Acest tutorial va demonstra cum se calculează „sumproduct if”, returnând suma produselor matricilor sau gamelor pe baza criteriilor.
Funcția SUMPRODUCT
Funcția SUMPRODUCT este utilizată pentru a multiplica matricele de numere, însumând matricea rezultată.
Pentru a crea un „Sumproduct If”, vom folosi funcția SUMPRODUCT împreună cu funcția IF într-o formulă matrice.
SUMPRODUCT IF
Combinând SUMPRODUCT și IF într-o formulă matrice, putem crea în esență o funcție „SUMPRODUCT IF” care funcționează similar cu modul în care funcționează funcția SUMIF încorporată. Să parcurgem un exemplu.
Avem o listă a vânzărilor realizate de iesle în diferite regiuni cu ratele de comision corespunzătoare:
Se presupune că ni se cere să calculăm valoarea comisionului pentru fiecare manager, astfel:
Pentru a realiza acest lucru, putem cuibări o funcție IF cu administrator ca și criteriile noastre din cadrul funcției SUMPRODUCT:
= SUMPRODUCT (IF (=, *))
= SUMPRODUCT (IF ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10))
Când utilizați Excel 2022 și versiunile anterioare, trebuie să introduceți formula apăsând CTRL + SHIFT + ENTER pentru a obține parantezele cretate în jurul formulei (vezi imaginea de sus).
Cum funcționează formula?
Formula funcționează evaluând fiecare celulă din intervalul nostru de criterii ca fiind ADEVĂRAT sau FALS.
Calculând comisionul total pentru Olivia:
= SUMPRODUCT (IF ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10))
= SUMPRODUCT (IF ({TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, {928.62; 668.22; 919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61}))
În continuare, funcția IF înlocuiește fiecare valoare cu FALS dacă condiția sa nu este îndeplinită.
= SUMPRODUCT ({928.62; 668.22; FALSE; FALSE; FALSE; 480.564; FALSE; FALSE; FALSE})
Acum, funcția SUMPRODUCT omite valorile FALSE și însumează valorile rămase (2.077,40).
SUMPRODUCT IF cu criterii multiple
Pentru a utiliza SUMPRODUCT IF cu mai multe criterii (similar cu modul în care funcționează funcția SUMIFS încorporată), pur și simplu cuibăriți mai multe funcții IF în funcția SUMPRODUCT astfel:
= SUMPRODUCT (IF (=, IF (=, *))
(CTRL + SHIFT + ENTER)
= SUMPRODUCT (IF ($ B $ 2: $ B $ 10 = G2 $, IF ($ C $ 2: $ C $ 10 = $ H2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10)))
(CTRL + SHIFT + ENTER)
O altă abordare a SUMPRODUCT IF
Adesea în Excel, există mai multe moduri de a obține rezultatele dorite. O modalitate diferită de a calcula „sumproduct if” este de a include criteriile în funcția SUMPRODUCT ca o matrice utilizând unar dublu ca așa:
= SUMPRODUCT (- ($ B $ 2: $ B $ 10 = $ G2), - ($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10)
Această metodă utilizează unarul dublu (-) pentru a converti o matrice TRUE FALSE în zerouri și unii. SUMPRODUCT multiplică apoi matricile de criterii convertite împreună:
= SUMPRODUCT ({1; 1; 0; 0; 0; 1; 0; 0; 0}, {1; 0; 1; 0; 1; 0; 0; 0; 0}, {928.62; 668.22; 919.695; 447.384; 697.620; 480.564; 689.325; 752.366; 869.61})
Sfaturi și trucuri:
- Unde este posibil, blocați întotdeauna referința (F4) intervalelor și intrărilor de formulă pentru a permite umplerea automată.
- Dacă utilizați Excel 2022 sau mai nou, puteți introduce formula fără Ctrl + Shift + Enter.
SUMPRODUCT IF în Foi de calcul Google
Funcția SUMPRODUCT IF funcționează exact la fel în Foi de calcul Google ca în Excel:
