Суммирование с условием (SUMIFS — СУММЕСЛИМН)

Данная статья написана по мотивам и в продолжение статьи о поисковых формулах. SUMIFS — это еще одна формула, которая пользуется бешенной популярностью в повседневной работе. Данная формула позволяет просуммировать некоторые значения, для которых выполняется одно или несколько условий. Итак, как же она работает.

Что делает:

Проверяет некий диапазон на выполнение условия — суммирует ячейки соответствующие тем, где условие выполняется. Что особенно приятно, условий может быть несколько.

Синтаксис: =SUMIFS(Sum_range;Criteria_range1;Criteria1…)

Sum_range — что суммируем,

Criteria_range1 — где проверяем, выполняется ли условие,

Criteria1 — собственно, условие,

Criteria_range2 — где проверяем, выполняется ли условие,

Criteria2 — собственно, условие, и т.д. столько условий, сколько необходимо проверить (в разумных пределах, конечно).

Как работает:

(1) Идет в первую ячейку диапазона Criteria_range1, проверяет, выполняется ли условие Criteria1. Если выполняется, идет в первую ячейку диапазона Criteria_range2, проверяет, выполняется ли для нее условие Criteria2. Операцию повторяет для каждого введенного условия. Если все условия выполнились, прибавляет к результату первую ячейку из диапазона Sum_range.

(2) Идет во вторую ячейку диапазона Criteria_range1, проверяет, выполняется ли условие Criteria1. Если выполняется, идет во вторую ячейку диапазона Criteria_range2, проверяет, выполняется ли для нее условие Criteria2. Операцию повторяет для каждого введенного условия. Если все условия выполнились, прибавляет к результату вторую ячейку из диапазона Sum_range.

(3) И так далее, пока не проверит весь условный диапазон.

Что иметь в виду:

(1) Формула реально проверяет ВЕСЬ условный диапазон. Если в таблице сто тысяч строк и вы прописали 10 условий, формула может считаться довольно долго. Особенно, если она повторяется пятьдесят тысяч раз. Для примера, у меня в работе была таблица, которая пятьдесят тысяч раз считала SUMIFS по трем условиям из таблицы в стопятьдесят тысяч строк. Это какой-то ужас! Такая таблица на двухядерном компе с 2ГБ оперативы считается от 30 до 40 минут. Поэтому, если есть возможность оптимизировать такую формулу — лучше это сделать. Об обтимизации поисковых формул читайте в одной из следующих статей.

(2) Посколько формула проверяет несколько диапазонов и всегда берет из суммируемого диапазона СООТВЕТСТВУЮЩЕЕ значение, то все диапазоны должны быть равны по размеру (что совершенно не значит, что они должны быть одинакого направлены).

(3) Условие — это не обязательно совпадение параметра (хоть это и наиболее частый вариант). Можно построить формулу, чтобы она проверяла, больше ли значение, чем нужное нам. Например, указать условие «>»&A2. Эта формула будет проверять, больше ли значение, чем значение в A2.

(4) В отличие от поисковых формул (vlookup и hlookup), если формула не находит совпадений, она не выдает ошибку, а возвращает ноль.

Nota bene. Коллеги, которые долго работали в Excel до 2007 версии, частно не знают этой формулы и пользуются формулой SUMIF (СУММЕСЛИ). Формула очень схожа с SUMIFS. Я нашел два очень существенных отличия. Первое, SUMIF имеет другой синтаксис (другой порядок членов), что может запутать. Второе, SUMIF позволяет сумировать проверяя не более одного критерия, что может оказаться существенным ограничением. Производительность при проверке одного условия у них приблизительно одинаковая. Поэтому лично я рекомендую пользоваться только SUMIFS — она может все то же, что и SUMIF, и даже больше. Кроме того использование одной и той же формулы в идентичных случаях ведет к унификации, что не бывает лишним.

Пример смотрите в приложенном файле.

Подробнее о различных формулах и функциях в Microsoft Excel вы можете прочитать в книге Формулы в Microsoft Excel 2010 Джона Уоренбаха. Книга описывает, как эффективно использовать различные функции и как эффективно сочетать их в формулах. В ней также описаны специальные формулы, такие как финансовые и формулы массива. Книга учит эффективно использовать сводные таблицы и диаграммы. Она определенно стоит того, чтобы иметь ее в своей библиотеке.

Также обратите внимание на книгу Microsoft Excel 2010. Библия пользователя. Это действительно Библия пользователя. Если у вас есть вопрос, то вы наверняка найдете на него ответ в этой книге.

Полезные материалы и курсы обучения по Microsoft Excel
Копирование материалов сайта разрешено только с согласия владельца с размещением ссылки на источник.