Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parādīs, kā aprēķināt “sumproduct if”, atgriežot masīvu vai diapazonu produktu summu, pamatojoties uz kritērijiem.
Funkcija SUMPRODUCT
Funkciju SUMPRODUCT izmanto, lai reizinātu skaitļu masīvus, summējot iegūto masīvu.
Lai izveidotu “Sumproduct If”, mēs masīva formulā izmantosim funkciju SUMPRODUCT kopā ar funkciju IF.
SUMPRODUCT IF
Masīva formulā apvienojot SUMPRODUCT un IF, mēs būtībā varam izveidot funkciju “SUMPRODUCT IF”, kas darbojas līdzīgi iebūvētajai SUMIF funkcijai. Apskatīsim piemēru.
Mums ir saraksts ar pārdošanas apjomiem, ko dažādos reģionos ir guvušas silītes, ar atbilstošām komisijas likmēm:
Pieņemot, ka mums tiek prasīts aprēķināt komisijas summu katram vadītājam šādi:
Lai to paveiktu, mēs varam ligzdot IF funkciju ar menedžeris kā mūsu kritēriji SUMPRODUCT funkcijā, piemēram:
= SUMPRODUCT (IF (=,*))
= SUMPRODUCT (JA ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
Ja izmantojat Excel 2022 un vecāku versiju, formula jāievada, nospiežot CTRL + SHIFT + ENTER lai ap formulu ievietotu cirtainās iekavas (sk. augšējo attēlu).
Kā darbojas formula?
Formula darbojas, novērtējot katru mūsu kritēriju diapazona šūnu kā PATIESA vai FALSE.
Aprēķinot kopējo komisijas maksu par Olīviju:
= SUMPRODUCT (JA ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
= SUMPRODUCT (JA ({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}))
Pēc tam IF funkcija aizstāj katru vērtību ar FALSE, ja tās nosacījums nav izpildīts.
= SUMPRODUCT ({928.62; 668.22; FALSE; FALSE; FALSE; 480.564; FALSE; FALSE; FALSE})
Tagad funkcija SUMPRODUCT izlaiž FALSE vērtības un summē atlikušās vērtības (2 077,40).
SUMPRODUCT IF ar vairākiem kritērijiem
Lai izmantotu SUMPRODUCT IF ar vairākiem kritērijiem (līdzīgi kā darbojas iebūvētā SUMIFS funkcija), vienkārši ievietojiet vairāk IF funkciju SUMPRODUCT funkcijā, piemēram:
= SUMPRODUCT (IF (=, IF (=, *))
(CTRL + SHIFT + ENTER)
= SUMPRODUCT (JA ($ B $ 2: $ B $ 10 = $ G2, IF ($ C $ 2: $ C $ 10 = $ H2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)))
(CTRL + SHIFT + ENTER)
Vēl viena pieeja SUMPRODUCT IF
Bieži programmā Excel ir vairāki veidi, kā iegūt vēlamos rezultātus. Cits veids, kā aprēķināt “sumproduct if”, ir kritēriju iekļaušana iekšpusē funkcija SUMPRODUCT kā masīvs, izmantojot dubultu vienotu, piemēram:
= SUMPRODUCT (-($ B $ 2: $ B $ 10 = $ G2),-($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)
Šī metode izmanto dubulto vienotību (-), lai pārveidotu PATIESU FALSE masīvu par nullēm un vienībām. Pēc tam SUMPRODUCT reizina konvertētos kritēriju masīvus kopā:
= 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})
Padomi un triki:
- Ja iespējams, vienmēr bloķējiet atsauces (F4) savus diapazonus un formulas ievades datus, lai varētu automātiski aizpildīt.
- Ja izmantojat Excel 2022 vai jaunāku versiju, varat ievadīt formulu, neizmantojot Ctrl + Shift + Enter.
SUMPRODUCT IF Google izklājlapās
Funkcija SUMPRODUCT IF darbojas Google izklājlapās tāpat kā programmā Excel:
