Stiahnite si ukážkový zošit
Tento tutoriál predvedie, ako vypočítať „súčet súčinov if“, pričom vráti súčet súčinov polí alebo rozsahov na základe kritérií.
Funkcia SUMPRODUCT
Funkcia SUMPRODUCT sa používa na znásobenie polí čísel a súčet výsledných polí.
Na vytvorenie „Sumproduct If“ použijeme vo vzorci poľa funkciu SUMPRODUCT spolu s funkciou IF.
SUMPRODUCT IF
Kombináciou SUMPRODUCT a IF vo vzorci poľa môžeme v podstate vytvoriť funkciu „SUMPRODUCT IF“, ktorá funguje podobne ako vstavaná funkcia SUMIF. Poďme sa pozrieť na príklad.
Máme zoznam tržieb dosiahnutých správcami v rôznych regiónoch so zodpovedajúcimi sadzbami provízií:
Predpokladá sa, že budeme požiadaní vypočítať výšku provízie pre každého manažéra takto:
Aby sme to dosiahli, môžeme vnoriť funkciu IF pomocou manažér ako naše kritériá vo funkcii SUMPRODUCT fungujú takto:
= SUMPRODUCT (IF (=,*))
= SUMPRODUCT (IF ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
Keď používate Excel 2022 a starší, musíte vzorec zadať stlačením CTRL + SHIFT + ENTER aby ste okolo vzorca dostali kučeravé zátvorky (pozri horný obrázok).
Ako funguje vzorec?
Vzorec funguje tak, že vyhodnotí každú bunku v našom rozsahu kritérií ako PRAVDU alebo NEPRAVDU.
Výpočet celkovej provízie pre Oliviu:
= 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})))
Ďalej funkcia IF nahradí každú hodnotu hodnotou FALSE, ak nie je splnená jej podmienka.
= SUMPRODUCT ({928,62; 668,22; FALSE; FALSE; FALSE; 480,564; FALSE; FALSE; FALSE})
Teraz funkcia SUMPRODUCT preskočí FALSE hodnoty a sčíta zostávajúce hodnoty (2 077,40).
SUMPRODUCT IF s viacerými kritériami
Ak chcete používať SUMPRODUCT IF s viacerými kritériami (podobne ako funguje vstavaná funkcia SUMIFS), jednoducho vnorte do funkcie SUMPRODUCT viac funkcií IF takto:
= 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)
Ďalší prístup k SUMPRODUCT IF
V programe Excel je často niekoľko spôsobov, ako dosiahnuť požadované výsledky. Odlišný spôsob výpočtu „súčtového produktu ak“ je zahrnutie kritérií vo vnútri funkcia SUMPRODUCT ako pole používajúce dvojité unary takto:
= SUMPRODUCT (-($ B $ 2: $ B $ 10 = $ G2),-($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)
Táto metóda používa double unary (-) na konverziu poľa TRUE FALSE na nuly a jednotky. SUMPRODUCT potom vynásobí prevedené polia kritérií dohromady:
= 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})
Tipy a triky:
- Ak je to možné, vždy uzamknite referenciu (F4) svojich rozsahov a vstupov vzorcov, aby bolo možné automatické vypĺňanie.
- Ak používate Excel 2022 alebo novší, vzorec môžete zadať bez Ctrl + Shift + Enter.
SUMPRODUCT IF v Tabuľkách Google
Funkcia SUMPRODUCT IF funguje v Tabuľkách Google úplne rovnako ako v programe Excel: