Sumproduct excel как пользоваться
Функция СУММПРОИВ ВОЗВРАЩАЕТ сумму продуктов соответствующих диапазонов или массивов. По умолчанию операция умножения, но возможна с добавлением, вычитанием и делением.
В этом примере мы используем СУММПРОИВ для возврата общего объема продаж для данного элемента и его размера:
SumPRODUCT соответствует всем экземплярам элемента Y/Size M и суммирует их, поэтому в данном примере «21 плюс 41» равен 62.
Синтаксис
Чтобы использовать операцию по умолчанию (умножение):
=СУММПРОИВ(массив1;[массив2];[массив3];. )
Аргументы функции СУММПРОИЗВ описаны ниже.
Первый массив, компоненты которого нужно перемножить, а затем сложить результаты.
[массив2], [массив3].
От 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить результаты.
Выполнение других арифметических операций
Используйте функцию СУММПРОИВ, как обычно, но вместо запятых, разделяющих аргументы массива, используйте нужные арифметические операторы (*, /, +, -). После выполнения всех операций результаты суммются обычным образом.
Примечание: Если вы используете арифметические операторы, заключите аргументы массива в скобки и используйте скобки для группировки аргументов массива для управления порядком арифметических операций.
Примечания
Аргументы, которые являются массивами, должны иметь одинаковые размерности. В противном случае функция СУММПРОИЗВ возвращает значение ошибки #ЗНАЧ!. Например, =СУММПРОИВ(C2:C10;D2:D5) возвращает ошибку, так как диапазоны не одного размера.
В функции СУММПРОИВТ ненумерические записи массива обрабатывают их так, как если бы они были нулями.
Для лучшей производительности не следует использовать суммпроив с полными ссылками на столбцы. Рассмотрим функцию =СУММПРОИВ(A:A;B:B), чтобы умножить 1 048 576 ячеек в столбце A на 1 048 576 ячеек в столбце B перед их добавлением.
Пример 1
Чтобы создать формулу на примере выше, введите =СУММПРОИВ(C2:C5;D2:D5) и нажмитеввод . Каждая ячейка в столбце C умножается на соответствующую ячейку в той же строке столбца D, и результаты сбавляются. Общая сумма продуктов составляет 78,97 долларов США.
Чтобы ввести более длинную формулу, которая дает такой же результат, введите =C2*D2+C3*D3+C4*D4+C5*D5 и нажмите ввод . После нажатия ввод результат будет таким же: 78,97 долларов США. Ячейка C2 умножается на D2, а ее результат добавляется к результату ячейки C3, умноженной на ячейку D3 и так далее.
Пример 2
В следующем примере sumPRODUCT возвращает суммарные чистую сумму продаж по агенту продаж, у которых есть как общие продажи, так и расходы по агенту. В этом случае мы используем таблицу Excel, в которой используются структурированные ссылки вместо стандартных Excel диапазонов. Здесь вы увидите, что диапазоны «Продажи», «Расходы» и «Агент» имеют ссылку по имени.
Формула: =СУММПРОИМ(((Таблица1[Продажи])+(Таблица1[Расходы]))*(Таблица1[Агент]=B8)) и возвращает сумму всех продаж и расходов агента, указанных в ячейке B8.
Пример 3
В этом примере мы хотим получить общую сумму по конкретному товару, проданного в конкретном регионе. В данном случае, сколько вишней было продается в восточном регионе?
Вот формула: =СУММПРОИВ((B2:B9=B12)*(C2:C9=C12)*D2:D9). Сначала оно умножает количество вхождений восточного на количество совпадающих вишней. Наконец, она суммирует значения соответствующих строк в столбце Продажи. Чтобы узнать, Excel вычисляет формулу, выйдите из ячейки формулы, а затем перейдите в > Вычислить формулу >.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Примеры функции СУММПРОИЗВ в Excel
В MS Excel достаточно много функций, которые упрощают расчеты в документах. Я уже писала статью про примеры использования СУММЕСЛИ и СУММЕСЛИМН. Последняя появилась только в Excel 2007, но в более ранних версиях ее отлично может заменить СУММПРОИЗВ , про которую мы сейчас поговорим. Разберемся, как ее применять на самом простом примере, а потом на более сложных.
Из названия понятно, что СУММПРОИЗВ отвечает за перемножение значений в указанных диапазонах, а потом суммирование полученных чисел. Аргументы достаточно просты – это массивы, которые надо перемножить, затем просуммировать. Их может быть сколько угодно, и разделяются они «;» . Только помните, что диапазоны с данными должны быть одинаковые по длине и все вертикальные, или горизонтальные.
Чтобы стало совсем понятно, возьмем вот такой пример. Предположим нужно посчитать, на какую сумму товара было продано. В таблице указано его наименование, количество проданных единиц и цена за одну штуку. Соответственно, необходимо сначала рассчитать, на какую сумму было продано отдельно табуреток, столов, диванов и остального, а потом сложить все числа в столбце D . Как видите, при этом нам нужно сделать один промежуточный расчет, эти значения занесены в D .
При использовании функции СУММПРОИЗВ нужно просто правильно указать для нее аргументы, и Вы тогда сразу получите результат.
Ставим знак «=» в ячейку D14 , пишем «СУММПРОИЗВ» и в скобках указываем сначала первый массив: В2:В10 , потом второй: С2:С10 .
Нажимайте «Enter» . Мы рассчитали нужное значение без промежуточных результатов и, как видите, два числа совпали.
В А15 я расписала, как считает функция. Она умножает по строкам числа в столбцах В и С , а потом их суммирует.
Теперь давайте немного усложним задачу, и добавим к аргументам функции дополнительные условия. Пример будет такой: завезены фрукты из разных стран, указано их привезенное и реализованное количество и цена покупки и продажи.
Обратите внимание, чтобы функция правильно работала, в диапазонах, которые Вы указываете не должно быть объединенных ячеек. То есть мне нужно повторить грушу в В6 , В7 , В8 .
Ставим равно, пишем СУММПРОИЗВ и указываем аргументы. Сначала будут условия:
(B:B=»Яблоко») – то есть нам нужно из столбца В искать только этот фрукт;
(С:С=»Турция») – чтобы оно было привезено из это страны.
Можете еще добавлять условия. Разделяются они «*» , это что-то вроде «И» . Если Вам не подходит выделение всего столбца полностью, тогда можете указать диапазон, например, (B4:B12=»Яблоко») . Также вместо «Яблоко» можно поставить ссылку на ячейку, в которой находится нужное значение: (B:B=»В4″) , причем ее лучше сделать абсолютной – $В$4 .
Затем ставьте «;» и указывайте столбец, значения из которого нужно суммировать: F:F (или F4:F12 ).
В результате получится, сколько мы за все время заплатили за яблоки привезенные из Турции, но это только за 1 кг.
Полученную формулу можете вставить и в любую другую ячейку, изменить условия и значение также будет подсчитано.
Чтобы получить результат не за килограмм, а за столько, сколько мы завезли, нужно добавить в формулу еще один массив, с завезенным количеством: D:D . Тогда цена закупки умножится на число в столбце завезено, все прибавится и будет результат.
Давайте теперь подсчитаем, сколько мы получили за проданные яблоки по цене их реализации. Для этого в формуле оставляем условия, но меняем массивы на G:G;E:E . Учитывая, что мы не продали весь завезенный товар, доход есть и чистая прибыль: 6120-5100=1020 рублей.
Посчитаем тоже и для завезенной из Украины груши. На ней мы заработали немного больше.
Использовать СУММПРОИЗВ можно для разных расчетов. Есть такая таблица: здесь указано, какой продавец, в каком месяце, что продал и на какую сумму.
Чтобы узнать, сколько получилось с Катиных продаж за январь, нужно написать формулу:
То есть из столбца С выбираем имя продавца, D – месяц, и значения в F суммируем.
Изменяем условия и считаем продажи у остальных продавцов.
В функции СУММПРОИЗВ в условиях можно добавлять еще и сравнение. Добавляется оно к общим условиям через «*» .
Например, рассчитаем, сколько мы получили за яблоки из Турции проданных меньше или равно 10 кг. В аргументы допишем: Е:Е<=10 . Вы можете использовать знаки: <, >, <=, >= .
Используем эту же формулу для груш из Украины и Турции. Если сравнить полученные значения с предыдущими расчетами, то выйдет что яблоки в основном покупали меньше 10 килограмм, а для груш это только половина проданных.
Надеюсь, данные примеры использования функции СУММПРОИЗВ в Excel Вам понятны. Добавляйте к диапазонам значений дополнительные условия и сравнения, чтобы получать производить расчеты.
Функция СУММПРОИЗВ в Excel. Как использовать?
Функция СУММПРОИЗВ (SUMPRODUCT) в Excel используется для перемножения двух или более массивов данных и затем их суммирования.
Что возвращает функция
Возвращает сумму и произведение двух или более массивов данных.
Синтаксис
=SUMPRODUCT(array1, [array2]. [array3], …) – английская версия
=СУММПРОИЗВ(массив1;[массив2];[массив3];…) – русская версия