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

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

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

原表

需要的結果

那使用Microsoft Query如何操作呢？

STEP 01 啟用Microsoft Query并加載數(shù)據(jù)

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

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

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

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

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

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

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

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

STEP 02 按需要項匹配數(shù)據(jù)

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

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

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

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

STEP 03 把結果數(shù)據(jù)返回到Excel工作表

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

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

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

返回結果如下：

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

額外之喜

我們發(fā)現(xiàn)Microsoft Query生成的數(shù)據(jù)就是一張超級表，也可以直接創(chuàng)建數(shù)據(jù)透視表或者數(shù)據(jù)透視圖。

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

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

這樣數(shù)據(jù)就同步過來了。

運用條件

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

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

相關學習推薦：excel教程