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

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

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

原表

需要的結(jié)果

那使用Microsoft Query如何操作呢？

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

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

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

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

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

進(jìn)入下方左側(cè)的【查詢向?qū)А看翱?，點擊下面的“選項”按鈕，打開右側(cè)【表選項】窗口，勾選“系統(tǒng)表”點擊確定。

這樣【查詢向?qū)А看翱诰蜁霈F(xiàn)數(shù)據(jù)源里的工作表了。這是由于Excel把自己的工作表叫做“系統(tǒng)表”，勾選了之后在查詢窗口就能看到了。

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

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

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

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

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

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

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

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

最后要做的就是把結(jié)果返回到EXCEL。

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

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

返回結(jié)果如下：

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

額外之喜

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

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

在返回結(jié)果上右鍵點擊刷新。

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

運用條件

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

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

相關(guān)學(xué)習(xí)推薦：excel教程