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

Excel — невероятно функциональная программа. Даже встроенного набора функций достаточно для выполнения практически любой задачи. И кроме стандартных, знакомых многим, есть и такие, о которых мало кто слышал. Но при этом они не перестают быть полезными. У них более узкая специализация, и необходимость в них есть не всегда. Но если о них знать, то в критический момент они могут быть очень полезны.

Сегодня мы поговорим об одной из таких функций — СУММЕСЛИМН.

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

Подробное описание функции SUMIFS

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

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

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

Сам синтаксис:

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

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

Рассмотрим аргументы подробнее:

1. Сумма_диапазона. Этот аргумент, а также диапазон условий 1 и 1 являются обязательными. Это набор ячеек, которые необходимо суммировать.
2. Условие_диапазон1. Это диапазон, в котором будет проверяться условие. Он соединен со следующим аргументом — Condition1. Суммирование значений, соответствующих критерию, осуществляется внутри ячеек, указанных в предыдущем аргументе.
3. Условие1. Этот аргумент определяет критерии, по которым будет осуществляться проверка. Его можно задать, например, так: «>32».
4. Диапазон условий 2, Условия 2… Здесь следующие условия задаются таким же образом. Если необходимо указать более нескольких условий, добавляются аргументы «Диапазон условий 3» и «Условие 3». Синтаксис одинаков для следующих аргументов.

Функция позволяет максимально обрабатывать до 127 пар условий и диапазонов. 

Вы можете использовать его сразу в нескольких сферах (приведем лишь некоторые, список на самом деле еще длиннее):

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

Как видите, спектр применения этой функции очень широк. Но это не единственное его достоинство. Давайте рассмотрим еще несколько преимуществ этой функции:

1. Возможность задать несколько критериев. Почему это преимущество? Вы можете использовать обычную функцию СУММЕСЛИ! А все потому, что это удобно. Нет необходимости проводить отдельные расчеты по каждому из критериев. Все действия можно запрограммировать заранее, еще раньше. как будет формироваться таблица данных. Это отличная экономия времени.
2. Автоматизация. Современная эпоха – это эпоха автоматизации. Много зарабатывать сможет только тот человек, который умеет правильно автоматизировать свою работу. Именно поэтому умение владеть Excel и функцией СУММЕСЛИМН в частности, так важно для любого человека, который хочет построить карьеру. Знание одной функции позволяет выполнять сразу несколько действий, как одно. И здесь мы переходим к следующему преимуществу этой функции.
3. Экономия времени. Как раз из-за того, что одна функция выполняет сразу несколько задач.
4. Простота. Несмотря на то, что синтаксис на первый взгляд довольно тяжелый из-за своей громоздкости, на самом деле логика этой функции очень проста. Сначала выбирается диапазон данных, затем диапазон значений, который будет проверен на соответствие определенному условию. И конечно, само условие тоже должно быть указано. И так несколько раз. Фактически эта функция основана всего на одной логической конструкции, что делает ее проще известной ВПР несмотря на то, что его можно использовать для тех же целей, еще и с учетом большего количества критериев. 

Особенности использования функции СУММИФС

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

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

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

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

Динамический диапазон суммирования условий

Итак, начнем с первого примера. Допустим, у нас есть таблица, содержащая информацию о том, как учащиеся справляются с учебной программой по тому или иному предмету. Имеется набор оценок, успеваемость оценивается по 10-балльной шкале. Задача — найти оценку за экзамен тем ученикам, фамилия которых начинается на букву А, а минимальный балл — 5.

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

Чтобы рассчитать общий балл на основе описанных выше критериев, нам необходимо применить следующую формулу.

Опишем аргументы подробнее:

1. C3:C14 — наш диапазон суммирования. В нашем случае он совпадает с диапазоном условий. Из него будут выбраны точки, используемые для расчета суммы, но только те, которые попадают под наши критерии.
2. «>5» — наше первое условие.
3. B3:B14 — это второй диапазон суммирования, который обрабатывается для соответствия второму критерию. Мы видим, что совпадения с диапазоном суммирования нет. Отсюда мы заключаем, что диапазон суммирования и диапазон условия могут совпадать, а могут и не совпадать. 
4. «А*» — второй диапазон, который определяет подбор оценок только для тех студентов, фамилия которых начинается на букву А. В нашем случае звездочка означает любое количество символов. 

После вычислений получаем следующую таблицу.

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

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

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

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

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

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

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

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

Функция СУММИФС для суммирования значений по нескольким условиям

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

Используем следующую формулу (но запишем ее как формулу массива, то есть вводим через комбинацию клавиш CTRL+SHIFT+ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

После функции СУММЕСЛИМН просуммирует массив значений на основе критериев, указанных в формуле (то есть стран Китая и Грузии), полученный массив суммируется обычной функцией СУММА, который записывается как формула массива.

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

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

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

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