Функция СУММЕСЛИ и ее варианты в Excel
Суммирование ячеек в Excel – одна из самых часто используемых операций. Она используется бухгалтерами и финансистами для получения итогов доходов и расходов, компаниями по продажам для получения результатов работы и вычисления статистики. Познакомимся со способами, при помощи которых можно решить все эти задачи.
Сложение в Excel
Определение суммы элементов в программе возможно с помощью простейших арифметических операций, которые обозначаются знаком «+». Для добавления небольшого количества слагаемых этот метод подходит лучше всего.
Например, для подсчета итоговой стоимости туристической заявки, которая включает цены на гостиницу, трансферы и экскурсии, можно использовать операцию сложения с помощью формулы. Формула вносится в ячейку после знака «равно» («=»). Для выбора ячеек, включенных в сумму, достаточно кликнуть на нужную, и ее расположение будет указано в формуле.
Нахождение суммы
Следующий этап – нахождение суммы по столбцам. В уже приведенном примере поставим задачу подсчитать общий доход с продажи всех заявок, включающий оплату отелей, транспорта и экскурсионных программ. Для этого нужно просуммировать все поля колонки «Общая стоимость». Применение арифметической суммы здесь неудобно, так как количество записей в таблице может быть очень большим.
Для таких ситуаций подходит функция Excel СУММ(). Она складывает числа в указанном диапазоне ячеек без необходимости прописывания расположения каждого поля: СУММ(число1;[число2];…), где в качестве аргумента «число» может быть указан диапазон чисел в ячейках. Функция принимает не более 255 аргументов. В примере заявок туристического агентства она будет применена к столбцу I:
Условное суммирование
Бывают задачи, в которых необходимо найти сумму не всех элементов таблицы в столбце или диапазоне, а только тех, что отвечают предъявляемым требованиям. В таких случаях пользователю необходима возможность указания условия для включения числа в вычисление. Для этого предназначена функция СУММЕСЛИ.
Ее синтаксис:
СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]).
В интервале полей, указанном аргументом «диапазон» проверяется заданное требование для добавления числа к сумме. Этот атрибут и следующий за ним – «условие» - являются обязательными для использования в формуле. Поэтому в поле «Критерий» мы указываем свое требование.
Третий параметр команды указывает ячейки, данные из которых будут суммироваться. Это поле не является обязательным. Если оно не указано, в качестве диапазона берется уже прописанное в первом аргументе. Но эти поля могут отличаться, если функция СУММЕСЛИ проверяет условие по одному столбцу, а складывает числа в другом. Покажем это на примере.
Примеры использования
Подсчитаем доходы только от тех туристов, которые заказывали у нас индивидуальные трансферы. В качестве полей для проверки требования указываем столбец «Трансферы», сам критерий – равенство поля значению «Индивидуальные».
И в этом случае мы должны указать колонку слагаемых в поле «Диапазон_суммирования» окна параметров функции СУММЕСЛИ:
В результате получаем сумму данных только по заявкам с индивидуальными трансферами.
Еще один пример – использование команды в совокупности с формулой ВПР: ВПР(искомый показатель; интервал поиска; номер колонки в интервале; точное или примерное равенство — 0 (точное) или 1(примерное)).
На практике для использования ВПР в Excel выделяется подтаблица (параметр 2), первый столбец которой содержит искомое значение (параметр 1). Команда определяет строку, в которой оно содержится, и показывает в результате содержимое поля в найденной строке и указанном столбце (параметр 3).
Допустим, в таблице доходов трансферы указаны не наименованиями, а только кодами, названия же содержатся в другой таблице.
Для решения предыдущей задачи определим из списка кодов нужный и найдем его в столбце таблицы доходов. Для выбора искомого кода воспользуемся функцией ВПР:
=ВПР(A11;Таблица12;2).
Она вернет число 2 – код индивидуальных трансферов. Теперь подставляем эту функцию в окно параметров СУММЕСЛИ в Excel и получаем то же значение, что и в первом случае.
Сумма с несколькими условиями
В усложненной задаче требуется вычислить сумму с указанием не одного, а нескольких условий. Но описанная формула работает только с одним требованием, поэтому нам потребуется другая. Аналогом предыдущей функции является СУММЕСЛИМН, которая складывает ячейки, учитывая несколько ограничений, указанных пользователем.
По аналогии с функцией СУММЕСЛИ в окне параметров данной формулы указываем диапазон ячеек для сложения и условия. Синтаксис команды:
СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …).
Здесь диапазон суммирования уже является обязательным аргументом. Кроме него, задают ряд условий для поиска с указанием критериев отбора.
На примере посчитаем доход только от тех заявок, трансферы в которых индивидуальные, а туристы заказали обзорную экскурсию.
Этим критериям соответствует всего одна заявка, поэтому итог будет равен доходу по ней.
Использование функции СУММЕСЛИ в Excel облегчает выполнение многих задач. Ее синтаксис прост и доступен для применения даже неопытному пользователю программы.