Сводная таблица по нескольким диапазонам данных
Постановка задачи
Сводные таблицы - один из самых замечательных инструментов в Excel. Но до сих пор, к сожалению, ни одна из версий Excel не умеет "на лету" делать такой простой и нужной вещи как построение сводной по нескольким исходным диапазонам данных, находящимся, например, на разных листах или в разных таблицах:
Прежде, чем начать давайте уточним пару моментов. Априори я полагаю, что в наших данных выполняются следующие условия:
- Таблицы могут иметь любое количество строк с любыми данными, но обязательно - одинаковую шапку.
- На листах с исходными таблицами не должно быть лишних данных. Один лист - одна таблица. Для контроля советую использовать сочетание клавиш Ctrl+End, которое перемещает вас на последнюю использованную ячейку листа. В идеале - это должна быть последняя ячейка таблицы с данными. Если при нажатии на Ctrl+End выделяется какая-либо пустая ячейка правее или ниже таблицы - удалите после таблицы эти пустые столбцы справа или строки снизу и сохраните файл.
Способ 1. Сборка таблиц для сводной с помощью Power Query
Начиная с 2010 версии для Excel существует бесплатная надстройка Power Query, которая умеет собирать и трансформировать любые данные и отдавать их потом как источник для построения сводной таблицы. Решить нашу задачу с помощью этой надстройки совсем несложно.
Сначала создадим новый пустой файл в Excel - в него будет происходить сборка и в нем же потом будет создаваться сводная таблица.
Затем на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) выберем команду Создать запрос - Из файла - Excel (Get Data - From file - Excel) и укажем исходный файл с таблицами, которые надо собрать:
В появившемся окне выберем любой лист (не принципиально какой именно) и внизу жмем кнопку Изменить (Edit):
Поверх Excel должно открыться окно редактора запросов Power Query. В правой части окна на панели Параметры запроса удалим все автоматически созданные шаги кроме первого - Источник (Source):
Теперь мы видим общий список всех листов. Если в файле кроме листов с данными есть еще какие-то побочные листы, то на этом шаге наша задача - отобрать только те листы, с которых нужно загрузить информацию, исключив все остальные с помощью фильтра в шапке таблицы:
Удалим все столбцы, кроме колонки Data, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Удалить другие столбцы (Remove other columns):
Затем можно развернуть содержимое собранных таблиц, щелкнув по двойной стрелке в верхней части столбца (флажок Использовать исходное имя столбца как префикс можно при этом отключить):
Если вы всё сделали правильно, то на этом моменте должны увидеть содержимое всех таблиц, собранных друг под другом:
Осталось поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home) и удалить попавшие в данные повторяющиеся шапки таблиц с помощью фильтра:
Сохраним всё проделанное с помощью команды Закрыть и загрузить - Закрыть и загрузить в... (Close & Load - Close & Load to...) на вкладке Главная (Home), а в открывшемся окне выберем опцию Только подключение (Connection Only):
Всё. Осталось только построить сводную. Для этого идём на вкладку Вставка - Сводная таблица (Insert - Pivot Table), выбирыем опцию Использовать внешний источник данных (Use external data source), а затем, нажав кнопку Выбрать подключение, наш запрос. Дальнейшее создание и настройка сводной происходит совершенно стандартным образом путем перетаскивания нужных нам полей в области строк, столбцов и значений:
Если в будущем изменятся исходные данные или добавится еще несколько листов-магазинов, то достаточно будет обновить запрос и нашу сводную с помощью команды Обновить все на вкладке Данные (Data - Refresh All).
Способ 2. Объединяем таблицы SQL-командой UNION в макросе
Еще одно решение нашей задачи представлено вот таким макросом, который создает набор данных (cache) для сводной таблицы, используя команду UNION языка запросов SQL. Эта команда объединяет таблицы со всех указанных в массиве SheetNames листов книги в единую таблицу данных. То есть вместо физического копирования-вставки диапазонов с разных листов на один мы делаем то же самое в оперативной памяти компьютера. Потом макрос добавляет новый лист с заданным именем (переменная ResultSheetName) и создает на нем полноценную(!) сводную на основе собранного кэша.
Чтобы воспользоваться макросом используйте кнопку Visual Basic на вкладке Разработчик (Developer) или сочетание клавиш Alt+F11. Затем вставляем новый пустой модуль через меню Insert - Module и копируем туда следующий код:
Sub New_Multi_Table_Pivot()
Dim i As Long
Dim arSQL() As String
Dim objPivotCache As PivotCache
Dim objRS As Object
Dim ResultSheetName As String
Dim SheetsNames As Variant
'имя листа, куда будет выводиться результирующая сводная
ResultSheetName = "Сводная"
'массив имен листов с исходными таблицами
SheetsNames = Array("Альфа", "Бета", "Гамма", "Дельта")
'формируем кэш по таблицам с листов из SheetsNames
With ActiveWorkbook
ReDim arSQL(1 To (UBound(SheetsNames) + 1))
For i = LBound(SheetsNames) To UBound(SheetsNames)
arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]"
Next i
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
'создаем заново лист для вывода результирующей сводной таблицы
On Error Resume Next
Application.DisplayAlerts = False
Worksheets(ResultSheetName).Delete
Set wsPivot = Worksheets.Add
wsPivot.Name = ResultSheetName
'выводим на этот лист сводную по сформированному кэшу
Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = objRS
Set objRS = Nothing
With wsPivot
objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3")
Set objPivotCache = Nothing
Range("A3").Select
End With
End Sub
Готовый макрос потом можно запустить сочетанием клавиш Alt+F8 или кнопкой Макросы на вкладке Разработчик (Developer - Macros).
Минусы такого подхода:
- Данные не обновляются, т.к. кэш не имеет связи с исходными таблицами. При изменении исходных данных надо запустить макрос еще раз и построить сводную заново.
- При изменении количества листов необходимо правки в код макроса (массив SheetNames).
Зато в итоге получаем самую настоящую полноценную сводную таблицу, построенную по нескольким диапазонам с разных листов:
Вуаля!
Техническое замечание: если при запуске макроса вы получаете сообщение об ошибке вида "Provider not registered", то скорее всего у вас 64-битная версия Excel или установлена не полная версия Office (нет Access). Чтобы исправить ситуацию замените в коде макроса фрагмент:
Provider=Microsoft.Jet.OLEDB.4.0;
на:
Provider=Microsoft.ACE.OLEDB.12.0;
И скачайте и установите бесплатный движок обработки данных из Access с сайта Microsoft - Microsoft Access Database Engine 2010 Redistributable
Способ 3. Мастер консолидации сводных таблиц из старых версий Excel
Этот способ немного устарел, но тоже стоит упоминания. Формально говоря, во всех версиях до 2003 включительно в мастере сводных таблиц была опция "построить сводную по нескольким диапазонам консолидации". Однако, отчет, построенный таким образом, к сожалению, будет лишь жалким подобием настоящей полноценной сводной и не поддерживает многие "фишки" обычных сводных таблиц:
В такой сводной нет заголовков столбцов в списке полей, нет гибкой настройки структуры, ограничен набор используемых функций и, в общем и целом, все это слабо похоже на сводную таблицу. Возможно именно поэтому начиная с 2007 года Microsoft эту функцию убрали из стандартного диалога при создании отчетов сводных таблиц. Теперь эта возможность доступна только через настраиваемую кнопку Мастер сводных таблиц (Pivot Table Wizard), которую при желании можно добавить на панель быстрого доступа через Файл - Параметры - Настройка панели быстрого доступа - Все команды (File - Options - Customize Quick Access Toolbar - All Commands):
После нажатия на добавленную кнопку нужно выбрать на первом шаге мастера соответствующую опцию:
А затем в следующем окне выделить по очереди каждый диапазон и добавить его в общий список:
Но, повторюсь, это не полноценная сводная, так что не ждите от нее слишком многого. Подобный вариант могу рекомендовать только в очень простых случаях.
Ссылки по теме
- Создание отчетов с помощью сводных таблиц
- Настройка вычислений в сводных таблицах
- Что такое макросы, как их использовать, куда копировать код на VBA и т.д.
- Сбор данных с нескольких листов на один (надстройка PLEX)
Спасибо!
SheetsNames = Array("Альфа"[/FONT], "Бета", "Гамма", "Дельта")что вида
SheetNames(1)=Worksheets("Лист1").Range("A1") SheetNames(2)=Worksheets("Лист1").Range("A2")... и т.д., где в ячейках А1,А2... лежат имена обрабатываемых листов.
Спасибо за Ваш сайт и за данную статью.
на одном из форумов нашел вот такое решение с листами ...
Заменить в макросе:
'массив имен листов с исходными таблицами
SheetsNames = Array("a1", "a2", "a3", "a4";)
на:
Dim s As String
'Ввод массива имен листов с исходными таблицами
s = InputBox("Имена листов через запятую";)
s = Replace(s, ", ", ",";) 'Удаляем возможные пробелы после запятой
SheetsNames = Split(s, ",";)
есть нюанс\\ Дело в том что этот макрос не подсчитывает строки ,если их больше 50.000
(у меня тоже 4 листа как в примере но каждый состоит из 110.000-120.000 строк )
Может я что-то упускаю???
Подскажите если не трудно.
Может вам уже бесплатную надстройку PowerPivot использовать - при таких размерах таблиц как раз она замечательно будет работать. И макросов никаких не надо.
причем что характерно: если строк будет больше 65536, то брать в сводную таблицу будет не первые 65536, а например, первые 4000. или 9000.
т.е. получается даже не обрыв таблицы, а совсем что-то неадекватное.
дело точно не в кэше Excel, потому что я разбила каждую из своих 12 таблиц по 85 тыс.строк на две таблицы по 40-45 тыс строк.и стало все нормально.
т.е. 12 таблиц по 85 тыс.строк не сводились.
а 24 таблицы по 45 тыс.строк сводятся абсолютно корректно. кэша хватает
Я подошел к решению этой проблемы с другой стороны. Я сделал макрос который на новом листе формирует таблицу, которая содержит дание с других листов независимо от колиства строк в этих таблицах, а также выделяет новою таблицу. Осталось только вставить сводную таблицу, но проблема заключается в том как задать диапазон даных для сводной таблици, так как количество строк может менятся. Я только начал изучать VBA и ище многого не усвоил, помогите пожайлуста.
Спасибо заранее.
Worksheets("Лист1").UsedRangeПомогла замена "Microsoft.Jet.OLEDB.4.0" на "Microsoft.ACE.OLEDB.12.0"
Как изменить код так, что-бы сформированный кэш из нескольких листов выводился на скрытый лист "база" без формирования сводной, и каждый раз при запуске макроса информация на листе "база" заменялась
Дело в том, что при выводе данных на отдельный лист, я смогу создать несколько сводных таблиц (т.е. в разных разрезах) из диапазона данных на листе, и при запуске макроса нет необходимости опять настраивать таблицы, а просто их обновить
Спасибо
SheetsNames = Array("Альфа", "Бета", "Гамма", "Дельта")SheetNames(1) = Worksheets("Лист1").Range("A1") SheetNames(2) = Worksheets("Лист1").Range("A2") SheetNames(3) = Worksheets("Лист1").Range("A3") SheetNames(4) = Worksheets("Лист1").Range("A4")Постарался не отходить от Вашего стиля, вынес часть кода по созданию объекта ADODB.Recordset в отдельную функцию GetData().
И добавил процедуру обновления кэша: UpdateCache():
- получил ссылку на уже созданную сводную таблицу,
- подменил ей источник данных (PivotCache.Recordset),
- обновил саму таблицу.
Этого оказалось достаточно. При этом текущее состояние таблицы не нарушается, обновляются только данные.
Sub New_Multi_Table_Pivot() Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim wsPivot As Worksheet 'имя листа, куда будет выводиться результирующая сводная ResultSheetName = "Сводная" Set objRS = GetData() 'создаем заново лист для вывода результирующей сводной таблицы On Error Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivot.Name = ResultSheetName 'выводим на этот лист сводную по сформированному кэшу Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3") Set objPivotCache = Nothing Range("A3").Select End With End Sub Sub UpdateCache() Dim pt As PivotTable Set pt = ActiveWorkbook.Worksheets("Сводная").PivotTables(1) Set pt.PivotCache.Recordset = GetData() pt.RefreshTable End Sub Private Function GetData() As Object 'ADODB.Recordset Dim i As Long Dim arSQL() As String Dim objRS As Object Dim SheetsNames As Variant 'массив имен листов с исходными таблицами SheetsNames = Array("Альфа", "Бета", "Гамма", "Дельта") With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1)) For i = LBound(SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i 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 GetData = objRS End FunctionДля того, чтобы предоставить возможность работы со срезами, надо следовать определенному формату источника данных - прописывать путь к файлу.
Пример, где данные берутся из разных файлов, здесь (автор - К.Л.):
Полагаю, что при извлечении данных из этого же файла, полный путь надо дублировать (несмотря на то, что это листы одной книги), потому что сейчас в определении подключения ничего нет (Источник данных/Свойства подключения/Определение/Текст команды - не заполнено). Обратите внимание на внешний источник данных - xlExternal (хотя это листы одной книги):
Сравните:
Н.П.: "UNION ALL SELECT * FROM [" & SheetsNames(i) & "$]"
К.Л.: "UNION ALL SELECT * FROM `" & arrFiles(i) & "`.[" & strSheet & "$]"
Различие вот здесь: у меня arrFiles(2) возвращает путь к файлу К.Л. --> C:\Users\User1\Downloads\KL_PivotWkbks\DB-Manitoba.xls
У Николая путь к файлу опущен.
формат строки подключения вот здесь:
При указании имени листа Excel надо добавлять знак "$" и оборачивать его в квадратные скобки "[" "]", как это показано выше.
Еще одно добавление для тех, кто будет читать, по поводу установки Microsoft Access. Эта программа есть не у всех (в стандартный комплект не входит), но здесь она и не нужна, нужен только OLEDB провайдер данных (Microsoft.Jet.OLEDB.4.0 или его аналоги под конкретную среду).
Владимир Безносюк, чуть выше (05.07.2013 11:58:49), спрашивает:
"Как изменить код так, что-бы сформированный кэш из нескольких листов выводился на скрытый лист "база" без формирования сводной, и каждый раз при запуске макроса информация на листе "база" заменялась
Дело в том, что при выводе данных на отдельный лист, я смогу создать несколько сводных таблиц (т.е. в разных разрезах) из диапазона данных на листе, и при запуске макроса нет необходимости опять настраивать таблицы, а просто их обновить".
Вероятно, правильным будет использовать один, главный, кэш для всех созданных таблиц.
Пример изменения кэша для всех сводных таблиц книги:
Sub ChangePivotCache() Dim pt As PivotTable Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets For Each pt In wks.PivotTables pt.CacheIndex = Sheets("Сводная").PivotTables(1).CacheIndex Next pt Next wks End Sub"Работать" такая книга будет намного лучше.
У меня задача состоит в том, чтобы вытащить данные из MS SQL Server на три таблицы и собрать их в одну сводную. Две таблицы содержат планы по двум разным брендам, третья - продажи. В связи с этим вопросы:
1) Если я обновляю выгрузку из БД, то сразу запущенный макрос показывает нулевую сумму. Чтобы все заработало, нужно сохранить книгу, закрыть и открыть снова. Как это исправить?
2) Второй вопрос заключается в том, что похоже возникает какая-то сложность есть, если у нас имеются данные, отличающиеся только значениями. Я прилагаю пример. В одной таблице план, а в другой факт. В итоге сводная по вашему макросу показывает, что она видит 1 количество по плану и факту, но 0 сумму по плану и факту, хотя ожидалось, что будет 7 и 8 в сумме. Как это исправить?
Во-первых, спасибо за этот сайт. Во-вторых за этот макрос - немного помог в работе! У меня такой вопрос: при пользовании этого макроса в сводной таблице не получается подвести "Итого По количеству чисел" - это моя локальная проблема или при таком методе построения сводных таблиц некоторые функции недоступны.
Измените макрос в части
и будет в кэше сводной поле с именем листа с которого взяты данные. По этому полю можно будет делать срезы. Или я чего-то не понимаю?
objRS.Open Join$(arSQL, " UNION ALL "), _
Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
.FullName, ";Extended Properties=""Excel 8.0;""";), vbNullString)
выдает ошибку. Пробовала как в комментариях выше заменить "Microsoft.Jet.OLEDB.4.0" на "Microsoft.ACE.OLEDB.12.0", но не помогло. Это проблема с настройкой SQL?
Скорее всего у вас стоит не про версия офиса (без Access 2007-2013), нужно тогда установить драйвер для работы с ACE.OLEDB.12
Определено слишком много полей.
Что это?
В макросе ссылается:
objRS.Open Join$(arSQL, " UNION ALL "), _
Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)
Невозможно найти установленный ISAM.
Не подскажите, что здесь нужно исправить? У меня excel 365
Подскажите, как побороть трабл: Run-time error: 3706 Не удается найти указанного поставщика. Вероятно, он установлен неправильно.
Debug ссылается на:
objRS.Open Join$(arSQL, " UNION ALL "), _
Join$(Array("Provider=Microsoft.jet.OLEDB.4.0; Data Source=", _
.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)
стрелка показывает на последнюю строчку
Спасибо1
Спасибо вам за этот сайт. Но есть вопросик по макросу сводной таблицы. Выдает ошибку
Run-time error '-2147217900 (80040e14)'
в таблицах или запросах, выбранных в запросе на объединение не совпадает количество столбцов.
выделяет
objRS.Open Join$(arSQL, " UNION ALL ";), _
Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
.FullName, ";Extended Properties=""Excel 8.0;""";), vbNullString)
и стрелка указывает на последнюю строку.
Помогите, пожалуйста:oops:
Большое спасибо за данную информацию! Все получилось так как надо. В тонкости программирования не вникаю, скопировала данный макрос в Word и храню как важный док-т!
""... PowerPivot использовать - при таких размерах таблиц как раз она замечательно будет работать. И макросов никаких не надо..."
У меня ситуация в том, что таблиц 8 на 8 листах и отличаются они только временным периодом. Заголовки все одинаковые, но размер не позволяет выгрузить запрос на один лист. Вариантов с OLAP или SQL нет, т.к. приходится возить все с собой и возможности обновиться нет. Попытка объединить через PP у меня заканчивается сообщением, что заголовки одинаковые. Подскажите как объединить все таблицы в одну сводную.
Спасибо.
Не работал с макросами.
Добавил модуль как указано, на всякий случай предварительно добавив Листы с именами, указанными в макросе (Сводная, Альфа, Бета, Гамма, Дельта)
А что дальше? Как использовать этот модуль?
Подскажите: как изменить макрос, чтобы собирать в сводную таблицу листы с названием, к примеру, "Экспорт" но со всех открытых в данный момент книг excel?
Нельзя ли создать сводную табл. из такого большого кол. книг и потом отфильтровать дубликаты ?
Или есть другие способы сверивания данных ?
За каждый совет большое спасибо ! ЮРА.
Возможен сбор данных не только с листов, но и закрытых книг, так же не только из таблиц, но и сразу и с произвольных ячеек и включения имен листов в итоговую таблицу и много чего еще.
Огромное спасибо за этот сайт!
Имею локальную проблему: в исходном массиве есть колонки "Месяц" и "Наименование_Сети". Для каждого месяца по два листа с количеством строк не более 50000.
При формировании сводной с полем "Месяц" в столбцах, в некоторых месяцах показатели по сетям не суммируются, то есть все значения поля "Наименование_Сети" в этих месяцах понимаются екселем как "пустые". При этом, если оставить в исходной выборке только листы с месяцами, в которых первоначально возникала ошибка - сводная формируется корректно.
Подскажите, пожалуйста, как можно это исправить?