БЛОГ

Только качественные посты

Смотрите видео к статье:

Или операторы объединения таблиц SQL JOIN в Excel Power Query (начиная с Excel 2016)

Как известно самым популярным и эффективным инструментом работы с табличными данными (или просто таблицами) является программа Microsoft Excel.

При этом также известно, что самым мощным и распространённым языком программирования для работы с табличными данными является язык Structured Query Language = SQL = Язык структурированных запросов

Исходя из этого факта, логично предположить, что Microsoft Excel должен поддерживать язык SQL по умолчанию. Но, как правило, SQL поддерживается только базами данных (СУБД).

Несмотря на это, для Excel (начиная с 2010 версии) появилась бесплатная надстройка Power Query, которая позволяет имитировать часть полезного функционала языка SQL, а именно:

В языке SQL есть очень интересный оператор «SELECT», который позволяет делать запросы к табличным данным в базе данных. В результате запроса возвращается набор данных (выборка из базы данных), удовлетворяющий заданному условию выборки.

Как правило, выборка данных делается из нескольких таблиц в базе данных. Для связи таблиц в языке программирования SQL существует оператор «JOIN», который выполняет различные операции соединения реляционных таблиц (в основе этого принципа лежат законы реляционной алгебры)

Различают следующие виды оператора «JOIN»:

  • INNER JOIN — Оператор внутреннего соединения двух таблиц
  • LEFT OUTER JOIN — Оператор левого внешнего соединения двух таблиц
  • RIGHT OUTER JOIN — Оператор правого внешнего соединения двух таблиц
  • FULL OUTER JOIN — Оператор полного внешнего соединения двух таблиц
  • CROSS JOIN — Оператор перекрёстного соединения (декартово произведение) двух таблиц

С выходом надстройки Power Query для Excel (это один из инструментов уровня Self-Service BI) в Excel появилась поддержка функционала всех видов операторов «JOIN» языка SQL:
SQL_JOIN_POWER_QUERY_BIWEB

Рассмотрим операторов «JOIN» в Excel на примерах:

Представим, что у нас в Excel есть две Таблицы: A «Люди» и B «Города»
SQL_JOIN_POWER_QUERY_BIWEB

Теперь давайте объединим данные таблицы с помощью различных операторов «JOIN»:
(объединяем таблицы через столбцы: A.Cityid = B.id)

Оператор INNER JOIN вернет следующий результат:
SQL_JOIN_POWER_QUERY_BIWEB

Оператор LEFT JOIN вернет следующий результат:
SQL_JOIN_POWER_QUERY_BIWEB

Оператор RIGHT JOIN вернет следующий результат:
SQL_JOIN_POWER_QUERY_BIWEB

Оператор FULL OUTER JOIN вернет следующий результат:
SQL_JOIN_POWER_QUERY_BIWEB

Оператор CROSS JOIN вернет следующий результат:
SQL_JOIN_POWER_QUERY_BIWEB

В надстройке Power Query для Excel данная функция «JOIN» называется «Слияние» — слияние запросов, где:

Оператору слияния INNER JOIN соответствует тип соединения: Внутреннее (только совпадающие строки)
SQL_JOIN_POWER_QUERY_BIWEB

Оператору слияния LEFT JOIN соответствует тип соединения: Внешнее соединение слева (все из первой таблицы, совпадающие из второй)
SQL_JOIN_POWER_QUERY_BIWEB

Оператору слияния RIGHT JOIN соответствует тип соединения: Внешнее соединение справа (все из второй таблицы, совпадающие из первой)
SQL_JOIN_POWER_QUERY_BIWEB

Оператору слияния FULL OUTER JOIN соответствует тип соединения: Полное внешнее (все строки из обеих таблиц)
SQL_JOIN_POWER_QUERY_BIWEB

Оператора слияния CROSS JOIN в интерфейсе Power Query нет, но его можно создать из оператора слияния FULL OUTER JOIN, убрав связи таблиц
SQL_JOIN_POWER_QUERY_BIWEB

Скачать Excel файл с примерами объединения SQL JOIN (функция «Слияние» в Power Query) можно скачать здесь

Пошаговая инструкция использования функции «Слияние»/«Объединения» в Power Query находится в видеоуроке к данной статье

5 комментарий

  • Здравствуйте, Сергей, очень хорошая статья, спасибо большое! А есть какие-то рекомендации, как лучше всего делать, если есть три или более таблицы у которых один и тот же ключ (причем, конечно, соблюдается условие уникальности записей по этому ключу в каждой таблице) и необходимо создать результирующую таблицу с определенными полями в которой были бы все «ключи» из исходных таблиц?

    Ответить
    • Константин,

      Универсальных рекомендация нет, все зависит от конечной цели/задачи..

      Чаще всего используется соединение «многие к одному», где:

      «многие» — это внешние ключи таблиц «фактов», а «к одному» – это первичные и уникальные ключи «справочников»

      Посмотрите еще вот эту статью может поможет: http://biweb.ru/bi-dwh-and-excel.html

      Ответить
  • Здравствуйте, Сергей. Очень хорошая статья: Коротко, понятно и полезно. Спасибо большое!

    Ответить
  • Здравствуйте.
    Спасибо за статью, ищу похожее решение. Подскажите, как можно корректно использовать оператор полного слияния или что-то подобное для сверок? данные в таблицах могут повторяться. Например в таблице А есть номер заказа 1 с суммой Х. и в другой строке такой же номер заказа 1 с суммой Х. оба они верные. в таблице В есть также номер заказа 1 с суммой Х и в еще одной строке заказ 1 с суммой У. цель получить в одной строке из таблицы А заказ 1 сумма Х, напротив из таблицы В заказ 1 сумма Х. во второй строке из таблицы А заказ 1 сумма Х, а напротив из таблицы В заказ 1 сумма У. оператор полного слияния дублирует строки. поставит 4 строки с повторяющимися суммами.

    Ответить
  • Хочу сводную таблицу преобразовать в обычную таблицу с тремя полями. А как? Ищу ответ в Интернете, но ещё не нашёл.

    Ответить