Функция «Промежуточные итоги»и Автофильтр

2. Функция «Промежуточные итоги»и Автофильтр

Функция «Промежуточные итоги» (английский вариант SUBTOTAL) используется для вычисления промежуточного итога (сумма, среднее, количество значений и т.д.) в диапазоне, в котором имеются скрытые строки.

Особенность функции состоит в том, что она предназначена для использования совестно с «Автофильтром».

2.1 Синтаксис функции

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

Номер_функции- это число от 1 до 11, которое указывает какую функцию использовать при вычислении итогов внутри списка.

Например, функция СУММ() имеет код 9. Функция СУММ() также имеет код 109, т.е. можно записать формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A10) или =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;A2:A10). Обычно используют коды функций от 1 до 11.

2.2 Применение фунции «Промежуточные итоги» иАвтофильтра

Ссылка1; Ссылка2; — от 1 до 29 ссылок на диапазон, для которых подводятся итоги (обычно используется один диапазон).

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() разработана для столбцов данных или вертикальных наборов данных. Она не предназначена для строк данных

или горизонтальных наборов данных (ее использование в этом случае может приводить к непредсказуемым результатам).

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

Применим автофильтр.

Автофильтр - это удобный инструмент для отбора в таблице строк, соответствующих условиям, задаваемым пользователем. Для нормальной работы автофильтра требуется "правильно" спроектированная таблица. Правильная с точки зрения EXCEL - это таблица без пустых строк/столбцов, с заголовком, с однотипными данными в столбце (т.е. не нужно смешивать в одном столбце данные разных форматов, например текст и числа или числа и даты).

Чтобы включить фильтр выделите любую ячейку таблицы, затем на вкладке Данные в группе Сортировкаи фильтр выберите команду Фильтр (Данные/ Сортировка и фильтр/ Фильтр) или нажмите сочетание клавиш CTRL+SHIFT+L.

В строке заголовка появятся стрелки раскрытия фильтра.

Отберем только строки с товаром Товар1. Пусть функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает сумму товаров Товар1, следовательно будем использовать код функции 9 или 109.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() исключает все строки не включенные в результат фильтра независимо от используемого значения константы номер_функции и, в нашем случае, подсчитывает сумму отобранных значений (сумму цен товара Товар1). Если бы мы записали формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B11:B20) или =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;B11:B20), то мы бы подсчитали число отобранных фильтром значений (5). Таким образом, эта функция "чувствует" скрыта ли строка автофильтром или нет.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() может подсчитать сумму, количество и среднее отобранных значений, а также включает еще 8 других функций (см. синтаксис). Как правило, этик функций вполне достаточно, но иногда требуется расширить возможности функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ().