СУММПРОИЗВ в Excel: примеры с условиями и без

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

Про СУММПРОИЗВ

СУММПРОИЗВ в Excel примеры с условиями

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

Читаем – как сравнивать две таблицы в Эксель.

a1, a2, a3, a4

b1, b2, b3, b4

И вот как будет выглядеть СУММПРОИЗВ этих массивов:

a1 * b1 + a2 * b2 + a3 * b3 + a4 * b4

Диапазоны в СУММПРОИЗВ должны быть одинакового размера, иначе Excel вернет ошибку #ЗНАЧ!.

Если пока не очень понятно, что здесь называется массивом, отдельно читаем про работу с массивами в Excel.

Лучше рассмотрим на конкретном примере. Представим себе, что у нас есть таблица с сотрудниками с двумя столбцами:

  • Ставка за один день работы.
  • Количество отработанных дней в квартале.

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

СУММПРОИЗВ в Excel: примеры с условиями и без

На самом деле можно сделать и по-другому. Например, создать отдельный столбец с общей выплатой для каждого сотрудника. А потом просто выполнить сумму по всем строчкам этого столбца. Если нужен именно такой вариант, вот инструкция – как суммировать столбец и отдельные ячейки в Excel.

СУММПРОИЗВ в Excel: примеры с условиями и без

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

=СУММПРОИЗВ(B2:B7

СУММПРОИЗВ в Excel: примеры с условиями и без

Каждый массив разделяется знаком точки с запятой (;), через нее указываем второй столбец с количеством рабочих дней за квартал. И итоговая функция будет выглядеть так:

=СУММПРОИЗВ(B2:B7;C2:C7)

СУММПРОИЗВ в Excel: примеры с условиями и без

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

СУММПРОИЗВ в Excel: примеры с условиями и без

С условиями

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

  1. Ставим знак равно (=) и жмем по кнопке вставки функции.

СУММПРОИЗВ в Excel: примеры с условиями и без

  1. Выбираем категорию «Математические» и нашу функцию.

СУММПРОИЗВ в Excel: примеры с условиями и без

  1. Заполняем первые два массива.

СУММПРОИЗВ в Excel: примеры с условиями и без

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

A2:A7=E2

СУММПРОИЗВ в Excel: примеры с условиями и без

  1. Если вы нажмете «ОК», то итог будет ноль:

0

СУММПРОИЗВ в Excel: примеры с условиями и без

  1. Нам нужно немного поправить нашу формулу, чтобы она работала. В строке формул уберите последний знак точки с запятой (;) и вместо него ставим знак умножения (*). А последнее условие заключите в скобки. При умножении ИСТИНА превращается в 1, а ЛОЖЬ – в 0, поэтому в сумму попадут только строки нужного сотрудника.

=СУММПРОИЗВ(B2:B7;C2:C7*(A2:A7=E2))

СУММПРОИЗВ в Excel: примеры с условиями и без

  1. Все отлично работает.

СУММПРОИЗВ в Excel: примеры с условиями и без

Вы можете использовать условия к данным разного типа. Например, к числам, строкам и датам. Напомню, что вы можете также использовать и другие логические знаки:

<, >, =, <=, >=, <>

Если формулу потом будете копировать в другие ячейки, может пригодиться закрепление ссылок в Excel.

Читаем – про логические функции.

Видео

Автор статьи
Бородач 2954 статей
Сенсей по решению проблем с WiFiем. Обладатель оленьего свитера, колчана витой пары и харизматичной бороды. Любитель душевных посиделок за танками.
WiFiGid
Комментарии: 4
  1. Юрий

    А вообще это похоже на декартово произведение

  2. оксана

    ого, сложно но можно сделать, спасибо

  3. Аноним

    А иногда проще вообще все руками написать)))

  4. Аноним

    Дай тебе всевышний огромного здоровья, мил человек! Еле нашел простое разъяснение, как работает эта простая функция под одним простым условием. Все остальные размазывают, запутывают, умничают, поэтому всё у них невнятно и не в тему. Бесят дико.

Добавить комментарий
После отправки комментарий может не отображаться - это нормально. Сразу же после модерации он будет опубликован. Если Вы хотите быстро узнать о получении ответа, рекомендуем оставить свой e-mail (это необязательно). E-mail используется исключительно для Вашего оповещения, мы не занимаемся спамом.

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Нажимая на кнопку "Отправить комментарий", я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.