Что делает макрос: Макрос, изложенный в данном разделе, просматривает форматирование чисел в исходных данных сводной таблицы и автоматически применяет соответствующее форматирование к каждому полю.
Как макрос работает
Перед выполнением этого кода, нужно убедиться, что:
- Исходные данные для сводной таблицы доступны. Макрос должен увидеть их, чтобы захватить правильное форматирование чисел.
- Исходные данные надлежащим образом отформатированы. Поля Деньги отформатированы как валюта, поля значений отформатированы как числа и так далее
Этот макрос использует свойство PivotTable SourceData, чтобы найти расположение источника данных. Затем он перебирает каждый столбец в источнике, захватив имя заголовка и числовой формат первого значения по каждой колонке. После того, как у него есть эта информация, макрос определяет, будут ли поля данных соответствовать оценочному столбцу. Если он находит совпадение, форматирование применяется к этому полю данных.
Код макроса
Sub ChislovoiFormatDlyaVsehElementovSvodnoi()
'Шаг 1: Объявляем переменные
Dim pt As PivotTable
Dim pf As PivotField
Dim SrcRange As Range
Dim strFormat As String
Dim strLabel As String
Dim i As Integer
'Шаг 2: Курсор в сводную таблицу на активную ячейку
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
'Шаг 3: Выход, если активная ячейка находится не в сводной таблице
If pt Is Nothing Then
MsgBox "Вы должны поместить курсор в сводную таблицу."
Exit Sub
End If
'Шаг 4: Захват диапазона источника
Set SrcRange = _
Range(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1))
'Шаг 5: Начните цикл по столбцам в исходном диапазоне
For i = 1 To SrcRange.Columns.Count
'Шаг 6: Поймать имя исходного столбца и числовой формат
strLabel = SrcRange.Cells(1, i).Value
strFormat = SrcRange.Cells(2, i).NumberFormat
'Шаг 7: Цикл по полям области данных сводной таблицы
For Each pf In pt.DataFields
'Шаг 8: Проверьте соответствие на SourceName, затем примените формат
If pf.SourceName = strLabel Then
pf.NumberFormat = strFormat
End If
Next pf
Next i
End Sub
Как этот код работает
- Шаг 1 объявляет шесть переменных. Он использует Pt в качестве контейнера памяти для нашей сводной таблицы и PF в качестве контейнера для памяти наших полей данных. Переменная SrcRange имеет диапазон данных для исходных данных. Переменные strFormat и strLabel являются текстовой строкой переменных, используемые для хранения источника подписи столбца и форматирования чисел. Переменная I служит в качестве счетчика, помогая нам пройти через столбцы диапазона исходных данных. Активная ячейка должна находиться внутри сводной таблицы для запуска этого макроса. Предполагается, что когда курсор находится внутри определенной сводной таблицы, мы хотим выполнить действие макроса на этой оси.
- Шаг 2 устанавливает переменную St к имени сводной таблицы на котором найдена активная ячейка. Мы делаем это, используя свойство ActiveCell.PivotTable.Name, чтобы получить имя целевой сводной. Если активная ячейка не находится внутри сводной таблицы, выдается ошибка. Именно поэтому макрос использует On Error Resume Next Statement. Это говорит Excel продолжить макро, если есть ошибка.
- Шаг 3 проверяет, является ли переменная PT заполненной сводной таблицей. Если переменная PT установлена в Nothing, активная ячейка была не на сводной таблице, таким образом, сводной таблице не может быть присвоена переменная. Если это так, то мы говорим пользователю в окне сообщения, а затем мы выходим из процедуры.
- Если макрос достигает Шага 4, он успешно указал на сводную таблицу. Мы сразу же заполняем нашу переменную объекта SrcRange с диапазоном исходных данных сводной таблицы. Все PivotTables имеют свойство SourceData, который указывает на адрес его источника. К сожалению, адрес хранится в стиле ссылки R1C1 — «Raw Data» R3C1: R59470C14!. объекты Range не используют стиль R1C1, поэтому нам нужен адрес, который будет преобразован в «Raw Data» $ A $ 3: $ N $ 59470. Это достаточно простое решение проблемы. Мы просто передаем свойство SourceData через функцию Application.ConvertFormula. Эта удобная функция преобразует диапазоны от эталонного стиля R1C1.
- После захвата диапазона макрос начинает циклически проходить по столбцам в источнике. В этом случае мы управляем циклом, используя целое число i в качестве номера индекса для столбцов в исходном диапазоне. Мы начинаем с номера индекса = 1 и заканчиваем его на максимуме количества строк в исходном диапазоне.
- Поскольку макрос проходит по столбцам в исходном диапазоне, мы фиксируем заголовок столбца, метку и формат столбца. Мы делаем это с помощью элемента Cells. Элемент Cells дает нам чрезвычайно удобный способ выбора диапазонов с помощью кода. Требуются только относительные позиции строк и столбцов, как параметры Cells(1,1) переводятся в строку 1, столбец 1 (или строку заголовка первого столбца и т.п.). Cells(2, 1) переводятся в строку 2, столбец 1 (или в первое значение в первом столбце). strLabel заполняется меткой заголовка, взятой из строки 1 выбранного столбца. strFormat заполняется форматированием чисел из строки 2 выбранного столбца.
- На этом этапе макрос связан с исходными данными сводной таблицы и захватил первое имя столбца и номер форматирования для этого столбца. Теперь он начинает цикл через поля данных в сводной таблице.
- Шаг 8 просто сравнивает каждое поле данных, чтобы увидеть, совпадает ли его источник с именем в strLabel. Если это произойдет, это означает, что номер форматирования захваченных в strFormat принадлежит к этой области данных.
- После того, как все поля данных были оценены, Макрос переходит с шагом i к следующему столбцу в диапазоне источника. После того, как все столбцы были оценены, макрос заканчивается.
Как использовать
Для реализации этого макроса, вы можете скопировать и вставить его в стандартный модуль:
- Активируйте редактор Visual Basic, нажав ALT + F11.
- Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта.
- Выберите Insert➜Module.
- Введите или вставьте код.