Изменить источник данных сводной таблицы vba excel
Сводные таблицы являются одним из наиболее мощных средств 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:
Массив строк, содержащий строку связи 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), либо семейством полей.
После создания сводной таблицы можно изменить диапазон исходных данных. Например, расширить его и включить дополнительные строки данных. Однако если исходные данные существенно изменены, например содержат больше или меньше столбцов, рекомендуется создать новую сводную таблицу.
Вы можете изменить источник данных для таблицы Excel на другую таблицу или диапазон ячеок либо другой внешний источник данных.
Щелкните Отчет сводной таблицы.
На вкладке "Анализ" в группе "Данные" нажмите кнопку "Изменить источник данных" и выберите "Изменить источник данных".
Отобразилось диалоговое окно "Изменение источника данных в pivotTable".
Выполните одно из указанных ниже действий.
Чтобы изменить источник данных для таблицы Excel на другую таблицу или диапазон ячеок, щелкните "Выбрать таблицу или диапазон", а затем введите первую ячейку в текстовом поле "Таблица или диапазон" и нажмите кнопку "ОК".
Чтобы использовать другое подключение, сделайте следующее:
Выберите "Использовать внешний источник данных" инажмите кнопку "Выбрать подключение".
Отобразилось диалоговое окно "Существующие подключения".
В списке "Показать" в верхней части диалоговых окнах выберите категорию подключений, для которых нужно выбрать подключение, или выберите "Все существующие подключения" (значение по умолчанию).
Выберите подключение в списке "Выберите подключение" и нажмите кнопку "Открыть".Что делать, если подключения нет в списке?
Примечание: При выборе подключения из категории "Подключения" в этой категории будет повторное использование или совместное использование существующего подключения. Если выбрать подключение из файлов подключения в сети или файлов подключения в этой категории компьютеров, файл подключения будет скопирован в книгу как новое подключение к книге, а затем использован в качестве нового подключения для отчета pivottable.
Что делать, если подключения нет в списке?
Если подключения нет в диалоговом окне "Существующие подключения", нажмите кнопку "Обзор дополнительных данных" и найдите нужный источник данных в диалоговом окне "Выбор источника данных". Если необходимо, щелкните Создание источника и выполните инструкции мастера подключения к данным, а затем вернитесь в диалоговое окно Выбор источника данных.
Если сводная таблица основана на подключении к диапазону или таблице в модели данных, сменить таблицу модели или подключение можно на вкладке Таблицы. Если же сводная таблица основана на модели данных книги, сменить источник данных невозможно.
Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
Щелкните Отчет сводной таблицы.
На вкладке "Параметры" в группе "Данные" нажмите кнопку "Изменить источник данных" и выберите "Изменить источник данных".
Отобразилось диалоговое окно "Изменение источника данных в pivotTable".
Выполните одно из указанных ниже действий.
Чтобы использовать другую таблицу или диапазон ячеок Excel, щелкните "Выбрать таблицу или диапазон", а затем введите первую ячейку в текстовом поле "Таблица или диапазон".
Вы также можете нажать кнопку "Свернуть ", чтобы временно скрыть диалоговое окно, выбрать ячейку в начале, а затем нажать кнопку "Развернуть ".
Чтобы использовать другое подключение, выберите "Использовать внешний источник данных" и нажмите кнопку "Выбрать подключение".
Отобразилось диалоговое окно "Существующие подключения".
В списке "Показать" в верхней части диалоговых окнах выберите категорию подключений, для которых нужно выбрать подключение, или выберите "Все существующие подключения" (значение по умолчанию).
Выберите подключение в списке "Выберите подключение" и нажмите кнопку "Открыть".
Примечание: При выборе подключения из категории "Подключения" в этой категории будет повторное использование или совместное использование существующего подключения. Если выбрать подключение из файлов подключения в сети или файлов подключения в этой категории компьютеров, файл подключения будет скопирован в книгу как новое подключение к книге, а затем использован в качестве нового подключения для отчета pivottable.
Что делать, если подключения нет в списке?
Если подключения нет в диалоговом окне "Существующие подключения", нажмите кнопку "Обзор дополнительных данных" и найдите нужный источник данных в диалоговом окне "Выбор источника данных". Если необходимо, щелкните Создание источника и выполните инструкции мастера подключения к данным, а затем вернитесь в диалоговое окно Выбор источника данных.
Если сводная таблица основана на подключении к диапазону или таблице в модели данных, сменить таблицу модели или подключение можно на вкладке Таблицы. Если же сводная таблица основана на модели данных книги, сменить источник данных невозможно.
Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Мы обычно создаем сводную таблицу, когда нам нужно создать какие-то графики или диаграммы, или нам нужно провести некоторый анализ над ней. Подготовив сводную таблицу, мы можем получить общее представление и представление о том, что на самом деле находится внутри данных. Это лучший способ найти способ получить данные. И каждый раз, когда мы вносим какие-либо изменения в данные, нам также необходимо обновлять сводную таблицу. Так что он будет заполнять обновленный счетчик данных. Обновление любой сводной таблицы очень просто, но что делать, если у нас есть 10сек сводных таблиц в одном файле Excel, который нам нужно обновить. Таким образом, вместо обновления всех сводных таблиц одну за другой, мы можем напрямую обновить все сводные таблицы за один раз с помощью VBA Refresh Pivot Table.
Как использовать Обновить сводную таблицу в Excel VBA?
Ниже приведены различные примеры использования Обновить сводную таблицу в Excel с использованием кода VBA.
Вы можете скачать этот шаблон Excel для сводной таблицы обновления VBA здесь - шаблон Excel для сводной таблицы обновления VBA
Сводная таблица обновления VBA - пример № 1
Для обновления сводной таблицы, во-первых, нам нужно создать сводную таблицу. Ниже у нас есть данные о позиции 1000 с, по которой мы будем создавать сводную таблицу.
На приведенном выше снимке экрана мы видим последнюю строку в 1001, и эти данные содержат информацию о покупателе и количество, проданное им.
Теперь перейдите на вкладку меню « Вставка » и выберите опцию « Сводная таблица» .
Мы получим окно Создать сводную таблицу. Оттуда сначала выберите диапазон таблицы, который мы хотим включить в сводную таблицу. Затем выберите любое место в текущем листе для сводной таблицы. Мы также можем выбрать новый рабочий лист.
После этого нажмите кнопку ОК.
Мы создадим сводную таблицу. Теперь перетащите необходимые поля в разные области, чтобы получить актуальную сводную таблицу. Здесь мы перетаскиваем имя клиента в ROWS, а количество в COLUMNS, как показано ниже.
Это завершает создание сводной таблицы. Но нам нужно обновить сводную таблицу после изменения чего-либо в Data, это можно сделать, просто обновив опцию из раскрывающегося списка правой кнопкой мыши, как показано ниже.
Но тот же процесс может быть автоматизирован и через VBA. Для этого нам понадобится модуль. Так,
Шаг 1: Перейдите на вкладку меню «Вставка» и выберите «Модуль» в раскрывающемся списке, как показано ниже.
Шаг 2: В недавно открытом модуле напишите подкатегорию VBA Pivot Refresh, или мы можем выбрать любое имя по нашему выбору.
Код:
Шаг 3. Сначала определите переменную как PivotCache, как показано ниже. PivotCache использует сводный кеш, а не данные, используемые для создания сводной таблицы.
Код:
Шаг 4: Теперь мы будем использовать цикл For-Each . Так что откройте цикл For-Each, как показано ниже.
Код:
Шаг 5: Теперь внутри цикла For-End мы напишем условие, в котором мы выберем текущий открытый лист с таблицей Pivot с PivotCache .
Код:
Шаг 6: Теперь мы будем использовать команду Обновить, назначив ей определенную переменную Table .
Код:
Шаг 7: Это завершает код, теперь скомпилируйте код в файл ошибки, нажав функциональную клавишу F8. Теперь, чтобы проверить, работает ли написанный код, мы изменили количество Customer14 на 69 .
Шаг 8: Теперь запустите код. Мы увидим, что общий счет против Customer14 обновлен до 2183, который выделен желтым цветом.
Сводная таблица обновления VBA - пример № 2
Есть еще один способ обновить сводную таблицу через VBA. Прежде чем двигаться дальше, мы можем рассмотреть вопрос об изменении имени сводной таблицы или использовать имя по умолчанию. Давайте попробуем дать новое имя сводной таблице. Для этого выберите сводную таблицу и перейдите на вкладку меню « Анализ », как показано ниже.
В первом разделе Имени сводной таблицы мы видим имя по умолчанию как Сводная таблица1 .
Теперь мы изменим это имя. Попробуйте записать новое имя как Данные клиента, как показано ниже.
Шаг 1: После этого откройте новый модуль и запишите подкатегорию VBA Refresh, как показано ниже.
Код:
Шаг 2: Теперь определите переменную как сводную таблицу . Здесь, с помощью сводной таблицы, мы рассмотрим полные исходные данные.
Код:
Шаг 3: Теперь используйте Set с определенной переменной и выберите лист, который открыт в данный момент.
Код:
Шаг 4: Выберите имя сводной таблицы, которую мы хотим обновить вместе с переменным типом данных, который мы использовали в качестве сводной таблицы .
Код:
Шаг 5: Наконец, используйте переменную с функцией RefreshTable для ее выполнения.
Код:
Шаг 6: Давайте внесем еще некоторые изменения в данные, чтобы получить реальное представление о прикладном коде.
Мы изменили счет Customer2 как 56 . Теперь, если мы запустим код, в сводной таблице имени клиента, должны быть изменения в сумме количества для клиента2 .
Шаг 7: Теперь перейдите в окно VBA и скомпилируйте код. Если ошибки не найдены, запустите ее, нажав кнопку воспроизведения, которая находится под строкой меню, как показано ниже. Мы заметим, что сумма количества, проданного за Customer2, теперь увеличивается до 1724, внося изменения в исходную таблицу.
Таким образом, мы можем включить несколько исходных данных и создать другую сводную таблицу. И автоматизировать эти сводные таблицы также легко, поскольку нам просто нужно включить имя сводной таблицы и лист, где расположена таблица.
Плюсы VBA Refresh Pivot Table
- Обновление сводной таблицы с помощью кода VBA занимает очень мало времени.
- Это очень легко реализовать.
То, что нужно запомнить
- Мы можем добавить несколько таблиц источников данных и автоматизировать их с помощью кода VBA.
- Чтобы изменения произошли, держите окно VBA и лист Excel параллельно друг другу.
- Лучше назвать каждую сводную таблицу, если вы имеете дело и работаете с несколькими источниками данных и сводными таблицами.
- Код с именем сводной таблицы легко понять, отследить и найти.
Рекомендуемые статьи
Это руководство к сводной таблице обновлений VBA. Здесь мы обсудим, как использовать Excel VBA Refresh Pivot Table вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
В данном примере представлены исходные коды и описания для макросов, которые позволяют настраивать и форматировать любые сводные таблицы.
Макрос для обновления сводной таблицы в Excel
Для примера воспользуемся тестовой сводной таблицей из предыдущего примера: Макрос для создания сводной таблицы в Excel.
Данной сводной таблице уже присвоено внутреннее имя «ТаблицаМ» (как описано в предыдущем примере, перейдите по ссылке выше картинки). Каждая сводная таблица состоит из 4-ох видов полей:
Поля фильтров содержат ту часть таблицы исходных данных, которые необходимо проанализировать уже в сводной таблице. В примере следует проанализировать объем оборотов по отдельным магазинам фирмы. Как поле колон, так и поле строк определяет соответственно ту часть исходных данных, которую необходимо сортировать по строкам или по столбцам. Нет здесь определенного правила – все зависит от желаемого результата, который необходимо получить.
В данном примере определено:
- Столбец в исходных данных «Год» – находиться в поле фильтров.
- «Месяц» – определен как поле строк.
- «Магазины» – подчиненный к полю колон.
- «Оборот» – это поле значений, соответственно.
Далее рассмотрим, как изменять значения полей сводной таблицы с помощью макроса.
С помощью блока опций для второго конструктора With собираем расположение полей в сводной таблице. Текущие настройки полей при создании сводной таблицы определяются следующими строками кода макроса:
With ActiveSheet.PivotTables("ТаблицаМ")
.SmallGrid = True
.PivotFields("Оборот").Orientation = xlDataField
.PivotFields("Год").Orientation = xlPageField
.PivotFields("Месяц").Orientation = xlRowField
.PivotFields("Магазины").Orientation = xlColumnField
End With
Как видно из структуры данной части кода, каждое с полей сводной таблицы определяется отдельной строкой макроса. Ведь эта часть кода находиться внутри конструктора, который начинается с инструкции With. Если бы мы не использовали конструктор, тогда эти свойства и методы необходимо было бы каждый раз добавлять к объекту:
Примечание. «ТаблицаМ» – это внутреннее имя таблицы, которое было ей присвоено при создании для того, чтобы на нее было легче ссылаться в том числе и в коде макроса.
И к такому объекту должно относиться каждое поле настраивая порядок полей для сводной таблицы.
Заголовки столбцов, которые необходимо упорядочить определенным полем указываем (в скобках) как аргумент в методе PivotFields перед его свойством Orientation. В конце строки указываем параметром, какое поле было определено. Для этого к распоряжению язык VBA предоставляет нам выше упоминаемые 4 типа полей:
| Поля | Код VBA |
| ФИЛЬТРЫ | xlPageField |
| СТРОКИ | xlRowField |
| КОЛОННЫ | xlColumnField |
| ЗНАЧЕНИЯ | xlDataField |
С помощью этих 4 строк VBA-кода макроса можно полностью изменить и по-другому настроить сводную таблицу. Разные настройки полей можно генерировать простейшими макросами, благодаря чему можно моментально перенастроить структуру любой сводной таблицы. Так даже намного удобнее, чем вручную перетягивать мышкой поля на уровне пользовательского интерфейса для настройки сводной таблицы в Excel.
Создадим макрос для изменения и настройки полей сводной таблицы. Откройте редактор макросов (ALT+F11) и создайте модуль если он еще не создан: «Insert»-«Module».
Введете код макроса, в результате которого будет выполнена автоматическая перенастройка структуры сводной таблицы с помощью изменения расположения полей для заголовков исходной таблицы «Магазины» и «Год»:
Sub ChangeTableM()
With ActiveSheet.PivotTables( "ТаблицаМ" )
.PivotFields( "Магазины" ).Orientation = xlPageField
.PivotFields( "Год" ).Orientation = xlColumnField
End With
End Sub
Чтобы запустить макрос нажмите комбинацию горячих клавиш (ALT+F8) или выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«ChangeTableM» и нажмите на кнопку «Выполнить».
В результате сводная таблица автоматически изменит свою структуру как показано на рисунке.
Обратите внимание! В первой версии структуры сводной таблицы поле страницы служило для выбора года, относительного к соответственным показателям оборота магазинов фирмы. А теперь поле страниц служит для выбора соответственного магазина фирмы. В то же время года находятся в заголовках столбцов (поле КОЛОННЫ).
Форматирование сводной таблицы макросом
Созданная по умолчанию сводная таблица в большинстве случаев не соответствует желаемому уровню читабельности для пользователей. Как минимум нужно ей задать все необходимые форматы для отображения числовых значений. Следующий код макроса позволяет автоматически присвоить желаемый стиль для отображения чисел в денежном формате:
Снова нажмите комбинацию горячих клавиш (ALT+F8) или выберите: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«FormatDeneg» и нажмите «Выполнить».
Данный код необходимо записать в тот же самый модуль, в котором находятся коды других макросов.
В результате действия этого короткого и простого макроса все числовые значения в таблице будут преобразованы в денежный формат рублей (с разделителем тысяч и без копеек). Запустив макрос «FormatDeneg» сводная, таблица приобретет следующий вид:
В коде макроса мы использовали свойство NumberFormat, которое играет главную роль в форматировании чисел. В параметрах свойства мы просто указываем тип формат для отображения значения. Разновидность типов можно взять из списка: «ГЛАВНАЯ»-«Ячейки»-«Формат»-«Формат ячеек» (CTRL+1).
В появившемся окне на закладке «Число» выберите опцию «(все форматы)» из списка «Числовые форматы:». В правом поле «Тип:» можно подобрать свой желаемый параметр для свойства NumberFormat.
Подобными способами можно форматировать всю сводную таблицу используя другие сроки кода макроса. Просто нужно менять параметры для свойств (присваивать толщину и типы линий границ, изменять цвета фонов ячеек и т.п.).
Читайте также: