В этой статье мы рассмотрим функции СУММЕСЛИ и СУММЕСЛИМН.
Аргументы функции СУММЕСЛИ
Итак, первая функция СУММЕСЛИ имеет три аргумента. Давайте разберем их.
Первый — это диапазон. Второй — сам критерий. И третий — необязательный аргумент, это диапазон суммирования. Давайте рассмотрим каждый из них:
- Диапазон — это область проверяемых ячеек, чаще всего это какой-то диапазон в столбце или весь столбец.
- Критерий — второй аргумент, который может быть числом, выражением или текстом, определяющим, какие ячейки необходимо суммировать.
- Диапазон суммирования — третий, необязательный, аргумент. Это значения ячеек, которые суммируются, если они отличаются от ячеек, представленных в основном диапазоне. Если аргумент диапазона суммирования опущен, то Excel суммирует ячейки именно в критерии диапазона.
Аргументы функции СУММЕСЛИМН
Сразу же рассмотрим функцию СУММЕСЛИМН, которая немного отличается, но очень похожа на первую функцию, поэтому мы объединили их в одной статье. В данной функции у нас есть аргумент диапазон_суммирования, диапазон_условие 1 и само условие 1. Давайте разберем, что обозначает каждый аргумент:
- Диапазон суммирования — то, что необходимо нам суммировать в таблице, это, как правило, диапазон в столбце или весь столбец.
- Диапазон_условие1 — непосредственно диапазон ячеек, проверяемых на соответствие определенному условию в этой же таблице.
- Условие1 — это наше условие в форме числа, выражения или текста, определяющие суммируемые ячейки.
В Excel 2007 появилась функция СУММЕСЛИМН. Разница в том, что если функция СУММЕСЛИ может проверять только одно условие, то эта функция может проверять до 127 условий проверки. Именно такое количество есть в этой функции.
Как использовать функцию СУММЕСЛИ: практический пример
Теперь давайте рассмотрим практический пример. Перед нами таблица, в которой представлены продажи по определенным группам товаров: название товара, количество и период продажи. В данном случае у нас есть уже колонка K с результатом, по которой мы будем оценивать правильность работы функции. Для удобства я разместил уже готовый результат в этих ячейках. Давайте рассмотрим конкретные задания, которые выделены красным цветом. Первое, что нам необходимо — это посчитать общий объем продаж. Хотя мы рассматриваем функции СУММЕСЛИ и СУММЕСЛИМН, данная задача решается простым суммированием, которое мы можем выполнить с помощью функции СУММ. Давайте сделаем это, чтобы понять, в чем отличие.
СУММ — это обычная функция, с помощью которой мы можем суммировать числа или диапазон. В данном случае мы можем выбрать всю колонку F и просуммировать ее, чтобы получить нужный результат. Посмотрите, в функциях СУМ, так же как и в СУММЕСЛИ и СУММЕСЛИМН, игнорируются ячейки с текстом. В данном случае у нас в колонке F попало значение «Сумма». Тем не менее функция СУММ игнорирует текст и суммирует только числа, видимые в ячейках. То же самое относится и к другим функциям.
Следующее задание — объем продаж, в которых есть только электроинструменты. Здесь нам не подойдет простое суммирование, поэтому на помощь приходит функция СУММЕСЛИ. Давайте выберем «равно» в СУММЕСЛИ, чтобы задать это условие. Мы можем открыть аргументы функции, нажав на кнопку fx. Давайте поработаем с этим окном.
Здесь наши критерии: диапазон, критерий и диапазон суммирования. Диапазон — это то, что мы будем проверять, в данном случае это «электроинструмент». Обратим внимание, что электроинструмент находится в колонке «Группа товаров», поэтому выбираем этот диапазон, то есть весь столбец.
Сам критерий мы можем задать, написав его в кавычках. Давайте так и сделаем. Вводим критерий «Электроинструмент», обязательно заключаем его в кавычки.
Далее, выбираем диапазон суммирования, то есть то, что мы должны суммировать. В данном случае мы его не опускаем, потому что диапазон, который мы выбрали для проверки, является текстом. Итак, сначала мы просматриваем текст, находим там «электроинструмент», а затем в диапазоне суммирования суммируем то, что соответствует этому критерию. Поэтому выбираем колонку F от F, нажимаем «ОК» и видим результат суммирования. Все прошло корректно, Excel посчитал правильно.
Считаем количество товаров по определённым группам с помощью функции СУММЕСЛИ
Переходим к следующему заданию. Нам нужно посчитать количество товаров по определенным группам. В отличие от предыдущего примера, здесь у нас уже есть непосредственные критерии в ячейках. Если посмотреть, то это критерии групп товаров, например, «цемент», «триммеры», «сухая смесь» и так далее. Здесь мы можем уже ссылаться на саму ячейку. То есть разница будет в том, что мы используем функцию СУММЕСЛИ. Берем диапазон (нажимая мышкой на столбец B), в данном случае это группа товаров, нажимаем точку с запятой, затем выбираем непосредственно сам критерий, который в данном случае будет нашей ячейкой H7. Затем указываем диапазон суммирования, в данном случае мы считаем по столбцу DD.
Закрываем скобку, нажимаем «Enter», и получаем корректный результат.
Следующее задание — объем продаж всех шуруповертов. Здесь нам нужно использовать функцию СУММЕСЛИ, так как у нас есть только один критерий. Но здесь нам нужны все шуруповерты. Давайте включим фильтр в таблице, чтобы посмотреть, какие группы у нас есть.
Давайте наберем букву «Ш» или «ШУР». Мы видим, что у нас нет групп товаров с наименованием «шуруповерт».
Однако, в наименовании товара встречается соответствующая информация.
Давайте попробуем использовать маску, чтобы выделить все шуруповерты. Мы можем использовать символ «*», чтобы указать это. Набираем «равно», выбираем функцию СУММЕСЛИ, указываем диапазон, который в данном случае это колонка CC. Затем вводим критерий, используя символ «*», который будет искать все упоминания «шуруповерт» в наименовании товара.
Как это делается? Сначала вводим звездочку, затем первые слова, например, «шуруп». После этого снова ставим звездочку и закрываем кавычку.
Что это означает? Это означает, что все слова, идущие до «шуруп» (в данном случае), будут учтены, а также все слова после «шуруп» также будут учтены. Таким образом, мы ограничиваем критерий, указывая, что нужно учитывать все слова до и после «шуруп». Давайте попробуем. Вводим точку с запятой и выбираем диапазон суммирования. Наш объем продаж — это колонка FF.
Закрываем скобку, нажимаем Enter. Видим результат 64 440. Все корректно, функция посчитала верно. Продолжаем дальше.
Суммирование с несколькими критериями с помощью функции СУММЕСЛИМН
Следующий блок — суммирование с использованием нескольких критериев. В первой ячейке мы должны найти объем продаж всего клея до 15 октября 2019 года. Для этого используем функцию СУММЕСЛИМН. Нажимаем кнопку fx и видим, что у нас в функции два аргумента: Диапазон_суммирования и Диапазон_условия1.
Выбираем диапазон суммирования — весь объем продаж. Переходим к диапазону условий. Нам нужно выбрать клей, который упоминается в наименовании товара. Группы товаров с таким названием нет, поэтому выбираем наименование товара (колонка CC). Условие1 указываем с использованием звездочки в кавычках («*клей*»).
Теперь можем выбрать второй диапазон условий, который автоматически появится. Нам нужна дата до 15 октября. Работаем с диапазоном условия «Дата» (столбец A). Условие пишем в кавычках. Для того, чтобы нам указать условие «до 15 октября», мы пишем «<15.10.2019».
Закрываем все скобки, нажимаем Enter, видим результат 8750. Проверяем — все верно.
Вычисляем объём продаж с указанной даты с помощью функции СУММЕСЛИ
Также рассмотрим еще одну задачу: объем продаж с указанной даты. В этом случае мы можем использовать ячейку с датой в качестве критерия. Однако здесь есть одна тонкость. Обратите внимание, что нам нужно использовать только один критерий, поэтому достаточно функции СУММЕСЛИ. Указываем диапазон, который будет содержать нашу дату. Дальше вводим критерий. Однако если мы просто введем «<=» в кавычках, получим ошибку, так как Excel воспримет это как текст.
Для решения этой проблемы используем символ «&» для объединения критерия и ячейки с датой. Таким образом, мы указываем, что критерий состоит из даты и условия «>=» и H24 и записан следующим образом: «>=»&H24. Это позволит нам правильно выполнить функцию.
Нажимаем ОК, видим результат. В этом видео мы рассмотрели две мощные и интересные функции: СУММЕСЛИ и СУММЕСЛИМН. Их можно использовать в различных задачах.
