Как сделать формулу суммы в Excel

В сегодняшней статье мы рассмотрим одну из самых популярных и мощных функций 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 — просто возвращает массив с продажами

Если вы перемножите все три массива и затем просуммируете произведения, получиться искомый результат.

работа функции суммпроизв

Послесловие

Функция СУММПРОИЗВ сможет гораздо больше, когда вы поймете, как она работает. Данная статья только приоткрывает завесу тайны к данной формуле.

Вам также могут быть интересны следующие статьи