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

Функция СУММЕСЛИ - как суммировать ячейки в Excel при большом количестве условий?

В этом руководстве объясняются различия в синтаксисе и использовании СУММЕСЛИ и СУММЕСЛИ, а также приводятся примеры формул для суммирования значений с несколькими критериями в Excel 2016, 2013, 2010, 2007, 2003 и более ранних версиях.

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

Те, кто знаком с функцией СУММЕСЛИ, могут подумать, что для ее преобразования в СУММЕСЛИ требуются только буквы «MH» и некоторые дополнительные критерии. Это может звучать логично .. но "логично" не всегда при работе с Microsoft:)

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

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

По сравнению с СУММЕСЛИ, синтаксис СУММЕСЛИМН немного сложнее:

СУММЕСЛИ (диапазон_суммы, диапазон_условия1, условие1, [диапазон_условия2, условие2],…)

Первые 3 аргумента являются обязательными, но необязательные диапазоны и связанные условия не являются обязательными.

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

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

condition1 - первое обязательное условие, которое должно быть выполнено. Вы можете указать его как число, логическое выражение, ссылку, текст или другую функцию Excel. Например, вы можете использовать такие критерии, как 10, «> = 10», A1, «яблоко» или СЕГОДНЯ().

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рассчитываем стоимость заказов для «Красного» покупателя, в которых было более 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: D21> I13) * (L2: E21))

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

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

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

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

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

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

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

= СУММ (G2: D21; A2: A21; " =" & СЕГОДНЯ () - 6)

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

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

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

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

Суммирование по пустым и непустым ячейкам.

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

Теперь давайте посмотрим, как можно использовать формулу СУММЕСЛИМН с «пустыми» и «непустыми» ячейками для реальных данных.

Для «Красного» покупателя рассчитаем количество товаров в незавершенных заказах. Для этого в столбце B ищем имя соответствующего клиента, а в F - пустую ячейку. Если обе потребности одинаковы, добавьте количество из столбца D.

= СУММЕСЛИ (RE2: RE21; F2: F21; ""; SI2: SI21; "Красный")

или

= СУММЕСЛИ (RE2: D21; F2: F21; "="; B2: B21, "Красный")

Каждое из этих выражений дает правильный результат: 144 единицы в порядке 4 февраля.

Сумма нескольких условий.

Теперь посчитаем общую стоимость выполненных заказов по двум позициям.

Если мы просто добавим второй критерий к I3, а вместо I2 будем использовать область I2: I3, то расчет будет неверным, потому что в C2: C21 мы будем искать товар, в названии которого присутствуют как «черные» И «молоко» заодно. Ведь таких людей просто нет.

Поэтому первый вариант расчета выглядит следующим образом:

= СУММЕСЛИ (MI2: MI21; DO2: DO21; I2; V2: V21; I4) + СУММЕСЛИ (MI2: MI21; DO2: DO21; I3; V2: V21; I4)

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

Второй вариант: используем элемент массива критериев и функцию СУММ.

= СУММ (СУММЕСЛИ (E2: E21; C2: C21; {"* молоко *"; "* черный *"}; F2: F21; I4))

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