雙11過后銷售業(yè)績動態(tài)查詢清單這樣做最簡便
?
作者:夏雪來源:部落窩教育發(fā)布時間:2018-11-20 00:35:51點擊:3643
銷售經(jīng)理問助理:人事小王能夠做員工信息動態(tài)查詢表,為啥你不做一張員工銷售業(yè)績動態(tài)查詢表呀?助理無語:不是不想做,而是人事的數(shù)據(jù)源就一張表,每位員工信息還是唯一的,做查詢很簡單;銷售部門每周一張銷售明細(銷售流水),并且每張明細中每個銷售員存在多筆銷售,這種動態(tài)查詢難……今天部落窩就借某單位雙11銷售數(shù)據(jù)分享一篇Microsoft Query做多表動態(tài)查詢銷售業(yè)績清單的技術(shù),解救各位銷售助理、銷售文員。
下面以銷售人員業(yè)績動態(tài)查詢?yōu)槔榻BMicrosoft Query多表動態(tài)查詢制作。
如下圖,桌面“銷售數(shù)據(jù)”文件夾里,包含了11月5日到11月7日三天的銷售數(shù)據(jù)。
每個工作簿里的內(nèi)容都是當(dāng)天所有業(yè)務(wù)人員的銷售數(shù)據(jù)。三個工作簿字段名都是一致的。
我們要做的動態(tài)跨表查詢?nèi)缦拢?/p>
根據(jù)上圖我們能看到,改變業(yè)務(wù)人員的姓名,下方數(shù)據(jù)區(qū)域就會自動篩選出三個工作表里該業(yè)務(wù)人員的銷售記錄。這里為了方便查詢,姓名使用數(shù)據(jù)驗證下拉菜單來顯示。當(dāng)然在單元格直接輸入姓名也可以達到同樣的效果。
這是怎么做到的呢?來跟我一起學(xué)習(xí)吧!
制作過程
Step 01 建立查詢表
保存關(guān)閉三個工作簿,新建工作簿,在A1單元格輸入“業(yè)務(wù)人員”,A2單元格輸入其中一個業(yè)務(wù)員的姓名“楊光”(這里也可以建立下拉菜單,有興趣的讀者可以自己試試)。
Step 02 初步指定查詢的數(shù)據(jù)源
點擊【數(shù)據(jù)】選項卡下【獲取外部數(shù)據(jù)】組里“自其他來源”下拉菜單的“來自Microsoft Query”。
在【選擇數(shù)據(jù)源】窗口“數(shù)據(jù)庫”選項下點擊“Excel Files”,勾選下方的“使用[查詢向?qū)?span>]創(chuàng)建/編輯查詢” ,點擊確定。
在【選擇工作簿】窗口右側(cè)目錄里找到數(shù)據(jù)源所在的文件夾,在左側(cè)數(shù)據(jù)庫名找到其中一個工作簿,點擊確定。
接下來有兩種可能:系統(tǒng)彈出提示窗口和不彈出提示窗口。彈出提示從(1)開始,不彈出提示直接從(2)開始。
(1)系統(tǒng)提示“數(shù)據(jù)源中沒有包含可見的表格”,如下,直接點擊確定。
進入【查詢向?qū)?span>-選擇列】窗口,點擊下面的“選項”按鈕,打開右側(cè)【表選項】窗口,勾選“系統(tǒng)表”點擊確定。
這樣【查詢向?qū)?span>-選擇列】窗口中就會出現(xiàn)數(shù)據(jù)源里的工作表了。
(2)在【查詢向?qū)?span>-選擇列】窗口中選中工作表點擊中間的“>”按鈕把左側(cè)的“可用的表和列”添加到右側(cè)的“查詢結(jié)果中的列”,點擊下一步。
進入【查詢向?qū)?span>-篩選數(shù)據(jù)】窗口,不用操作,點擊下一步。
進入【查詢向?qū)?span>-排序順序】窗口,同樣不用操作,點擊下一步。
進入【查詢向?qū)?span>-完成】窗口,選擇“將數(shù)據(jù)返回Microsoft Excel”,點擊完成。
Step 03 指定所有查詢數(shù)據(jù)并設(shè)置查詢條件
在EXCEL中出現(xiàn)【導(dǎo)入數(shù)據(jù)】窗口,我們選擇顯示為“表”,位置放置在現(xiàn)有工作表。點擊下方左側(cè)的“屬性”按鈕。
打開【連接屬性】窗口,在“定義”選項卡里的“命令文本”輸入框中輸入下列文本。注意,讀者應(yīng)該根據(jù)自己的文件修改工作簿路徑和工作表名、查詢條件。
SELECT * FROM [C:UsersAdministratorDesktop銷售數(shù)據(jù)11.5銷售數(shù)據(jù).xlsx].[Sheet1$] where 業(yè)務(wù)人員=? union all
SELECT * FROM [C:UsersAdministratorDesktop銷售數(shù)據(jù)11.6銷售數(shù)據(jù).xlsx].[Sheet1$] where 業(yè)務(wù)人員=? union all
SELECT * FROM [C:UsersAdministratorDesktop銷售數(shù)據(jù)11.7銷售數(shù)據(jù).xlsx].[Sheet1$] where 業(yè)務(wù)人員=?
解析:
SELECT * FROM [C:UsersAdministratorDesktop銷售數(shù)據(jù)11.5銷售數(shù)據(jù).xlsx].[Sheet1$] where 業(yè)務(wù)人員=? union all
這里使用了SQL語句。意思是根據(jù)業(yè)務(wù)人員的名稱在指定的工作表中查詢表中所有列數(shù)據(jù)。
SELECT 列名 :指定要查詢的數(shù)據(jù)列,譬如“SELECT [Sheet1$].日期, [Sheet1$].商品代碼, [Sheet1$].金額”,就表示只查詢、顯示Sheet1表中日期、商品代碼、金額三列數(shù)據(jù)。當(dāng)前“SELECT *”,“*”是通配符,代表所有列,也就是表格中所有內(nèi)容。
FROM 表名稱:指定要查詢的工作簿以及工作表名,由兩部分組成[].[],前部分是工作簿路徑,后部分是工作表名稱。譬如當(dāng)前“FROM [C:UsersAdministratorDesktop銷售數(shù)據(jù)11.5銷售數(shù)據(jù).xlsx].[Sheet1$]”就表示查詢工作簿“11.5銷售數(shù)據(jù).xlsx”中的Sheet1工作表。讀者需要根據(jù)自己的文件路徑來設(shè)置。
where 查詢條件:指定按什么條件進行查詢。譬如“where 業(yè)務(wù)人員=?”表示按輸入的業(yè)務(wù)人員的姓名進行查詢。因為需要按輸入的內(nèi)容進行查詢,所以后續(xù)需要指定輸入的位置。
union all:指的是把查詢的下一個工作表的結(jié)果與當(dāng)前的查詢連接排列。最后一個工作表就不能寫這句代碼了。譬如當(dāng)前最后一個工作簿“11.7銷售數(shù)據(jù).xlsx”后就沒有“union all”語句。
注意:這里的所有符號必須是英文狀態(tài)下的。
Step 04 指定查詢條件的輸入位置
點擊兩次確定。彈出【輸入?yún)?shù)值】窗口,鼠標單擊B1單元格在參數(shù)1中指定查詢條件輸入位置“=Sheet1!$B$1”。分別勾選“在以后的刷新中使用該值或該引用”和“當(dāng)單元格值更改時自動刷新”復(fù)選項,點擊確定。
然后用同樣的方式設(shè)置參數(shù)2和參數(shù)3。這樣就完成了。
后續(xù)當(dāng)我們在B1單元格輸入其他業(yè)務(wù)員名字時,下方就能自動刷新出對應(yīng)結(jié)果了。不過輸入的時候,名字要完全正確系統(tǒng)才能查詢到。
Microsoft Query動態(tài)查詢優(yōu)勢和不足
使用Microsoft Query進行動態(tài)查詢具有以下優(yōu)勢:
◎不用記函數(shù)
◎避免VLOOKUP等函數(shù)無法一對多查詢的弊端
使用Microsoft Query進行動態(tài)查詢的不足:
如果數(shù)據(jù)表很多,逐個定義連接屬性和參數(shù)很繁瑣的。
適用條件
使用Microsoft Query建立跨表動態(tài)查詢需要滿足下方條件:
◎各個表的字段一致
◎各個表中不能有其他無關(guān)數(shù)據(jù)
后續(xù)操作
如果后續(xù)需要增加或者減少查詢數(shù)據(jù)源怎么辦?
(1)點中查詢結(jié)果的某個單元格,單擊“數(shù)據(jù)”選項卡“連接”功能組中的“屬性”按鈕.
(2)在彈出的“外部數(shù)據(jù)屬性”對話框中單擊“連接屬性”按鈕。
(3)進入“連接屬性”對話框,單擊“定義”選項卡,修改“命令文本”中的語句,增減工作表即可。
歡迎加入QQ群:920864360交流學(xué)習(xí)下載素材。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
跨表提取數(shù)據(jù)的非函數(shù)做法1《跨表提取數(shù)據(jù),函數(shù)高手被名不經(jīng)傳的Microsoft Query 直接KO》
跨表提取數(shù)據(jù)的非函數(shù)做法2《打敗查找函數(shù),pq合并查詢一次搞定多表匹配》
一對多提取數(shù)據(jù)萬金油函數(shù)法《Excel萬金油公式INDEX-SMALL-IF-ROW篩選函數(shù)公式解讀》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!