Как объединить две таблицы в Excel, как в SQL?
У меня есть две таблицы в двух разных файлах Excel. Они оба содержат список имен, идентификационных номеров и связанных данных. Один представляет собой основной список, который включает в себя общие демографические поля, а другой представляет собой список, который включает только имя и идентификатор и адрес. Этот список был исключен из основного списка другим офисом.
Я хочу использовать 2-й список для фильтрации первого. Кроме того, я хочу, чтобы результаты включали другие поля из главной таблицы вместе с полями адреса из второй таблицы. Я знаю, как можно легко это сделать с помощью внутреннего соединения с базой данных, но я не совсем понимаю, как это сделать эффективно в Excel. Как можно объединить две таблицы в Excel? Бонусные баллы за показ того, как выполнять внешние объединения, и я бы предпочел знать, как это сделать без макроса.
10 ответов
Для 2007+ использования Data > From Other Sources > From Microsoft Query :
- выбирать
Excel Fileи выберите свой первый Excel - выбрать столбцы
(если вы не видите списка столбцов, обязательно проверьтеOptions>System Tables) - идти к
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
Поддержите принятый ответ. Я просто хочу подчеркнуть "выбрать столбцы (если вы не видите списка столбцов, обязательно проверьте Параметры> Системные таблицы)"
Как только вы выберете файл Excel, очень вероятно, что вы увидите this data source contains no visible tables подсказки, а доступные вкладки и столбцы отсутствуют. Microsoft признала, что ошибка в том, что вкладки в файлах Excel рассматриваются как "Системные таблицы", а опция "Системные таблицы" по умолчанию не выбрана. Так что не паникуйте на этом шаге, вам просто нужно нажать "опция" и проверить "Системные таблицы", после чего вы увидите доступные столбцы.
VLOOKUP и HLOOKUP могут использоваться для поиска совпадающих первичных ключей (хранящихся вертикально или горизонтально) и возврата значений из столбцов / строк "атрибута".
Вы можете использовать Microsoft Power Query, доступный для более новых версий Excel (аналогично принятому ответу, но гораздо проще и проще). Power Query вызывает объединения "слияния".
Самый простой способ - это получить 2 листа Excel в виде таблиц Excel. Затем в Excel перейдите на вкладку ленты Power Query и нажмите кнопку "Из Excel". После того, как вы импортировали обе таблицы в Power Query, выберите одну и нажмите "Объединить".
Хотя я думаю, что ответ Aprillion с использованием Microsoft Query превосходен, это вдохновило меня на использование Microsoft Access для присоединения к таблицам данных, которые мне показались намного проще.
Конечно, вам нужно установить MS Access.
шаги:
- Создайте новую базу данных Access (или используйте чистую базу данных).
- использование
Get External Dataимпортировать данные Excel в виде новых таблиц. - использование
Relationshipsчтобы показать, как ваши таблицы объединяются. - Установите тип отношений, чтобы соответствовать тому, что вы хотите (представляющий левое соединение и т. Д.)
- Создайте новый запрос, который объединит ваши таблицы.
- использование
External Data->Export to Excelгенерировать ваши результаты.
Я действительно не мог бы сделать это без отличного ответа Aprillion.
Вы не можете предварительно сформировать соединения в стиле SQL в таблицах Excel из Excel. Тем не менее, есть несколько способов выполнить то, что вы пытаетесь сделать.
В Excel, как говорит Рувим, формулы, которые, вероятно, будут работать лучше, VLOOKUP а также HLOOKUP, В обоих случаях вы сопоставляете уникальную строку, и она возвращает значение данного столбца \ строки влево \ вниз от найденного идентификатора.
Если вы хотите добавить только пару дополнительных полей во второй список, добавьте формулы во второй список. Если вы хотите таблицу стилей "external join", добавьте VLOOKUP формула в первый список с ISNA проверить, был ли поиск найден. Если в справке Excel недостаточно подробностей о том, как их использовать в вашем конкретном случае, сообщите нам об этом.
Если вы предпочитаете использовать SQL, то свяжите данные с программой базы данных, создайте запрос и экспортируйте результаты обратно в Excel. (В Access вы можете импортировать таблицы Excel или именованные диапазоны в виде связанной таблицы.)
На 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 не поддерживаются).
Вот пример:
Для пользователей Excel 2007: Данные> Из других источников> Из Microsoft Query > перейдите к файлу Excel
Согласно этой статье, запрос из XLS версии 2003 может привести к "Этот источник данных не содержит видимых таблиц". ошибка, потому что ваши рабочие листы обрабатываются как таблица SYSTEM. Поэтому проверяйте параметры "Системные таблицы" в диалоговом окне "Мастер запросов - выберите столбцы", когда вы создаете запрос, который будет работать.
Чтобы определить ваше объединение: диалоговое окно Microsoft Query > меню "Таблица"> "Объединения..."
Чтобы вернуть данные на исходный лист Excel, выберите "Вернуть данные в лист Excel" в диалоговом окне Microsoft Query > меню "Файл".
В поисках той же проблемы я наткнулся на RDBMerge, который, на мой взгляд, является удобным для пользователя способом объединения данных из нескольких рабочих книг Excel, файлов CSV и XML в сводную рабочую книгу.
Если вы достаточно знакомы с базами данных, вы можете использовать SQL Server для подключения обеих таблиц в качестве связанных серверов, а затем использовать T-SQL для выполнения ваших внутренних данных. Затем закончите, подключив Excel обратно к SQL и вытяните данные в таблицу (обычную или сводную). Вы также можете рассмотреть возможность использования Powerpivot; это позволит объединять любые источники базы данных, включая Excel, используемый в качестве плоских баз данных.