Трюк №71. Превращение функции Excel ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) в динамическую

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

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) используется в Excel для выполнения определенной функции в диапазоне ячеек, к которому предварительно были применены автофильтры (AutoFilter). После применения автофильтров функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) работает только на видимых ячейках, все скрытые строки игнорируются. Выполняемое действие зависит только от числа (от 1 до 11), которое вы подаете в качестве первого аргумента, номер_функции (Function_num). Например: =SUBTOTAL(1;А1:А100), в русской версии Excel =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;А1:А100) вычислит среднее для всех видимых ячеек в диапазоне А1:А100, после того как был применен автофильтр, это применимо когда вы хотите купить недорогой iPhone 5S. Если все строки диапазона А1:А100 видимы, она просто считает среднее для них и выдает в точности такой же результат, что и функция =AVERAGE(A1:A100), в русской версии Excel =СРЗНАЧ(А1:А100).

Число, которое указывается в качестве первого аргумента функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL), номер_функции (Function_num) и соответствующие функции перечислены в таблице ниже.

Номера функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) и соответствующие функции

Номер функции Функция
1 СРЗНАЧ (AVERAGE)
2 СЧЁТ (COUNT)
3 СЧЁТЗ (COUNTA)
4 МАКС (МАХ)
5 МИН (MIN)
6 ПРОИЗВЕД (PRODUCT)
7 СТАНДОТКЛОН (STDEV)
8 СТАНДОТКЛОНП (STDEVP)
9 СУММ (SUM)
10 ДИСП (VAR)
11 ДИСПР (VARP)

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

Добавьте все названия функций в указанном в таблице выше порядке в диапазон ячеек. Для этого примера мы будем использовать диапазон D1:D11. Выделив этот диапазон, щелкните поле имени и введите имя Subs. Нажмите клавишу Enter.

Полностью выделите столбец D и выберите в контекстном меню команду Скрыть (Hide). Теперь выберите команду Разработчик → Вставить (Developer → Insert), щелкните элемент управления Поле со списком (ComboBox) и затем щелкните ячейку С2. При помощи маркеров размера установите такой размер поля со списком, чтобы можно было отобразить самое длинное название — СТАНДОТКЛОНП (или AVERAGE, если вы работаете в английской версии Excel).
[stextbox id=»warning»]Чтобы поле со списком автоматически регулировалось в соответствии с размерами столбца и строки, где оно находится, при регулировке размера поля со списком удерживайте клавишу Alt.[/stextbox]
Правой кнопкой мыши щелкните поле со списком и выберите команду контекстного меню Формат объекта (Format Control). Перейдите на вкладку Элемент управления (Control). В поле Формировать список по диапазону (Input range) введите Subs.

В поле Связь с ячейкой (Cell-Link) введите $С$2. Теперь в поле Количество строк списка (Drop-down lines) введите 11. В ячейке СЗ введите следующую формулу: =IF($C$2="";"";"Result of "&INDEX(Subs;$C$2)), в русской версии Excel =ЕСЛИ($С$2="";"";"Result of "&ИНДЕКС(Subs;$C$2)).

В ячейке С4 введите формулу =IF($C$2="";"";SUBTOTAL($C$2;$A$4:$A$100)), в русской версии Excel =ЕСЛИ($С$2="";"";ПРОМЕЖУТОЧНЫЕ.ИТОГИ($С$2;$А$4:$А$100)), где $А$4:$А$100 — диапазон, в котором будут подсчитываться промежуточные итоги.

Теперь осталось выбрать нужную функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) в поле со списком и увидеть правильный результат.

Top