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

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

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

=СУММПРОИЗВ(Массив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 выполнятся обновременно, то этот элемент войдет в сумму дважды).