Как объединить данные из нескольких книг 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. Они показали мне предварительный просмотр имеющихся у меня данных. Я открываю это небольшое раскрывающееся меню «Объединить» и выбираю «Объединить и редактировать». Хорошо, а затем шаг здесь заключается в том, что мы собираемся выбрать один файл, который является хорошим репрезентативным файлом, хорошо, а они просто выбирают первый, который находится в алфавитном порядке. Неважно, какой из них вы выберете. И затем этот пример запроса, который мы создаем из первого файла, который мы выбираем, в данном случае мне нужен Sheet1.Если у вас несколько листов, вы можете сказать Sheet2, или лист под названием Income Statement, или Name Range, или таблицу. Мне показали, как это выглядит, и я нажимаю ОК.
На этом экране много всего происходит. На самом деле они здесь создали Sample Query, ahh Sample Query, а затем создали его как функцию и запустили эту функцию для всего в папке. Они добавляют новый столбец, который сообщает мне, откуда пришел файл, и в этом конкретном случае я уже знаю, откуда он, потому что здесь указана дата. Так что мне это не нужно, я собираюсь щелкнуть правой кнопкой мыши и удалить это. И, возможно, там есть какие-то данные, которые мне не нужны. Возможно, эти данные будут отправлены клиенту, поэтому я не хочу показывать прибыль, поэтому я могу удалить этот столбец и даже столбец затрат, и, возможно, есть некоторые раздражающие вещи, например, они использовали плохие заголовки, а я хотите переименовать эти заголовки. Итак, щелкните правой кнопкой мыши и переименуйте, мы назовем это 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 с версией 2017, включая Power Query, Power BI и все новые инструменты. Проверить это. Щелкните букву «i» в правом верхнем углу.
Хорошо, подводим итоги этого эпизода: мы возвращаемся к чистым данным с подкастом Power Query №2037, в котором мы объединяем несколько файлов CSV. Теперь мы можем использовать тот же инструмент, улучшенную версию этого инструмента, чтобы объединить все файлы Excel в папке. Еще одно улучшение, изменение с 2037 года: они автоматически удаляют заголовки из всех файлов, кроме первого, и продвигают эти заголовки в первый файл. Вы выбираете, какой файл использовать в качестве файла примера. В моем случае я использовал январь. Работа - выберите, какой лист, таблицу или именованный диапазон импортировать, а затем выполните любые преобразования в редакторе запросов. Нажимаем Close & Load, он объединит все файлы. А затем, если вы добавили больше файлов в папку или некоторые файлы были удалены или некоторые файлы изменены, просто обновите запрос, и он снова выполнит все эти шаги. Power Query,совершенно потрясающий инструмент.
Я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз на другой сетевой трансляции от.
Скачать файл
Загрузите образец файла здесь: Podcast2077.xlsm
