Excel – незаменимый помощник в работе таких специалистов, как экономисты, бухгалтеры, финансисты, менеджеры, технологи и многих других. При приеме на работу по этим вакансиям все чаще кандидаты вынуждены проходить тестирование на знание программы. В заданиях проверяется владение ВПР, умение построить сводную таблицу, знание основных функций, среди которых обязательно присутствует функция СУММЕСЛИ. Она очень важна. В Excel функция СУММЕСЛИ значительно ускоряет процесс проведения вычислений, когда необходимо вычислить сумму по одному или нескольким условиям.
Сущность функции
В Excel функция СУММЕСЛИ относится к категории математических. В отличии от простой функции суммы, эта позволяет вычислить сумму только по элементам, соответствующим определенным критериям.
Аргументы этой функции:
- Диапазон – все ячейки, в которых следует искать заданный критерий
- Собственно критерий – условие, по которому происходит суммирование (это может быть текст, логические условия, даты (как определенные, так и диапазоны дат). Использование определенных видов критериев имеет свои особенности, которые необходимо знать для использования их в работе.
- Диапазон суммирования – ячейки, значения которых нужно сложить в итоге.
Пример применения функции СУММЕСЛИ с одним условием
Для функции СУММЕСЛИ в Excel самым простым примером является выборочное суммирование с одним условием. Для анализа предоставлена таблица (на рисунке ниже), в которую внесена информация о том, на какую сумму, какому заказчику, в каком регионе продали заказы менеджеры компании.
Данные в таблице заполняются ежедневно. В конце месяца для расчета премии Щербакову А. К. нужно узнать, какова общая сумма заказов, реализованных этим сотрудником. В таких случаях в Excel функция СУММЕСЛИ и используется. Для вычисления необходимо произвести действия:
- Поставить курсор в ячейку, куда необходимо вывести результат
- На ленте выбрать «Формулы»-«Математические»-СУММЕСЛИ. Откроется окно «Аргументы функции».
На поле определенного аргумента выбираются необходимые данные. В рассматриваемом случае диапазон должен включать фамилии, поэтому выделяется столбец с фамилиями сотрудников. Критерием является текст. Следует помнить, что Excel узнает текст, если он взят в кавычки. Диапазоном суммирования будет столбец стоимости заказов, ведь именно этот показатель требуется получить в итоге. Когда все поля заполнены, нажатием клавиши ОК выводится результат вычислений.
Менеджер Щербаков А. К. за прошедший месяц реализовал продукции на 54302 рубля (поле «Значение» в диалоговом показывает результат еще до нажатия клавиши ОК). На рассмотренном выше примере видно, что в Excel функция СУММЕСЛИ позволяет оперативно суммировать нужные показатели.
Бывают случаи, когда суммирование необходимо провести не по одному, а по нескольким условиям. Начиная с 2007 года, суммировать с указанием нескольких условий можно при помощи функции СУММЕСЛИмн, которая функционально расширяет в Excel функцию СУММЕСЛИ. До версии Excel 2007 года приходилось в дополнение к использованию функций применять другие инструменты Excel, например, фильтр.
Функция СУММЕСЛИ в Excel: пример с несколькими условиями
В рассмотренном выше примере необходимо найти сумму по заказам, реализованным менеджером Воробьевым И. И. заказчику ИП Васькову А. П. Таким образом, критериев отбора два: сотрудник и заказчик. Как и в предыдущем примере, ставим курсор в ячейку, где будет результат. «Формулы»-«Математические»-СУММЕСЛИмн. Появляется окно «Аргументы». Заполняется оно несколько отлично от предыдущего примера.
Здесь диапазон суммирования стоит первым, необходимо выделить столбец со стоимостью заказов. Далее идут диапазоны условий, нужно выделить столбцы, которые их содержат. Сами условия прописываются как текстовые, аналогично предыдущему примеру.
СУММЕСЛИ с логическим критерием (больше, меньше, равно)
Часто возникает необходимость использовать в качестве критерия не определенное значение, а область значений, больше, меньше или равную точке отсчета.
В таких случаях при указании в критерии заданного условия его заключают в двойные кавычки.
На рисунке показано, как посчитать сумму всех заказов стоимостью больше 10000 рулей.
Аналогично используется функция при других условиях, меняются только знаки в области критериев:
- < – меньше;
- = – равно (можно не указывать);
- < > – не равно;
- >= – больше или равно;
- <= – меньше или равно.
В Excel функция СУММЕСЛИ с критерием в виде логического выражения применяется достаточно часто.
СУММЕСЛИ с текстовым критерием
Раннее уже было рассмотрено применение текста в качестве критерия в функции СУММЕСЛИ. Из особенностей применения следует отметить, что совпадение по тексту может быть не точным. Для нахождения суммы по заказам в Москве и Московской области в поле критерия вводится значение "*моск*". Выделение звездочками предполагает частичное совпадение по критерию.
При работе с текстовыми критериями нет необходимости четко соблюдать регистр.
Функция СУММЕСЛИ с датой в качестве критерия
Пример с датами для функции СУММЕСЛИ в Excel можно представить, если добавить в исходной таблице столбец с датами продаж. Пусть задачей станет суммирование всех заказов, реализованных 15.10.2017 года и позже.
Могут меняться знаки сравнения, вместо конкретной даты может использоваться функция "сегодня". Для суммирования значений, которые относятся к периодам между двумя датами, применяется функция СУММЕСЛИ.
Конечно, без Excel тот ритм работы во многих отраслях, который задан сегодня, невозможен. Совершенствование в этой программе – залог успешного карьерного роста для многих специалистов. И функция СУММЕСЛИ – не последняя помощница во всех профессиональных начинаниях.
