В сегодняшней статье мы рассмотрим одну из самых популярных и мощных функций Excel — СУММПРОИЗВ. Изначально функция может показаться вам не такой полезной, как многие описывают. Но стоит начать работать со списками или массивами данных, польза СУММПРОИЗВ становится очевидной.
СУММПРОИЗВ — синтаксис формулы
Синтаксис формулы Excel суммы произведений довольно прост. Функция берет в качестве аргументов один или более массивов данных и возвращает сумму произведений соответствующих значений.
Синтаксис функции выглядит следующим образом: =СУММПРОИЗВ(список1; список2 …)
То есть, если у вас есть массив данных {2;3;4} в одной колонке и {5;10;20} — в другой, и вы воспользовались функцией СУММПРОИЗВ, вы получите результат 120 (потому что 2*5+3*10+4*20=120).
СУММПРОИЗВ и массивы
Давайте предположим, что у вас имеется таблица продаж с колонками Имя продавца, Регион и Сумма продаж. И вам необходимо узнать, на какую сумму сделал продаж тот или иной агент. Это просто, можно воспользоваться функцией СУММЕСЛИ и указать в качестве критерия суммирования, необходимое нам имя агента.
Вопрос довольно просто решаем с одним условием, но что если количество условий возрастает и нам необходимо узнать сумму продаж конкретного агента в конкретном регионе. У нас есть три пути:
- Воспользоваться формулами массивов
- Воспользоваться сводными таблицами
- Воспользоваться формулой СУММЕСЛИМН
На самом деле у нас есть еще один, скрытый путь, воспользоваться СУММПРОИЗВ.
Использование СУММПРОИЗВ в формулах массива
Если предположить, что данные находятся в диапазоне A2:C21, с именами агентов в колонке A, регионами — в колонке B и продажами — в колонке C, то формула СУММПРОИЗВ будет выглядеть следующим образом:
=СУММПРОИЗВ(- -(A2:A21=”Агент Смит”);- -(B2:B21=”Запад”);C2:C21)
Давайте разберемся, как работает формула:
- Часть формулы (- -(A2:A21=”Агент Смит” ищет Агента Смита в диапазоне A2:A21 и возвращает массив с единицами и нулями (единица, если ячейка содержит Агента Смита, и ноль — если нет).
- Часть — -(B2:B21=”Запад”) делает тоже самое, только возвращает единицу, если ячейка содержит Запад.
- C2:C21 — просто возвращает массив с продажами
Если вы перемножите все три массива и затем просуммируете произведения, получиться искомый результат.
Послесловие
Функция СУММПРОИЗВ сможет гораздо больше, когда вы поймете, как она работает. Данная статья только приоткрывает завесу тайны к данной формуле.
