Сводная по нескольким диапазонам с разных листов

Думаю, что большинство профессиональных пользователей согласятся со мной в том, что сводные таблицы - один из самых замечательных инструментов в Excel. Но до сих пор, к сожалению, ни одна из версий Excel до 2010-й включительно не умеет делать такой простой и нужной вещи как построение сводной по нескольким исходным диапазонам данных, находящимся, например, на разных листах:

pivot_multi_sheets1.gif

Формально говоря, во всех версиях до 2003 включительно в мастере сводных таблиц была опция "построить сводную по нескольким диапазонам консолидации". Однако, отчет, построенный таким образом был лишь жалким подобием настоящей полноценной сводной и не имел большей части возможностей настройки обычных сводных таблиц:

pivot_multi_sheets2.gif

В такой сводной нет заголовков столбцов в списке полей, нет гибкой настройки структуры, ограничен набор используемых функций и, в общем и целом, все это слабо похоже на сводную таблицу. Возможно именно поэтому в Excel 2007/2010 эту функцию убрали из стандартного диалога при создании отчетов сводных таблиц и она доступна только через настраиваемую кнопку "Мастер сводных таблиц" (Pivot Table Wizard), которую при желании можно добавить на панель быстрого доступа.

Тем не менее, выход есть всегда. Одно из решений представлено вот таким макросом, который надо добавить в новый модуль в редакторе Visual Basic (жмем 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

Макрос создает набор данных (cache) для сводной таблицы, используя команду UNION языка запросов SQL. Эта команда объединяет таблицы со всех указанных в массиве SheetNames листов книги в единую базу данных. То есть вместо физического копирования-вставки диапазонов с разных листов на один мы делаем то же самое в оперативной памяти компьютера. Потом макрос добавляет новый лист с заданным именем (переменная ResultSheetName) и создает на нем полноценную(!) сводную на основе собранного кэша.

Минусы такого подхода:

  • Данные не обновляются, т.к. кэш не имеет связи с исходными таблицами. При изменении исходных данных надо запустить макрос еще раз и построить сводную заново.
  • Таблицы могут иметь любое количество строк с любыми данными, но обязательно - одинаковую шапку.
  • На листах с исходными таблицами не должно быть лишних данных. Один лист - одна таблица. Для контроля советую использовать сочетание клавиш Ctrl+End, которое перемещает вас на последнюю использованную ячейку листа. В идеале - это должна быть последняя ячейка таблицы с данными. Если при нажатии на Ctrl+End выделяется какая-либо пустая ячейка правее или ниже таблицы - удалите после таблицы эти пустые столбцы справа или строки снизу и сохраните файл.

Зато в итоге получаем самую настоящую полноценную сводную таблицу, построенную по нескольким диапазонам с разных листов:

pivot_multi_sheets3.gif

Вуаля!

Ссылки по теме

 


если мне не нужно собирать данные из разных источников как мне сделать точно такой же конструктор чтобы я мог перетаскивать шапки и у меня менялся отчет
10.03.2013 09:34:01
Обожаю такие формулировки вопросов. Куда перетаскивать? Какой конструктор? Если вы про создание сводной таблицы по одному диапазону без консолидации, то см. первую ссылку в Ссылки по теме.
19.03.2013 11:00:12
Возможно изменить функцию так, что-бы имена листов вычислялись из книги (каждый раз писать новые имена в макрос не удобно)
Спасибо!
11.04.2013 08:31:39
Да, конечно. Напишите вместо
SheetsNames = Array("Альфа"[/FONT], "Бета", "Гамма", "Дельта")

что вида
SheetNames(1)=Worksheets("Лист1").Range("A1")
SheetNames(2)=Worksheets("Лист1").Range("A2")

... и т.д., где в ячейках А1,А2... лежат имена обрабатываемых листов.
11.04.2013 08:55:48
Спасибо!
04.05.2016 11:07:20
Добрый день, Николай!
Спасибо за Ваш сайт и за данную статью.

на одном из форумов нашел вот такое решение с листами ...
http://www.excelworld.ru/forum/10-17185-1
Заменить в макросе:
'массив имен листов с исходными таблицами
SheetsNames = Array("a1", "a2", "a3", "a4";)

на:

Dim s As String
'Ввод массива имен листов с исходными таблицами
s = InputBox("Имена листов через запятую";)
s = Replace(s, ", ", ",";)  'Удаляем возможные пробелы после запятой
SheetsNames = Split(s, ",";)
21.12.2016 13:30:06
Добрый день! Подскажите пожалуйста как поменять столбцы, в вашем файле берет столбик А:К, а мне например нужен К:Р )
27.03.2013 15:32:56
Здавствуйте я использовал ваш пример в своих таблицах , все прекрасно но
есть нюанс\\ Дело в том что этот макрос не подсчитывает строки ,если их больше 50.000
(у меня тоже 4 листа как в примере но каждый состоит из 110.000-120.000 строк )
Может я что-то упускаю???

Подскажите если не трудно.
11.04.2013 08:29:16
В коде этого ограничения точно нет. Возможно, это уже нехватка ресурсов компьютера (ограничение кэша Excel 2010 версии - 2 Гб). Точнее сказать, не видя вашего примера, сложно.
Может вам уже бесплатную надстройку PowerPivot использовать - при таких размерах таблиц как раз она замечательно будет работать. И макросов никаких не надо.
11.01.2014 12:13:36
Добрый день! Подскажите, пожалуйста, а как в PowerPivot можно объединить данные из двух источников данных? (у меня данные в sharepoint, отдельно я их из списков могу получить, а как объединить в один источник данных для построения сводной - не могу найти.
24.03.2016 09:53:34
как я установила опытным путем, ограничение 65536, т.е. кол-во строк в старом Excel
причем что характерно: если строк будет больше 65536, то брать в сводную таблицу будет не первые 65536, а например, первые 4000. или 9000.
т.е. получается даже не обрыв таблицы, а совсем что-то неадекватное.

дело точно не в кэше Excel, потому что я разбила каждую из своих 12 таблиц по 85 тыс.строк на две таблицы по 40-45 тыс строк.и стало все нормально.
т.е. 12 таблиц по 85 тыс.строк не сводились.
а 24 таблицы по 45 тыс.строк сводятся абсолютно корректно. кэша хватает :)
04.04.2013 11:49:52
Николай, вещь полезная, но при использовании данного варианта становится невозможно использование СРЕЗОВ. Оптимизация кода не планируется? :)
11.04.2013 08:25:39
Спасибо за наводку. Подумаем :)
11.04.2013 19:20:18
Здраствуйте.

Я подошел к решению этой проблемы с другой стороны. Я сделал макрос который на новом листе формирует таблицу, которая содержит дание с других листов независимо от колиства строк в этих таблицах, а также выделяет новою таблицу. Осталось только вставить сводную таблицу, но проблема заключается в том как задать диапазон даных для сводной таблици, так как количество строк может менятся. Я только начал изучать VBA и ище многого не усвоил, помогите пожайлуста.


Спасибо заранее.
13.04.2013 08:26:09
Включите макрорекордер и создайте простую сводную таблицу. Затем посмотрите код VBA, который получился и замените в нем фиксированный диапазон с исходными данными на:
Worksheets("Лист1").UsedRange
22.04.2013 21:12:39
здравствуйте! Проблема следующая: при формировании данным макросом сводной таблицы не все значения из исходных листов попадают в поле сводной таблицы. Т.е. у меня на всех листах имеется столбец с данными  который называется "номер скважины". Так вот, если в нем имеются данные типа "100н" т.е. нечисловые, то в сводную таблицу отбираются только числовые данные. Или наоборот. Как решить эту проблему. У меня только один вариант; применить к этому столбцу формат "текстовый". Но это не очень то удобно. Вообще почему так происходит? и можно ли исправить эту проблему программно?
09.05.2013 15:04:09
Если у вас в столбце одновременно есть и текстовые и числовые значения, то что вы планируете увидеть в сводной?
09.05.2013 15:22:29
хотелось бы видеть и текстовые и числовые данные.Вообще элементы как правило имеют числовые значения, но некоторые - тектовые и они просто не попадают в сформированную сводную таблицу
09.05.2013 15:24:09
Тогда, боюсь, единственный вариант - форматировать все как текст. Поля смешанного типа тут не поддерживаются.
08.05.2013 19:23:59
Макрос допускал ошибку на Win 8 x64, Office 2013 x64.
Помогла замена "Microsoft.Jet.OLEDB.4.0" на "Microsoft.ACE.OLEDB.12.0"
09.05.2013 15:05:16
Спасибо за уточнение. На 64-битном Office мне его негде было потестировать :)
05.07.2013 11:58:49
Добрый день, помогите пожалуйста:)
Как изменить код так, что-бы сформированный кэш из нескольких листов выводился на скрытый лист "база" без формирования сводной, и каждый раз при запуске макроса информация на листе "база" заменялась
Дело в том, что при выводе данных на отдельный лист, я смогу создать несколько сводных таблиц (т.е. в разных разрезах) из диапазона данных на листе, и при запуске макроса нет необходимости опять настраивать таблицы, а просто их обновить
Спасибо
15.07.2013 20:57:13
Всем доброго времени. Прошу повторно вернуться к вопросу PetrovichOskol от 11.04.2013. Он на разъяснения Николая написал СПАСИБО. Значит у него получилось. Но у меня почему то никак не идет.  Я вместо
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")
    
 
В книге всего 5 листов. 1,2,3,4,Лист1. В Лист1 в А1 = Альфа, в А2=Бета, А3=Гамма, А4=Дельта
Выдает ошибку в SheetNames(1) Подскажите пожалуйста что я сделал неправильно? Ведь у PetrovichOskol видимо все получилось. С уважением. Юрий.
23.07.2013 10:51:01
Подскажите пожалуйста, вообще нет возможности сделать обновление сводной таблицы, которая была построена Вашим макросом?
26.07.2013 10:50:34
Нет. Только создавать заново.
Добрый день.
У меня задача состоит в том, чтобы вытащить данные из MS SQL Server на три таблицы и собрать их в одну сводную. Две таблицы содержат планы по двум разным брендам, третья - продажи. В связи с этим вопросы:

1) Если я обновляю выгрузку из БД, то сразу запущенный макрос показывает нулевую сумму. Чтобы все заработало, нужно сохранить книгу, закрыть и открыть снова. Как это исправить?

2) Второй вопрос заключается в том, что похоже возникает какая-то сложность есть, если у нас имеются данные, отличающиеся только значениями. Я прилагаю пример. В одной таблице план, а в другой факт. В итоге сводная по вашему макросу показывает, что она видит 1 количество по плану и факту, но 0 сумму по плану и факту, хотя ожидалось, что будет 7 и 8 в сумме. Как это исправить?
03.09.2014 18:16:46
Зачем из MS SQL вытаскивать три таблицы в разные таблицы. Можно вытащить сразу одной таблицей, используйте "UNION ALL"
02.10.2013 11:16:14
Подскажите пожалуйста, вообще нет возможности сделать обновление сводной таблицы
В принципе, возможно. Нужно только создать подключение с хранимым SQL запросом, объединяющим данные с нескольких листов, а уже это подключение использовать как источник данных для сводной. Тогда будет возможность обновлять сводную.
25.10.2013 19:19:32
Вечер добрый. Подскажите пожалуйста: со срезами получилось что-нибудь? Чтобы можно было на данные с разных листов наложить срез?
27.10.2013 08:56:40
Пробовал, но не пока не выходит.
27.10.2013 18:43:35
Добрый вечер!
Во-первых, спасибо за этот сайт. Во-вторых за этот макрос - немного помог в работе! У меня такой вопрос: при пользовании этого макроса в сводной таблице не получается подвести "Итого По количеству чисел" - это моя локальная проблема или при таком методе построения сводных таблиц некоторые функции недоступны.
29.10.2013 09:43:17
Пробовал, но не пока не выходит.
Николай, а в чём проблема?
Измените макрос в части
"SELECT """ & & SheetsNames(i) & """ As С_Листа, * FROM [" & SheetsNames(i) & "$]"

и будет в кэше сводной поле с именем листа с которого взяты данные. По этому полю можно будет делать срезы. Или я чего-то не понимаю?
29.10.2013 17:03:31
Николай, подскажите пожалуйста, на этапе:

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?
30.10.2013 10:52:24
Светлана С
Скорее всего у вас стоит не про версия офиса (без Access 2007-2013), нужно тогда установить драйвер для работы с ACE.OLEDB.12, например, отсюда.
14.11.2013 16:36:07
Извиняюсь что так долго. Спасибо большое! Скачала, установила и все заработало отлично
06.12.2013 15:43:49
У меня 2010 Про X64 стоит под Win8.1 x64 и выскакивает то же самое. ставил то что предлагали по ссылке + AccessRuntime 2010 X64. Не помогло.
28.12.2013 11:31:36
Не уверен, что этот макрос будет работать в 64-битном Office без корректировки.
22.01.2014 09:24:16
Run-time error '-2147467259 (80004005)':
Определено слишком много полей.

Что это?
В макросе ссылается:

objRS.Open Join$(arSQL, " UNION ALL "), _
                  Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
                              .FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)
10.02.2014 14:48:52
Run-time error '-2147467259 (80004005)':
Невозможно найти установленный ISAM.

Не подскажите, что здесь нужно исправить? У меня excel 365
26.02.2014 13:30:50
Добрый день!
Подскажите, как побороть трабл: 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
24.06.2014 20:11:16
А как добавить еще одну страницу в данный пример?
Привет уважаемые разработчики данного макроса! При обновлении макроса все строки и графы исчезают . Поэтому приходится заново создавать. Можно заставить работать макрос так, чтобы при внесении новых данных и запуске макроса оставалось  исходное форматирование сводной таблицы? При описании минусов работы данного макроса указано, что нужно заново формировать таблицу, но всё таки, можно попробовать заставить работать макрос  так,чтобы таблица осталась?
07.10.2014 18:29:06
Запиши рекордером код и подставь.
28.01.2015 21:56:12
записал макрос который, восстанавливает нужное мне форматирование после перезапуска с целью обновления данных, но проблема в том что при каждом запуске Вашего макроса имя сводной таблицы меняется (СводнаяТаблица1, СводнаяТаблица2, СводнаяТаблица3 и т.д.) и от этого у меня ошибка. подскажите пожалуйста как сделать её имя статичным?
23.03.2015 12:00:11
Я правильно понимаю, что надстройка PowerPivot в 2013 Excel выполняет ту же самую задачу?
23.03.2015 15:28:14
Не совсем. Этот пример про то, как собрать несколько таблиц в одну друг под друга в оперативной памяти и построить по этой общей куче сводную. А Power Pivot умеет строить сводную по нескольким связанным таблицам, т.е. таким, которые пришлось бы без нее сначала связывать функцией ВПР. В Power Pivot для этого достаточно создать связь. И это можно сделать без Power Pivot - в Excel 2013 этот функционал уже встроен в базовой версии.
07.04.2015 12:02:49
Добрый день.
Спасибо вам за этот сайт. Но есть вопросик по макросу сводной таблицы. Выдает ошибку
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:
29.04.2015 11:43:30
Добрый день!
Большое спасибо за данную информацию! Все получилось так как надо. В тонкости программирования не вникаю, скопировала данный макрос в Word и храню как важный док-т! :)
04.06.2015 10:31:34
Николай здравствуйте. Здесь часто звучит что PowerPivot может объединять разные таблицы и обрабатывать их как целое.

""... PowerPivot использовать - при таких размерах таблиц как раз она замечательно будет работать. И макросов никаких не надо..."

У меня ситуация в том, что таблиц 8 на 8 листах  и отличаются они только временным периодом. Заголовки все одинаковые, но размер не позволяет выгрузить запрос на один лист.  Вариантов с OLAP или SQL нет, т.к. приходится возить все с собой и возможности обновиться нет. Попытка объединить через PP у меня заканчивается сообщением, что заголовки одинаковые. Подскажите как объединить все таблицы в одну сводную.

Спасибо.
24.06.2015 16:34:34
Добрый вечер!
Не работал с макросами.
Добавил модуль как указано, на всякий случай предварительно добавив Листы с именами, указанными в макросе (Сводная, Альфа, Бета, Гамма, Дельта)
А что дальше? Как использовать этот модуль?
27.12.2015 02:19:01
Добрый день.
Подскажите: как изменить макрос, чтобы собирать в сводную таблицу листы с названием, к примеру, "Экспорт" но со всех открытых в данный момент книг excel?
19.03.2016 16:56:54
Добрый день. У меня такая ситуация.... Есть папка с товаром за год . В этой папке 12 папок товара по месяцам. В каждой папке 20-22 книги ексель с названием по датам. В этих книгах от 1 до 15 листов а в каждом есть коды товара. При поступлении нового товара мне нужно проверить, не был ли этот товар раньше у нас. А на данный момент мне нужно сверить товар за весь год, не был ли тот же товар у нас дважды (напр. коды товара с апреля, не совпадают ли с кодами тов. за июль ) . Каждая книга называется датой , каждый лист - поставщик.
Нельзя ли создать сводную табл. из такого большого кол. книг и потом отфильтровать дубликаты ?
Или есть другие способы сверивания данных ?
За каждый совет большое спасибо ! ЮРА.
22.08.2016 07:11:52
Добавлю более универсальное решение на основе SQL запросов с использованием надстройки Активные Таблицы.
Возможен сбор данных не только с листов, но и закрытых книг, так же не только из таблиц, но и сразу и с произвольных ячеек и включения имен листов в итоговую таблицу и много чего еще.

https://vk.com/topic-72446554_33994932
22.09.2016 10:51:32
Доброго времени суток!
Огромное спасибо за этот сайт!
Имею локальную проблему: в исходном массиве есть колонки "Месяц" и "Наименование_Сети". Для каждого месяца по два листа с количеством строк не более 50000.
При формировании сводной с полем "Месяц" в столбцах, в некоторых месяцах показатели по сетям не суммируются, то есть все значения поля "Наименование_Сети" в этих месяцах понимаются екселем как "пустые". При этом, если оставить в исходной выборке только листы с месяцами, в которых первоначально возникала ошибка - сводная формируется корректно.
Подскажите, пожалуйста, как можно это исправить?
18.10.2016 12:24:13
Николай здравствуйте! Знаю, что, если "поле страниц" содержит несколько значений, то можно на каждое из этих значений открыть новый лист. Но каким образом? Какой параметр дает эту возможность?
22.11.2016 00:03:05
Добрый день. Подскажите, какое максимальное кол-во строк сможет выдать сводная таблица построенная данным методом?(изначальные таблицы содержали суммарно свыше 3 млн. строк)
22.05.2017 15:29:27
22.05.2017 15:33:01
Это не ответ, ошибся комментарием
23.10.2017 12:42:13
Николай, добрый день!
Могли бы Вы пожалуйста подсказать как настроить в сводной таблице формат времени.
мне нужно проанализировать данные со временем, с этим проблемка( подскажите если не трудно.

спасибо заранее
19.02.2018 08:22:46
Здравствуйте! Пишет "run time error 3706" не удается найти указанный поставщик.... что делать?