Создание сводной таблицы в excel vba
Обновлено: 08.07.2024
В Excel 2000 и более поздних версиях, прежде чем создавать сводную таблицу, нужно сформировать объект кеша сводной таблицы, применяемый для описания области ввода данных.
После определения кеша сводной таблицы используйте метод СrеatePivotTable для создания пустой сводной таблицы на основе выделенного ранее кеша.
В методе CreatePivotTable вы указываете расположение выходных данных и (не обязательно) определяете имя таблицы с несколько странным видом. После выполнения этой строки кода вы получите пустую сводную таблицу. Если в области списка полей сводной таблицы был установлен флажок Отложить обновление макета (Defer Layout Update), то Excel не станет вновь вычислять сводную таблицу после перетаскивания в таблицу каждого поля. По умолчанию программа вычисляет сводную таблицу после выполнения каждого этапа построения макета таблицы. Таким образом, сводная таблица будет вычисляться много раз, пока не будет получен итоговый результат.
Чтобы ускорить выполнение кода, временно отключите вычисление сводной таблицы с помошью свойства ManualUpdate.
Теперь можно выполнять все операции по компоновке сводной таблицы.
В методе .AddFields указывается одно или несколько полей, которые должны находиться в области строк, столбцов или страниц сводной таблицы. Параметр RowFields позволяет определять поля, которые добавлены в область заголовков строк списка полей сводной таблицы. Параметр ColumnFields соответствует области заголовков столбцов, а параметр PageFields — области фильтров отчета.
Следующий программный код представляет сводную таблицу с двумя полями в области заголовков строк и одним полем в области заголовков столбцов.
Если в одну из областей сводной таблицы было добавлено единственное поле, например поле Регион в области столбцов сводной таблицы, имя поля заключается в кавычки. Если добавляются два либо больше полей, список полей включают в состав функции массива. Несмотря на то что поля строк, столбцов и страниц в сводной таблице могут обрабатываться с помощью метода .AddFields, для добавления полей в область данных лучше воспользоваться кодом, который будет описан в следующей статье.
В данном примере представлены исходные коды и описания для макросов, которые позволяют настраивать и форматировать любые сводные таблицы.
Макрос для обновления сводной таблицы в 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.
Подобными способами можно форматировать всю сводную таблицу используя другие сроки кода макроса. Просто нужно менять параметры для свойств (присваивать толщину и типы линий границ, изменять цвета фонов ячеек и т.п.).
Полезные макросы Excel для автоматизации рутинной работы с примерами применения для разных задач.
Примеры макросов для автоматизации работы
Пример удобного макроса для автоматической подсветки добирающихся значений ячеек разными цветами. Как выделить разным цветом дубликаты ячеек?
Готовое решение для поиска на всех листах рабочей книги как точного, так и максимально приближенного исходного значения. VBA код макрос для нахождения и получения числа с любого листа книги.
Альтернатива инструменту слияния в Word для печати рассылок прямо из Excel. Исходный код макроса, который выполняет слияние данных для серийной печати рассылок.
Как автоматизировать фильтр в сводных таблицах с помощью макроса? Исходные коды макросов для фильтрации и скрытия столбцов в сводной таблице.
Как быстро преобразовать значения сводной таблицы на доли в процентном соотношении к общим итогам по строкам и по столбцам? Исходные коды макросов для работы с процентами.
Управление полями сводной таблицы с помощью макроса. Исходный код VBA-макроса для настройки и изменения структуры в сводных таблицах.
Как автоматически сгенерировать сводную таблицу с помощью макроса? Исходный код VBA для создания и настройки сводных таблиц на основе исходных данных.
Поиск всех скрытых строк и столбцов на рабочем листе с помощью VBA-макроса. Исходные коды макросов для получения сводной информации о скрытых строках и столбцах рабочего листа.
Как одновременно копировать и переименовывать большое количество листов одним кликом мышкой? Исходный код макроса, который умеет одновременно скопировать и переименовать любое количество листов.
Задача: у вас данных больше, чем может поместиться на одном листе, или данные по иным причинам распределены на нескольких листах (например, один месяц – один лист, рис. 1, или один отдел – один лист). Вы хотите создать сводную таблицу из данных, распределенных между несколькими листами. В старых версиях Excel функция консолидации работает, мягко выражаясь, странно (см., Сводная таблица на основе нескольких листов). Начиная с Excel 2013 функция консолидации значительно улучшена (см. Сводная таблица на основе нескольких листов или диапазонов консолидации). Книга Джелена вышла в 2009 г., так что метод, предложенный в настоящей заметке, возможно, и не представляет значительной практической ценности. Однако, как я уже упоминал ранее, материалы Джелена методически интересны сами по себе, поэтому я счел полезным перевести и эту главу.
Рис. 1. Исходные данные расположены на пяти листах
Справочная информация: Fazza из Перта, Австралия, опубликовал замечательный фрагмент кода в 2008, который позволяет построить кеш сводной таблицы из нескольких листов или даже нескольких книг (больше информации о кеше можно найти в заметке Excel 2013. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?). Удивительно, но кеш сводной таблицы сохраняется вместе с книгой, так что, работая в Excel 2003, вы можете создать сводный отчет на основе более чем 65 536 строк.
Решение: предлагается создать SQL-оператор, чтобы собрать данные с нескольких листов в один массив. Вы фактически объединяете данные с нескольких листов в один набор записей. Далее вы открываете новую книгу и создаете сводную таблицу для внешнего набора данных – только что созданного набора записей. В результате получается чистая книга со сводной таблицей и кешем, хранящимся в ней, как бы, за кадром.
Вы пишете код, который создает массив из SQL-операторов. Полный код выглядит следующим образом (его также можно найти в модуле VBA в приложенном Excel-файле):
Dim i As Long
Dim arSQL() As String
Dim objPivotCache As PivotCache
Dim objRS As Object
Dim wbkNew As Workbook
Dim wks As Worksheet
With ActiveWorkbook
ReDim arSQL(1 To .Worksheets.Count)
For Each wks In .Worksheets
i = i + 1
arSQL(i) = " SELECT * FROM [ " & wks.Name & " $] "
Next wks
Set wks = Nothing
Set objRS = CreateObject( " ADODB.Recordset " )
objRS.Open Join$(arSQL, " UNION ALL " ), Join$(Array( " Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " , _
.FullName, " ;Extended Properties= " " Excel 8.0; " " " ), vbNullString)
End With
Set wbkNew = Workbooks.Add(Template:=xlWBATWorksheet)
With wbkNew
Set objPivotCache = .PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = objRS
Set objRS = Nothing
With .Worksheets(1)
objPivotCache.CreatePivotTable TableDestination:=.Range( " A3 " )
Set objPivotCache = Nothing
Range( " A3 " ).Select
End With
End With
Set wbkNew = Nothing
Если активная книга содержит пять листов, массив может выглядеть как показано на рис. 2.
Рис. 2. Вы строите несколько SQL-запросов, чтобы собрать данные с каждого из пяти листов
Затем код создает новый набор записей на основе пяти запросов. Этот набор записей будет содержать все записи из всех таблиц. Где же этот набор записей сохраняется? Просто в памяти, как переменная объекта objRS. Позже, кода создается новая книга, этот набор записей используется в качестве источника для внешнего кеша сводной таблицы:
Set objPivotCache = .PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = objRS
В результаты работы кода вы получите новую книгу с пустой сводной (рис. 3). Как можно видеть, список полей сводной содержит список всех полей из таблицы, приведенной на рис. 1. Правда, на рис. 3 поля отсортированы по алфавиту.
Рис. 3. Хотя книга не содержит никаких видимых данных, в ней хранится кеш сводной таблицы
С этого момента, вы можете работать со сводной таблицей в обычном режиме (рис. 4).
Рис. 4. Сводная таблица (см. также Excel-файл)
Если кеш сводной таблицы содержит меньше записей, чем строк на листе Excel, вы можете попробовать дважды щелкнуть на ячейке Общий итог (выделено на рис. 4). После этого на отдельный лист будут выведены данные, хранящиеся в кеше сводной таблицы (рис. 5)
Рис. 5. Данные, хранящиеся в кеше сводной таблицы
Подводный камень: в отличие от сводных таблиц, построенных из данных одного листа, эта сводная таблица не показывает никаких данных, пока вы разместите какое-либо поле в области ЗНАЧЕНИЯ. Не волнуйтесь, если вы разместили, например, поле Region в области СТРОКИ и ничего не произошло (регионы не отразились в строках таблицы). Как только вы добавите данные в область ЗНАЧЕНИЯ, сводная таблица проведет расчет, и регионы появятся.
Резюме: создание сводной таблицы на основе нескольких листов может быть выполнено кодом VBA, извлекающим данные из нескольких листов и помещающих их в один кеш.
Источник. Решение было предложено Денисом Райтом (ник SydneyGeek), который, среди прочего, поддерживает веб-сайт, посвященный Excel.
12 комментариев для “Создание сводной таблицы на основе нескольких листов”
А может просто создать модель данных без макросов?
Спасибо за статью.
У меня за год набирается файл-реестр в котором около 200.000 строк и 65 столбцов записей.
Сергей, это довольно сложная задача. Однако, если у вас Excel 2013 или более поздняя версия, рекомендую почитать Сводная таблица на основе нескольких листов или диапазонов консолидации .
Спасибо за оперативный ответ!
Правильно ли я понял, что в Excel 2013 (или более поздних) сводная, созданная из нескольких листов, является нормальной, т.е. без ограничений по возможностям работы с ней, которые были в предыдущих версиях?
Здравствуйте, Сергей Викторович.
Читайте также: