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跨表提取，Microsoft Query KO一切函數的詳細內容，更多請關注TW511.COM其它相關文章！