Функция СУММПРОИЗВ в Excel

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

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

 Синтаксис:

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

 Аргументы:

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

Примечания:

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

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

Возвращает результат перемноженных и просуммированных массивов.

 Примеры:

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

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

Для выполнения этой задачи примените следующую формулу:

Объяснение: Эта формула СУММПРОИЗВ(B2:B7;C2:C7) = B2*C2+B3*C3+B4*C4+B5*C5+B6*C6+B7*C7.

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

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

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

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

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

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. Как и при суммировании значений, вам просто нужно удалить аргумент sum_range из формулы.

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

Здесь я хочу подсчитать ячейки, где продукт — KTE, а продажи больше 200. Для этого используйте любую из следующих формул:

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

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

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

Для подсчета количества продуктов KTE и KTO в столбце A примените эту формулу:

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

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

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

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

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

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

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

Для расчета средневзвешенного значения используйте следующую формулу:

Объяснение: Эта формула: СУММПРОИЗВ(B2:B6; C2:C6) / СУММ(C2:C6) = (B2*C2+B3*C3+B4*C4+B5*C5+B6*C6)/(C2+C3+C4+C5+C6)

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

 Больше статей с функцией СУММПРОИЗВ: