Забыли пароль?


Хитрости »
Основные понятия (24)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (17)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (66)
Разное (42)
Баги и глюки Excel (4)

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

Одной из наиболее часто решаемых задач в Excel является сравнение таблиц между собой. Как правило это делается либо для выявления расхождений/совпадений между двумя таблицами, либо для получения данных из одной таблицы в другую на основании какого-то критерия. Чаще всего для этого используется функция ВПР(VLOOKUP). Но и PowerQuery здесь тоже может помочь, особенно если речь идет о больших объемах данных. Называется этот инструмент – Слияние. При этом Слияние в PowerQuery имеет несколько реализаций, которые мы и рассмотрим.

Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?


Скачать файл с исходными данными, используемый в видеоуроке:

  Слияние запросов в PowerQuery.xlsx (52,6 KiB, 526 скачиваний)


Для примера возьмем две таблицы:
Таблица 1
Таблица 2
Как видно здесь есть столбец, который совпадает в обеих таблицах – ID отдела. Значит на основании этого столбца эти две таблицы можно сравнивать и объединять, что мы и будем делать.
Сначала загружаем по очереди обе таблицы в PowerQuery: вкладка Данные(или вкладка Power Query) -группа Загрузить и преобразовать -Из таблицы
Объединить запросы
После загрузки запрос каждой таблицы переименованием в более понятные названия Отделы и Сотрудники.
Чтобы к каждому сотруднику подставить название отдела из таблицы Отделы на основании ID необходимо выделить запрос Сотрудники -перейти на вкладку Главная -группа Объединить -Объединить запросы -Объединить в новый запрос
В демонстративных целях я выбираю Объединить в новый запрос, чтобы запрос Сотрудники не изменялся. В реальных задачах можно использовать Объединить запросы.
Появится окно объединения запросов
Параметры объединения
В этом окне вверху будет по умолчанию выбрана таблица, из которой было вызвано слияние. Выделяем столбец ID отдела, т.к. именно на основании него мы хотим сравнить таблицы. Далее выбираем из списка таблицу, с которой хотим сравнить таблицу Сотрудники. Это таблица Отделы. В ней выделяем так же столбец ID, т.к. сравнивать будем именно по этому столбцу.

Примечание: в качестве ключевого столбца может быть не один столбец, а сразу несколько. Здесь надо строго соблюдать очередность выделения столбцов-ключей – в обеих таблицах последовательность должна быть одинаковой. Для этого в заголовке каждого столбца PowerQuery подписывает порядковый номер, показывая порядок, в котором были выделены столбцы ключей.

И в самом низу выбираем из списка тип слияния. Всего доступно 6 типов:

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


1. Внешнее соединение слева (все из первой таблицы, совпадающие из второй)
Это по сути аналог ВПР к первой таблице. В результате будет создана таблица, в которой будут все строки первой таблицы и только те строки из второй, ID которых совпадает:
Внешнее соединение слева
Однако здесь есть существенное отличие от ВПР(плюс или минус - зависит от ситуации). А заключается оно в следующем: если во второй таблице одному ключу соответствует более одного значения - то Power Qwery вернет для каждого такого ключа первой таблицы все строки этого ключа из второй. Например, вторая таблица содержит такие значения:
Задвоение записей второй таблицы
как видно, для ID 1 целых три записи. Как следствие мы получим для каждого ID 1 первой таблицы не одну строку, а 3. У нас там два сотрудника с ID 1: Ангелочкин П.Н. и Удачный С.А.:
Одинаковые ID
а в итоге мы получим "раздутую" первую таблицу:
Множество записей в LeftJoin
Чтобы этого избежать и получить только первую запись из всех(полный аналог ВПР в Excel) можно изменить запрос. Не разворачивать полученные в результате слияния таблицы, а выбрать из них только первое значение. Для этого сразу после слияния идем на вкладку Добавление столбца -Настраиваемые столбец и в качестве формулы записываем:
try [Отделы]{0}[Отдел] otherwise null
Настраиваемый столбец
Если кратко, то мы берем полученную в результате запроса таблицу для каждой строки([Отделы]), отбираем из неё первую строку({0} - в PowerQwery отсчет строк начинается с нуля) и берем из этой строки значение только столбца [Отдел]. А try ... otherwise null - это обработка ошибок в случаях, когда для строк первой таблицы нет совпадений во второй. Если ошибок нет - записывается результат, если будет ошибка - запишем значение null(аналог пустой ячейки в Excel). Т.е. такая конструкция это нечто вроде ЕСЛИОШИБКА в Excel.
Кстати, даже эту строку можно записать еще короче:
[Отделы][Отдел]{0}?
try ... otherwise выглядит нагляднее, но вопросительный знак на конце короче. Хотя по сути в данной ситуации делает тоже самое: если не углубляться, то он говорит PowerQwery выполнять строку только в случае, если она возвращает значение. Особое внимание здесь следует обратить на то, что при использовании вопр.знака в нашем случае сначала надо указать имя таблицы в квадратных скобках, потом имя столбца и самым последним номер строки в фигурных скобках. Т.е. немного изменяем порядок обращения. Если этого не сделать, то вопр.знак не сработает как ожидается.

Запрос в расширенном редакторе будет выглядеть примерно так:

let
    Источник = Table.NestedJoin(Сотрудники,{"ID отдела"},Отделы,{"ID"},"Отделы",JoinKind.LeftOuter),
    custom = Table.AddColumn(Источник, "Пользовательский", each try [Отделы]{0}[Отдел] otherwise null)
in
    custom


2. Внешнее соединение справа (все из второй таблицы, совпадающие из первой)
Тоже аналог ВПР, но уже ко второй таблице. В результате будет создана таблица, в которой выводит все строки второй таблицы и только те строки первой, которые есть во второй(опять же, на основании столбца ID):
Внешнее соединение справа
Но здесь есть существенное отличие от работы ВПР. Как видно, у нас два сотрудника из одного отдела и один сотрудник, для которого отдел отсутствует. PowerQuery взяла таблицу отделов и отобрала только те, для которых совпал ID у таблицы сотрудники. Но при этом для каждого отдела добавились ВСЕ сотрудники с одним ID. ВПР в таких случаях возвращает всегда только первое найденное совпадение и для отдела с ID 1 у нас просто получилось бы два одинаковых сотрудника(Ангелочкин П.Н.).
Если нужно только первое(точный аналог ВПР), то решение такое же, как и в случае с Внешним соединением слева - через добавление столбца и отбора первой найденной записи.


3. Полное внешнее (все строки из обеих таблиц)
Выводит все строки из обеих таблиц. Примерно то же самое, если бы мы применили к обеим таблицам ВПР и после этого добавили к первой таблице те значения из второй, которые не найдены в первой:
Полное внешнее соединение


4. Внутреннее (только совпадающие строки)
Выводит только те строки, которые есть в обеих таблицах(на основании ID)
Внутреннее соединение
При этом те строки, для которых не найдено совпадений, просто удаляются


5. Анти-соединение слева (только строки в первой таблице)
Выявляет те строки первой таблицы, для которых нет соответствия во второй таблице:
Анти-соединение слева


6. Анти-соединение справа (только строки во второй таблице)
Выявляет те строки второй таблицы, для которых нет соответствия в первой
Анти-соединение справа


Как видно, PowerQuery предоставляет довольно неплохие возможности по сравнению таблиц и может вполне составить конкуренцию ВПР, особенно учитывая возможность сравнения сразу по нескольким столбцам и гораздо более быстрый алгоритм работы с большими наборами данных.
Однако следует помнить, что инструмент слияние учитывает регистр букв и не может сравнивать значения по части строки и при помощи подстановочных символов(звездочка и вопросительный знак). ВПР в этом плане гораздо гибче.

Скачать файл с исходными данными, используемый в видеоуроке:

  Слияние запросов в PowerQuery.xlsx (52,6 KiB, 526 скачиваний)

Так же см.:
Собрать данные из файлов защищенных паролем PowerQuery
Собрать данные с таблиц с изменяющимися столбцами в PowerQuery
Относительный путь к данным PowerQuery


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки
Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2020 Excel для всех   Войти