散文網(wǎng) » 科技 »學(xué)習(xí) » excel動(dòng)態(tài)圖表：如何制作多表動(dòng)態(tài)查詢銷售業(yè)績(jī)清單

excel動(dòng)態(tài)圖表：如何制作多表動(dòng)態(tài)查詢銷售業(yè)績(jī)清單

2018-11-21 11:40 作者:IT部落窩教育 0人讀過(guò) | 我要投稿

編按：銷售經(jīng)理問(wèn)助理：人事小王能夠做員工信息動(dòng)態(tài)查詢表，為啥你不做一張員工銷售業(yè)績(jī)動(dòng)態(tài)查詢表呀？助理無(wú)語(yǔ)：不是不想做，而是人事的數(shù)據(jù)源就一張表，每位員工信息還是唯一的，做查詢很簡(jiǎn)單；銷售部門(mén)每周一張銷售明細(xì)（銷售流水），并且每張明細(xì)中每個(gè)銷售員存在多筆銷售，這種動(dòng)態(tài)查詢難……今天部落窩就借某單位雙11銷售數(shù)據(jù)分享一篇Microsoft Query做多表動(dòng)態(tài)查詢銷售業(yè)績(jī)清單的技術(shù)，解救各位銷售助理、銷售文員。

下面以銷售人員業(yè)績(jī)動(dòng)態(tài)查詢?yōu)槔榻BMicrosoft Query多表動(dòng)態(tài)查詢制作。

如下圖，桌面“銷售數(shù)據(jù)”文件夾里，包含了11月5日到11月7日三天的銷售數(shù)據(jù)。

每個(gè)工作簿里的內(nèi)容都是當(dāng)天所有業(yè)務(wù)人員的銷售數(shù)據(jù)。三個(gè)工作簿字段名都是一致的。

我們要做的動(dòng)態(tài)跨表查詢?nèi)缦拢?/p>

根據(jù)上圖我們能看到，改變業(yè)務(wù)人員的姓名，下方數(shù)據(jù)區(qū)域就會(huì)自動(dòng)篩選出三個(gè)工作表里該業(yè)務(wù)人員的銷售記錄。這里為了方便查詢，姓名使用數(shù)據(jù)驗(yàn)證下拉菜單來(lái)顯示。當(dāng)然在單元格直接輸入姓名也可以達(dá)到同樣的效果。

這是怎么做到的呢？來(lái)跟我一起學(xué)習(xí)吧！?

制作過(guò)程：

Step 01?建立查詢表

保存關(guān)閉三個(gè)工作簿，新建工作簿，在A1單元格輸入“業(yè)務(wù)人員”，A2單元格輸入其中一個(gè)業(yè)務(wù)員的姓名“楊光”（這里也可以建立下拉菜單，有興趣的讀者可以自己試試）。

Step 02?初步指定查詢的數(shù)據(jù)源

點(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ù)名找到其中一個(gè)工作簿，點(diǎn)擊確定。

接下來(lái)有兩種可能：系統(tǒng)彈出提示窗口和不彈出提示窗口。彈出提示從（1）開(kāi)始，不彈出提示直接從（2）開(kāi)始。

（1）系統(tǒng)提示“數(shù)據(jù)源中沒(méi)有包含可見(jiàn)的表格”，如下，直接點(diǎn)擊確定。

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

這樣【查詢向?qū)?選擇列】窗口中就會(huì)出現(xiàn)數(shù)據(jù)源里的工作表了。

（2）在【查詢向?qū)?選擇列】窗口中選中工作表點(diǎn)擊中間的“>”按鈕把左側(cè)的“可用的表和列”添加到右側(cè)的“查詢結(jié)果中的列”，點(diǎn)擊下一步。

進(jìn)入【查詢向?qū)?篩選數(shù)據(jù)】窗口，不用操作，點(diǎn)擊下一步。

進(jìn)入【查詢向?qū)?排序順序】窗口，同樣不用操作，點(diǎn)擊下一步。

進(jìn)入【查詢向?qū)?完成】窗口，選擇“將數(shù)據(jù)返回Microsoft Excel”，點(diǎn)擊完成。

Step 03 指定所有查詢數(shù)據(jù)并設(shè)置查詢條件

在EXCEL中出現(xiàn)【導(dǎo)入數(shù)據(jù)】窗口，我們選擇顯示為“表”，位置放置在現(xiàn)有工作表。點(diǎn)擊下方左側(cè)的“屬性”按鈕。

打開(kāi)【連接屬性】窗口，在“定義”選項(xiàng)卡里的“命令文本”輸入框中輸入下列文本。注意，讀者應(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ù)人員=?

公式參數(shù)解析：

SELECT *? FROM [C:UsersAdministratorDesktop銷售數(shù)據(jù)11.5銷售數(shù)據(jù).xlsx].[Sheet1$] where 業(yè)務(wù)人員=?? union all

這里使用了SQL語(yǔ)句。意思是根據(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ù)自己的文件路徑來(lái)設(shè)置。

where?查詢條件：指定按什么條件進(jìn)行查詢。譬如“where 業(yè)務(wù)人員=?”表示按輸入的業(yè)務(wù)人員的姓名進(jìn)行查詢。因?yàn)樾枰摧斎氲膬?nèi)容進(jìn)行查詢，所以后續(xù)需要指定輸入的位置。

union all：指的是把查詢的下一個(gè)工作表的結(jié)果與當(dāng)前的查詢連接排列。最后一個(gè)工作表就不能寫(xiě)這句代碼了。譬如當(dāng)前最后一個(gè)工作簿“11.7銷售數(shù)據(jù).xlsx”后就沒(méi)有“union all”語(yǔ)句。

注意：這里的所有符號(hào)必須是英文狀態(tài)下的。

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

點(diǎn)擊兩次確定。彈出【輸入?yún)?shù)值】窗口，鼠標(biāo)單擊B1單元格在參數(shù)1中指定查詢條件輸入位置“=Sheet1!$B$1”。分別勾選“在以后的刷新中使用該值或該引用”和“當(dāng)單元格值更改時(shí)自動(dòng)刷新”復(fù)選項(xiàng)，點(diǎn)擊確定。