Для чего нужна функция СУММЕСЛИ в Excel (примеры применения)

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

Общие сведения о функции СУММЕСЛИ

Как видно из названия, эта функция осуществляет суммирование значений, соответствующих определенному условию. Она позволяет заменить сложную формулу одной функцией.

Синтаксис функции СУММЕСЛИ

Эта функция имеет следующие аргументы:

1. Диапазон поиска. Это набор ячеек, которые будут проверяться на предмет соответствия условиям.
2. Условие. Этот аргумент указывается в кавычках. Это непосредственно тот критерий, при котором ячейки будут суммироваться.
3. Диапазон суммирования. Если нужно проверить определённые ячейки на предмет соответствия указанному критерию, используется этот аргумент. Он необязательный. Если его не указывать, суммирование будет осуществляться в диапазоне поиска.

Как работает функция СУММЕСЛИ в Эксель

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

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

Давайте опишем аргументы.

1. В качестве диапазона поиска используется колонка с должностями. Конечно, заголовок колонки не входит в него.
2. Используемое условие – продавец. Не стоит забывать заключать этот аргумент в кавычки.
3. В нашем случае в качестве диапазона суммирования используется заработная плата. Соответственно, значение этого аргумента – F2:F14.

   

Получившийся результат – 92900. Согласитесь, что это значительно удобнее, чем вручную анализировать список профессий, проверять их на соответствие определенному условию, а потом ещё и суммировать эти значения. Особенно, если таблица большая.

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

Функция СУММЕСЛИ в Excel с несколькими условиями

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

Синтаксис

Возможно использование неограниченного количества аргументов, но минимально необходимо указать 5 штук.

1. Диапазон суммирования. Здесь это главный аргумент, который нужно обязательно указывать. Значение то же самое – указание конкретных ячеек, которые нужно просуммировать.
2. Диапазон первого условия. Аргумент, эквивалентный диапазону поиска в функции СУММЕСЛИ. Только в этом случае указывает диапазон первого критерия.
3. Первое условие.
4. Диапазон второго условия. Аргументы, аналогичный диапазону первого условия.
5. Второе условие.

Дальше логика такая же. Указывается диапазон поиска, а потом непосредственно критерий. Таким образом, по мере увеличения количества критерии, количество аргументов увеличивается в арифметической прогрессии, где шаг равен двум: 5,7,9,11 и так далее.

Пример применения

Допустим, нам нужно посчитать сумма зарплаты за месяц для всех женщин-продавцов, которые соответствуют двум критериям:

1. Они являются женщинами.
2. Они являются продавцами.

Следовательно, для реализации этой задачи нужно применять функцию СУММЕСЛИМН.

В нашем случае аргументы будут следующими:

1. В качестве диапазона суммирования оставляем тот же диапазон, что и в прошлом примере (поскольку там содержатся зарплаты).
2. Диапазон условия 1 – профессия работника. 
3. Условие 1 – продавец.
4. Диапазон условия 2 – пол работника.
5. Условие 2 – женский

   

Вот так просто оно работает на практике. Общая сумма денег, которая была получена составила 51100 рублей.

Функция СУММЕСЛИ с динамическим условием

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

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

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

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

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

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

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

Сумма по нескольким условиям

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

Предположим, у нас есть таблица с заказами. И наша задача – определить общее количество заказов шоколада разных видов.

Первый метод – использование двух функций СУММЕСЛИ.

Нами была использована формула =СУММЕСЛИ($C$2:$C$21;»*»&H3&»*»;$E$2:$E$21)+СУММЕСЛИ($C$2:$C$21;»*»&H4&»*»;$E$2:$E$21)

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

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

Такой метод позволяет обрабатывать сразу большое количество данных и критериев. В этом случае в качестве аргумента нужно указать сразу целый массив условий. Давайте более подробно рассмотрим этот способ.

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

=СУММЕСЛИ($C$2:$C$21;{«*черный*»;»*молочный*»};$E$2:$E$21)

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

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

=СУММ(СУММЕСЛИ($C$2:$C$21;{«*черный*»;»*молочный*»};$E$2:$E$21))

Как видим, результаты вычисления в обоих случаях одинаковые.

Еще один вариант использования нескольких критериев – сочетание функций СУММЕСЛИ и СУММПРОИЗВ. Это дает возможность перечислять условия в отдельной части диапазона.

Формула будет такой.

=СУММПРОИЗВ(СУММЕСЛИ(C2:C21;H3:H4;E2:E21))

Как видим из этой формулы, критерии записываются в ячейки с адресами H3 и H4. Но также возможно использование массива критериев, как в предыдущем примере. 

=СУММПРОИЗВ(СУММЕСЛИ(C2:C21;{«*черный*»;»*молочный*»};E2:E21))

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

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

Сумма значений, соответствующих пустым или непустым ячейкам

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

Чтобы учесть непустые ячейки, в качестве критерия используется символ звездочка (*). В нашем случае формула будет следующей.

=СУММЕСЛИ(F2:F21;»*»;E2:E21)

Аналогичный итог можно получить, если использовать такой набор символов – <>.

=СУММЕСЛИ(F2:F21;»<>»;E2:E21)

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

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

=СУММЕСЛИ(F2:F21;»»;E2:E21)

Точная дата, диапазон дат

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

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

Здесь также можно использовать ссылку на аргумент или вписать его непосредственно в формулу.

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

=СУММЕСЛИ(A2:A21;СЕГОДНЯ();E2:E21)

Если использовать в аргументе СЕГОДНЯ()-1, то в качестве критерия будет использоваться «вчера».

Подстановочные знаки для частичного совпадения

Иногда нужно искать не целую фразу, а ее часть. Для этого существуют такие символы, которыми заменяется определенная часть аргумента.

1. ? – замена любого символа.
2. * – замена какого-угодно количества символов.

Например, три знака вопроса соответствует любому слову, которое состоит из трех букв.

Чтобы в качестве аргумента использовать значение, содержащее сами символы ? и *, то нужно перед ними написать знак ~ . Тогда они будут считаться обычным текстом, а не шаблоном.

Критерии для текста

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

=СУММЕСЛИ(F2:F21;I2;E2:Е21)

Не стоит забывать все текстовые значения заключать в кавычки.

Сумма если «больше чем», «меньше», «равно»

В таком случае перед аргументом, заключенным в кавычки, нужно написать соответствующий логический оператор. Например, так.

=СУММЕСЛИ(D2:D21;»<144″)

В этом примере суммироваться будут все заказы, количество которых меньше 144. Указывать непосредственно «равно» нет смысла, потому что такой логический оператор применяется по умолчанию.

Почему функция СУММЕСЛИ может не работать?

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

1. Диапазоны должны указываться в виде ссылок на диапазон, а не массива. Эта ошибка бывает довольно редко, но это возможно, поскольку многие новички не полностью понимают разницу между диапазоном и массивом.
2. Если суммируются значения из других листов или книг, которые закрыты на момент использования формулы.
3. Диапазон данных и поиска отличаются по размеру.

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