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

Работа во многих профессиональных областях на сегодняшний день не обходится без использования программы "Эксель". Эта программа, как любое орудие труда, становится полезной только в умелых руках. Для успешной деятельности в сфере экономики, финансирования и многих других необходимы базовые знания по Excel. На необъятных просторах интернета представители профессий, относящихся к вышеуказанным областям, сформировали список функций и возможностей, остро необходимых для решения профессиональных задач. В этот список, помимо умения работы со сводными таблицами, вошли функции:

• СУММЕСЛИ.
• ЕСЛИ.
• СУММПРОИЗВ.
• ВПР.
• СУММЕСЛИМН.

Следовательно, функция СУММЕСЛИ в "Эксель" является одной из самых востребованных.

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

Функция СУММЕСЛИ: диалоговое окно аргументов функции

Если при анализе таблицы данных в "Эксель" задача стоит в необходимости суммирования показателей, которые соответствуют определенным условиям, значит, пришло время применения функции СУММЕСЛИ. Курсор мыши ставится в ячейку, куда нужно вывести результат. Далее либо функция "Эксель" СУММЕСЛИ выбирается из ленты (Формула – математические – СУММЕСЛИ), либо вводится через горячую клавишу =, которая означает готовность ввести в ячейку формулу или функцию. Если после знака = начать вводить первые буквы названия функции, "Эксель" сам выдаст несколько подходящих, остается только выбрать нужную. Опытные пользователи прописывают формулу СУММЕСЛИ в "Эксель" прямо в строке формул, вписывая аргументы через точку с запятой. Новичку же лучше воспользоваться значком fx, который находится слева от строки формул. Появляется окно "Аргументы функции". Грамотное заполнение всех окон – залог успешной работы с функцией СУММЕСЛИ в "Эксель".

Обычно три поля:

1. Диапазон – совокупность ячеек, которые программа и будет сравнивать на соответствие заданному условию. Курсор ставится в поле аргумента, и мышью выбираются нужные ячейки.
2. Критерий – условие, при котором будет производится суммирование. Здесь могут быть числа, выражения, текст, даты. Обычно значение критерия вводится в кавычках.
3. Диапазон суммирования – ячейки со значениями, которые войдут в итоговую сумму, если соответствующая ячейка в диапазоне соответствует критерию. Курсор ставится в поле аргумента, и мышью выбираются нужные ячейки.

Могут заполняться 2 поля – диапазон и критерий, такое происходит, если первый совпадает с диапазоном суммирования. В диалоговом окне аргументы функции, в самой нижней строке показывается получившееся значение. Если с диапазонами все просто: нужно просто обозначить область анализируемых ячеек, то для заполнения поля "Критерий" нужно знать некоторые тонкости и особенности программы.

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

Критериями для функции могут служить:

1. Логические выражения.
2. Текст.
3. Точные даты.
4. Временной интервал.

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

Логическое значение в качестве критерия

Самый частый вариант использования функции СУММЕСЛИ в "Эксель" – это использование логического значения (больше, меньше, равно) в качестве условия. Для вышеуказанной таблицы сумма заказов, равных 2000 рублей, опишется так.

Как видно из примера, диапазон и диапазон суммирования совпадают, заполняется только первая строка. Условие прописывается в кавычках (это справедливо всегда для этого поля!). Сразу видно вычисленное значение: 4000. Возвращаемся к начальной таблице: действительно, присутствуют два заказа в сумме 2000 рублей. Вместе они составляют 4000 рублей. Не используя диалоговое окно, можно записать в строке формул: =СУММЕСЛИ(E2:E15;"=2000"), результат будет тем же.

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

1. ">2000" – функция посчитает сумму всех заказов больше 2000 рублей.
2. "<2000" – вернется сумма заказов меньше 2000 рублей
3. ">=2000" – вернется сумма заказов которые больше и равны 2000 рублей.
4. "<=2000" – сумма заказов меньше и равных 2000 рублям.

Текст в качестве критерия

Пример СУММЕСЛИ в "Эксель" текстовым критерием: подсчитать, на какую сумму продал заказов менеджер Иванов И. И. Так выглядит окно аргументов в этом случае.

Функция вернула значение 13000 рублей. На эту сумму продал товаров менеджер Иванов И. И.

Иногда возникает необходимость в условии задать неточное совпадение по тексту. Например, нужно найти сумму, вырученную от заказов всех миксеров (в таблице их два вида). Для неточного совпадения нужно внутри кавычек проставить символы *.

В поле критерий будет запись "*миксер*".

Задать условие с текстом, которое будет иметь значение "не равно", можно комбинацией знаков <>. Например, для подсчета суммы заказов не от продажи миксеров, окно аргументов функции будет иметь следующий вид.

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

Дата и диапазон дат в качестве критерия

Если в функции СУММЕСЛИ в "Эксель" в качестве критерия для условия должна использоваться конкретная дата, то к оформлению поля применяются те же критерии, что и в случае с числами и с текстом. Тем же знакам =,>,<,<=,>=,<>, соответствуют те же значения, только применимо это к датам. Например, в исходной таблице необходимо суммировать заказы за первый квартал 2018 года. В качестве диапазона выбирается дата продажи, критерий, основываясь на задании, выглядит как "">=31.03.2018", в итоге получится следующее.

Значит, 19600 рублей – объем продаж по названной номенклатуре за первый квартал.

Ссылка в поле критерия

Часто итоговый результат от вычислений по СУММЕСЛИ в "Эксель" нужно сделать динамичным, зависящим от изменений в ячейках, входящих в аргументы функции. Если с первым и третьим аргументом все ясно – они являются ссылками изначально, то в поле "Критерий" для того, чтобы ввести ссылку, нужно воспользоваться дополнительным символом – значком амперсанд (&). Знаки для логического выражения необходимо, как и прежде, взять в кавычки, а перед ссылкой поставить &.

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

Аналогично ссылки можно вставить в любое выражение в поле "Критерий" в аргументах функции СУММЕСЛИ в "Эксель".

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