Excel power query объединение таблиц

Обновлено: 08.07.2024

Операция слияния запросов объединяет две существующие таблицы на основе совпадающих значений из одного или нескольких столбцов. В зависимости от желаемого результата можно использовать различные типы соединений.

Слияние запросов

Команду объединить запросы можно найти на вкладке Главная в группе объединить . В раскрывающемся меню вы увидите два варианта:

Слияние запросов: отображает диалоговое окно Слияние с выбранным запросом в левой таблице операции слияния.
Объединить запросы как новые: отображает диалоговое окно Слияние без предварительно выбранных таблиц для операции слияния.

Поиск таблиц для слияния

Для операции слияния требуются две таблицы:

Левая таблица для объединения: первое выделение, сверху вниз экрана.
Правая таблица для слияния: второй выбор в верхней части экрана.

Позиции — слева или справа — от таблиц становятся очень важными при выборе правильного типа объединения для использования.

Выбор пар столбцов

После выбора и левой, и правой таблиц можно выбрать столбцы, которые будут выполнять соединение между таблицами. В приведенном ниже примере есть две таблицы:

Sales: поле каунтрид является ключом или идентификатором из таблицы « страны ».
Страны: Эта таблица содержит каунтрид и название страны.

Диалоговое окно Слияние с левой таблицей для объединения с набором продаж и выбранным столбцом Каунтрид и правой таблицей для объединения выберите страны и столбец Каунтрид.

Хотя в этом примере показан один и тот же заголовок столбца для обеих таблиц, это не является требованием для операции слияния. Заголовки столбцов не должны совпадать между таблицами. Однако важно отметить, что столбцы должны иметь один и тот же тип данных. в противном случае операция слияния может привести к неправильным результатам.

Можно также выбрать несколько столбцов для выполнения объединения, нажав клавишу CTRL при выборе столбцов. При этом порядок, в котором были выбраны столбцы, отображается небольшими числами рядом с заголовками столбцов, начиная с 1.

В этом примере у вас есть таблицы « продажи » и « страны ». Каждая таблица содержит столбцы каунтрид и StateId , которые необходимо связать с соединением обоих столбцов.

Сначала выберите столбец каунтрид в таблице Sales (продажи ), нажмите клавишу CTRL, а затем выберите столбец StateId . (При этом будут показаны небольшие числа в заголовках столбцов.) Затем выполните те же настройки, что и в таблице « страны ». На следующем рисунке показан результат выбора этих столбцов.

! [Диалоговое окно слияния с левой таблицей для объединения с набором продаж с выбранными столбцами Каунтрид и StateID и правой таблицей для объединения в странах с выбранными столбцами Каунтрид и StateID. Тип объединения установлен в левое внешнее.

Развернуть или объединить новый столбец Объединенной таблицы

После нажатия кнопки ОК в диалоговом окне Слияние базовая таблица запроса будет содержать все столбцы из левой таблицы. Кроме того, будет добавлен новый столбец с тем же именем, что и у правой таблицы. Этот столбец содержит значения, соответствующие правильной таблице, на основе строк.

Здесь можно развернуть или объединить поля из этого нового столбца таблицы, которые будут полями из правой таблицы.

Таблица, показывающая столбец с объединенными странами справа, со всеми строками, содержащими таблицу. Был выбран значок развертывания справа от заголовка столбца страны, а в меню развернуть — открыть. В меню развернуть находятся выбранные параметры выбрать все, Каунтрид, StateID, страна и регион. Также выбирается параметр использовать исходное имя столбца в качестве префикса.

В настоящее время Power Query в Интернете предоставляет только операцию Expand в своем интерфейсе. Параметр для статистической обработки будет добавлен позднее в этом году.

Типы объединения

Тип объединения указывает, как будет выполняться операция слияния. В следующей таблице описаны доступные типы соединений в Power Query.

Тип соединения	Значок	Описание
Левое внешнее		Все строки из левой таблицы, совпадающие строки из правой таблицы
Правое внешнее		Все строки из правой таблицы, совпадающие строки из левой таблицы
Полное внешнее		Все строки из обеих таблиц
Внутреннее		Только совпадающие строки из обеих таблиц
Левое сглаживание		Только строки из левой таблицы
Правая защита		Только строки из правой таблицы

Нечеткое соответствие

Используйте нечеткое слияние для применения алгоритмов нечетких соответствий при сравнении столбцов, чтобы попытаться найти совпадения между объединяемыми таблицами. Эту функцию можно включить, установив флажок использовать нечеткое соответствие для выполнения слияния в диалоговом окне Слияние . Разверните раздел Параметры нечетких соответствий , чтобы просмотреть все доступные конфигурации.

Нечеткие совпадения поддерживаются только для операций слияния над текстовыми столбцами.

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Перед профессионалами Excel часто встают задачи объединения данных из нескольких однотипных таблиц.[1] Power Query может делать это автоматически.

В папке примеров есть три CSV-файла: Jan 2008.csv, Feb 2008.csv и Mar 2008.csv. Начните с импорта первого файла:

Создайте новую книгу Excel
Создайте запрос Данные –> Из текстового/CSV-файла
Выберите файл Jancsv

Рис. 3.1. Импортированный CSV-файл Jan 2008.csv содержит одну ошибку

Power Query импортирует файл и автоматически выполнит следующие действия:

Продвинет первую строку в заголовки.
Задаст типы данных.

Данные всё еще содержат одну ошибку – общие итоги. Вернитесь в редактор Power Query. Выделите столбец Date, кликните Удалить строки –> Удалить ошибки. Нажмите Закрыть и загрузить. Строка с итогами будет удалена.

Повторите операции для импорта Feb 2008.csv и Mar 2008.csv. Когда вы закончите, у вас будет три таблицы в книге Excel, каждая на своем листе. Чтобы объединить таблицы создайте новый запрос. Пройдите по меню Получить данные –> Объединить запросы –> Добавить:

Рис. 3.2. Меню объединения запросов

Откроется диалоговое окно Добавление (рис. 3.3). Доступ к окну Добавление можно получить и из редактора Power Query. Для этого в редакторе перейдите на вкладку Главная и пройдите по меню Добавить в запросы –> Добавить запросы в новый. (рис. 3.4).

Рис. 3.3. Окно Добавление

Рис. 3.4. Доступ к окну Добавление из редактора Power Query

Диалоговое окно Добавление объединяет запросы Power Query, а не таблицы Excel. Упорядочьте запросы в правом окне, чтобы данные располагались последовательно. Нажмите Ok. Power Query создаст новый запрос Append1, который включает один шаг:

Рис. 3.5. Новый объединенный запрос Append1

У вас может возникнуть соблазн прокрутить запрос вниз, чтобы увидеть, все ли ваши записи вошли в него. К сожалению, это займет много времени, так как бегунок работает не так как вы привыкли в Excel. При перемещении вниз новые строки будут подгружаться довольно медленно. Причина в том, что Power Query может использоваться для обработки больших наборов данных. Представьте, что вы подключаетесь к набору данных, из 5 миллионов строк, но хотите вытащить записи только для отдела №150. Power Query осуществляет как бы «предварительный просмотр», который должен дать достаточно информации для определения ключевой структуры данных. Вы выполните преобразования в данных предварительного просмотра и создаёте шаблон. Во время загрузки всех строк Power Query обрабатывает этот шаблон, извлекая только необходимые записи. Это намного эффективнее, чем загрузка всех данных в книгу и последующая обработка каждой строки и столбца.

Но если вы не видить все данные, как вы проверите, что объединенный запрос корректен? Переименуйте запрос Append1 –> Transactions. Кликните Закрыть и загрузить.

Рис. 3.6. Новый запрос суммирует все строки трех запросов

Вы также можете создать сводную таблицу, и убедиться, что Excel корректно объединил запросы:

Рис. 3.7. Сводная таблица на основе данных из запроса Transactions

Объединение запросов с разными заголовками

Ниже показана ситуация, когда пользователь забыл переименовать столбец TranDate в запросе Mar 2008. При объединении запросов Jan 2008 и Mar 2008 получится:

Рис. 3.8. Столбец TranDate, полный нулевых значений в январе, и столбец Date, полный нулевых значений в марте

[1] На самом деле, Power Query поддерживает два типа объединений:

В английском варианте, это Merge Queries и Append Queries. Первая опция позволяет объединять таблицы, исключая строки-дубли и проводя иные интеллектуальные операции с данными. Вторая опция просто добавляет каждый последующий набор в конец существующего. Пиктограммы довольно неплохо иллюстрируют это. Настоящая заметка посвящена второй опции.

Одна из классических проблем, которую решают профессионалам Excel – это объединение двух таблиц данных, и последующее создание сводной таблицы. Как правило, обработка основывается на функциях ВПР() или ИНДЕКС(ПОИСКПОЗ()). Power Query представил еще один относительно простой метод объединения двух таблиц. Предположим, вы хотите объединить две Таблицы, расположенные на листе Excel:

Рис. 9.1. Исходные Таблицы на листе Excel

Создание запроса-подключения

Запрос отображается в области Запросы и подключения книги Excel, но Power Query не создал новую таблицу ни на текущем, ни на новом листе Excel:

Рис. 9.2. Новый запрос, созданный только для подключения к Таблице; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

И наоборот, если вы создали запрос только для подключения, то в дальнейшем можете через этот интерфейс добавить Таблицу на лист Excel.

Теперь импортируйте в Power Query таблицы Sales. Аналогичным образом загрузите ее только создав подключение.

Объединение запросов

Данные –> Получить данные –> Объединить запросы –> Объединить. Откроется диалоговое окно Слияние. Выберите таблицу Sales в верхнем раскрывающемся списке, и таблицу Inventory – в нижнем. Однако, кнопка Ok по-прежнему не подсвечена:

Рис. 9.3. Вы выбрали таблицы, но почему вы не можете продолжить?

Еще раз внимательно прочтите инструкцию, содержащуюся под заголовком окна. Power Query не знает, какие поля вы хотите использовать для выполнения слияния. Вам нужно в каждой таблице выделить столбцы с идентификатором. Причем так, чтобы в одной таблице этот столбец содержал уникальные значения, а в другой таблице значения могут повторяться. Такая связь называется «один ко многим», и ее использование – лучший способ гарантировать, что вы получите результаты, соответствующие вашим ожиданиям.

Power Query также поддерживает соединения один-к-одному и многие-ко-многим.

В нашем примере столбец SKU Number содержит уникальные продукты в таблице Inventory. Столбце SKU Number представлен и в таблице Sales. Здесь значения SKU Number могут повторяться много раз. Используйте этот столбец для связывания таблиц. Щелкните заголовок SKU Number в каждой таблице:

Рис. 9.4. Столбцы для связывания выбраны корректно

Рис. 9.5. Новый (последний) столбец таблицы Sales содержит соответствующие записи таблицы Inventory

Разверните столбец Inventory. Вопрос только в том, какие столбцы таблицы Inventory вам нужны. Итак, щелкните значок развернуть. Снимите галочку со столбцов, которые уже есть в таблице Sales (SKU Number and Brand), снимите флажок Использовать исходное имя столбца как префикс, нажмите Ok. Теперь сведения о продукте объединены с продажами:

Рис. 9.6. Детали из таблицы Inventory объединены с данными таблицы Sales

Переименуйте запрос OneToMany. Главная –> Закрыть и загрузить. На листе Excel отобразится 20 строк Таблицы, как если бы свою работу выполнила функция ВПР:

Рис. 9.7. Слияние на основе связи один-ко-многим

Многие-ко-многим

При выполнении процедуры слияния нужно быть внимательным. Если вы попытаетесь выполнить слияние иным образом, вы обнаружите иной результат:

Данные –> Получить данные –> Объединить запросы –> Объединить.
Выберите Sales в верхней части, а Inventory– в нижней
Щелкните заголовок Brand в каждой таблице
Нажимать Ok
Щелкните значок развернуть
Снимите галочку со столбцов SKU Number and Brand
Снимите флажок Использовать исходное имя столбца как префикс
Нажмите Ok
Переименовать запрос ManyToMany
Главная –> Закрыть и загрузить

Новая процедура слияния отличается двумя аспектами: (1) таблицы Sales и Inventory в окне Слияние переставлены местами, (2) для связи выбран столбец Brand. Тем не менее, в Таблице на листе Excel появилось 22 записи – на 2 больше, чем исходное количество транзакций. Чтобы понять, почему это произошло, вернитесь в редактор Power Query, перейдите к первому шагу запроса ManyToMany (цифра 1 на рис. 9.8). Если вы перейдете к строке 19 и щелкните пробел справа от слова Table (2), вы увидите предварительный просмотр данных в таблице, которые при слиянии будут объединены в таблице Sales (3).

Рис. 9.8. Слияние многие-ко-многим в действии

В предыдущем слиянии вы связали данные на основе столбца SKU Number. В этом примере слияние осуществляется на основе столбца Brand. Однако бренду OK Springs соответствует два артикула в таблице Inventory. На основе этого примера вы можете увидеть, что нужно быть осторожным при создании слияний, чтобы не попасться в ловушку многие-ко-многим.

В предыдущей главе были описаны методы агрегации данных из нескольких .txt или .csv файлов. В настоящей заметке описано, как объединять Таблицы или листы Excel из активной или внешней рабочей книги. К сожалению, стандартный пользовательский интерфейс Power Query этого не умеет. Но дополнительные манипуляции не будут слишком сложными. При этом методы работы с данными в активной книге отличаются от методов извлечения данных из внешнего файла.

Рис. 5.1. Доступные Таблицы в окне редактора Power Query

Объединение таблиц и диапазонов в текущем файле

Создайте пустой запрос: пройдите по меню Данные –> Получить данные –> Из других источников –> Пустой запрос. В окне редактора Power Query в строке формул ведите (рис. 5.1):

Нажмите Enter. Вы увидите доступные объекты текущей книги: Таблицы, именованные диапазоны, подключения к внешним источникам данных. К сожалению, нельзя получить перечень листов файла.

Как вы узнали из главы 4, можно щелкнуть пробел рядом с зелеными словами в столбце Content для предварительного просмотра данных Table (рис. 5.2).

Рис. 5.2. Предварительный просмотр содержимого Table

В главе 4 вы также узнали, что кликнув на двуглавую стрелку в верхней части столбца Content, вы развернете содержимое Таблиц, сохраняя сведения из столбца Name. Итак, кликните двуглавую стрелку, в открывшемся окне настроек снимите флажок Использовать исходное имя столбца как префикс, нажмите Ok:

Рис. 5.3. Импорт и объединение данных с добавлением столбца с именем таблицы; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Преобразуйте имена таблиц в даты (подробнее см. главу 4):

Щелкните правой кнопкой мыши столбец Name –> Замена значений
Заменить символ _ (подчеркивание) на ˽ 1,˽ (пробел, единица, запятая, пробел)
Щелкните правой кнопкой мыши столбец Name –> Тип изменения –> Дата
Перейдите на вкладку Преобразование –> Дата –> Месяц –> Конец месяца
Щелкните правой кнопкой мыши столбец Name –> Переименовать –> Конец месяца
Измените имя запроса на Подарочные сертификаты

Рис. 5.4. Финальный вид запроса

Перейдите на вкладку Главная и кликните Закрыть и загрузить. Объединенные данные будут размещены на новом листе Excel. К сожалению, запрос содержит кучу ошибок:

Рис. 5.5. Данные объединены на листе Excel; правда, вкрались ошибки

Не страшно. Наведите курсор мыши на запрос Подарочные сертификаты, и во всплывающем окне кликните Изменить. Снова откроется редактор Power Query. В области ПРИМЕНЕННЫЕ ШАГИ выберите первый шаг – Источник (цифра 1 на рис. 5.6). Вы заметите, что теперь у вас четыре таблицы в списке. Добавилась таблица Подарочные сертификаты, которая была создана в результате запроса. Чтобы избавиться от дублирования, необходимо добавить фильтрацию таблиц, участвующих в запросе. Кликните стрелочку возле названия столбца Name (2), выберите опцию Текстовые фильтры –> Не содержит. Подтвердите, что вы хотите вставить шаг. В окне Фильтрация строк выберите Не содержит – Подарочные (3):

Рис. 5.6. Фильтрация таблиц по имени

Нажмите Ok. В редакторе Power Query перейдите на вкладку Главная. Кликните кнопку Закрыть и загрузить. Теперь запрос содержит 62 строки; ошибок нет.

Существует и вторая возможность избавиться от ошибок – убрать дубли. Откройте редактор Power Query. Перейдите на шаг Измененный тип. Выберите столбец Name и на вкладке Главная кликните Удалить строки –> Удалить ошибки. Подтвердите, что вы хотите вставить новый шаг в середину запроса. На вкладке Главная кликните Закрыть и загрузить.

Объединение диапазонов и листов

Данные на листах Excel могут располагаться не в Таблицах. Напомню, что Power Query «не видит» листы Excel. Поэтому исходные данные можно организовать в именованные диапазоны. Это можно сделать, например, с помощью определения области печати. Трюк работает потому, что имя области печати является именем динамического диапазона.

Перейдите на лист Jan 2008. На вкладке Разметка страницы кликните Печатать заголовки. На закладке Лист введите A:D в поле Выводить на печать диапазон, кликните Ok.

Рис. 5.7. Выбор области печати

Повторите процедуру для листов Feb 2008 и Mar 2008. Создайте пустой запрос, и в строке формул введите: =Excel.CurrentWorkbook(). Нажмите Enter. Вы увидите список трех таблиц и трех именованных диапазонов:

Рис. 5.8. Объекты книги Excel, доступные для импорта в Power Query

Рис. 5.9. Необработанный рабочий лист

Выполним дополнительную очистку данных:

Главная –> Удалить строки –> Удалить верхние строки –> 2
Главная –> Использовать первую строку в качестве заголовков
Столбец CertNumber –> Фильтр –> снимите флажок c null
Щелкните правой кнопкой мыши столбец CertNumber –> Тип изменения –> Целое число
Выберите столбец CertNumber
Закладка Главная –> Удалить строки –> Удалить ошибки
Выберите столбец CertNumber. Удерживайте нажатой клавишу Shift выберите столбец Service
Щелкните правой кнопкой мыши один из выбранных заголовков столбцов –> Удалить другие столбцы
Измените имя запроса на Все листы
Главная –> Закрытьизагрузить

При работе с областями печати рекомендуется ограничивать область печати необходимыми строками и столбцами. В примере выше мы выбрали целиком столбцы, что привело к импорту в Power Query около 3 млн. строк с трех листов. Наверное, вы заметили, как медленно выполнялись некоторые команды!

Агрегирование данных из других книг

Вам нужно создать список книг Excel и извлечь их содержимое, аналогично тому, что вы сделали в главе 4, когда вы извлекли содержимое файлов CSV.

Создайте новую книгу Excel. Создать новый запрос: Данные –> Получить данные –> Из файла –> Из папки. Выберите папку Source Files. В списке есть как файлы Excel, так и иные файлы:

Рис. 5.10. Файлы, доступные в папке Source Files

Нажмите Преобразовать данные, и отфильтруйте файлы Excel:

Щелкните правой кнопкой мыши столбец Extension –> Преобразование –> нижний регистр
Фильтр столбца Extension –> Текстовые фильтры –> Начинается с… –> .xlsx
Выберите столбцы Content имя Name –> щелкните правой кнопкой мыши –> Удалить другие столбцы

У вас может возникнуть соблазн нажать кнопку Объединить файлы…

Рис. 5.11. Объединить файл

… и, к сожалению, Power Query позволит вам это сделать. Однако, вы обнаружите, что Power Query сделает что-то весьма странное. Чтобы откатить импорт, перейдите в область ПРИМЕНЕННЫЕ ШАГИ и удалите все шаги после шага Другие удаленные столбы.

Раз вы не можете объединить и импортировать файлы простым методом, пойдем трудным способом:

Новый пользовательский столбец содержит все объекты, к которым можно подключиться, включая все Таблицы Excel, именованные диапазоны и даже листы:

Рис. 5.12. Объекты, доступные для импорта

Столбец Пользовательская имеет двуглавую стрелку (значок расширения), поэтому его можно развернуть. Нажав на значок вы получаете список всех объектов трех файлов, доступных для импорта:

Рис. 5.13. Объекты, доступные для импорта

Столбец Kind показывает, что у вас есть Листы, определенное имя и Таблицы. Если не отфильтровать этот перечень объектов, у вас будет много дублей:

Отфильтруйте столбец Kind, оставив только Sheet
Отфильтруйте столбец Name, удалив файл NamedRange.xlsx
Выберите столбцы Name, Name.1 и Data –> щелкните правой кнопкой мыши на заголовке одного из этих столбцов –> Удалить другие столбцы
Кликните кнопку Развернуть у заголовка столбца Data (снимите настройки префикса)

Запрос теперь выглядит следующим образом:

Рис. 5.14. Запрос с шестью импортированными объектами Sheet

Главная –> Использовать первую строку в качестве заголовков
Щелкните правой кнопкой мыши на заголовке столбца Workbookxlsx –> Переименовать –> Source File
Щелкните правой кнопкой мыши заголовке столбца Jan 2008 column –> Переименовать –> Month
Выберите столбец Amount –> Главная –> Удалить строки –> Удалить ошибки
Измените имя запроса FromExcelFiles
Главная –> Закрыть и загрузить

Данные загружаются в Таблицу на листе Excel. На их основе можно создать сводную таблицу, чтобы увидеть, что вы смогли извлечь из внешних файлов Excel:

Рис. 5.15. Сводная позволяет проверить, что же вы импортировали

Видно, что вы успешно извлекли данные из двух Excel-файлов, каждый из которых содержит по три листа. В общей сложности извлекли более 12 000 записей.

Читайте также: