跨表提取數據很多伙伴第一反應就是函數如VLOOKUP，或者什么INDEX+SMALL+IF萬金油公式。其實，如果提取的是多列數據，有一個被很多人丟在旮旯里許久許久的Microsoft Query才是王者！它不但操作簡易，輕易解決“一對多”，而且它生成的結果表可以與數據源形成動態鏈接，數據源變化了，結果也會動態更新！

今天給大家分享一個很少人用但有奇效的功能—Microsoft Query來幫助大家解決兩個表格“一對多”的數據提取，或者說解決用一個表去匹配另一個表生成特定數據的做法。

如下圖所示，同一個工作簿里有兩個工作表，“部門人員信息表”列出了各部門的員工姓名和對應的主管，“省份銷售數據表”列出了每個員工負責的多個省份以及對應省份的三個月銷售數據。現在要求把兩個表根據姓名這列匯總到一個表里。

原表

需要的結果

那使用Microsoft Query如何操作呢？

STEP 01 啟用Microsoft Query并加載數據

（1）新建一個工作簿，點擊【數據】選項卡下【獲取外部數據】組里“自其他來源”下拉菜單的“來自Microsoft Query”。

在【選擇數據源】窗口“數據庫”選項下點擊“Excel Files”,勾選下方的“使用[查詢向導]創建/編輯查詢” ，點擊確定。

在【選擇工作簿】窗口右側目錄里找到數據源所在的位置，在左側數據庫名找到文件，點擊確定。

（2）有時系統會提示如下窗口：“數據源中沒有包含可見的表格”，這個不用管，點擊確定。

進入下方左側的【查詢向導】窗口，點擊下面的“選項”按鈕，打開右側【表選項】窗口，勾選“系統表”點擊確定。

這樣【查詢向導】窗口就會出現數據源里的工作表了。這是由于Excel把自己的工作表叫做“系統表”，勾選了之后在查詢窗口就能看到了。

接下來選中兩個工作表分別點擊中間的“>”按鈕把左側的“可用的表和列”添加到右側的“查詢結果中的列”，點擊下一步。

這時又會彈出一個窗口，提示““查詢向導”無法繼續，因為該表格無法鏈接到您的查詢中。您必須在Microsoft Query中的表格之間拖動字段，人工鏈接。”這個也不用管，點擊確定。

STEP 02 按需要項匹配數據

此時我們就進入Microsoft Query窗口，上方是類似EXCEL的菜單欄，中間是表區域，顯示了當前我們添加的兩個表以及對應的字段。下方的數據區域就是融合了兩個表的結果。

這時候數據區域的結果是雜亂無章的，原因是我們沒有給兩個表添加關系。兩個表里是通過姓名列來一一對應的。

（1）用鼠標選中左邊“部門人員信息表”中的“姓名”，將其拖曳到右表“省份銷售數據表”中的“姓名”上面，然后松開鼠標。這時在兩個表的“姓名”字段之間出現了一條兩端帶有細小節點的聯接線。下方數據區域就立即更新了。

（2）由于有兩列相同的姓名，我們選中其中一列，點擊菜單欄【記錄】下方的“刪除列”。

STEP 03 把結果數據返回到Excel工作表

最后要做的就是把結果返回到EXCEL。

（1）點擊菜單欄“SQL”左側的按鈕，將數據返回到Excel。

（2）在EXCEL中出現【導入數據】窗口，我們選擇顯示為“表”，位置放置在現有工作表。

返回結果如下：

到此簡單的3步我們完成了需要的數據匹配，生成了新的數據表。

額外之喜

我們發現Microsoft Query生成的數據就是一張超級表，也可以直接創建數據透視表或者數據透視圖。

同時，這張表是和數據源動態鏈接的。比如我們修改一下原數據，點擊保存關閉。

在返回結果上右鍵點擊刷新。

這樣數據就同步過來了。

運用條件

需要注意的是，使用這種方法，必須要保證數據源的規范性。要求工作表不能存在與數據源無關的數據，并且表格第一行為列標題。如果要實現動態鏈接，那么工作簿和工作表的名字和位置不能修改。

怎么樣，大家學會了嗎？是否比PQ簡單，比函數簡單？

相關學習推薦：excel教程