Формула суммы по условию критерия выборки значений в Excel

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

Примеры формулы для суммы диапазонов с условием отбора в Excel

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

Функция СУММЕСЛИ анализирует каждое значение ячейки в диапазоне B2:B12 и проверяет соответствует ли оно заданному условию (указанному во втором аргументе функции). Если значение меньше чем 0, тогда условие выполнено и данное число учитывается в общей итоговой сумме. Числовые значения больше или равно нулю игнорируются функцией. Проигнорированы также текстовые значения и пустые ячейки.

В приведенном примере сначала проверяется значения ячейки B2 и так как оно больше чем 0 – будет проигнорировано. Далее проверяется ячейка B3. В ней числовое значение меньше нуля, значит условие выполнено, поэтому оно добавляется к общей сумме. Данный процесс повторяется для каждой ячейки. В результате его выполнения суммированы значения ячеек B3, B6, B7, B8 и B10, а остальные ячейки не учитываются в итоговой сумме.

Обратите внимание что ниже результата суммирования отрицательных чисел находится формула суммирования положительных чисел. Единственное отличие между ними — это обратный оператор сравнения во втором аргументе где указывается условие для суммирования – вместо строки "<0" (меньше чем ноль) используется строка ">0" (больше чем ноль). Теперь мы можем убедиться в том, что дебет с кредитом сходится балансовое сальдо будет равно нулю если сложить арифметически в ячейке B16 формулой =B15+B14.



Пример логического выражения в формуле для суммы с условием

Другой пример, когда нам нужно отдельно суммировать цены на группы товаров стоимости до 1000 и отдельно со стоимостью больше 1000. В таком случае одного оператора сравнения нам недостаточно (<1000) придется использовать знак «меньше или равно» (<=1000) иначе цены со значением ровно 1000 не будут включены в расчеты. Более того в таком случае мы уже не можем использовать в другой формуле «больше или равно» (>=1000) иначе мы просуммируем сумму ровно в 1000 – 2 раза, что приведет к ошибочным итоговым результатам:

Это очень распространенная ошибка пользователей Excel при работе с логическими функциями!

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

Второй аргумент функции СУММЕСЛИ, то есть условие, которое должно быть выполнено, записывается между двойными кавычками. В данном примере используется символ сравнения – «меньше» (<). По синтаксису функции необходимо представлять такие записи как строка, то есть в двойных кавычках. Иначе Excel выдаст предупреждение об ошибке в формуле.

Синтаксис формулы суммирования по условию в Excel

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

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

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

• сроки с текстовым значением;
• числовые значения;
• логические выражения;
• формулы с функциями;
• ссылки на ячейки.

Далее рассмотрим больше примеров составления условий для второго логического аргумента функции СУММЕСЛИ более детально.

Создание критериев условий для функции СУММЕСЛИ

Второй аргумент функции называется «Критерий». Данный логический аргумент используется и в других подобных логических функциях: СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. В каждом случаи аргумент заполняется согласно одних и тех же правил составления логических условий. Другими словами, для всех этих функций второй аргумент с критерием условий является логическим выражением возвращающим результат ИСТИНА или ЛОЖЬ. Это значит, что выражение должно содержать оператор сравнения, например: больше (>) меньше (<) равно (=) неравно (<>), больше или равно (>=), меньше или равно (<=). За исключением можно не указывать оператор равно (=), если должно быть проверено точное совпадение значений.

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

Таблица правил составления критериев условий:

Чтобы создать условие	Примените правило	Пример
Значение равно заданному числу или ячейке с данным адресом.	Не используйте знак равенства и двойных кавычек.	=СУММЕСЛИ(B1:B10;3)
Значение равно текстовой строке.	Не используйте знак равенства, но используйте двойные кавычки по краям.	=СУММЕСЛИ(B1:B10;"Клиент5")
Значение отличается от заданного числа.	Поместите оператор и число в двойные кавычки.	=СУММЕСЛИ(B1:B10;">=50")
Значение отличается от текстовой строки.	Поместите оператор и число в двойные кавычки.	=СУММЕСЛИ(B1:B10;"<>выплата")
Значение отличается от ячейки по указанному адресу или от результата вычисления формулы.	Поместите оператор сравнения в двойные кавычки и соедините его символом амперсант (&) вместе со ссылкой на ячейку или с формулой.	=СУММЕСЛИ(A1:A10;"<"&C1) или =СУММЕСЛИ(B1:B10;"<>"&СЕГОДНЯ())
Значение содержит фрагмент строки	Используйте операторы многозначных символов и поместите их в двойные кавычки	=СУММЕСЛИ(A1:A10;"*кг*";B1:B10)

Во втором аргументе критериев условий можно использовать разные функции и формулы. Ниже на рисунке изображен список дат и присвоенных им значений. Важно отметить что сегодня на момент написания статьи дата – «03.11.2018». Чтобы суммировать числовые значения только по сегодняшней дате используйте формулу:

Чтобы суммировать только значения от сегодняшнего дня включительно и до конца периода времени воспользуйтесь оператором «больше или равно» (>=) вместе с соответственной функцией =СЕГОДНЯ(). Формула c операторам (>=):

Суммирование по неточному совпадению в условии критерия отбора

Во втором логическом аргументе критериев условий функции СУММЕСЛИ можно применять многозначные символы – (?)и(*) для составления относительных неточных запросов. Знак вопроса (?) – следует читать как любой символ, а звездочка (*) – это строка из любого количества любых символов или пустая строка. Например, нам необходимо просуммировать только защитные краски-лаки с кодом 3 английские буквы в начале наименования:

Суммируются все значения ячеек в диапазоне B2:B16 в соответствии со значениями в ячейках диапазона A2:A16, в которых после третьего символа фрагмент строки «-защита».

Таким образом удалось суммировать только определенную группу товаров в общем списке отчета по складу. Данный фрагмент наименования товара должен встречаться в определенном месте – 3 символа от начала строки. Нет необходимости использовать сложные формулы с функцией =ЛЕВСИМВ() и т.д. Достаточно лишь воспользоваться операторами многозначных символов чтобы сформулировать простой и лаконичный запрос к базе данных с минимальными нагрузками на системные ресурсы.