В сегодняшней статье мы рассмотрим одну из самых популярных и мощных функций 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 – просто возвращает массив с продажами
Если вы перемножите все три массива и затем просуммируете произведения, получиться искомый результат.
Послесловие
Функция СУММПРОИЗВ сможет гораздо больше, когда вы поймете, как она работает. Данная статья только приоткрывает завесу тайны к данной формуле.
А вот вопросик такой, второй вариант который вы описали повторяет формулу СУММЕСЛИМН, так вот не сталкивались ли вы какая будет работать быстрее? и где можно подробнее почитать про использование «- -» никогда раньше с этим не сталкивался?
Сергей, однозначно, формула СУММЕСЛИМН будет работать быстрее, так как встроенные формулы шустрее, чем пользовательские. Данный пример будет полезен читателям, которые используют старые версии Excel (до 2007), в которых данная функция не реализована.
По поводу второго вопроса на англоязычных ресурсах об этом много написано, в любом поисковике введите «double dash excel», думаю, вы найдете ответ на свой вопрос.
Сергей, однозначно, формула СУММЕСЛИМН будет работать быстрее, так как встроенные формулы шустрее, чем пользовательские
СУММПРОИЗВ — это пользовательская функция?
Здесь я имею в виду, что функцию СУММПРОИЗ мы затачиваем под функционал СУММЕСЛИМН
Добрый день.
Одно из преимуществ функции СУММПРОИЗ в отличии от СУММЕСЛИ — это работа с закрытым файлом, т.е. СУММПРОИЗ рассчитывает результат, используя данные из другой рабочей книги, даже если она закрыта.
Все было хорошо, пока длина диапазона данных (список1; список2 …) не стала переменной. Решила стандартное написание диапазона заменить на название колонки таблицы, например, (- -(Табл1[Агент]=»Агент Смит»)….), где Табл1 — таблица в другом файле. Пока этот другой файл открыт, все работает. Но при закрытом файле любой Update приводит СУММПРОИЗ к ошибке (Ref). Или я что-то делаю неправильно?
Добрый день, Лилия
К сожалению, это слабое место Excel. Он не способен вытягивать динамические диапазоны с закрытых книг. Есть предложение производить все вычисления на стороне закрытой книги, а в книге, где это будет использоваться, помещать только ссылку на ячейку закрытой книги
Спасибо Вам огромное!!! Побольше вам времени и позитивных эмоций и дальше вести свой сайт!
Допустим у нас в компании сидят «грамотеи» и пишут слова с ошибками: Агент Смит, Агент Смид, Запад, Запат и т.д. Вопрос:
Как используются подстановочные знаки «*», «?» в функции «СУММПРОИЗВ»?
Когда пишу =СУММПРОИЗВ(- -(A2:A21=»Агент*»);- -(B2:B21=»Запа?»);C2:C21), результат=0
Заранее спасибо!
Доброго времени суток.
При помощи функции суммпроизводства пытаюсь посчитать, столбце с датами. Пишу в условии следующее. =Сегодня()-4
Ошубку не выдает но не считает результат.
А если пишу <=Сегодня()-4 то считает правильно по условию проводит расчет. Как правильно записать условие подскажите пожалуйста?
Здравствуйте! Подскажите, как задать условие — если значение в ячейке $H$2=1, то ссылаться на столбец MATYEAR и возвращать 1, если MAT 17, если $H$2 равно не один, тогда ссылаться на соседний столбец. Писала условие через ЕСЛИ, но почему-то не работает.
=ЕСЛИОШИБКА(СУММПРОИЗВ(—(Вкус=$B10);—(Форма=C$9);—(ЕСЛИ($H$2=1;MATYEAR=»MAT 17″;ЕСЛИ($H$2=2;Season=»summer»;ЕСЛИ($H$2=3;Season=»winter»;ЛОЖЬ))));—(MARKET=(ЕСЛИ($B$2=1;$A$2;ЕСЛИ($B$2=2;$A$3;ЕСЛИ($B$2=3;$A$4;0)))));ЕСЛИ($F$2=1;Value__in_1000_RUR;ЕСЛИ($F$2=2;Volume__in_1000_KG;ЕСЛИ($F$2=3;Units__in_1000_PACKS;ЛОЖЬ))));»»)
Добрый день!
Вопрос по второму примеру. Можно ли узнать через формулу, какие значения были использованы в расчете? Допустим ответ вышел 200 через данные 1*1*198+1*1*2=200
Вопрос как можно по формуле узнать значения какие значения были просуммированы?