24-09-2018 16:25

Функция СУММЕСЛИ и ее варианты в Excel

Суммирование ячеек в Excel – одна из самых часто используемых операций. Она используется бухгалтерами и финансистами для получения итогов доходов и расходов, компаниями по продажам для получения результатов работы и вычисления статистики. Познакомимся со способами, при помощи которых можно решить все эти задачи.

Сложение в Excel

Определение суммы элементов в программе возможно с помощью простейших арифметических операций, которые обозначаются знаком «+». Для добавления небольшого количества слагаемых этот метод подходит лучше всего.

How dangerous is the new coronavirus?You will be interested:How dangerous is the new coronavirus?

Например, для подсчета итоговой стоимости туристической заявки, которая включает цены на гостиницу, трансферы и экскурсии, можно использовать операцию сложения с помощью формулы. Формула вносится в ячейку после знака «равно» («=»). Для выбора ячеек, включенных в сумму, достаточно кликнуть на нужную, и ее расположение будет указано в формуле.

Нахождение суммы

Следующий этап – нахождение суммы по столбцам. В уже приведенном примере поставим задачу подсчитать общий доход с продажи всех заявок, включающий оплату отелей, транспорта и экскурсионных программ. Для этого нужно просуммировать все поля колонки «Общая стоимость». Применение арифметической суммы здесь неудобно, так как количество записей в таблице может быть очень большим.

Для таких ситуаций подходит функция Excel СУММ(). Она складывает числа в указанном диапазоне ячеек без необходимости прописывания расположения каждого поля: СУММ(число1;[число2];…), где в качестве аргумента «число» может быть указан диапазон чисел в ячейках. Функция принимает не более 255 аргументов. В примере заявок туристического агентства она будет применена к столбцу I:

Условное суммирование

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

Ее синтаксис:

СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]).

В интервале полей, указанном аргументом «диапазон» проверяется заданное требование для добавления числа к сумме. Этот атрибут и следующий за ним – «условие» - являются обязательными для использования в формуле. Поэтому в поле «Критерий» мы указываем свое требование.

Третий параметр команды указывает ячейки, данные из которых будут суммироваться. Это поле не является обязательным. Если оно не указано, в качестве диапазона берется уже прописанное в первом аргументе. Но эти поля могут отличаться, если функция СУММЕСЛИ проверяет условие по одному столбцу, а складывает числа в другом. Покажем это на примере.

Примеры использования

Подсчитаем доходы только от тех туристов, которые заказывали у нас индивидуальные трансферы. В качестве полей для проверки требования указываем столбец «Трансферы», сам критерий – равенство поля значению «Индивидуальные».

И в этом случае мы должны указать колонку слагаемых в поле «Диапазон_суммирования» окна параметров функции СУММЕСЛИ:

В результате получаем сумму данных только по заявкам с индивидуальными трансферами.

Еще один пример – использование команды в совокупности с формулой ВПР: ВПР(искомый показатель; интервал поиска; номер колонки в интервале; точное или примерное равенство — 0 (точное) или 1(примерное)).

На практике для использования ВПР в Excel выделяется подтаблица (параметр 2), первый столбец которой содержит искомое значение (параметр 1). Команда определяет строку, в которой оно содержится, и показывает в результате содержимое поля в найденной строке и указанном столбце (параметр 3).

Допустим, в таблице доходов трансферы указаны не наименованиями, а только кодами, названия же содержатся в другой таблице.

Для решения предыдущей задачи определим из списка кодов нужный и найдем его в столбце таблицы доходов. Для выбора искомого кода воспользуемся функцией ВПР:

=ВПР(A11;Таблица12;2).

Она вернет число 2 – код индивидуальных трансферов. Теперь подставляем эту функцию в окно параметров СУММЕСЛИ в Excel и получаем то же значение, что и в первом случае.

Сумма с несколькими условиями

В усложненной задаче требуется вычислить сумму с указанием не одного, а нескольких условий. Но описанная формула работает только с одним требованием, поэтому нам потребуется другая. Аналогом предыдущей функции является СУММЕСЛИМН, которая складывает ячейки, учитывая несколько ограничений, указанных пользователем.

По аналогии с функцией СУММЕСЛИ в окне параметров данной формулы указываем диапазон ячеек для сложения и условия. Синтаксис команды:

СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …).

Здесь диапазон суммирования уже является обязательным аргументом. Кроме него, задают ряд условий для поиска с указанием критериев отбора.

На примере посчитаем доход только от тех заявок, трансферы в которых индивидуальные, а туристы заказали обзорную экскурсию.

Этим критериям соответствует всего одна заявка, поэтому итог будет равен доходу по ней.

Использование функции СУММЕСЛИ в Excel облегчает выполнение многих задач. Ее синтаксис прост и доступен для применения даже неопытному пользователю программы.



Источник