Как не забивать гвозди микроскопом с функцией СУММПРОИЗВ

Стандартное использование

Базовый синтаксис нашей функции прост:

=СУММПРОИЗВ(Массив1; Массив2; ... )

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

Базовый сценарий применения СУММПРОИЗВ

По сути, то, что делает эта функция можно выразить формулой:

=B2*C2+B3*C3+B4*C4+B5*C5

Технически, перемножаемых массивов (диапазонов) может быть не два, а три или больше (до 255). Главное, чтобы они были одного размера. Удобно, но ничего особенно. Однако, использовать СУММПРОИЗВ только так - забивать гвозди микроскопом, ибо, на самом деле, она умеет гораздо больше.

Работа с массивами без Ctrl+Shift+Enter

Если вы хоть немного знакомы в Excel с формулами массива, то должны понимать их мощь и красоту. Иногда одна формула массива может заменить несколько столбцов дополнительных вычислений и ручного труда. Но у формул массива есть и минусы. Главные - это относительная сложность понимания, замедление пересчета книги и необходимость вводить эти формулы сочетанием Ctrl+Shift+Enter вместо обычного Enter. И вот как раз с последним может помочь наша функция СУММПРОИЗВ. Нюанс в том, что она умеет работать с массивами по определению, т.е. не требует обязательного нажатия Ctrl+Shift+Enter при вводе.

На этом факте основано большинство трюков с использованием СУММПРОИЗВ (SUMPRODUCT). Давайте, для примера, рассмотрим пару-тройку наиболее характерных сценариев.

Подсчет количества выполненных условий

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

Подсчет количества условий

Умножение на 1, в данном случае, нужно, чтобы преобразовать результаты сравнения плана и факта - логическую ИСТИНУ и ЛОЖЬ в 1 и 0, соответственно.

Проверка нескольких условий

Если нужно проверять больше одного условия, то формулу из предыдущего примера нужно будет дополнить еще одним (или несколькими) множителями. И если нужно подсчитывать не количество, а сумму, то умножать можно не на 1, а на диапазон с суммируемыми данными:

СУММПРОИЗВ по нескольким условиям

Фактически, получается что-то весьма похожее на математическую функцию выборочного подсчета СУММЕСЛИМН (SUMIFS), которая также умеет проверять несколько условий (до 127) и суммировать по ним значения из заданного диапазона.

Логические связки И и ИЛИ (AND и OR)

Если нужно связывать условия не логическим "И", как в примере выше (Факт>=План) И (Регион=Восток), а логическим ИЛИ, то конструкция немного изменится - знак умножения заменяется на плюс:

СУММЕСЛИ с условиями ИЛИ

Подсчет по данным из закрытого(!) файла

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

СУММПРОИЗВ из закрытой книги

...то вторая формула с классической функцией СЧЁТЕСЛИМН (COUNTIFS) будет работать только до тех пор, пока исходный файл открыт. Если его закрыть, то появляется ошибка #ЗНАЧ! Наша же функция СУММПРОИЗВ (SUMPRODUCT) спокойно считает по данным даже из неоткрытой книги!

Ссылки по теме



Николай! Спасибо огромное за доходчивые пояснения, как всегда!
27.03.2017 12:55:03
Николай, спасибо!

За идею работы с массивами без Ctrl+Shift+Enter

Совсем забыл про эту фишку…

Протестировал Ваши примеры:

=СУММПРОИЗВ(B2:B5;C2:C5)
{=СУММ(B2:B5*C2:C5)} – прямая альтернатива

***

=СУММПРОИЗВ((C2:C9>=B2:B9)*1)
{=СУММ((C2:C9>=B2:B9)*1)}  – прямая альтернатива
{=СУММ(ЕСЛИ(C2:C9>=B2:B9;1;0))}  -  дополнительная альтернатива

***

=СУММПРОИЗВ((D2:D14>=C2:C14)*(B2:B14="Восток")*D2:D14)
{=СУММ((D2:D14>=C2:C14)*(B2:B14="Восток")*D2:D14)}  – прямая альтернатива
{ =СУММ(ЕСЛИ(И(D2:D14>=C2:C14;B2:B14="Восток");D2:D14;0))}  -  дополнительная альтернатива (не работает?)

***

=СУММПРОИЗВ((B2:B14="Север")+(B2:B14="Восток"))
{=СУММ((B2:B14="Север")+(B2:B14="Восток"))}  – прямая альтернатива
{=СУММ(ЕСЛИ(ИЛИ(B2:B14="Север";B2:B14="Восток");1;0))}  -  дополнительная альтернатива (не работает?)


Интересно почему с условиями И / ИЛИ не работает дополнительная альтернатива?

Но в любом случае получается вариант с СУММПРОИЗВ красивей!!!
30.03.2017 11:18:42
С условиями И/ИЛИ формулы массива не работают "напрямую". Для того, чтобы это применить, Ваши альтернативные формулы
нужно писать так:


{=СУММ(ЕСЛИ(D2:D14>=C2:C14;ЕСЛИ(B2:B14="Восток";D2:D14;0)))}
{=СУММ(ЕСЛИ(B2:B14="Север";1;0))+СУММ(ЕСЛИ(B2:B14="Восток";1;0))}
С СУММПРОИЗВ красивее, согласен.:)
30.03.2017 15:00:44
Круто! Но в этом случае СУММПРОИЗВ гораздо красивее;)
11.04.2017 13:53:49
Добрый день, подскажите пожалуйста, как можно реализовать перемножение массивов одной формулой, в гугл таблицах:
ФОрмат такой, есть данные:
А | 1
Б | 2
В | 3
Д | 4
Нужно получить в третьем столбце:
А1
Б1
В1
Д1
А2
Б2
В2
Д2
А3
Б3
В3
Д3
А4
Б4
В4
Д4
Как возможно склеить 2 массива, и получить перемноженные данные в строку?
14.04.2017 15:53:26
в гугл таблицах?
02.08.2017 12:40:16
=СУММПРОИЗВ((A2:A14="Вологда")+(В2:В14="Центр")) даёт 5, так что это арифметическая сумма условий, а не логическая (если условие1 и условие2 выполнятся обновременно, то этот элемент войдет в сумму дважды).
10.07.2020 14:22:36
Я придумал безумное решение: сумма произведения и неравенства даёт логическое "или"
=СУММПРОИЗВ(((B2:B10="Вологда")*(C2:C10="Центр"))+((B2:B10="Вологда")<>(C2:C10="Центр"))) 
04.05.2021 16:10:15
Добрый день!

Суммпроизв в базовом функционале не работает с диапазоном вертикальным и горизонтальным? т.е. надо получить сумму произведений столбца и строки.
13.09.2021 14:24:30
Спасибо огромное! Очень помогли, как всегда!
06.07.2023 22:32:21
Добрый день!  Прошу помочь в составлении графика контрольных работ (федеральных и школьных (ОО)) с проверкой данных.
Условие: в каждом классе даты федеральных и школьных (ОО) контрольных работ не должны пересекаться, так же даты в столбцах Федеральных и школьных (ОО) контрольных работ не должны повторяться. Другими словами - в каждом классе в текущем месяце в один день может быть ТОЛЬКО ОДНА контрольная работа (федеральная или школьная (ОО)). Как запретить ввод дублей?
пример таблицы

ссылка на скрин таблицы
13.09.2023 15:58:00
Попробуйте запретить ввод дубликатов таким образом (не моё, но я адаптировал):

Выделяем диапазон ячеек   C8:Е18   и жмем кнопку
Проверка данных (Data Validation) на вкладке Данные (Data).
В старых версиях - Excel 2003 и ранее - открываем меню Данные - Проверка  (Data - Validation) .
На вкладке Параметры (Settings) из выпадающего списка Тип данных (Allow) выбираем вариант Другой  (Custom) и вводим следующую формулу в строку Формула (Formula):

=СЧЁТЕСЛИ($C$8:$E$18;E11)<2
Даёт вводить разные числа месяца, но одинаковые нет.

Ввод номера урока пропадёт, чтобы его вернуть, просто протянуть снизу с ячейки D20 вверх, чтобы ввод номера урока опять вернулся бы.
18.07.2023 12:54:36
здраствуйте! подскажите или направьте в нужное русло.
Есть формула для сводного отчета месячного,  отбор происходит  исходя из даты по месяцу и году в умной таблице все работает
=СУММПРОИЗВ( --(МЕСЯЦ(Джетта[ДЕНЬ])=[@мес2]); --(ГОД(Джетта[ДЕНЬ])=[@год]);Джетта[ИТОГО ПРОБЕГ])

еще хочу добавить еще одно условие по температуре (столбец в умной таблице есть)

=СУММПРОИЗВ( --(МЕСЯЦ(Джетта[ДЕНЬ])=[@мес2]); --(ГОД(Джетта[ДЕНЬ])=[@год]);Джетта[темп]<0;Джетта[ИТОГО ПРОБЕГ])
перепробовал много вариантов ,увы
19.07.2023 09:07:19
все разобрался это все они "--" :D
11.09.2023 16:38:22
Приветствую всех!
а как в сумму произведений задать условие больше нуля (или меньше нуля)
если есть столбец "Цена"  (графа "H";) и столбец "Количество" (графа "N";)
При этом количество может быть отрицательной величиной
по аналогии с примерами здесь я написал формулу: =СУММПРОИЗВ((H3:H10)*(N3:N10>0))
Но она не выдаёт правильные значения...
а мне очень нужно эту формулу в макросе вычислять и вывести результат в box
11.09.2023 17:39:24
Всем Спасибо, я сам решил задачку:
=СУММПРОИЗВ(H3:H10;N3:N10;(ЗНАК(N3:N10)=1)*1)
А ларчик просто открывался
Наверх