7 примеров использования формулы СУММЕСЛИ в Excel с несколькими условиями
В таблицах Excel вы можете не только найти сумму чисел, но и сделать это на основе заранее определенных критериев выбора. Мы увидим, как правильно применять функцию СУММЕСЛИ в таблицах Excel. Начнем с простейших случаев, таких как использование подстановочных знаков, назначение диапазона суммирования и работа с числами, текстом и датами. Давайте сосредоточимся на том, как использовать несколько условий одновременно. И, конечно же, применим полученные знания на практике на простых примерах.
Хорошо, что функция СУММЕСЛИ одинакова во всех версиях MS Excel с 2016 по 2003 год. Еще одна хорошая новость: если вы потратите некоторое время на ее изучение, вам потребуется совсем немного усилий, чтобы понять другие функции «ЕСЛИ», такие как СУММЕСЛИ, СЧЁТЕСЛИ, СЧЁТЕСЛИ и т.д.
Как пользоваться СУММЕСЛИ в Excel – синтаксис
Его цель — найти совокупность значений, отвечающих определенным требованиям.
Синтаксис функции следующий:
= СУММЕСЛИ (диапазон; критерий; [диапазон_суммы])
Диапазон — это область, которую мы исследуем на предмет определенного значения.
Критерий — это значение или шаблон, по которому мы выбираем числа для суммирования.
Значение критерия можно записать прямо в формулу. В этом случае не забывайте, что текст необходимо заключать в кавычки.
Его также можно представить как ссылку на ячейку таблицы, которая укажет необходимое ограничение. Конечно, второй способ более рациональный, так как позволяет гибко модифицировать вычисления, не меняя выражения.
Sum_range — третий параметр, необязательный, но очень полезный. Благодаря ему мы можем искать в одной области и суммировать значения другой в соответствующих строках.
Итак, если он указан, расчет основан на его данных. Если он отсутствует, добавляются значения той же области, в которой производился поиск.
Чтобы лучше понять это описание, рассмотрим несколько простых действий. Надеюсь, они будут понятны не только «продвинутым» пользователям, но и подойдут «чайникам».
Примеры использования функции СУММЕСЛИ в Excel
Сумма если больше чем, меньше, или равно
Начнем с самого простого. Допустим, у нас есть данные о продажах шоколада. Рассчитаем различные варианты продажи.
В I3 написано:
= СУММЕСЛИ (D2: D21; I2)
D2: D21 — координаты, в которых мы ищем значение.
I2 — ссылка на критерий выбора. Другими словами, мы ищем ячейки со значением 144 и складываем их.
Поскольку третий параметр функции не указан, сразу добавляем выбранные числа. Область поиска одновременно будет интервалом суммирования.
Кроме того, вы можете указать текстовое выражение, состоящее из>, , = и числа, в качестве действия для выбора желаемых значений.
Вы можете указать это прямо в формуле, как это сделано в I13
= СУММЕСЛИ (RE2: RE21; «
То есть суммируем все заказы, где количество меньше 144.
Но, согласитесь, это не очень удобно, поскольку приходится корректировать саму формулу, а также не забывать заключать условие в кавычки.
В дальнейшем мы постараемся использовать только ссылку на критерий, так как это значительно упрощает любые настройки.
Критерии для текста.
Гораздо чаще встречаются ситуации, когда необходимо выполнить поиск в одном месте и в другом, чтобы обобщить данные, соответствующие найденному.
Чаще всего это необходимо, если вам нужно использовать выделение для определенных слов. В конце концов, текстовые значения не могут быть добавлены, а соответствующие числа можно.
В качестве простого метода использования формулы СУММЕСЛИ в таблицах Excel мы вычисляем общую сумму выполненных заказов.
В I3 записываем выражение:
= СУММЕСЛИ (F2: F21; I2; E2: E21)
F2: F21 — это область, в которой мы выбираем соответствующие значения.
I2 — здесь написано, что именно мы выбираем.
E2: E21 — складывает числа, соответствующие найденным совпадениям.
Конечно, вы можете указать параметр фильтра прямо в выражении:
= СУММЕСЛИ (F2: F21; «Да»; E2: E21)
Но мы уже договорились, что это не совсем рационально.
Важная заметка. Не забывайте, что все текстовые значения должны быть заключены в кавычки.
Подстановочные знаки для частичного совпадения.
При работе с текстовыми данными часто бывает необходимо найти часть слова или фразы.
Вернемся к нашему случаю. Определяем общее количество заказов на черный шоколад. В результате у нас есть 2 совпадающих названия продуктов. Как вы объясните их оба? Для этого существует концепция нечеткого соответствия.
Мы можем искать и подсчитывать значения, указывая не все содержимое ячейки, а только ее часть. Следовательно, мы можем расширить границы поиска, используя символы подстановки “?”, “*”.
Условное обозначение «?» позволяет заменить любой символ.
Символ «*» позволяет заменять не один, а любое количество символов (включая ноль).
Эти знаки можно применить в нашем случае двояко. Или напишите их прямо в таблице –
= СУММЕСЛИ (C2: C21; I2; E2: E21), где E2 содержит * [слово]*
или
= СУММЕСЛИ (C2: C21; «*» & E2 & «*»; E2: E21)
где * вставляются непосредственно в выражение и «наклеиваются» желаемым текстом.
Давайте немного потренируемся:
- «* Черный *» — ищем предложение, в котором встречается данное выражение, а до и после него — любую букву, знак и цифру. В нашем случае этому соответствуют «Темный шоколад» и «Супер темный шоколад”.
- «Д?» — вам нужно слово из 2 букв, первая из которых — «D», а вторая — любая. В нашем случае «да» будет нормально”.
- «???» — найти любое слово из 3 букв
= СУММЕСЛИ (F2: F21; «???»; E2: MI21)
Этому требованию отвечает ответ «Нет”.
- «??????? *» — текст из 7 и более букв.
= СУММЕСЛИ (LA2: SI21; «??????? *», MI8: MI28)
«Зеленый», «Оранжевый», «Серебряный», «Синий», «Коричневый», «Золотой», «Розовый» подойдут”.
- «З *» — выбираем предложение, первая буква которого «З», а затем — любая буква, знак и цифра. Это «Золотой» и «Зеленый”.
- «Черный *» — подходит предложение, начинающееся с этого слова, следовательно — любая буква, знак и цифра. Подойдет «черный шоколад”.
Примечание. Если вам нужно использовать текст, содержащий * и? В качестве действия поиска, используйте знак тильды (~), помещая его перед этими символами. Так что * е? будут рассматриваться обычные символы, а не шаблон:
= СУММЕСЛИ (LA2: SI21; «* ~? *», MI8: MI28)
Важная заметка. Если ваш поисковый текст содержит несколько символов * и?, Вы должны вставить тильду (~) перед каждым из них. Например, если мы ищем текст, состоящий из трех звездочек, мы можем написать его так:
= СУММЕСЛИ (LA2: SI21; «~ * ~ * ~ *»; MI8: MI28)
А если в тексте просто 3 звездочки, наше выражение можно переписать следующим образом:
= СУММЕСЛИ (SI2: SI21; «* ~ * ~ * ~ **»; MI8: MI28)
Точная дата либо диапазон дат.
Если нам нужно найти сумму чисел, соответствующих определенной дате, самый простой способ — указать саму эту дату в качестве критерия.
Примечание. При этом не забывайте, что указанный вами формат даты должен соответствовать региональным настройкам вашей таблицы!
Обратите внимание, что мы также можем вставить его прямо в формулу здесь или использовать ссылку.
Подсчитываем общий объем продаж на сегодня — 04.02.2020.
= СУММЕСЛИ (LA2: LA21; I1; E2: E21)
или
= СУММЕСЛИ (A2: A21; СЕГОДНЯ (); E2: E21)
Рассчитываем на вчерашний день.
= СУММЕСЛИ (A2: A21; СЕГОДНЯ () — 1; E2: E21)
СЕГОДНЯ () — 1 будет «вчера».
Сумма за даты до 1 февраля.
= СУММЕСЛИ (LA2: A21; «
После 1 февраля включительно:
= СУММЕСЛИ (LA2: A21; «> =» & «01.02.2020»; E2: E21)
Что, если нам важны временные рамки «от до»?
Мы можем подсчитать итоги за определенный период времени. Для этого воспользуемся небольшим трюком: различием функций СУММЕСЛИ. Допустим, нам нужен доход с 1 по 4 февраля включительно. Из продаж после 1 февраля вычитаем все, что было продано до 4 февраля.
= СУММЕСЛИ (A2: A21; «> =» & «02/01/2020»; E2: E21) — СУММЕСЛИ (A2: A21; «
Сумма значений, соответствующих пустым либо непустым ячейкам
Бывает, что все непустые ячейки, содержащие хотя бы одну букву, цифру или символ, должны использоваться в качестве условия суммирования.
Давайте рассмотрим другой вариант использования формулы СУММЕСЛИ в электронной таблице Excel, где вам нужно подсчитать заказы, у которых нет знака завершения, и сколько заказов было обработано.
Если критерий просто «*», то для подсчета учитываются непустые ячейки, в которых есть хотя бы одна буква или символ (кроме пустых).
= СУММЕСЛИ (F2: F21; «*»; E2: E21)
Точно такой же результат дает использование пары знаков «больше» и «минус» — вместо звездочки» — .
= СУММЕСЛИ (F2: F21; «»; E2: E21)
Теперь давайте посмотрим, как найти сумму, соответствующую пустым ячейкам.
Для поиска пустых, в которых нет букв или цифр, в качестве критерия поставить парные одинарные кавычки », если значение критерия указано в ячейке и формула относится к нему.
Если вы укажете, что в самой формуле нужно выбирать только пустые ячейки, введите двойные кавычки.
= СУММЕСЛИ (F2: F21; «»; E2: E21)
Сумма по нескольким условиям.
Как и раньше, функция СУММЕСЛИ может работать только с одним условием. Но очень часто бывает, что вам нужно найти набор данных, который одновременно удовлетворяет нескольким требованиям. Это можно сделать с помощью некоторых уловок или других функций. Рассмотрим все по порядку.
Вернемся к нашему заказу. Мы учитываем два условия и выясняем, сколько заказов на черный и молочный шоколад уже сделано.
1. СУММЕСЛИ + СУММЕСЛИ
это просто:
= СУММЕСЛИ ($ C $ 2: $ C $ 21; «*» & H3 & «*»; $ E $ 2: $ E $ 21) + СУММЕСЛИ ($ C $ 2: $ C $ 21; «*» & H4 & «*»; $ E $ 2: $ E $ 21)
Находим количество заказов по каждому виду товаров и складываем их. Думаю, вы уже научились с этим работать :).
Это самое простое решение, но не самое универсальное и далеко не единственное.
2. СУММ и СУММЕСЛИ с аргументами массива.
Вышеупомянутое решение очень простое и может быстро выполнить работу при небольшом количестве критериев. Но если вы хотите работать с несколькими, он просто станет огромным. В этом случае лучший подход — использовать набор критериев в качестве аргумента. Давайте посмотрим на этот подход.
Вы можете начать с перечисления всех ваших условий, разделенных запятыми, а затем заключить окончательный список, разделенный точками с запятой, в {фигурные скобки}, технически называемый массивом.
Если вы хотите найти покупки этих двух продуктов, ваши критерии в виде массива будут выглядеть так:
СУММЕСЛИ ($ C $ 2: $ C $ 21; {«* черный *»; «* молоко *»}; $ E $ 2: $ E $ 21)
Поскольку здесь используется массив критериев, результатом вычислений также будет массив, состоящий из двух значений.
Теперь мы используем функцию SUM, которая может работать с массивами данных, добавляя их содержимое.
= СУММ (СУММЕСЛИ ($ C $ 2: $ C $ 21, {«* черный *», «* молоко *»}, $ E $ 2: $ E $ 21))
важно, чтобы результаты расчетов в первом и втором случаях совпадали.
3. СУММПРОИЗВ и СУММЕСЛИ.
Что делать, если вы предпочитаете перечислять критерии в специально отведенной части таблицы? Вы можете использовать СУММЕСЛИ в сочетании с функцией СУММПРОИЗВ, которая умножает компоненты в определенных массивах и возвращает сумму этих продуктов.
Вот как это будет выглядеть:
= СУММПРОИЗВ (СУММЕСЛИ (C2: C21; H3: H4; E2: E21))
в H3 и H4 мы напишем критерии выбора.
Но, конечно, ничто не мешает перечислить значения в виде набора критериев:
= СУММ.ПРОДУКТ (СУММЕСЛИ (C2: C21, {«* черный *», «* молоко *»}, E2: E21))
Результат, возвращенный в обоих случаях, будет идентичен тому, что вы видите на скриншоте.
Важная заметка! Обратите внимание, что все три из вышеперечисленных методов выполняют логические вычисления ИЛИ. То есть нам нужны продажи шоколада, который будет черным или молочным.
Почему СУММЕСЛИ у меня не работает?
Для этого может быть несколько причин. Иногда ваше выражение не возвращает то, что вы ожидаете, только потому, что тип данных в ячейке или какой-либо аргумент не подходит. Итак, вот что нужно проверить.
1. На «диапазон данных» и «диапазон суммирования» следует ссылаться, а не в массив.
Первый и третий атрибуты функции всегда должны быть ссылкой на область таблицы, например A1: A10. Если вы попытаетесь передать что-то еще, например массив {1,2,3}, Excel выдаст сообщение об ошибке.
Правильно: = СУММЕСЛИ (A1: A3; «цвет»; C1: C3)
Недействительно: = СУММЕСЛИ ({1,2,3}, «цвет», C1: C3)
2. Ошибка при добавлении значений из других листов или книг
Как и любая другая функция Excel, СУММЕСЛИ может ссылаться на другие листы и книги, если они в данный момент открыты.
Находим сумму значений в F2: F9 на листе 1 книги 1, если соответствующие данные записаны в столбце A и если между ними есть «яблоки»:
= СУММЕСЛИ ([Book1.xlsx] Sheet1! $ A $ 2: $ A $ 9, «яблоки», [Book1.xlsx] Sheet1! $ F $ 2: $ F $ 9)
Однако это перестанет работать, как только Book1 будет закрыт. Это связано с тем, что области, на которые ссылаются формулы в закрытых книгах, преобразуются в массивы и сохраняются как таковые в текущей книге. А поскольку в аргументах 1 и 3 нельзя использовать массивы, формула выдает ошибку # ЗНАЧ!.
3. Во избежание проблем убедитесь, что ваши данные и диапазоны поиска одинакового размера.
Как отмечалось в начале этого руководства, они не обязательно должны быть одинакового размера в современных версиях Microsoft Excel. Однако в Excel 2000 и более ранних версиях это может вызвать проблемы. Однако даже в более новых версиях Excel 2010 и Excel 2016 сложные выражения, в которых в добавляемом диапазоне меньше строк и/или столбцов, чем в диапазоне поиска, являются причудливыми. По этой причине желательно всегда иметь их одного размера и формы.
