Определение частичных сумм

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

Более мощным средством является Мастер частичных сумм, позволяющий сгенерировать формулу для подсчета суммы значений ячеек столбца, расположенных в строках, ячейки которых удовлетворяют заданным пользователем критериям. Вызывается Мастер через меню Сервис. В результате его работы в определяемую пользователем ячейку заносится формула массива, позволяющая получить нужную частичную сумму. Формула для вычисления частичной суммы может быть построена для таблиц, представляющих собой списки (базы данных) Excel.

Рассмотрим пример. В базе данных Excel находится информация о заказах клиентов: фамилия клиента, дата исполнения заказа и стоимость заказа. Требуется определить суммарную стоимость всех заказов со стоимостью свыше 150 руб., которые должны быть выполнены до 10.10.2001.

С помощью мастера частичных сумм для решения этой задачи построена формула массива

=СУММ(ЕСЛИ($B$2:$B$5<=ДАТАЗНАЧ("10.10.01");ЕСЛИ($C$2:$C$5>150;$C$2:$C$5;0);0))

записанная в ячейку C7 (рис. 28).

Полученную с помощью Мастера формулу можно модифицировать, если, например, требуется использовать более сложные критерии отбора данных, включающие не только операции сравнения, но и функции и т.п. Например: таблица содержит информацию и среднегодовых температурах (в столбце A записан год, а в столбце B – температура). Требуется определить максимальную среднегодовую температуру только для високосных лет. Формула массива будет выглядеть следующим образом:

=МАКС(ЕСЛИ(ОСТАТ($A$2:$A$11;4)=0;$B$2:$B$11;0))

(фигурные скобки появляются при вводе формулы с помощью комбинации Ctrl+Shift+Enter).