Функция СУММЕСЛИ — как суммировать ячейки в 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; «
Однако СУММЕСЛИМН делает добавление намного проще и понятнее, не так ли?
Суммирование по пустым и непустым ячейкам.
При анализе отчетов и других данных часто может возникнуть необходимость суммировать данные, соответствующие пустым или непустым ячейкам в таблице.
| Критерии | Описание | Содержание | |
| Пустые ячейки | «=» | Складывает числа, соответствующие пустым, которые не содержат абсолютно ничего, ни формулы, ни строки нулевой длины. | = СУММЕСЛИ (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) * (DLSTR (A2: A10)> 0) * (DLSTR (B2: B10)> 0))} Если столбцы A и B содержат текст ненулевой длины, соответствующий номер из C. Внимание! Это формула массива! Подтяжки вставлять не обязательно! |
Теперь давайте посмотрим, как можно использовать формулу СУММЕСЛИМН с «пустыми» и «непустыми» ячейками для реальных данных.
Для «Красного» покупателя рассчитаем количество товаров в незавершенных заказах. Для этого в столбце 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))
Как видите, результаты такие же. Выберите тот способ, который вам будет проще и понятнее. 