СУММПРОИЗВ (функция СУММПРОИЗВ)
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 или получить поддержку в сообществах.
Sumproduct excel как пользоваться
Вопросы по покупке sales@onlyoffice.com
Запросы на партнерство partners@onlyoffice.com
Запросы от прессы press@onlyoffice.com
Следите за нашими новостями:
© Ascensio System SIA 2023. Все права защищены
© Ascensio System SIA 2023. Все права защищены
Не пропустите обновление!
Получайте последние новости ONLYOFFICE на ваш email
Имя не указано.
Email не указан.
На ваш адрес электронной почты отправлено сообщение с подтверждением.
В Справочном центре ONLYOFFICE используются файлы cookie для обеспечения максимального удобства работы пользователей. Продолжая использовать этот сайт, вы соглашаетесь с тем, что мы можем сохранять файлы cookie в вашем браузере.
Функция СУММПРОИЗВ() — Сложение и подсчет с множественными условиями в EXCEL
Функция СУММПРОИЗВ() , английская версия SUMPRODUCT(), не так проста, как кажется с первого взгляда: помимо собственно нахождения суммы произведений, эта функция может использоваться для подсчета и суммирования значений на основе критериев, а также, в некоторых случаях, избавить от необходимости применений формул массива.
Существует несколько вариантов применения функции СУММПРОИЗВ() :
- нахождение суммы произведений элементов списка (массива);
- суммирование и подсчет значений, удовлетворяющих определенным критериям;
- замена формул массива (в некоторых случаях).
Нахождение суммы произведений элементов массивов
В этом разделе показан синтаксис функции СУММПРОИЗВ() и раскрыт ее потенциал для других применений.
Пусть имеется 2 диапазона чисел A3:A6 и B3:B6 , содержащие соответственно 2 массива чисел : и . Записав формулу =СУММПРОИЗВ(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, т.к. для перевода значений ИСТИНА/ЛОЖЬ в числовую форму требуется применить к ним арифметическую операцию. Для этого можно, например, применить операцию двойного отрицания (—), что позволит привести массив в числовую форму .
Итак, задача подсчета значений больше 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;)) сумма 3-х наибольших значений ;
- =СУММПРОИЗВ((A3:A6>СРЗНАЧ(A3:A6))*(A3:A6)) сумма значений, которые больше среднего .
Совет : Дополнительную информацию об этой функции можно подчерпнуть здесь (английский язык).
Функция СУММПРОИЗВ в Excel. Как использовать?
Функция СУММПРОИЗВ (SUMPRODUCT) в Excel используется для перемножения двух или более массивов данных и затем их суммирования.
Что возвращает функция
Возвращает сумму и произведение двух или более массивов данных.
Больше лайфхаков в нашем Telegram Подписаться
Синтаксис
=SUMPRODUCT(array1, [array2]. [array3], …) — английская версия
=СУММПРОИЗВ(массив1;[массив2];[массив3];…) — русская версия
Аргументы функции
- array1 (массив1) — первый массив данных, который вы хотите перемножить, а затем суммировать;
- [array2] ([массив2]) — (опционально) второй массив данных, который вы хотите перемножить, а затем суммировать. Максимальное количество используемых массивов — 255.
Дополнительная информация
- размер всех массивов должен быть одинаковым;
- все не числовые значения игнорируются и воспринимаются функцией как «0».