Примеры функции «СУММЕСЛИ» в Excel

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

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

Давайте посмотрим на два оставшихся аргумента: «Диапазон» и «Критерий». Первый содержит список ячеек (A1: A100), которые будут проверяться и сравниваться с условием. В поле «Критерий» пользователь вводит условие, согласно которому ячейка становится одной из сводок. Это может быть условие неравных чисел (<30,> 50) или совпадение определенного текста («Текст»). Чтобы упростить понимание того, как задавать аргументы, откройте графическое окно «Аргумент функции» и задайте все условия по очереди в отдельных полях.

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

Как суммировать значения между двумя динамическими датами

В приведенном выше примере мы видели, как суммировать данные между двумя конкретными датами. Но что, если мы хотим суммировать данные, например, за последние 7 дней до даты открытия файла? Что, если мы не хотим каждый раз вставлять в формулу определенные даты?

В этом случае нам поможет следующая формула:

= СУММ (B2: B18; A2: A18; ”<=” & СЕГОДНЯ (); A2: A18; ”> =» & СЕГОДНЯ () — 6)

СУММЕСЛИМН для суммирования значений по нескольким условиям в Excel

Пример 3. Используя таблицу из второго примера, определите общую прибыль от поставки товаров №1. 1 в Китай и Грузию за весь период (три месяца).

Чтобы найти нужное значение, воспользуйтесь формулой массива (для ввода нажмите CTRL + SHIFT + Enter):

Функция СУММПИИФ возвращает массив значений для критериев Китая и Грузии, соответственно, которые суммируются функцией СУММ.

Примечание. Если критерии передаются как константа массива для двух или более пар критериев_диапазонN; conditionN, результат формулы будет неверным.

Суммирование с множеством условий.

Есть данные о заказах и продажах шоколада. Подсчитываем сумму реализованных продаж молочного шоколада. То есть у нас есть два требования: название продукта должно совпадать и в столбце «Завершено» должно быть указано «Да».

В качестве первого аргумента мы указываем интервал суммирования E2: E21, затем попарно интервал условия и само условие.

= СУММЕСЛИ (E2: E21; C2: C21; I2; F2: F21; I3)

В C2: C21 мы будем искать слово «молоко» в любом его появлении. То есть до и после него могут быть другие символы.

В F2: F21 мы ищем «Да», что является признаком того, что заказ выполнен.

При соблюдении ОБЕИХ требований такой заказ нам подходит и мы учтем его стоимость.

Как видите, мы нашли 2 матча, где продавался молочный шоколад.

Рекомендации

Необходимые действия

Описание

Использование подстановочных знаков	Подстановочные знаки, такие как вопросительный знак (?) Или звездочка (*) в аргументах Condition1, 2, могут использоваться для поиска похожих, но не равных значений. Знак вопроса соответствует любому одиночному символу. Звездочка: любая последовательность символов. Если вы хотите найти ровно один вопросительный знак или звездочку, вам нужно поставить тильду (~) перед вопросительным знаком. Например, формула = СУММЕСЛИ (A2: A9; B2: B9; «= I *»; C2: C9; «Искусство?») Складывает все значения с именем, которое начинается с «Искусство» и заканчивается любым письмо.
Различия между СУММЕСЛИ и СУММЕСЛИ	Порядок аргументов в функциях СУММЕСЛИ и СУММЕСЛИ различается. Например, в функции СУММЕСЛИ Sum_Range является первым аргументом, а в функции СУММЕСЛИ — третьим. Этот момент часто является источником проблем при использовании этих функций. При копировании и редактировании этих похожих формул убедитесь, что аргументы отсортированы в правильном порядке.
Равное количество строк и столбцов для аргументов, указывающих диапазоны ячеек	Criteria_range должен иметь такое же количество строк и столбцов, что и Sum_range.

Функция СУММЕСЛИ при условии неравенства

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

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



2. Начните запись, указав это в поле ввода, написав СУММЕСЛИ.



3. Создайте открывающую и закрывающую круглые скобки для вставки диапазона выбранных ячеек, например C2: C25. После этого обязательно поставить знак;, что означает окончание темы.



4. Откройте котировки и укажите в них условие, которое в нашем случае будет> 300000.



5. Как только вы нажмете клавишу Enter, функция активируется. На снимке экрана ниже показано, что только две ячейки соответствуют условию> 300000, поэтому формула суммирует их числа и отображает их в отдельном блоке.

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

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

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

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

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

Как СУММЕСЛИМН работает с датами?

Если вы хотите выбрать и добавить некоторые индикаторы в определенный временной диапазон на основе текущей даты, используйте функцию СЕГОДНЯ () в ваших ограничениях, как показано ниже.

Следующая формула суммирует числа в столбце D, если соответствующая дата в столбце A приходится на последние 7 дней, включая сегодняшний день (при условии, что сегодня 7 февраля):

= СУММ (G2: D21; A2: A21; «<=» & СЕГОДНЯ (); A2: A21; «> =» & СЕГОДНЯ () — 6)

Комментарий. При использовании другой функции Excel в сочетании с логическим оператором для создания ограничения необходимо использовать амперсанд (&) для объединения всего выражения в виде текста, например «<=» & TODAY().

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

= СУММЕСЛИ (A2: A21, «> =» & СЕГОДНЯ () — 6; D2: D21) — СУММЕСЛИ (A2: A21; «<=» & СЕГОДНЯ (); D2: D21)

Однако СУММЕСЛИМН делает добавление намного проще и понятнее, не так ли?

Выборочное суммирование по условию в Эксель

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

Сама таблица выглядит так.

5

Для определения конечного результата нам понадобится такая формула.

= (СУММЕСЛИ (G2: D14; A2: A14; «= июнь»; B2: B14; «Продукт_2»; C2: C14; «Казахстан») + (СУММЕСЛИ (G2: D14; A2: A14; «= август»; B2: B14; «Товар_2»; C2: C14; «Казахстан»)))

В результате расчетов по этой формуле получаем следующий результат.

4

Внимание! Эта формула выглядит довольно здорово, хотя мы использовали только два критерия. Если диапазон данных такой же, вы можете значительно уменьшить длину формулы, как показано на скриншоте ниже.

Альтернативный вариант

Альтернативный вариант для всех 4 задач — использовать автоматический фильтр .

Для решения 3-й задачи таблица с настроенным автоматическим фильтром выглядит так (см. Пример файла Лист 2 Дата).

Во-первых, таблица должна быть преобразована в формат таблицы MS EXCEL 2007 и включать строку итогов.

Описание функции

Сумма чисел, соответствующих определенным критериям, получается с помощью оператора summw в Excel. Примеры, если рассматривать их, ясно покажут, как правильно использовать эту функцию и избегать ошибок.

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

Синтаксис функции следующий: = summw (диапазон_суммы; диапазон_условий1; условие1; диапазон_условий2; условие2; …)

Диапазон суммирования — это массив, ячейки которого будут добавлены, если они соответствуют следующим условиям. Другой блок аргументов в синтаксисе — диапазон_критериев1; условие1. Они позволяют выбрать нужные ячейки в определенном массиве на основе первого фактора, которые затем суммируются в пределах начального диапазона. Дополнительными критериями являются следующие критерии_диапазона10; условие 10.

Как работает эта формула

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

Давайте проанализируем формулу, чтобы понять, как она работает:

• Первым делом указываем диапазон с данными о продажах (B2: B28), между которыми мы должны выбрать, какие значения мы будем добавлять
• Далее мы указываем диапазон данных, к которому будет применяться элемент управления. В нашем случае это диапазон с датами (A2: A28)
• На следующем шаге мы устанавливаем условие относительно диапазона дат, в соответствии с которым формула должна определять, какие данные нужно суммировать. Мы указали первое условие, что дата должна быть больше или равна 01.06.2018
• На заключительном этапе мы устанавливаем второе условие для диапазона с датами (A2: A28), согласно которому формула должна суммировать данные за период меньше или равный 15.06.2018

В результате функция суммирует значения в диапазоне с 1 по 15 июня 2018 года.

Распространенные неполадки

Проблема

Описание

Вместо ожидаемого результата отображается 0 (ноль).	Если вы ищете текстовые значения, такие как имя человека, убедитесь, что значения для Condition1, 2 заключены в кавычки.
Недопустимый результат возвращается, если диапазон ячеек, указанный Sum_Range, содержит TRUE или FALSE.	Значения ИСТИНА и ЛОЖЬ в диапазоне ячеек, заданном параметром Sum_Range, оцениваются по-разному, что может привести к неожиданным результатам при сложении. Ячейки в Sum_range, для которых установлено значение TRUE, оцениваются как 1. Ячейки, которым присвоено значение FALSE, оцениваются как 0 (ноль).

Особенности применения функции СУММЕСЛИМН

Есть несколько особенностей использования этой функции, на которые нужно обратить внимание. Прежде всего, эта функция игнорирует диапазоны с текстовыми строками или пустыми значениями, поскольку эти типы данных не могут быть добавлены арифметически, а только объединены как строки. Эта функция не может этого сделать. Также необходимо обратить внимание на следующие условия:

1. вы можете использовать эти типы значений в качестве условий для выбора ячеек для дальнейшего добавления содержащихся в них значений: числовые значения, логические выражения, ссылки на ячейки и т.д.
2. Если отмечены текст, логические выражения или математические знаки, эти критерии указываются в кавычках.
3. Условия, длина которых превышает 255 символов, использовать нельзя.
4. вы можете использовать грубые критерии для выбора значений с использованием подстановочных знаков. Знак вопроса используется для замены символа, а знак умножения (звездочка) используется для замены нескольких символов.
5. Логические значения, находящиеся в диапазоне суммирования, автоматически преобразуются в числовые значения в зависимости от их типа. Следовательно, значение «ИСТИНА» становится единицей, а «ЛОЖЬ» — нулем.
6. Если в ячейке появляется ошибка #VALUE!, Это означает, что количество ячеек в диапазоне условий и сумм различается. Вам нужно убедиться, что размеры этих аргументов совпадают.

Использование СУММЕСЛИМН и СУММЕСЛИ в Excel — что нужно запомнить?

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

Хотя общая часть понятна — схожее назначение и параметры — различия не так заметны, хотя и весьма существенны.

1. Порядок аргументов

Аргументы применяются по-разному. В частности, диапазон_суммирования — это первый параметр в СУММЕСЛИ, но это третий параметр в СУММЕСЛИ.

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

Однако в SUMIFN это очень важно и обязательно, поэтому оно стоит на первом месте. Ребята из Microsoft, наверное, подумали, что после добавления десятого или сотого интервала / пары критериев кто-то может забыть указать интервал для добавления:)

2. Диапазон суммирования и область критериев должны быть одинакового размера

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

Выражение = СУММЕСЛИ (E2: E21; C2: C21; I2; F2: F22; I3) вернет #VALUE! Сообщение об ошибке, потому что второй параметр поиска (F2: F22) не того же размера, что и остаток (E2: E21) и (C2: C21).

Хорошо, хватит стратегии (т.е теории), перейдем к тактике (примерам).

Функция СУММЕСЛИ при условии соответствия тексту

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

1. На этот раз, помимо диапазона суммируемых ячеек, определите те, в которых есть метки, соответствующие условию.



2. Начните писать функцию, назовите ее так, как было показано выше.



3. Первым делом введите диапазон меток, поставьте; и установите условие. Итак, это синтаксическое выражение примет что-то вроде этого: A2: A25; «Сентябрь»;.



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



5. Проверим действие функции. Мы устанавливаем в качестве условия сентябрь месяц и наблюдаем за таблицей, что два значения ячеек таблицы, которые соответствуют ей, были добавлены. Остальные данные при проверке просто игнорируются.

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

Задача2 (2 числовых критерия)

Другой задачей может быть поиск сумм коробок только для тех партий товаров, у которых количество коробок попадает в определенный диапазон, например от 5 до 20 (см. Пример файла Sheet 2Numbers).

Формулы построены аналогично задаче 1: = СУММЕСЛИ (B2: B13; B2: B13; «> =» & D2; B2: B13; «<=» & E2)

Примечание: для удобства строки, участвующие в суммировании, выделены условным форматированием с правилом = И ($ B2> = $ D $ 2; $ B2 <= $ E $ 2)

Задача3 (2 критерия Дата)

Другой задачей может быть определение общих продаж за период (см. Пример файла Листа «2 даты»). Давайте воспользуемся другой исходной таблицей со столбцами «Дата продажи» и «Сумма продаж .

Формулы строятся аналогично задаче 2: = СУММЕСЛИ (B6: B17; A6: A17; «> =» & D6; A6: A17; «<=» & E6)

Примечание. Даты могут быть представлены в числовом формате, см. Статью Как Excel сохраняет дату и время

При необходимости даты можно ввести непосредственно в формулу = СУММЕСЛИ (B6: B17; A6: A17; «> = 15.01.2010»; A6: A17; «<= 06.03.2010»)

Чтобы отобразить условия выбора в текстовой строке, используйте формулу = «Объем продаж за период от» & ТЕКСТ (D6; «дд.ММ.гг») & «до» & ТЕКСТ (E6; «дд.ММ .я»)

Последняя формула использует собственный формат .

Использование операторов сравнения.

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

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

= СУММЕСЛИ (MI2: MI21; SI2: SI21; «Красный»; RE2: RE21; «> 100”)

Но рациональнее использовать ссылки, как это сделано на рисунке:

= СУММЕСЛИ (MI2: MI21; SI2: SI21; I2; RE2: RE21; I4)

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

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

Как еще мы можем решить нашу проблему?

Способ 2. Используем функцию СУММПРОИЗВ.

Давайте подробнее рассмотрим, как работает SUMPRODUCT():

= СУММПРОИЗВ (- (B2: B21 = $ I $ 12), — (D2: D21> I13), E2: E21)

Результат вычисления B2: B21 = $ I $ 12 — это массив

{ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ.: ЛОЖЬ

ИСТИНА означает, что код покупателя соответствует условию, например, слово Красный. Этот массив можно увидеть, выбрав в строке формул B2: B21 = $ I $ 12, а затем нажав F9.

А что это за странные минусовые знаки перед этими выражениями? Дело в том, что нам нужны не эти логические выражения, а числа, чтобы затем их можно было умножить и сложить. Если Excel выполняет математическую операцию с логическим выражением, он автоматически преобразует его в число. А знак минус означает умножение на -1. А если умножить на -1 дважды, результат не изменится. Мы помним это из школьной математики

И в результате логический массив превратится в массив чисел {0: 1: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 1: 0: 0: 0: 1: 0: 0: 0}.

Результат вычисления D2: D21> I13 — это массив

{ИСТИНА: ИСТИНА: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ}.

ИСТИНА соответствует пределу «количество больше 100». Здесь мы также используем двойное отрицание для преобразования логических значений в числа.

И, наконец, результатом вычисления B2: B13 является массив {11250: 23210: 12960: 3150: 5280: 9750: 3690: 18300: 5720: 6150: 8400: 2160: 7200: 1890: 17050: 3450: 15840: 2250 : 7200: 8250}, т.е только числа из столбца E.

Результатом умножения этих трех массивов на элемент будет {0: 23210: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 15840: 0: 0: 0 }. Складываем эти продукты и получаем 39050.

Способ 3. Формула массива.

И еще один вариант расчета — применяем формулу массива. В I14 пишем:

= СУММ ((L2: L21 = I12) * (L2: L21> I13) * (L2: E21))

Не забудьте нажать CTRL + SHIFT + ENTER в конце, чтобы обозначить это выражение как формулу массива. Программа автоматически добавит фигурные скобки в начало и в конец. Опять получаем результат 39050.

Способ 4. Автофильтр.

Другой альтернативой является использование автоматического фильтра. Для этого преобразуйте диапазон данных A1: F21 в интеллектуальную таблицу. Напоминаю, что для этого в меню «Главная» выберите «Форматировать как таблицу». Далее добавляем итоговую строку (вкладка «Дизайн») и устанавливаем необходимые фильтры.

Без формул будет определено общее количество отфильтрованных строк.