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

Постановка задачи

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

pivot_multi_sheets1.gif

Прежде, чем начать давайте уточним пару моментов. Априори я полагаю, что в наших данных выполняются следующие условия:

  • Таблицы могут иметь любое количество строк с любыми данными, но обязательно - одинаковую шапку.
  • На листах с исходными таблицами не должно быть лишних данных. Один лист - одна таблица. Для контроля советую использовать сочетание клавиш 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) и укажем исходный файл с таблицами, которые надо собрать:

Запрос к файлу 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).

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

pivot_multi_sheets3.gif

Вуаля!

Техническое замечание: если при запуске макроса вы получаете сообщение об ошибке вида "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 включительно в мастере сводных таблиц была опция "построить сводную по нескольким диапазонам консолидации". Однако, отчет, построенный таким образом, к сожалению, будет лишь жалким подобием настоящей полноценной сводной и не поддерживает многие "фишки" обычных сводных таблиц:

pivot_multi_sheets2.gif

В такой сводной нет заголовков столбцов в списке полей, нет гибкой настройки структуры, ограничен набор используемых функций и, в общем и целом, все это слабо похоже на сводную таблицу. Возможно именно поэтому начиная с 2007 года Microsoft эту функцию убрали из стандартного диалога при создании отчетов сводных таблиц. Теперь эта возможность доступна только через настраиваемую кнопку Мастер сводных таблиц (Pivot Table Wizard), которую при желании можно добавить на панель быстрого доступа через Файл - Параметры - Настройка панели быстрого доступа - Все команды (File - Options - Customize Quick Access Toolbar - All Commands):

Добавляем кнопку

После нажатия на добавленную кнопку нужно выбрать на первом шаге мастера соответствующую опцию:

Мастер сводных таблиц

А затем в следующем окне выделить по очереди каждый диапазон и добавить его в общий список:

Выделение диапазонов

Но, повторюсь, это не полноценная сводная, так что не ждите от нее слишком многого. Подобный вариант могу рекомендовать только в очень простых случаях.

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

 


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

спасибо заранее
19.02.2018 08:22:46
Здравствуйте! Пишет "run time error 3706" не удается найти указанный поставщик.... что делать?
04.08.2018 17:23:33
если при запуске макроса вы получаете сообщение об ошибке вида "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
10.08.2018 14:45:09
Добрый день! При использовании способа №1 вопрос: В одной книге есть пять таблиц (листов), которые объединены в сводную. При добавлении данных в уже имеющиеся в листах колонки/строки все нормально обновляется, но при попытке добавить в эти листы новые колонки, или при добавлении новых листов информация не подтягивается.
Есть ли иной способ решения проблемы, кроме как создавать новый запрос?
09.09.2018 17:49:23
Нужно отредактировать запрос, проверив где в его коде встречаются старые имена столбцов и подправив их на новые. Посмотреть исходный код запроса можно в окне Power Query на вкладке Вид - Расширенный редактор.
Добрый день, возможно ли указать версию Сводной Таблицы более новую?
09.09.2018 17:49:38
Не понял - это как?
Чтоб Сводная была без синих разделителей как границы печати (в Excel 2016 выглядит значительно красивее). Пытался прикрутить DefaultVersion но безрезультатно.
27.01.2019 21:21:05
У вас файл не старого xls формата, часом?
14.11.2018 13:07:22
Добрый день, подскажите, пожалуйста, как сделать все то же самое с Power Query в том же файле в котором расположены вкладки с таблицами?
15.11.2018 10:33:10
Добрый день. Если у вас уже создан лист, где хотите строить сводную, то после того, как в правой части окна на панели Параметры запроса удалили все автоматически созданные шаги кроме первого - Источник. У вас будет список листов. Оставляете только с листы с данными.
11.12.2018 14:34:10
Спасибо, что упрощаете жизнь)
15.01.2019 23:15:09
Добрый день !
Как на ios Microsoft Mac Excel версия 16.20 сделать подключение к внешнему источнику данных ?
27.01.2019 21:19:52
Боюсь, что никак. Excel для Mac не содержит Power Query в принципе :(
Технология OLEDB не поддерживается.
Насколько могу судить, сам не пробовал, для версии Mac Excel 2011 это можно сделать только при помощи ODBC-драйверов от сторонних разработчиков. Это стоит денег.
   www.actualtech.com
   www.openlinksw.com

Но, как отмечают, это возможно при использовании версии Mac Excel 2016 при подключении к SQL Server только. К другим источникам, опять-таки, при помощи ODBC-драйверов от сторонних разработчиков.

ADODB is NOT supported in Mac Excel 2011, but ODBC works in conjunction with a 3rd party driver.
HOWEVER, as noted, if you're using Excel 2016 for Mac, you can do it:

This version of Excel does provide an ODBC driver for connecting to SQL Server Databases. On the Data tab, click New Database Query > SQL Server ODBC. Then use the dialog boxes to import the data...
www.microsoft.com/en-us/microsoft-365/blog/2015/08/20/working-with-external-data-in-excel-2016-for-mac/
www.support.office.com/en-us/article/ODBC-drivers-that-are-compatible-with-Excel-for-Mac-9FA6BC7F-D19E-4F7F-9BE4-92E85C77D712
05.03.2019 18:50:47
Здравствуйте, Николай! Подскажите, пожалуйста, почему когда я собираю данные в сводную таблицу, после  объединения их в power query, я получаю Таблицу с непонятными значениями. Можно подумать, что это количества, но когда я выставляю итоги по сумме, все значения приводятся к нулю. Что я делаю не так?
Количество по полю ПрибыльНазвания столбцов
Названия строкАльфаБетаГаммаДельтаОбщий итог
Бакалея196196196196784
Кондитерка202211231235879
Кулинария мясо птицы рыбы16649187142544
Напитки196172196196760
Овощи и фрукты49491049157
ОУХ239147245241872
Сопутствующие товары195193196196780
Общий итог12431017126112554776
13.03.2019 10:19:37
Добрый день, вопрос по первому способу -  в измененных шагах надо и навигацию убирать также? Проделал вроде все как показано, но при раскрытии колонки показывает только данные из одного источника.
15.07.2019 12:25:35
Как можно модернизировать макрос, чтобы он корректно собирал информации на листах, где порядок названий столбцов разный?

Например, если во вкладке "Лист1" Столбцы с данными "артикул" и "наименование", а во вкладке "Лист2" сначала "наименование", а потом "артикул", то макрос не корректно собирает данные.
Добрый день.
У меня вопрос, когда создаю сводную таблицу из нескольких разных таблиц(на разных листах) расположенных в пределах одного документа, и сохраняю ее на этом же документе, при передаче этого документа на другой сервер сводная таблица перестает обновляться и теряет свое предназначение в целом.
Поэтому, приходится делать сводную таблицу одного документа отдельным документом. И тогда при передаче на другой сервер все работает. Но это очень неудобно.
Помоги пожалуйста разобраться в этой проблеме.
Спасибо.
17.09.2019 14:12:17
Такой вопрос: есть папка с книгами .xlsm имена файлов разные, листы с таблицами во всех книгах одинаковые но разные данные в них. Как сделать сводную таблицу, т.е. собрать из всех книг с конкретного листа, например "Заявка" выбрать только столбец С. В общем что бы все столбцы С из всех книг (это около 60 шт.) были на одном листе сводной таблицы?
08.11.2019 17:34:59
это то, что я искал, спасибо большое
https://www.youtube.com/watch?v=AhaNv3Iis3c
28.11.2019 10:35:41
Николай, добрый день. Пытаюсь сделать сводную, как описано в статье с помощью Power Query. Исходные файлы, как и файл со сводной находятся в одной сетевой папке. При построении сводной несколько запросов выдают ошибку подключения. В чём может быть проблема? Файлы к которым выполняется подключения идентичны (размножены из шаблона макросом, который копировал файл, проставлял название региона из списка и сохранял с соответствующим названием)
Здравствуйте, Николай.
Я использовала Power Query для постороения сводной таблицы из разных диапазонов. Причем диапазоны и сводная таблица расположены в одном файле. Все отлично.
НО каждый месяц я добавляю в один диапазон новые данные за текущий период и ,к сожалению, никак не могу обновить сводную. Нажимаю - обновить все -новый период не появляется.
Может здесь какие-то еще нъюансы для обновления сводной ?
Спасибо за ответ.
27.04.2020 13:55:12
Добрый день!

Я правильно понимаю,что на сегодняшний день  (Апрель 2020) самая оптимальная технология для связывания в одной сводной таблице данных из разных таблиц это создание модели данных и Pivot Table?
27.04.2020 16:06:47
Правильно понимаете.
Альтернативы - это слияние запросов через Power Query или ВПР :)
25.05.2020 18:10:08
Добрый день! Создала сводную таблицу 1-ым способом. Но когда я хочу группировать данные, выдает ошибку "выбранный диапазон группировать нельзя". Данные которые я хочу сгруппировать в числовом формате. Подскажите, пожалуйста, в чем может быть проблема?
02.06.2020 17:13:28
как сделать сводную таблицу с разных листов оного файле, где:
1.на каждом листе разные таблицы.
2.в каждой таблице совпадает только одно название первого столбца.
3.количество столбцов и строк разное (например на первом листе таблица мест, второй таблица вида работ и т.д.).
4.сопоставить данные одной таблицы с другой можно только через номер в первом столбце.
5.номер в первом столбце может быть как только в одной таблице, так и в двух или во всех.
PS: EXEL 2016
27.09.2020 20:06:50
не нашёл более подходящей темы для своего вопроса. рискнул задать его поэтому тут. столкнулся со следующей проблемой: в бизнес-плане делаю сценарии событий. один из них предполагает отсутствие некоторых работников, которые есть во втором (сценарии). Т.е., говоря конкретнее, с листа "себестоимость/cogs" мне надо в листе "сценарии" (где сводный результат) видеть сумму зарплат с налогами не всех сотрудников.  А эта сумма, в свою очередь, сидит в листе "персонал/labour". Какой формулой можно это условие ввести? Я тупо умножил на коэфициент, который дал мне нужную цифру...но логику этого решения не видно (((
27.11.2020 04:49:48
Здравствуйте! Использую 1-й способ. Но почему-то в сводной таблице, значения как-бы дублируются. Т.е. в книге на листе например: Затрачено топлива 100 литров, а в сводной таблице пишет, якобы 200 литров. В сводной таблице в "Параметрах полей значений", если в "Операциях" поставить "Количество" то во всех строках отображаются двоечки, якобы значение введено 2 раза. Подскажите куда копать?
14.09.2022 01:52:53
Привет)
У меня проблема разрешилась после удаления фильтра в "шапке" (на листах расположены "диапазоны", т.к. динамич.таблицы не позволяют работать в режиме совместного доступа (старая версия)). Благо, количество строк на каждом листе небольшое, а вот если бы было "поболя", то отсутствие фильтра намного бы испортило удовольствие)).
24.12.2021 08:36:32
Добрый день!
У меня Excel   2016. Стала делать сводную таблицу на своих рабочих таблицах способом №1. Дошла до шага формирования самой сводной таблицы и выскакивает ошибка "[Expression.Error] Ключу не соответствует ни одна строка в таблице". Подскажите, пожалуйста, что я не так сделала.
09.03.2022 15:11:49
Добрый день! Подскажите, как можно создать сводную таблицу с разных листов с большим объёмом, у меня при формировании 1-ым способом часть данных теряется
10.02.2023 10:56:24
Добрый день, если обновляешь данные в листах, обновление не переноситься в сводную?
Страницы: 1  2  
Наверх