Excel跨表擷取，Microsoft Query KO一切函數

跨表擷取資料很多夥伴第一反應就是函數如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教學

以上是Excel跨表擷取，Microsoft Query KO一切函數的詳細內容。更多資訊請關注PHP中文網其他相關文章！