Условные суммы на основе нескольких критериев

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

Функция СУММЕСЛИМН может использоваться для суммирования диапазона ячеек на основе множества критериев. Первым аргументом этой функции является суммируемый диапазон. Далее могут следовать от 1 до 127 пар “диапазон/критерий”, определяющих, какие значения из диапазона включать в сумму. В следующих примерах будут представлены альтернативные формулы, предназначенные для тех рабочих книг, с которыми работают пользователи, вооруженные более ранними версиями Excel.

Применение оператора И

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

• в диапазоне Разница им соответствуют отрицательные значения;
• соответствующий им текст в диапазоне Отделение – это Калуга.

Функция СУММЕСЛИМН как раз и предназначена для выполнения таких задач:

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

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

В предыдущих версиях Excel с той же задачей справлялась следующая формула массива:

{=СУММ((Разница<0)*(Филиал=“Калуга”)*Сумма)}

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

• Булев массив, содержащий значение ИСТИНА в том случае, если соответствующее значение диапазона Разница меньше нуля, и значение ЛОЖЬ – если значение диапазона больше (или равно) нуля.
• Булев массив, содержащий значение ИСТИНА в том случае, если соответствующее значение диапазона Отделение равно значению Калуга, и значение ЛОЖЬ – в противном случае.

Умножение булевых значений дает следующие результаты.

ИСТИНА * ИСТИНА = 1
ИСТИНА * ЛОЖЬ = 0
ЛОЖЬ * ЛОЖЬ = 0

Таким образом, значение диапазона Сумма, отвечающее заданным условиям, возвращается как ненулевое только в том случае, если оба соответствующих ему значения, находящихся в массивах, имеют значение ИСТИНА. Результатом является сумма значений диапазона Сумма, которые удовлетворяют заданным условиям.

Примечание

Предыдущую формулу массива можно переписать с помощью функции СУММПРОИЗВ, выполняющей умножение и сложение элементов массивов.

=СУММПРОИЗВ((Разница<0);(Филиал=“Калуга”);Сумма)

Однако в силу непонятных причин функция СУММПРОИЗВ обрабатывает булевы значения некорректно, поэтому данная формула нежизнеспособна. Тем не менее можно использовать другую формулу, которая умножает булевы значения на 1, – она корректно справится со своей задачей.

=СУММПРОИЗВ(1*(Разница<0);1*(Филиал=“Калуга”);Сумма)

Применение оператора ИЛИ

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

• в диапазоне Разница им соответствуют отрицательные значения;
• соответствующий им текст в диапазоне Отделение – это Калуга.

Приведенная ниже формула массива выполняет поставленную задачу.

{=СУММ(ЕСЛИ((Филиал=“Калуга”)+(Разница<0);1;0)*Сумма)}

Знак “+” указывает на объединение условий (оператор ИЛИ), количество которых не ограничивается двумя (как в данном примере), а может быть и больше.

Комбинирование условий И и ИЛИ

Как правило, ситуация усложняется, когда необходимо задать условие, в котором использовано несколько операторов И и ИЛИ. Предположим, что необходимо просуммировать значения диапазона Сумма, удовлетворяющие следующим условиям:

• в диапазоне Разница им соответствуют отрицательные значения;
• соответствующий им текст в диапазоне филиал — это текст Калуга или Тула.

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

СУММЕСЛИМН(Сумма;Разница;“<0”;Филиал;“Калуга”)+СУММЕСЛИМН(Сумма; Разница;“<0”;Филиал;“Тула”)

Следующая формула массива тоже справится с поставленной задачей:

{=СУММ((Разница<0)*ЕСЛИ((Филиал=“Калуга”)+(Филиал=“Тула”);1)*Сумма)

В начало

Полезное

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

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