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


Метод PivotTableWizard



Метод 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).









Начало  Назад  Вперед