Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodys, kaip apskaičiuoti „sumproduct if“, grąžinant masyvų ar diapazonų produktų sumą pagal kriterijus.
SUMPRODUCT funkcija
Funkcija SUMPRODUCT naudojama skaičių masyvams dauginti, susumuojant gautą masyvą.
Norėdami sukurti „Sumproduct If“, masyvo formulėje naudosime funkciją SUMPRODUCT kartu su funkcija IF.
SUMPRODUCT IF
Sujungdami SUMPRODUCT ir IF masyvo formulėje, iš esmės galime sukurti funkciją „SUMPRODUCT IF“, kuri veikia panašiai kaip veikia integruota SUMIF funkcija. Eikime per pavyzdį.
Mes turime pardavimų, kuriuos ėdžiai pasiekė skirtinguose regionuose, sąrašą su atitinkamais komisinių tarifais:
Tarkime, kad mūsų prašoma apskaičiuoti komisinį atlyginimą kiekvienam valdytojui taip:
Norėdami tai padaryti, mes galime įdėti IF funkciją su vadybininkas kaip mūsų kriterijai SUMPRODUCT funkcijos viduje, taip:
= SUMPRODUCT (IF (=,*))
= SUMPRODUCT (IF ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
Kai naudojate „Excel 2022“ ir ankstesnę versiją, turite įvesti formulę paspausdami CTRL + SHIFT + ENTER norėdami gauti garbanotus skliaustus aplink formulę (žr. viršutinį paveikslėlį).
Kaip veikia formulė?
Formulė veikia įvertindama kiekvieną mūsų kriterijų diapazono langelį kaip TRUE arba FALSE.
Apskaičiuojant bendrą Olivijos komisinį atlyginimą:
= SUMPRODUCT (IF ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
= SUMPRODUKTAS (JEI ({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}))
Tada IF funkcija pakeičia kiekvieną reikšmę FALSE, jei jos sąlyga nėra įvykdyta.
= SUMPRODUCT ({928.62; 668.22; FALSE; FALSE; FALSE; 480.564; FALSE; FALSE; FALSE})
Dabar funkcija SUMPRODUCT praleidžia FALSE reikšmes ir sumuoja likusias reikšmes (2 077,40).
SUMPRODUCT IF su keliais kriterijais
Jei norite naudoti SUMPRODUCT IF su keliais kriterijais (panašiai kaip veikia integruota SUMIFS funkcija), tiesiog įdėkite daugiau IF funkcijų į SUMPRODUCT funkciją taip:
= 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)
Kitas požiūris į SUMPRODUCT IF
Dažnai „Excel“ yra keli būdai, kaip pasiekti norimų rezultatų. Kitas būdas apskaičiuoti „sumproduct if“ yra įtraukti kriterijus viduje funkcija SUMPRODUCT kaip masyvas, naudojant dvigubą vienetą, taip:
= SUMPRODUCT (-($ B $ 2: $ B $ 10 = $ G2),-($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)
Šis metodas naudoja dvigubą vienetinį (-), kad konvertuotų TIKRĄ FALSE masyvą į nulius ir vienetus. SUMPRODUCT tada padaugina konvertuotus kriterijų masyvus:
= 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})
Patarimai ir gudrybės:
- Jei įmanoma, visada užfiksuokite (F4) savo diapazonus ir formulės įvestis, kad būtų galima automatiškai užpildyti.
- Jei naudojate „Excel 2022“ ar naujesnę versiją, formulę galite įvesti be „Ctrl“ + „Shift“ + „Enter“.
SUMPRODUCT IF „Google“ skaičiuoklėse
Funkcija SUMPRODUCT IF „Google“ skaičiuoklėse veikia taip pat, kaip ir „Excel“:
