Сборка данных со всех листов книги в одну таблицу

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

Допустим, что у нас есть книга с большим количеством листов, где на каждом листе находится таблица с данными по сделкам в этом городе:

Исходный файл

Давайте будем исходить из следующих соображений:

  • Структура и столбцов на всех листах одинаковая.
  • Количество строк на всех листах разное.
  • Листы могут в будущем добавляться или удаляться.

Наша задача - собрать все данные со всех листов в одну таблицу, чтобы потом с ней работать (фильтровать, сортировать, построить сводную и т.д.) Сделать это можно разными способами, но самыми удобными будут, пожалуй, Power Query и макросы.

Способ 1. Сборка данных с листов с помощью Power Query

Если вы ещё не сталкивались в своей работе с Power Query, то очень советую копнуть в этом направлении. Использование этой бесплатной и уже встроенной по умолчанию в Excel надстройки, способно полностью перевернуть весь ваш процесс сбора и анализа данных, упростив всё в разы. С задачей сбора данных с листов Power Query справляется весьма успешно.

Шаг 1. Подключаемся к файлу

Для начала, создадим новый пустой файл в Excel, куда и будут собираться данные.

Если у вас Excel 2010-2013 и вы установили Power Query как отдельную надстройку, то откройте вкладку Power Query, если у вас Excel 2016 или новее, то вкладку Данные (Data). Нажмите кнопку Получить данные / Создать запрос - Из файла - Книга Excel (Get Data / New Query - From file - From Excel) и укажите наш файл с исходными листами:

Указываем файл

В появившемся окне Навигатора (Navigator) выберите слева любой лист и нажмите в правом нижнем углу кнопку Преобразовать данные (Transform Data) или Изменить (Edit):

Выбираем любой лист

Должно появиться окно редактора запросов Power Query, где отобразятся данные с выбранного листа. Поскольку нам нужен, на самом деле, не один лист, а все, то удалим в правой панели все шаги, кроме первого шага Источник (Source) используя крестик слева от названия шага:

Удаляем лишние шаги

То, что останется после удаления шагов - это список всех объектов, которые Power Query "видит" во внешних файлах, а это:

  • листы (Sheet)
  • "умные таблицы" (Table)
  • именованные диапазоны (Defined Name)
  • области печати (Print Area), которые, по сути, являются одним из видов именованного диапазона

Шаг 2. Отбираем нужные листы

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

Во-первых, легко можно отфильтровать нужные объекты по типу по столбцу Kind. Например, если вам нужны только листы:

Фильтруем листы

Во-вторых, если нам нужны только видимые листы, то дополнительно можно отфильтровать ещё по столбцу Hidden.

В-третьих, если вы точно знаете размер таблиц, которые вам нужны, то можно легко добавить к нашему списку вычисляемый столбец с формулой, выводящей количество столбцов или строк и использовать потом эти числа для отбора. Для этого выберем на вкладке Добавление столбца - Настраиваемый столбец (Add Column - Custom Column) и введём в открывшееся окно следующую формулу (с учётом регистра):

Подсчет числа столбцов

Для подсчёта количества строк можно использовать аналогичную функцию Table.RowCount. Получившийся столбец затем можно использовать для фильтрации "мусорных" таблиц.

В-четвёртых, можно извлечь с каждого листа содержимое любой ячейки (например, А1) и использовать его для отбора. Например, если там нет слова "Товар", то это не наш лист. Для извлечения нужно будет также добавить вычисляемый столбец с такой конструкцией:

=[Data][Column1]{0}

Здесь:

  • [Data] - имя столбца, где в каждой ячейке лежат таблицы с содержимым каждого листа (убийственная формулировка для рядового пользователя Excel, да, я знаю)
  • [Column1] - имя столбца на листе, из которого мы хотим извлечь данные
  • {0} - номер строки (считая с нуля), откуда мы хотим взять данные

Извлекаем содержимое А1 с каждого листа

После фильтрации "мусора" все добавленные вспомогательные столбцы можно, конечно же, спокойно удалить, оставив только колонки Name и Data.

Шаг 3. Разворачиваем таблицы

Теперь развернём содержимое таблиц в одно целое, используя кнопку с двойными стрелками в заголовке столбца Data, отключив флажок Использовать исходное имя столбца как префикс (Use original column name as prefix):

Разворачиваем вложенные таблицы

После нажатия на ОК Power Query соберёт для нас все данные в одну мегатаблицу со всех отобранных листов нашего файла:

Собранные данные

Останется лишь "навести блеск", а именно:

  1. Поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home).
  2. Переименовать первый столбец в Город двойным щелчком на заголовку.
  3. Удалить повторяющиеся шапки таблиц, попавшие в одну кучу вместе с данными, используя фильтр по столбцу Товар.

Всё. Осталось только дать нашему запросу подходящее имя (например, Сборка) в панели справа и выгрузить затем собранные данные обратно в Excel кнопкой Закрыть и загрузить на вкладке Главная (Home - Close & Load):

Собранные данные

В будущем, при любых изменениях в исходном файле достаточно будет лишь обновить наш запрос, щелкнув по собранной таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) или такой же кнопкой на вкладке Данные (Data) или сочетанием клавиш Ctrl+Alt+F5.

Плюсы такого подхода:

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

Минусы этого способа:

  • Собираются только значения, т.е. формулы с исходных листов не сохраняются.
  • Названия столбцов должны на всех листах совпадать с точностью до регистра.
  • Нельзя выбрать какой именно диапазон берётся с каждого листа - это определяется автоматически (берётся всё, что есть).
  • Для обновления нужен Excel 2016 или новее или установленная надстройка Power Query.

Способ 2. Сборка данных с листов макросом на VBA

Похожего результата можно добиться и с помощью более "классического" подохода - макросом на VBA. Для этого на вкладке Разработчик (Developer) нажмите кнпоку Visual Basic или воспользуйтесь сочетанием клавиш Alt+F11. В открывшемся окне добавьте новый модуль через меню Insert - Module и скопируйте туда текст вот такого макроса:

Sub CollectDataFromAllSheets()
    Dim ws As Worksheet
    
    Set wbCurrent = ActiveWorkbook
    Workbooks.Add
    Set wbReport = ActiveWorkbook
    
    'копируем на итоговый лист шапку таблицы из первого листа
    wbCurrent.Worksheets(1).Range("A1:D1").Copy Destination:=wbReport.Worksheets(1).Range("A1")
    
    'проходим в цикле по всем листам исходного файла
    For Each ws In wbCurrent.Worksheets
    
        'определяем номер последней строки на текущем листе и на листе сборки
        n = wbReport.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
        
        'задаем исходный диапазон, который надо скопировать с каждого листа - любой вариант на выбор:
        Set rngData = ws.Range("A1:D5")            'фиксированный диапазон или
        Set rngData = ws.UsedRange                 'всё, что есть на листе или
        Set rngData = ws.Range("F5").CurrentRegion    'область, начиная от ячейки F5 или
        Set rngData = ws.Range("A2", ws.Range("A2").SpecialCells(xlCellTypeLastCell))    'от А2 и до конца листа
        
        'копируем исходный диапазон и вставляем в итоговую книгу со следующей строки
        rngData.Copy Destination:=wbReport.Worksheets(1).Cells(n + 1, 1)
        
    Next ws
End Sub

Запустить созданный макрос можно на вкладке Разработчик кнопкой Макросы (Developer - Macros) или с помощью сочетания клавиш Alt+F8. Макрос автоматически создаст новую книгу и скопирует туда нужные вам данные.

Плюсы такого подхода:

  • Формулы с исходных листов сохраняются в сборке.
  • Имена столбцов не играют роли.
  • Макрос будет работать в любой версии Excel.
  • Можно выбирать, что именно брать с каждого листа (конкретный фиксированный диапазон или весь лист).

Минусы этого способа:

  • Последовательность столбцов на всех листах должна быть одинаковой, т.к. происходит, по сути, тупое копирование таблиц друг-под-друга.
  • Защита от макросов должна быть отключена.
  • Быстрого обновления, как это было с Power Query, здесь, к сожалению, не будет. При изменении исходных данных придётся запустить макрос повторно.

Способ 3. Готовый макрос из надстройки PLEX

Если лень возиться с макросами или Power Query, то можно пойти по пути наименьшего сопротивления - использовать готовый макрос (кнопка Собрать) из моей надстройки PLEX для Excel. Это, может, и не спортивно, но зато эффективно:

Сборка данных с листов через PLEX

В общем, выбирайте любой удобный вам вариант и действуйте. Выбор - это всегда хорошо.

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

 



25.03.2019 13:08:53
Шаг 1. Можно не выбирать лист и потом удалять шаги, а сразу файл выбирать
07.04.2019 08:04:07
Согласен. Добавил в видео этот момент. Спасибо!
07.04.2019 14:15:19
Привет!
Строки:
        Set rngData = ws.Range("A1:D5")            'фиксированный диапазон или
        Set rngData = ws.UsedRange                 'всё, что есть на листе или
        Set rngData = ws.Range("F5").CurrentRegion    'область, начиная от ячейки F5 или

забыли удалить?
08.04.2019 09:44:41
Николай, подскажите, пожалуйста, как бороться с ошибкой возникающей при переименовании первого листа (либо ставкой листа в начало книги).
Ошибка возникает при выполнении строчки:
#"Переименованные столбцы" = Table.RenameColumns(#"Измененный тип",{{"Абакан", "Город"}}),

Такая ошибка возникает, в том числе, при работе с файлами из папки.

Спасибо!

P.S.
Когда мы увидим Вашу книгу по PQ? Очень ждём!
09.04.2019 23:10:49
Сергей, а как у вас в заголовке оказался "Абакан"? Если вы делали все как написал Николай, такого бы не было. Проверьте по шагам. Ну и в большинстве случаев проблемы доставляют автоматически создающиеся шаги #"Измененный тип", как правило их можно удалять, оставляя приведение типов на последний шаг.
10.04.2019 09:30:22
Степан, "Абакан" оказался в заголовке после первого шага в "наведении блеска" - повышения заголовков. На экранной форме Николая видно, что "Абакан" находится на одной строке с другими заголовками. Следующий шаг как раз и приводит к ошибке в случае, если появился новый лист в начале книги.

С такой проблемой я часто встречаюсь при обработке файлов из папки. И технически с помощью условных столбцов решение этой проблемы мне известно. Но возможно, существует более изящное решение.
Удалил. Разобрался
11.04.2019 17:48:03
Простите, а "технически с помощью условных столбцов" это как? я, обычно, из папки делаю через функцию и файл примера
12.04.2019 14:41:05
В случае обработки файлов из папки, если данные берутся просто из диапазона, повышение столбцов приводит к внесению наименования первого файла в заголовок. Если в будущем в этой папке появляется файл с названием "выше" чем старые файлы, то записанный ранее шаг с переименованием приводит к ошибке. Условными столбцами эту проблему обойти можно. Хотелось бы услышать, как Николай с этим борется.
15.04.2019 17:30:20
Простите, проблема мне понятна, сам наталкивался не раз, меня интересовало как она решается "технически с помощью условных столбцов"
16.04.2019 16:12:36
Алексей, на самом деле идея очень простая, но почти всегда требуются "особые" настройки, которые необходимо внести до повышения первой строки.
В случае файла Николая условный столбец может быть такой:
#"step_N" = Table.AddColumn(#"step_N-1", "NEW_COLUMN", each if [Column1] = "Товар" then "SheetName" else [Name],
Т.е. если в столбце "Column1" содержится слово "Товар", то в новый столбец вносим "SheetName", в противном случае наименование листа из столбца "Name".
Теперь необходимо удалить первый столбец с наименованиями листов, применить фильтр по слову не "Товар" и повысить первую строку.
Готово!
24.05.2019 18:18:52
Добрый день!
У меня в таком варианте PQ не корректно обрабатывает в случаях если порядок столбцов отличается, а названия столбцов полностью совпадают. Т.е. в итоге данные копируются именно в порядке столбцов на исходных листах.
Подскажите, пожалуйста, что я могу делать не так?
08.06.2019 11:12:50
Здравствуйте. А если книга распологается в облаке (onedrive)? Как сделать подобное?
Наверх