Недавно я получил вопрос от читателя об объединении нескольких листов в одной книге в один лист.
Я попросил его использовать Power Query для объединения разных листов, но потом понял, что для новичка в Power Query это может быть непросто.
Поэтому я решил написать этот учебник и показать точные шаги по объединению нескольких листов в одну таблицу с помощью Power Query.
Ниже видео, в котором я показываю, как объединить данные из нескольких листов / таблиц с помощью Power Query:
Ниже приведены письменные инструкции о том, как объединить несколько листов (на случай, если вы предпочитаете письменный текст, а не видео).
Примечание. Power Query можно использовать в качестве надстройки в Excel 2010 и 2013, и это встроенная функция, начиная с Excel 2016. В зависимости от вашей версии некоторые изображения могут выглядеть по-другому (в этом руководстве используются снимки из Excel 2016).
Объедините данные из нескольких листов с помощью Power Query
При объединении данных из разных листов с помощью Power Query требуется, чтобы данные были в таблице Excel (или, по крайней мере, в именованных диапазонах). Если данных нет в таблице Excel, показанный здесь метод не будет работать.
Предположим, у вас есть четыре разных листа - Восток, Запад, Север и Юг.
На каждом из этих листов есть данные в таблице Excel, и структура таблицы согласована (т. Е. Заголовки одинаковы).
Нажмите здесь, чтобы загрузить данные и следить за ними.
Такие данные очень легко объединить с помощью Power Query (который очень хорошо работает с данными в таблице Excel).
Чтобы этот метод работал лучше всего, лучше иметь имена для таблиц Excel (работайте и без него, но проще использовать, если таблицы имеют имена).
Я дал таблицам следующие названия: Восточные_данные, запад_данные, север_данные и южные_данные.
Вот шаги, чтобы объединить несколько листов с таблицами Excel с помощью Power Query:
- Перейдите на вкладку "Данные".
- В группе «Получить и преобразовать данные» нажмите «Получить данные».
- Выберите вариант «Из других источников».
- Выберите вариант «Пустой запрос». Откроется редактор Power Query.
- В редакторе запросов введите следующую формулу в строке формул: = Excel.CurrentWorkbook(). Обратите внимание, что формулы Power Query чувствительны к регистру, поэтому вам нужно использовать точную формулу, как указано (иначе вы получите сообщение об ошибке).
- Нажмите клавишу Enter. Это покажет вам все имена таблиц во всей книге (он также покажет вам именованные диапазоны и / или соединения, если они существуют в книге).
- [Необязательный шаг] В этом примере я хочу объединить все таблицы. Если вы хотите объединить только определенные таблицы Excel, вы можете щелкнуть значок раскрывающегося списка в заголовке имени и выбрать те, которые хотите объединить. Точно так же, если у вас есть именованные диапазоны или соединения и вы хотите только объединить таблицы, вы также можете удалить эти именованные диапазоны.
- В ячейке заголовка содержимого щелкните двойную стрелку.
- Выберите столбцы, которые вы хотите объединить. Если вы хотите объединить все столбцы, убедитесь, что установлен флажок (Выбрать все столбцы).
- Снимите флажок «Использовать исходное имя столбца в качестве префикса».
- Щелкните ОК.
Вышеупомянутые шаги объединят данные со всех листов в одну таблицу.
Если вы присмотритесь, вы обнаружите, что последний столбец (крайний правый) имеет имя таблиц Excel (East_Data, West_Data, North_Data и South_Data). Это идентификатор, который сообщает нам, какая запись из какой таблицы Excel пришла. Это также причина, по которой я сказал, что лучше иметь описательные имена для таблиц Excel.
Вот несколько модификаций, которые вы можете внести в объединенные данные в самом Power Query:
- Перетащите столбец «Имя» в начало.
- Удалите «_Data» из столбца имени (чтобы в столбце имени остались Восток, Запад, Север и Юг). Для этого щелкните правой кнопкой мыши заголовок «Имя» и выберите «Заменить значения». В диалоговом окне «Заменить значения» замените _Data пустым.
- Измените столбец данных, чтобы отображались только даты (а не время). Для этого щелкните заголовок столбца «Дата», перейдите на вкладку «Преобразование» и измените тип данных на «Дата».
- Переименуйте запрос в ConsolidatedData.
Теперь, когда у вас есть объединенные данные со всех листов в Power Query, вы можете загрузить их в Excel - как новую таблицу на новом листе.
Сделать это. выполните следующие шаги:
- Щелкните вкладку «Файл».
- Нажмите «Закрыть» и «Загрузить в».
- В диалоговом окне «Импорт данных» выберите параметры «Таблица» и «Новый лист».
- Щелкните ОК.
Вышеупомянутые шаги объединят данные со всех листов и предоставят вам эти объединенные данные на новом листе.
Одна проблема, которую необходимо решить при использовании этого метода
Если вы использовали описанный выше метод для объединения всех таблиц в книге, вы, вероятно, столкнетесь с проблемой.
Смотрите количество строк объединенных данных - 1304 (что верно).
Теперь, если я обновлю запрос, количество строк изменится на 2607. Обновите снова, и оно изменится на 3910.
Вот в чем проблема.
Каждый раз, когда вы обновляете запрос, он добавляет все записи исходных данных к объединенным данным.
Примечание. Вы столкнетесь с этой проблемой только в том случае, если вы использовали Power Query для объединения ВСЕ ТАБЛИЦЫ EXCEL в трудовой книжке. Если вы выбрали определенные таблицы для объединения, вы не столкнетесь с этой проблемой.Давайте разберемся в причине этой проблемы и как это исправить.
Когда вы обновляете запрос, он возвращается и выполняет все шаги, которые мы предприняли для объединения данных.
На шаге, где мы использовали формулу = Excel.CurrentWorkbook (), он дал нам список всех таблиц. В первый раз это сработало нормально, так как было всего четыре стола.
Но когда вы обновляете, в книге появляется пять таблиц, включая новую таблицу, которую вставил Power Query, в которую у нас есть объединенные данные.
Таким образом, каждый раз, когда вы обновляете запрос, помимо четырех таблиц Excel, которые мы хотим объединить, он также добавляет существующую таблицу запроса к полученным данным.
Это называется рекурсией.
Вот как решить эту проблему.
После того, как вы вставите = Excel.CurrentWorkbook () в строку формул Power Query и нажмете клавишу ВВОД, вы получите список таблиц Excel. Чтобы убедиться, что вы можете объединить только таблицы из рабочего листа, вам нужно каким-то образом отфильтровать только те таблицы, которые вы хотите объединить, и удалить все остальное.
Вот шаги, чтобы убедиться, что у вас есть только необходимые таблицы:
- Щелкните раскрывающийся список и наведите курсор на Текстовые фильтры.
- Нажмите на опцию Содержит.
- В диалоговом окне «Фильтр строк» введите _Data в поле рядом с параметром «содержит».
- Щелкните ОК.
Вы можете не увидеть никаких изменений в данных, но это предотвратит повторное добавление результирующей таблицы при обновлении запроса.
Обратите внимание, что в приведенных выше шагах мы использовали "_Данные», Чтобы отфильтровать так, как мы назвали таблицы таким образом. Но что, если ваши таблицы не имеют одинакового названия. Что делать, если все имена таблиц случайны и не имеют ничего общего.
Вот способ решить эту проблему - используйте фильтр «не равно» и введите имя запроса (в нашем примере это ConsolidatedData). Это гарантирует, что все останется прежним, а результирующая таблица запросов будет отфильтрована.
Помимо того факта, что Power Query значительно упрощает весь этот процесс объединения данных из разных листов (или даже одного листа), есть еще одно преимущество его использования в том, что он делает его динамичным. Если вы добавите больше записей в любую из таблиц и обновите Power Query, он автоматически предоставит вам объединенные данные.Важное примечание: в примере, использованном в этом руководстве, заголовки были такими же. Если заголовки разные, Power Query объединит и создаст все столбцы в новой таблице. Если данные доступны для этого столбца, они будут показаны, иначе будет отображаться значение NULL.
Вам также могут понравиться следующие руководства по Power Query:
- Объедините данные из нескольких книг в Excel (с помощью Power Query).
- Как отключить данные в Excel с помощью Power Query (также известного как Get & Transform)
- Получение списка имен файлов из папок и подпапок (с помощью Power Query)
- Объедините таблицы в Excel с помощью Power Query.
- Как сравнить два листа / файла Excel
