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

         

Метод PivotTableWizard



Программно сводная таблица создается методом PivotTableWizard. Вручную на рабочем листе сводная таблица конструируется с помощью команды Данные, Сводная таблица (Data, Pivot Table and Pivot Chart Report).

Синтаксис:

Объект.PivotTableWizard(SourceType, SourceData, TableDestination,

TableName, RowGrand, ColumnGrand, SaveData, HasAutoFormat, AutoPage, Reserved, BackgroundQuery, OptimizeCache, PageFieldOrder, PageFieldWrapCount, ReadData, Connection)

Аргументы



Объект

Объект Worksheet (рабочий лист ) или PivotTable (сводная таблица)

SourceType

Тип источника данных. Допустимые значения:

  • xlConsolidation (консолидация нескольких диапазонов рабочих листов Excel)

  • xlDatabase (список или база данных Excel)

  • xlExternal (внешняя база данных)

    xlPivotTabie (сводная таблица)

  • SourceData

    Определяет вид источника данных в зависимости от значения аргумента SourceType:

  • Диапазон, если значением аргумента является xlDatabase

  • Массив строк, содержащий строку связи ODBC и SQL-оператор, если — xlExternal

  • Массив диапазонов, если — xlConsolidation

  • Имя существующей сводной таблицы, если -xlPivotTable

  • TableDestination

    Диапазон, где будет размещена сводная таблица

    TableName

    Имя создаваемой сводной таблицы

    RowGrand

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

    ColumnGrand

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

    SaveData

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

    HasAuto Format

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

    AutoPage

    Применим только при аргументе sourceType, равным xlConsolidation. Допустимые значения: True (Excel создает поле страницы) и False (пользователь должен создать поле)

    Reserved

    Не используется

    BackgroundQuery

    Допустимые значения: True (Excel выполняет запрос в фоновом режиме) и False (в последовательном)

    OptimizeCache

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

    PagePieldOrder

    Задает ориентацию поля страницы. Допустимые значения: xlDownThenOver (поле страницы располагается вертикально) и xlOverThenDown (поле страницы располагается горизонтально)

    PageFieldWrapCount

    Задает номер поля, с которого начинается новая страница. По умолчанию 0, т. е. отменена разбивка на страницы

    ReadData

    Допустимые значения: True (данные сразу считываются в кэш) и False (данные считываются в кэш по мере необходимости)

    Connection

    Используется для указания источника данных ODBC, источника данных URL и имени файла, содержащего запрос

    <
    С методом pivotTableWizard тесно связан метод PivotTables, применяемый к рабочему листу. Метод PivotTabies возвращает объект PivotTable или семейство сводных таблиц, размещенных на рабочем листе. Этот метод имеет два синтаксиса.

    Синтаксис 1:

    Объект.PivotTabies

    Возвращает семейство сводных таблиц. Здесь и во втором синтаксисе объект -рабочий лист.

    Синтаксис 2:

    Объект.PivotTables(Index)

    Возвращает сводную таблицу из семейства сводных таблиц с именем или номером, указанным в аргументе index .

    Объект PivotTable имеет следующие наиболее часто используемые методы.



    PivotFields



    Возвращает объект, являющийся либо единичным полем (синтаксис 1), либо семейством полей.

    Синтаксис 1:

    PivotFields (Index)

    Index — имя или номер поля сводной таблицы Синтаксис 2:

    PivotFields



    PivotSelect



    Выбирает элементы сводной таблицы.

    Синтаксис:

    PivotSelect (Name, Mode)

    Аргументы: G Name — строковое выражение, идентифицирующее выбранный элемент

  • Mode — специфицирует структуры выбранного элемента. Допустимые значения:

    xlBlanks, xiButton,

    xlDataAndLabel, xlDataOnly, xlLabelOnly или xlOrigin



  • RefreshTable



    Обновляет данные. Дело в том, что в сводной таблице не происходит автоматического перерасчета при изменении исходных данных. Для перерасчета сводной таблицы вручную надо ее выделить и выбрать команду Данные, Обновить данные (Data, Refresh Data). Программно перерасчет сводной таблицы производится методом RefreshTable



    AddFields



    Добавляет строки, столбцы и страницы в сводную таблицу.

    Синтаксис:

    AddFields (RowFields, ColumnFields, PageFields, AddToTable)

    Аргументы:

  • RowFields — специфицирует имя или массив имен полей, которые будут играть роль строк сводной таблицы

  • ColumnFields — специфицирует имя или массив имен полей, которые будут играть роль столбцов сводной таблицы

  • FageFields — специфицирует имя или массив имен полей, которые будут играть роль страниц сводной таблицы

    AddToTabie — допустимые значения: True (добавляет поля в сводную таблицу) и False (заменяет существующие поля)

  • <


    Объект pivotTabie имеет следующие наиболее часто используемые свойства.



    ColumnFields, RowFields, DataFields

    И

    PageFields



    Возвращает объект, являющийся либо единичным полем (синтаксис 1), либо семейством полей (синтаксис 2), который является столбцом (строкой, данными или страницей) сводной таблицы.

    Синтаксис 1:

    ColumnFields (Index) RowFields (Index) DataFields (Index) PageFields (Index)

    Index — имя или номер поля сводной таблицы

    Синтаксис 2:

    ColumnFields RowFields DataFields PageFields


    VisibleFields и HiddenFields



    Возвращает объект, являющийся либо единичным полем (синтаксис 1), либо семейством полей (синтаксис 2), который в данный момент отображается (скрыт) в сводной таблице.

    Синтаксис 1:

    VisibleFields (Index) HiddenFields (Index)

  • Index — имя или номер поля сводной таблицы

    Синтаксис 2:

    VisibleFields HiddenFields

  • Объект PivotField имеет следующие наиболее часто используемые свойства.

    Orientation Возвращает местоположение поля в сводной таблице.

    Допустимые значения:

    xlColumnField, xlDataField,

    xlHidden, xlPageField или xlRowField

    Возвращает позицию поля (первая, вторая и т. д.) среди полей того же местоположения

    Рассмотрим соответствие между аргументами метода pivotTabiewizard и созданием сводной таблицы вручную на рабочем листе с помощью команды Данные, Сводная таблица (Data, Pivot Table). Сводную таблицу будем создавать для отчета по продажам компьютеров сети из трех магазинов (рис. 6.2).



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



    Шаг 1



    Выберите команду Данные, Сводная таблица (Data, Pivot Table and Pivot Chart Report). Появится первое диалоговое окно мастера сводных таблиц (рис. 6.3). Выбор переключателей, расположенных под заголовком Создать таблицу на основе данных, находящихся: (Where is the data that you want to analyze?) позволяет установить источник данных для сводной таблицы.

  • Переключатель в списке или базе данных Microsoft Excel (Microsoft Excel List or Database) устанавливает создание сводной таблицы на основе списка данных, расположенных на рабочем листе.

  • Переключатель во внешнем источнике данных (External Data Source) устанавливает создание сводной таблицы на основе файлов или таблиц, созданных другими программами.

  • Переключатель в нескольких диапазонах консолидации (Multiple Consolidation Ranges) устанавливает создание сводной таблицы на основе нескольких списков.

  • Переключатель в другой сводной таблице (Another Pivot Table) устанавливает создание сводной таблицы на основе другой существующей сводной таблицы.

    Установите переключатель в списке или базе данных Microsoft

    Excel (Microsoft Excel List or Database), т. к. сводная таблица будет создаваться на основе одного списка активного рабочего листа. Нажмите кнопку Далее > (Next >)

  • <




    Рис. 6.3. Первое диалоговое окно мастера сводных таблиц



    Шаг 2



    На экране появится второе диалоговое окно мастера сводных таблиц (рис. 6.4). В поле Диапазон (Range) введите ссылку, например AI :Е1б, на диапазон, по которому будет строиться сводная таблица. Нажмите кнопку Далее > (Next >).



    Рис. 6.4. Второе диалоговое окно мастера сводных таблиц



    ШагЗ



    На экране появится третье диалоговое окно мастера сводных таблиц (рис. 6.5), в котором создается структура сводной таблицы.

  • Выберите поле, по которому будут подводиться итоги сводных таблиц, и перетащите кнопку, соответствующую этому полю в область Данные (Data). В данном случае перетащим кнопку Стоимость в область Данные (Data) (рис. 6.6). Двойной щелчок на кнопке Стоимость в области Данные (Data) приводит к отображению диалогового окна Вычисления поля сводной таблицы (PivotTable Field), позволяющее установить операцию, на основе которой будут подводиться итоги. Выберите операцию Сумма (Sum).

  • Выберите поля, которые будут образовывать строки сводной таблицы и перетащите кнопки, соответствующие этим полям в область Строка (Row). В данном случае строки сводной таблицы будут соответствовать магазинам. Поэтому перетащим кнопку магазин в область Строка (Row).

  • Выберите поля, которые будут образовывать столбцы сводной таблицы и перетащите кнопки, соответствующие этим полям в область Столбец (Column). В данном случае строки сводной таблицы будут соответствовать месяцам. Поэтому перетащим кнопку месяц в область Столбец (Column).

    Если вы хотите создать сводную таблицу в виде подшивки страниц, то перетащите кнопку, соответствующую полю, по которому будут строиться страницы в область Страница (Page). В данном случае сводная таблица, состоящая из страниц, не строится и поэтому не будем перетаскивать никакую кнопку в область Страница (Page). Нажмите кнопку Далее > (Next >).





  • Рис. 6.5. Третье диалоговое окно мастера сводных таблиц



    Рис. 6.6. Третье диалоговое окно мастера сводных таблиц после создания структуры сводной таблицы





    ШагЗ



    Появится последнее четвертое диалоговое окно мастера сводных таблиц (рис. 6.7). В этом окне устанавливается местоположение сводной таблицы. П Выбор переключателя новый лист (New worksheets) приводит к созданию нового рабочего листа и размещения на нем сводной таблицы. Выберите переключатель новый лист. П Выбор переключателя существующий лист* (Existing worksheets) позволяет разместить сводную таблицу в любом месте уже существующего листа. Для этого в поле Поместить таблицу в (Pivot Table Starting Cell) надо указать ссылку на ячейку, в которой будет располагаться левый верхний угол сводной таблицы. О Нажав кнопку Параметры (Options) можно вызвать диалоговое окно Параметры сводной таблицы (Pivot Table Options), где задаются имя сводной таблицы и ее формат. Нажмите кнопку Готово (Finish).



    Шаг 4



    Сводная таблица построена (рис. 6.8). Такую же сводную таблицу можно построить при помощи следующей последовательности инструкций VBA:

    ActiveSheet. PivotTableWizard SourceType:=xlDatabase, SourceData:= "Лист1 !R1C1:R16C5", TableDestination:="", TableName : ="СводнаяТаблица1 "

    ActiveSheet . PivotTables ( "СводнаяТаблица! " ) . AddFields RowFields : ="Магазин" , ColumnFields : ="Месяц"

    ActiveSheet . PivotTables ( "СводнаяТаблица! " } . PivotFields ("Стоимость") .Orientation = xlDataField

    Если в данных, по которым строилась сводная таблица, произошли изменения, для перерасчета сводной таблицы надо ее выделить и выбрать команду Данные, Обновить данные (Data, Refresh Data). Программно перерасчет сводной таблицы осуществляется следующими инструкциями:

    ActiveSheet . PivotTables ( "СводнаяТаблица! " ) . PivotSelect "", xlDataAndLabel

    ActiveSheet . PivotTables ("СводнаяТаблица!") . Ref reshTable



    Рис. 6.7. Четвертое диалоговое окно мастера сводных таблиц



    Рис. 6.8. Сводная таблица




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