Суммировать значения в соответствии с несколькими условиями можно различными способами. В статье объясняется чем отличаются в Excel функция СУММЕСЛИ (SUMIF) и функция СУММЕСЛИМН (SUMIFS) с точки зрения их синтаксиса и использования, а также приводятся примеры как формула СУММЕСЛИМН суммирует значения по нескольким условиям в таблицах Excel.
В этой статье вы найдете:
- Подробные инструкции по работе с СУММЕСЛИМН.
- Примеры использования функции.
- Советы и рекомендации.
Как известно, Microsoft Excel предоставляет множество функций для выполнения различных расчетов с данными. Мы уже рассмотрели функцию СУММЕСЛИ, которая суммирует числа, соответствующие указанным критериям. Теперь пришло время перейти к расширенной версии этой функции – СУММЕСЛИМН, которая позволяет найти сумму сразу по нескольким условиям.
Те, кто знаком с функцией СУММЕСЛИ, могут подумать, что преобразование ее в СУММЕСЛИМН потребует лишь букв "МН" и некоторого количества дополнительных критериев. Это может показаться вполне логичным… но «логично» - это то, что не всегда имеет место при работе с Microsoft:)
Как работает функция СУММЕСЛИМН?
При помощи функции СУММЕСЛИМН можно найти сумму величин, для которых есть много условий. Она появилась впервые в MS Excel 2007, поэтому вы можете использовать ее во всех современных версиях программы.
По сравнению с СУММЕСЛИ, здесь синтаксис немного сложнее:
СУММЕСЛИМН(диапазон_суммирования, диапазон_условия1, условие1, [диапазон_условия2, условие2],…)
Первые 3 аргумента являются обязательными, а вот дополнительные диапазоны и связанные с ними условия являются необязательными.
диапазон_суммирования - нужно указать диапазон для суммирования. Это может быть отдельная клеточка, область или именованный диапазон. Суммируются только ячейки с чисами; пустые и текстовые значения игнорируются.
диапазон_условия1 - обязательный первый диапазон ячеек, который мы будем проверять на соответствие условию.
условие1 - обязательное первое условие, которое должно быть выполнено и по которому оценивается первый диапазон ячеек. Вы можете предоставить его в виде числа, логического выражения, ссылки, текста или другой функции Excel. Например, вы можете использовать такие критерии, как 10, "> = 10", A1, "яблоко" или СЕГОДНЯ().
Все, что следует далее - это дополнительные диапазоны ячеек и связанные с ними условия. Они не являются обязательными, но если у вас только одно ограничение, то зачем вам эта функция? Просто используйте СУММЕСЛИ. Тем не менее, вы можете использовать до 127 пар диапазон/условие.
Важно! Функция СУММЕСЛИМН в Excel работает с логикой «И». Это означает, что число в диапазоне суммирования учитывается, только если оно удовлетворяет всем указанным условиям (все требования соблюдаются для этой ячейки).
Как строится формула СУММЕСЛИМН - что нужно помнить
Поскольку целью этого руководства является охват всех возможных способов суммирования значений по большому количеству ограничений, мы обсудим примеры выражений с обеими функциями - СУММЕСЛИМН и СУММЕСЛИ с несколькими условиями. Чтобы использовать их правильно, вам необходимо четко понимать, что общего между ними и чем они отличаются.
Хотя общая часть ясна - схожий пункт назначения и параметры – но различия не так очевидны, хотя и достаточно существенны.
1. Порядок аргументов
Аргументы применяются по-разному. В частности, диапазон сумирования является первым аргументом в СУММЕСЛИ, но только третьим в функции СУММЕСЛИМН.
На первый взгляд может показаться, что Microsoft намеренно усложняет процесс обучения для своих пользователей. Однако при ближайшем рассмотрении вы увидите причины этого. Дело в том, что этот диапазон суммирования является необязательным в СУММЕСЛИ. Если вы его опустите, то нет никаких проблем, ваша формула будет складывать в диапазоне поиска (первый параметр).
В функции СУММЕСЛИМН в Excel он, напротив, очень важен и обязателен, и поэтому и стоит первым. Вероятно, ребята из Microsoft подумали, что после добавления 10- й или 100- й пары диапазон/критерий кто-то может забыть указать диапазон для суммирования:)
2. Диапазон суммирования и область критериев должны быть одинакового размера
В функции СУММЕСЛИ эти аргументы не обязательно должны иметь одинаковую размерность. Достаточно указать начальную точку. Формула СУММЕСЛИМН требует, чтобы все аргументы содержали одинаковое количество строк и столбцов.
Выражение =СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F22;I3) вернет сообщение об ошибке #ЗНАЧ!, так как второй параметр поиска (F2:F22) не совпадает по размеру с остальными (E2:E21) и (C2:C21).
Хорошо, хватит стратегии (т.е. теории), давайте перейдем к тактике (к примерам).
Формула СУММЕСЛИМН - пример с множеством условий
Имеются условные данные о заказах и продаже шоколада. Подсчитаем итог совершённых продаж по молочному шоколаду. То есть, у нас два требования: должно совпадать наименование товара и в колонке «Выполнен» должно быть указано «Да».
Первым аргументом мы указываем диапазон суммирования E2:E21, а затем попарно – диапазон условия и само условие для него.
=СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F21;I3)
В C2:C21 будем искать слово «молочный» с любым его вхождением. То есть, до и после него могут быть еще любые другие символы.
В F2:F21 ищем «Да», то есть отметку о том, что заказ выполнен.
Если ОБА эти требования выполняются, то такой заказ нам подходит, и его стоимость мы учтём.
Как видите, формула СУММЕСЛИМН обнаружила 2 совпадения, в которых был продан молочный шоколад.
Использование операторов сравнения для чисел
Рассчитаем по покупателю «Красный» стоимость заказов, в которых было более 100 единиц товара. Как видим, здесь нужно использовать и текстовый, и числовой критерий.
Критерии можно записать в саму формулу СУММЕСЛИМН, и выглядеть это будет так:
=СУММЕСЛИМН(E2:E21;B2:B21;”Красный”;D2:D21;”>100”)
Но более рационально использовать ссылки на ячейки:
=СУММЕСЛИМН(E2:E21;B2:B21;I2;D2:D21;I4)
Примечание. Обратите внимание, что логические выражения с операторами сравнения всегда должны быть заключены в двойные кавычки ("").
Функция СУММЕСЛИМН работает с числами, текстом и датами точно так же, как и СУММЕСЛИ. Поэтому рекомендую обратиться к нашему предыдущему материалу об условном суммировании.
Сумма в интервале дат
Если вы хотите отобрать и сложить какие-то показатели в определенном временном интервале на основе текущей даты, используйте функцию СЕГОДНЯ() в ваших условиях, как это показано ниже.
Следующая формула суммирует числа в столбце D, если соответствующая дата в столбце А попадает в последние 7 дней, включая сегодняшний день (предполагается, что сегодня 7 февраля):
=СУММЕСЛИМН(D2:D21;A2:A21;"<="&СЕГОДНЯ();A2:A21;">="&СЕГОДНЯ()-6)
Замечание. Когда вы при составлении условия используете другую функцию Excel вместе с логическим оператором, нужно использовать амперсанд (&) для объединения всего выражения в виде текста, например "<="&СЕГОДНЯ().
Аналогичным образом может быть использована функция Excel СУММЕСЛИ для суммирования каких-то показателей в заданном диапазоне дат. Например, это выражение также решит нашу задачу:
=СУММЕСЛИ(A2:A21;">="&СЕГОДНЯ()-6;D2:D21) - СУММЕСЛИ(A2:A21;"<="&СЕГОДНЯ();D2:D21)
Однако, функция СУММЕСЛИМН в Excel сложение делает гораздо проще и понятнее, не так ли?
Суммирование по пустым и непустым ячейкам.
При анализе отчетов и других данных вам часто может потребоваться суммировать данные, соответствующие пустым или непустым клеткам таблицы.
| Критерии | Описание | Содержание | |
| Пустые ячейки | "=" | Суммируйте числа, соответствующие пустым, которые не содержат абсолютно ничего - ни формулы, ни строки нулевой длины. | =СУММЕСЛИМН(C2:C10;A2:A10;"=";B2:B10,"=") Суммируйте в C2:C10, если соответствующие ячейки в столбцах A и B абсолютно пусты. |
| «» | Суммируйте числа, соответствующие «визуально» пустым, включая те, которые содержат пустые строки, возвращаемые какой-либо другой функцией Excel (например, ячейки с формулой вроде = ""). | =СУММЕСЛИМН(C2:C10;A2:A10;"";B2:B10,"") Суммируйте в C2:C10 с теми же параметрами, что и в приведенной выше формуле, но с пустыми строками. | |
| Непустые ячейки | "<>" | Суммируйте числа, соответствующие непустым, включая строки нулевой длины. | =СУММЕСЛИМН(C2:C10;A2:A10;"<>";B2:B10,"<>") Суммируйте в C2: C10, если соответствующие ячейки в столбцах A и B не пусты, включая ячейки с пустыми строками. |
| Суммируйте числа, соответствующие непустым, не включая строки нулевой длины. | =СУММ(C2:C10)-СУММЕСЛИМН(C2:C10;A2:A10;"";B2:B10,"") или {=СУММ((C2:C10)*(ДЛСТР(A2:A10)>0)*(ДЛСТР(B2: B10)>0))} Если в столбцах A и B содержится текст ненулевой длины, тогда соответствующее число из C складывается. Внимание! Это формула массива! Фигурные скобки вводить не нужно! |
А теперь давайте посмотрим, как может быть использована формула СУММЕСЛИМН в Excel с «пустыми» и «непустыми» ячейками для реальных данных.
По покупателю «Красный» рассчитаем количество товара в невыполненных заказах. Для этого в столбце B ищем соответствующее название клиента, а в F – пустую ячейку. Если оба требования совпадают, складываем количество товара из столбца D.
=СУММЕСЛИМН(D2:D21;F2:F21;"";B2:B21;"Красный")
или
=СУММЕСЛИМН(D2:D21;F2:F21;"=";B2:B21;"Красный")
Каждое из этих выражений дает верный результат – 144 единицы в заказе от 4 февраля.
Несколько условий И и ИЛИ в СУММЕСЛИМН
А теперь давайте рассчитаем общую стоимость выполненных заказов по двум товарам.
Если мы просто добавим второй критерий в I3, и вместо I2 используем область I2:I3, то расчет будет неверным, поскольку в C2:C21 будем искать товар, в названии которого есть И «черный», И «молочный» одновременно. Ведь таких просто нет.
Поэтому первый вариант расчета таков:
=СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F21;I4)+СУММЕСЛИМН(E2:E21;C2:C21;I3;F2:F21;I4)
Просто складываем выполненные заказы сначала по первому, а затем по второму наименованию.
Второй вариант: используем элемент массива критериев и функцию СУММ.
=СУММ(СУММЕСЛИМН(E2:E21;C2:C21;{"*молочный*";"*черный*"};F2:F21;I4))
Как видите, результаты получены одинаковые. Выбирайте способ, который будет для вас проще и понятнее.
Альтернативный пример суммы с условиями – СУММПРОИЗВ.
На скриншоте ниже вы видите результаты суммирования по двум условиям, которая возвратила функция СУММЕСЛИМН и формула СУММПРОИЗВ.
Разберем подробнее, как работает СУММПРОИЗВ():
=СУММПРОИЗВ(--(B2:B21=$I$12);--(D2:D21>I13);E2:E21)
Результатом вычисления B2:B21=$I$12 является массив
{ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}.
ИСТИНА означает соответствие кода покупателя условию, т.е. слову Красный. Массив этот можно увидеть, выделив в строке формул B2:B21=$I$12, а затем нажав F9.
А что за странные знаки «минус» перед этими выражениями? Дело в том, что нам необходимы не эти логические выражения, а числа, чтобы их затем можно было перемножать и складывать. Если Эксель производит математическую операцию с логическим выражением, то он автоматически преобразует его в число. А знак минус означает умножение на -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». Здесь мы также применяем двойное отрицание, чтобы преобразовать логические переменные в числа.
И, наконец, результатом вычисления В2:В13 является массив {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.
Формула массива для суммы по условиям
И еще один вариант расчета – применим формулу массива. В I14 запишем:
=СУММ((B2:B21=I12)*(D2:D21>I13)*(E2:E21))
Не забудьте в конце нажать комбинацию клавиш CTRL+SHIFT+ENTER, чтобы обозначить это выражение как формулу массива. Фигурные скобки в начале и в конце программа добавит автоматически. Вновь получим результат 39050.
Как найти сумму без формул при помощи автофильтра Excel
Еще один альтернативный вариант – применение автофильтра. Для этого преобразуйте диапазон данных A1:F21 в «умную» таблицу Excel. Напомню, что для этого в меню «Главная» выберите «Форматировать как таблицу». После этого добавьте в нее строку итогов (вкладка «Конструктор») и установите необходимые фильтры.
Без всяких формул итог по отфильтрованным строкам будет определён.
Итак, теперь найти сумму значений по нескольким условиям в Excel при помощи СУММЕСЛИМН или другими способами не будет для выс проблемой.
Почему не работает замена значений условий в формуле "*молочный*";"*черный*" на ссылки на ячейки с тем же текстом?
Если заменить на ссылку на диапазон, то прекрасно работает
=СУММ(СУММЕСЛИМН(E2:E21;C2:C21;I2:I3;F2:F21;I4))
Большое спасибо за статью! Очень подробно и понятно расписано.
Столкнулась с такой проблемой. Ctrl+Shift+Enter не работает для обозначения границ массива при использовании комбинации формул СУММ и СУММЕСЛИМН. Выдаёт ошибку.
При ручном наборе фигурных скобок считает верно. В чём может быть причина?
Эти функции сами по себе умеют работать с массивами. Поэтому использовать для них Ctrl+Shift+Enter нет необходимости. А ввод скобок вручную бесполезен, так как не превращает формулу в формулу массива.