Объединение данных из нескольких книг в Excel (с помощью Power Query)

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

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

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

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

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

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

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

Каждая книга содержит данные в таблице 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, но все имена листов (из которых вы хотите объединить данные) одинаковы, вы можете использовать метод, показанный в этом разделе.

Есть несколько вещей, с которыми нужно быть осторожными, когда это просто табличные данные, а не таблица 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.