題 如何在SQL中加入Excel中的兩個工作表?


我在兩個不同的Excel文件中有兩個工作表。它們都包含名稱,ID號和相關數據的列表。一個是包含一般人口統計字段的主列表,另一個是僅包含名稱和ID以及地址的列表。這個清單是由另一個辦公室從主清單中削減的。

我想使用第二個列表來過濾第一個。此外,我希望結果包括主工作表中的其他字段以及第二個工作表中的地址字段。我知道如何使用數據庫內連接很容易地做到這一點,但我不清楚如何在Excel中有效地執行此操作。如何在Excel中加入兩個工作表?顯示如何進行外連接的加分點,我更希望知道如何在不需要宏的情況下執行此操作。


110
2018-05-04 16:20


起源


我發現以下頁面非常有用: randomwok.com/excel/how-to-use-index-match - Tommy Bravo
randomwok.com/excel/how-to-use-index-match =>特別是最後的簡單“提醒”: =INDEX ( Column_I_want_a_return_value_from , ( MATCH ( My_Lookup_Value , Column_I_want_to_Lookup_against , 0 )) - Tommy Bravo


答案:


適合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

151
2018-05-07 09:37



有沒有辦法避免硬編碼路徑?相對路徑會起作用嗎? - Rekin
作為額外的獎勵,Microsoft Query允許您體驗16位樣式的通用控件,用於打開Excel文件和消息框。這樣你就可以記得你年輕的時候。 :-) - Edward Brey
這適用於CSV文件嗎?我正在使用MS Office Professional Plus 2010並且看不到如何執行第3步 - 我是否將第2步中的對話框掛起?在我的“連接”對話框中沒有“選擇新連接”。 - John Freeman
@JohnFreeman因為你必須這樣做 選擇 根據您的需求提供的其中一個選項(例如'添加...') - Aprillion
請注意,您可能會出現一個對話框,指出“此數據源不包含可見表”,然後是“查詢嚮導 - 選擇列”對話框。它來自 這個 要在“選項”按鈕上單擊的對話框,然後選中“系統表”複選框以查看要查詢的數據。 - Tola Odejayi


支持接受的答案。我只想強調“選擇列(如果您沒有看到任何列列表,請務必選中選項>系統表)”

一旦選擇了excel文件,很可能會看到 this data source contains no visible tables 提示,可用的選項卡和列均為無。微軟 承認這是一個錯誤  excel文件中的選項卡被視為“系統表”,默認情況下不選擇“系統表”選項。所以不要在這一步恐慌,只需要點擊“選項”並選中“系統表”,然後就會看到可用的列。


11
2017-12-09 18:20





VLOOKUP和HLOOKUP可用於搜索匹配的主鍵(垂直或水平存儲)並從“屬性”列/行返回值。


8
2018-05-04 16:22



對同一工作簿上的工作表非常有用(但我發現INDEX + MATCH函數更有用),從封閉的外部工作簿更新數據時稍微複雜一點...... - Aprillion


您無法在Excel中對Excel表格執行SQL樣式連接。也就是說,有多種方法可以實現您的目標。

在Excel中,就像Reuben所說的那樣,可能效果最好的公式是 VLOOKUP 和 HLOOKUP。在這兩種情況下,您匹配一個唯一的行,並從找到的ID返回給定列\行的值到左\下。

如果您只想在第二個列表中添加一些額外的字段,請將公式添加到第二個列表中。如果需要“外連接”樣式表,則添加 VLOOKUP 公式到第一個列表 ISNA 測試是否找到了查找。如果Excel的幫助沒有為您提供有關如何在特定實例中使用這些內容的詳細信息,請告訴我們。

如果您更喜歡使用SQL,則將數據鏈接到數據庫程序,創建查詢,然後將結果導出回Excel。 (在Access中,您可以將Excel工作表或命名範圍作為鏈接表導入。)


4
2018-05-04 17:29



實際上,你可以 - 它被稱為Microsoft Query - Aprillion


您可以使用Microsoft Power Query,可用於較新版本的Excel(類似於已接受的答案,但更簡單,更簡單)。 Power Query調用join'merges'。

最簡單的方法是將2張Excel表格作為Excel表格。然後在Excel中,轉到Power Query功能區選項卡,然後單擊“從Excel”按鈕。將兩個表導入Power Query後,選擇一個並單擊“合併”。


4
2018-05-17 14:09



比任何其他提議的選項容易得多。這應該是答案! 電源查詢 現已包含在Excel 2016下 數據 標籤。 - SliverNinja - MSFT
另一個在Mac上似乎不存在的功能 - juandesant


在XLTools.net,我們為MS Query創建了一個很好的替代方案,特別是針對Excel表的SQL查詢。它被稱為 XLTools SQL查詢。它比MS Query更容易使用,如果你只需要創建和運行SQL就行得很好 - 沒有VBA,沒有MS Query的複雜操作......

使用此工具,您可以使用嵌入式SQL編輯器對Excel工作簿中的表創建任何SQL查詢,並立即運行它,並將結果放在新工作表或任何現有工作表上。

您可以使用幾乎任何類型的連接,包括LEFT OUTER JOIN(不支持RIGHT OUTER JOIN和FULL OUTER JOIN)。

這是一個例子:

XLTools SQL Queries - Query Builder


3
2017-07-04 22:25





對於Excel 2007用戶: 數據>來自其他來源>來自Microsoft Query>瀏覽到Excel文件

根據 本文從XLS版本2003查詢可能會導致“此數據源不包含可見表”。錯誤,因為您的工作表被視為SYSTEM表。因此,在創建查詢時,請檢查“查詢嚮導 - 選擇列”對話框中的“系統表”選項是否有效。

要定義您的加入: Microsoft Query對話框>表格菜單>加入...

要將數據返回到原始Excel工作表,請從“Microsoft查詢”對話框>“文件”菜單中選擇“將數據返回到Excel工作表”。


2
2017-07-16 02:41





雖然我認為使用Microsoft Query的Aprillion的答案非常好,但它激勵我使用它 Microsoft Access 加入我發現的數據表更容易。

您當然需要安裝MS Access。

腳步:

  • 創建一個新的Access數據庫(或使用臨時數據庫)。
  • 使用 Get External Data 將Excel數據導入為新表。
  • 使用 Relationships 顯示您的表格如何連接。
  • 設置關係類型以匹配您想要的(表示左連接等)
  • 創建一個連接表的新查詢。
  • 使用 External Data->Export to Excel 生成結果。

沒有Aprillion的好答案,我真的不可能做到這一點。


2
2018-02-12 11:00