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

В данном примере представлены исходные коды и описания для макросов, которые позволяют настраивать и форматировать любые сводные таблицы.

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

Для примера воспользуемся тестовой сводной таблицей из предыдущего примера: Макрос для создания сводной таблицы в Excel.

Данной сводной таблице уже присвоено внутреннее имя «ТаблицаМ» (как описано в предыдущем примере, перейдите по ссылке выше картинки). Каждая сводная таблица состоит из 4-ох видов полей:

1. Поля ФИЛЬТРЫ.
2. Поля СТРОКИ.
3. Поля КОЛОННЫ.
4. Поля ЗНАЧЕНИЯ.

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

В данном примере определено:

1. Столбец в исходных данных «Год» – находиться в поле фильтров.
2. «Месяц» – определен как поле строк.
3. «Магазины» – подчиненный к полю колон.
4. «Оборот» – это поле значений, соответственно.

Далее рассмотрим, как изменять значения полей сводной таблицы с помощью макроса.

С помощью блока опций для второго конструктора With собираем расположение полей в сводной таблице. Текущие настройки полей при создании сводной таблицы определяются следующими строками кода макроса:

With ActiveSheet.PivotTables("ТаблицаМ")
.SmallGrid = True
.PivotFields("Оборот").Orientation = xlDataField
.PivotFields("Год").Orientation = xlPageField
.PivotFields("Месяц").Orientation = xlRowField
.PivotFields("Магазины").Orientation = xlColumnField
End With

Как видно из структуры данной части кода, каждое с полей сводной таблицы определяется отдельной строкой макроса. Ведь эта часть кода находиться внутри конструктора, который начинается с инструкции With. Если бы мы не использовали конструктор, тогда эти свойства и методы необходимо было бы каждый раз добавлять к объекту:

ActiveSheer.PivotTables(„ТаблицаМ”)

Примечание. «ТаблицаМ» – это внутреннее имя таблицы, которое было ей присвоено при создании для того, чтобы на нее было легче ссылаться в том числе и в коде макроса.

И к такому объекту должно относиться каждое поле настраивая порядок полей для сводной таблицы.

Заголовки столбцов, которые необходимо упорядочить определенным полем указываем (в скобках) как аргумент в методе 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» и нажмите на кнопку «Выполнить».

В результате сводная таблица автоматически изменит свою структуру как показано на рисунке.

Обратите внимание! В первой версии структуры сводной таблицы поле страницы служило для выбора года, относительного к соответственным показателям оборота магазинов фирмы. А теперь поле страниц служит для выбора соответственного магазина фирмы. В то же время года находятся в заголовках столбцов (поле КОЛОННЫ).



Форматирование сводной таблицы макросом

Созданная по умолчанию сводная таблица в большинстве случаев не соответствует желаемому уровню читабельности для пользователей. Как минимум нужно ей задать все необходимые форматы для отображения числовых значений. Следующий код макроса позволяет автоматически присвоить желаемый стиль для отображения чисел в денежном формате:

Sub FormatDeneg()
ActiveSheet.PivotTables("ТаблицаМ").PivotFields("Сумма по полю Оборот").NumberFormat = "# ##0 " & ChrW(8381)
End Sub


Снова нажмите комбинацию горячих клавиш (ALT+F8) или выберите: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«FormatDeneg» и нажмите «Выполнить».

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

В результате действия этого короткого и простого макроса все числовые значения в таблице будут преобразованы в денежный формат рублей (с разделителем тысяч и без копеек). Запустив макрос «FormatDeneg» сводная, таблица приобретет следующий вид:

В коде макроса мы использовали свойство NumberFormat, которое играет главную роль в форматировании чисел. В параметрах свойства мы просто указываем тип формат для отображения значения. Разновидность типов можно взять из списка: «ГЛАВНАЯ»-«Ячейки»-«Формат»-«Формат ячеек» (CTRL+1).

В появившемся окне на закладке «Число» выберите опцию «(все форматы)» из списка «Числовые форматы:». В правом поле «Тип:» можно подобрать свой желаемый параметр для свойства NumberFormat.

Обратите внимание! В конце строки параметра мы с помощью символа амперсанта добавляем непечатный символ, который возвращает функция ChrW(8381). Данная функция позволяет там вводить непечатные символы Unicode. А код 8381 в ее аргументе – это юникод непечатного символа рубля ₽

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