二维码 购物车
部落窝在线教育欢迎您!

双11过后销售业绩动态查询清单这样做最简便

 

作者:夏雪来源:部落窝教育发布时间:2018-11-20 00:35:51点击:3373

分享到:
0
收藏    收藏人气:1人
版权说明: 原创作品,禁止转载。
编按:

销售经理问助理:人事小王能够做员工信息动态查询表,为啥你不做一张员工销售业绩动态查询表呀?助理无语:不是不想做,而是人事的数据源就一张表,每位员工信息还是唯一的,做查询很简单;销售部门每周一张销售明细(销售流水),并且每张明细中每个销售员存在多笔销售,这种动态查询难……今天部落窝就借某单位双11销售数据分享一篇Microsoft Query做多表动态查询销售业绩清单的技术,解救各位销售助理、销售文员。

下面以销售人员业绩动态查询为例介绍Microsoft Query多表动态查询制作。

如下图,桌面销售数据文件夹里,包含了115日到117日三天的销售数据。

 

 

每个工作簿里的内容都是当天所有业务人员的销售数据。三个工作簿字段名都是一致的。

 

 

我们要做的动态跨表查询如下:

 

 

根据上图我们能看到,改变业务人员的姓名,下方数据区域就会自动筛选出三个工作表里该业务人员的销售记录。这里为了方便查询,姓名使用数据验证下拉菜单来显示。当然在单元格直接输入姓名也可以达到同样的效果。

这是怎么做到的呢?来跟我一起学习吧!

 

制作过程

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)进入“连接属性”对话框,单击“定义”选项卡,修改“命令文本”中的语句,增减工作表即可。

 

 

欢迎加入QQ群:920864360交流学习下载素材。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

http://www.itblw.com/ewebeditor/uploadfile/20180914164059384001.png

相关推荐:

跨表提取数据的非函数做法1跨表提取数据,函数高手被名不经传的Microsoft Query 直接KO

跨表提取数据的非函数做法2打败查找函数,pq合并查询一次搞定多表匹配

一对多提取数据万金油函数法《Excel万金油公式INDEX-SMALL-IF-ROW筛选函数公式解读