Все примеры, рассмотренные в предыдущем разделе, касались случаев использования только одного условия сравнения данных. В этом разделе мы будем работать с формулами суммирования, использующими несколько условий. Так как функция СУММЕСЛИ не работает со множеством критериев, мы будем использовать формулы массивов, а также функцию СУМЕСЛИМН. На рисунке для напоминания снова показана используемая в расчетах область данных рабочего листа.
Функция СУММЕСЛИМН может использоваться для суммирования диапазона ячеек на основе множества критериев. Первым аргументом этой функции является суммируемый диапазон. Далее могут следовать от 1 до 127 пар “диапазон/критерий”, определяющих, какие значения из диапазона включать в сумму. В следующих примерах будут представлены альтернативные формулы, предназначенные для тех рабочих книг, с которыми работают пользователи, вооруженные более ранними версиями Excel.
Применение оператора И
Предположим, необходимо подсчитать общую сумму по счетам, оплата которых просрочена И которые, в то же время, выписаны филиалом в Калуге. Иными словами, значения, содержащиеся в диапазоне Сумма, будут суммироваться только в том случае, если одновременно выполняются следующие условия:
• в диапазоне Разница им соответствуют отрицательные значения;
• соответствующий им текст в диапазоне Отделение – это Калуга.
Функция СУММЕСЛИМН как раз и предназначена для выполнения таких задач:
=СУММЕСЛИМН(Сумма;Разница;“<0”;Филиал;“Калуга”)
Первым аргументом функции СУММЕСЛИМН является суммируемый диапазон. Остальные аргументы, будучи объединенными в пары, определяют критерии. Каждая пара состоит из диапазона и критерия, который на него накладывается.
В предыдущих версиях Excel с той же задачей справлялась следующая формула массива:
{=СУММ((Разница<0)*(Филиал=“Калуга”)*Сумма)}
Данная формула создает в памяти системы два новых массива.
• Булев массив, содержащий значение ИСТИНА в том случае, если соответствующее значение диапазона Разница меньше нуля, и значение ЛОЖЬ – если значение диапазона больше (или равно) нуля.
• Булев массив, содержащий значение ИСТИНА в том случае, если соответствующее значение диапазона Отделение равно значению Калуга, и значение ЛОЖЬ – в противном случае.
Умножение булевых значений дает следующие результаты.
ИСТИНА * ИСТИНА = 1
ИСТИНА * ЛОЖЬ = 0
ЛОЖЬ * ЛОЖЬ = 0
Таким образом, значение диапазона Сумма, отвечающее заданным условиям, возвращается как ненулевое только в том случае, если оба соответствующих ему значения, находящихся в массивах, имеют значение ИСТИНА. Результатом является сумма значений диапазона Сумма, которые удовлетворяют заданным условиям.
Примечание
Предыдущую формулу массива можно переписать с помощью функции СУММПРОИЗВ, выполняющей умножение и сложение элементов массивов.
=СУММПРОИЗВ((Разница<0);(Филиал=“Калуга”);Сумма)
Однако в силу непонятных причин функция СУММПРОИЗВ обрабатывает булевы значения некорректно, поэтому данная формула нежизнеспособна. Тем не менее можно использовать другую формулу, которая умножает булевы значения на 1, – она корректно справится со своей задачей.
=СУММПРОИЗВ(1*(Разница<0);1*(Филиал=“Калуга”);Сумма)
Применение оператора ИЛИ
Предположим, что необходимо получить общую сумму просроченных счетов и счетов филиала в Калуге. Иными словами, требуется просуммировать те значения диапазона Сумма, которые соответствуют хотя бы одному из следующих условий:
• в диапазоне Разница им соответствуют отрицательные значения;
• соответствующий им текст в диапазоне Отделение – это Калуга.
Приведенная ниже формула массива выполняет поставленную задачу.
{=СУММ(ЕСЛИ((Филиал=“Калуга”)+(Разница<0);1;0)*Сумма)}
Знак “+” указывает на объединение условий (оператор ИЛИ), количество которых не ограничивается двумя (как в данном примере), а может быть и больше.
Комбинирование условий И и ИЛИ
Как правило, ситуация усложняется, когда необходимо задать условие, в котором использовано несколько операторов И и ИЛИ. Предположим, что необходимо просуммировать значения диапазона Сумма, удовлетворяющие следующим условиям:
• в диапазоне Разница им соответствуют отрицательные значения;
• соответствующий им текст в диапазоне филиал — это текст Калуга или Тула.
Обратите внимание, что второе условие на самом деле состоит из двух условий, объединенных с помощью оператора ИЛИ. Эту задачу реализует следующая формула, в которой используется функция СУММЕСЛИМН:
СУММЕСЛИМН(Сумма;Разница;“<0”;Филиал;“Калуга”)+СУММЕСЛИМН(Сумма; Разница;“<0”;Филиал;“Тула”)
Следующая формула массива тоже справится с поставленной задачей:
{=СУММ((Разница<0)*ЕСЛИ((Филиал=“Калуга”)+(Филиал=“Тула”);1)*Сумма)