Вы работали со сводными таблицами Excel? Если нет – нужно срочно начинать. Это супер-инструмент, который позволяет быстро и просто обрабатывать большие объемы данных. А если вы с ними все-таки работали, то я как экстрасенс-капитан-очевидность могу точно сказать, что вы сталкивались с ситуацией, когда нужно построить сводную таблицу на основе нескольких источников данных. Например, с помощью нескольких одинаковых таблиц Excel, копируя их одну под другой. Или дополняя свою таблицу новыми столбцами и аналитиками.
Добавление или объединение таблиц?
Чаще всего, чтобы объединить данные в Excel, пользователи копируют таблицы одну под другую. Или пишут формулы по типу ВПР, если в таблицу нужно добавить новые столбцы или аналитики. Но вы наверняка знаете, что самый удобный инструмент для объединения данных в Excel – это Power Query. Там есть два принципиальных способа:
- По вертикали – добавление таблицы под таблицу. Полезно, когда таблицы с одинаковой шапкой находятся в разных файлах или на разных листах.
- По горизонтали – слияние таблиц, похожее на ВПР. А здесь есть еще варианты — не только аналогичные ВПР, но и ВПР-наоборот, и ВПР-неВПР… Целых шесть видов объединения таблиц.
Разберем, чем эти способы отличаются друг от друга.
Вариант 1. Добавление таблицы под таблицу
Приём с добавлением таблиц применяется, когда у вас есть несколько таблиц с одинаковой шапкой, но они находятся в разных местах, например на разных листах или в нескольких файлах. Тогда, чтобы построить итоговую сводную, мы можем просто строки одной таблицы добавить в другую. Обращаю внимание, что здесь данные таблиц объединяются как-бы по вертикали.
Раньше процедуру добавления строк из одной таблицы в другую можно было выполнить с помощью копирования. Отсюда много ручной работы при внесении новых данных (ну, или писали макросы). Однако с момента появления в Excel надстройки Power Query у нас появилась возможность добавлять таблицы одну под другую несколькими щелчками мыши. Как это сделать – смотрите в видео.
Вариант 2. Объединение таблиц
Объединение таблиц применяется, когда у вас есть две таблицы (шапки у них скорее всего разные), и одна таблица дополняет другую. Например, в отчете продажи показаны по городам, а у вас просят вывести информацию по областям. Тогда потребуется объединение по горизонтали:
В «обычном» Excel это делается с помощью формулы ВПР или других формул с похожим функционалом. Power Query предлагает нам больше способов объединения. Используя этот инструмент, мы можем не «просто» связать таблицы, но и сделать это по определенным условиям.
Типы объединения данных в Power Query
Power Query предлагает на выбор шесть различных способов соединения таблиц. Если посмотреть теорию про объединение данных, вы наверняка встретите примеры рисунков с кругами – схематичным изображением множеств или таблиц, которые мы хотим объединить.
- Внешнее соединение слева (все из первой таблицы, совпадающие из второй)
- Внешнее соединение справа (все из второй таблицы, совпадающие из первой)
- Полное внешнее (все строки из обеих таблиц)
- Внутреннее (только совпадающие строки)
- Анти-соединение слева (только строки в первой таблице)
- Анти-соединение справа (только строки во второй таблице)
Внешнее соединение слева
Внутреннее соединение
Внешнее соединение справа
Анти-соединение слева
Полное внешнее
Анти-соединение справа
Разберем в способы объединения данных на примерах.
Внешнее соединение слева
Внешнее соединение слева наиболее часто применяется для объединения таблиц. Это то, что мы делаем в Excel с помощью формулы ВПР – из одной таблицы «перетаскиваем» данные в другую.
Когда вы выбираете «Внешнее соединение слева», к данным из первой таблицы добавляются все значения из второй таблицы, соответствующие столбцу поиска. Если во второй таблице нет искомых значений, вы получите null или «пусто».
Пример: объединим план продаж в штуках с плановыми ценами.
Добавьте таблицы в Power Query: вкладка Данные → Получить данные → Из других источников → Из таблицы / диапазона, или для новых версий Excel: вкладка Данные → Из таблицы / диапазона. Объедините запросы: вкладка Главная → Объединить запросы, выберите столбец слияния «Товар» и тип объединения «Внешнее соединение слева». На некоторые товары нет плановых цен, поэтому после объединения в столбце «цена» для этих товаров будет проставлен null.
Хотя в диалоговом окне таблицы расположены одна под другой (просто так удобнее технически), связь все равно горизонтальная. В диалоговом окне указывается столбец, по которому выполняется объединение.
Внешнее соединение справа
Тип соединения «Внешнее соединение справа» используется, если в первую таблицу нужно не только добавить новые столбцы, но и отфильтровать её. Вторая таблица используется как источник данных и управляет содержимым первой таблицы.
Что происходит, когда вы выбираете «Внешнее соединение справа»: из первой таблицы исчезнут все не найденные во второй таблице данные. Из второй таблицы будут добавлены те значения, которых нет в первой. Если посмотреть на рисунок, видно, что Внешнее соединение справа работает так же, как и Внешнее соединение слева, отличается только порядок расположения таблиц.
Пример: в первой таблице — ожидаемые среднемесячные продажи на одного покупателя, во второй – количество потенциальных покупателей. Объединим таблицы, чтобы запланировать будущие продажи.
Объединим таблицы, чтобы запланировать будущие продажи. Общий столбец для поиска — «Направление».
В результате объединения из таблицы «исчезли» данные по направлениям, где нет потенциальных покупателей.
Полное внешнее соединение
Этот тип соединения создает таблицу, в которой есть все строки и первой, и второй таблицы. С помощью общего столбца объединяются все строки таблиц.
Пример: в первой таблице приведен объем производства по дням, во второй – объем брака. Создадим общую таблицу.
Для создания общей таблицы используется Полное внешнее соединение. Для объединения используем общий столбец «дата». В результате получится отчет с датами и цифрами из первой и второй таблицы. В ячейках, где данные за аналогичные периоды были только в первой или только во второй таблице, стоит null.
Внутреннее соединение
Внутреннее соединение находит и оставляет только совпадающие строки из обеих таблиц. Если данные есть в одной из таблиц, но нет в другой, то в объединенном запросе они не появятся.
Таблицы объединяются с помощью общего столбца. При этом из первой таблицы исчезнут все не найденные в «общем» столбце второй таблицы строки. Из второй таблицы – исчезнут не найденные в первой.
Пример: есть два списка покупателей, принявших участие в акциях А и Б – по одному для каждой из акций. С помощью Внутреннего соединения получим список покупателей, участвовавших и в той, и в другой акции.
Поиск выполняется по общему столбцу «Покупатель». Результат — список покупателей, участвовавших в обеих акциях.
Анти-соединение слева
Если вам требуется исключить из таблицы ненужные строки, можно воспользоваться объединением с Анти-соединением слева. С помощью этого способа из первой таблицы будут исключены все строки, найденные во второй.
Пример: в первой таблице – записи о сделанных покупателям коммерческих предложениях, во второй – данные о продажах. С помощью Анти-соединения слева найдем покупателей, которые получили предложение, но не сделали покупку.
С помощью запроса исключим из первой таблицы найденных во второй покупателей. В результате получится список покупателей, которые не сделали покупки.
Анти-соединение справа
Анти-соединение справа работает так же, как и Анти-соединение слева. Разница здесь только в порядке расположения таблиц.
Пример: в первой таблице – отчет о выполненных задачах, во второй – список поставленных задач. Объединив таблицы с помощью Анти-соединения справа, найдем невыполненные задачи.
В результате объединения получим список невыполненных задач.
Кроме объединения таблиц с помощью запросов Power Query, такие операции можно выполнять и с помощью DAX-формул, применяя их в различных сочетаниях: GENERATEALL, NATURALLEFTOUTERJOIN, NATURALINNERJOIN, CROSSJOIN, FILTER+CROSSJOIN, GENERATE, EXCEPT и др.