Волшебство СУММПРОИЗВ или самая полезная формула Excel для экономистов

Функция СУММПРОИЗВ - без преувеличения одна из самых полезных в списке функций Excel. Если бы ее не было, таблицы Excel были бы совсем другим программным продуктом.

Когда я только-только начинал изучать Excel, эта функция показалась мне совершенно бесполезной, потому что получить такой же результат (а именно сумму произведений элементов массивов) я мог и другими способами, тем более, что сначала мне редко приходилось производить какие-то сложные вычисления. Поэтому я надолго забыл про эту ненужную, как мне казалось, функцию.

К счастью, со временем мое отношение к ней поменялось, и сейчас я объясню, почему.

Обратите внимание на следующий рисунок:

Простой пример использования СУММПРОИЗВ

Простой пример использования СУММПРОИЗВ

Столбец D (Объем) - это произведение первых трех столбцов. В ячейке D2 содержится формула =A2*B2*C2, она же скопирована вниз, до ячейки D10. Единственное предназначение столбца D в данном примере - это расчет промежуточных значений, которые потом нужно сложить - это сделано в ячейке D11. Иногда введение столбца с промежуточными значениями оправдано, но иногда нужно всего лишь получить окончательный результат, и мы можем получить его с помощью функции СУММПРОИЗВ. Обратите внимание на формулу в строке формул - она возвращает точно такой же результат.

Исходя из этого примера стоит отметить два момента:

Во-первых, функция СУММПРОИЗВ перемножает между собой значения, находящиеся на одной и той же позиции в разных массивах, а после выполнения всех операций умножения складывает их результаты. В примере массивы были представлены в виде столбцов, но они с равным успехом могли бы быть строками или прямоугольными диапазонами любых размеров. Они также могли бы быть константами массивов (т.е., например, {1;2;3;4;5;6;7}), или именованными диапазонами, или результатом логических операций, а также смесью из любых функций Excel. Единственное требование к диапазонам заключается в том, что они должны быть прямоугольными, непрерывными и одинаковыми по размеру (по каждому измерению).
Во-вторых, в этом простом примере я использовал данные из трех столбцов (массивов). В функции СУММПРОИЗВ можно использовать от одного до 30 массивов, поскольку 30 - это максимальное количество аргументов функции Excel.

Итак, СУММПРОИЗВ предназначена для суммирования произведений одинаково расположенных элементов разных массивов. В чем же ее исключительная полезность? В том, что ее можно использовать для анализа и обработки данных не совсем очевидным образом.

Например, эта формула блистательно совершает вычисления сумм, средних значений, количества элементов и других статистических величин на основе критериев, и для экономиста это, пожалуй, самое главное. Вот пример:

Исходные данные для примера СУММПРОИЗВ

Исходные данные для примера СУММПРОИЗВ

В таблице (и вложенном файле) приведен отчет по продажам вымышленной компании, которая занимается поставкой предметов роскоши, а именно автомобилей, яхт и самолетов. Офисы и клиенты компании расположены по всему миру, поэтому компания ведет учет продаж в разрезе регионов и валют контрактов.

Допустим, нам нужно вычислить общую выручку в рублях от продаж самолетов по всему миру. Для этого нужно перемножить значения столбцов Цена, Количество и Курс в тех строках, в которых Продукт = Самолет. Вот 9 возможных способов сделать это с помощью функции СУММПРОИЗВ:

=СУММПРОИЗВ((Количество*Цена*Курс); --(Продукт="Самолет"))
=СУММПРОИЗВ((Количество*Цена*Курс); 1*(Продукт="Самолет"))
=СУММПРОИЗВ((Количество*Цена*Курс); (Продукт="Самолет")*1)
=СУММПРОИЗВ((Количество*Цена*Курс); (Продукт="Самолет")+0)
=СУММПРОИЗВ((Количество*Цена*Курс); (Продукт="Самолет")^1)
=СУММПРОИЗВ((Количество*Цена*Курс); ЗНАК(Продукт="Самолет"))
=СУММПРОИЗВ((Количество*Цена*Курс); Ч(Продукт="Самолет"))
=СУММПРОИЗВ((Количество*Цена*Курс); (Продукт="Самолет")*ИСТИНА)
=СУММПРОИЗВ((Количество*Цена*Курс)*(Продукт="Самолет"))

Эта формула требует некоторых пояснений.

Прежде всего, я присвоил имена столбцам таблицы, чтобы формулы легче читались и выглядели аккуратней. Вместо имен можно было бы использовать ссылки вида Таблица1[Количество] или даже ссылки на конкретные ячейки, например, C2:C40 вместо "Продукт", на результат это никак не повлияет. Порядок аргументов также не имеет значения.

У первых восьми вариантов есть две общие черты: они используют два аргумента, разделенных точкой с запятой, и каждый из них так или иначе трансформирует результат логической проверки Продукт="Самолет" в числовой эквивалент, то есть в 1 или 0. Если не провести такое преобразование, функция СУММПРОИЗВ вернет 0. Двойной минус - это самый быстрый из предложенных способов. Подробнее о таком преобразовании - в следующей статье.

Последний вариант предпочтительней остальных. Все вычисления происходят в рамках одного аргумента, поэтому можно использовать столько массивов, сколько хочется, преодолев ограничение Excel на 30 аргументов функции. Более того, преобразовывать логические значения в числовые не нужно, потому что Excel делает это автоматически во время операции умножения. Но есть и один минус - этот вариант незначительно медленнее остальных. Это расплата за большую гибкость в построении логических выражений. Точнее говоря, за возможность комбинировать условия с помощью оператора ИЛИ.

Например, если нам потребуется уточнить предыдущий запрос и рассчитать стоимость продаж самолетов только по валютным контрактам, то мы сможем использовать вот такую формулу:

=СУММПРОИЗВ((Количество*Цена*Курс)*(Продукт="Самолет")*((Валюта="EUR")+(Валюта="USD")))

При создании сложных логических проверок нужно помнить, что операция сложения эквивалентна логическому оператору ИЛИ, а операция умножения - оператору И. То есть приведенную выше формулу следует понимать как указание перемножить значения столбцов Количество, Цена и Курс для строк в которых Продукт="Самолет" И Валюта="EUR" ИЛИ "USD".

Такой синтаксис функции СУММПРОИЗВ позволяет усложнять ее до бесконечности, используя любые, сколь угодно сложные логические проверки. А в качестве бонуса этот синтаксис помогает отлавливать ошибки, например, в том случае, если вместо числовых значений в исходных массивах присутствует текст. Синтаксис с несколькими аргументами превращает текст в 0 и в итоге выдает неверный результат вычислений, а синтаксис с одним аргументом возвращает ошибку. Мне кажется, что явное указание на ошибку в данных гораздо приятнее, чем неправильные вычисления.

Продолжим пример. Давайте рассчитаем среднюю стоимость одной яхты в Африке и Австралии в рублях. Общую сумму продаж яхт найдем с помощью формулы

=СУММПРОИЗВ((Количество*Цена*Курс)*(Продукт="Яхта")*((Регион="Африка")+(Регион="Австралия")))

Количество проданных яхт определим формулой:

=СУММПРОИЗВ((Количество)*(Продукт="Яхта")*((Регион="Африка")+(Регион="Австралия")))

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

Скачать файл с примером таблицы - Пример использования функции СУММПРОИЗВ