Изтеглете примерната работна книга
Този урок ще покаже как да се изчисли „sumproduct if“, връщайки сумата от продуктите на масиви или диапазони въз основа на критерии.
SUMPRODUCT функция
Функцията SUMPRODUCT се използва за умножаване на масиви от числа, сумиране на получения масив.
За да създадем „Sumproduct If“, ще използваме функцията SUMPRODUCT заедно с функцията IF във формула на масив.
СУМПРОДУКТ АКО
Комбинирайки SUMPRODUCT и IF във формула на масив, можем по същество да създадем функция „SUMPRODUCT IF“, която работи подобно на начина, по който работи вградената функция SUMIF. Нека преминем през пример.
Имаме списък на продажбите, постигнати от мениджърите в различни региони със съответните комисионни:
Предполага се, че сме помолени да изчислим размера на комисионната за всеки мениджър по следния начин:
За да постигнем това, можем да вмъкнем IF функция с управител като нашите критерии вътре в функцията SUMPRODUCT функционират така:
= СУМПРОДУКТ (АКО (=,*))
= СУМПРОДУКТ (АКО ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))
Когато използвате Excel 2022 и по -ранни версии, трябва да въведете формулата, като натиснете CTRL + SHIFT + ENTER за да завъртите фигурните скоби около формулата (вижте горното изображение).
Как действа формулата?
Формулата работи, като оценява всяка клетка в нашия диапазон от критерии като TRUE или FALSE.
Изчисляване на общата комисионна за Olivia:
= СУМПРОДУКТ (АКО ($ 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}))
След това функцията IF заменя всяка стойност с FALSE, ако нейното условие не е изпълнено.
= SUMPRODUCT ({928.62; 668.22; FALSE; FALSE; FALSE; 480.564; FALSE; FALSE; FALSE})
Сега функцията SUMPRODUCT пропуска стойностите FALSE и сумира останалите стойности (2,077.40).
СУМПРОДУКТ АКО с множество критерии
За да използвате SUMPRODUCT IF с множество критерии (подобно на начина, по който работи вградената функция SUMIFS), просто вложете повече IF функции във функцията SUMPRODUCT така:
= СУМПРОДУКТ (IF (=, IF (=, *))
(CTRL + SHIFT + ENTER)
= СУМПРОДУКТ (АКО ($ B $ 2: $ B $ 10 = $ G2, IF ($ C $ 2: $ C $ 10 = $ H2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)))
(CTRL + SHIFT + ENTER)
Друг подход към SUMPRODUCT IF
Често в Excel има няколко начина да се получат желаните резултати. Различен начин за изчисляване на „суперпродукт, ако“ е да се включат критериите в рамките на функцията SUMPRODUCT като масив, използващ двоен унар по следния начин:
= SUMPRODUCT (-($ B $ 2: $ B $ 10 = $ G2),-($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10)
Този метод използва двойния унар (-), за да преобразува масив TRUE FALSE в нули и единици. След това 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})
Съвети и трикове:
- Когато е възможно, винаги заключвайте (F4) вашите диапазони и входове за формули, за да разрешите автоматично попълване.
- Ако използвате Excel 2022 или по -нова версия, можете да въведете формулата без Ctrl + Shift + Enter.
СУМПРОДУКТ АКО в Google Таблици
Функцията SUMPRODUCT IF работи абсолютно същото в Google Sheets, както и в Excel:
