Расчет суммы произведений в Excel

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

Функция СУММПРОИЗВ() , английская версия 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)) сумма значений, которые больше среднего .

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

Сумма произведений в Excel

В Microsoft Excel можно проводить разные расчеты, от самых элементарных до более сложных, где задаются определенные условия. Вот и задачу, которую мы с Вами сейчас рассмотрим можно решить по-разному.

В этой статье я покажу, как рассчитать сумму произведений в Excel различными способами: используя привычные «+» и «*» , ссылаясь на определенные ячейки или с помощью функции СУММПРОИЗВ .

Возьмем следующий пример: умножим пары чисел, а затем просуммируем их самым простым способом.

Поставьте в ячейку «=» и начните вписывать значения, расставляя между числами знаки суммы и умножения. Эксель посчитает по законам математики – сначала умножит, а потом прибавит. Чтобы получить результат, жмите «Enter» .

Если у Вас все значения были записаны как и в примере в А1 , то можно не переписывать, достаточно выделить ячейку и поставить в строке формул знак равенства в самом начале.

Еще один вариант – это ссылаться на определенные ячейки, которые берутся для расчета. Преимущества такого способа в том, что если у Вас поменяются значения, например, в заполненной таблице, то сумму произведений не придется исправлять вручную, меняя там числа, все произойдет автоматически.

Для этого поставьте знак равно и выделяйте мышкой ячейки, расставляя между их адресами «+» или «*» . Потом нажмите «Enter» .

Теперь если я изменю в С1 число 24 на 3, результат в D6 будет пересчитан.

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

Примеры СУММПРОИЗВ в Excel, я приводила в отдельной статье, там же и рассказывала, как ей пользоваться.

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

Если у Вас таблица с большим количеством строк, то можно сделать и по-другому. В качестве аргументов для функции указывайте все столбцы полностью: (E:E;G:G) . Теперь перемножаются 10 и 216, 5 и 114, 8 и 96 и так далее.

В аргументах к СУММПРОИЗВ можно задавать определенные условия. Например, чтобы посчитать, сколько мы заработали на яблоках, привезенных именно с Греции, нужна вот такая формула:
=СУММПРОИЗВ((В:В=»Яблоко»)*(С:С=»Греция»);Е:Е;G:G)

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

Поскольку я растянула только на одну ячейку, то и столбцы сместились на это же количество, и теперь в формуле перемножаются F и H . Учитывая, что все строки в столбце H пусты, результат получился нулевой.

Представьте, что нам нужно перемножить и просуммировать E с G и F с G . Поэтому в J4 исправляем аргумент H на G .

Если Вы растянули формулу не на одну, а скажем 10 ячеек, то исправлять не совсем удобно. Поэтому лучше закрепить тот столбец, который используется в расчете постоянно. В примере это G и на него нужно сделать абсолютную ссылку. В первой ячейке с расчетами, у меня это I4 , ставим перед G знак «$» , чтобы получилось $G4:$G10 . Теперь растягивайте. Из примера видно, что в аргументах столбец E сменился на F , а вот G так и остался.

Используйте любой из способов, и у Вас точно получится рассчитать сумму произведений в Эксель.

СУММПРОИЗВ (функция СУММПРОИЗВ)

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

В этом примере мы будем использовать СУММПРОИЗВ, чтобы вернуть общие продажи для определенного элемента и размера:

СУММПРОИЗВ соответствует всем экземплярам элемента Y/size M и суммирует их, поэтому для этого примера 21 плюс 41 равняется 62.

Синтаксис

Чтобы использовать операцию по умолчанию (умножение), выполните указанные ниже действия.

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

Аргументы функции СУММПРОИЗВ описаны ниже.

Первый массив, компоненты которого нужно перемножить, а затем сложить результаты.

От 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить результаты.

Выполнение других арифметических операций

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

Примечание: При использовании арифметических операторов следует заключать аргументы массива в круглые скобки и использовать круглые скобки для группировки аргументов массива для управления порядком арифметических операций.

Примечания

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

СУММПРОИЗВ обрабатывает нечисловые элементы массива так, как если бы они были нулевыми.

Пример 1

Чтобы создать формулу с помощью нашего примера списка выше, введите = СУММПРОИЗВ (C2: C5; D2: D5) и нажмите клавишу Ввод. Каждая ячейка в столбце C умножается на соответствующую ячейку в той же строке в столбце D, и результаты будут добавлены в нее. Общая сумма для сходить составляет $78,97.

Чтобы ввести более длинную формулу, которая приводит к тому же результату, введите = C2 * D2 + C3 * D3 + C4 * D4 + C5 * D5 и нажмите клавишу Ввод. После нажатия клавиши Ввод результат будет таким же: $78,97. Ячейка C2 умножается на D2, и ее результат добавляется в результат ячейки С3 и т. д.

Пример 2

В следующем примере СУММПРОИЗВ используется для возврата общего количества чистых продаж по агенту по продажам, где у нас есть как общие продажи, так и расходы с помощью агента. В этом случае мы используем таблицу Excel, которая использует структурированные ссылки вместо стандартных диапазонов Excel. Здесь вы увидите, что на диапазоны продажи, расходы и агент ссылаются по имени.

Формула: = СУММПРОИЗВ (((Таблица1 [Sales]) + (Таблица1 [продажи])) * (Table1 [агент] = B8)) и возвращает сумму всех продаж и расходов для агента, указанного в ячейке B8.

Пример 3

В этом примере мы хотели бы вернуть сумму определенного элемента, проданного в определенном регионе. В этом случае количество вишний в регионе «Восток» продавалось?

Формула: = СУММПРОИЗВ ((B2: B9 = B12) * (C2: C9 = C12) * D2: D9). Сначала число вхождений Востока умножается на число совпадающих вхождений вишни. Наконец, она суммирует значения соответствующих строк в столбце Sales. Чтобы увидеть, как Excel вычисляет это, выделите ячейку с формулой, а затем перейдите к формулам > Вычислить формулу > оценить.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Расчет суммы произведений в Excel

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

Вычисление суммы произведения

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

Способ 1: использование математической формулы

Большинству пользователей известно, что в Экселе можно выполнить значительное количество математических действий просто поставив знак «=» в пустой ячейке, а далее записав выражение по правилам математики. Этот способ можно использовать и для нахождения суммы произведений. Программа, согласно математическим правилам, сразу подсчитывает произведения, а уже потом складывает их в общую сумму.

  1. Устанавливаем знак «равно» (=) в ячейке, в которой будет выводиться результат производимых вычислений. Записываем туда выражение суммы произведений по следующему шаблону:

Например, таким способом можно рассчитать выражение:

Способ 2: работа со ссылками

Вместо конкретных чисел в этой формуле можно указывать ссылки на ячейки, в которых они расположены. Ссылки можно вписать вручную, но удобнее это делать, выделив после знака «=», «+» или «*» соответствующую ячейку, в которой содержится число.

    Итак, сразу записываем выражение, где вместо чисел указаны ссылки на ячейки.

  • Затем, чтобы произвести подсчет, кликаем по кнопке Enter. Результат вычисления будет выведен на экран.
  • Конечно, данный вид вычисления довольно простой и интуитивно понятный, но в случае, если в таблице очень много значений, которые нужно перемножить, а потом сложить, этот способ может занять очень много времени.

    Способ 3: использование функции СУММПРОИЗВ

    Для того, чтобы подсчитать сумму произведения, некоторые пользователи предпочитают специально предназначенную для этого действия функцию – СУММПРОИЗВ.

    Наименование данного оператора говорит о его предназначении само за себя. Преимущество этого способа перед предыдущим состоит в том, что с его помощью можно обрабатывать сразу целые массивы, а не выполнять действия с каждым числом или ячейкой в отдельности.

    Синтаксис данной функции имеет такой вид:

    Аргументами этого оператора являются диапазоны данных. При этом они сгруппированы по группам множителей. То есть, если отталкиваться от шаблона, о котором мы говорили выше (a1*b1*…+a2*b2*…+a3*b3*…+…), то в первом массиве располагаются множители группы a, во втором – группы b, в третьем – группы c и т.д. Данные диапазоны обязательно должны быть однотипными и равными по длине. Располагаться они могут, как вертикально, так и горизонтально. Всего данный оператор может работать с количеством аргументов от 2 до 255.

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

      Выделяем ячейку на листе, в которой будет выводиться итоговый результат. Жмем на кнопку «Вставить функцию». Она оформлена в виде пиктограммы и располагается слева от поля строки формул.

    После того, как пользователь произвел данные действия, запускается Мастер функций. В нем открывается список всех, за малым исключением, операторов, с которыми можно работать в Экселе. Чтобы найти нужную нам функцию, переходим в категорию «Математические» или «Полный алфавитный перечень». После того, как нашли наименование «СУММПРОИЗВ», выделяем его и жмем на кнопку «OK».

    Запускается окно аргументов функции СУММПРОИЗВ. По количеству аргументов оно может иметь от 2 до 255 полей. Адреса диапазонов можно вбивать вручную. Но это займет значительное количество времени. Можно сделать несколько по-другому. Устанавливаем курсор в первое поле и выделяем с зажатой левой кнопкой мыши массив первого аргумента на листе. Таким же образом поступаем со вторым и со всеми последующими диапазонами, координаты которых сразу же отображаются в соответствующем поле. После того, как все данные введены, жмем на кнопку «OK» в нижней части окна.

  • После этих действий программа самостоятельно производит все требуемые вычисления и выводит итоговый результат в ту ячейку, которая была выделена в первом пункте данной инструкции.
  • Способ 4: применение функции по условию

    Функция СУММПРОИЗВ хороша и тем, что её можно применять по условию. Разберем, как это делается на конкретном примере.

    У нас есть таблица окладов и отработанных дней работников предприятия за три месяца помесячно. Нам нужно узнать, какую сумму на протяжении этого периода заработал сотрудник Парфенов Д.Ф.

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

    После того, как все данные внесены, жмем кнопку «OK».

    Приложение производит вычисление. В расчет берутся только строчки, в которых присутствует имя «Парфенов Д.Ф», то есть то, что нам и нужно. Результат вычислений отображается в предварительно выделенной ячейке. Но результат равен нулю. Это связано с тем, что формула в том виде, в котором она существует сейчас, работает некорректно. Нам нужно её немного преобразовать.

  • Для того чтобы преобразовать формулу, выделяем ячейку с итоговым значением. Выполняем действия в строке формул. Аргумент с условием берем в скобки, а между ним и другими аргументами точку с запятой меняем на знак умножения (*). Жмем на кнопку Enter. Программа производит подсчет и на этот раз выдает уже корректное значение. Мы получили общую сумму заработной платы за три месяца, которая причитается сотруднику предприятия Парфенову Д. Ф.
  • Таким же образом можно применять условия не только к тексту, но и к числам с датами, добавляя знаки условия « », «=», « ».

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

    Функция СУММПРОИЗВ для вычисления произведения суммы в Excel

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

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

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

    Например, таблица состоит из трех полей данных с названиями «Товар», «Продавец», «Сумма покупки». Чтобы определить общую средств, полученных на продаже товара товар_1 продавцом Продавец_1 можно использовать следующую формулу массива: =СУММПРОИЗВ(—(A1:A100=”товар_1”);—(B1:B100=”Продавец_1”);C1:C100), где A1:A100 – диапазон ячеек с названием товаров, B1:B100 – диапазон ячеек, в которых хранятся фамилии продавцов, C1:C100 – диапазон ячеек с числовыми значениями стоимости проданных товаров. Символы «—» — двойное отрицание, используемое для прямого преобразования логических ИСТИНА и ЛОЖЬ к числовым 1 и 0 соответственно. Подробнее эта формула будет рассмотрена в одном из примеров.

    Расчет вероятности используя вычисление произведение суммы в Excel

    Пример 1. На склад поступили новые товары от 5 различных производителей по 20% от каждого, при этом 25% товаров первого поставщика – высшего качества, второго – 18%, третьего – 27%, четвертого – 55%, пятого – 5% соответственно. Определить вероятность того, что случайно выбранный товар окажется высшего качества.

    Вид таблицы данных:

    Для определения вероятности выбора товара высокого качества необходимо найти сумму произведений вероятностей, которые записаны в ячейки столбцов B:B и C:C соответственно. Для этого используем формулу:

    • B2:B6 – первый диапазон ячеек с вероятностями выбора товаров 1-го, 2-го и т. д. производителей, значения которых будут умножены на соответствующие значения из второго диапазона;
    • C2:C6 – второй диапазон ячеек с вероятностями выбора товара высшего сорта среди товаров 1-го, 2-го и т. д. производителей соответственно.

    В результате вычислений получаем следующее значение вероятности:

    Как вычислить произведение суммы числ по условию в Excel?

    Пример 2. В таблице хранятся данные о поставщиках, видах товаров и суммарной стоимости. Определить общую сумму закупок хлеба от первого поставщика без использования функции СУММЕСЛИМН.

    Вид таблицы данных:

    Для расчета используем формулу:

    В качестве первого и второго аргументов функции переданы логически выражения, проверяющие на соответствие указанным значениям («хлеб», «поставщик_1») диапазоны ячеек A2:A17 и B2:B17 соответственно. В результате выполнения этих выражений будут получены массивы логических значений ИСТИНА (если совпадение) и ЛОЖЬ, которые будут преобразованы в массивы числовых значений 1 или 0 благодаря использованию двойного отрицания «—». C2:C17 – диапазон ячеек со значениями стоимости. В результате перемножения элементов и сложения произведений получим следующую суммарную закупочную стоимость хлеба от первого поставщика:

    Расчет количества отклонений с помощью функции СУММПРОИЗВ в Excel

    Пример 3. В цехе по производству деталей работают несколько работников определенного разряда, для каждого из которых установлена норма выработки за месяц. Определить число сотрудников третьего и выше разряда, не выполнивших свою месячную норму.

    Вид таблицы данных:

    Для расчета используем следующую формулу:

    Первый аргумент указан в виде логического выражения для сравнения значений в диапазонах, содержащих фактическую и требуемую продуктивности для каждого работника. Функция НЕ используется для возврата обратного значения. Второй аргумент указан в виде выражения для проверки разряда каждого сотрудника. Умножение на единицу выполняется для преобразования логических данных к числам.

    В результате вычислений получим следующее число не выполнивших норму сотрудников:

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

    Функция имеет следующий синтаксис:

    =СУММПРОИЗВ( массив1; [массив2];[массив3];…)

    • массив1 – обязательный аргумент, принимающий константу массива или диапазон ячеек с числовыми значениями, каждый элемент которого будет перемножен с соответствующим элементом (элементами) второго и последующих массивов или диапазонов, переданных в качестве последующих аргументов данной функции;
    • [массив2];[массив3];… — второй и последующие необязательные аргументы функции, принимающие константу массива или ссылку на диапазон ячеек, элементы которых будут перемножены с соответствующими элементами массивов или диапазонов (которые указаны в качестве предыдущих аргументов), а затем будет вычислена сумма произведений.
    1. Максимальное количество аргументов, принимаемых функцией СУММПРОИЗВ, составляет 255 диапазонов (или констант массивов).
    2. Если рассматриваемая функция принимает только один аргумент массив1, считается, что второй аргумент является массивом единиц, размерность которого соответствует размерности массив1. То есть, функции СУММ(A1:A10) и СУММПРОИЗВ(A1:10) вернут одинаковый результат.
    3. Если в качестве аргументов функции были переданы 2 и более константы массивов, они должны иметь одинаковую размерность (равно число элементов). Если в качестве аргументов СУММПРОИЗВ были переданы ссылки на 2 и более диапазона ячеек, эти диапазоны должны содержать одинаковое количество ячеек. При несоблюдении указанных условий функция СУМППРОИЗВ вернет код ошибки #ЗНАЧ!.
    4. Если в качестве аргументов функций были переданы константы массивов, содержащие нечисловые значения или ссылки на диапазоны ячеек с логическими ИСТИНА или ЛОЖЬ, не преобразуемые к числам строки или имена, функция СУММПРОИЗВ будет преобразовывать их числовому значению 0 (нуль). Например, если в одном столбце (A:A) содержатся названия товаров, а во втором (B:B) – их стоимость, функция =СУММПРОИЗВ(A1:A10;B1:B10) вернет значение 0, поскольку каждое значение в столбце A:A будет интерпретировано как число 0.

    Сумма произведений в Excel

    В Microsoft Excel можно проводить разные расчеты, от самых элементарных до более сложных, где задаются определенные условия. Вот и задачу, которую мы с Вами сейчас рассмотрим можно решить по-разному.

    В этой статье я покажу, как рассчитать сумму произведений в Excel различными способами: используя привычные «+» и «*», ссылаясь на определенные ячейки или с помощью функции СУММПРОИЗВ.

    Возьмем следующий пример: умножим пары чисел, а затем просуммируем их самым простым способом.

    Поставьте в ячейку «=» и начните вписывать значения, расставляя между числами знаки суммы и умножения. Эксель посчитает по законам математики – сначала умножит, а потом прибавит. Чтобы получить результат, жмите «Enter».

    Если у Вас все значения были записаны как и в примере в А1, то можно не переписывать, достаточно выделить ячейку и поставить в строке формул знак равенства в самом начале.

    Еще один вариант – это ссылаться на определенные ячейки, которые берутся для расчета. Преимущества такого способа в том, что если у Вас поменяются значения, например, в заполненной таблице, то сумму произведений не придется исправлять вручную, меняя там числа, все произойдет автоматически.

    Для этого поставьте знак равно и выделяйте мышкой ячейки, расставляя между их адресами «+» или «*». Потом нажмите «Enter».

    Теперь если я изменю в С1 число 24 на 3, результат в D6 будет пересчитан.

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

    Примеры СУММПРОИЗВ в Excel, я приводила в отдельной статье, там же и рассказывала, как ей пользоваться.

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

    Если у Вас таблица с большим количеством строк, то можно сделать и по-другому. В качестве аргументов для функции указывайте все столбцы полностью: (E:E;G:G). Теперь перемножаются 10 и 216, 5 и 114, 8 и 96 и так далее.

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

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

    Поскольку я растянула только на одну ячейку, то и столбцы сместились на это же количество, и теперь в формуле перемножаются F и H. Учитывая, что все строки в столбце H пусты, результат получился нулевой.

    Представьте, что нам нужно перемножить и просуммировать E с G и F с G. Поэтому в J4 исправляем аргумент H на G.

    Если Вы растянули формулу не на одну, а скажем 10 ячеек, то исправлять не совсем удобно. Поэтому лучше закрепить тот столбец, который используется в расчете постоянно. В примере это G и на него нужно сделать абсолютную ссылку. В первой ячейке с расчетами, у меня это I4, ставим перед G знак «$», чтобы получилось $G4:$G10. Теперь растягивайте. Из примера видно, что в аргументах столбец E сменился на F, а вот G так и остался.

    Используйте любой из способов, и у Вас точно получится рассчитать сумму произведений в Эксель.