我们在实际工作中经常遇到的情况是,从办公ERP系统导出的数据只是一个底层的汇总表格,还需要汇总加工一下才能得到我们想要的结果。要汇总的每个工作表中有多列文本,这些工作表的数据结构完全相同,日积月累会发现表格已经有上万多条记录,工作簿的大小也达到了十几兆的储存,这时候在使用的时候会发生Excel加载缓慢,卡顿的情况。今天我们来学习下在Excel中通过数据链接来使用SQL语句汇总表格。
Excel+SQL很类似于Excel+VBA。通过编写简单的查询语句,可以快速得到自己想要的表格格式和运算结果。它具有以下优点:
省时、省力(将复杂的人工手动加工过程变成几条语句,或数据量太大手动加工运算很慢)
查询语句编写简单(sql语言很简单,也是数据分析必备的技能之一)
可重复多次使用(同样的操作或类似操作可以使用同一语句直接完成)
语句延伸使用的空间较大(即使表格数据有一些变化,只要大体的加工思路不变,只需更简单改语句中一些地方即可复用)
等等
需要注意的是,为了能够利用导入数据用于SQL数据查询汇总,每个工作表的第一行最好是数据区域的列标题,如果每个工作表的第一行不是数据区域的列标题,就需要先对数据区域定义名称,或者在SQL语句中进行特殊处理。
我模拟了一份导出的数据文件,有14个区域,112家门店2016年、2017年每天销售记录,每个工作表均有几万行记录,工作簿达到了13.7M大小。
举一个简单的例子,咱们就汇总一下每个区域每年的销售合计。
新建一个Excel文件。打开“数据->来自其他源->来自Microsoft Query”
点击“来自Microsoft Query”后会弹出一个提示框,选择Excel Files后点确认。
从目录中选择Excel文件的文件地址和文件名(为数据库名),确定后添加表"2016年“、”2017年“。
添加表"2016年“、”2017年“后,关闭窗口。点击显示的SQL按钮,在显示的SQL窗口中写入SQL语句。
SELECT `2016年$`.区域, Sum(`2016年$`.分类一)+Sum(`2016年$`.分类二)+Sum(`2016年$`.分类三)+Sum(`2016年$`.分类四)+Sum(`2016年$`.分类五)+Sum(`2016年$`.分类六) AS '2016年合计'FROM `2016年$` `2016年$`GROUP BY `2016年$`.区域
要汇总2017年的只需要将代码的2016年替换成2017年即可。
点击"文件"-"将数据返回microso Excel(R)",保存数据到单元格。
要实现其他的汇总查询功能只需要修改SQL语句就可以了,至于SQl语句的使用我相信它比英语语法还要简单易懂。
细心的小伙伴可能发现我使用了Microsoft Query,它是用于将数据从外部数据源检索到Excel中的一种程序。通过使用Query可以从企业的数据库和文件中检索数据,而不必重新键入需要在Excel中分析的数据。也可以在每次更新数据库时,自动通过源数据库中的数据来更新Excel报表和汇总数据。2010版和2013版的Excel需要下载安装,2016版的已经内置。
小伙伴们如果有什么关于Excel的问题或者想了解哪方面的内容,可以发消息给我留言,欢迎互相交流学习,共同进步。
如果觉得内容有帮助,请转发给您的小伙伴吧!