Работа во многих профессиональных областях на сегодняшний день не обходится без использования программы "Эксель". Эта программа, как любое орудие труда, становится полезной только в умелых руках. Для успешной деятельности в сфере экономики, финансирования и многих других необходимы базовые знания по Excel. На необъятных просторах интернета представители профессий, относящихся к вышеуказанным областям, сформировали список функций и возможностей, остро необходимых для решения профессиональных задач. В этот список, помимо умения работы со сводными таблицами, вошли функции:
- СУММЕСЛИ.
- ЕСЛИ.
- СУММПРОИЗВ.
- ВПР.
- СУММЕСЛИМН.
Следовательно, функция СУММЕСЛИ в "Эксель" является одной из самых востребованных.
Для успешной работы с функцией нужно знать некоторые тонкости. С помощью этой статьи даже новичок в "Эксель" без труда сможет воспользоваться функцией СУММЕСЛИ.
Функция СУММЕСЛИ: диалоговое окно аргументов функции
Если при анализе таблицы данных в "Эксель" задача стоит в необходимости суммирования показателей, которые соответствуют определенным условиям, значит, пришло время применения функции СУММЕСЛИ. Курсор мыши ставится в ячейку, куда нужно вывести результат. Далее либо функция "Эксель" СУММЕСЛИ выбирается из ленты (Формула – математические – СУММЕСЛИ), либо вводится через горячую клавишу =, которая означает готовность ввести в ячейку формулу или функцию. Если после знака = начать вводить первые буквы названия функции, "Эксель" сам выдаст несколько подходящих, остается только выбрать нужную. Опытные пользователи прописывают формулу СУММЕСЛИ в "Эксель" прямо в строке формул, вписывая аргументы через точку с запятой. Новичку же лучше воспользоваться значком fx, который находится слева от строки формул. Появляется окно "Аргументы функции". Грамотное заполнение всех окон – залог успешной работы с функцией СУММЕСЛИ в "Эксель".
Обычно три поля:
- Диапазон – совокупность ячеек, которые программа и будет сравнивать на соответствие заданному условию. Курсор ставится в поле аргумента, и мышью выбираются нужные ячейки.
- Критерий – условие, при котором будет производится суммирование. Здесь могут быть числа, выражения, текст, даты. Обычно значение критерия вводится в кавычках.
- Диапазон суммирования – ячейки со значениями, которые войдут в итоговую сумму, если соответствующая ячейка в диапазоне соответствует критерию. Курсор ставится в поле аргумента, и мышью выбираются нужные ячейки.
Могут заполняться 2 поля – диапазон и критерий, такое происходит, если первый совпадает с диапазоном суммирования. В диалоговом окне аргументы функции, в самой нижней строке показывается получившееся значение. Если с диапазонами все просто: нужно просто обозначить область анализируемых ячеек, то для заполнения поля "Критерий" нужно знать некоторые тонкости и особенности программы.
Критерии для функции СУММЕСЛИ в "Эксель"
Критериями для функции могут служить:
- Логические выражения.
- Текст.
- Точные даты.
- Временной интервал.
Можно прописывать значения внутри выражений с клавиатуры, а можно ссылаться на ячейку со значением. Второй способ более динамичный, он ставит результат в зависимость от ячеек, на которые стоит ссылка. Для примера применения СУММЕСЛИ в "Эксель" ниже приведена таблица исходных данных, где указан менеджер, продавший конкретный товар в определенном количестве и по известной цене, в указанную дату.
Логическое значение в качестве критерия
Самый частый вариант использования функции СУММЕСЛИ в "Эксель" – это использование логического значения (больше, меньше, равно) в качестве условия. Для вышеуказанной таблицы сумма заказов, равных 2000 рублей, опишется так.
Как видно из примера, диапазон и диапазон суммирования совпадают, заполняется только первая строка. Условие прописывается в кавычках (это справедливо всегда для этого поля!). Сразу видно вычисленное значение: 4000. Возвращаемся к начальной таблице: действительно, присутствуют два заказа в сумме 2000 рублей. Вместе они составляют 4000 рублей. Не используя диалоговое окно, можно записать в строке формул: =СУММЕСЛИ(E2:E15;"=2000"), результат будет тем же.
Для примера аналогично можно задать логические критерии, используя в соответствующем поле выражения:
- ">2000" – функция посчитает сумму всех заказов больше 2000 рублей.
- "<2000" – вернется сумма заказов меньше 2000 рублей
- ">=2000" – вернется сумма заказов которые больше и равны 2000 рублей.
- "<=2000" – сумма заказов меньше и равных 2000 рублям.
Текст в качестве критерия
Пример СУММЕСЛИ в "Эксель" текстовым критерием: подсчитать, на какую сумму продал заказов менеджер Иванов И. И. Так выглядит окно аргументов в этом случае.
Функция вернула значение 13000 рублей. На эту сумму продал товаров менеджер Иванов И. И.
Иногда возникает необходимость в условии задать неточное совпадение по тексту. Например, нужно найти сумму, вырученную от заказов всех миксеров (в таблице их два вида). Для неточного совпадения нужно внутри кавычек проставить символы *.
В поле критерий будет запись "*миксер*".
Задать условие с текстом, которое будет иметь значение "не равно", можно комбинацией знаков <>. Например, для подсчета суммы заказов не от продажи миксеров, окно аргументов функции будет иметь следующий вид.
Продажи всех товаров, кроме миксера, составили в сумме 34200 рублей. При использовании текста в качестве критерия можно не обращать внимания на регистр букв, совпадения по условию в любом случае будут найдены.
Дата и диапазон дат в качестве критерия
Если в функции СУММЕСЛИ в "Эксель" в качестве критерия для условия должна использоваться конкретная дата, то к оформлению поля применяются те же критерии, что и в случае с числами и с текстом. Тем же знакам =,>,<,<=,>=,<>, соответствуют те же значения, только применимо это к датам. Например, в исходной таблице необходимо суммировать заказы за первый квартал 2018 года. В качестве диапазона выбирается дата продажи, критерий, основываясь на задании, выглядит как "">=31.03.2018", в итоге получится следующее.
Значит, 19600 рублей – объем продаж по названной номенклатуре за первый квартал.
Ссылка в поле критерия
Часто итоговый результат от вычислений по СУММЕСЛИ в "Эксель" нужно сделать динамичным, зависящим от изменений в ячейках, входящих в аргументы функции. Если с первым и третьим аргументом все ясно – они являются ссылками изначально, то в поле "Критерий" для того, чтобы ввести ссылку, нужно воспользоваться дополнительным символом – значком амперсанд (&). Знаки для логического выражения необходимо, как и прежде, взять в кавычки, а перед ссылкой поставить &.
В исходной таблице необходимо найти сумму по всем заказам, которые больше, чем заказ в ячейке E10. Это выглядит так.
Аналогично ссылки можно вставить в любое выражение в поле "Критерий" в аргументах функции СУММЕСЛИ в "Эксель".
Рассмотренные выше примеры подразумевают получение суммы по одному условию. Выполнения нескольких условий формулой СУММЕСЛИ в "Эксель" не добиться. Для реализации этой задачи используется более расширенная функция – СУММЕСЛИМН, введенная в программу, начиная с версии 2007 года.
