Prenesite primer delovnega zvezka
Ta vadnica bo pokazala, kako izračunati „sumproduct if“ in vrniti vsoto produktov nizov ali obsegov na podlagi meril.
PODROČJE Funkcija
Funkcija SUMPRODUCT se uporablja za množenje nizov številk in seštevanje nastale matrike.
Če želimo ustvariti »Sumproduct If«, bomo v formuli matrike uporabili funkcijo SUMPRODUCT skupaj s funkcijo IF.
PODROČJE ČE
S kombinacijo SUMPRODUCT in IF v formuli matrike lahko v bistvu ustvarimo funkcijo »SUMPRODUCT IF«, ki deluje podobno, kot deluje vgrajena funkcija SUMIF. Pojdimo skozi primer.
Imamo seznam prodaje, ki so jo upravljavci dosegli v različnih regijah z ustreznimi stopnjami provizije:
Predvidevamo, da za vsakega upravitelja izračunamo znesek provizije:
Da bi to dosegli, lahko funkcijo IF ugnezdimo z upravitelj kot naša merila znotraj funkcije SUMPRODUCT delujejo takole:
= POVEZAVA (ČE (=,*))
= POVEZAVA (ČE ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
Ko uporabljate Excel 2022 in starejše, morate formulo vnesti s pritiskom na CTRL + SHIFT + ENTER , da oklepate oklepane oklepaje (glejte zgornjo sliko).
Kako deluje formula?
Formula deluje tako, da vsako celico v obsegu meril ovrednoti kot TRUE ali FALSE.
Izračun skupne provizije za Olivijo:
= POVEZAVA (ČE ($ 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}))
Nato funkcija IF nadomesti vsako vrednost z FALSE, če njen pogoj ni izpolnjen.
= SUMPRODUCT ({928,62; 668,22; FALSE; FALSE; FALSE; 480,564; FALSE; FALSE; FALSE})
Zdaj funkcija SUMPRODUCT preskoči vrednosti FALSE in povzame preostale vrednosti (2.077,40).
POVEZAVA ČE z več merili
Če želite uporabiti SUMPRODUCT IF z več merili (podobno kot deluje vgrajena funkcija SUMIFS), preprosto vstavite več funkcij IF v funkcijo SUMPRODUCT na naslednji način:
= PODROČJE (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)
Drug pristop k SUMPRODUCT IF
Pogosto v Excelu obstaja več načinov za doseganje želenih rezultatov. Drugačen način izračuna "sumproduct if" je vključitev meril znotraj funkcija SUMPRODUCT kot matrika z uporabo dvojnega unarskega načina:
= SUMPRODUCT (-($ B $ 2: $ B $ 10 = $ G2),-($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)
Ta metoda uporablja dvojno enotno (-) za pretvorbo polja TRUE FALSE v ničle in enote. SUMPRODUCT nato pomnoži pretvorjene matrike meril skupaj:
= PODPROIZVOD ({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})
Namigi in triki:
- Če je mogoče, vedno zaklenite referenco (F4) za svoje obsege in vnose formule, da omogočite samodejno polnjenje.
- Če uporabljate Excel 2022 ali novejši, lahko formulo vnesete brez Ctrl + Shift + Enter.
PODROČJE ČE v Google Preglednicah
Funkcija SUMPRODUCT IF deluje v Google Preglednicah popolnoma enako kot v Excelu:
