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

Использование СУММПРОИЗВ для суммирования произведений соответствующих значений в одном или нескольких массивах

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

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

1. Чтобы создать формулу, введите =СУММПРОИВЕД(B3:B6;C3:C6) и нажмите ввод.

Каждая ячейка в столбце B умножается на соответствующую ячейку в той же строке столбца C, и результаты добавляются. Общая сумма для продуктов составляет 21,60 долларов США.

1. Чтобы создать более длинную формулу, которая дает такой же результат, введите =B3*C3+B4*C4+B5*C5+B6*C6 и нажмите ввод.

После нажатия ввода результат будет таким же: 21,60 долларов США. Ячейка B3 умножается на C3, а ее результат добавляется к результату ячейки B4, умноженной на ячейку C4 и так далее. Представьте, что если у вас тысячи (или даже сотни) строк, то насколько проще было бы использовать суммпроидуц.

• Длина столбцов или строк (массивов) должна быть равной.
• Если текст, а не число, находится в ячейке, он будет обработан как нуль.

Пример

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

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

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

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 или получить поддержку в сообществах.

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

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

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

Синтаксис СУММПРОИЗВ

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

Простейший пример использования функции

Чтобы стало понятно, как и что считает команда, рассмотрим простой пример. Имеем таблицу с указанными длинами и ширинами прямоугольников. Нам нужно сосчитать сумму площадей всех прямоугольников. Если не пользоваться данной функцией, придется произвести промежуточные действия и сосчитать площадь каждого прямоугольника, а только потом сумму. Как мы и сделали.

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

СУММПРОИЗВ с условием

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

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

Для расчета затрат по канцелярии в январе-месяце, используем нашу функцию и указываем в начале 2 условия. Каждое из них заключаем в скобки, а между ними ставим знак «звездочка», подразумевающий союз «и». Получаем следующий синтаксис команды:

• (А:А=»Январь») – первое условие;
• (Е:Е=»канцелярия») – второе условие;
• D:D – массив, из которого выводится итоговая сумма.

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

Сравнение в СУММПРОИЗВ

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

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

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

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

Синтаксис функции СУММПРОИЗВ — следующий:

Первый массив является обязательным. Значения должны быть числовыми, текстовые значения формула посчитает как 0. Массивы 2,3 и так далее являются дополнительными необязательными аргументами и используются в зависимости от постановки задачи. Данная функция по ячейкам или по элементам перемножает несколько массивов и суммирует получившиеся произведения. Самый обычный и простой пример использования функции СУММПРОИЗВ – получить общую стоимость, перемножив цену и количество товаров или услуг. В столбце «В» у нас имеются цены, а в столбце «С» – количество товаров. Нам нужно узнать общую стоимость реализации товаров. В ячейке Е4 пишем формулу:

СУММПРОИЗВ поочередно умножила соответствующие значения из двух массивов (450*3; 340*6 и так далее) и суммировала результаты. Функция СУММПРОИЗВ поддерживает работу с массивами, это означает, что можно использовать логику формул массива, используя СУММПРОИЗВ. Кстати, если помните, для ввода формул массива всегда обязательно следует использовать сочетание клавиш Ctrl+Shift+Enter. В нашем случае надобности в этом нет, поскольку СУММПРОИЗВ работает по логике формул массива по определению. В следующем примере у нас есть таблица, которая содержит информацию о компаниях, регионах их деятельности, плановых продажах и фактических продажах. Наша задача – узнать, сколько компаний выполнили план по реализации товаров или услуг. Эту задачу можно решить и иным способом, например, добавить столбец, в котором сосчитать процент выполнения плана, затем применить СЧЕТЕСЛИ или СЧЕТЕСЛИМН. И это хороший и правильный способ, Однако. можно выполнить задачу проще и быстрее. В ячейке Е23 пишем формулу:

Таким образом, условие D13:D20>=C13:C20 будет по очереди применяться к паре значений по ряду, затем это сравнение будет возвращать логическое значение ложь или истину. Для того чтобы трансформировать ложь и истину в числовое значение, умножаем содержащееся внутри СУММПРОИЗВ условие на 1. Ложь*1=0, истина*1=1. Затем все полученные 1 суммируются:

В предыдущем примере мы использовали одно условие, но при необходимости можно добавлять дополнительные условия. Например, нам нужно найти сколько компаний выполнили план в Северной Европе. Для этого нам следует к имеющийся формуле добавить еще одно условие – диапазон В13:В20, ячейки которого содержат текст «Северная Европа»:

Текст обязательно вставляем в кавычки. Формула нашла компании, которые выполнили план, затем сопоставила найденные результаты с вторым условием, оставила только те, которые соответствуют и возвратила их количество:



Суммирование промежуточных итогов в Excel по условию

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

Теперь наша ИСТИНА будет умножаться на соответствующую величину продаж, а ЛОЖЬ при умножении будет возвращать ноль. Функции, работающие с условиями, сопровождаются логическими операторами И, ИЛИ, НЕ и так далее. Если посмотреть на нашу формулу, два заданных условия связаны между собой логическим оператором И, то есть, выполняется поиск компаний, в которых и выполнен план, и расположение – Северная Европа. Что, если нам нужно связать условия логическим оператором ИЛИ? Допустим, нам нужно просуммировать сумму компаний из регионов Северная и Центральная Европа. Тогда между двумя условиями нужно вместо умножения поставить знак «плюс»:

Выборка данных из диапазона по нескольким условиям в Excel

У нас есть таблица с данными о сотрудниках и их баллах, полученные за тестирование навыков. Нам нужно выбрать среди сотрудников тех, чьи результаты оказались лучше остальных. Первым делом нужно посчитать среднее значение. Можно сделать это через функцию СРЗНАЧ и это будет правильно:

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

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

Теперь заново внимательно смотрим результаты – у нас переопределялся лидер. В прошлом примере Джек занимал первое место, теперь у него самый низкий результат. Мария из второго места ушла на 3, а лидером оказался Питер.

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

Еще один простой, но работающий пример использования:

В этом примере мы сосчитали количество ячеек, содержимое которых превышает 20 символов. Работает эта формула изнутри: ДЛСТР считает сколько всего символов содержит текст в ячейке (возвращаемый результат функции ДЛСТР можно посмотреть по столбцу Е). Затем элементы из массива сопоставляются с условием «больше 20» и приобретают значение ЛОЖЬ или ИСТИНА. После этого функция Ч трансформирует полученные логические результаты в числа 1 и 0. В конце СУММПРОИЗВ подсчитывает количество соответствующих ячеек:

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры