Слияние запросов (Power Query)
При слиянии обычно объединяются два запроса, которые находятся в Excel или из внешнего источника данных. Кроме того, функция слияния имеет интуитивно понятный пользовательский интерфейс, помогающий легко объединить две связанные таблицы. Пример объединения общего объема продаж из запроса сведений о заказе в таблицу продуктов см. в руководстве Об объединении нескольких источников данных.
Внимание: Уровни конфиденциальности не позволяют пользователю непреднамеренно объединять данные из нескольких источников данных, которые могут быть частными или организационными. В зависимости от запроса пользователь может непреднамеренно отправить данные из частного источника данных в другой источник данных, который может быть вредоносным. Power Query анализирует каждый источник данных и классифицирует его по определенному уровню конфиденциальности: общедоступному, организационному и частному. Дополнительные сведения об уровнях конфиденциальности см. в разделе Установка уровней конфиденциальности.
Сведения о слиянии запросов
Запрос слиянием создает новый запрос на основе двух существующих запросов. Один результат запроса содержит все столбцы из первичной таблицы, при этом один столбец выступает в качестве одного столбца, содержащего связь с дополнительной таблицей. Связанная таблица содержит все строки, которые соответствуют каждой строке из главной таблицы на основе общего значения столбца. Операция развертывания добавляет столбцы из связанной таблицы в главную.
Существует два типа операций слияния:
- Встроенное слияние Вы объединяете данные в существующий запрос, пока не достигнете окончательного результата. Результатом является новый шаг в конце текущего запроса.
- Промежуточное слияние Для каждой операции слияния создается новый запрос.
Чтобы просмотреть визуальное представление связей в диалоговом окне Зависимости запросов , выберите Просмотр > зависимостей запросов. В нижней части диалогового окна выберите команду Макет , чтобы управлять ориентацией схемы.
Выполнение слияния
Вам потребуется по крайней мере два запроса, которые можно объединить и которые имеют по крайней мере один или несколько столбцов для сопоставления в операции соединения. Они могут поступать из разных типов внешних источников данных. В следующем примере используются Products и Total Sales.
- Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос >Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
- Выберите Главная >Запросы слияния. По умолчанию выполняется встроенное слияние. Чтобы выполнить промежуточное слияние, щелкните стрелку рядом с командой и выберите Команду Объединить запросы как Новые.
Убедитесь, что выбрано одинаковое количество столбцов для сопоставления в предварительном просмотре основной и связанной или вторичной таблиц. Сравнение столбцов основано на порядке выбора в каждой таблице. Соответствующие столбцы должны иметь один и тот же тип данных, например Text или Number. Можно также выбрать несколько столбцов для слияния.
Левое внешнее соединение Сохраняет все строки из первичной таблицы и возвращает все совпадающие строки из связанной таблицы.
Правое внешнее соединение Сохраняет все строки из связанной таблицы и возвращает все совпадающие строки из основной таблицы.
Полный внешний Возвращает все строки из основной и связанной таблиц.
Левое анти-соединение Возвращает только строки из основной таблицы, в которых нет совпадающих строк из связанной таблицы.
Правое анти-соединение Возвращает только строки из связанной таблицы, в которых нет совпадающих строк из основной таблицы.
Result (Результат)
Разверните столбец Таблица
После операции слияния можно развернуть структурированный столбец Таблица, чтобы добавить столбцы из связанной таблицы в основную таблицу. После развертывания столбца в главной таблице вы можете применить фильтры и другие операции преобразования. Дополнительные сведения см. в статье Работа со структурированным столбцом списка, записи или таблицы.
- В предварительном просмотре данных щелкните значок Развернутьрядом с заголовком столбца NewColumn .
- В раскрывающемся списке Развернуть выберите или очистите столбцы, чтобы отобразить нужные результаты. Чтобы агрегировать значения столбцов, выберите Агрегат.
Объединение запросов и объединение таблиц
В настоящее время данные обобщаются только на уровне продукта. В таблице «Категория» можно скатить продукты на уровне. таким образом, вы можете загрузить таблицу «Категория» и создать для нее соединить поля «Название товара».
- Выберите таблицу «Категории», а затем выберите «Данные»>«&» > «Из таблицы» или «Диапазон».
- Выберите «Закрыть& Загрузить таблицу, чтобы вернуться на лист, а затем переименуем ярлыж листа в «Категории PQ».
- Выберите таблицу «Данные о продажах», откройте Power Query, а затем на домашней>в>объединить запросы >слияние как новые.
- В диалоговом окне «Слияние» под таблицей «Продажи» выберите в списке столбец «Название товара».
- В столбце «Название товара» выберите таблицу «Категория» из списка.
- Чтобы завершить операцию, выберите «ОК».
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
Объединение таблиц c Power Query
По своим наблюдениям, я замечал, что многие мои коллеги — при объединении 2-х и более таблиц в Excel используют методы ручного копирования данных, при соединении таблиц по одинаковым столбцам данных используют функцию ВПР. Это, конечно удобно, когда речь идет о небольших объемах данных. Но, что делать, если Вам необходимо обработать большие массивы данных из разных источников и из большого количества таблиц? В данном случае, Вам подойдет инструмент Power Query.
7.9K открытий
Как быстро можно объединить 2 и более таблицы в одну путем добавления данных:
1.В программе Excel импортируем данные из 2-х или более источников, например из книги *xlsx.
2. Создаем подключения данных.
3. Объединяем запросы через вкладку: Данные – создать запрос – объединить запросы – Добавить.
Таким образом данные из нескольких таблиц соединяются в одну таблицу буквально за несколько кликов мыши.
С использованием Power Query также можно объединять таблицы в одну через одинаковые столбцы данных, используя путь: Данные – создать запрос – объединить запросы – Объединить. Это аналог инструмента ВПР, но наиболее быстрый, удобный и эффективный.
Объединение таблиц в Power Query
Вы работали со сводными таблицами Excel? Если нет – нужно срочно начинать. Это супер-инструмент, который позволяет быстро и просто обрабатывать большие объемы данных. А если вы с ними все-таки работали, то я как экстрасенс-капитан-очевидность могу точно сказать, что вы сталкивались с ситуацией, когда нужно построить сводную таблицу на основе нескольких источников данных. Например, с помощью нескольких одинаковых таблиц Excel, копируя их одну под другой. Или дополняя свою таблицу новыми столбцами и аналитиками.
Добавление или объединение таблиц?
Чаще всего, чтобы объединить данные в Excel, пользователи копируют таблицы одну под другую. Или пишут формулы по типу ВПР, если в таблицу нужно добавить новые столбцы или аналитики. Но вы наверняка знаете, что самый удобный инструмент для объединения данных в Excel – это Power Query. Там есть два принципиальных способа:
- По вертикали – добавление таблицы под таблицу. Полезно, когда таблицы с одинаковой шапкой находятся в разных файлах или на разных листах.
- По горизонтали – слияние таблиц, похожее на ВПР. А здесь есть еще варианты — не только аналогичные ВПР, но и ВПР-наоборот, и ВПР-неВПР… Целых шесть видов объединения таблиц.
Разберем, чем эти способы отличаются друг от друга.
Вариант 1. Добавление таблицы под таблицу
Приём с добавлением таблиц применяется, когда у вас есть несколько таблиц с одинаковой шапкой, но они находятся в разных местах, например на разных листах или в нескольких файлах. Тогда, чтобы построить итоговую сводную, мы можем просто строки одной таблицы добавить в другую. Обращаю внимание, что здесь данные таблиц объединяются как-бы по вертикали.
Как на основе таблиц с одинаковой шапкой построить единую базу? Добавив таблицы одну под другую
Раньше процедуру добавления строк из одной таблицы в другую можно было выполнить с помощью копирования. Отсюда много ручной работы при внесении новых данных (ну, или писали макросы). Однако с момента появления в Excel надстройки Power Query у нас появилась возможность добавлять таблицы одну под другую несколькими щелчками мыши. Как это сделать – смотрите в видео.
Вариант 2. Объединение таблиц
Объединение таблиц применяется, когда у вас есть две таблицы (шапки у них скорее всего разные), и одна таблица дополняет другую. Например, в отчете продажи показаны по городам, а у вас просят вывести информацию по областям. Тогда потребуется объединение по горизонтали:
Одна таблица дополняет другую с помощью объединения по общему столбцу
В «обычном» Excel это делается с помощью формулы ВПР или других формул с похожим функционалом. Power Query предлагает нам больше способов объединения. Используя этот инструмент, мы можем не «просто» связать таблицы, но и сделать это по определенным условиям.
Типы объединения данных в Power Query
Power Query предлагает на выбор шесть различных способов соединения таблиц. Если посмотреть теорию про объединение данных, вы наверняка встретите примеры рисунков с кругами – схематичным изображением множеств или таблиц, которые мы хотим объединить.
- Внешнее соединение слева (все из первой таблицы, совпадающие из второй)
- Внешнее соединение справа (все из второй таблицы, совпадающие из первой)
- Полное внешнее (все строки из обеих таблиц)
- Внутреннее (только совпадающие строки)
- Анти-соединение слева (только строки в первой таблице)
- Анти-соединение справа (только строки во второй таблице)
Внешнее соединение слева
Внешнее соединение справа
Разберем в способы объединения данных на примерах.
Внешнее соединение слева
Left Outer Join
Внешнее соединение слева наиболее часто применяется для объединения таблиц. Это то, что мы делаем в Excel с помощью формулы ВПР – из одной таблицы «перетаскиваем» данные в другую.
Когда вы выбираете «Внешнее соединение слева», к данным из первой таблицы добавляются все значения из второй таблицы, соответствующие столбцу поиска. Если во второй таблице нет искомых значений, вы получите null или «пусто».
Пример: объединим план продаж в штуках с плановыми ценами.
Добавьте таблицы в Power Query: вкладка Данные → Получить данные → Из других источников → Из таблицы / диапазона , или для новых версий Excel: вкладка Данные → Из таблицы / диапазона . Объедините запросы: вкладка Главная → Объединить запросы , выберите столбец слияния «Товар» и тип объединения «Внешнее соединение слева». На некоторые товары нет плановых цен, поэтому после объединения в столбце «цена» для этих товаров будет проставлен null.
Хотя в диалоговом окне таблицы расположены одна под другой (просто так удобнее технически), связь все равно горизонтальная. В диалоговом окне указывается столбец, по которому выполняется объединение.
Внешнее соединение справа
Right Outer Join
Тип соединения «Внешнее соединение справа» используется, если в первую таблицу нужно не только добавить новые столбцы, но и отфильтровать её. Вторая таблица используется как источник данных и управляет содержимым первой таблицы.
Что происходит, когда вы выбираете «Внешнее соединение справа»: из первой таблицы исчезнут все не найденные во второй таблице данные. Из второй таблицы будут добавлены те значения, которых нет в первой. Если посмотреть на рисунок, видно, что Внешнее соединение справа работает так же, как и Внешнее соединение слева, отличается только порядок расположения таблиц.
Пример: в первой таблице — ожидаемые среднемесячные продажи на одного покупателя, во второй – количество потенциальных покупателей. Объединим таблицы, чтобы запланировать будущие продажи.
Объединим таблицы, чтобы запланировать будущие продажи. Общий столбец для поиска — «Направление».
В результате объединения из таблицы «исчезли» данные по направлениям, где нет потенциальных покупателей.
Полное внешнее соединение
Full Outer Join
Этот тип соединения создает таблицу, в которой есть все строки и первой, и второй таблицы. С помощью общего столбца объединяются все строки таблиц.
Пример: в первой таблице приведен объем производства по дням, во второй – объем брака. Создадим общую таблицу.
Для создания общей таблицы используется Полное внешнее соединение. Для объединения используем общий столбец «дата». В результате получится отчет с датами и цифрами из первой и второй таблицы. В ячейках, где данные за аналогичные периоды были только в первой или только во второй таблице, стоит null.
Внутреннее соединение
Inner Join
Внутреннее соединение находит и оставляет только совпадающие строки из обеих таблиц. Если данные есть в одной из таблиц, но нет в другой, то в объединенном запросе они не появятся.
Таблицы объединяются с помощью общего столбца. При этом из первой таблицы исчезнут все не найденные в «общем» столбце второй таблицы строки. Из второй таблицы – исчезнут не найденные в первой.
Пример: есть два списка покупателей, принявших участие в акциях А и Б – по одному для каждой из акций. С помощью Внутреннего соединения получим список покупателей, участвовавших и в той, и в другой акции.
Поиск выполняется по общему столбцу «Покупатель». Результат — список покупателей, участвовавших в обеих акциях.
Анти-соединение слева
Left Anti Join
Если вам требуется исключить из таблицы ненужные строки, можно воспользоваться объединением с Анти-соединением слева. С помощью этого способа из первой таблицы будут исключены все строки, найденные во второй.
Пример: в первой таблице – записи о сделанных покупателям коммерческих предложениях, во второй – данные о продажах. С помощью Анти-соединения слева найдем покупателей, которые получили предложение, но не сделали покупку.
С помощью запроса исключим из первой таблицы найденных во второй покупателей. В результате получится список покупателей, которые не сделали покупки.
Анти-соединение справа
Right Anti Join
Анти-соединение справа работает так же, как и Анти-соединение слева. Разница здесь только в порядке расположения таблиц.
Пример: в первой таблице – отчет о выполненных задачах, во второй – список поставленных задач. Объединив таблицы с помощью Анти-соединения справа, найдем невыполненные задачи.
В результате объединения получим список невыполненных задач.