excel动态图表如何制作多表动态查询销售业绩清单.docx
《excel动态图表如何制作多表动态查询销售业绩清单.docx》由会员分享,可在线阅读,更多相关《excel动态图表如何制作多表动态查询销售业绩清单.docx(24页珍藏版)》请在冰点文库上搜索。
excel动态图表如何制作多表动态查询销售业绩清单
excel 动态图表:
如何制作多表动态查询销售业绩清
单
编按:
销售经理问助理:
人事小王能够做员工信息动态
查询表,为啥你不做一张员工销售业绩动态查询表呀?
助
理无语:
不是不想做,而是人事的数据源就一张表,每位
员工信息还是唯一的,做查询很简单;销售部门每周一张
销售明细(销售流水),并且每张明细中每个销售员存在多
笔销售,这种动态查询难……今天部落窝就借某单位双 11
销售数据分享一篇 MicrosoftQuery 做多表动态查询销售业
绩清单的技术,解救各位销售助理、销售文员。
下面以销售人员业绩动态查询为例介绍 MicrosoftQuery
多表动态查询制作。
如下图,桌面“销售数据”文件夹里,包含了 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 进行动态查询的不足:
如果数据表很多,逐个定义连接属性和参数很繁琐的。
适用条件:
使用 MicrosoftQuery 建立跨表动态查询需要满足下方条
件:
*各个表的字段一致
*各个表中不能有其他无关数据
后续操作:
如果后续需要增加或者减少查询数据源怎么办?
(1)点中查询结果的某个单元格,单击”数据”选项卡“连
接”功能组中的“属性”按钮.
(2)在弹出的“外部数据属性”对话框中单击“连接属性”
按钮。
(3)进入“连接属性”对话框,单击“定义”选项卡,修改
“命令文本”中的语句,增减工作表即可。
大家在应用这种方法的时候要了解源数据的情况,要与
使用情况相符合,不然就要处理源数据才能正确应用。
****部落窝教育-excel 动态跨表查询****