Как не забивать гвозди микроскопом с функцией СУММПРОИЗВ
Стандартное использование
Базовый синтаксис нашей функции прост:
=СУММПРОИЗВ(Массив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) спокойно считает по данным даже из неоткрытой книги!
Ссылки по теме
- Что такое формулы массива и как они работают
- Как производить выборочные вычисления по одному или нескольким критериям
За идею работы с массивами без 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))} - дополнительная альтернатива (не работает?)
Интересно почему с условиями И / ИЛИ не работает дополнительная альтернатива?
Но в любом случае получается вариант с СУММПРОИЗВ красивей!!!
нужно писать так:
{=СУММ(ЕСЛИ(D2:D14>=C2:C14;ЕСЛИ(B2:B14="Восток";D2:D14;0)))} {=СУММ(ЕСЛИ(B2:B14="Север";1;0))+СУММ(ЕСЛИ(B2:B14="Восток";1;0))}ФОрмат такой, есть данные:
А | 1
Б | 2
В | 3
Д | 4
Нужно получить в третьем столбце:
А1
Б1
В1
Д1
А2
Б2
В2
Д2
А3
Б3
В3
Д3
А4
Б4
В4
Д4
Как возможно склеить 2 массива, и получить перемноженные данные в строку?
Суммпроизв в базовом функционале не работает с диапазоном вертикальным и горизонтальным? т.е. надо получить сумму произведений столбца и строки.
Условие: в каждом классе даты федеральных и школьных (ОО) контрольных работ не должны пересекаться, так же даты в столбцах Федеральных и школьных (ОО) контрольных работ не должны повторяться. Другими словами - в каждом классе в текущем месяце в один день может быть ТОЛЬКО ОДНА контрольная работа (федеральная или школьная (ОО)). Как запретить ввод дублей?
Выделяем диапазон ячеек C8:Е18 и жмем кнопку
Проверка данных (Data Validation) на вкладке Данные (Data).
В старых версиях - Excel 2003 и ранее - открываем меню Данные - Проверка (Data - Validation) .
На вкладке Параметры (Settings) из выпадающего списка Тип данных (Allow) выбираем вариант Другой (Custom) и вводим следующую формулу в строку Формула (Formula):
=СЧЁТЕСЛИ($C$8:$E$18;E11)<2
Даёт вводить разные числа месяца, но одинаковые нет.
Ввод номера урока пропадёт, чтобы его вернуть, просто протянуть снизу с ячейки D20 вверх, чтобы ввод номера урока опять вернулся бы.
Есть формула для сводного отчета месячного, отбор происходит исходя из даты по месяцу и году в умной таблице все работает
=СУММПРОИЗВ( --(МЕСЯЦ(Джетта[ДЕНЬ])=[@мес2]); --(ГОД(Джетта[ДЕНЬ])=[@год]);Джетта[ИТОГО ПРОБЕГ])
еще хочу добавить еще одно условие по температуре (столбец в умной таблице есть)
=СУММПРОИЗВ( --(МЕСЯЦ(Джетта[ДЕНЬ])=[@мес2]); --(ГОД(Джетта[ДЕНЬ])=[@год]);Джетта[темп]<0;Джетта[ИТОГО ПРОБЕГ])
перепробовал много вариантов ,увы
а как в сумму произведений задать условие больше нуля (или меньше нуля)
если есть столбец "Цена" (графа "H";) и столбец "Количество" (графа "N";)
При этом количество может быть отрицательной величиной
по аналогии с примерами здесь я написал формулу: =СУММПРОИЗВ((H3:H10)*(N3:N10>0))
Но она не выдаёт правильные значения...
а мне очень нужно эту формулу в макросе вычислять и вывести результат в box
=СУММПРОИЗВ(H3:H10;N3:N10;(ЗНАК(N3:N10)=1)*1)
А ларчик просто открывался