Как подготовить диапазон данных для сводной таблицы

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

Есть бесчисленное множество методов, которые можно использовать, чтобы транспонировать весь диапазон данных. Макрос в этом разделе обеспечивает простой способ автоматизировать эту задачу.
Несколько диапазонов консолидации может выводить только три базовые поля: строка, столбец и значение. Поле Row всегда состоит из первого столбца в источнике данных. Column состоит из всех заголовков столбцов после первого столбца в источнике данных. Поле Value состоит из значений в источнике данных.
Из-за этого, вы можете иметь только один столбец измерения. Чтобы понять это, взгляните на рисунки. Обратите внимание, что первый столбец, по существу, каскадный столбец, состоящий из двух измерений данных: Market и Category. Это потому, что сводная таблица диапазона консолидации может обрабатывать только одно
измерение поля.

Как макрос работает

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

1. Нажмите Alt + D + P для вызова мастера Excel 2003 сводной таблицы.
2. Выберите опцию для нескольких диапазонов консолидации и нажмите кнопку Далее.
3. Выберите опцию Поля страниц, а затем нажмите кнопку Далее.
4. Определить диапазон, в котором вы работаете, и нажмите кнопку Готово, чтобы создать сводную таблицу.
   Этот макрос дублирует вышеуказанные шаги.

Код макроса

Sub PodgotovitDiapazonDlyaSvodnoiTablici()
'Шаг 1: Объявляем переменные
Dim SourceRange As Range
Dim GrandRowRange As Range
Dim GrandColumnRange As Range
'Шаг 2: Определите свой диапазон источника данных
Set SourceRange = Sheets("Лист1").Range("A4:M87")
'Шаг 3: Составить сводную таблицу с несколькими консолидациями
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, _
SourceData:=SourceRange.Address(ReferenceStyle:=xlR1C1), _
Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="", _
TableName:="Pvt2", _
DefaultVersion:=xlPivotTableVersion14
'Шаг 4: Найти столбец и ряд гранд итоги
ActiveSheet.PivotTables(1).PivotSelect "'Row Grand Total'"
Set GrandRowRange = Range(Selection.Address)
ActiveSheet.PivotTables(1).PivotSelect "'Column Grand Total'"
Set GrandColumnRange = Range(Selection.Address)
'Шаг 5: Дрель в пересечении строки и столбца
Intersect(GrandRowRange, GrandColumnRange).ShowDetail = True
End Sub

Как использовать

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

1. Активируйте редактор Visual Basic, нажав ALT + F11.
2. Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта.
3. Выберите Insert➜Module.
4. Введите или вставьте код.