Волшебство СУММПРОИЗВ или самая полезная формула 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 и в итоге выдает неверный результат вычислений, а синтаксис с одним аргументом возвращает ошибку. Мне кажется, что явное указание на ошибку в данных гораздо приятнее, чем неправильные вычисления.

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

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

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

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

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

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

Добавить комментарий

  1. Спасибо, большое за статью.
    А Вы мне можете подсказать как воспользоваться этой формулой, например если я хочу перемножить
    диапазон 1 объем продаж в шт по месяцам
    на диапазон 2 на прайс в зависимости от признака продукции.
    То есть.

    клиент продукция январь февраль Март

    объем продаж ,шт
    Сидоров ручки 10 20 40

    прайс лист, руб/шт
    ручки 5 5 6
    карандаши 4 4 5
    краски 10 12 16

    Планируемая сумма контракта по Сидорову = 390 (не могу сообразить как написать формулу, с учетом того что клиентов много и у каждого своя продукция или несколько. Буду очень признательна за помощь!

    • Ирина, добрый день.
      Придется добавить вспомогательный столбец в таблицу "Объем продаж" и вставить в него вот такую формулу:
      =СУММПРОИЗВ(ОбъемПродаж[@[Январь]:[Март]];СМЕЩ(ПрайсЛист[[#Заголовки];[Январь]:[Март]];ПОИСКПОЗ([@Продукция];ПрайсЛист[Продукция];0);0))
      А затем суммировать значения в этом столбце по клиенту - проще всего с помощью сводной таблицы.

      Подробности и файл с примером расчета.

  2. Подскажите, пожалуйста, - не пойму где ошибка в формуле:
    =СУММПРОИЗВ(Флатовка!$J:$J;--(Флатовка!$AE:$AE=$A22);--(Флатовка!$B:$B=$D$1);--(Флатовка!$A:$A=$C$1);(--(Флатовка!$AD:$AD="71-1")+(--(Флатовка!$AD:$AD="71-2"))+(--(Флатовка!$AD:$AD=5))+(--(Флатовка!$AD:$AD=6))+(--(Флатовка!$AD:$AD=11))))

    Суммирует данные по колонке J на листе Флатовка только при AD="71-1", а мне надо чтоб суммировало при AD="71-1" или AD="71-2" или AD=5 или AD=6 или AD=11.

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

      1. Логика: Формула сейчас суммирует данные в столбце J, удовлетворяющие такому условию:
        $AE:$AE=$A22 И $B:$B=$D$1 И A:$A=$C$1 И
           ($AD:$AD="71-1" ИЛИ $AD:$AD="71-2" ИЛИ $AD:$AD=5 ИЛИ $AD:$AD=6 ИЛИ $AD:$AD=11)
        Во-первых, правильно ли задан адрес ячейки для сравнения в первом условии: $A22? Точно ссылка должна быть абсолютной по столбцу и относительной по строке? Следующие два аргумента используют полностью абсолютные ссылки, может, и первый аргумент должен использовать ссылку на $A$1?
        Во-вторых, действительно ли аргументы должны быть объединены именно этими операторами (И и ИЛИ). Сейчас формула суммирует значения только из тех строк, для которых первые три аргумента и все выражение в скобках истинны. Это соответствует Вашему замыслу?
      2. Данные: А точно ли есть такие данные, для которых выполняется это условие? Попробуйте вручную отфильтровать свою таблицу по этим условиям - вдруг для этой комбинации правил есть данные только со значением 71-1 в столбце AD?
      • Евгений, здравствуйте!
        Прошу Вашей помощи. Написала формулу с СУММПРОИЗВ, о которой Вас спрашивала выше, скопировала на соседние ячейки в столбце (порядка 15 ячеек). И Excel стал выдавать ошибку: "При попытке вычисления одной или нескольких формул ресурсы Excel закончились, поэтому значения этих формул вычислить не удалось"

        Excel 2007. Файл 8694 КБ
        Что можно сделать?

        • Арина, попробуйте в формуле задавать ссылки не на весь столбец (например, $AD:$AD - это ссылка на весь столбец), а только на используемую область (например, $AD$1:$AD$50 - это ссылка на 50 ячеек). При использовании столбца целиком Excel вынужден перемножать миллионы ячеек, из-за такого объема он и выдает ошибку.

          • Спасибо за ответ, буду пробовать.

  3. Евгений, благодарю за ответ.
    Нашла ошибку. Ошибка была в том, что в столбце J некоторые значения с зелёной пометкой в левом верхнем углу ячейки: "Число в этой ячейке отформатировано как текст, или перед ним стоит апостроф".
    Столкнулась с тем, что задаю формат ячеек=Числовой или = Общий, эта зеленая пометка не уходит. Убирается только, если на каждой конкретной ячейки колонки J становиться и указывать: "Преобразовать в число".
    Подскажите, пожалуйста, а возможно ли сразу во всем столбце J сделать "Преобразовать в число"?

    • Арина, проще всего умножить весь столбец на единицу. В любую пустую ячейку листа введите единицу, скопируйте ее в буфер обмена. Затем выделите все значения в нужном столбце и выполните "Специальную вставку" (Ctrl + Alt + V). В открывшемся окне поставьте пометки "Значения" и "Умножить".

    • Анастасия, здравствуйте.

      Скорее всего, название функции написано с ошибкой. Или Вы используете в формуле ссылку на имя, которое не определено в рабочей книге или на листе.

      • Спасибо! Разберусь, наверно. И еще подскажите где поискать или как сделать. Задача такая: работники компании ездят в командировки, иногда один и тот же чел несколько раз, численность большая. в конце месяца есть по-фамильный список всех поездок. Как узнать сколько человек ездило? И можно ли это сделать..

        • Анастасия, Вам нужно подсчитать уникальные значения в списке. В этой статье и в комментариях к ней предложены способы получить уникальные значения, а подсчитать их будет просто (вручную; выделить значения и получить в строке состояния их количество; использовать функцию СЧЁТ())

  4. Добрый день, Евгений.
    А есть ли какая-то разница в использовании функции сумпроизв() и {=сумма()}, .те. сумма с массивом ? По скорости по удобству и т.п.
    Ну, например, ваша функция записанная как:
    {=СУММ((Количество*Цена*Курс)*(Продукт="Яхта")*((Регион="Африка")+(Регион="Австралия")))} - возвращает то же значение, почему вы предпочтете сумпроизв()?

  5. Евгений!
    Тут немного добавил. Поместил на фейсбуке.
    https://www.facebook.com/photo.php?v=1001134703271052
    Посмотрите. Понравится - скачайте. Позже уберу.
    Когда писал нашел еще доработку - добавлю.
    И еще замечание - пишет Алексей. Чем отличается от формулы массива {}.
    Функционал - ничем. Кроме: "Проще, понятней, быстрее" и можно еще найти преимущества.

  6. Мне понравилось элегантное решение из статьи с проверкой условий (+ или; * и). Решил приспособить под свои нужды - вытаскивать уникальное (не числовое) значение из диапазона по нескольким условиям.
    Теперь вместо монстра (где всего-то нужно проверить два условия):
    =ИНДЕКС(
    (О_БТ;О_РГ;О_ПГ;О_НГ;О_ЧТ;О_В1;О_АТ;О_В2;О_ПА;О_Х1;О_Х3;О_К1;О_К2;О_К3;О_А1;О_МГ);ПОИСКПОЗ(D10;ИНДЕКС(
    (О_БТ;О_РГ;О_ПГ;О_НГ;О_ЧТ;О_В1;О_АТ;О_В2;О_ПА;О_Х1;О_Х3;О_К1;О_К2;О_К3;О_А1;О_МГ);;4;P10);0);5;P10)

  7. вот эта прелесть
    {=ИНДЕКС(О;ПОИСКПОЗ(1;(ИНДЕКС(О;;2)=B10)*(ИНДЕКС(О;;4)=D10);0);5)}

    где первая проверка сразу отбрасывает напрочь необходимость заранее определять поддиапазоны (и, соответственно, не требуется условие упорядоченности значений в изначальном диапазоне)

    Файл на 10тыс.строк, открывается за 5 сек (было 19 сек) и закрывается мгновенно (а это странно вроде как).
    Спасибо за наводку! :)

  8. У вас кнопка "Отправить комментарий" (да и вся форма) сдвигается вниз при наборе объёмного текста и прячется за нижние элементы страницы - кнопка исчезает.

  9. Кстати, дополню про СУММПРОИЗВ, если попадающиеся текстовые данные требуется пропускать, без инициации ошибки, то достаточно будет просто разделить рассчитываемый диапазон от диапазонов условий (а их можно и в в свою кучу или каждый раздельно - это уже без разницы).
    И на производительности вообще никак не сказывается - делите хоть каждый диапазон через ";" или все в кучу - одинаково по времени рассчитывается.

  10. Евгений, доброй ночи! После попыток решить задачу самостоятельно, решила, что мне расти ещё и расти:) Обращаюсь с просьбой к гуру:) В строке A1:AH1 находятся числа от 0 до 4. Возможно ли суммирование, с помощью формулы, таким образом, чтобы каждая из ячеек, в коей больше нуля, воспринималась за цифру "8" (рабочий день)..К примеру 1+2+3= 8+8+8? Или только макрос выручит? Спасибо!

  11. Евгений, добрый день.
    Прочитал вашу статью, впечатлился глубоким знанием екселя.
    Прошу помощи гуру.

    Есть 2 массива кодов и цифр + один код, с которым это нужно связать.
    Но сложность в том, что коды расположены в разных строках.

    Вот пример.
    нужно связать например код «абв» с кодом ааа и суммой по строке ааа.
    код 1 код 2 сумма
    абв
    где
    жзи
    ааа 5
    ббб 6
    ввв 7

    Подскажите, пож-та.

    С уважением, Алексей

  12. Спасибо ! Подскажите , пожалуйста, как воспользовавшись формулой найти курсовую разницу по статьям дохода. Есть таблица с указанием статьи дохода, валюта, сумма по месяцам, курс

    Спасибо !

  13. =СУММПРОИЗВ(('M1'!$C$4:$C$1000>=B27)*('M1'!$C$4:$C$1000=B27)*('M2'!$C$4:$C$1000=B27)*('M3'!$C$4:$C$1000=B27)*('M4'!$C$4:$C$1000=B27)*('M5'!$C$4:$C$1000=B27)*('M6'!$C$4:$C$1000=B27)*('M7'!$C$4:$C$1000=B27)*('M8'!$C$4:$C$1000=B27)*('M9'!$C$4:$C$1000=B27)*('M10'!$C$4:$C$1000=B27)*('M11'!$C$4:$C$1000=B27)*('M12'!$C$4:$C$1000<=C27)*('M12'!$K$4:$K$1000="иван");'M12'!$I$4:$I$1000)

    Почему считает только первых 2 листа??