Applies ToExcel для Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

Використовуйте надбудову Power Query, щоб об'єднати кілька файлів з однією схемою, що зберігається в одній папці, в одну таблицю. Наприклад, щомісяця потрібно об'єднувати бюджетні книги з кількох відділів, у яких стовпці однакові, але кількість рядків і значень у кожній книзі відрізняється. Налаштувавши її, ви можете застосовувати додаткові перетворення так само, як і з будь-яким джерелом даних, що імпортується, а потім оновлювати дані , щоб переглядати результати за кожен місяць.  

Концептуальний огляд об'єднання файлів папок

Примітка.    У цій статті описано, як об'єднувати файли з папки. Також можна об'єднувати файли, які зберігаються в SharePoint, сховищі BLOB-об'єктів Azure і сховищі Azure Data Lake. Процес схожий.

Не заскладніть цю функцію.

  • Переконайтеся, що всі файли, які потрібно об'єднати, містяться в виділеній папці без зайвих файлів. В іншому разі всі файли в папці та вкладені папки додаються до даних, які потрібно об'єднати.

  • Кожен файл має мати однакову схему з узгодженими заголовками стовпців, типами даних і кількістю стовпців. Стовпці не обов'язково мають збігатися з іменами стовпців.

  • Якщо це можливо, уникайте непов'язаних об'єктів даних для джерел даних, які можуть містити кілька об'єктів даних, наприклад JSON-файл, книгу Excel або базу даних Access.

Кожен із цих файлів має простий шаблон, який містить лише одну таблицю даних у кожному файлі.

  1. Виберіть пункт Дані > Отримати дані > з файлу > з папки. Відкриється діалогове вікно Огляд .

  2. Знайдіть папку з файлами, які потрібно об'єднати.

  3. Список файлів у папці з'явиться в діалоговому вікні> шлях до папки <. Переконайтеся, що всі потрібні файли наведено в списку.Приклад діалогового вікна імпорту тексту

  4. Виберіть одну з команд у нижній частині діалогового вікна, наприклад Об'єднати > Об'єднати & Завантажити. У розділі Про всі ці команди описано додаткові команди.

  5. Якщо вибрати будь-яку команду Об'єднати , відкриється діалогове вікно Об'єднання файлів . Щоб змінити параметри файлу, виберіть кожен файл у полі Зразок файлу , задайте потрібні параметри "Джерело файлу", "Роздільник" і "Виявлення типів даних ". Також можна встановити або зняти прапорець Пропустити файли з помилками в нижній частині діалогового вікна.

  6. Натисніть кнопку OK.

Результат

Надбудова Power Query автоматично створює запити для об'єднання даних із кожного файлу на аркуш. Створені кроки та стовпці запиту залежать від вибраної команди. Докладні відомості див. в розділі Про всі ці запити.

  1. Виберіть пункт Дані > Отримати дані > з файлу > з папки. Відкриється діалогове вікно Огляд.

  2. Знайдіть папку з файлами, які потрібно об'єднати.

  3. Список файлів у папці з'явиться в діалоговому вікні> шлях до папки <. Переконайтеся, що всі потрібні файли наведено в списку.

  4. Виберіть одну з команд у нижній частині діалогового вікна, наприклад Об'єднати > Об'єднати & Перетворення. У розділі Про всі ці команди описано додаткові команди.Відкриється редактор Power Query.

  5. Стовпець Значення – це стовпець структурованого списку . Клацніть піктограму Розгорнути Піктограма розгортання стовпця , а потім виберіть Розгорнути до нових рядків.  Розгортання списку JSON

  6. Стовпець "Значення" тепер є структурованим стовпцем "Запис ". Клацніть піктограму Розгорнути Піктограма розгортання стовпця . Відкриється діалогове вікно розкривного списку.Розгортання запису JSON

  7. Залишити виділеними всі стовпці. Можна зняти прапорець Використовувати вихідне ім'я стовпця як префікс . Натисніть кнопку OK.

  8. Виділіть усі стовпці, які містять значення даних. На вкладці Основне клацніть стрілку поруч із кнопкою Видалити стовпці, а потім виберіть видалити інші стовпці.

  9. Натисніть кнопку Основне > Закрити & Завантажити.

Результат

Надбудова Power Query автоматично створює запити для об'єднання даних із кожного файлу на аркуш. Створені кроки та стовпці запиту залежать від вибраної команди. Докладні відомості див. в розділі Про всі ці запити.

Кожне з цих джерел даних може мати кілька об'єктів для імпорту. Книга Excel може містити кілька аркушів, таблиць Excel або іменованих діапазонів. База даних Access може містити кілька таблиць і запитів. 

  1. Виберіть пункт Дані > Отримати дані > з файлу > з папки. Відкриється діалогове вікно Огляд.

  2. Знайдіть папку з файлами, які потрібно об'єднати.

  3. Список файлів у папці з'явиться в діалоговому вікні> шлях до папки <. Переконайтеся, що всі потрібні файли наведено в списку.

  4. Виберіть одну з команд у нижній частині діалогового вікна, наприклад Об'єднати > Об'єднати & Завантажити. У розділі Про всі ці команди описано додаткові команди.

  5. У діалоговому вікні Об'єднання файлів виконайте такі дії:

    • У полі Зразок файлу виберіть файл, який використовуватиметься як зразок даних для створення запитів. Не можна виділити об'єкт або виділити лише один об'єкт. Але вибрати кілька елементів не можна.

    • Якщо об'єктів багато, скористайтеся полем пошуку , щоб знайти об'єкт або параметри відображення , а також кнопку Оновити , щоб відфільтрувати список.

    • Установіть або зніміть прапорець Пропустити файли з помилками в нижній частині діалогового вікна.

  6. Натисніть кнопку OK.

Результат

Надбудова Power Query автоматично створює запит на об'єднання даних із кожного файлу на аркуш. Створені кроки та стовпці запиту залежать від вибраної команди. Докладні відомості див. в розділі Про всі ці запити.

Для більшої гнучкості ви можете явно об'єднати файли в редакторі Power Query за допомогою команди Об'єднати файли . Припустімо, що вихідна папка містить кілька типів файлів і вкладених папок, і вам потрібно націлювати певні файли з однаковим типом файлів і схемою, але не іншими. Це може підвищити продуктивність і спростити перетворення.

  1. Виберіть елемент Дані > Отримати > данихіз файлу > з папки. Відкриється діалогове вікно Огляд .

  2. Знайдіть папку з файлами, які потрібно об'єднати, і натисніть кнопку Відкрити.

  3. Список усіх файлів у папці та вкладених папках відображається в діалоговому вікні >шлях до папки< . Переконайтеся, що всі потрібні файли наведено в списку.

  4. Натисніть кнопку Перетворення даних у нижній частині екрана. Відкриється редактор Power Query, у якому відобразяться всі файли в папці та вкладених папках.

  5. Щоб вибрати потрібні файли, відфільтруйте стовпці, наприклад Розширення або Шлях до папки.

  6. Щоб об'єднати файли в одну таблицю, виділіть стовпець Вміст , який містить кожен двійковий файл (зазвичай це перший стовпець), а потім виберіть Основне > Об'єднати файли. Відкриється діалогове вікно Об'єднання файлів .

  7. Надбудова Power Query аналізує за замовчуванням файл прикладу першого файлу в списку, щоб використовувати правильний з'єднувач і визначати відповідні стовпці.Щоб використати інший файл для прикладу файлу, виберіть його з розкривного списку Зразок файлу .

  8. За потреби в нижній частині виберіть пропустити файли з помилкоюs, щоб виключити ці файли з результату.

  9. Натисніть кнопку OK.

Результат

Надбудова Power Query автоматично створює запити для об'єднання даних із кожного файлу на аркуш. Створені кроки та стовпці запиту залежать від вибраної команди. Докладні відомості див. в розділі Про всі ці запити.

Є кілька команд, які можна вибрати, і кожна з них має різні призначення.

  • Об'єднання та перетворення даних    Щоб поєднати всі файли із запитом, а потім запустити редактор Power Query, виберіть об'єднати > об'єднати та перетворити дані.

  • Об'єднати та завантажити   Щоб відкрити діалогове вікно Зразок файлу, створіть запит, а потім завантажте його до аркуша, а потім натисніть кнопку Об'єднати > Об'єднати та завантажити.

  • Об'єднати та завантажити до   Щоб відкрити діалогове вікно Зразок файлу, створіть запит, а потім відобразіть діалогове вікно Імпорт , виберіть пункт Об'єднати > Об'єднати та завантажити до.

  • Навантаження    Щоб створити запит одним кроком, а потім завантажити його до аркуша, натисніть кнопку Завантажити > Завантажити.

  • Завантажити до   Щоб створити запит одним кроком, а потім відкрити діалогове вікно Імпорт , натисніть кнопку Завантажити > Завантажити до.

  • Перетворення данихЩоб створити запит одним кроком, а потім запустити редактор Power Query, натисніть кнопку Перетворити дані.

Проте ви об'єднуєте файли, в області Запити в групі "Довідкові запити" створюються кілька допоміжних запитів.

Список запитів, створених в області "Запити"

  • Надбудова Power Query створює запит "Зразок файлу" на основі прикладу запиту.

  • У запиті функції "Файл перетворення" використовується запит "Параметр1", щоб указати кожен файл (або двійковий файл) як вхідні дані в запиті "Зразок файлу". Цей запит також створює стовпець Вміст , що містить вміст файлу, і автоматично розширює структурований стовпець Запис, щоб додати дані стовпця до результатів. Запити "Файл перетворення" та "Зразок файлу" зв'язані, тому зміни в запиті "Зразок файлу" відображаються в запиті "Файл перетворення".

  • Запит, який містить остаточні результати, міститься в групі "Інші запити". За замовчуванням він називається на честь папки, з якої імпортовано файли.

Для подальшого дослідження клацніть кожен запит правою кнопкою миші та виберіть Редагувати , щоб перевірити кожен крок запиту та побачити, як запити працюють у узгодженому середовищі.

Додаткові відомості

Довідка з надбудови Power Query для Excel

Додавання запитів

Огляд об'єднання файлів (docs.com)

Об'єднання CSV-файлів у надбудові Power Query (docs.com)

Facebook LinkedIn Електронна пошта

Потрібна додаткова довідка?

Потрібні додаткові параметри?

Виявити Спільнота

Ознайомтеся з перевагами передплати, перегляньте навчальні курси, дізнайтесь, як захистити свій пристрій тощо.

Переваги передплати на Microsoft 365

Навчальні матеріали з Microsoft 365

Захисний комплекс Microsoft

Центр спеціальних можливостей

Спільноти допомагають ставити запитання й відповідати на них, надавати відгуки та дізнаватися думки висококваліфікованих експертів.

Запитайте спільноту Microsoft

Спільнота Microsoft Tech

Оцінювачі Windows

Оцінювачі Microsoft 365