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

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

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

原表

需要的結(jié)果

那使用Microsoft Query如何操作呢？

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

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

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

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

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

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

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

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

這時(shí)又會(huì)彈出一個(gè)窗口，提示““查詢向?qū)А睙o(wú)法繼續(xù)，因?yàn)樵摫砀駸o(wú)法鏈接到您的查詢中。您必須在Microsoft Query中的表格之間拖動(dòng)字段，人工鏈接?！边@個(gè)也不用管，點(diǎn)擊確定。

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

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

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

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

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

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

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

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

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

返回結(jié)果如下：

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

額外之喜

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

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

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

這樣數(shù)據(jù)就同步過(guò)來(lái)了。

運(yùn)用條件

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

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

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