Загрузите образец книги
В этом руководстве будет показано, как вычислить «суммарное произведение если», возвращая сумму произведений массивов или диапазонов на основе критериев.
Функция СУММПРОИЗВ
Функция СУММПРОИЗВ используется для умножения массивов чисел и суммирования результирующего массива.
Чтобы создать «Sumproduct If», мы будем использовать функцию SUMPRODUCT вместе с функцией IF в формуле массива.
СУММПРОИЗВ, ЕСЛИ
Комбинируя СУММПРОИЗВ и ЕСЛИ в формуле массива, мы можем создать функцию «СУММПРОИЗВ ЕСЛИ», которая работает аналогично встроенной функции СУММЕСЛИ. Давайте рассмотрим пример.
У нас есть список продаж, осуществленных менеджерами в разных регионах с соответствующими ставками комиссионных:
Предположим, нас просят рассчитать размер комиссии для каждого менеджера так:
Для этого мы можем вложить функцию ЕСЛИ с менеджер как наши критерии внутри функции СУММПРОИЗВ:
= СУММПРОИЗВ (ЕСЛИ (=, *))
= СУММПРОИЗВ (ЕСЛИ ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10))
При использовании Excel 2022 и более ранних версий необходимо ввести формулу, нажав CTRL + SHIFT + ВВОД чтобы заключить формулу в фигурные скобки (см. верхнее изображение).
Как работает формула?
Формула работает, оценивая каждую ячейку в нашем диапазоне критериев как ИСТИНА или ЛОЖЬ.
Расчет общей комиссии для Оливии:
= СУММПРОИЗВ (ЕСЛИ ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10))
= СУММПРОИЗВ (ЕСЛИ ({ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}, {928,62; 668,22; 919,695; 447,384; 697,620; 480,564; 689,325; 752,366; 869,61}))
Затем функция IF заменяет каждое значение на FALSE, если его условие не выполняется.
= СУММПРОИЗВ ({928,62; 668,22; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; 480,564; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ})
Теперь функция СУММПРОИЗВ пропускает значения ЛОЖЬ и суммирует оставшиеся значения (2077,40).
СУММПРОИЗВ, ЕСЛИ с несколькими критериями
Чтобы использовать СУММПРОИЗВ ЕСЛИ с несколькими критериями (аналогично тому, как работает встроенная функция СУММЕСЛИМН), просто вложите больше функций ЕСЛИ в функцию СУММПРОИЗВ, например:
= СУММПРОИЗВ (ЕСЛИ (=, ЕСЛИ (=, *))
(CTRL + SHIFT + ВВОД)
= СУММПРОИЗВ (ЕСЛИ ($ B $ 2: $ B $ 10 = $ G2, IF ($ C $ 2: $ C $ 10 = $ H2, $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10)))
(CTRL + SHIFT + ВВОД)
Другой подход к SUMPRODUCT IF
Часто в Excel есть несколько способов добиться желаемых результатов. Другой способ рассчитать «суммарный результат если» - это включить критерии в функция СУММПРОИЗВ как массив с использованием двойного унарного типа:
= СУММПРОИЗВ (- ($ B $ 2: $ B $ 10 = $ G2), - ($ C $ 2: $ C $ 10 = $ H2), $ D $ 2: $ D $ 10 * $ E $ 2: $ E $ 10)
Этот метод использует двойной унарный (-) для преобразования массива ИСТИНА ЛОЖЬ в нули и единицы. 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 Таблицах
Функция СУММПРОИЗВ, ЕСЛИ работает в Google Таблицах точно так же, как и в Excel:
