Подведение итогов в отфильтрованном списке

Подведение итогов предусматривает автоматический расчет значений по полям списка с использованием функций агрегирования данных (суммирование, расчет среднего, определение минимума или максимума и т.д.). Для подведения итогов в отфильтрованном списке существует специальная встроенная функция Excel – ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Синтаксис

ПРОМЕЖУТОЧНЫЕ.ИТОГИ (Номер функции; Ссылка1; Ссылка2;…)

где Номер функции – число от 1 до 11, определяющее какую функцию необходимо использовать при вычислении итого внутри списка:

Ссылка1, Ссылка2 - диапазоны ячеек, наименование диапазонов или списков по которым необходимо рассчитывать итоги.

Предположим, реализация товаров какого-либо предприятия фиксируется в списке следующего вида:

Необходимо обеспечить выборку значений по произвольным критериям (включить Автофильтр):

1. Выделить ячейки шапки таблицы.

2. Данные ® Фильтр ® Автофильтр.

Необходимо присвоить списку определенное имя, для чего:

1. Выделяем область ячеек списка вместе со строкой заголовков полей.

Внимание! Следует также отметить, что диапазон списка при присвоении ему определенного имени должен захватывать минимум одну пустую строку после самой последней строк списка, чтобы обеспечить автоматическое расширение размерности списка в дальнейшем при использовании функции Вставка ® Строки.

В этом случае происходит автоматическое расширение размерности диапазона, что снимает необходимость изменения параметров диапазона после расширения списка за счет добавления новых записей.

2. Вставка ® Имя ® Присвоить или Ctrl + F3.

3. Вводим без пробелов в поле имя диапазона значение
РеализацияТоваров.

4. Нажимаем Ok.

После проведенных действий можно выполнить любую выборку, воспользовавшись технологией автофильтрации.

Создадим строку итогов по отфильтрованному списку, куда запишем необходимые формулы для подведения итогов. Так как список (база данных) может расширяться, то строку итогов сделаем на одну строку ниже самой последней строки списка.

Воспользуемся функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ для подведения итогов в графах «Кол-во» и «Сумма».

Для графы «Кол-во» формула в строке итогов примет вид

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;E4:E18)

где 9 – означает тип итогов – функция СУММ; E4:E18 – диапазон, по которому проводятся промежуточные итоги.

Для графы «Сумма» формула в строке итогов примет вид

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;G4:G18)

Результат представлен на рисунке:

Теперь после задания строки итогов установка при любом отборе с помощью автофильтра итоговая строка будет рассчитана автоматически. Т.е. можно выбрать, например, объем продаж по товару «Товар1» установив в автофильтре таблицы в графе «Наименование товара» критерий «Товар1», выбрав его из списка. В результате список будет отфильтрован по товару «Товар1» и будет содержать информацию только по этому товару. Так как строка итогов рассчитывается только по текущему содержимому списка, то в ней будут получены итоги по товару «Товар1» как это показано на рисунке.

Если тип промежуточных итогов – суммирование значений (как в рассмотренном выше примере) , то для создания промежуточных итогов можно использовать средство Автосумма. Для этого необходимо установить какой-либо фильтр (например, Наименование товара = Товар2).

Теперь инструмент Автосумма будет формировать промежуточные итоги, что видно из рисунка ниже.