Условное суммирование по одному критерию

Достаточно часто в расчетах возникает необходимость использовать условную сумму. Что она собой представляет? Это сумма, которая складывается из значений диапазона, удовлетворяющих одному или нескольким критериям. В этом разделе рассмотрены примеры условного суммирования, удовлетворяющего только одному критерию.

Функция СУММЕСЛИ весьма часто применяется в суммирующих формулах, использующих одно условие. Эта функция принимает три аргумента.

Диапазон, который задает ячейки со значениями, претендующими на включение в сумму.
Условие в форме числа, выражения или текста, которое определяет добавляемую ячейку.
Диапазон_суммирования – необязательный аргумент. Определяет начальный диапазон ячеек, которые подлежат суммированию. Если этот аргумент опущен, функция использует диапазон ячеек, указанный в первом аргументе.

Приведенные ниже примеры демонстрируют принцип применения функции СУММЕСЛИ. Все формулы используют данные рабочего листа, показанного на рисунке (столбец F содержит формулу, которая вычитает дату, указанную в столбце Е, из даты в столбце D). Отрицательное значение в столбце F свидетельствует о том, что оплата просрочена. В рабочем листе используется несколько диапазонов, названных в соответствии со значениями строки 1.

Суммирование только отрицательных значений

Следующая формула возвращает сумму отрицательных значений, содержащихся в столбце F. Выражаясь другими словами, функция возвращает общее количество просроченных дней оплаты всех счетов. В нашем случае формула возвращает значение -58.

=СУММЕСЛИ(Разница;“<0”)

Поскольку в данном случае третий аргумент опущен, второй аргумент функции (“<0”) относится к значениям диапазона Разница.

Примечание

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

{=СУММ(ЕСЛИ(Разница<0;Разница))}

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

=СУММ(Разница;G2)

Когда критерий в ячейке G2 изменяется, формула возвращает новый результат.

Суммирование значений на основе диапазона Разница

Представленная ниже формула возвращает сумму всех просроченных счетов оплаты (из столбца С на рисунке выше).

=СУММЕСЛИ(Разница;“<0”;Сумма)

Данная формула использует значения диапазона Разница для определения значений диапазона Сумма, участвующих в формировании суммы просроченных платежей.

Примечание

Чтобы вычислить сумму значений диапазона Сумма, для которых соответствующие значения диапазона Разница являются отрицательными, можно также воспользоваться следующей формулой массива:

{=СУММ(ЕСЛИ(Разница<0;Сумма))}

Суммирование значений на основе сравнения текста

Следующая формула возвращает общую сумму счетов для филиала в Калуге:

=СУММЕСЛИ(Филиал;“=Калуга”;Сумма)

Использование знака равенства не обязательно. Следующая формула возвращает идентичный результат:

=СУММЕСЛИ(Филиал;“Калуга”;Сумма)

Чтобы просуммировать общие суммы счетов всех филиалов, кроме расположенного в Калуге, используйте следующую формулу:

=СУММЕСЛИ(Филиал;“<>Калуга”;Сумма)

Учитывайте, что сравнение текстовых строк в данном случае не чувствительно к регистру.

Суммирование значений на основе сравнения дат

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

=СУММЕСЛИ(Срок_оплаты;“>=”&ДАТА(2010;5;1);Сумма)

Обратите внимание, что второй аргумент функции СУММЕСЛИ представляет собой выражение, использующее функцию ДАТА, которая возвращает значение даты. Заключенный в кавычки условный оператор в комбинации с оператором конкатенации & формирует критерий с участием функции ДАТА.

Формула, приведенная ниже, возвращает общую сумму счетов, дата оплаты которых приходится на будущий период времени (включая текущую дату).

=СУММЕСЛИ (Дата_платежа;“>=”&СЕГОДНЯ();Сумма)

В начало

Полезное

Условное суммирование по одному критерию: 1 комментарий

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *