excel 運用Query制作多表動態查詢銷售業績清單的技術

借某單位的銷售數據分享一篇Microsoft Query做多表動態查詢銷售業績清單的技術，解救各位銷售助理、銷售文員。下面以銷售人員業績動態查詢為例介紹Microsoft Query多表動態查詢制作。如下圖，桌面“銷售數據”文件夾里，包含了11月5日到11月7日三天的銷售數據。

?

每個工作簿里的內容都是當天所有業務人員的銷售數據。三個工作簿字段名都是一致的。

?

我們要做的動態跨表查詢如下：

?

根據上圖我們能看到，改變業務人員的姓名，下方數據區域就會自動篩選出三個工作表里該業務人員的銷售記錄。這里為了方便查詢，姓名使用數據驗證下拉菜單來顯示。當然在單元格直接輸入姓名也可以達到同樣的效果。

這是怎么做到的呢？來跟我一起學習吧！

Step 01 建立查詢表

保存關閉三個工作簿，新建工作簿，在A1單元格輸入“業務人員”，A2單元格輸入其中一個業務員的姓名“楊光”（這里也可以建立下拉菜單，有興趣的小伙伴可以自己試試）。

?

Step 02 初步指定查詢的數據源

點擊【數據】選項卡下【獲取外部數據】組里“自其他來源”下拉菜單的“來自Microsoft Query”。

?

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

?

在【選擇工作簿】窗口右側目錄里找到數據源所在的文件夾，在左側數據庫名找到其中一個工作簿，點擊確定。

?

接下來有兩種可能：系統彈出提示窗口和不彈出提示窗口。彈出提示從（1）開始，不彈出提示直接從（2）開始。

（1）系統提示“數據源中沒有包含可見的表格”，如下，直接點擊確定。

?

進入【查詢向導-選擇列】窗口，點擊下面的“選項”按鈕，打開右側【表選項】窗口，勾選“系統表”點擊確定。

?

這樣【查詢向導-選擇列】窗口中就會出現數據源里的工作表了。

（2）在【查詢向導-選擇列】窗口中選中工作表點擊中間的“>”按鈕把左側的“可用的表和列”添加到右側的“查詢結果中的列”，點擊下一步。

?

進入【查詢向導-篩選數據】窗口，不用操作，點擊下一步。

?

進入【查詢向導-排序順序】窗口，同樣不用操作，點擊下一步。

?

進入【查詢向導-完成】窗口，選擇“將數據返回Microsoft Excel”，點擊完成。

?

Step 03 指定所有查詢數據并設置查詢條件

在EXCEL中出現【導入數據】窗口，我們選擇顯示為“表”，位置放置在現有工作表。點擊下方左側的“屬性”按鈕。

?

打開【連接屬性】窗口，在“定義”選項卡里的“命令文本”輸入框中輸入下列文本。注意，讀者應該根據自己的文件修改工作簿路徑和工作表名、查詢條件。

SELECT * FROM [C:UsersAdministratorDesktop銷售數據11.5銷售數據.xlsx].[Sheet1$] where 業務人員=? union all

SELECT * FROM [C:UsersAdministratorDesktop銷售數據11.6銷售數據.xlsx].[Sheet1$] where 業務人員=? union all

SELECT * FROM [C:UsersAdministratorDesktop銷售數據11.7銷售數據.xlsx].[Sheet1$] where 業務人員=?

?

解析：

SELECT * FROM [C:UsersAdministratorDesktop銷售數據11.5銷售數據.xlsx].[Sheet1$] where 業務人員=? union all

這里使用了SQL語句。意思是根據業務人員的名稱在指定的工作表中查詢表中所有列數據。

SELECT 列名 ：指定要查詢的數據列，譬如“SELECT [Sheet1$].日期, [Sheet1$].商品代碼, [Sheet1$].金額”，就表示只查詢、顯示Sheet1表中日期、商品代碼、金額三列數據。當前“SELECT *”，“*”是通配符，代表所有列，也就是表格中所有內容。

FROM 表名稱：指定要查詢的工作簿以及工作表名，由兩部分組成[].[]，前部分是工作簿路徑，后部分是工作表名稱。譬如當前“FROM [C:UsersAdministratorDesktop銷售數據11.5銷售數據.xlsx].[Sheet1$]”就表示查詢工作簿“11.5銷售數據.xlsx”中的Sheet1工作表。讀者需要根據自己的文件路徑來設置。

where 查詢條件：指定按什么條件進行查詢。譬如“where 業務人員=?”表示按輸入的業務人員的姓名進行查詢。因為需要按輸入的內容進行查詢，所以后續需要指定輸入的位置。

union all：指的是把查詢的下一個工作表的結果與當前的查詢連接排列。最后一個工作表就不能寫這句代碼了。譬如當前最后一個工作簿“11.7銷售數據.xlsx”后就沒有“union all”語句。

注意：這里的所有符號必須是英文狀態下的。

Step 04 指定查詢條件的輸入位置

點擊兩次確定。彈出【輸入參數值】窗口，鼠標單擊B1單元格在參數1中指定查詢條件輸入位置“=Sheet1!$B$1”。分別勾選“在以后的刷新中使用該值或該引用”和“當單元格值更改時自動刷新”復選項，點擊確定。

?

然后用同樣的方式設置參數2和參數3。這樣就完成了。

?

后續當我們在B1單元格輸入其他業務員名字時，下方就能自動刷新出對應結果了。不過輸入的時候，名字要完全正確系統才能查詢到。

Microsoft Query動態查詢

優勢和不足

使用Microsoft Query進行動態查詢具有以下優勢：

◎不用記函數

◎避免VLOOKUP等函數無法一對多查詢的弊端

使用Microsoft Query進行動態查詢的不足：

如果數據表很多，逐個定義連接屬性和參數很繁瑣的。

適用條件

使用Microsoft Query建立跨表動態查詢需要滿足下方條件：

◎各個表的字段一致

◎各個表中不能有其他無關數據

后續操作

如果后續需要增加或者減少查詢數據源怎么辦？

（1）點中查詢結果的某個單元格，單擊“數據”選項卡“連接”功能組中的“屬性”按鈕。

?

（2）在彈出的“外部數據屬性”對話框中單擊“連接屬性”按鈕 。

?

（3）進入“連接屬性”對話框，單擊“定義”選項卡，修改“命令文本”中的語句，增減工作表即可。