Достаточно часто в расчетах возникает необходимость использовать условную сумму. Что она собой представляет? Это сумма, которая складывается из значений диапазона, удовлетворяющих одному или нескольким критериям. В этом разделе рассмотрены примеры условного суммирования, удовлетворяющего только одному критерию.
Функция СУММЕСЛИ весьма часто применяется в суммирующих формулах, использующих одно условие. Эта функция принимает три аргумента.
• Диапазон, который задает ячейки со значениями, претендующими на включение в сумму.
• Условие в форме числа, выражения или текста, которое определяет добавляемую ячейку.
• Диапазон_суммирования – необязательный аргумент. Определяет начальный диапазон ячеек, которые подлежат суммированию. Если этот аргумент опущен, функция использует диапазон ячеек, указанный в первом аргументе.
Приведенные ниже примеры демонстрируют принцип применения функции СУММЕСЛИ. Все формулы используют данные рабочего листа, показанного на рисунке (столбец F содержит формулу, которая вычитает дату, указанную в столбце Е, из даты в столбце D). Отрицательное значение в столбце F свидетельствует о том, что оплата просрочена. В рабочем листе используется несколько диапазонов, названных в соответствии со значениями строки 1.
Суммирование только отрицательных значений
Следующая формула возвращает сумму отрицательных значений, содержащихся в столбце F. Выражаясь другими словами, функция возвращает общее количество просроченных дней оплаты всех счетов. В нашем случае формула возвращает значение -58.
=СУММЕСЛИ(Разница;“<0”)
Поскольку в данном случае третий аргумент опущен, второй аргумент функции (“<0”) относится к значениям диапазона Разница.
Примечание
Для того чтобы просуммировать отрицательные значения диапазона Разница, можно также использовать следующую формулу массива:
{=СУММ(ЕСЛИ(Разница<0;Разница))}
Вовсе не обязательно в функции СУММЕСЛИ жестко программировать критерий. Например, следующая формула использует ссылку на содержимое ячейки G2, в которую введен критерий:
=СУММ(Разница;G2)
Когда критерий в ячейке G2 изменяется, формула возвращает новый результат.
Суммирование значений на основе диапазона Разница
Представленная ниже формула возвращает сумму всех просроченных счетов оплаты (из столбца С на рисунке выше).
=СУММЕСЛИ(Разница;“<0”;Сумма)
Данная формула использует значения диапазона Разница для определения значений диапазона Сумма, участвующих в формировании суммы просроченных платежей.
Примечание
Чтобы вычислить сумму значений диапазона Сумма, для которых соответствующие значения диапазона Разница являются отрицательными, можно также воспользоваться следующей формулой массива:
{=СУММ(ЕСЛИ(Разница<0;Сумма))}
Суммирование значений на основе сравнения текста
Следующая формула возвращает общую сумму счетов для филиала в Калуге:
=СУММЕСЛИ(Филиал;“=Калуга”;Сумма)
Использование знака равенства не обязательно. Следующая формула возвращает идентичный результат:
=СУММЕСЛИ(Филиал;“Калуга”;Сумма)
Чтобы просуммировать общие суммы счетов всех филиалов, кроме расположенного в Калуге, используйте следующую формулу:
=СУММЕСЛИ(Филиал;“<>Калуга”;Сумма)
Учитывайте, что сравнение текстовых строк в данном случае не чувствительно к регистру.
Суммирование значений на основе сравнения дат
Ниже представлена формула, возвращающая общую сумму счетов, оплата которых должна быть выполнена после 1 мая 2010 года.
=СУММЕСЛИ(Срок_оплаты;“>=”&ДАТА(2010;5;1);Сумма)
Обратите внимание, что второй аргумент функции СУММЕСЛИ представляет собой выражение, использующее функцию ДАТА, которая возвращает значение даты. Заключенный в кавычки условный оператор в комбинации с оператором конкатенации & формирует критерий с участием функции ДАТА.
Формула, приведенная ниже, возвращает общую сумму счетов, дата оплаты которых приходится на будущий период времени (включая текущую дату).
=СУММЕСЛИ (Дата_платежа;“>=”&СЕГОДНЯ();Сумма)
отличная статья