Содержание:
Постановка задачи
Сводные таблицы — один из самых замечательных инструментов Excel. Но пока, к сожалению, ни одна из версий Excel не умеет на лету делать такую простую и нужную вещь, как построение сводки для нескольких диапазонов исходных данных, расположенных, например, на разных листах или в разных таблицах:
Прежде чем начать, давайте проясним пару моментов. Априори я считаю, что в наших данных выполняются следующие условия:
- Таблицы могут иметь любое количество строк с любыми данными, но у них должен быть одинаковый заголовок.
- На листах с исходными таблицами не должно быть лишних данных. Один лист – одна таблица. Для управления советую использовать сочетание клавиш Ctrl+Конец, который перемещает вас к последней использованной ячейке на листе. В идеале это должна быть последняя ячейка в таблице данных. Если при нажатии на Ctrl+Конец выделяется любая пустая ячейка справа или под таблицей — удалите эти пустые столбцы справа или строки под таблицей после таблицы и сохраните файл.
Способ 1. Создайте таблицы для сводной таблицы с помощью Power Query.
Начиная с версии 2010 для Excel существует бесплатная надстройка Power Query, которая может собирать и преобразовывать любые данные, а затем предоставлять их в качестве источника для построения сводной таблицы. Решить нашу задачу с помощью этой надстройки совсем не сложно.
Для начала создадим новый пустой файл в Excel — в нем будет происходить сборка, а затем в нем будет создана сводная таблица.
Затем на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) выберите команду Создать запрос – Из файла – Excel (Получить данные — Из файла — Excel) и укажите исходный файл с таблицами, которые нужно собрать:
В появившемся окне выберите любой лист (не важно какой) и нажмите кнопку внизу Изменить (Редактировать):
Окно редактора запросов Power Query должно открыться поверх Excel. В правой части окна на панели Параметры запроса удалить все автоматически созданные шаги, кроме первого – Источник (Источник):
Теперь мы видим общий список всех листов. Если кроме даташитов в файле есть еще какие-то боковые листы, то на этом шаге наша задача выбрать только те листы, информацию из которых необходимо загрузить, исключив все остальные с помощью фильтра в заголовке таблицы:
Удалить все столбцы, кроме столбца Данныещелкнув правой кнопкой мыши заголовок столбца и выбрав Удалить другие столбцы (Удалять другие столбцы):
Затем вы можете развернуть содержимое собранных таблиц, нажав на двойную стрелку вверху столбца (флажок Использовать исходное имя столбца в качестве префикса можно отключить):
Если вы все сделали правильно, то на этом этапе вы должны увидеть содержимое всех таблиц, собранных одна под другой:
Осталось поднять первую строку в шапку таблицы кнопкой Используйте первую строку в качестве заголовков (Используйте первую строку в качестве заголовков) таб Главная (Главная) и удалите повторяющиеся заголовки таблиц из данных с помощью фильтра:
Сохраняем все сделанное командой Закрыть и загрузить – Закрыть и загрузить… (Закрыть и загрузить — Закрыть и загрузить в…) таб Главная (Главная), и в открывшемся окне выберите опцию Только подключение (Только подключение):
Все. Осталось только построить резюме. Для этого перейдите на вкладку Вставка — сводная таблица (Вставка — сводная таблица), выберите вариант Использовать внешний источник данных (Использовать внешний источник данных)а затем нажав кнопку Выберите связь, наша просьба. Дальнейшее создание и настройка пивота происходит совершенно стандартным способом путем перетаскивания нужных нам полей в область строк, столбцов и значений:
Если в дальнейшем исходные данные изменятся или добавятся еще несколько листов магазина, то достаточно будет обновить запрос и нашу сводку с помощью команды Обновить все таб Данные (Данные — Обновить все).
Способ 2. Объединяем таблицы командой UNION SQL в макросе
Еще одно решение нашей проблемы представляет собой этот макрос, который создает набор данных (кэш) для сводной таблицы с помощью команды UNITY Язык запросов SQL. Эта команда объединяет таблицы из всех указанных в массиве Имена Листов листы книги в единую таблицу данных. То есть вместо того, чтобы физически копировать и вставлять диапазоны с разных листов в один, мы делаем то же самое в оперативной памяти компьютера. Затем макрос добавляет новый лист с заданным именем (переменная ИмяРезультирующегоЛиста) и создает по нему полноценную (!) сводку на основе собранного кеша.
Чтобы использовать макрос, используйте кнопку Visual Basic на вкладке разработчик (Разработчик) или сочетание клавиш другой+F11. Затем вставляем новый пустой модуль через меню Вставка – Модуль и скопируйте туда следующий код:
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 = "Pivot" 'массив листов имена с исходными таблицами SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'формируем кэш для таблиц из листов из SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Для i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(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 'пересоздать лист для отображения итоговой сводной таблицы. При ошибке возобновить следующее приложение.DisplayAlerts = False Worksheets(ResultSheetName).Удалить 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").Выберите End With End Sub Готовый макрос затем можно запустить с помощью сочетания клавиш. другой+F8 или кнопку Макросы на вкладке разработчик (Разработчик — Макросы).
Минусы такого подхода:
- Данные не обновляются, поскольку кэш не имеет связи с исходными таблицами. Если вы измените исходные данные, вам придется снова запустить макрос и заново построить сводку.
- При изменении количества листов необходимо редактировать код макроса (массив Имена Листов).
Но в итоге мы получаем настоящую полноценную сводную таблицу, построенную на нескольких диапазонах из разных листов:
Вуаля!
Техническое примечание: если при запуске макроса вы получаете ошибку типа «Провайдер не зарегистрирован», то, скорее всего, у вас 64-битная версия Excel или установлена неполная версия Office (нет доступа). Чтобы исправить ситуацию, замените фрагмент в коде макроса:
Провайдер = Microsoft.Jet.OLEDB.4.0;
чтобы:
Поставщик = Microsoft.ACE.OLEDB.12.0;
А также загрузите и установите бесплатный механизм обработки данных Access с веб-сайта Microsoft — Microsoft Access Database Engine 2010 Redistributable.
Способ 3. Мастер консолидации сводных таблиц из старых версий Excel
Этот метод немного устарел, но все же стоит упомянуть. Формально говоря, во всех версиях до 2003 года включительно в Мастере сводных таблиц была опция «построить сводную таблицу для нескольких диапазонов консолидации». Однако построенный таким образом отчет, к сожалению, будет лишь жалким подобием настоящей полноценной сводки и не поддерживает многие «фишки» обычных сводных таблиц:
В такой сводке нет заголовков столбцов в списке полей, нет гибкой настройки структуры, ограничен набор используемых функций и в целом все это мало похоже на сводную таблицу. Возможно, поэтому начиная с 2007 года Microsoft убрала эту функцию из стандартного диалога при создании отчетов сводной таблицы. Теперь эта функция доступна только через специальную кнопку. Мастер сводных таблиц(Мастер сводных таблиц), который при желании можно добавить на панель быстрого доступа через Файл – Параметры – Настроить панель быстрого доступа – Все команды. (Файл — Параметры — Настроить панель быстрого доступа — Все команды):
После нажатия на добавленную кнопку необходимо на первом шаге мастера выбрать соответствующий вариант:
А затем в следующем окне поочередно выберите каждый диапазон и добавьте его в общий список:
Но, повторюсь, это не полноценное резюме, так что не ждите от него слишком многого. Я могу рекомендовать этот вариант только в очень простых случаях.
- Создание отчетов с помощью сводных таблиц
- Настройка вычислений в сводных таблицах
- Что такое макросы, как их использовать, куда копировать код VBA и т.д.
- Сбор данных из нескольких листов в один (дополнение PLEX)