有小伙伴私信提问:每天要统计同一文件夹下不同地区的销售数据,数据表结构相同,每天更新增加新记录,他是复制所有数据到一个工作表,再用透视表统计,想问有没有好的办法。
今天分享一个利用数据透视表和SQL语句快速实现多表汇总统计的方法,增减数据时,刷新一下即可,一刷出结果,不用一秒!提问的小伙伴速来围观!
一、案例数据源
二、要求:天天统计每个销售部门和每位销售员的累计销售情况
三、具体方法:
通过数据透视表与SQL结合实现动态提取数据,做好透视表后,只需要刷新就能获取最新数据。有小伙伴可能觉得SQL语句很难,实际并没有那么难,只要掌握简单的SQL语句就能解决大问题。
四、操作步骤:
步骤1:新建一个“销售数据汇总”工作簿
步骤2:建立链接(同一个工作簿或跨工作簿操作相似)
打开“销售数据汇总”工作簿,在工作表的任一单元格,点【数据】→【获取和转换数据】→【现有连接】→在【现有连接】对话框中,点【浏览更多】→在【选取数据源】对话框中找到”销售数据表“文件夹下的任一个工作簿→在弹出的【导入数据】对话框中选择【数据透视表】→确定。
动图如下:
步骤3:设置刷新方式
将光标放在透视表中的任一位置→点【分析】→【更改数据源】→【连接属性】→在弹出的【连接属性】对话框中勾选【打开文件时刷新数据】→单击【定义】
步骤4:输入SQL语句
先清除【命令文本】框中的文本→再输入以下SQL语句→确定,SQL语名如下:
SELECT * FROM [D:10我的发布068销售数据表郴州.XLSX].[郴州$] UNION ALL
SELECT * FROM [D:10我的发布068销售数据表衡阳.XLSX].[衡阳$] UNION ALL
SELECT * FROM [D:10我的发布068销售数据表零陵.XLSX].[零陵$] UNION ALL
SELECT * FROM [D:10我的发布068销售数据表长沙.XLSX].[长沙$] UNION ALL
SELECT * FROM [D:10我的发布068销售数据表株洲.XLSX].[株洲$]
SQL语句解析:
1、SELECT * FROM [D:10我的发布068销售数据表郴州.XLSX].[郴州$]】:表示提取D:10我的发布068销售数据表郴州.XLSX]工作簿中郴州工作表的所有数据。
2、UNION ALL:表示将两个表的数据连接在一起。
动图如下:
步骤5:创建数据透视表
其中统计销售部门销售量的行字段为销售部门,值为数量;统计销售人员销售量的行字段为销售人员,值为数量。
动图如下:
步骤6:验证更新效果
打开【郴州】表,增加一条记录,存盘关闭后,再打开“销售据量汇总”工作簿,数据已更新。
动图如下:
你学会了吗?欢迎小伙伴留言讨论,如果觉得好用的话,点个赞,转发支持一下呗!更多的EXCEL技能,可以关注 “EXCEL学习微课堂”。
与本课程内容相关的往期课程有:
数据透视表1《为什么要学数据透视表——因为它能快速汇总、智能分组、动态交互!》
数据透视表2《你知道EXCEL数据透视表布局的那些门道儿吗?》
数据透视表3《Excel数据透视表日期、时间、数字和文本字段组合功能应用!》
数据透视表4《 数据透视表功能太强大了,原来可以这样轻松搞定多维度计算!》
数据透视表5《让你的EXCEL数据透视表更美观:字段计算+自定义格式》
数据透视表6《EXCEL数据透视表中的排序技巧汇总》
数据透视表7《EXCEL数据透视表中的筛选技巧汇总,你学会了吗?》
数据透视表8《EXCEL制作高逼格动态图表神器,数据透视表之切片器技巧详解!》
数据透视表9《5个案例告诉你:EXCEL条件格式让你的数据透视表显示更直观!》
数据透视表10《透视表+VBA,1分钟搞定按条件拆分工作表,工作表拆分到工作簿!》