Как объединить все листы в 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

Power Query может оказаться большим подспорьем, если вы хотите объединить несколько книг в одну.

Например, предположим, что у вас есть данные о продажах для разных регионов (Восток, Запад, Север и Юг). Вы можете объединить эти данные из разных книг в один лист с помощью Power Query.

Если у вас есть эти книги в разных местах / папках, рекомендуется переместить все их в одну папку (или создать копию и поместить эту копию книги в ту же папку).

Итак, для начала у меня есть четыре книги в папке (как показано ниже).

В этом руководстве я рассмотрю три сценария, в которых вы можете объединить данные из разных книг с помощью Power Query:

• Каждая книга содержит данные в таблице Excel, и все имена таблиц одинаковы.
• В каждой книге есть данные с одинаковым именем листа. Это может быть в том случае, если во всех книгах есть лист с именем «сводка» или «данные», и вы хотите объединить все это.
• В каждой книге много листов и таблиц, и вы хотите объединить определенные таблицы / листы. Этот метод также может быть полезен, если вы хотите объединить таблицы / листы, у которых нет согласованного имени.

Давайте посмотрим, как объединить данные из этих книг в каждом случае.

Приведенный ниже метод будет работать, если ваши таблицы Excel имеют одинаковую структуру (те же имена столбцов).

Количество строк в каждой таблице может быть разным.

Не беспокойтесь, если в некоторых таблицах Excel есть дополнительные столбцы. Вы можете выбрать одну из таблиц в качестве шаблона (или в качестве «ключа», как его называет Power Query), и Power Query будет использовать ее для объединения с ней всех других таблиц Excel.

Если в других таблицах есть дополнительные столбцы, они будут проигнорированы, и будут объединены только те, которые указаны в шаблоне / ключе. Например, если выбранная таблица шаблона / ключа имеет 5 столбцов, а одна из таблиц в какой-либо другой книге имеет 2 дополнительных столбца, эти дополнительные столбцы будут проигнорированы.

Теперь у меня есть четыре книги в папке, которые я хочу объединить.

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

Вот шаги, чтобы объединить данные из этих книг в одну книгу (в виде единой таблицы).

1. Перейдите на вкладку «Данные».
2. В группе «Получить и преобразовать» щелкните раскрывающееся меню «Новый запрос».
3. Наведите курсор на «Из файла» и нажмите «Из папки».
4. В диалоговом окне «Папка» введите путь к папке, в которой находятся файлы, или нажмите «Обзор» и найдите папку.
5. Щелкните ОК.
6. В открывшемся диалоговом окне нажмите кнопку объединения.
7. Щелкните «Объединить и загрузить».
8. В открывшемся диалоговом окне «Объединить файлы» выберите таблицу на левой панели. Обратите внимание, что Power Query показывает вам таблицу из первого файла. Этот файл будет действовать как шаблон (или ключ) для объединения других файлов. Power Query теперь будет искать «Таблицу 1» в других книгах и объединять ее с этой.
9. Щелкните ОК.

Это загрузит окончательный результат (объединенные данные) в ваш активный рабочий лист.

Обратите внимание, что вместе с данными Power Query автоматически добавляет имя книги в качестве первого столбца объединенных данных. Это помогает отслеживать, какие данные были получены из какой книги.

Если вы хотите сначала отредактировать данные перед загрузкой в ​​Excel, на шаге 6 выберите «Объединить и отредактировать». Это откроет окончательный результат в редакторе Power Query, где вы сможете редактировать данные.

Несколько вещей, которые нужно знать:

• Если вы выберете таблицу Excel в качестве шаблона (на шаге 7), Power Query будет использовать имена столбцов в этой таблице для объединения данных из других таблиц. Если в других таблицах есть дополнительные столбцы, они будут проигнорированы. Если в этих других таблицах нет столбца, который есть в вашей таблице шаблонов, Power Query просто поместит для него «null».
• Столбцы необязательно располагать в том же порядке, в котором Power Query использует заголовки столбцов для сопоставления столбцов.
• Поскольку вы выбрали Table1 в качестве ключа, Power Query будет искать Table1 во всех книгах и объединять все это. Если он не найдет таблицу Excel с таким же именем (Таблица1 в этом примере), Power Query выдаст ошибку.

Добавление новых файлов в папку

Теперь давайте займемся минутой и поймем, что мы сделали с вышеуказанными шагами (что заняло у нас всего несколько секунд).

Мы объединили данные из четырех разных книг в одну таблицу за несколько секунд, даже не открывая никаких книг.

Но это не все.

Настоящая МОЩНОСТЬ Power Query заключается в том, что теперь, когда вы добавляете больше файлов в папку, вам не нужно повторять ни один из этих шагов.

Все, что вам нужно сделать, — переместить новую книгу в папку, обновить запрос, и он автоматически объединит данные из всех книг в этой папке.

Например, в приведенном выше примере, если я добавлю новую книгу — ‘Mid-West.xlsx’ в папку и обновите запрос, он сразу же предоставит мне новый комбинированный набор данных.

Вот как обновить запрос:

В каждой книге есть данные с одинаковым именем листа.

Если у вас нет данных в таблице Excel, но все имена листов (из которых вы хотите объединить данные) одинаковы, вы можете использовать метод, показанный в этом разделе.

Есть несколько вещей, с которыми нужно быть осторожными, когда это просто табличные данные, а не таблица Excel.

• Имена рабочих листов должны быть такими же. Это поможет Power Query просмотреть ваши книги и объединить данные из листов с одинаковыми именами в каждой книге.
• Power Query чувствителен к регистру. Это означает, что таблицы с именами «данные» и «данные» считаются разными. Точно так же столбец с заголовком «Магазин» и столбец с заголовком «Магазин» считаются разными.
• Несмотря на то, что заголовки столбцов должны совпадать, не обязательно иметь одинаковый порядок. Если столбец 2 в «East.xlsx» является столбцом 4 в «West.xlsx», Power Query будет правильно сопоставить его, сопоставив заголовки.

Теперь давайте посмотрим, как быстро объединить данные из разных книг, у которых имя листа совпадает.

В этом примере у меня есть папка с четырьмя файлами.

В каждой книге у меня есть рабочий лист с названием «Данные», который содержит данные в следующем формате (обратите внимание, что это не таблица Excel).

Вот шаги, чтобы объединить данные из нескольких книг в один рабочий лист:

1. Перейдите на вкладку «Данные».
2. В группе «Получить и преобразовать» щелкните раскрывающееся меню «Новый запрос».
3. Наведите курсор на «Из файла» и нажмите «Из папки».
4. В диалоговом окне «Папка» введите путь к папке, в которой находятся файлы, или нажмите «Обзор» и найдите папку.
5. Щелкните ОК.
6. В открывшемся диалоговом окне нажмите кнопку объединения.
7. Щелкните «Объединить и загрузить».
8. В открывшемся диалоговом окне «Объединить файлы» выберите «Данные» на левой панели. Обратите внимание, что Power Query показывает имя рабочего листа из первого файла. Этот файл будет действовать как ключ / шаблон для объединения других файлов. Power Query просмотрит каждую книгу, найдет лист с именем «Данные» и объединит все это.
9. Щелкните ОК. Теперь Power Query просмотрит каждую книгу, найдет в ней лист с именем «Данные», а затем объединит все эти наборы данных.

Это загрузит окончательный результат (объединенные данные) в ваш активный рабочий лист.

Если вы хотите сначала отредактировать данные перед загрузкой в ​​Excel, на шаге 6 выберите «Объединить и отредактировать». Это откроет окончательный результат в редакторе Power Query, где вы сможете редактировать данные.

В каждой книге есть данные с разными именами таблиц или именами листов.

Иногда вы не можете получить структурированные и согласованные данные (например, таблицы с тем же именем или рабочий лист с тем же именем).

Например, предположим, что вы получаете данные от кого-то, кто создал эти наборы данных, но назвал рабочие листы как Восточные данные, Западные данные, Северные данные и Южные данные.

Или, возможно, человек создал таблицы Excel, но с другими именами.

В таких случаях вы все равно можете использовать Power Query, но вам нужно сделать это с помощью пары дополнительных шагов.

1. Перейдите на вкладку «Данные».
2. В группе «Получить и преобразовать» щелкните раскрывающееся меню «Новый запрос».
3. Наведите курсор на «Из файла» и нажмите «Из папки».
4. В диалоговом окне «Папка» введите путь к папке, в которой находятся файлы, или нажмите «Обзор» и найдите папку.
5. Щелкните ОК.
6. В открывшемся диалоговом окне нажмите кнопку «Редактировать». Откроется редактор Power Query, в котором вы увидите подробную информацию обо всех файлах в папке.
7. Удерживая клавишу Control, выберите столбцы «Содержание» и «Имя», щелкните правой кнопкой мыши и выберите «Удалить другие столбцы». Это удалит все остальные столбцы, кроме выбранных.
8. На ленте редактора запросов нажмите «Добавить столбец», а затем нажмите «Пользовательский столбец».
9. В диалоговом окне «Добавить настраиваемый столбец» назовите новый столбец «Импорт данных» и используйте следующую формулу. = Excel.Workbook ([КОНТЕНТ]). Обратите внимание, что в этой формуле учитывается регистр, и вам нужно ввести ее точно так, как я показал здесь.
10. Теперь вы увидите новый столбец, в котором записана таблица. Теперь позвольте мне объяснить, что здесь произошло. Вы предоставили Power Query имена книг, а Power Query извлек такие объекты, как рабочие листы, таблицы и именованные диапазоны из каждой книги (которая на данный момент находится в ячейке таблицы). Вы можете щелкнуть пустое пространство рядом с текстовой таблицей, и вы увидите информацию внизу. В этом случае, поскольку у нас есть только одна таблица и один рабочий лист в каждой книге, вы можете увидеть только две строки.
11. Щелкните значок с двойной стрелкой вверху столбца «Импорт данных».
12. В открывшемся окне данных столбца снимите флажок «Использовать исходный столбец в качестве префикса» и нажмите кнопку «ОК».
13. Теперь вы увидите развернутую таблицу, в которой вы видите по одной строке для каждого объекта в таблице. В этом случае для каждой книги объект листа и объект таблицы указываются отдельно.
14. В столбце «Вид» отфильтруйте список, чтобы отображалась только таблица.
15. Удерживая клавишу Control, выберите столбец «Имя и данные». Теперь щелкните правой кнопкой мыши и удалите все остальные столбцы.
16. В столбце «Данные» щелкните значок с двойной стрелкой в ​​правом верхнем углу заголовка данных.
17. В открывшемся поле данных столбца нажмите ОК. Это объединит данные во всех таблицах и отобразит их в Power Query.
18. Теперь вы можете выполнить любое необходимое преобразование, а затем перейти на вкладку «Главная» и нажать «Закрыть и загрузить».

Теперь позвольте мне попытаться быстро объяснить, что мы здесь сделали. Поскольку имена листов или таблиц не были согласованными, мы использовали формулу = Excel.Workbook для получения всех объектов книг в Power Query. Эти объекты могут включать листы, таблицы и именованные диапазоны. Когда у нас были все объекты из всех файлов, мы отфильтровали их, чтобы рассматривать только таблицы Excel. Затем мы расширили данные в таблицах и объединили все это.

В этом примере мы отфильтровали данные, чтобы использовать только таблицы Excel (на шаге 13). Если вы хотите объединить листы, а не таблицы, вы можете фильтровать листы.

Примечание. Этот метод даст вам объединенные данные, даже если есть несоответствие в именах столбцов. Например, если в East.xlsx у вас есть столбец с ошибкой, вы получите 5 столбцов. Power Query заполнит данные в столбцах, если найдет их, а если не сможет найти столбец, сообщит значение как «null».

Точно так же, если у вас есть дополнительные столбцы на любом из листов таблиц, они будут включены в окончательный результат.

Теперь, если у вас есть больше книг, из которых вам нужно объединить данные, просто скопируйте и вставьте их в папку и обновите Power Query.

По своим наблюдениям, я замечал, что многие мои коллеги — при объединении 2-х и более таблиц в Excel используют методы ручного копирования данных, при соединении таблиц по одинаковым столбцам данных используют функцию ВПР. Это, конечно удобно, когда речь идет о небольших объемах данных. Но, что делать, если Вам необходимо обработать большие массивы данных из разных источников и из большого количества таблиц? В данном случае, Вам подойдет инструмент Power Query.

Как быстро можно объединить 2 и более таблицы в одну путем добавления данных:

1.В программе Excel импортируем данные из 2-х или более источников, например из книги *xlsx.

2. Создаем подключения данных.

3. Объединяем запросы через вкладку: Данные – создать запрос – объединить запросы – Добавить.

Таким образом данные из нескольких таблиц соединяются в одну таблицу буквально за несколько кликов мыши.

С использованием Power Query также можно объединять таблицы в одну через одинаковые столбцы данных, используя путь: Данные – создать запрос – объединить запросы – Объединить. Это аналог инструмента ВПР, но наиболее быстрый, удобный и эффективный.