С Power Query стало проще работать с данными, распределенными по листам или даже книгам.
Одна из вещей, где Power Query может сэкономить вам много времени, - это когда вам нужно объединить таблицы с разными размерами и столбцами на основе соответствующего столбца.
Ниже приведено видео, в котором я показываю, как именно объединить таблицы в Excel с помощью Power Query.
Если вы предпочитаете читать текст, а не смотреть видео, ниже приведены письменные инструкции.
Предположим, у вас есть таблица, как показано ниже:
В этой таблице есть данные, которые я хочу использовать, но в ней все еще отсутствуют два важных столбца - «Идентификатор продукта» и «Регион», в котором работает торговый представитель.
Эта информация представлена в виде отдельных таблиц, как показано ниже:
Чтобы собрать всю эту информацию в единую таблицу, вам нужно будет объединить эти три таблицы, чтобы затем вы могли создать сводную таблицу и проанализировать ее или использовать для других целей отчетности / приборной панели.
И под слиянием я не имею в виду простую копипасту.
Вам нужно будет сопоставить соответствующие записи из таблицы 1 с данными из таблиц 2 и 3.
Теперь вы можете положиться на ВПР или ИНДЕКС / ПОИСКПОЗ.
Или, если вы специалист по VBA, вы можете написать для этого код.
Но эти варианты трудоемки и сложны по сравнению с Power Query.
В этом уроке я покажу вам, как объединить эти три таблицы Excel в одну.
Чтобы эта техника работала, вам понадобятся соединительные столбики. Например, в таблицах 1 и 2 общим столбцом является «Товар», а в таблицах 1 и 3 общим столбцом является «Торговый представитель». Также обратите внимание, что в этих соединительных столбцах не должно быть повторений.Примечание. Power Query можно использовать в качестве надстройки в Excel 2010 и 2013, и это встроенная функция, начиная с Excel 2016. В зависимости от вашей версии некоторые изображения могут выглядеть по-другому (в этом руководстве используются снимки из Excel 2016).
Слияние таблиц с помощью Power Query
Я назвал эти таблицы, как показано ниже:
- Таблица 1 - Sales_Data
- Таблица 2 - Pdt_Id
- Таблица 3 - Область
Переименовывать эти таблицы не обязательно, но лучше давать имена, описывающие, о чем таблица.
За один раз вы можете объединить только две таблицы в Power Query.
Итак, нам сначала нужно объединить Таблицу 1 и Таблицу 2, а затем на следующем шаге объединить с ней Таблицу 3.
Объединение таблицы 1 и таблицы 2
Чтобы объединить таблицы, вам сначала нужно преобразовать эти таблицы в соединения в Power Query. Как только у вас будут связи, вы можете легко их объединить.
Вот шаги, чтобы сохранить таблицу Excel как соединение в Power Query:
- Выберите любую ячейку в таблице Sales_Data.
- Щелкните вкладку Данные.
- В группе «Получить и преобразовать» нажмите «Из таблицы / диапазона». Откроется редактор запросов.
- В редакторе запросов перейдите на вкладку «Файл».
- Нажмите на опцию «Закрыть и загрузить в».
- В диалоговом окне «Импорт данных» выберите «Только создать соединение».
- Щелкните ОК.
Вышеупомянутые шаги создадут соединение с именем Sales_Data (или любым именем, которое вы дали таблице Excel).
Повторите вышеуказанные шаги для таблиц 2 и 3.
Итак, когда вы закончите, у вас будет три подключения (с именами Sales_Data, Pdt_Id и Region).
Теперь давайте посмотрим, как объединить таблицы Sales_Data и Pdt_Id.
- Щелкните вкладку "Данные".
- В группе «Получить и преобразовать данные» нажмите «Получить данные».
- В раскрывающемся списке нажмите «Объединить запросы».
- Щелкните "Объединить". Это откроет диалоговое окно слияния.
- В диалоговом окне «Объединение» выберите «Sales_Data» в первом раскрывающемся списке.
- Во втором раскрывающемся списке выберите «Pdt_Id».
- В предварительном просмотре «Sales_Data» щелкните столбец «Товар». При этом будет выбран весь столбец.
- В предварительном просмотре «Pdt_Id» щелкните столбец «Item». При этом будет выбран весь столбец.
- В раскрывающемся списке «Присоединиться к типу» выберите «Левый внешний (все с первого, соответствие со второго)».
- Щелкните ОК.
Вышеупомянутые шаги откроют редактор запросов и покажут вам данные из Sales_Data с одним дополнительным столбцом (Pdt_Id).
Объединение таблиц Excel (таблицы 1 и 2)
Теперь процесс объединения таблиц будет происходить в редакторе запросов со следующими шагами:
- В дополнительном столбце (Pdt_Id) щелкните двунаправленную стрелку в заголовке.
- В открывшемся окне параметров снимите флажки со всех имен столбцов и выберите только элемент. Это потому, что у нас уже есть столбец с названием продукта в существующей таблице, и нам нужен только идентификатор продукта для каждого продукта.
- Снимите флажок «Использовать исходное имя столбца в качестве префикса».
- Щелкните ОК.
Это даст вам результирующую таблицу, в которой есть все записи из таблицы Sales_Data и дополнительный столбец, который также имеет идентификаторы продуктов (из таблицы Pdt_Id).
Теперь, если вы хотите объединить только две таблицы, вы можете загрузить этот Excel, который готов.
Но у нас есть три таблицы, которые нужно объединить, так что работы еще предстоит.
Вам необходимо сохранить эту результирующую таблицу как соединение (чтобы мы могли использовать ее для объединения с таблицей 3).
Вот шаги, чтобы сохранить эту объединенную таблицу (с данными из таблиц Sales_Data и Pdt_Id) в качестве соединения:
- Перейдите на вкладку Файл.
- Нажмите на опцию «Закрыть и загрузить в».
- В диалоговом окне «Импорт данных» выберите «Только создать соединение».
- Щелкните ОК.
Это сохранит недавно объединенные данные как соединение. Вы можете переименовать это соединение, если хотите.
Объединение таблицы 3 с итоговой таблицей
Процесс слияния третьей таблицы с результирующей таблицей (которую мы получили путем слияния таблиц 1 и 2) точно такой же.
Вот шаги, чтобы объединить эти таблицы:
- Щелкните вкладку "Данные".
- В группе «Получить и преобразовать данные» нажмите «Получить данные».
- В раскрывающемся списке нажмите «Объединить запросы».
- Щелкните «Объединить». Это откроет диалоговое окно слияния.
- В диалоговом окне «Объединение» выберите «Объединить1» в первом раскрывающемся списке.
- Во втором раскрывающемся списке выберите «Регион».
- В предварительном просмотре «Merge1» щелкните столбец «Торговый представитель». При этом будет выбран весь столбец.
- В предварительном просмотре региона щелкните столбец «Торговый представитель». При этом будет выбран весь столбец.
- В раскрывающемся списке «Присоединиться к типу» выберите «Левый внешний» (все начиная с первого, соответствующие - со второго).
- Щелкните ОК.
Вышеупомянутые шаги откроют редактор запросов и покажут вам данные из Merge1 с одним дополнительным столбцом (регион).
Теперь процесс объединения таблиц будет происходить в редакторе запросов со следующими шагами:
- В дополнительном столбце («Регион») нажмите на двойную стрелку в заголовке.
- В открывшемся окне параметров снимите флажки со всех имен столбцов и выберите только «Регион».
- Снимите флажок «Использовать исходное имя столбца в качестве префикса».
- Щелкните ОК.
Вышеупомянутые шаги предоставят вам таблицу, в которой объединены все три таблицы (таблица Sales_Data с одним столбцом для Pdt_Id и одним для региона).
Вот шаги, чтобы загрузить эту таблицу в Excel:
- Щелкните вкладку Файл.
- Нажмите «Закрыть и загрузить в».
- В диалоговом окне «Импорт данных» выберите параметры «Таблица» и «Новые листы».
- Щелкните ОК.
Это даст вам объединенную таблицу на новом листе.
Одна из лучших особенностей Power Query заключается в том, что вы можете легко учесть любые изменения в базовых данных (таблицы 1, 2 и 3), просто обновив их.
Например, предположим, что Лауру переводят в Азию, и вы получаете новые данные за следующий месяц. Теперь вам не нужно повторять описанные выше шаги снова. Все, что вам нужно сделать, это обновить таблицу, и она сделает все заново за вас.
Через несколько секунд у вас будет новая объединенная таблица.
Вам также могут понравиться следующие руководства по Power Query:
- Объедините данные из нескольких книг в Excel (с помощью Power Query).
- Объедините данные из нескольких листов в один лист в Excel.
- Как отключить данные в Excel с помощью Power Query (также известного как Get & Transform)
- Получение списка имен файлов из папок и подпапок (с помощью Power Query)
