Sumproduct excel как пользоваться
СУММПРОИЗВ в Excel – любимая функция бухгалтеров, т.к. она чаще всего используется для расчета заработной платы. Хотя она бывает, полезна и во многих других сферах.
По названию можно догадаться, что команда отвечает за суммирование произведений. Произведения при этом считаются либо диапазонами, либо целыми массивами.
Синтаксис СУММПРОИЗВ
Аргументами функции СУММПРОИЗВ являются массивы, т.е. заданные диапазоны. Их может быть сколько угодно. Перечисляя их через точку с запятой, мы задаем количество массивов, которые надо сначала перемножить, а затем просуммировать. Единственное условие: массивы должны быть равными по длине и однотипными (т.е. либо все горизонтальные, либо все вертикальные).
Простейший пример использования функции
Чтобы стало понятно, как и что считает команда, рассмотрим простой пример. Имеем таблицу с указанными длинами и ширинами прямоугольников. Нам нужно сосчитать сумму площадей всех прямоугольников. Если не пользоваться данной функцией, придется произвести промежуточные действия и сосчитать площадь каждого прямоугольника, а только потом сумму. Как мы и сделали.
Обратите внимание, что нам не понадобился массив с промежуточными итогами. В аргументах функции мы использовали только массивы с длиной и шириной, а функция их автоматически перемножила и просуммировала, выдав тот же результат = 70.
СУММПРОИЗВ с условием
Функция СУММПРОИЗВ в естественном виде почти не используется, потому что подсчет суммы произведений может редко пригодиться на производстве. Одно из популярных применений формулы СУММПРОИЗВ – для вывода значений, удовлетворяющих заданным условиям.
Рассмотрим пример. Имеем таблицу затрат небольшой компании за один расчетный месяц. Необходимо сосчитать общую сумму потраченных средств за январь и февраль по всем статьям расходов.
Для расчета затрат по канцелярии в январе-месяце, используем нашу функцию и указываем в начале 2 условия. Каждое из них заключаем в скобки, а между ними ставим знак «звездочка», подразумевающий союз «и». Получаем следующий синтаксис команды:
- (А:А="Январь") – первое условие;
- (Е:Е="канцелярия") – второе условие;
- D:D – массив, из которого выводится итоговая сумма.
В итоге получилось, что в январе на канцелярские принадлежности было затрачено 3700 рублей. Протянем формулу на остальные строки и заменим в каждой из них условия (заменив месяц или статью расходов).
Сравнение в СУММПРОИЗВ
Одним из условий при использовании команды СУММПРОИЗВ может быть сравнение. Рассмотрим сразу на примере. Предположим, что нам нужно сосчитать не просто все расходы по канцелярии за январь, а только те, которые составляли меньше 1000 рублей (назовем их «мелкие расходы»). Прописываем функцию с теми же аргументами, но дополнительно проставляем оператор сравнения. В данном случае он выглядит как D:D
И действительно, это та самая тысяча, которая была потрачена в январе на карандаши. Мы задали дополнительно условие сравнения, и при автоматическом возврате значения суммы функция выдала нам такой ответ.
Протянем формулу на остальные ячейки, частично заменив данные. Видим, сколько денег ушло в январе и феврале на мелкие расходы по каждой статье затрат.
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More. Less
The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays. The default operation is multiplication, but addition, subtraction, and division are also possible.
In this example, we'll use SUMPRODUCT to return the total sales for a given item and size:
SUMPRODUCT matches all instances of Item Y/Size M and sums them, so for this example 21 plus 41 equals 62.
Syntax
To use the default operation (multiplication):
=SUMPRODUCT(array1, [array2], [array3], . )
The SUMPRODUCT function syntax has the following arguments:
The first array argument whose components you want to multiply and then add.
[array2], [array3].
Array arguments 2 to 255 whose components you want to multiply and then add.
To perform other arithmetic operations
Use SUMPRODUCT as usual, but replace the commas separating the array arguments with the arithmetic operators you want (*, /, +, -). After all the operations are performed, the results are summed as usual.
Note: If you use arithmetic operators, consider enclosing your array arguments in parentheses, and using parentheses to group the array arguments to control the order of arithmetic operations.
Remarks
SUMPRODUCT treats non-numeric array entries as if they were zeros.
For best performance, SUMPRODUCT should not be used with full column references. Consider =SUMPRODUCT(A:A,B:B), here the function will multiply the 1,048,576 cells in column A by the 1,048,576 cells in column B before adding them.
Example 1
To create the formula using our sample list above, type =SUMPRODUCT(C2:C5,D2:D5) and press Enter. Each cell in column C is multiplied by its corresponding cell in the same row in column D, and the results are added up. The total amount for the groceries is $78.97.
To write a longer formula that gives you the same result, type =C2*D2+C3*D3+C4*D4+C5*D5 and press Enter. After pressing Enter, the result is the same: $78.97. Cell C2 is multiplied by D2, and its result is added to the result of cell C3 times cell D3 and so on.
Example 2
The following example uses SUMPRODUCT to return the total net sales by sales agent, where we have both total sales and expenses by agent. In this case, we're using an Excel table, which uses structured references instead of standard Excel ranges. Here you'll see that the Sales, Expenses, and Agent ranges are referenced by name.
The formula is: =SUMPRODUCT(((Table1[Sales])+(Table1[Expenses]))*(Table1[Agent]=B8)), and it returns the sum of all sales and expenses for the agent listed in cell B8.
Example 3
In this example, we want to return the total of a particular item sold by a given region. In this case, how many cherries did the East region sell?
Here, the formula is: =SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9). It first multiplies the number of occurrences of East by the number of matching occurrences of cherries. Finally, it sums the values of the corresponding rows in the Sales column. To see how Excel calculates this, select the formula cell, then go to Formulas > Evaluate Formula > Evaluate.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
Самый скучный вариант использования этой замечательной функции - применять ее так, как описано в справке - для суммирования попарных произведений ячеек в двух (и более) указанных диапазонах. Например, можно без дополнительного столбца расчитать общую стоимость заказа:
По сути, то, что делает эта функция можно выразить формулой:
Технически, перемножаемых массивов (диапазонов) может быть не два, а три или больше (до 255). Главное, чтобы они были одного размера. Удобно, но ничего особенно. Однако, использовать СУММПРОИЗВ только так - забивать гвозди микроскопом, ибо, на самом деле, она умеет гораздо больше.
Работа с массивами без Ctrl+Shift+Enter
Если вы хоть немного знакомы в Excel с формулами массива, то должны понимать их мощь и красоту. Иногда одна формула массива может заменить несколько столбцов дополнительных вычислений и ручного труда. Но у формул массива есть и минусы. Главные - это относительная сложность понимания, замедление пересчета книги и необходимость вводить эти формулы сочетанием Ctrl+Shift+Enter вместо обычного Enter. И вот как раз с последним может помочь наша функция СУММПРОИЗВ. Нюанс в том, что она умеет работать с массивами по определению, т.е. не требует обязательного нажатия Ctrl+Shift+Enter при вводе.
На этом факте основано большинство трюков с использованием СУММПРОИЗВ (SUMPRODUCT) . Давайте, для примера, рассмотрим пару-тройку наиболее характерных сценариев.
Подсчет количества выполненных условий
Допустим, нам нужно посчитать количество филиалов компании, где план выполнен (т.е. факт больше или равен плану). Это можно сделать одной формулой с СУММПРОИЗВ без дополнительных столбцов:
Умножение на 1, в данном случае, нужно, чтобы преобразовать результаты сравнения плана и факта - логическую ИСТИНУ и ЛОЖЬ в 1 и 0, соответственно.
Проверка нескольких условий
Если нужно проверять больше одного условия, то формулу из предыдущего примера нужно будет дополнить еще одним (или несколькими) множителями. И если нужно подсчитывать не количество, а сумму, то умножать можно не на 1, а на диапазон с суммируемыми данными:
Фактически, получается что-то весьма похожее на математическую функцию выборочного подсчета СУММЕСЛИМН (SUMIFS) , которая также умеет проверять несколько условий (до 127) и суммировать по ним значения из заданного диапазона.
Логические связки И и ИЛИ (AND и OR)
Если нужно связывать условия не логическим "И", как в примере выше (Факт>=План) И (Регион=Восток) , а логическим ИЛИ, то конструкция немного изменится - знак умножения заменяется на плюс:
Подсчет по данным из закрытого(!) файла
Кроме всего вышеперечисленного, у СУММПРОИЗВ есть еще одно неочевидное и весьма полезное свойство - она умеет работать с данными из неоткрытых книг. Если, для сравнения, попробовать подсчитать в другом файле количество филиалов из региона Восток нашей книги и написать вот такое:
В этом руководстве рассказывается о том, как использовать логику ЕСЛИ с функцией СУММПРОИЗВ в Excel.
Как использовать СУММПРОИЗВ с функцией ЕСЛИ в Excel?
Взгляните на следующий пример. Чтобы суммировать общую стоимость продукта «КТЭ» или «ОТ», вы можете попробовать следующую формулу.
Общие формулы
=SUMPRODUCT(--( array1=”condition”) , [array2] , [array3])
аргументы
[Массив2], [Массив3]: Эти два массива содержат значения, которые вы будете умножать, а затем добавлять на основе соответствующих значений в массиве1.
Как пользоваться этой формулой?
Выберите пустую ячейку рядом с первым условием «KTE» в G5, введите приведенную ниже формулу и нажмите кнопку Enter ключ, чтобы получить общую стоимость «KTE». Выберите эту ячейку результата, а затем перетащите ее маркер автозаполнения вниз, чтобы получить общую стоимость «OT».
=SUMPRODUCT(--($C$5:$C$14=$G5), $D$5:$D$14, $E$5:$E$14)
Примечание: Вы также можете вложить функцию ЕСЛИ внутрь функции СУММПРОИЗВ:
=SUMPRODUCT(IF($C$5:$C$14=$G5,$D$5:$D$14*$E$5:$E$14))
После ввода формулы нажмите кнопку Ctrl + Shift + Enter ключи для подтверждения.
Как работает формула?
=SUMPRODUCT(--($C$5:$C$14=$G5), $D$5:$D$14, $E$5:$E$14)
$C$5:$C$14=$G5: Здесь проверяется, равно ли значение в каждой ячейке диапазона C5:C14 «KTE» (находится в G5). И возвращает такой массив: ;
=SUMPRODUCT(, $D$5:$D$14, $E$5:$E$14): Затем функция СУММПРОИЗВ возвращает сумму произведений соответствующих значений (число 1 в массиве). В итоге получается 1239.
Связанные функции
Функция СУММПРОИЗВ в Excel
Функцию СУММПРОИЗВ в Excel можно использовать для умножения двух или более столбцов или массивов, а затем получения суммы произведений.
Родственные формулы
Суммировать только видимые ячейки или строки в отфильтрованном списке
В этом руководстве демонстрируется формула, основанная на функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ с указанным номером функции, которая поможет вам суммировать только видимые ячейки в отфильтрованном списке в Excel.
Сумма значений в горизонтальном диапазоне в Excel
Чтобы суммировать числа в горизонтальном диапазоне на основе условия, вы можете применить функцию СУММЕСЛИМН. Это пошаговое руководство поможет вам сделать это.
Используйте СУММЕСЛИМН с несколькими критериями на основе логики ИЛИ
По умолчанию функция СУММЕСЛИМН обрабатывает несколько критериев на основе логики И. Если вы хотите суммировать несколько критериев на основе логики ИЛИ, вам нужно использовать функцию СУММЕСЛИМН в константе массива.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 1. Функция СУММЕСЛИ, когда одно условие
Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:
Жмем ОК и вводим ее аргументы:
- Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
- Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
- Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.
Способ 2. Функция СУММЕСЛИМН, когда условий много
Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:
При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться - см. следующие способы.
Способ 3. Столбец-индикатор
Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:
=(A2="Копейка")*(B2="Григорьев")
Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:
Способ 4. Волшебная формула массива
Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:
=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)
После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.
Способ 4. Функция баз данных БДСУММ
В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:
Читайте также: