Говоря о стандартных функция Excel нельзя не упомянуть о функции "ПРОМЕЖУТОЧНЫЕ.ИТОГИ". Её особый статус обусловлен не только необычным названием и синтаксисом, но и тем, что она очень универсальна и обладает достаточной степенью интерактивности.
Как уже неоднократно замечалось, Excel для каждой конкретной задачи может предоставить небольшую функцию, которая эту задачу решит. Функция "ПРОМЕЖУТОЧНЫЕ.ИТОГИ" способна решать аж 11 (!!!) задач. И это не шутка.
Зачем же в Excel создана эта функция и почему мы используем множество других, вроде "СУММ" или "СЧЁТЗ", а не её? Ответ прост. Эта функция (как понятно из ее названия) работает с промежуточными итогами. Так тоже пока не понятно. Давайте разберемся. Когда мы имеем дело с какой-либо большой таблицей содержащей множество строк и столбцов, то мы используем фильтры для получения быстрых выборок данных. Поясню на примере. Есть у нас такая таблица.
Для подсчета количество позиций в столбце "ФИО" мы решили использовать функцию "СЧЁТЗ", а для подсчета количества единиц (чего-либо) в столбце "Количество ед." используем функцию "СУММ". Все работает корректно. Но вот нам стало необходимо выбрать данные по работникам подразделения C. Ставим фильтр, выбираем необходимое подразделение и...
Получаем все те же итоговые значения. Почему же? Функции "СЧЁТЗ" и "СУММ", равно как и все остальные математические функции строго привязаны к данным в используемом диапазоне. Т.е. мы отфильтровали данные, скрыли их из видимой области, но они продолжают использоваться в расчетах. Но нам же необходимо посмотреть итоги по выборке!
Вот тут то на помощь и приходит функция "ПРОМЕЖУТОЧНЫЕ.ИТОГИ". Сначала немного о ее синтаксисе. Он необычный, да, но и особых сложностей с ним не возникает.
= ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции; [ссылка 1], [ссылка 2], ...)
Разберемся с номерами функции. Их 22 штуки:
Номера с 1 по 11 используются если мы хотим включать в расчеты по выборке все данные, в т.ч. и скрытые (не с помощью фильтра, а при помощи пункта контекстного меню "скрыть"). Номера функций 101-111 используются если для всех расчетов нам нужны только видимые данные, т.е. буквально данные, которые сейчас видны в таблице.
Попробуем использовать эту функцию в нашем примере. Там, где мы считали количество позиций в столбце "ФИО" выбираем функцию 3 и указываем весь диапазон данных.
Для подсчета суммы используем функцию номер 9.
Смотрим итоги. Они точно совпадают с теми, что мы получаем от использования стандартных функцию. Пробуем, как и ранее, отфильтровать данные для подразделения "C".
Вуаля, итоги для выборки теперь формируются верно. Рекомендую использовать эту функцию если вам необходимо работать с большой таблицей и время от времени получать информацию по отдельным составляющим. Это хорошая альтернатива получению данных сводных таблиц (если еще плохо с ними работаете).
Также посмотрите:
Полезные функции Excel: "СЧЁТ" и "СЧЁТЗ"
Полезные функции Excel: убираем дубликаты
Полезные функции Excel: условное форматирование
Полезные функции Excel:"СУММЕСЛИ"