Как работает формула суммпроизв в excel

Порядок

Функция СУММПРОИЗВ() — Сложение и подсчет с множественными условиями в EXCEL

history 3 апреля 2013 г.
    Группы статей

  • Сложение с несколькими критериями
  • Подсчет с множественными условиями

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

Существует несколько вариантов применения функции СУММПРОИЗВ() :

  • нахождение суммы произведений элементов списка (массива);
  • суммирование и подсчет значений, удовлетворяющих определенным критериям;
  • замена формул массива (в некоторых случаях).

Нахождение суммы произведений элементов массивов

В этом разделе показан синтаксис функции СУММПРОИЗВ() и раскрыт ее потенциал для других применений.

Пусть имеется 2 диапазона чисел A3:A6 и B3:B6 , содержащие соответственно 2 массива чисел : <4:8:6:1>и <7:6:7:5>. Записав формулу =СУММПРОИЗВ(A3:A6;B3:B6) , получим 123. Результат получен поэлементным перемножением всех элементов двух массивов, а затем сложением полученных произведений. То есть были выполнены следующие арифметические действия: 4*7 + 8*6 + 6*7 + 1*5= 123

Таким образом, можно найти сумму произведений 3-х, 4-х и т.д. массивов.

Аргументы, которые являются массивами, должны иметь одинаковые размерности (в нашем случае это массивы по 4 элемента). В противном случае функция СУММПРОИЗВ() возвращает значение ошибки #ЗНАЧ!.

В формуле =СУММПРОИЗВ(A3:A6;B3:B6) функция СУММПРОИЗВ() трактует нечисловые элементы массивов как нулевые. Однако, как показано ниже, функцию можно использовать для подсчета текстовых значений.

Что произойдет если указать только 1 массив, т.е. =СУММПРОИЗВ(A3:A6) ? Тогда функция СУММПРОИЗВ() вернет сумму элементов, т.е. будет эквивалентна функции СУММ() : =СУММ(A3:A6) .

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

  • =СУММПРОИЗВ(A3:A6*2) , получим сумму произведений =38 (каждый элемент массива из A3:A6 был умножен на 2, затем все произведения просуммированы);
  • =СУММПРОИЗВ(A3:A6*B3:B6) , получим результат суммы произведений – 123 (все элементы массивов были попарно перемножены, а затем сложены, т.е. A3*B3+ A4*B4+ A5*B5+ A6*B6), т.е. эта запись эквивалента формула =СУММПРОИЗВ(A3:A6;B3:B6) ;
  • =СУММПРОИЗВ(A3:A6+B3:B6) , получим сумму элементов из двух диапазонов;
  • =СУММПРОИЗВ(A3:A6/B3:B6 ), получим сумму попарных отношений всех элементов, т.е. 4/7 + 8/6 + 6/7 + 1/5= 2,9619

Аналогичные вычисления можно выполнить и с функцией СУММ() , только для этого нужно ее ввести как формулу массива , т.е. после ввода функции в ячейку вместо ENTER нажать CTRL+SHIFT+ENTER : =СУММ(A3:A6/B3:B6)

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

Оказывается, что в качестве аргумента этой функции можно указать не только произведение массивов ( A3:A6*B3:B6 ), но и использовать другие функции и даже применить к массивам операции сравнения, т.е. использовать ее для сложения чисел, удовлетворяющих определенным условиям.

Суммирование и подсчет значений удовлетворяющих определенным критериям

Попробуем подсчитать число значений больших 2 в диапазоне A3:A6 , содержащий значения 4, 8, 6, 1.

Если мы запишем формулу =СУММПРОИЗВ(A3:A6>2) , то получим результат 0. Выделив в Строке формул A3:A6>2 и нажав клавишу F9 , получим массив <ИСТИНА: ИСТИНА: ИСТИНА: ЛОЖЬ>, который говорит, что мы движемся в правильном направлении: в диапазоне A3:A6 больше 2 только первые 3 значения. Хотя значению ИСТИНА соответствует 1, а ЛОЖЬ – 0, мы не получим 3, т.к. для перевода значений ИСТИНА/ЛОЖЬ в числовую форму требуется применить к ним арифметическую операцию. Для этого можно, например, применить операцию двойного отрицания (—), что позволит привести массив в числовую форму <1:1:1:0>.

Итак, задача подсчета значений больше 2 решается следующим образом: =СУММПРОИЗВ(—(A3:A6>2))

Вместо двойного отрицания можно использовать другие формулы: =СУММПРОИЗВ(1*(A3:A6>2)) или =СУММПРОИЗВ(0+(A3:A6>2)) или даже так =СУММПРОИЗВ((A3:A6>2)^1) .

Запись >2 является критерием, причем можно указать любые операции сравнения ( =; =).

Критерии можно указывать в форме ссылки: =СУММПРОИЗВ(—(A3:A6>G8)) – ячейка G 8 должна содержать число 2.

Критерии можно применять и к текстовым значениям, например, =СУММПРОИЗВ(—(B3:B6="яблоки")) – вернет количество ячеек, содержащие слово яблоки (подробнее, например, в статье Подсчет значений с множественными критериями (Часть 1. Условие И)) .

Функцию СУММПРОИЗВ() можно использовать для отбора значений по нескольким критериям (с множественными условиями). Как известно, 2 критерия могут образовывать разные условия:

  • Условие ИЛИ . Например, подсчитать ячеек содержащих значение яблоки ИЛИ груши =СУММПРОИЗВ((B3:B6="яблоки")+ (B3:B6="груши") ) ;
  • Условие И . Например, подсчитать количество значений больше 2 и меньше 5: =СУММПРОИЗВ((A3:A6>2)* (A3:A6 )
  • Условие И . Например, найти сумму Чисел больше 2 и меньше 5: =СУММПРОИЗВ((A3:A6>2)* (A3:A6 A3:A6 ) )

В файле примера приведены решения подобных задач.

СУММПРОИЗВ() – как формула массива

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

  • =СУММПРОИЗВ(—ЕПУСТО(D2:D23)) подсчет пустых ячеек в диапазоне;
  • =СУММПРОИЗВ(НАИБОЛЬШИЙ(A:A;<1;2;3>)) сумма 3-х наибольших значений ;
  • =СУММПРОИЗВ((A3:A6>СРЗНАЧ(A3:A6))*(A3:A6)) сумма значений, которые больше среднего .

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

GeekBrains

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Функция СУММПРОИЗВ — как использовать формулу СУММПРОИЗВ в Excel

В сегодняшней статье мы рассмотрим одну из самых популярных и мощных функций 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 с примерами использования
  • Создание списка уникальных значений в Excel с помощью формул массивов
  • Несколько условий ЕСЛИ в Excel
  • Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel
  • Как расчитать дисперсию в Excel с помощью функции ДИСП.В
  • Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ Excel
  • Функция СУММЕСЛИМН в Excel
  • Изучаем функцию СМЕЩ
  • Многоразовое копирование формулы ВПР

11 комментариев

А вот вопросик такой, второй вариант который вы описали повторяет формулу СУММЕСЛИМН, так вот не сталкивались ли вы какая будет работать быстрее? и где можно подробнее почитать про использование «- -» никогда раньше с этим не сталкивался?

Сергей, однозначно, формула СУММЕСЛИМН будет работать быстрее, так как встроенные формулы шустрее, чем пользовательские. Данный пример будет полезен читателям, которые используют старые версии Excel (до 2007), в которых данная функция не реализована.
По поводу второго вопроса на англоязычных ресурсах об этом много написано, в любом поисковике введите «double dash excel», думаю, вы найдете ответ на свой вопрос.

Сергей, однозначно, формула СУММЕСЛИМН будет работать быстрее, так как встроенные формулы шустрее, чем пользовательские
СУММПРОИЗВ — это пользовательская функция?

Здесь я имею в виду, что функцию СУММПРОИЗ мы затачиваем под функционал СУММЕСЛИМН

Добрый день.
Одно из преимуществ функции СУММПРОИЗ в отличии от СУММЕСЛИ — это работа с закрытым файлом, т.е. СУММПРОИЗ рассчитывает результат, используя данные из другой рабочей книги, даже если она закрыта.
Все было хорошо, пока длина диапазона данных (список1; список2 …) не стала переменной. Решила стандартное написание диапазона заменить на название колонки таблицы, например, (- -(Табл1[Агент]=»Агент Смит»)….), где Табл1 — таблица в другом файле. Пока этот другой файл открыт, все работает. Но при закрытом файле любой Update приводит СУММПРОИЗ к ошибке (Ref). Или я что-то делаю неправильно?

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

Спасибо Вам огромное. Побольше вам времени и позитивных эмоций и дальше вести свой сайт!

Допустим у нас в компании сидят «грамотеи» и пишут слова с ошибками: Агент Смит, Агент Смид, Запад, Запат и т.д. Вопрос:
Как используются подстановочные знаки «*», «?» в функции «СУММПРОИЗВ»?
Когда пишу =СУММПРОИЗВ(- -(A2:A21=»Агент*»);- -(B2:B21=»Запа?»);C2:C21), результат=0
Заранее спасибо!

Доброго времени суток.
При помощи функции суммпроизводства пытаюсь посчитать, столбце с датами. Пишу в условии следующее. =Сегодня()-4
Ошубку не выдает но не считает результат.
А если пишу

Здравствуйте! Подскажите, как задать условие — если значение в ячейке $H$2=1, то ссылаться на столбец MATYEAR и возвращать 1, если MAT 17, если $H$2 равно не один, тогда ссылаться на соседний столбец. Писала условие через ЕСЛИ, но почему-то не работает.

Вопрос по второму примеру. Можно ли узнать через формулу, какие значения были использованы в расчете? Допустим ответ вышел 200 через данные 1*1*198+1*1*2=200
Вопрос как можно по формуле узнать значения какие значения были просуммированы?

Корпорация "Центр"

Ссылка на основную публикацию