Как объединить две таблицы в Excel, как в SQL?

Question

У меня есть две таблицы в двух разных файлах Excel. Они оба содержат список имен, идентификационных номеров и связанных данных. Один представляет собой основной список, который включает общие демографические поля, а другой - список, который включает только имя и идентификатор, а также адрес. Этот список был исключен из основного списка другим офисом.

Я хочу использовать 2-й список для фильтрации первого. Кроме того, я хочу, чтобы результаты включали другие поля из главной рабочей таблицы вместе с полями адреса из второй рабочей таблицы. Я знаю, как можно легко это сделать с помощью внутреннего соединения с базой данных, но я не совсем понимаю, как это сделать эффективно в Excel. Как можно объединить две таблицы в Excel? Бонусные баллы за то, что вы показали, как делать внешние соединения, и я бы предпочел знать, как это сделать без макроса.

Answer 1

Для 2007+ используйте Data > From Other Sources > From Microsoft Query:

1. выберите Excel File и выберите свой первый Excel
2. выбрать столбцы
   (если вы не видите списка столбцов, обязательно проверьте Options > System Tables)
3. перейдите в Data > Connections > [выберите только что созданное соединение]> Properties > Definition > Command text

Теперь вы можете редактировать этот Command text как SQL. Не уверен, какой синтаксис поддерживается, но я пробовал неявные объединения, "внутреннее соединение", "левое соединение" и объединения, которые все работают. Вот пример запроса:

SELECT *
FROM `C:\Users\Peter\Documents\Excel-to-excel\Source_1.xlsx`.`Sheet1$` a
LEFT JOIN `C:\Users\Peter\Documents\Excel-to-excel\Source_2.xlsx`.`Sheet1$` b
ON a.col2 = b.col2

Answer 2

Поддержите принятый ответ. Я просто хочу подчеркнуть "выбрать столбцы (если вы не видите списка столбцов, обязательно проверьте Параметры> Системные таблицы)"

После выбора файла Excel, скорее всего, вы увидите, что this data source contains no visible tables подсказок видимых таблиц , а доступные вкладки и столбцы отсутствуют. Microsoft признала, что ошибка в том, что вкладки в файлах Excel рассматриваются как "Системные таблицы", а параметр "Системные таблицы" по умолчанию не выбран. Так что не паникуйте на этом шаге, вам просто нужно нажать "опция" и проверить "Системные таблицы", после чего вы увидите доступные столбцы.

Answer 3

VLOOKUP и HLOOKUP могут использоваться для поиска совпадающих первичных ключей (хранящихся вертикально или горизонтально) и возврата значений из столбцов / строк «атрибута».

Answer 4

Вы можете использовать Microsoft Power Query, доступный для более новых версий Excel (аналогично принятому ответу, но гораздо проще и проще). Power Query вызывает объединения «слияния».

Самый простой способ - это получить 2 листа Excel в виде таблиц Excel. Затем в Excel перейдите на вкладку ленты Power Query и нажмите кнопку «Из Excel». После того, как вы импортировали обе таблицы в Power Query, выберите одну и нажмите «Объединить».

Answer 5

Вы не можете предварительно сформировать соединения в стиле SQL в таблицах Excel из Excel. Тем не менее, есть несколько способов выполнить то, что вы пытаетесь сделать.

В Excel, как говорит Рувим, формулы, которые, вероятно, будут работать лучше всего, это VLOOKUP и HLOOKUP . В обоих случаях вы сопоставляете уникальную строку, и она возвращает значение данного столбца \ строки влево \ вниз от найденного идентификатора.

Если вы хотите добавить только пару дополнительных полей во второй список, добавьте формулы во второй список. Если вам нужна таблица стилей "external join", добавьте формулу VLOOKUP в первый список с ISNA чтобы проверить, был ли найден поиск. Если в справке Excel недостаточно подробностей о том, как использовать их в вашем конкретном случае, сообщите нам об этом.

Если вы предпочитаете использовать SQL, то свяжите данные с программой базы данных, создайте запрос и экспортируйте результаты обратно в Excel. (В Access вы можете импортировать таблицы Excel или именованные диапазоны в виде связанной таблицы.)

Answer 6

На XLTools.net мы создали хорошую альтернативу для MS Query, особенно для работы с SQL-запросами к таблицам Excel. Это называется XLTools SQL Queries. Его гораздо проще использовать, чем MS Query, и он работает действительно хорошо, если вам просто нужно создать и запустить SQL - без VBA, без сложных манипуляций с MS Query ...

С помощью этого инструмента вы можете создать любой SQL-запрос к таблицам в книгах Excel с помощью встроенного редактора SQL и сразу же запустить его с возможностью поместить результат на новый или любой существующий лист.

Вы можете использовать практически любой тип соединения, включая LEFT OUTER JOIN (только RIGHT OUTER JOIN и FULL OUTER JOIN не поддерживаются).

Вот пример:

Answer 7

Хотя я думаю, что ответ Aprillion с использованием Microsoft Query превосходен, я вдохновил меня использовать Microsoft Access, чтобы присоединиться к таблицам данных, которые мне показались намного проще.

Конечно, вам нужно установить MS Access.

шаги:

• Создайте новую базу данных Access (или используйте чистую базу данных).
• Используйте Get External Data чтобы импортировать данные Excel в виде новых таблиц.
• Используйте Relationships чтобы показать, как соединяются ваши таблицы.
• Установите тип Отношения, чтобы соответствовать тому, что вы хотите (представляющий левое соединение и т.д.)
• Создайте новый запрос, который объединит ваши таблицы.
• Используйте External Data->Export to Excel чтобы сгенерировать результаты.

Я действительно не мог бы сделать это без отличного ответа Aprillion.

Answer 8

Для пользователей Excel 2007: Данные> Из других источников> Из Microsoft Query> перейдите к файлу Excel

Согласно этой статье, запрос из XLS версии 2003 может привести к "Этот источник данных не содержит видимых таблиц". ошибка, потому что ваши рабочие листы обрабатываются как таблица SYSTEM. Поэтому проверяйте параметры "Системные таблицы" в диалоговом окне «Мастер запросов - выберите столбцы» при создании запроса.

Чтобы определить ваше объединение: Диалог Microsoft Query> Меню таблицы> Объединения ...

Чтобы вернуть данные на исходный лист Excel, выберите "Вернуть данные в лист Excel" в диалоговом окне Microsoft Query> меню "Файл".

Answer 9

Если вы достаточно хорошо знакомы с базами данных, вы можете использовать SQL Server для соединения обеих таблиц в качестве связанных серверов, а затем использовать T-SQL для выполнения ваших внутренних данных. Затем закончите, подключив Excel обратно к SQL и перетащите данные в таблицу (обычную или сводную). Вы также можете рассмотреть возможность использования Powerpivot; это позволит объединять любые источники базы данных, включая Excel, используемый в качестве плоских баз данных.

Answer 10

В поисках той же проблемы я наткнулся на RDBMerge, который, как мне кажется, представляет собой удобный способ объединения данных из нескольких рабочих книг Excel, файлов CSV и XML в сводную рабочую книгу .