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

Подведение итогов предусматривает автоматический расчет значений по полям списка с использованием функций агрегирования данных (суммирование, расчет среднего, определение минимума или максимума и т.д.). Для подведения итогов в отфильтрованном списке существует специальная встроенная функция 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).

Теперь необходимо выделить ячейку E20 и вызвать инструмент Автосумма (кнопка или сочетание клавиш Alt + «=»). В результате средство Автосумма будет формировать промежуточные итоги (т.е. итоги по текущему содержимому списка), как показано на следующем рисунке.