Формулы суммирования по нескольким условиям в Excel

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

Функция СУММЕСЛИМН для суммирования значений с учетом нескольких условий

Ниже на рисунке находится часть списка стран с показателями ВВП за период начиная от 2000-х и до 2005-х годов. Допустим нам необходимо суммировать показатель ВВП Бразилии за 2001-2004 год. Для суммирования значений по двум или нескольким условиям удобно использовать функцию СУММЕСЛИМН. В данном случае нам нужно учитывать:

1. Первое условие – только страна Бразилия.
2. Второе условие – суммировать ВВП только за 2001 и 2004 год.

Формула вычисления суммы показателей ВВП для указанной страны за определенный период лет:

Первым аргументом функции СУММЕСЛИНМ является диапазон ячеек, содержащие суммированные значения. Следующие аргументы объединены в пары по схеме Диапазон_условия1 вместе c Услвоие1. Количество аргументов функции – это всегда непарное число учитывая их последовательность. Первая пара критериев (второй и третий аргумент) являются обязательными для заполнения. Без них данная функция ничем не отличалась бы от обычной функции СУММ. Дополнительные последующие пары критериев не обязательны для заполнения. А их максимальное количество пар аргументов может быть достигнуто до 127-ми. То есть максимальное количество всех аргументов в функции СУММЕСЛИМН 127*2+1= 255.

В выше приведенном примере суммируются значения столбца C только тех ячеек, которые соответствуют значениям в столбцах A и B при выполнении определенных условий отбора. Условием для отбора ячеек в столбце A является совпадение значений с ячейкой F1, где указывается название страны для отбора. Год проверяется по двум условиям определяющих начальный год и конечный период времени. Начальный год записан в ячейке F2, а конечный – F3. Ссылки на эти ячейки через символ амперсант (&) объединены с оператором сравнения «больше или равно» (>=) и «меньше или равно» (<=) – соответственно. Значение ячейки столбца D будет учитываться в итоговой сумме только тогда, когда будут исполнены все три условия.



Альтернативная функция СУММПРОИЗВ для суммы с несколькими условиями

Функция СУММЕСЛИМН появилась лишь только в относительно новых версиях Excel 2010-го года. В старших версиях наилучшим способом суммирования значений с двумя или несколькими условиями – это было использование функции СУММПРОИЗВ. Она содержит только лишь один аргумент обязательный для заполнения, но с расширенными возможностями. В данном примере можно решить задачу с помощью другой формулы:

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

Excel в этой формуле воспринимает каждое логическое выражение как отдельную таблицу значений.

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

Принцип работы функции СУММПРОИЗВ основан на табличных массивах. Это такие таблицы, которые создаются в памяти и представляют сбой ничто иное как списки значений. В функции СУММПРОИЗВ каждый элемент таблицы сравнивается с соответственным ему значением. Например, в выше описанном примере сначала проверяется значение каждой ячейки в диапазоне A2:A20 на соответствие значению, записанному в F1. То есть функция ищет адреса ячеек, которые содержать значение «Бразилия». Результатом выполненных сравнений является очередная таблица, которая содержит список значений ИСТИНА и ЛОЖЬ, в соответствии с каждой ячейкой исходной таблицы. Эти результирующие логические значения в памяти называются элементами. Большая часть из них это естественно ложные элементы (в данном примере), но 6 элементов соответствуют ячейкам содержащих строку «Бразилия» и получают результативное значение ИСТИНА.

Четыре пары скобок соответствуют четырем таблицам, которые умножаются между собой. Фактически каждый элемент каждой таблицы перемножается с текущим элементом каждой другой таблицы. В Excel логическое значение ИСТИНА = 1, а ЛОЖЬ = 0. В результате если хотя бы один из 4-х умножаемых текущих элементов возвращает значение 0, результирующий текущий элемент также возвращает значение 0 – то есть ЛОЖЬ. Ведь любое число, умноженное на ноль = 0. Но, если вместо этого каждый текущий умножаемый элемент имеет значение ИСТИНА, тогда элемент в результирующей таблице будет иметь значение текущей ячейки в диапазоне D2:D20. Так как будут перемножены между собой три единицы и текущее значение с ячейки столбца D.

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

ЛОЖЬ*ИСТИНА*ИСТИНА*394,48

Другими словами:

(название страны это не Бразилия)*(год больше чем 2001)*(год меньше чем 2004)*(текущее значение ВВП)

Или так:

0*1*1*394,48=0

Первый ноль – это результат проверки первого табличного массива, который вернул логическое значение ЛОЖЬ – не равно Бразилия! Так как одно из умножаемых числе является нулем в результате простых арифметических операций также получаем 0.

В случае с 15-ой строкой листа складывается совсем другая ситуация. Все результаты проверки значений возвращают ИСТИНА, а значит =1. На этот раз при проверки первой ячейки выявлено, что она содержит текст «Бразилия». При проверке года на «больше или равно» >=2001 снова получаем ИСТИНА. Эта же ячейка с годом теперь проверяется по третьему условию «меньше или равно 2005» и в результате ИСТИНА. Таким образом выполняется следующая операция:

1*1*1*559,37=559,37

Для наглядности примера приведем таблицу:

Это итоговый результат вычислений для 15-ой строки листа 14-ой строки таблицы в памяти. По такому же принципу происходят операции и для остальных строк таблицы. По каждой строке выводится свой итоговый результат (0 или показатель ВВП) и суммируется функцией СУММПРОИЗВ. Данная функция весьма сложная и работает медленнее чем СУММЕСЛИМН, но ее все еще можно использовать в новых версиях Excel так как опытные пользователи привыкли к ней. Так же это дает возможность открывать старые версии файлов без ошибок в формулах. А при некоторых редких случаях она может быть даже более удобной других функций, поэтому стоит знать ее принципы работы.