Как объединить листы в excel в один лист power query

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

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

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

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

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

Наша задача — собрать все данные со всех листов в одну таблицу, чтобы потом с ней работать (фильтровать, сортировать, построить сводную и т.д.) Сделать это можно разными способами, но самыми удобными будут, пожалуй, 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} — номер строки (считая с нуля), откуда мы хотим взять данные

Шаг 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. Это, может, и не спортивно, но зато эффективно:

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

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

• Что такое Power Query и с чем его едят.
• Как создавать свои макросы, использовать чужие и запускать их в своих файлах.
• Сборка данных из нескольких файлов с помощью Power Query.

Merging data into a single sheet is a crucial and significant skill to know. For example, you have the monthly sales of a company in the 12 worksheets of excel. Now you want to calculate the net revenue made in the entire year. So, we need to merge all the data in the 12 excel sheets, this task could be easily achieved by power query in excel. Let’s learn how to merge data from all sheets in a selected file using a power query. 

Power Query 

Power Query is one of the very powerful tools in excel. Some important points of power query are: 

• Power query can save more than 50% of your time.
• Power query comes with triple power. It has functionalities of Advance Excel, SQL, and VBA.
• VLOOKUP function can be used in just a few mouse clicks.
• If you are using an excel version greater than 2016 then you will get a built-in power query. For versions older than this, the power query needs to be added separately.

Power query is already built-in in excel. Go to Data Tab, Get & Transform Data, and Queries & Connections section is the power query for users having the 2019 excel version. 

Merging data from all sheets in a file

The best way to understand merging sheets in a file is by an example, given a data set of three worksheets sheet1, sheet2, and sheet3. Each sheet contains a table name mid_sem_1, mid_sem_2 and end_sem respectively. The table in sheet1 has columns as Roll No. , Name, and Mid Sem 1 marks. The table in sheet2 has columns as Roll No., Mid Sem 2 marks. The table in sheet3 has columns as Name and End Sem marks. The name of this workbook is geeks_for_geeks.xlsx.

Following are the steps to merge all the sheets in the selected file: 

Step 1: Press Ctrl + N to create a new file. Go to the Data tab, and click on Get Data. 

Step 2: Go to From File and click on From Workbook.

Step 3: Open file geeks_for_geeks.xlsx. A navigator dialogue box appears.

Step 4: Select the tables you want to transform and merge data from different excel sheets. 

Step 5: A power query editor appears. Go to the mid_sem_1 table.

Step 6: In the home tab, click on Merge Queries. A dialogue-box name Merge appears. 

Step 7: Select the table you want to merge with mid_sem_1. For example, mid_sem_2.

Step 8:  Select the column that is common in both tables. For example, Roll No. is the attribute that is common in both tables. Click Ok.

Step 9: A new column in table mid_sem_1 is added. The name of the attribute is mid_sem_2.

Step 10: Click on the double-sided arrow and select the columns you want to add to this table. Uncheck the box Use the original column name as a prefix to have the original column names in your table. 

Step 11: The mid sem 2 marks get appended in your table name mid_sem_1. 

Step 12: Repeat step 6. Go to the home tab and click on Merge Queries. A Merge dialogue box appears. Select the table name end_sem. Now, select the column which is common in the mid_sem_1 and end_sem table. Click Ok.

Step 13: A column name end_sem appears in mid_sem_1 table. Click on the double-sided arrow. Select the column you want to add to the merged table. For example, End sem marks. Uncheck the box Use the original column name as a prefix. Click Ok.

Step 14: A new column name End sem Marks is appended in the table name mid_sem_1. 

Step 15: Sort the Roll No. in ascending order. 

Step 16: The table is ready. In the home tab, click on Close & Load.

Step 17: The merged table is created in sheet name mid_sem_1. We can see that there is a total of 4 worksheets. The 3 worksheets are redundant. Delete all the worksheets except the sheet name mid_sem_1. Rename the sheet name from mid_sem_1 to final_result.

Step 18: The final merged table appears. 

Файлы к уроку:

• Для спонсоров Boosty
• Для спонсоров VK

Ссылки:

• Страница курса
• Плейлист YouTube
• Плейлист ВК

Описание

В этом уроке мы научимся объединять все таблицы одной книги Excel друг за другам, т. е. по вертикали с помощью Power Query.

В этом уроке вы узнаете/повторите:

• Функция Excel.CurrentWorkbook
• Как объединить все таблицы из одного файла Excel друга за другом по вертикали
• Разберем некоторые тонкости объединения/консолидации/комбинирования таблиц одной книги Excel друг за другом в Power Query

Решение

Решение можно разбить на несколько шагов:

• Подключение к файлу Excel
• В таблице с доступными источниками оставить только табличный столбец с нужными данными
• Развернуть это табличный столбец

Примененные функции

• Excel.CurrentWorkbook
• Table.SelectRows
• Table.SelectColumns
• Table.ExpandTableColumn
• Table.TransformColumnTypes

Код

let
   // Подключаемся к файлу Excel
   source = Excel.CurrentWorkbook(),

   // Выбираем только столбец, в котором находится таблица
   rows_select = Table.SelectRows(source, each ([Name] <> "Добавлено")),
   cols_select = Table.SelectColumns(rows_select, {"Content"}),

   // Разворачиваем табличный столбец и указываем типы данных
   table_expand = Table.ExpandTableColumn(
      cols_select,
      "Content",
      {
         "Дата",
         "Агент",
         "ФИО",
         "Город",
         "ТУ",
         "Категория льготы",
         "Контакты#(lf)льготника",
         "ГБУЗ"
      },
      {
         "Дата",
         "Агент",
         "ФИО",
         "Город",
         "ТУ",
         "Категория льготы",
         "Контакты#(lf)льготника",
         "ГБУЗ"
      }
   ),
   types = Table.TransformColumnTypes(
      table_expand,
      {
         {"Дата", type date},
         {"Агент", Int64.Type},
         {"ФИО", type text},
         {"Город", type text},
         {"ТУ", Int64.Type},
         {"Категория льготы", type text},
         {"Контакты#(lf)льготника", type text},
         {"ГБУЗ", type text}
      }
   )
in
   types

Этот урок входит в Базовый курс Power Query