СУММЕСЛИ (SUMIF) – Cправка – Редакторы Документов

Как работать с СУММЕСЛИ – синтаксис, примеры различных текстовых и числовых критериев, дат и значений, удовлетворяющих нескольким условиям сразу.

Что возвращает функция

Возвращает сумму чисел, указанных в качестве аргументов и отвечающих заданным в формуле критериям.

Пример суммирования с использованием функции СУММЕСЛИ

Этот пример можно считать классическим. Пусть есть таблица с данными о продажах некоторых товаров.

Данные о продажах

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

Первым делом выделяем ячейку, где будет подсчитана сумма. Далее вызываем Мастера функций. Это значок fx в строке формул. Далее ищем в списке функцию СУММЕСЛИ и нажимаем на нее. Открывается диалоговое окно, где для решения данной задачи нужно заполнить всего два (первые) поля из трех предложенных.

Диалоговое окно функции СУММЕСЛИ

Поэтому я и назвал такой пример упрощенным. Почему 2 (два) из 3 (трех)? Потому что наш критерий находится в самом диапазоне суммирования.

В поле «Диапазон» указывается та область таблицы Excel, где находятся все исходные значения, из которых нужно что-то отобрать и затем сложить. Задается обычно с помощью мышки.

В поле «Критерий» указывается то условие, по которому формула будет проводить отбор. В нашем случае указываем «>70». Если не поставить кавычки, то они потом сами дорисуются.

Последнее поле «Дапазон_суммирования» не заполняем, так как он уже указан в первом поле.

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

Заполнив в Мастере функций необходимые поля, нажимаем на клавиатуре кнопку «Enter», либо в окошке Мастера «Ок». На месте вводимой функции должно появиться рассчитанное значение. В моем примере получилось 224шт. То есть суммарное значение проданных товаров в количестве более 70 штук составило 224шт. (это видно в нижнем левом углу окна Мастера еще до нажатия «ок»). Вот и все. Это был упрощенный пример, когда критерий и диапазон суммирования находятся в одном месте.

Теперь давайте рассмотрим, пример, когда критерий не совпадает с диапазоном суммирования. Такая ситуация встречается гораздо чаще. Рассмотрим те же условные данные. Пусть нам нужно узнать сумму не больше или меньше какого-то значения, а сумму конкретной группы товаров, допустим, группы Г.

Для этого снова выделяем ячейку с будущим подсчетом суммы и вызываем Мастер функций. В первом окошке указываем диапазон, где содержится критерий, в нашем случае это столбец с названиями групп товаров. Далее сам критерий прописываем либо вручную, оставив в соответствующем поле запись «группа Г», либо просто указываем мышкой ячейку с нужным критерием. Последнее окошко – это диапазон, где находятся суммируемые данные.

Диалоговое окно функции СУММЕСЛИ с отдельным критерием

Результатом будет сумма проданных товаров из группы Г – 153шт.

Итак, мы посмотрели, как рассчитать одну сумму по одному конкретному критерию. Однако чаще возникает задача, когда требуется рассчитать несколько сумм для нескольких критериев. Нет ничего проще! Например, нужно узнать суммы проданных товаров по каждой группе. То бишь интересует 4 (четыре) значения по 4-м (четырем) группам (А, Б, В и Г). Для этого обычно делается список групп в виде отдельной таблички. Понятное дело, что названия групп должны в точности совпадать с названиями групп в исходной таблице. Сразу добавим итоговую строчку, где сумма пока равна нулю.

Таблица для подтягивания данных по СУММЕСЛИ

Затем прописывается формула для первой группы и протягивается на все остальные. Здесь только нужно обратить внимание на относительность ссылок. Диапазон с критериями и диапазон суммирования должны быть абсолютным ссылками, чтобы при протягивании формулы они не «поехали вниз», а сам критерий, во-первых нужно указать мышкой (а не прописать вручную), во-вторых, должен быть относительной ссылкой, так как каждая сумма имеет свой критерий суммирования.

Заполненные поля Мастера функций при подобном расчете будут выглядеть примерно так.

Заполненное диалоговое окно функции СУММЕСЛИ

Как видно, для первой группы А сумма проданных товаров составила 161шт (нижний левый угол рисунка). Теперь нажимаем энтер и протягиваем формулу вниз.

Подтянутые данные по СУММЕСЛИ

Все суммы рассчитались, а их общий итог равен 535, что совпадает с итогом в исходных данных. Значит, все значения просуммировались, ничего не пропустили.

Простое сложение в Excel

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

Функция СУММ в Excel

Синтаксис и создание функции

Функция СУММЕСЛИ популярна, поскольку почти в каждой таблице нужно посчитать сумму чисел в ячейках, игнорируя значения, не попадающие под основное условие. Благодаря этой формуле подсчет не становится чем-то сложным и длительным. Стандартное оформление функции выглядит как =СУММЕСЛИ(Диапазон;Критерий;Диапазон_суммирования), а «Диапазон суммирования» указывается только при условии, когда есть фактические ячейки, сложение которых выполняется при любых обстоятельствах. Если данные в «Диапазоне суммирования» отсутствуют, проверяться будут все ячейки, входящие в «Диапазон».

Ознакомление с синтаксисом функции СУММЕСЛИ в Excel перед ее созданием

Разберем и два оставшихся аргумента —

«Диапазон»

и

«Критерий»

. В первый вписывается перечень ячеек (

A1:A100

), которые будут попадать под проверку и сравнение с условием. В

«Критерий»

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

,>50) или соответствия заданному тексту («Text»). Для упрощения понимания установки аргументов откройте графическое окно «Аргумент функции» и задайте все условия по очереди в отдельных полях.

Использование графического меню для объявления функции СУММЕСЛИ в Excel

Существует не так много разных примеров, особенности заполнения которых стоит учитывать при оформлении функции СУММЕСЛИ, и далее разберемся с основными и самыми популярными.

Функция СУММЕСЛИ Excel.

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

СУММЕСЛИ следует использовать в тех случаях, когда Вам необходимо суммировать значения в диапазоне, которые соответствует указанному Вами критерию.

Возможные варианты данной функции:

  • сумма, если больше;
  • сумма, если меньше;
  • сумма, если равно;
  • сумма, если не равно;
  • сумма если больше или равно;
  • сумма если меньше или равно.

Отдельная статья описывает функцию СУММЕСЛИ Excel. Точное совпадение. Частичное совпадение. С  текстовыми критерий. Содержит описание, примеры и иллюстрации. Статья находиться по этой ссылке: Функция СУММЕСЛИ в MS Excel. Точное совпадение. Частичное совпадение. Инструкция и примеры.

Самый простой и понятный способ вызвать данную функцию, это нажать в правом верхнем углу, рядом с кнопкой ∑, стрелку вниз (Рис. 1).

Функция СУММЕСЛИ Excel, варианты и примеры

Рис 1

.

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

Функция СУММЕСЛИ Excel, варианты и примеры

Рис 2

Появляется диалоговое окно – Вставка функции. Самое верхнее, это поле для поиска нужной функции. Вводим название функции, выбираем ее в результатах поиска и нажимаем ОК (Рис. 3).

Функция СУММЕСЛИ Excel, варианты и примеры

Рис. 3

Появляется диалоговое окно — Аргументы функции (Рис. 4).

Функция СУММЕСЛИ Excel, варианты и примеры

Рис. 4

В нем есть три ключевых поля для заполнения:

  1. Диапазон — здесь указываться диапазон ячеек, в которых будет происходить поиск по указанному критерию;
  2. Критерий — здесь будет указан сам критерий поиска;
  3. Диапазон _ суммирования, здесь указывается диапазон ячеек, данные в которых будут суммироваться, исходя из критерия поиска. Применяется не во всех случаях.

Что бы выбрать нужный диапазон нажимаете на стрелочку в конце строки диапазона и выбираете нужные Вам ячейки с помощью мыши. Можно сразу выбрать нужный диапазон, не нажимая на стрелочку, но сначала нужно поставив курсор на нужную строку в диалоговом окне. Можно прописать диапазон вручную. Результат от этого не зависит. После того как все готово, нажимает ОК  (Рис. 5).

Функция СУММЕСЛИ Excel, варианты и примеры

Рис. 5

Пример использования

СУММЕСЛИ(A1:A10; “>20”)

СУММЕСЛИ(A1:A10; “Оплачено”; B1:B10)

Синтаксис

=SUMIF(range, criteria [sum_range]) – английская версия

=СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]) – русская версия

Как работает функция СУММЕСЛИ в Excel?

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

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

Но как быть, если нам нужно быстро посчитать заработные платы только продавцов? В дело вступает использование функции СУММЕСЛИ.

Прописываем аргументы.

  1. Диапазоном в данном случае будет являться список всех должностей сотрудников, потому что нам нужно будет определить сумму заработных плат. Поэтому проставляем E2:E14.
  2. Критерий выбора в нашем случае – продавец. Заключаем слово в кавычки и ставим вторым аргументом.
  3. Диапазон суммирования – это заработные платы, потому что нам нужно узнать сумму зарплат всех продавцов. Поэтому F2:F14.

СУММЕСЛИ.

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

Аналогично можно подсчитать зарплаты всех менеджеров, продавцов-кассиров и охранников. Когда табличка небольшая, кажется, что все можно сосчитать и вручную, но при работе со списками, в которых по несколько сотен позиций, целесообразно использовать СУММЕСЛИ.

Аргументы функции

  • range (диапазон) – диапазон ячеек, по которым оцениваются критерии. Аргументом могут быть числа, текст, массивы или ссылки, содержащие числа;
  • criteria (условие) – критерии, которые проверяются по указанному диапазону ячеек и определяют, какие ячейки суммировать;
  • sum_range (диапазон_суммирования) – (не обязательно) суммируемые ячейки. Если этот аргумент не указан, то функция использует аргумент range (диапазон) в качестве sum_range (диапазон_суммирования).

Функция SUMIFS (СУММЕСЛИМН) в Google таблицах

Практически всегда стоит задача отфильтровать данные по заданным условиям (параметр дата, значение показателя, наименование клиента, признак действия) и вывести результат суммы этих отфильтрованных данных в определенную ячейку гугл таблиц — этим и занимается функция SUMIFS (СУММЕСЛИМН).

Синтаксис функции:

=SUMIFS(сумма_диапазона; диапазон_критерия1; критерий1; [диапазон_критерия2; …]; [условие2; …])

Рассмотрим пример, где стоит задача: вывести сумму проданных товаров по месяцам, которые будут отфильтрованы по товарным группам. Для этой процедуры мы сначала создаем вспомогательный столбец (потом его можно будет удалить / очистить) и выводим месяцы из указанных дат формулой MONTH (МЕСЯЦ). Синтаксис прост:

=MONTH(дата)

Пример функции MONTH (МЕСЯЦ) в Google таблице

Мы уже с вами знаем функцию IF(ЕСЛИ), давайте воспользуемся ею для красоты и заменим числа месяцев, выводимых формулой MONTH (МЕСЯЦ), на названия этих месяцев:

=IF(MONTH(E2)=5;”Май“;IF(MONTH(E2)=4;”Апрель“;IF(MONTH(E2)=3;”Март“;IF(MONTH(E2)=2;”Февраль“;IF(MONTH(E2)=1;”Январь“;””)))))

Пример многомерной формулы функции IF (ЕСЛИ) в Google таблице

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

=SUMIFS($D:$D;$A:$A;H2;$F:$F;”Январь”)

Пример работы функции SUMIFS (СУММЕСЛИМН) в Google таблице

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

Таким образом можно построить аналитический отчет по месяцам или по любым другим параметрам через функцию SUMIFS (СУММЕСЛИМН). Поделитесь в комментариях, где вы применяли данную функцию и какие задачи решали с ее помощью?

Примечания

  • СУММЕСЛИ выполняет условное суммирование только на основании одного условия. Для учета нескольких условий используйте функцию БДСУММ.

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

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

Примеры использования функции СУММЕСЛИ в Excel

Функция SUMIF (СУММЕСЛИ) в Excel

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

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