Смотрите видео к статье:
Как известно самым популярным и эффективным инструментом работы с табличными данными (или просто таблицами) является программа Microsoft Excel.
При этом также известно, что самым мощным и распространённым языком программирования для работы с табличными данными является язык Structured Query Language = SQL = Язык структурированных запросов
Исходя из этого факта, логично предположить, что Microsoft Excel должен поддерживать язык SQL по умолчанию. Но, как правило, SQL поддерживается только базами данных (СУБД).
В языке SQL есть очень интересный оператор «SELECT», который позволяет делать запросы к табличным данным в базе данных. В результате запроса возвращается набор данных (выборка из базы данных), удовлетворяющий заданному условию выборки.
Как правило, выборка данных делается из нескольких таблиц в базе данных. Для связи таблиц в языке программирования SQL существует оператор «JOIN», который выполняет различные операции соединения реляционных таблиц (в основе этого принципа лежат законы реляционной алгебры)
С выходом надстройки Power Query для Excel (это один из инструментов уровня Self-Service BI) в Excel появилась поддержка функционала всех видов операторов «JOIN» языка SQL:
Представим, что у нас в Excel есть две Таблицы: A «Люди» и B «Города»
Теперь давайте объединим данные таблицы с помощью различных операторов «JOIN»:
(объединяем таблицы через столбцы: A.Cityid = B.id)
Оператор INNER JOIN вернет следующий результат:
Оператор LEFT JOIN вернет следующий результат:
Оператор RIGHT JOIN вернет следующий результат:
Оператор FULL OUTER JOIN вернет следующий результат:
Оператор CROSS JOIN вернет следующий результат:
Оператору слияния INNER JOIN соответствует тип соединения: Внутреннее (только совпадающие строки)
Оператору слияния LEFT JOIN соответствует тип соединения: Внешнее соединение слева (все из первой таблицы, совпадающие из второй)
Оператору слияния RIGHT JOIN соответствует тип соединения: Внешнее соединение справа (все из второй таблицы, совпадающие из первой)
Оператору слияния FULL OUTER JOIN соответствует тип соединения: Полное внешнее (все строки из обеих таблиц)
Оператора слияния CROSS JOIN в интерфейсе Power Query нет, но его можно создать из оператора слияния FULL OUTER JOIN, убрав связи таблиц
Скачать Excel файл с примерами объединения SQL JOIN (функция «Слияние» в Power Query) можно скачать здесь
Пошаговая инструкция использования функции «Слияние»/«Объединения» в Power Query находится в видеоуроке к данной статье
Здравствуйте, Сергей, очень хорошая статья, спасибо большое! А есть какие-то рекомендации, как лучше всего делать, если есть три или более таблицы у которых один и тот же ключ (причем, конечно, соблюдается условие уникальности записей по этому ключу в каждой таблице) и необходимо создать результирующую таблицу с определенными полями в которой были бы все «ключи» из исходных таблиц?
Константин,
Универсальных рекомендация нет, все зависит от конечной цели/задачи..
Чаще всего используется соединение «многие к одному», где:
«многие» — это внешние ключи таблиц «фактов», а «к одному» – это первичные и уникальные ключи «справочников»
Посмотрите еще вот эту статью может поможет: http://biweb.ru/bi-dwh-and-excel.html
Здравствуйте, Сергей. Очень хорошая статья: Коротко, понятно и полезно. Спасибо большое!
Здравствуйте.
Спасибо за статью, ищу похожее решение. Подскажите, как можно корректно использовать оператор полного слияния или что-то подобное для сверок? данные в таблицах могут повторяться. Например в таблице А есть номер заказа 1 с суммой Х. и в другой строке такой же номер заказа 1 с суммой Х. оба они верные. в таблице В есть также номер заказа 1 с суммой Х и в еще одной строке заказ 1 с суммой У. цель получить в одной строке из таблицы А заказ 1 сумма Х, напротив из таблицы В заказ 1 сумма Х. во второй строке из таблицы А заказ 1 сумма Х, а напротив из таблицы В заказ 1 сумма У. оператор полного слияния дублирует строки. поставит 4 строки с повторяющимися суммами.
Хочу сводную таблицу преобразовать в обычную таблицу с тремя полями. А как? Ищу ответ в Интернете, но ещё не нашёл.