Vba excel pivottables методы

Обновлено: 06.07.2024

Программно сводная таблица создается методом 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 (сводная таблица)

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

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

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

• Диапазон, если значением аргумента является xlDatabase
  
• Массив строк, содержащий строку связи ODBC и SQL-оператор, если — xlExternal
• Массив диапазонов, если — xlConsolidation
  
• Имя существующей сводной таблицы, если -xlPivotTable
  

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

PivotSelect (Name, Mode)

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

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

xlDataAndLabel, xlDataOnly, xlLabelOnly или xlOrigin

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

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

AddFields (RowFields, ColumnFields, PageFields, AddToTable)

• RowFields — специфицирует имя или массив имен полей, которые будут играть роль строк сводной таблицы
• ColumnFields — специфицирует имя или массив имен полей, которые будут играть роль столбцов сводной таблицы
• FageFields — специфицирует имя или массив имен полей, которые будут играть роль страниц сводной таблицы
• AddToTabie — допустимые значения: True (добавляет поля в сводную таблицу) и False (заменяет существующие поля)
  

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

ColumnFields, RowFields, DataFields

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

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

ColumnFields RowFields DataFields PageFields

VisibleFields и HiddenFields

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

VisibleFields (Index) HiddenFields (Index)

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

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

xlHidden, xlPageField или xlRowField

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

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

Сводные таблицы являются одним из наиболее мощных средств Excel для анализа данных, помещенных в таблицы или списки. Сводные таблицы позволяют группировать данные и производить их анализ. Создавая сводные таблицы, пользователь оперирует именами полей, которые должны помещаться в ее строках и столбцах. Возможно также задание поля страницы, превращающего сводную таблицу в подшивку из нескольких страниц. Иерархически сводная таблица входит в рабочий лист. Все сводные таблицы рабочей книги образуют семейство pivotTabies (сводные таблицы), которое содержит в себе семейство pivotFieids (поля сводной таблицы) всех полей, входящих в сводную таблицу. Объект Pivotitem (элемент сводной таблицы) является конкретным элементом объекта pivotFieid. Все объекты Pivotitem образуют семейство Pivotitems. На рис. 6.1 показана иерархия этих семейств.

Рис. 6.1. Иерархия семейства объектов PivotTabies, PivotFieids и Pivotitems

Семейство PivotTabies имеет единственный метод item, возвращающий элемент этого семейства, т. е. конкретную сводную таблицу.

• Index — имя или номер возвращаемого элемента семейства PivotFieids
  

Из свойств семейства PivotTabies отметим только свойство Count, возвращающее число элементов этого семейства.

Программно сводная таблица создается методом 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 (сводная таблица)

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

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

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

Диапазон, если значением аргумента является xlDatabase
Массив строк, содержащий строку связи ODBC и SQL-оператор, если — xlExternal Имя существующей сводной таблицы, если -xlPivotTable

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

У объекта PivotCache 23 свойства. Большинство из них я рассмотрю:

• Connection - позволяет задать соединение с источником данных. Возвращает или устанавливает строку, имеющую разный синтаксис в зависимости от типа источника данных. Строка может задавать:
  • OLE DB установки для связи Excel с OLE DB источниками данных,
  • ODBC установки для связи Excel с ODBC источниками данных,
  • URL, когда Excel связывается с данными Web-страниц,
  • Полный путь, задающий текстовый файл или файл, задающий Web-запрос или базу данных.
  Строка начинается специальным ключевым словом, указывающим тип источника данных. В зависимости от варианта префикс, начинающий строку соединения, имеет вид - OLEDB; ODBC; URL; TEXT . Префикс заканчивается символом " ; " (точка с запятой). В остальном, строка удовлетворяет требованиям, предъявляемым к строке соединения при работе с ADO. Вот пример задания свойства Connection для соединения с базой данных Access с использованием провайдера Microsoft Jet:
  • Имя куба для OLAP кубов,
  • Текст команды, учитывающий специфику и требования провайдера,
  • Текст SQL-запроса,
  • Имя таблицы.

  Рассмотрим теперь методы объекта PivotCache . Их немного - всего три:

  • Function CreatePivotTable(TableDestination, [TableName], [ReadData]) As PivotTable . Этот метод (функция) создает объект PivotTable , основанный на данном кэше - объекте PivotCache . Это основной способ создания и появления объектов PivotTable .
    • Аргумент TableDestination представляет объект Range, задающий область построения сводной таблицы. Аргумент задает ячейку в левом верхнем углу этой области. Напомню, что объект PivotTable связан с определенным листом рабочей книги, поэтому аргумент должен определять и нужный рабочий лист, в противном случае будет выбран активный лист рабочей книги.
    • Аргумент TableName задает имя сводной таблицы - имя объекта PivotTable , которым можно пользоваться при работе с коллекцией PivotTables .
    • Булев аргумент ReadData позволяет установить способ чтения записей в кэш. Он имеет значение True , если в кэш читаются все записи.

    На этом я закончу рассмотрение свойств и методов объекта PivotCache . Примеры создания этого объекта приведу чуть позже, после рассмотрения объекта PivotTable , поскольку создавать эти объекты, тесно связанные между собой, следует в одной процедуре.

    Объект PivotTable и коллекция PivotTables

    Если коллекция PivotCaches связана с рабочей книгой, то коллекция PivotTables связана с отдельным листом этой книги. Коллекция PivotTables возвращается при вызове одноименного свойства объекта Worksheet . Обе коллекции устроены одинаково и имеют один и тот же набор свойств и методов. У коллекции PivotTables имеются следующие свойства: Application, Count, Creator, Parent . У нее есть также два метода - Item и Add . Из всего этого набора заслуживает рассмотрения только метод Add , позволяющий создать новый объект. Вот его синтаксис:

    Чуть выше я рассматривал, как основной способ создания объектов PivotTable , вызов метода CreatePivotTable объекта PivotCache . Я называл этот способ основным по той причине, что создание кэша не является самоцелью, - это не самостоятельный объект. Всегда он создается для того, чтобы связать с ним отчет сводной таблицы - объект PivotTable . Поэтому разумно, создав объект PivotCache тут же вызвать его метод CreatePivotTable , чтобы создать и объект PivotTable .

    Тем не менее, для создания объекта PivotTable есть возможность использовать метод Add коллекции PivotTables . Он выполняет ту же работу, что и метод CreatePivotTable , и имеет тот же набор аргументов. Дополнительно, в качестве первого аргумента, естественно, указывается объект PivotCache , на основе которого создается объект PivotTable .

    Чуть позже я приведу примеры, где будет показано применение обоих способов создания объекта PivotTable .

    Свойства и методы объекта PivotTable

    Объект PivotTable , задающий отчет сводной таблицы - его внешнее представление устроен, естественно, более сложно. У него значительно больше свойств, чем у объекта PivotCache , - их 54, да и методов в четыре раза больше. Замечу, что для программного создания сводной таблицы достаточно использовать лишь малую часть из этого набора. Большая часть этих свойств и методов необходима, если Вы хотите программно поддерживать работу пользователя со сводной таблицей.

    Давайте рассмотрим основные свойства этого объекта:

    • ColumnFields([Index]) As Object, DataFields([Index]) As Object, PageFields([Index]) As Object, RowFields([Index]) As Object . Все эти свойства имеют статус "только для чтения" и возвращают коллекцию или отдельный элемент коллекции, если указан индекс. Возвращаемые объекты задают поля сводной таблицы по соответствующему измерению - поля столбцов, данных, страниц или строк. Вне зависимости от измерения все возвращаемые объекты принадлежат единому классу PivotField или PivotFields для коллекций.
    • ColumnRange, DataLabelRange, DataBodyRange, PageRange, RowRange - возвращают объект Range , задающий соответствующую область. Вот простенькая процедура, поочередно выделяющая указанные области сводной таблицы:

    На этом я закончу рассмотрение свойств и перейду к рассмотрению методов:

    • Function AddFields([RowFields], [ColumnFields], [PageFields], [AddToTable]) . Позволяет добавить поля к соответствующему измерению. Последний булев параметр позволяет указать, будут ли поля добавляться или заменять существующий набор полей. В предыдущей версии
    • Function CalculatedFields() As CalculatedFields . Возвращает одноименную коллекцию вычисляемых полей.
    • Sub Format(Format As xlPivotFormatType) . Производит форматирование сводной таблицы. Аргумент Format задает один из возможных типов форматирования.
    • Function GetData(Name As String) As Double . Позволяет получить данные из отдельной ячейки сводной таблицы. Аргумент Name задает поля таблицы, однозначно определяющие ячейку. Он имеет достаточно сложный синтаксис, на деталях которого останавливаться не буду.
    • Function PivotCache() As PivotCache - возвращает объект PivotCache , связанный с отчетом.
    • Function PivotFields([Index]) As Object - возвращает одноименную коллекцию, а при указании индекса элемент этой коллекции, задающий поле сводной таблицы. В качестве индекса можно использовать имя поля. Возвращаемые объекты принадлежат классу PivotField . Позже в примере я продемонстрирую работу с этими объектами при программном формировании структуры сводной таблицы.
    • Sub PivotTableWizard([SourceType], [SourceData], [TableDestination], [TableName], [RowGrand], [ColumnGrand], [SaveData], [HasAutoFormat], [AutoPage], [Reserved], [BackgroundQuery], [OptimizeCache], [PageFieldOrder], [PageFieldWrapCount], [ReadData], [Connection]) . Этим методом, но не в виде процедуры, а в виде функции обладает и объект Worksheet . Вызванный этим объектом метод позволяет создать объект PivotTable . В предыдущих версиях Office этот способ был основным для создания подобных объектов. Теперь надобность в нем практически отпала. Метод моделирует работу Мастера сводных таблиц и имеет многочисленные аргументы, позволяющие определить сводную таблицу. Поскольку, как я сказал, теперь не следует пользоваться этим методом, то я не буду останавливаться на деталях его описания.
    • Function RefreshTable() As Boolean - обновляет данные сводной таблицы и возвращает значение True, если обновление прошло удачно.
    • Function ShowPages([PageField]) - создает новый отчет для каждого элемента в поле страниц. Каждый отчет создается на отдельной странице.

    Два способа создания объектов PivotCache и PivotTable

    Я уже говорил, что объекты PivotCache и PivotTable можно создавать несколькими способами, и рассмотрел методы, используемые в этих способах. Теперь пришла пора привести соответствующие процедуры, решающие эти задачи. Я приведу две процедуры, в каждой из которых создаются оба эти объекты, но в каждой из них это делается по-разному. Вот код первой из этих процедур:

    Обратите внимание, как создается объект PivotCache , - вначале он добавляется в коллекцию методом Add , но при этом объект не связан еще ни с каким источником данных и, следовательно, данных не содержит. На следующем этапе при заполнении свойств этого объекта - Connection, CommandType и CommandText - осуществляется связь с источником данных и выполняется команда, задающая запрос на получение данных. Соответствующий участок текста процедуры подсвечен.

    Так кэш становится заполненным. После этого, к нему можно привязать и отчет сводной таблицы. В данном варианте для построения объекта PivotTable используется метод CreatePivotTable объекта PivotCache .

    Заметьте, нельзя размещать сводную таблицу в области уже занятой другой сводной таблицей. По этой причине в процедуре проводится соответствующая проверка и область очищается, если она была занята. Приведу текст вызываемой процедуры ClearRegion :

    Надеюсь, она не требует особых пояснений. Напомню лишь, что свойство UsedRange возвращает всю область, занятую на рабочем листе. Эта область и очищается.

    Давайте рассмотрим теперь другой способ создания объектов PivotCache и PivotTable . Приведу код процедуры для этого варианта:

    В этом варианте построения кэша сводной таблицы за доставку данных от источника данных в программу отвечают хорошо нам знакомые объекты ADO. Этот вариант имеет несомненные достоинства, поскольку объекты ADO обеспечивают эффективную доставку данных и, что не менее важно, способны получать данные из самых разнообразных источников данных, которые теперь можно использовать для построения сводных таблиц.

    Работа с объектами ADO заканчивается построением объекта Recordset , задающего набор записей. Теперь остается ссылку на этот объект сделать значением одноименного свойства объекта PivotCache .

    Так кэш становится заполненным. После этого, к нему можно привязать и отчет сводной таблицы. В данном варианте для построения объекта PivotTable используется метод Add коллекции PivotTables .

    Чтобы закончить рассмотрение, остается привести текст вызываемых процедур CreateConnection и CreateRecordset :

    Процедуры CreateConnection и CreateRecordset нам хорошо знакомы, - их аналоги уже появлялись в главе 15 при рассмотрении объектов ADO. При работе с ними, как и ранее, используются глобальные объекты - Con1, Cmd1, Rst1 .

    Программное формирование структуры сводной таблицы

    Теперь, когда объекты PivotCache и PivotTable уже созданы, можно приступить к завершающему этапу - формированию структуры отчета сводной таблицы. Это означает, что нужно поля таблицы, содержащиеся в коллекции PivotFields распределить по измерениям. И здесь существует несколько способов выполнения этой работы. В предыдущих версиях я применял метод AddFields , который, правда, имел ряд ограничений. Теперь всю эту работу удобнее выполнять, работая непосредственно с объектами PivotField . Полного описания этих объектов давать не буду, но поясню, какими свойствами я пользовался, на примере формирования отчета сводной таблицы. Процедура, которую я сейчас приведу, полностью решает вопрос программного создания сводной таблицы, начиная от этапа связывания с источником данных, кончая этапом формирования структуры таблицы и группирования ее данных. Вот ее текст:

    Я приведу несколько комментариев:

    • На первом этапе работы создаются объекты PivotCache и PivotTable , для чего вызываются уже рассмотренные нами процедуры. Реально вызывается одна из этих процедур, вызов другой закомментирован. Какой вариант предпочесть - дело вкуса. О достоинствах этих вариантов я говорил.
    • После создания указанных объектов формируется структура отчета сводной таблицы. Для каждого из полей сводной таблицы задается соответствующее измерение и порядок расположения. Для этого используются свойства объектов PivotField - Orientation и Position . Первое из них задает измерение, второе - порядок в измерении. Добраться до нужного поля позволяет коллекция PivotFields , где в качестве индекса используется имя поля.
    • На заключительном шаге производится группирование данных по полю "Дата заказа". В данном случае я группирую данные по неделям. Скажу несколько слов о методе группирования данных - Group, производящем эту операцию. Он является методом класса Range и, следовательно, может вызываться объектом Selection . Здесь применяется его форма, специально созданная для группирования данных сводной таблицы. При группировании дат булев массив Periods указывает одну из 7 возможных единиц группирования (секунду, минуту, час, день, месяц, квартал, год), а параметр BY задает количество единиц в группе.

    Единственное, что осталось сделать для завершения рассказа о программном создании отчета сводной таблицы, - это посмотреть на результаты работы нашей процедуры:

    
    

    
    увеличить изображение
    Рис. 8.25. Программно построенная сводная таблица

    Методы прогнозирования

    Сводные таблицы позволяют анализировать прошлое и настоящее. Прогнозирование - это способ заглянуть в будущее. Любая направленная деятельность предполагает построение прогноза параметров, определяющих эту деятельность. Есть два пути прогнозирования. Первый - построить модель поведения исследуемого параметра, основанную на причинно-следственных связях, изучении законов его поведения. Так, довольно просто описать траекторию полета ракеты, подчиняющуюся законам небесной механики. Траекторию управляемого пилотом самолета описать труднее. Еще сложнее описать "траекторию" спроса на тот или иной продукт, поскольку она определяется действиями большого числа "пилотов", которые в любой момент могут начать или перестать покупать фирменный продукт.

    Второй путь - статистическое прогнозирование, позволяет, не вдаваясь в механику движения, предсказать будущее поведение, анализируя полученную статистику поведения в прошлом. Статистическое прогнозирование - неотъемлемый атрибут экономической деятельности любого масштаба. Подобный прогноз может быть краткосрочным или среднесрочным. В первом случае прогноз базируется на данных за короткий период времени (например, месяц) и строится на один-два момента вперед. Такой прогноз обычно должен быть оперативным и непрерывным. Среднесрочный прогноз определяет поведение в отдаленном будущем, скажем, на год вперед. Он требует больше данных и специальных методов, отличных от методов краткосрочного прогноза.

    О методах прогноза написано много. В документации можно найти обширный список литературы, использованной при построении стандартных процедур. Мы пользовались другими, доступными для нас источниками, и позволим себе без подробных обоснований привести несколько методов. О регрессионном анализе и оценках по методу наименьших квадратов можно прочитать в любом хорошем учебнике по математической статистике. Замечу, что те, кому экскурс в разделы статистики покажется утомительным, могут без особого ущерба пропустить его, и перейти к чтению разделов, где непосредственно рассматриваются соответствующие средства Excel.

    Методы краткосрочного прогноза

    Применяемые при краткосрочном прогнозе методы основываются на разных моделях поведения спроса. Наиболее часто используются модели:

    • устойчивого (постоянного) спроса;
    • линейно изменяющегося спроса (возрастающего или убывающего);
    • сезонного спроса;
    • комбинации этих моделей.

    Метод экспоненциального сглаживания

    В модели устойчивого спроса методы прогноза основаны на скользящем среднем, где вычисляется средневзвешенное значение по результатам предыдущих измерений. Весь вопрос в том, какой временной интервал учитывать и какие веса приписывать данным. Один из простых и лучших методов - экспоненциальное сглаживание, описываемое соотношением:

    
    

    где S_t - фактический спрос в момент времени t , а P_t - его оценка, экстраполируемая на будущее. Формула показывает, что оценка является взвешенной суммой последнего полученного значения спроса и предыдущей оценки. Параметром метода, устанавливаемым эмпирически, является весовой коэффициент . Чем меньше , тем большее значение придается прошлым данным. Если же большего доверия заслуживают последние данные, следует увеличивать. Рекомендуемые значения обычно выбираются из интервала 0.1-0.5 .

    Метод Чоу адаптивного прогнозирования позволяет подбирать в процессе прогноза. Его суть состоит в том, чтобы одновременно вести три прогноза с разными значениями , например 0.1, 0.15 и 0.2 . Если реальный спрос ближе к одной из границ, скажем, верхней, система перестраивается, и новыми значениями будут 0.15, 0.2 и 0.25 .

    Я думаю, вы знакомы с созданием и восстановлением сводных таблиц вручную, и это руководство поможет вам использовать VBA для динамического создания и восстановления сводных таблиц. Статья использовалась для Excel 2000.

    Предположим, что на листе 1 у меня есть блок данных для анализа, как показано на фигура 1 . Этот блок данных включает поля: SalesRep (торговый представитель), Region (регион), Month (месяц), Sales (продажи).

    
    

    Перед созданием сводной таблицы, например фигура 2 , Я выбрал «Записать новый макрос». как показано в Рисунок 3 , чтобы увидеть, как записывается код.

    
    

    
    

    Затем я перехожу на экран VBE, нажимая Alt + F11 . Я вошел Модуль1 , увидев следующий код:

    Макро записано 17.03.2003 Дуйе

    ActiveWorkbook.PivotCaches.Add (SourceType: = xlDatabase, SourceData: = _

    ActiveSheet.PivotTables («Сводная таблица1»). SmallGrid = False

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

    Поле строки в сводной таблице.

    Поле столбца в сводной таблице.

    Поле данных в сводной таблице с использованием функции Sum

    ActiveSheet.PivotTables («Сводная таблица1»). Сводные поля («Продажи»). Ориентация = _

    Изучите написанный код:

    Чтобы исследовать приведенный выше код, вам необходимо знать некоторые связанные объекты. Все эти объекты описаны в интерактивной справке.

    представляет собой набор объектов PivotCache в объекте Workbook

    представляет собой набор объектов сводной таблицы в объекте Workbook

    Поля сводной таблицы

    представляет собой набор полей в объекте сводной таблицы

    Создать сводную таблицу

    метод объекта PivotCache для создания сводной таблицы с использованием данных в PivotCache

    Мы можем переписать описанную выше процедуру, используя CreatePivotTable процедура (обратите внимание, что вы вводите эту процедуру в module1), она может быть немного длинной, но ее будет легче понять, и вы можете запустить программу где угодно, нажав Alt + F8 комбинацию клавиш, затем выберите CreatePivotTable процедуру и выберите Запустить как показано в Рисунок 4 .

    
    

    Dim PTCache как PivotCache

    Dim PT As PivotTable

    Потрите сводную таблицу около уха

    
    

    При ошибке Возобновить Далее

    При ошибке GoTo 0

    Tao Pivot Cache

    Установите PTCache = ActiveWorkbook.PivotCaches.Add _

    (SourceType: = xlDatabase, _

    Создать новый рабочий лист и каталог

    Tao Pivot Table tu Cache

    Установите PT = PTCache.CreatePivotTable _

    .PivotFields («Месяц»). Ориентация = xlColumnField

    Когда вы закончите описанную выше процедуру, вы получите сводную таблицу на листе 2, в этом случае лист называется сводной таблицей. ( Рисунок 5. )

    Теперь предположим, что я добавил Цель поле в блоке данных, а в Сводная таблица Я добавлю целевое поле, а также добавлю Дисперсия поле расчета. Это поле (Дисперсия) будет равно Объект скидок . Мой новый блок данных показан в Рисунок 6 .

    Код в CreatePivotTable Приведенная выше процедура будет добавлена ​​следующим образом (я только что добавил в абзаце With PT. End With):

    
    

    .PivotFields («Месяц»). Ориентация = xlColumnField

    Заменить заголовок doi

    После повторного запуска вышеупомянутой процедуры я буду похож на Рисунок 7 .

    .PivotFields («Месяц»). Ориентация = xlColumnField

    
    

    Полная учетная запись

    .PivotFields («Месяц»). PivotItems («Q1»). Позиция = 4

    .PivotFields («Месяц»). PivotItems («Q2»). Позиция = 8

    Заменить заголовок doi

    После повторного запуска процедуры CreatePivotTable я получу результат, как показано на Рисунок 9 .

    Что ж, подойдите сюда, видите ли, если мы будем знать, как использовать VBA, анализ данных станет проще. Кроме того, мы также можем создать сводную таблицу из внешнего источника данных, такого как Access. Для хорошего программирования сводных таблиц вы должны прочитать раздел онлайн-справки Excel по объектам, методам и атрибутам, упомянутым выше.

    Читайте также:

      
    • Wa ошибка в программе
      
    • Сертификат не связан ни с одним абонентом системы 1с эдо
      
    • Разработчики браузеров рекомендуют размещать на странице не более 1500 элементов dom
      
    • Программы для изучения анатомии на компьютер
      
    • Как найти и удалить скрытые apk файлы с андроида