Самоучитель VBA

Метод Subtotal



Метод Subtotal


Метод Subtotal добавляет промежуточные итоги в список данных, основываясь на изменениях в определенных полях данных. Промежуточные итоги позволяют обобщить данные. Метод Subtotal автоматически вставляет строки с промежуточными итогами, в которые введены формулы для подсчета итогов. Необходимо, чтобы до активизации этого метода данные были правильно отсортированы. В противном случае этот метод может привести к неверному выводу промежуточных итогов. Вручную метод subtotal вызывается командой Данные, Промежуточные итоги (Data, Subtotal).

Синтаксис:

Объект.Subtotal(GroupBy, Function, TotalList, Replace, PageBreaks, SummaryBelowData)

Аргументы:



Объект

Диапазон, для которого подводятся промежуточные итоги

GroupBy

Номер поля, по которому вычисляются промежуточные итоги

Function

Определяет функцию, по которой производится подсчет промежуточных итогов. Допустимые значения:

  • xlAverage (среднее)
  • xlcount (количество значений)
  • xlCountNums (количество чисел)
  • xlMax (максимум)
  • xlWin (минимум)
  • xlProduct (произведение)
  • xlStoev (несмещенное отклонение)
  • xlStoevp (смещенное отклонение)
  • xlSum (сумма)
  • xlVar (несмещенная дисперсия)
  • xlVarP (смещенная дисперсия)

TotalList

Массив целых чисел с номерами полей, по которым вычисляются промежуточные итоги,

Replace

Допустимые значения: True (существующие промежуточные итоги будут замещены) и False (в противном случае)

PageBreaks

Допустимые значения: True (после каждой группы будет вставлено по символу разрыва страницы) и False (в противном случае)

SummaryBelowData

Определяет местоположение для вывода промежуточных итогов. Допустимые значения: П xlSummaryAbove (промежуточные итоги будут выведены над данными) П и xlSummaryBeiow (промежуточные итоги будут выведены под данными)

Основным методом, связанным с Subtotal, ывляется метод RemoveSubtotal, удаляющий промежуточные итоги с рабочего листа. Метод RemoveSubtotal применяется к объекту Range.

Приведем соответствие между аргументами метода subtotal и подведением промежуточных итогов на рабочем листе вручную при помощи команды Данные, Промежуточные итоги (Data, Subtotal) на примере подсчета продаж компьютеров по месяцам и нахождения средних объемов продаж (Рисунок 3.20).

Рисунок 3.20. Отчет о продажах компьютеров

Шаг 1

Выделим ячейки списка, в данном случае диапазон A1:C16. Выберем команду Данные, Промежуточные итоги (Data, Subtotal). На экране отобразится диалоговое окно Промежуточные итоги (Subtotal) (Рисунок 3.21).

Рисунок 3.21. Диалоговое окно Промежуточные итоги

Рисунок 3.22. Результат выполнения команды Промежуточные итоги

Шаг 2

В диалоговом окне Промежуточные итоги (Subtotal):

  • Раскрывающийся список При каждом изменении в (Ateach change) устанавливает, по какому столбцу группируются данные. В данном случае выберем компьютер.
  • Раскрывающийся список Операция (Use function) устанавливает операцию, выполняемую над данными. Выберем Сумма (Sum).
  • Список Добавить итоги по (Add subtotal to) устанавливает данные, которые участвуют в расчетах. В данном случае выберем Количество.
  • Флажок Заменить текущие итоги (Replace current subtotals) определяет, надо ли заменить старые промежуточные итоги на вновь созданные. Сбросим этот флажок.
  • Флажок Конец страницы между группами (Page break between groups) определяет, надо ли вставлять символ конца страницы после каждой группы, для которой подводятся промежуточные итоги. Сбросим этот флажок.
  • Флажок Итоги под данными (Summary below data) определяет расположение промежуточных итогов под или над данными, по которым подводятся итоги. Расположим их под данными и поэтому установим флажок Итоги под данными (Summary below data).

ШагЗ

Нажмем кнопку ОК в диалоговом окне Промежуточные итоги (Subtotal), что приведет к созданию промежуточных итогов на рабочем листе по выделенному диапазону данных (Рисунок 3.22).

На VBA тот же результат достигается применением следующих инструкций:

Range("Al:C16")

.Select Selection. Subtotal GroupBy:=l, Function : =xlSum, TotalList:=Array (3) , Replace :=False, PageBreaks:=False, SuramaryBelowData : =True

Шаг 4

Добавим показатель средней реализации компьютеров по месяцам к уже найденным объемам их реализации. С этой целью выделим ячейки списка с данными и уже подведенными ранее итогами, в данном случае диапазон AI :С20. Выберем команду Данные, Промежуточные итоги (Data, Subtotal). На экране отобразится диалоговое окно Промежуточные итоги (Subtotal). В этом диалоговом окне:

  • В раскрывающемся списке При каждом изменении в (At each change) выберем компьютер.
  • В раскрывающемся списке Операция (Use function) выберем Среднее (Average).
  • В списке Добавить итоги по (Add subtotal) установим флажок Количество.
  • Сбросим флажок Заменить текущие итоги (Replace current subtotals).
  • Сбросим флажок Конец страницы между группами (Page break between groups).
  • Установим флажок Итоги под данными (Summary below data).

Шаг 5

Нажмем кнопку OK в диалоговом окне Промежуточные итоги (Subtotal), что приведет к добавлению показателя средней реализации компьютеров по месяцам к уже существующим промежуточным итогам на рабочем листе (Рисунок 3.23). На VBA тот же результат достигается применением следующих инструкций:

Range ("A1:C20") .Select Selection. Subtotal GroupBy:=l, Function: =xlAverage, TotalList:=Array (3) , Replace : =False, PageBreaks : =False, SummaryBelowData : =True

Шаг 6

Вместе с промежуточными итогами метод Subtotal создает структуру, которая позволяет управлять отображением детализации таблицы. Управляющими элементами структуры являются кнопки, отображаемые на левой стороне рабочего листа с номерами уровней иерархии, и кнопки, помеченные знаками <+> и <— >. Нажатие на кнопку <+> или <— > позволяет отобразить или скрыть детализацию данного элемента структуры, а на кнопки с номером уровня — детализацию целого уровня. Отобразим, например, только промежуточные итоги без их детализации. Для этого надо нажать сначала кнопку 2, а затем 3 (Рисунок 3.24). На VBA тот же результат достигается применением следующих инструкций:

ActiveSheet . Outline . ShowLevels RowLevels : =2 ActiveSheet . Outline . ShowLevels RowLevels : =3

Здесь свойство Outline рабочего листа возвращает объект Outline (структура), а свойство ShowLevels объекта outline устанавливает отображаемый уровень детализации.

Рисунок 3.23. Результат добавлению показателя средней реализации компьютеров по месяцам к уже существующим промежуточным итогам на рабочем листе

Шаг7

Для удаления промежуточных итогов следует выделить данные с этими итогами, выполнив команду Данные, Промежуточные итоги (Data, Subtotal) и нажать кнопку Убрать все (Remove All) в появившемся диалоговом окне Промежуточные итоги (Subtotal). На VBA тот же результат в данном примере достигается применением следующих инструкций

Range ("A1:C24") .Select Selection . RemoveSubtotal

Рисунок 3.24. Вывод промежуточных итогов без детализации



Содержание  Назад