Excel power query объединение таблиц
Операция слияния запросов объединяет две существующие таблицы на основе совпадающих значений из одного или нескольких столбцов. Вы можете использовать различные типы соединений в зависимости от требуемых выходных данных.
Объединение запросов
Команду "Объединить запросы" можно найти на вкладке "Главная " в группе "Объединение ". В раскрывающемся меню вы увидите два варианта:
- Запросы на слияние: отображает диалоговое окно слияния с выбранным запросом в качестве левой таблицы операции слияния.
- Запросы на слияние как новые: отображает диалоговое окно слияния без предварительно выбранных таблиц для операции слияния.
Определение таблиц для объединения
Для операции слияния требуется две таблицы:
- Левая таблица для слияния: первый выбор сверху вниз экрана.
- Правая таблица для слияния: второй выбор сверху вниз экрана.
Положение (слева или справа) таблиц становится очень важным при выборе правильного типа соединения для использования.
Выбор пар столбцов
Выбрав левую и правую таблицы, можно выбрать столбцы, которые управляют соединением между таблицами. В приведенном ниже примере есть две таблицы:
- Sales: Поле CountryID является ключом или идентификатором из таблицы "Страны ".
- Страны: эта таблица содержит идентификатор CountryID и название страны.
Диалоговое окно "Слияние" с левой таблицей для объединения с выбранным столбцом Sales и CountryID, а также выбрана таблица Right для объединения в "Страны" и столбца CountryID.
Хотя в этом примере показан один и тот же заголовок столбца для обеих таблиц, это не является обязательным требованием для операции слияния. Заголовки столбцов не должны совпадать между таблицами. Однако важно отметить, что столбцы должны иметь один и тот же тип данных, в противном случае операция слияния может привести к неправильному результату.
Чтобы выполнить соединение, можно также выбрать несколько столбцов, нажав клавиши CTRL при выборе столбцов. При этом порядок выбора столбцов отображается в небольших числах рядом с заголовками столбцов, начиная с 1.
В этом примере у вас есть таблицы Sales и Countries . Каждая из таблиц содержит столбцы CountryID и StateID , которые необходимо связать для соединения между обоими столбцами.
Сначала выберите столбец CountryID в таблице Sales , нажмите клавиши CTRL, а затем выберите столбец StateID . (При этом будут показаны небольшие числа в заголовках столбцов.) Затем выполните те же выборы в таблице "Страны ". На следующем рисунке показан результат выбора этих столбцов.
! [Диалоговое окно "Слияние" с левой таблицей для слияния с набором "Продажи" с выбранными столбцами CountryID и StateID, а также таблица Right для объединения со столбцами CountryID и StateID. Тип соединения имеет значение "Левое внешнее".
Развертывание или статистическая обработка нового объединенного столбца таблицы
После нажатия кнопки "ОК " в диалоговом окне слияния базовая таблица запроса будет содержать все столбцы из левой таблицы. Кроме того, новый столбец будет добавлен с тем же именем, что и у правой таблицы. Этот столбец содержит значения, соответствующие правой таблице на основе строк по строкам.
Здесь можно развернуть или агрегировать поля из этого нового столбца таблицы, которые будут полями из правой таблицы.
Таблица, показывающая столбец объединенных стран справа со всеми строками, содержащими таблицу. Выбран значок развертывания справа от заголовка столбца "Страны", и откроется меню "Развернуть". В меню "Развернуть" выбраны выбранные параметры "Выбрать все", "CountryID", "StateID", "Страна" и "Штат". Также выбрано имя исходного столбца в качестве префикса.
В настоящее время интерфейс Power Query Online предоставляет только операцию развертывания в интерфейсе. Параметр агрегирования будет добавлен в конце этого года.
Типы объединения
Тип соединения указывает, как будет выполняться операция слияния. В следующей таблице описаны доступные типы соединения в Power Query.
Тип соединения | Значок | Описание |
---|---|---|
Левый внешний | Все строки из левой таблицы, соответствующие строки из правой таблицы | |
Правый внешний | Все строки из правой таблицы, соответствующие строки из левой таблицы | |
Полный внешний | Все строки из обеих таблиц | |
Внутренний | Только совпадающие строки из обеих таблиц | |
Левое анти | Только строки из левой таблицы | |
Правый анти | Только строки из правой таблицы |
Нечеткое сопоставление
Вы используете нечеткое слияние для применения нечетких алгоритмов сопоставления при сравнении столбцов, чтобы попытаться найти совпадения между таблицами, которые вы объединяете. Эту функцию можно включить, установив флажок "Использовать нечеткое сопоставление" для выполнения флажка слияния в диалоговом окне "Слияние ". Разверните параметры сопоставления нечетких соответствий , чтобы просмотреть все доступные конфигурации.
Нечеткое сопоставление поддерживается только для операций слияния по текстовым столбцам.
Power Query и Power Pivot — надстройки Excel для легкой работы с тяжелыми файлами. С помощью Power Query можно подключать и обрабатывать источники информации произвольного вида, а в Power Pivot – выполнять сложные расчеты и создавать модель данных.
Надстройка Power Query
Появление Power Query – одно из важных событий в истории развития Excel. То, что раньше пользователи делали в Excel с помощью рутинных операций по преобразованию данных, писали огромные формулы или макросы на VBA, теперь можно выполнять буквально в несколько щелчков мышкой.
Power Query – это надстройка для создания запросов по импорту, очистке и преобразованию данных.
Power Query сам автоматически генерирует и записывает код запросов на языке M .Но это вовсе не значит, что пользователям нужно писать код – интерфейс Power Query очень понятный и позволяет выполнять операции только с помощью мышки. Большинство операций по преобразованию данных в нем можно выполнить, не написав ни одной строчки кода. Все запросы Power Query записываются и сохраняются, поэтому при последующих подключениях повторять операции еще раз не нужно – они выполнятся автоматически после нажатия на кнопку в меню Данные → Обновить.
Как работает
В привычном нам виде Power Query впервые появился в Excel 2013. В Excel 2010 и 2013 надстройка отображается в виде отдельной вкладки «Power Query».
В версии Excel 2019 на вкладке Данные разделы с Power Query — «Получить и преобразовать данные» и «Запросы и подключения».
Power Query — это инструмент для продвинутого бизнес-анализа, предназначенный для подключения к источникам данных и их преобразования.
Несмотря на то, что данные в Excel можно загружать с помощью инструментов Power Pivot , возможностей для преобразования и доступных источников данных в Power Query намного больше и работа с ними проще. Итак, теперь для обработки таблиц и подключения к данным больше не нужны сложные формулы и макросы.
Power Query в меню Excel
В зависимости от того, какая у вас версия Excel, вид надстройки Power Query может выглядеть по-разному. В Excel 2010 и 2013 надстройка появляется в виде отдельной вкладки «Power Query» (если у вас такой вкладки нет, прочитайте, как ее установить ).
Работа с данными в Power Query
Power Query умеет:
- напрямую подключаться к данным в различных источниках;
- очищать данные и выполнять преобразования;
- подготовленные данные выгружать на лист, в сводную таблицу или добавлять в модель данных Excel.
Таким образом, Power Query – это полноценный ETL-инструмент (Extract, Transform, Load).
Подключение к данным в Power Query
В Power Query можно подключать данные из самых разных источников: таблицы в самом файле и других Excel-файлах, текстовые/csv файлы, папки, базы данных, источники в интернете, файлы xml и json, pdf-файлы, данные из канала OData и так далее. А также загрузить данные из Power BI и написать запрос с нуля – Пустой запрос.
Чтобы посмотреть, какие именно источники данных доступны в Excel, перейдите на вкладку Данные → Получить данные (или Создать запрос, если у вас не новая версия Excel).
Для примера добавим в Power Query данные из таблицы.
-
Выделите любую таблицу на листе Excel и перейдите в меню:
— в Excel 2010 и 2013: вкладка Power Query → Из таблицы (или С листа).
— для Excel после 2016: меню Данные → Из таблицы (Из таблицы/диапазона).
В открывшемся окне поставьте галочку «Таблица с заголовками».
Таблица с данными при этом превратится в «умную» smart-таблицу.
- Откроется окно редактора запросов, в котором будет наша таблица. Нажимаем кнопку в меню Главная → Закрыть и загрузить. Готово!
Чтобы открыть список запросов, нажмите в меню Данные → Запросы и подключения. В открывшейся вкладке «Запросы и подключения» отобразится список всех запросов, созданных в файле.
Редактор запросов Power Query
Разберем подробнее интерфейс редактора запросов Power Query.
Если окно редактора у вас закрыто, откройте его в меню Данные → Получить данные → Запустить редактор запросов. Или щелкните 2 раза мышкой по названию запроса на вкладке Запросы и подключения.
Итак, в редакторе Power Query есть:
- Лента редактора запросов для вкладок меню: Главная, Преобразование, Добавить столбец, Просмотр.
- Перечень созданных запросов, который можно свернуть / развернуть.
- Строка формул.
- Название самого запроса.
- Примененные шаги запроса: записанные шаги получения или преобразования данных. Их можно редактировать, выбирая в списке, изменять последовательность шагов, добавлять новые или удалять.
- Область предварительного просмотра, в которой выводится результат преобразования данных для каждого шага.
- Меню для данных, которое открывается при нажатии правой кнопкой мышки.
- При выборе правой кнопкой мыши названия шага появляется его контекстное меню.
Преобразование данных
Посмотрим на простом примере, как преобразовать данные в Power Query.
Допустим, у нас есть таблица с выручкой и расходами по городам за несколько лет. В таблице эти показатели разделены на две группы. Столбец с городами тоже имеет группировки (смотрите рисунок).
Если вы знакомы со сводными таблицами, то знаете, что построить сводную на основе таких данных не получится. Привести их в «нужный вид» можно в Power Query буквально за несколько щелчков мышкой:
- выделите таблицу (можно выделить таблицу целиком или одну из ячеек);
- выберите в меню Данные → Из таблицы (Из таблицы/диапазона);
- в появившемся окне поставьте галочку рядом с «Таблица с заголовками» → ОК;
- в открывшемся редакторе запросов выделите столбцы «показатель» и «город», нажав мышкой на названия столбцов с зажатым Ctrl;
- в меню нажмите Преобразование → Заполнить → Вниз.
При создании запроса Power Query сам автоматически записывает его шаги. Их можно увидеть в области справа Параметры запроса → Примененные шаги.
Шаги запроса можно редактировать, выбирая мышкой (таблица в области предварительного просмотра при этом тоже изменится). Ненужные шаги удаляются при нажатии на «крестик». Можно добавлять новые шаги в середину запроса или менять их местами, перемещая мышкой.
Обработка данных в Power Query выполняется последовательно, шаг за шагом, и каждое последующее действие использует результаты предыдущего. Поэтому при добавлении новых шагов или изменении их последовательности обязательно проверьте, все ли в порядке со следующими операциями. Проверить, все ли в порядке, можно, нажав на самый нижний шаг.
Кроме простых операций с данными, 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. Первая опция позволяет объединять таблицы, исключая строки-дубли и проводя иные интеллектуальные операции с данными. Вторая опция просто добавляет каждый последующий набор в конец существующего. Пиктограммы довольно неплохо иллюстрируют это. Настоящая заметка посвящена второй опции.
Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.
Одна из классических проблем, которую решают профессионалам Excel – это объединение двух таблиц данных, и последующее создание сводной таблицы. Как правило, обработка основывается на функциях ВПР() или ИНДЕКС(ПОИСКПОЗ()). Power Query представил еще один относительно простой метод объединения двух таблиц. Предположим, вы хотите объединить две Таблицы, расположенные на листе Excel:
Рис. 9.1. Исходные Таблицы на листе Excel
Создание запроса-подключения
Чтобы объединить запросы, они должны существовать. Наличие Таблицы в Excel недостаточно; Power Query должен распознавать данные как запрос. Откройте файл Merge.xlsx. Чтобы загрузить таблицу Inventory в Power Query, щелкните любую ячейку в ней, пройдите по меню Данные –> Из таблицы/диапазона. Таблица будет импортирована в Power Query и отобразится в окне редактора. На вкладке Главная щелкните раскрывающийся список в нижней части кнопки Закрыть и загрузить. Выберите Закрыть и загрузить в… –> Только создать подключение.
Запрос отображается в области Запросы и подключения книги 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. На основе этого примера вы можете увидеть, что нужно быть осторожным при создании слияний, чтобы не попасться в ловушку многие-ко-многим.
Читайте также: