Создание сводной таблицы на основе нескольких листов

Рубрика: 7. Полезняшки Excel

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: у вас данных больше, чем может поместиться на одном листе, или данные по иным причинам распределены на нескольких листах (например, один месяц – один лист, рис. 1, или один отдел – один лист). Вы хотите создать сводную таблицу из данных, распределенных между несколькими листами. В старых версиях Excel функция консолидации работает, мягко выражаясь, странно (см., Сводная таблица на основе нескольких листов). Начиная с Excel 2013 функция консолидации значительно улучшена (см. Сводная таблица на основе нескольких листов или диапазонов консолидации). Книга Джелена вышла в 2009 г., так что метод, предложенный в настоящей заметке, возможно, и не представляет значительной практической ценности. Однако, как я уже упоминал ранее, материалы Джелена методически интересны сами по себе, поэтому я счел полезным перевести и эту главу.

Рис. 1. Исходные данные расположены на пяти листах

Рис. 1. Исходные данные расположены на пяти листах

Скачать заметку в формате Word или pdf, примеры в формате Excel (с кодом VBA)

Справочная информация: Fazza из Перта, Австралия, опубликовал замечательный фрагмент кода в 2008, который позволяет построить кеш сводной таблицы из нескольких листов или даже нескольких книг (больше информации о кеше можно найти в заметке Excel 2013. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?). Удивительно, но кеш сводной таблицы сохраняется вместе с книгой, так что, работая в Excel 2003, вы можете создать сводный отчет на основе более чем 65 536 строк.

Решение: предлагается создать SQL-оператор, чтобы собрать данные с нескольких листов в один массив. Вы фактически объединяете данные с нескольких листов в один набор записей. Далее вы открываете новую книгу и создаете сводную таблицу для внешнего набора данных – только что созданного набора записей. В результате получается чистая книга со сводной таблицей и кешем, хранящимся в ней, как бы, за кадром.

Вы пишете код, который создает массив из SQL-операторов. Полный код выглядит следующим образом (его также можно найти в модуле VBA в приложенном Excel-файле):

Sub BuildPivotCache()

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

End Sub

Если активная книга содержит пять листов, массив может выглядеть как показано на рис. 2.

Рис. 2. Вы строите несколько SQL-запросы, чтобы захватить все данные с каждого из 5 листов

Рис. 2. Вы строите несколько SQL-запросов, чтобы собрать данные с каждого из пяти листов

Затем код создает новый набор записей на основе пяти запросов. Этот набор записей будет содержать все записи из всех таблиц. Где же этот набор записей сохраняется? Просто в памяти, как переменная объекта objRS. Позже, кода создается новая книга, этот набор записей используется в качестве источника для внешнего кеша сводной таблицы:

Set objPivotCache = .PivotCaches.Add(xlExternal)

Set objPivotCache.Recordset = objRS

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

Рис. 3. Хотя книга не содержит никаких видимых данных, в ней хранится кэш сводной таблицы

Рис. 3. Хотя книга не содержит никаких видимых данных, в ней хранится кеш сводной таблицы

С этого момента, вы можете работать со сводной таблицей в обычном режиме (рис. 4).

Рис. 4. Сводная таблица

Рис. 4. Сводная таблица (см. также Excel-файл)

Если кеш сводной таблицы содержит меньше записей, чем строк на листе Excel, вы можете попробовать дважды щелкнуть на ячейке Общий итог (выделено на рис. 4). После этого на отдельный лист будут выведены данные, хранящиеся в кеше сводной таблицы (рис. 5)

Рис. 5. Данные, хранящиеся в кеше сводной таблицы

Рис. 5. Данные, хранящиеся в кеше сводной таблицы

Подводный камень: в отличие от сводных таблиц, построенных из данных одного листа, эта сводная таблица не показывает никаких данных, пока вы разместите какое-либо поле в области ЗНАЧЕНИЯ. Не волнуйтесь, если вы разместили, например, поле Region в области СТРОКИ и ничего не произошло (регионы не отразились в строках таблицы). Как только вы добавите данные в область ЗНАЧЕНИЯ, сводная таблица проведет расчет, и регионы появятся.

Резюме: создание сводной таблицы на основе нескольких листов может быть выполнено кодом VBA, извлекающим данные из нескольких листов и помещающих их в один кеш.

Источник. Решение было предложено Денисом Райтом (ник SydneyGeek), который, среди прочего, поддерживает веб-сайт, посвященный Excel.

Комментарии: 9 комментариев

Здравствуйте! Спасибо за статью. Сейчас «происходит» бюджетирование на следующий год. На одном листе приходилось держать и 2014 год, и бюджет 2015 года и сам факт 2015 года с прогнозом до декабря, и бюджет на 2016 год. Я работаю в типографии, поэтому бюджет ещё и по производствам. Ваш макрос существенно облегчит жизнь. С уважением, Наталья.

[…] Создание сводной таблицы на основе нескольких листов […]

А может просто создать модель данных без макросов?

Спасибо за статью.
У меня за год набирается файл-реестр в котором около 200.000 строк и 65 столбцов записей.

Комп невероятно медленно собирает сводную таблицу, да и дополнительные действия с первичной таблицей тоже проходят очень медленно, вставить в середину столбец, например — 30, а иногда и больше, минут. Все лишние процессы убраны. И всё равно медленно. А уже однажды я попытался создать модель данных… лучше бы не делал — вернул все потом назад.
У майкрософт заявлено, что excel обрабатывает до миллиона строк, но я нигде не смог найти системных требований при работе с большими файлами.
Использование этого совета, по консолидации нескольких листов, заветная, но недосягаемая мечта — все годы свести в одну таблицу — счастье!
Вы не знаете о таких требованиях к системе?
Core i3-3220 на 3,30GHz, 4Gb памяти у меня.

Здравствуйте, уважаемый Автор отличного блога!!! Огромное спасибо, как за статью, так и за бОльшую (подавляющую) часть остальных материалов сайта!! Очень полезно и здорово помогает экономить время! Вопрос по теме: возможно ли модернизировать код, приведенный в статье, чтобы и кэш, и сводная на его основе формировались не в новой книге, а в текущей? Было бы очень удобно, чувствую, что это решаемо, но вот мои познания в VBA пока что «околоноля» (( Заранее благодарен за ответ…

Сергей, это довольно сложная задача. Однако, если у вас Excel 2013 или более поздняя версия, рекомендую почитать Сводная таблица на основе нескольких листов или диапазонов консолидации.

Спасибо за оперативный ответ!
Правильно ли я понял, что в Excel 2013 (или более поздних) сводная, созданная из нескольких листов, является нормальной, т.е. без ограничений по возможностям работы с ней, которые были в предыдущих версиях?

Не вполне «нормальной». Ограничения всё же есть, но их гораздо меньше. Основные возможности доступны в стандартном интерфейсе сводных таблиц.

Здравствуйте, Сергей Викторович.

Фраза о том, что «это довольно сложная задача» всё же не отбила желания иметь собранную из листов сводную в той же книге, где находятся исходные данные. Возможно, даже наоборот ))
Понадоедал другу-программисту, и он-таки уделил мне время, глянул в код и оказалось, что там дел на две строчки. А именно:
——
‘Set wbkNew = Workbooks.Add(Template:=xlWBATWorksheet)
Sheets.Add After:=Sheets(Sheets.Count)

With ActiveWorkbook ‘wbkNew
——
В третьем с конца «абзаце» заменены закомментированные строки.
После этого всё работает как и в оригинале, только новый лист с макетом сводной (и её кэш, разумеется) создаётся в текущей книге.
Настоятельно рекомендуется к употреблению!
И ещё раз спасибо Вам за исходный макрос…


Прокомментировать