Как смоделировать полное внешнее объединение в Excel?

Question

Давайте предположим, что у меня есть некоторые данные в Excel (а не в реальной базе данных). На одном листе у меня есть данные, где один столбец работает как идентификатор, и я позаботился о том, чтобы значения в этом столбце были уникальными. На другом листе у меня также есть некоторые данные, опять же с одним столбцом, который может быть взят в качестве идентификатора, и он также уникален. Если строка N на листе 1 имеет какое-то значение, а строка M на листе 2 имеет то же значение, я уверен, что строка N и строка M описывают один и тот же объект реального мира.

Что я спрашиваю: как я могу получить эквивалент полного внешнего соединения без написания макросов? Формулы и все функции, доступные через ленту, в порядке.

Небольшой пример "игровых данных":

Лист 1:

Dostoyevski    Russia
Pushkin        Russia
Shelley        England
Flaubert       France
Hugo           France
Eichendorff    Germany
Byron          England
Zola           France

Лист 2:

Shelley        Percy Bysshe
Eichendorff    Josef Freiherr Von
Flaubert       Gustave
Byron          Lord
Keller         Gottfried
Dostoyevski    Fyodor
Zola           Emile
Balzac         Honoré de

Желаемый вывод (сортировка не важна):

Dostoyevski    Russia   Fyodor
Pushkin        Russia
Shelley        England  Percy Bysshe
Flaubert       France   Gustave
Hugo           France
Eichendorff    Germany  Josef Freiherr von
Byron          England  Lord
Zola           France   Emile
Keller                  Gottfried
Balzac                  Honoré de

---

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

Answer 1

Простой подход - стандартные операции Excel

Сначала скопируйте / вставьте оба ключевых столбца из обеих таблиц в один новый лист в виде одного столбца.

Используйте "Удалить дубликаты", чтобы получить единый список всех ваших уникальных ключей.

Затем добавьте два столбца (в данном случае), по одному для каждого столбца данных в каждой таблице. Я также рекомендую использовать формат в качестве табличного параметра, поскольку он делает ваши формулы более привлекательными. Используя vlookup, используйте следующую формулу:

=IFERROR(VLOOKUP([@ID],Sheet4!A:B,2,FALSE),"")

Где Sheet4!A:B представляет собой таблицу данных исходной таблицы для каждого соответствующего значения. IFERROR предотвращает появление уродливых результатов # N/A, которые возникают, когда vlookup не удается, и в этом случае возвращает пустую ячейку.

Это дает вам вашу итоговую таблицу.

---

Sheet3:

Лист4:

Данные результата:

Формулы результата (Ctrl+~ переключит это):

---

Встроенный SQL-запрос

Вы также можете сделать это с помощью встроенного SQL-запроса. Это ... гораздо менее удобно для пользователя, но, возможно, будет лучшим вариантом использования. Это, вероятно, потребует от вас отформатировать ваши "исходные" данные в виде таблиц.

1. Нажмите на ячейку на новом листе
2. Перейти к данным -> Из других источников -> Из запроса Microsoft
3. Выберите файлы Excel * на вкладке Базы данных и нажмите ОК
4. Выберите вашу рабочую книгу
5. Выберите следующие четыре поля:
   • 
6. Нажмите "Далее" и "ОК", чтобы увидеть красивое отформатированное предупреждение 1990-х годов.
7. Следуя этим инструкциям, создайте первое левое внешнее соединение. В моем случае я использую таблицу "страны" в качестве левого источника и "имена" в качестве правого.
   • 
   • Это дает только некоторые строки (так как вы присоединяетесь к ID)

8. Часть "создать соединение вычитания, а затем добавить его как объединение" более сложна ..

   • Вот конфигурации вычитания соединения:
   • Скопируйте SQL этого соединения из кнопки SQL:

   • ВЫБЕРИТЕ countries$ID, countries$ .Val1, names$ .ID, names$ .Val2 FROM {oj C:\Users\Username\Desktop\Book2.xlsx . countries$ countries$ ВЛЕВО НАРУШЕНИЕ C:\Users\Username\Desktop\Book2.xlsx . names$ names$ ON countries$ .ID = names$ .ID} ГДЕ (names$ .ID является нулевым)

9. Вернитесь к первому внешнему соединению, которое вы создали. Вручную отредактируйте SQL и

   • добавить Union внизу
   • Добавьте приведенный выше текст объединения вычитания в конец объединения
10. Нажмите кнопку "Возврат данных" сразу слева от кнопки SQL.
    • Вы можете отредактировать SQL, чтобы выбрать только конкретные данные, которые вы хотите на данный момент. Мне проще скрыть столбцы в результате.
11. Разместите запрос и подтвердите его местоположение.
    • 

Не для слабонервных. Но если вы хотите получить отличную возможность увидеть некоторые не обновленные до тех пор, пока вы можете быть живыми части Office, это отличный шанс.

Answer 2

В качестве альтернативного решения, могу ли я предложить Power Query? Это бесплатная надстройка Excel от Microsoft, предназначенная для выполнения именно такого рода задач. Его функциональные возможности будут непосредственно включены в Excel 2016, так что он защищен от будущего.

В любом случае, с Power Query, шаги довольно просты:

1. Импортируйте обе таблицы как запросы в Power Query Editor.
2. Выполните преобразование «Запросы слияния» для них, задав соответствующий столбец соединения и установив тип соединения как «Полное внешнее».
3. Загрузите таблицу результатов на новый лист.

Хорошая вещь об этом, как только вы настроите это, если вы вносите изменения в свои базовые таблицы данных, вы просто нажимаете Данные> Обновить все, и ваш лист результатов Power Query также обновляется.