Функция СУММЕСЛИМН() Сложение с несколькими критериями в EXCEL (Часть 2.Условие И). Как суммировать значения между двумя датами

Сумма произведений – часто применяемое математическое действие. В Экселе его можно выполнить с помощью простой формулы или с применением функции.

Синтаксис СУММПРОИЗВ

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

Простейший пример использования функции

Чтобы стало понятно, как и что считает команда, рассмотрим простой пример. Имеем таблицу с указанными длинами и ширинами прямоугольников. Нам нужно сосчитать сумму площадей всех прямоугольников. Если не пользоваться данной функцией, придется произвести промежуточные действия и сосчитать площадь каждого прямоугольника, а только потом сумму. Как мы и сделали.

Сумма площадей.СУММПРОИЗВ.

Обратите внимание, что нам не понадобился массив с промежуточными итогами. В аргументах функции мы использовали только массивы с длиной и шириной, а функция их автоматически перемножила и просуммировала, выдав тот же результат = 70.

Вычисление суммы произведения

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

Способ 1: использование математической формулы

Большинству пользователей известно, что в Экселе можно выполнить значительное количество математических действий просто поставив знак «=» в пустой ячейке, а далее записав выражение по правилам математики. Этот способ можно использовать и для нахождения суммы произведений. Программа, согласно математическим правилам, сразу подсчитывает произведения, а уже потом складывает их в общую сумму.

  1. Устанавливаем знак «равно» (=) в ячейке, в которой будет выводиться результат производимых вычислений. Записываем туда выражение суммы произведений по следующему шаблону:

    =a1*b1*…+a2*b2*…+a3*b3*…+…

    Например, таким способом можно рассчитать выражение:

    =54*45+15*265+47*12+69*78

Формула суммы произведений в Microsoft Excel

Чтобы произвести расчет и вывести его результат на экран, жмем на кнопку Enter на клавиатуре.

Результат расчета формулы суммы произведений в Microsoft Excel

Способ 2: работа со ссылками

Вместо конкретных чисел в этой формуле можно указывать ссылки на ячейки, в которых они расположены. Ссылки можно вписать вручную, но удобнее это делать, выделив после знака «=», «+» или «*» соответствующую ячейку, в которой содержится число.

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

Формула суммы произведений со ссылками в Microsoft Excel

Затем, чтобы произвести подсчет, кликаем по кнопке Enter. Результат вычисления будет выведен на экран.

Результат расчета формулы суммы произведений со ссылками в Microsoft Excel

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

Урок: Работа с формулами в Excel

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

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

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

Синтаксис данной функции имеет такой вид:

=СУММПРОИЗВ(массив1;массив2;…)

Аргументами этого оператора являются диапазоны данных. При этом они сгруппированы по группам множителей. То есть, если отталкиваться от шаблона, о котором мы говорили выше (a1*b1*…+a2*b2*…+a3*b3*…+…), то в первом массиве располагаются множители группы a, во втором – группы b, в третьем – группы c и т.д. Данные диапазоны обязательно должны быть однотипными и равными по длине. Располагаться они могут, как вертикально, так и горизонтально. Всего данный оператор может работать с количеством аргументов от 2 до 255.

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

  1. Выделяем ячейку на листе, в которой будет выводиться итоговый результат. Жмем на кнопку «Вставить функцию». Она оформлена в виде пиктограммы и располагается слева от поля строки формул.

Переход в Мастер функций в Microsoft Excel

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

Переход к аргументам функции СУММПРОИЗВ в Microsoft Excel

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

Аргументы функции СУММПРОИЗВ в Microsoft Excel

После этих действий программа самостоятельно производит все требуемые вычисления и выводит итоговый результат в ту ячейку, которая была выделена в первом пункте данной инструкции.

Результаты вычисления функции СУММПРОИЗВ в Microsoft Excel

Урок: Мастер функций в Excel

Способ 4: применение функции по условию

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

У нас есть таблица окладов и отработанных дней работников предприятия за три месяца помесячно. Нам нужно узнать, какую сумму на протяжении этого периода заработал сотрудник Парфенов Д.Ф.

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

    =”Парфенов Д.Ф.”

    После того, как все данные внесены, жмем кнопку «OK».

Аргументы функции СУММПРОИЗВ с условием в Microsoft Excel

Приложение производит вычисление. В расчет берутся только строчки, в которых присутствует имя «Парфенов Д.Ф», то есть то, что нам и нужно. Результат вычислений отображается в предварительно выделенной ячейке. Но результат равен нулю. Это связано с тем, что формула в том виде, в котором она существует сейчас, работает некорректно. Нам нужно её немного преобразовать.

Промежуточный результат вычисления по условию в Microsoft Excel

Для того чтобы преобразовать формулу, выделяем ячейку с итоговым значением. Выполняем действия в строке формул. Аргумент с условием берем в скобки, а между ним и другими аргументами точку с запятой меняем на знак умножения (*). Жмем на кнопку Enter. Программа производит подсчет и на этот раз выдает уже корректное значение. Мы получили общую сумму заработной платы за три месяца, которая причитается сотруднику предприятия Парфенову Д. Ф.

Итоговый результат вычисления по условию в Microsoft Excel

Таким же образом можно применять условия не только к тексту, но и к числам с датами, добавляя знаки условия «<», «>», «=», «< >».

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

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.
ЗакрытьОпишите, что у вас не получилось.

Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

Функция СУММПРОИЗВ() – Сложение и подсчет с множественными условиями в MS EXCEL

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

​ содержащие слово яблоки​ к массивам операции​

  • ​ только 1 массив,​Функция СУММПРОИЗВ(), английская версия​
  • ​ число даст?​buchlotnik​
  • ​ только если соблюдена​ рассчитанное для чисел​

Нахождение суммы произведений элементов массивов

​ 4-х задач является​ нахождение сумм ящиков​ Строке формул A2_A13=D2,​ с товаром персики​

​ всех данных.​ количество приобретенных товаров.​​=СУММПРОИЗВ((B2:B8=C12)*(C2:C8=C11)*(D2:D8>=C13)*(D2:D8 Получится так.​​ С12, т.е. в​ столбца А с​ (подробнее, например, в​ сравнения, т.е. использовать​ т.е.=СУММПРОИЗВ(A3:A6)? Тогда функция​ SUMPRODUCT(), не так​Сергей​: регистр имеет значение,​ строгая пунктуация СУММПРОИЗВ​ в массиве. Пример​ применение Автофильтра.​ только тех партий​ а затем нажав​ (ячейка​

​A​Чтобы создать формулу, введите​Обратите внимание​ диапазоне «менеджер» искать​

​ данными столбца В​ статье Подсчет значений​ ее для сложения​ СУММПРОИЗВ() вернет сумму​ проста, как кажется​: Я в массивах​ дата – это​ (только через​

​ прилагаю.​Для решения 3-й задачи​ товаров, у которых​F9​D2​B​=СУММПРОИЗВ(B3:B6,C3:C6)​

​.​ слово «Васечкин». Подправляем​ (1*2, 3*4, 5*6)​ с множественными критериями​ чисел, удовлетворяющих определенным​ элементов, т.е. будет​ с первого взгляда:​

​ не селен.​ не текст, и​, а также​Владимир​ таблица с настроенным​ количество ящиков попадает​;​

  • ​), у которых остаток​D​и нажмите клавишу​​В формуле перед​​ формулу – эту​ и, затем, все​ (Часть 1. Условие​
  • ​ условиям.​ эквивалентна функции СУММ():​ помимо собственно нахождения​Пробовал просит значение.​ т.д.​ есть только одно​: Можно вставить ЕЧИСЛО(),​ автофильтром выглядит так​ в определенный интервал,​
  • ​Результатом вычисления B2:B13 является​ ящиков на складе​
  • ​E​ ВВОД.​ датой начала периода​ часть формулы заключаем​ результаты сложить. В​

​ И)).​Попробуем подсчитать число значений​ =СУММ(A3:A6).​ суммы произведений, эта​Можно на примере​=СУММПРОИЗВ((G1:G17=B3)*(F1:F17=СТРОЧН(B$4))*(ТЕКСТ(H1:H17;”ММММ”)=ПРОПНАЧ(B$6))*(ГОД(H1:H17)=C$6))​ условие. Не универсальная​ ЕСЛИОШИБКА().​​ (см. файл примера​​ например от 5​​ массив {3:5:11:98:4:8:56:2:4:6:10:11}, т.е.​​ >=6 (ячейка​

​3​Каждое значение в столбце​ ставим не просто​ в круглые скобки​ ячейку С 21​​Функцию СУММПРОИЗВ() можно использовать​​ больших 2 в​Синтаксис функции СУММПРОИЗВ() позволяет​ функция может использоваться​

​ показать?​Cactus​ функция​Tatirus​ Лист 2 Даты).​ до 20 (см.​ просто значения из​E2​4​ B будет умножено​ «равно», а «больше»​ и ставим знак​

Суммирование и подсчет значений удовлетворяющих определенным критериям

​ устанавливаем функцию.​ для отбора значений​ диапазоне​​ не просто указывать​​ для подсчета и​vikttur​

​: Спасибо огромное, а​Юрий М​: Получилось вставить ЕЧИСЛО()​Предварительно таблицу нужно преобразовать​ файл примера Лист​ столбца​​). Мы должны получить​​2​ на значение в​ и «равно». Перед​ «*» (умножить).​На закладке «Формулы»​ по нескольким критериям​​A3:A6​​ в качестве аргумента​ суммирования значений на​: Вы о функции​ каким образом можно​: Tatirus, да сколько​ под функцию СУММПРОИЗВ…​ в формат таблиц​ 2Числа).​B​ результат 64. Подсчет​7​ той же строке​ датой окончания периода​C2:C8=C11 – это​ в разделе «Библиотека​ (с множественными условиями).​, содержащий значения 4,​

​ определенный диапазон, но​ основе критериев, а​ уже прочитали? И​

​ получить информацию за​ же можно Вам​ Но решение формула​ MS EXCEL 2007​Формулы строятся аналогично задаче​

​;​ можно реализовать множеством​8​ столбца C, а​

​ ставим «меньше» и​ означает, что в​ функций» нажимаем на​​ Как известно, 2​​ 8, 6, 1.​ и осуществлять арифметические​

​ также, в некоторых​ попробовали применить? В​ весь год, за​ про цитирование говорить?​ выдала неверное…​ и включить строку​ 1: =СУММЕСЛИМН(B2:B13;B2:B13;”>=”&D2;B2:B13;”​Результатом поэлементного умножения массивов​ формул, приведем несколько​6​

​ произведения будут суммированы.​ «равно».​ диапазоне С2:С8 нужно​ кнопку «Математические» и​ критерия могут образовывать​Если мы запишем формулу​ действия перед операцией​

  • ​ случаях, избавить от​​ справке после описания​​ все время.​ Уже и себя​V​
  • ​ Итогов.​​Примечание​​ (A2:A13=D2)*(B2:B13) является {0:0:0:0:4:8:56:0:0:0:0:0}.​ (см. файл примера​6​ Общая стоимость приобретенных​
  • ​В Excel можно​​ искать то, что​​ выбираем функцию «СУММПРОИЗВ».​ разные условия:​ =СУММПРОИЗВ(A3:A6>2), то получим​ суммирования. Например, записав:​

​ необходимости применений формул​ нормальный пример есть…​

СУММПРОИЗВ() – как формула массива

​Che79​ цитируете…​: =СУММ(C2:C9*(B2:B9>2)*ЕСЛИ(ЕЧИСЛО(B2:B9);B2:B9;0)) формула массива.​Verniy55​: для удобства, строки,​ При умножении числа​ Лист Текст и​

  • ​7​ товаров составит 21,60 р.​
  • ​ вставить ссылки не​ написано в ячейке​
  • ​Диалоговое окно заполнили​Условие​

​ результат 0. Выделив​​=СУММПРОИЗВ(A3:A6*2), получим сумму произведений​ массива.​

excel2.ru>

Примечания

  • Аргументы, которые являются массивами, должны иметь одинаковые размерности. В противном случае функция СУММПРОИЗВ возвращает значение ошибки #ЗНАЧ!. Например, =СУММПРОИВ(C2:C10;D2:D5) возвращает ошибку, так как диапазоны не одного размера.

  • В функции СУММПРОИВТ ненумерические записи массива обрабатывают их так, как если бы они были нулями.

  • Для лучшей производительности не следует использовать суммпроив с полными ссылками на столбцы. Рассмотрим функцию =СУММПРОИВ(A:A;B:B), чтобы умножить 1 048 576 ячеек в столбце A на 1 048 576 ячеек в столбце B перед их добавлением. 

Синтаксис

=SUMPRODUCT(array1, [array2]. [array3], …) – английская версия

=СУММПРОИЗВ(массив1;[массив2];[массив3];…) – русская версия

СУММПРОИЗВ с условием

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

Рассмотрим пример. Имеем таблицу затрат небольшой компании за один расчетный месяц. Необходимо сосчитать общую сумму потраченных средств за январь и февраль по всем статьям расходов.

Для расчета затрат по канцелярии в январе-месяце, используем нашу функцию и указываем в начале 2 условия. Каждое из них заключаем в скобки, а между ними ставим знак «звездочка», подразумевающий союз «и». Получаем следующий синтаксис команды:

Расходы.

  • (А:А=”Январь”) – первое условие;
  • (Е:Е=”канцелярия”) – второе условие;
  • D:D – массив, из которого выводится итоговая сумма.

В итоге получилось, что в январе на канцелярские принадлежности было затрачено 3700 рублей. Протянем формулу на остальные строки и заменим в каждой из них условия (заменив месяц или статью расходов).

Пример.

Пример 1

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

Чтобы создать формулу на примере выше, введите =СУММПРОИВ(C2:C5;D2:D5) и нажмитеввод . Каждая ячейка в столбце C умножается на соответствующую ячейку в той же строке столбца D, и результаты сбавляются. Общая сумма продуктов составляет 78,97 долларов США.

Чтобы ввести более длинную формулу, которая дает такой же результат, введите =C2*D2+C3*D3+C4*D4+C5*D5 и нажмите ввод . После нажатия ввод результат будет таким же: 78,97 долларов США. Ячейка C2 умножается на D2, а ее результат добавляется к результату ячейки C3, умноженной на ячейку D3 и так далее.

Суммирование и подсчет значений удовлетворяющих определенным критериям

Попробуем подсчитать число значений больших 2 в диапазоне A3:A6 , содержащий значения 4, 8, 6, 1.

Если мы запишем формулу =СУММПРОИЗВ(A3:A6>2) , то получим результат 0. Выделив в Строке формул A3:A6>2 и нажав клавишу F9 , получим массив {ИСТИНА: ИСТИНА: ИСТИНА: ЛОЖЬ}, который говорит, что мы движемся в правильном направлении: в диапазоне A3:A6 больше 2 только первые 3 значения. Хотя значению ИСТИНА соответствует 1, а ЛОЖЬ – 0, мы не получим 3, т.к. для перевода значений ИСТИНА/ЛОЖЬ в числовую форму требуется применить к ним арифметическую операцию. Для этого можно, например, применить операцию двойного отрицания (–), что позволит привести массив в числовую форму {1:1:1:0}.

Итак, задача подсчета значений больше 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 ) )

В файле примера приведены решения подобных задач.

Задача4 (Месяц)

Немного модифицируем условие предыдущей задачи: найдем суммарные продаж за месяц(см. файл примера Лист Месяц ).

Формулы строятся аналогично задаче 3, но пользователь вводит не 2 даты, а название месяца (предполагается, что в таблице данные в рамках 1 года).

sumpng-6.png

Месяц вводится с помощью Выпадающего списка , перечень месяцев формируется с использованием Динамического диапазона (для исключения лишних месяцев).

Логические связки И и ИЛИ (AND и OR)

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

Функция СУММПРОИЗВ в Excel - применение, синтаксис, примеры8

Пример 2

В следующем примере sumPRODUCT возвращает суммарные чистую сумму продаж по агенту продаж, у которых есть как общие продажи, так и расходы по агенту. В этом случае мы используем таблицу Excel, в которой используются структурированные ссылки вместо стандартных Excel диапазонов. Здесь вы увидите, что диапазоны “Продажи”, “Расходы” и “Агент” имеют ссылку по имени.

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

Формула: =СУММПРОИМ(((Таблица1[Продажи])+(Таблица1[Расходы]))*(Таблица1[Агент]=B8)) и возвращает сумму всех продаж и расходов агента, указанных в ячейке B8.

Видео работы функции

Решение сбалансированной транспортной задачи в MS ExcelРешение не сбалансированной транспортной задачи в MS Excel

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Подсчет по данным из закрытого файла

А теперь главная фишка. После того, как вы про нее узнаете, вы окончательно полюбите эту функцию. Дело в том, что она умеет находить информацию в тех файлах, которые не открыты на данный момент. Как известно, многие остальные функции умеют осуществлять поиск лишь в тех книгах, которые были ранее открыты на компьютере. В этом случае пользователь может даже их не открывать. Это особенно удобно, когда ресурсов компьютера мало (например, таблица большая, открыто много параллельных нужных вкладок в браузере, а оперативной памяти на компьютере мало). В этом случае эта функция будет как нельзя кстати. 

Вот скриншот, где мы получаем данные о восточном филиале компании. 

Функция СУММПРОИЗВ в Excel - применение, синтаксис, примеры9

Как оно работает в других формулах (вернее, не работает)? Если документ, из которого черпается информация, не открыт в данный момент, то появляется ошибка #ЗНАЧ! В случае же с использованием функции СУММПРОИЗВ, если попытаться считать данные из файла, это получится сделать. Это открывает широчайшие возможности, поскольку дает возможность пользоваться недорогими компьютерами для выполнения сложных вычислений или сравнений.

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

Оцените качество статьи. Нам важно ваше мнение:

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...