Вы работали со сводными таблицами 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
Анти-соединение справа работает так же, как и Анти-соединение слева. Разница здесь только в порядке расположения таблиц.
Пример: в первой таблице – отчет о выполненных задачах, во второй – список поставленных задач. Объединив таблицы с помощью Анти-соединения справа, найдем невыполненные задачи.
В результате объединения получим список невыполненных задач.
Кроме объединения таблиц с помощью запросов Power Query, такие операции можно выполнять и с помощью DAX-формул, применяя их в различных сочетаниях: GENERATEALL, NATURALLEFTOUTERJOIN, NATURALINNERJOIN, CROSSJOIN, FILTER+CROSSJOIN, GENERATE, EXCEPT и др.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Еще…Меньше
При слиянии обычно объединяются два запроса, которые находятся в Excel или из внешнего источника данных. Кроме того, функция слияния имеет интуитивно понятный пользовательский интерфейс, помогающий легко объединить две связанные таблицы. Пример объединения общего объема продаж из запроса сведений о заказе в таблицу продуктов см. в руководстве Об объединении нескольких источников данных.
Внимание: Уровни конфиденциальности не позволяют пользователю непреднамеренно объединять данные из нескольких источников данных, которые могут быть частными или организационными. В зависимости от запроса пользователь может непреднамеренно отправить данные из частного источника данных в другой источник данных, который может быть вредоносным. Power Query анализирует каждый источник данных и классифицирует его по определенному уровню конфиденциальности: общедоступному, организационному и частному. Дополнительные сведения об уровнях конфиденциальности см. в разделе Установка уровней конфиденциальности.
Запрос слиянием создает новый запрос на основе двух существующих запросов. Один результат запроса содержит все столбцы из первичной таблицы, при этом один столбец выступает в качестве одного столбца, содержащего связь с дополнительной таблицей. Связанная таблица содержит все строки, которые соответствуют каждой строке из главной таблицы на основе общего значения столбца. Операция развертывания добавляет столбцы из связанной таблицы в главную.
Существует два типа операций слияния:
-
Встроенное слияние Вы объединяете данные в существующий запрос, пока не достигнете окончательного результата. Результатом является новый шаг в конце текущего запроса.
-
Промежуточное слияние
Для каждой операции слияния создается новый запрос.
Чтобы просмотреть визуальное представление связей в диалоговом окне Зависимости запросов , выберите Просмотр > зависимостей запросов. В нижней части диалогового окна выберите команду Макет , чтобы управлять ориентацией схемы.
Вам потребуется по крайней мере два запроса, которые можно объединить и которые имеют по крайней мере один или несколько столбцов для сопоставления в операции соединения. Они могут поступать из разных типов внешних источников данных. В следующем примере используются Products и Total Sales.
-
Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
-
Выберите Главная > Запросы слияния. По умолчанию выполняется встроенное слияние. Чтобы выполнить промежуточное слияние, щелкните стрелку рядом с командой и выберите Команду Объединить запросы как Новые.
Откроется диалоговое окно Слияние .
-
Выберите основную таблицу из первого раскрывающегося списка, а затем выберите столбец соединения, выбрав заголовок столбца.
-
Выберите связанную таблицу из следующего раскрывающегося списка, а затем выберите соответствующий столбец, выбрав заголовок столбца.
Убедитесь, что выбрано одинаковое количество столбцов для сопоставления в предварительном просмотре основной и связанной или вторичной таблиц. Сравнение столбцов основано на порядке выбора в каждой таблице. Соответствующие столбцы должны иметь один и тот же тип данных, например Text или Number. Можно также выбрать несколько столбцов для слияния.
-
После выбора столбцов из основной таблицы и связанной таблицы Power Query отображает количество совпадений из верхнего набора строк. Это действие проверяет, правильно ли выполнена операция слияния или необходимо ли внести изменения, чтобы получить нужные результаты. Можно выбрать различные таблицы или столбцы.
-
Операция соединения по умолчанию — это внутреннее соединение, но в раскрывающемся списке Тип соединения можно выбрать следующие типы операций соединения:
Внутреннее соединение Возвращает только совпадающие строки из основной и связанной таблиц.
Левое внешнее соединение Сохраняет все строки из первичной таблицы и возвращает все совпадающие строки из связанной таблицы.
Правое внешнее соединение Сохраняет все строки из связанной таблицы и возвращает все совпадающие строки из основной таблицы.
Полный внешний Возвращает все строки из основной и связанной таблиц.
Левое анти-соединение Возвращает только строки из основной таблицы, в которых нет совпадающих строк из связанной таблицы.
Правое анти-соединение Возвращает только строки из связанной таблицы, в которых нет совпадающих строк из основной таблицы.
Перекрестные соединения Возвращает декартовое произведение строк из обеих таблиц путем объединения каждой строки из основной таблицы с каждой строкой из связанной таблицы.
-
Если вы хотите выполнить нечеткое совпадение, выберите Использовать нечеткое сопоставление для выполнения слияния и выберите из параметров Нечеткое сопоставление. Дополнительные сведения см. в разделе Создание нечеткого совпадения.
-
Чтобы включить только те строки из первичной таблицы, которые соответствуют связанной таблице, выберите Только включить соответствующие строки. В противном случае все строки из первичной таблицы включаются в результирующий запрос.
-
Нажмите кнопку ОК.
Result (Результат)
После операции слияния можно развернуть структурированный столбец Таблица, чтобы добавить столбцы из связанной таблицы в основную таблицу. После развертывания столбца в главной таблице вы можете применить фильтры и другие операции преобразования. Дополнительные сведения см. в статье Работа со структурированным столбцом списка, записи или таблицы.
-
В предварительном просмотре данных щелкните значок Развернуть
рядом с заголовком столбца NewColumn . -
В раскрывающемся списке Развернуть выберите или очистите столбцы, чтобы отобразить нужные результаты. Чтобы агрегировать значения столбцов, выберите Агрегат.
-
Вы можете переименовать новые столбцы. Дополнительные сведения см. в разделе Переименование столбца.
рядом с заголовком столбца NewColumn .
См. также
Справка по Power Query для Excel
Сведения об объединении нескольких источников данных
Общие сведения о запросах слияния (docs.com)
Левое внешнее соединение (docs.com)
Правое внешнее соединение (docs.com)
Полное внешнее соединение (docs.com)
Внутреннее соединение (docs.com)
Левое анти-соединение (docs.com)
Правое анти-соединение (docs.com)
Перекрестные соединения (docs.com)
Нужна дополнительная помощь?
Постановка задачи
Давайте разберем красивое решение для одной из весьма стандартных ситуаций, с которой рано или поздно сталкивается большинство пользователей Excel: нужно быстро и автоматически собрать данные из большого количества файлов в одну итоговую таблицу.
Предположим, что у нас есть вот такая папка, в которой содержится несколько файлов с данными из филиалов-городов:
Количество файлов роли не играет и может меняться в будущем. В каждом файле есть лист с именем Продажи, где расположена таблица с данными:
Количество строк (заказов) в таблицах, само-собой, разное, но набор столбцов везде стандартный.
Задача: собрать данные из всех файлов в одну книгу с последующим автоматическим обновлением при добавлении-удалении файлов-городов или строк в таблицах. По итоговой консолидированной таблице затем можно будет строить любые отчеты, сводные таблицы, фильтровать-сортировать данные и т.д. Главное — суметь собрать.
Подбираем оружие
Для решения нам потребуется последняя версия Excel 2016 (в нее нужный функционал уже встроен по умолчанию) или предыдущие версии Excel 2010-2013 с установленной бесплатной надстройкой Power Query от Microsoft (скачать ее можно здесь). Power Query — это супергибкий и супермощный инструмент для загрузки в Excel данных из внешнего мира с последующей их зачисткой и обработкой. Power Query поддерживает практически все существующие источники данных — от текстовых файлов до SQL и даже Facebook 
Если у вас нет Excel 2013 или 2016, то дальше можно не читать (шучу). В более древних версиях Excel подобную задачу можно реализовать только программированием макроса на Visual Basic (что весьма непросто для начинающих) или монотонным ручным копированием (что долго и порождает ошибки).
Шаг 1. Импортируем один файл как образец
Для начала давайте импортируем данные из одной книги в качестве примера, чтобы Excel «подхватил идею». Для этого создайте новую пустую книгу и…
- если у вас Excel 2016, то откройте вкладку Данные и выберите Создать запрос — Из файла — Из книги (Data — New Query- From file — From Excel)
- если у вас Excel 2010-2013 с установленной надстройкой Power Query, то откройте вкладку Power Query и выберите на ней Из файла — Из книги (From file — From Excel)
Затем в открывшемся окне переходим в нашу папку с отчетами и выбираем любой из файлов-городов (не играет роли какой именно, т.к. они все типовые). Через пару секунд должно появиться окно Навигатор, где нужно в левой части выбрать требуемый нам лист (Продажи), а в правой отобразится его содержимое:
Если нажать в правом нижнем углу этого окна кнопку Загрузить (Load), то таблица будет сразу импортирована на лист в исходном виде. Для одиночного файла — это хорошо, но нам нужно загрузить много таких файлов, поэтому мы пойдем немного другим путем и жмем кнопку Правка (Edit). После этого должен в отдельном окне отобразиться редактор запросов Power Query с нашими данными из книги:
Это очень мощный инструмент, позволяющий «допилить» таблицу под нужный нам вид. Даже поверхностное описание всех его функций заняло бы под сотню страниц, но, если совсем кратко, то с помощью этого окна можно:
- отфильтровывать ненужные данные, пустые строки, строки с ошибками
- сортировать данные по одному или нескольким столбцам
- избавляться от повторов
- делить слипшийся текст по столбцам (по разделителям, количеству символов и т.д.)
- приводить текст в порядок (удалять лишние пробелы, исправлять регистр и т.д.)
- всячески преобразовывать типы данных (превращать числа как текст в нормальные числа и наоборот)
- транспонировать (поворачивать) таблицы и разворачивать двумерные кросс-таблицы в плоские
- добавлять к таблице дополнительные столбцы и использовать в них формулы и функции на встроенном в Power Query языке М.
- …
Для примера, давайте добавим к нашей таблице столбец с текстовым названием месяца, чтобы потом проще было строить отчеты сводных таблиц. Для этого щелкните правой кнопкой мыши по заголовку столбца Дата и выберите команду Дублировать столбец (Duplicate Column), а затем щелкните правой кнопкой мыши по заголовку появившегося столбца-дубликата и выберите команды Преобразование — Месяц — Название месяца:
Должен образоваться новый столбец с текстовыми названиями месяца для каждой строки. Дважды щелкнув по заголовку столбца, его можно переименовать из Копия Дата в более удобное Месяц, например.
Если в каких-то столбцах программа не совсем корректно распознала тип данных, то ей можно помочь, щелкнув по значку формата в левой части каждого столбца:
Исключить строки с ошибками или пустые строки, а также ненужных менеджеров или заказчиков можно с помощью простого фильтра:
Причем все выполненные преобразования фиксируются в правой панели, где их всегда можно откатить (крестик) или изменить их параметры (шестеренка):
Легко и изящно, не правда ли?
Шаг 2. Преобразуем наш запрос в функцию
Чтобы впоследствии повторить все сделанные преобразования данных для каждой импортируемой книги, нужно преобразовать наш созданный запрос в функцию, которая затем будет применяться, по очереди, ко всем нашим файлам. Сделать это, на самом деле, очень просто.
В редакторе запросов перейдите на вкладку Просмотр и нажмите кнопку Расширенный редактор (View — Advanced Editor). Должно открыться окно, где все наши предыдущие действия будут записаны в виде кода на языке М. Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера:
Теперь аккуратно вносим пару правок:
Смысл их прост: первая строка (filepath)=> превращает нашу процедуру в функцию с аргументом filepath, а ниже мы меняем фиксированный путь на значение этой переменной.
Все. Жмем на Готово и должны увидеть вот это:
Не пугайтесь, что пропали данные — на самом деле все ОК, все так и должно выглядеть Мы успешно создали нашу пользовательскую функцию, где запомнился весь алгоритм импорта и обработки данных без привязки к конкретному файлу. Осталось дать ей более понятное имя (например getData) на панели справа в поле Имя и можно жать Главная — Закрыть и загрузить (Home — Close and Load). Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера.. Вы вернетесь в основное окно Microsoft Excel, но справа должна появиться панель с созданным подключением к нашей функции:
Шаг 3. Собираем все файлы
Все самое сложное — позади, осталась приятная и легкая часть. Идем на вкладку Данные — Создать запрос — Из файла — Из папки (Data — New Query — From file — From folder) или, если у вас Excel 2010-2013, аналогично на вкладку Power Query. В появившемся окне указываем папку, где лежат все наши исходные файлы-города и жмем ОК. Следующим шагом должно открыться окно, где будут перечислены все найденные в этой папке (и ее подпапках) файлы Excel и детализация по каждому из них:
Жмем Изменить (Edit) и опять попадаем в знакомое окно редактора запросов.
Теперь нужно добавить к нашей таблице еще один столбец с нашей созданной функцией, которая «вытянет» данные из каждого файла. Для этого идем на вкладку Добавить столбец — Пользовательский столбец (Add Column — Add Custom Column) и в появившемся окне вводим нашу функцию getData, указав для ее в качестве аргумента полный путь к каждому файлу:
После нажатия на ОК созданный столбец должен добавиться к нашей таблице справа.
Теперь удалим все ненужные столбцы (как в Excel, с помощью правой кнопки мыши — Удалить), оставив только добавленный столбец и столбец с именем файла, т.к. это имя (а точнее — город) будет полезно иметь в итоговых данных для каждой строки.
А теперь «вау-момент» — щелкнем мышью по значку со своенным стрелками в правом верхнем углу добавленного столбца с нашей функцией:
… снимаем флажок Использовать исходное имя столбца как префикс (Use original column name as prefix)и жмем ОК. И наша функция подгрузит и обработает данные из каждого файла, следуя записанному алгоритму и собрав все в общую таблицу:
Для полной красоты можно еще убрать расширения .xlsx из первого столбца с именами файлов — стандартной заменой на «ничего» (правой кнопкой мыши по заголовку столбца — Заменить) и переименовать этот столбец в Город. А также подправить формат данных в столбце с датой.
Все! Жмем на Главной — Закрыть и загрузить (Home — Close & Load). Все собранные запросом данные по всем городам будут выгружены на текущий лист Excel в формате «умной таблицы»:
Созданное подключение и нашу функцию сборки не нужно никак отдельно сохранять — они сохраняются вместе с текущим файлом обычным образом.
В будущем, при любых изменениях в папке (добавлении-удалении городов) или в файлах (изменение количества строк) достаточно будет щелкнуть правой кнопкой мыши прямо по таблице или по запросу в правой панели и выбрать команду Обновить (Refresh) — Power Query «пересоберет» все данные заново за несколько секунд.
P.S.
Поправка. После январских обновлений 2017 года Power Query научился собирать Excel’евские книги сам, т.е. не нужно больше делать отдельную функцию — это происходит автоматически. Таким образом второй шаг из этой статьи уже не нужен и весь процесс становится заметно проще:
- Выбрать Создать запрос — Из файла — Из папки — Выбрать папку — ОК
- После появления списка файлов нажать Изменить
- В окне редактора запросов развернуть двойной стрелкой столбец Binary и выбрать имя листа, который нужно взять из каждого файла
И все! Песня!
Ссылки по теме
- Редизайн кросс-таблицы в плоскую, подходящую для построения сводных таблиц
- Построение анимированной пузырьковой диаграммы в Power View
- Макрос для сборки листов из разных файлов Excel в один
По своим наблюдениям, я замечал, что многие мои коллеги — при объединении 2-х и более таблиц в Excel используют методы ручного копирования данных, при соединении таблиц по одинаковым столбцам данных используют функцию ВПР. Это, конечно удобно, когда речь идет о небольших объемах данных. Но, что делать, если Вам необходимо обработать большие массивы данных из разных источников и из большого количества таблиц? В данном случае, Вам подойдет инструмент Power Query.
Как быстро можно объединить 2 и более таблицы в одну путем добавления данных:
1.В программе Excel импортируем данные из 2-х или более источников, например из книги *xlsx.
2. Создаем подключения данных.
3. Объединяем запросы через вкладку: Данные – создать запрос – объединить запросы – Добавить.
Таким образом данные из нескольких таблиц соединяются в одну таблицу буквально за несколько кликов мыши.
С использованием Power Query также можно объединять таблицы в одну через одинаковые столбцы данных, используя путь: Данные – создать запрос – объединить запросы – Объединить. Это аналог инструмента ВПР, но наиболее быстрый, удобный и эффективный.
Самый удобный инструмент для объединения таблиц – надстройка Power Query. Сегодня разберем 6 базовых типов объединения таблиц в Power Query. Пробежимся по особенностям соединений, найдем плюсы и минусы разных способов и выберем оптимальные.
Power Query предлагает на выбор шесть различных способов объединения таблиц:
- Внешнее соединение слева (все из первой таблицы, совпадающие из второй)
- Внешнее соединение справа (все из второй таблицы, совпадающие из первой)
- Полное внешнее (все строки из обеих таблиц)
- Внутреннее (только совпадающие строки)
- Анти-соединение слева (только строки в первой таблице)
- Анти-соединение справа (только строки во второй таблице)
Для разных целей вам понадобятся разные виды соединений. Несмотря на то, что способы 1 и 2, а также 5 и 6 — зеркальны, мы разберём каждый из них.
Внешнее соединение слева (Left Outer Join)
Из правой таблицы «перетаскиваем» данные в левую (в Excel это делают обычно с помощью ВПР). К данным из первой таблицы добавляются все значения из второй таблицы, соответствующие столбцу поиска. Если во второй таблице нет искомых значений, вы получите значение null (null – это значит пусто).
Пример: объединим продажи товаров с ценами.
Шаг 1. Добавим таблицы в Power Query.
Выбираем: вкладка Данные → Получить данные → Из других источников → Из таблицы / диапазона. Для новых версий Excel: вкладка Данные → Из таблицы / диапазона.
Шаг 2. Создадим подключения
После загрузки вернемся обратно в Excel из Power Query командой «Закрыть и загрузить — Закрыть и загрузить в…». В появившемся затем окне выбираем «Только создать подключение». 
Повторяем то же самое со второй таблицей с ценами. Теперь в Power Query появились таблицы, которые можно объединять.
Шаг 3. Объединим таблицы
Создадим третий запрос, который будет объединять и сравнивать данные из предыдущих двух запросов.
Для этого выберем в Excel на вкладке Данные — Получить данные — Объединить запросы — Объединить или нажмем кнопку Объединить на вкладке Power Query.
В окне объединения выберем в выпадающих списках наши таблицы, выделим в них столбцы с названиями товаров и в нижней части зададим тип соединения «Внешнее соединение слева»:
Далее появится таблица из трех столбцов. Третий столбец содержит таблицы цен по каждому товару — эти таблицы нужно развернуть с помощью двойной стрелки в шапке:
Оставляем галочки для нужных столбцов и нажимаем ОК. Можно не использовать имя столбца как префикс — это только загружает заголовки.
В итоге получим слияние данных из обеих таблиц. На некоторые товары цены еще не установлены, поэтому в столбце «цена» для этих товаров будет значение null:
Осталось выгрузить получившийся отчет на лист Excel с помощью кнопки «Закрыть и загрузить» на вкладке Главная. Объединение выполнено успешно!
Этот тип Join довольно удобен если у вас есть таблица статистики, в которую нужно подтянуть дополнительные столбцы.
Внешнее соединение справа (Right Outer Join)
Вторая таблица используется как источник данных и управляет содержимым первой таблицы. Из второй таблицы будут добавлены те значения, которых нет в первой. Из первой таблицы исчезнут все ненайденные во второй таблице данные.
«Внешнее соединение справа» работает так же, как и «Внешнее соединение слева» – отличается только порядок расположения таблиц. В связи с этим оно редко используется на практике.
Пример: объединим таблицу ставок по оплатам и таблицу с переработками.
Объединим таблицы, чтобы рассчитать будущие выплаты сотрудникам. Общий столбец для поиска — «сотрудники».
В результате объединения у нас «исчезли» данные по сотрудникам, у которых нет переработок. В объединенную таблицу попали только сотрудники из второй таблицы.
Полное внешнее соединение (Full Outer Join)
В этом случае будут объединены все строки из первой и второй таблиц. Это самый безопасный способ соединения двух таблиц — вы не теряете данные. Такой способ часто помогает сравнить данные двух таблиц и найти расхождения.
Пример: объединим таблицы по объему производства и количеству брака.
В данном примере попробуем объединить таблицы по нескольким столбцам. Для создания общей таблицы используется «Полное внешнее соединение». Для объединения используем общие столбцы «Дата» и «Подразделение» — выбираем их в нужном порядке в каждой таблице, зажимая клавишу ctrl:
В результате получится отчет, который нужно немного доработать:
Приводим к нужному виду нашу объединенную таблицу:
Во всех ячейках, где даты и подразделения таблиц не совпали, проставлено null. Сразу видим расхождения между таблицами, что очень удобно для сравнений.
Внутреннее соединение (Inner Join)
При таком типе соединения в общей таблице остаются только совпадающие строки из обеих таблиц. Если вас интересуют только пересечения таблиц, Inner Join — самый подходящий способ.
Пример: получить список только тех сотрудников, которые выполнили успешно оба теста.
Для решения этой задачи также объединим таблицы по нескольким столбцам. Для создания общей таблицы используется тип соединения «Внутреннее (только совпадающие строки)».
Получим список сотрудников, участвующих в обоих тестах:
После фильтрации и удаления лишних столбцов приводим объединенную таблицу к нужному виду:
Анти-соединение слева (Left Anti Join)
При Left Anti Join подтянутся все данные из левой таблицы, которых нет в правой таблице. Можно сказать, анти-ВПР.
Если надо вычесть одну таблицу из другой — вам подойдёт именно Left Anti Join.
Пример: в первой таблице – список клиентов и количество звонков, во второй – данные о сделках. Нужно найти клиентов, которым звонили, но они ничего не купили.
Исключаем из первой таблицы всех клиентов второй таблицы. Для создания общей таблицы используется «Анти-соединение слева»:
После удаления лишнего столбца получаем готовую таблицу клиентов, которые ничего не купили:
Анти-соединение справа (Right Anti Join)
С помощью Right Anti Join из второй таблицы будут исключены все строки, найденные в первой таблице. По сути это зеркальное отражение Left Anti Join, поэтому используется редко.
Пример: в первой таблице — участники акций, во второй – клиенты и суммы оплат. Следует найти клиентов, которые совершили сделки, но не участвовали в акциях.
Нужно исключить из второй таблицы всех найденных клиентов в первой таблице. Для создания общей таблицы используется «Анти-соединение справа».
Power Query на этапе слияния всегда делает нам подсказку: «Выделенный фрагмент исключает строки из второй таблицы (4 из 6)». В результате получаем запрос такого вида:
Разворачиваем содержимое вложенных таблиц с помощью двойной стрелки в шапке и удаляем два первых столбца. Получаем готовый отчет о клиентах, не участвующих в акции:
Заключение
Мы рассмотрели 6 базовых соединений таблиц в Power Query. 4 из них довольно часто применяются на практике, а 2 — являются зеркальными и используются редко.
Для подтягивания дополнительных данных к таблицам статистики рекомендуем использовать Left Join. Если вы хотите сравнить несколько таблиц между собой, вам пригодится Full Outer Join. Когда нужно найти пересечения таблиц — пользуйтесь Inner Join. Ну а для вычитания одной таблицы из другой всегда можно задействовать Left Anti Join.
«Right Join» и «Right Anti Join» на практике обычно не используются, так как являются зеркальным отражением «Left Join» и «Left Anti Join». Но на всякий случай мы включили их в описание, чтобы вы увидели как их использовать.