Як об’єднати дані з декількох книг Excel. Скажімо, у вас є папка, повна файлів Excel. На кожному є один робочий аркуш із різною кількістю рядків. Ви можете імпортувати всі файли в цій папці до однієї книги Excel.
Переглянути відео
- Повторне відвідування чистого обробки даних за допомогою подкасту Power Query № 2037
- Тепер Power Query може поєднувати всі файли Excel у папці.
- Покращено: вони автоматично видаляють заголовки з усіх файлів, крім першого.
- Ви обираєте, який файл використовувати як зразок.
- Виберіть робочий аркуш, таблицю, іменований діапазон для імпорту
- Використовуйте редактор запитів для будь-яких перетворень
- Закрити та завантажити, щоб об’єднати всі файли
- Пізніше оновіть запит, щоб оновити його
Стенограма відео
Дізнайтеся Excel з подкасту, серія 2077: Об’єднайте книги
Привіт, ласкаво просимо назад до трансляції, я Білл Джелен. Буквально 40 серій тому ми говорили про очищення даних за допомогою Power Query, як взяти цілу купу CSV-файлів і перенести їх в один файл Excel. Ну ця функція щойно стала значно кращою.
Отже, я маю тут сьогодні папку, і в цій папці є ціла серія щомісячних файлів Excel, 22 з яких протягом майже двох років. Мені потрібно об’єднати всі ці файли. Тепер усі вони мають однаковий макет стовпців, добре? Тож усі вони мають 9 стовпців, усі мають однакові заголовки і так далі, добре. Отже, ми збираємось поєднати все це без макросу.
Якщо ви перебуваєте в Excel 2016, ви збираєтесь перейти на вкладку Дані, щоб отримати та перетворити дані. Якщо ви працюєте в Excel 2010 або 2013, ви збираєтеся завантажити чудовий продукт від Microsoft під назвою Power Query. Ви можете завантажити все це і встановити його менш ніж за хвилину, добре.
Отже, ми збираємось приїжджати сюди, щоб отримати дані із файлу, але не з однієї книги. Ми хочемо отримати його з такої папки. Гаразд, ось вони. Вони просять вас переглянути папку, але я просто натисну туди і вставте Ctrl + V, тому що я вставив каталог, натисніть OK. Тому вони показали мені попередній перегляд даних, які я маю. Я відкриваю цей маленький випадаючий список Combine і вибираю Combine & Edit. Добре, і тоді крок тут полягає в тому, що ми збираємось вибрати один файл, який є хорошим репрезентативним файлом, добре, і вони просто вибирають перший, який трапляється в алфавітному порядку. Не має значення, яку з них ви оберете. А потім цей Зразок запиту, який ми створюємо з першого вибраного нами файлу, у цьому випадку я хочу Sheet1.Якщо у вас кілька аркушів, ви можете сказати Аркуш2 або аркуш, що називається Звіт про доходи, Діапазон імен або таблицю. І вони показали мені, як це виглядає, і я натискаю кнопку ОК.
Зараз на цьому екрані багато що відбувається. Те, що вони насправді зробили тут, - це те, що вони створили зразок запиту, ахх, зразок запиту, а потім створили його як функцію та запустили цю функцію щодо всього, що знаходиться в папці. Вони додають новий стовпець, який повідомляє мені, звідки взявся файл, і в цьому конкретному випадку я вже знаю, звідки він взявся, тому що у нас тут вказана дата. Отже, мені це не потрібно, я збираюся клацнути правою кнопкою миші та видалити це. І, можливо, там є якісь дані, які мені не потрібні. Можливо, ці дані будуть надходити до замовника, тому я не хочу показувати прибуток, щоб я міг видалити цей стовпець і навіть видалити стовпець Витрати, і, можливо, є деякі надокучливі речі, наприклад, ви знаєте, вони використовували погані заголовки, і я хочу перейменувати ці заголовки. Тож клацніть правою кнопкою миші та перейменуйте, ми назвемо це QTY для кількості.Можливо, все в неправильній послідовності, можливо, я хочу взяти цей предмет і перенести його в інше місце, добре? Просто всі ці маленькі дратівливі речі, які у вас можуть бути, і, звичайно, тут вони виконують усі застосовані кроки, а потім ви можете продовжувати робити будь-які речі Power Query, які вам потрібно зробити. Але в цьому випадку це просто просто. Я просто хочу отримати дані. Тож я збираюся Закрити та Завантажити, пройде кілька секунд, і ось воно. У мене свій фінал. Давайте просто відсортуємо це тут за датою; Ви бачите, ми йдемо з січня 2018 року аж до жовтня 2019 року. Як круто це.повторивши всі застосовані кроки, а потім ви зможете продовжувати виконувати будь-які справи Power Query, які вам потрібно зробити. Але в цьому випадку це просто просто. Я просто хочу отримати дані. Тож я збираюся Закрити та Завантажити, пройде кілька секунд, і ось воно. У мене свій фінал. Давайте просто відсортуємо це тут за датою; Ви бачите, ми йдемо з січня 2018 року аж до жовтня 2019 року. Як круто це.повторивши всі застосовані кроки, а потім ви зможете продовжувати виконувати будь-які справи Power Query, які вам потрібно зробити. Але в цьому випадку це просто просто. Я просто хочу отримати дані. Тож я збираюся Закрити та Завантажити, пройде кілька секунд, і ось воно. У мене свій фінал. Давайте просто відсортуємо це тут за датою; Ви бачите, ми йдемо з січня 2018 року аж до жовтня 2019 року. Як круто це.
Тепер ось дивовижна, дивовижна річ у цьому. Скажімо, щось змінюється в нашій папці. Отже, тут, у файлах місяця, я отримую два нових файли, Вставити, і тепер тут є ще два записи, ще два файли Excel, які я повинен поєднати. Просто поверніться до мого файлу Excel, який наразі має 516 рядків, знайдіть MonthFiles і натисніть кнопку Оновити. І це швидко, я тепер маю нові записи з листопада та грудня. Power Query - це дивовижний новий інструмент, безкоштовний для Excel 2010, безкоштовний для Excel 2013. Це було прекрасно, коли вони могли поєднувати файли CSV, але тепер вони також можуть поєднувати файли Excel, просто феноменально.
Отже, я оновив свою книгу, Power Excel with, 2017 Edition, включає Power Query, Power BI та всі нові інструменти. Перевір. Клацніть на "i" у верхньому правому куті.
Добре, підведення підсумків цього епізоду. Ми переглядаємо «Чисті дані» за допомогою подкасту Power Query № 2037, де ми поєднуємо кілька файлів CSV. Тепер ми можемо використовувати той самий інструмент, вдосконалену версію цього інструменту, для об’єднання всіх файлів Excel у папці. Ще одне вдосконалення, зміна з 2037 року, вони автоматично видаляють заголовки з усіх файлів, крім першого, і просувають ці заголовки у перший файл. Ви обираєте, який файл використовувати як зразок. У моєму випадку я використовував січень. Робота - Виберіть робочий аркуш, таблицю чи іменований діапазон для імпорту, а потім виконайте будь-які перетворення в редакторі запитів. Клацнемо Закрити та завантажити, він об’єднає всі файли. А потім, якщо в папку потрапляє більше файлів, або якісь файли вилучаються або деякі файли змінюються, просто оновіть запит, і він знову зробить усі ці дії. Power Query,абсолютно дивовижний інструмент.
Я хочу подякувати вам, що завітали. Ми побачимось наступного разу для чергової трансляції від.
Завантажити файл
Завантажте зразок файлу тут: Podcast2077.xlsm
