Завантажте зразок робочої книги
У цьому посібнику буде показано, як обчислити “супровід, якщо”, повертаючи суму добутків масивів або діапазонів на основі критеріїв.
Функція 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.
Розрахунок загальної комісії за Олівію:
= ПІДПРИЄМСТВО (ЯКЩО ($ 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 і підсумовує інші значення (2077,40).
ПОВІДОМЛЕННЯ ІФ за кількома критеріями
Щоб використовувати SUMPRODUCT IF з кількома критеріями (подібно до того, як працює вбудована функція SUMIFS), просто вкладіть більше функцій IF у функцію SUMPRODUCT так:
= ПІДВИСОК (IF (=, IF (=, *))
(CTRL + SHIFT + ENTER)
= ПІДПРИЄМСТВО (IF ($ 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 Таблицях, як і в Excel:
