Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak vypočítat „souhrnný produkt, pokud“ a vrátí součet součinů polí nebo rozsahů na základě kritérií.
Funkce SUMPRODUCT
Funkce SUMPRODUCT se používá k vynásobení polí čísel a sečtení výsledného pole.
K vytvoření „Sumproduct If“ použijeme ve vzorci pole funkci SUMPRODUCT společně s funkcí IF.
SUMPRODUCT IF
Kombinací SUMPRODUCT a IF ve vzorci pole můžeme v zásadě vytvořit funkci „SUMPRODUCT IF“, která funguje podobně jako vestavěná funkce SUMIF. Pojďme se podívat na příklad.
Máme seznam prodejů dosažených správci v různých regionech s odpovídajícími sazbami provizí:
Předpokládáme, že budeme požádáni o výpočet výše provize pro každého manažera takto:
Abychom toho dosáhli, můžeme vnořit funkci IF pomocí manažer jako naše kritéria uvnitř funkce SUMPRODUCT takto:
= SUMPRODUCT (IF (=,*))
= SUMPRODUCT (IF ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
Pokud používáte Excel 2022 a starší, musíte vzorec zadat stisknutím CTRL + SHIFT + ENTER dostat složené závorky kolem vzorce (viz horní obrázek).
Jak funguje vzorec?
Vzorec funguje tak, že vyhodnotí každou buňku v našem rozsahu kritérií jako PRAVDU nebo NEPRAVDU.
Výpočet celkové provize pro Olivii:
= 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})))
Dále funkce IF nahradí každou hodnotu FALSE, pokud není splněna její podmínka.
= SUMPRODUCT ({928,62; 668,22; FALSE; FALSE; FALSE; 480,564; FALSE; FALSE; FALSE})
Nyní funkce SUMPRODUCT přeskočí FALSE hodnoty a sečte zbývající hodnoty (2 077,40).
SUMPRODUCT IF s více kritérii
Chcete-li používat SUMPRODUCT IF s více kritérii (podobně jako funguje vestavěná funkce SUMIFS), jednoduše vnořte více funkcí IF do funkce SUMPRODUCT 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)
Další přístup k SUMPRODUCT IF
V aplikaci Excel často existuje několik způsobů, jak odvodit požadované výsledky. Jiný způsob výpočtu „souhrnného produktu pokud“ je zahrnutí kritérií v rámci funkce SUMPRODUCT jako pole pomocí dvojité unární takto:
= SUMPRODUCT (-($ B $ 2: $ B $ 10 = $ G2),-($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)
Tato metoda používá double unary (-) k převodu pole TRUE FALSE na nuly a jedničky. SUMPRODUCT pak vynásobí pole převedených 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:
- Pokud je to možné, vždy uzamkněte referenci (F4) svých rozsahů a vstupů vzorců, aby bylo možné automatické vyplňování.
- Pokud používáte Excel 2022 nebo novější, můžete vzorec zadat bez Ctrl + Shift + Enter.
SUMPRODUCT IF v Tabulkách Google
Funkce SUMPRODUCT IF funguje v Tabulkách Google úplně stejně jako v Excelu:
