Трюк №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) в поле со списком и увидеть правильный результат.