Алексей Гладкий - Компьютер для бухгалтера

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

Ее можно записать следующим образом:

Учтите, что при вводе формул массива нужно нажимать не клавишу Enter, а комбинацию клавиш Ctrl+Shift+Enter. Это необходимо для того, чтобы программа автоматически заключила формулу в фигурные скобки.

В итоге формула должна принять следующий вид:

Если вы попытаетесь обхитрить Excel и поставите фигурные скобки самостоятельно, то формула функционировать не будет – расстановку скобок программа должна выполнить автоматически. Это одна из распространенных ошибок, часто допускаемых не только начинающими, но и опытными пользователями. Также не стоит забывать, что применение формул массива может привести к замедлению пересчета в Excel. Особенно если эти формулы включают в себя значительное количество ссылок на большие диапазоны.

Существует еще один способ решения поставленной задачи. Сущность его заключается в использовании дополнительного столбца (для примера возьмем столбец В) для ссылки на ячейки, находящиеся в столбце А. Смысл применения этих ссылок состоит в том, что они отобразят результат в столбце В только в том случае, если значение будет соответствовать заранее определенному условию (в нашем примере – находиться в диапазоне от 10 до 20).

Установите курсор в ячейку В1и введите в нее формулу следующего вида:

Затем введите данную формулу в каждую ячейку столбца В до ячейки В100включительно.

Быстро скопировать формулу в расположенные ниже ячейки можно следующим образом. Введите формулу в первую ячейку диапазона, выделите весь диапазон (включая эту ячейку), затем на вкладке Главнаяв разделе Редактированиенажмите кнопку Заполнить(название кнопки отображается в виде всплывающей подсказки при подведении к ней указателя мыши) и в открывшемся меню выберите команду Вниз.

Если столбец А у вас уже заполнен, то в столбце В вы увидите только значения, удовлетворяющие заданному условию (то есть находящиеся в диапазоне от 10 до 20).

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

Оба рассмотренных выше способа довольно эффективно решают задачу, однако в Excel включена функция, возможности которой предусматривают применение одновременно нескольких условий (именно в этом часто нуждаются бухгалтеры, которые часто и помногу вынуждены работать с большими объемами данных).

Эта функция (она называется БДСУММ) является одной из функций баз данных. Чтобы на конкретном примере увидеть, каким образом она работает, используем тот же самый набор чисел, расположенных в диапазоне А2:А100, с которым мы уже работали ранее.

Выделите диапазон ячеек C1:D2и присвойте ему имя SumCriteria (напомним, что имя диапазона вводится в специально предназначенном поле, которое находится слева от строки формул, см. выше раздел «Строка формул»). После этого установите курсор в ячейку С1и введите в нее значение =$A$1 (иначе говоря, мы ввели в ячейку С1ссылку на ячейку А1, которая является первой ячейкой рабочего листа). Теперь скопируйте данную ссылку в ячейку D1, которая находится в этом же диапазоне – в результате вы получите две копии заголовка столбца А. В дальнейшем эти созданные копии нам понадобятся в качестве заголовков условий функции БДСУММ (C1:D2) для диапазона, который мы назвали SumCriteria.

На следующем этапе в ячейку С2 следует ввести значение >10, а в ячейку D2 – значение <20. Как нетрудно догадаться, это условие, в соответствии с которым значения должны находиться в диапазоне от 10 до 20. Теперь установите курсор в ячейку, предназначенную для отображения результата, и введите в нее формулу следующего вида: