Функция SUMPRODUCT Excel

В Excel функция СУММПРОИЗВ позволяет перемножать два или более столбцов или массивов, а затем суммировать полученные произведения. На самом деле СУММПРОИЗВ — чрезвычайно полезная функция, способная подсчитывать или суммировать значения ячеек по нескольким критериям, подобно функциям СЧЁТЕСЛИМН и СУММЕСЛИМН. В этой статье приведены синтаксис функции и примеры её применения.

• Пример 1: базовое использование функции СУММПРОИЗВ
• Пример 2: суммирование ячеек по нескольким критериям с помощью функции СУММПРОИЗВ
• Пример 3: подсчёт ячеек по нескольким критериям с помощью функции СУММПРОИЗВ
• Пример 4: расчёт средневзвешенного значения с помощью функции СУММПРОИЗВ

 Синтаксис:

Синтаксис функции СУММПРОИЗВ в Excel:

 Аргументы:

• array1: обязательно. Первый массив или диапазон ячеек, которые нужно перемножить, а затем сложить.
• array2: необязательно. Второй массив или диапазон ячеек, которые нужно перемножить, а затем сложить.

Примечания:

• 1. Если в вашем диапазоне есть нечисловые значения, функция СУММПРОИЗВ считает их нулями.
• 2. Если массивы или диапазоны содержат разное количество строк и столбцов, функция СУММПРОИЗВ вернёт ошибку #ЗНАЧ!
• 3. Если в массивах используются логические проверки, они возвращают значения ИСТИНА и ЛОЖЬ. В большинстве случаев их нужно преобразовать в 1 и 0 с помощью двойного унарного оператора (--).
• 4. Функция СУММПРОИЗВ поддерживает до 255 аргументов в Excel 2007 и более поздних версиях, тогда как в более ранних версиях Excel — только до 30 аргументов.
• 5. Функция СУММПРОИЗВ не работает с символами подстановки.

 Возвращаемое значение:

Возвращает результат поэлементного перемножения массивов с последующим суммированием произведений.

 Примеры:

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

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

Чтобы решить эту задачу, используйте приведённую ниже формулу:

Пояснение: эта формула SUMPRODUCT(B2:B7,C2:C7) = B2*C2 + B3*C3 + B4*C4 + B5*C5 + B6*C6 + B7*C7.

Затем нажмите клавишу Enter, чтобы перемножить ячейки столбца B с соответствующими ячейками в той же строке столбца C и сложить полученные результаты. См. снимок экрана:

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

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

Случай 1: суммирование значений ячеек, если в другом столбце содержится определённый текст

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

1. Введите или скопируйте в пустую ячейку любую из приведённых ниже формул:

• Советы: в приведённой выше формуле:
• A2:A12=«KTE»: первый диапазон критериев и условие, по которому нужно выполнить суммирование.
• B2:B12=«David»: второй диапазон критериев и условие, по которому будет выполняться суммирование.
• D2:D12: диапазон суммирования, содержащий значения ячеек, которые нужно сложить.

2. Затем нажмите клавишу Enter, чтобы получить нужный результат:

Случай 2: суммирование ячеек по нескольким критериям с логикой ИЛИ

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

Например, вы хотите получить общую стоимость товаров KTE и KTO в столбце A, как показано на снимке экрана ниже:

Введите следующую формулу в пустую ячейку:

Затем нажмите клавишу Enter, чтобы рассчитать общую стоимость товаров KTE и KTO. См. снимок экрана:

Случай 3: суммирование ячеек по нескольким критериям с логикой ИЛИ и И

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

Звёздочка (*) используется как оператор И.

Символ плюс (+) используется как оператор ИЛИ.

Рассмотрим пример: нужно просуммировать общую стоимость товаров KTE и KTO, объём продаж которых превышает 200.

Скопируйте или введите приведённую ниже формулу:

Затем нажмите клавишу Enter, чтобы получить требуемый результат:

Пример 3: подсчёт ячеек по нескольким критериям с помощью функции СУММПРОИЗВ

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

Случай 1: подсчёт ячеек по нескольким критериям с логикой И

Здесь нужно подсчитать количество ячеек, в которых продукт — KTE, а объём продаж превышает 200. Воспользуйтесь любой из приведённых ниже формул:

• Советы: в приведённой выше формуле:
• A2:A12=«KTE»: первый диапазон и условие, по которым выполняется подсчёт.
• B2:B12>200: второй диапазон критериев и условие, по которому необходимо выполнить подсчёт.

Затем нажмите клавишу Enter, чтобы получить количество результатов:

Случай 2: подсчёт ячеек по нескольким критериям с логикой ИЛИ

Чтобы подсчитать количество товаров KTE и KTO в столбце A, используйте следующую формулу:

Затем нажмите клавишу Enter, чтобы получить количество товаров KTE и KTO. См. снимок экрана:

Случай 3: подсчёт ячеек по нескольким критериям с логикой ИЛИ и И

Чтобы подсчитать количество товаров KTE и KTO, у которых объём продаж превышает 200, используйте следующую формулу:

Затем нажмите клавишу Enter, чтобы получить требуемое количество результатов:

Пример 4: расчёт средневзвешенного значения с помощью функции СУММПРОИЗВ

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

Например, у вас есть таблица успеваемости студента, в которой каждое задание имеет свой вес, как показано на снимке экрана ниже.

Чтобы рассчитать средневзвешенное значение, используйте следующую формулу:

Пояснение: эта формула:SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6) = (B2*C2+B3*C3+B4*C4+B5*C5+B6*C6)/(C2+C3+C4+C5+C6)

Затем нажмите Enter — и взвешенное среднее будет рассчитано мгновенно. См. снимок экрана:

 Другие статьи с использованием функции СУММПРОИЗВ: