数据透视表与SQL典型应用

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,*,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,工作中常收集到的数据：同一文件夹下不同工作簿表头一样的工作表,这些数据放在D盘的“汇总数据文件夹,包含“财务部、“车间部、“销售部三个工作簿,“销售部工作簿包含“销售1部、“销售2部2个工作表,注：所有工作簿的工作表的表头都是一样的,表头一样,1,如何把这些工作表的所有数据汇总在一起进展统计计算,常规做法：把各个工作簿的工作表的数据一个一个地复制到同一个工作表中，然后使用数据透视表进展汇总,操作过程,结果,如果这些工作表更新了数据或删除局部数据，此时的结果是怎么样？,答案或许是这样：重新复制数据，重新操作一次，心里很痛苦,此时你会想到为什么Excel里面没有一种方法“只需要刷新一下即可得到更新后数据的统计结果呢？,后期面临的问题,期待的方法,2,通过SQL与数据透视表结合使用，可快速汇总同一文件夹不同工作簿不同工作表一样字段的数据，并且可以随时刷新,数据透视表,SQL,语句,+,=,数据处理利器,报表人士必杀技,Excel,必修课,数据分析人士工具箱,3,请看如何操作：如何使用SQL与数据透视表把同一文件夹下不同工作簿表头一样的工作表进展汇总统计1,第一步：新建一个名字为“汇总的工作簿。,新建一个“汇总工作簿,4,操作步骤,2,第二步：翻开“汇总工作簿，在“Sheet1工作表中任意单击一个单元格，选择“数据选项卡，单击“获取外部数据组中的“现有连接按钮，弹出“现有连接对话框，单击“浏览更多按钮。,“现有连接按钮,注：该界面是Excel2021的，对于Excel2007也适用,“浏览更多按钮,5,操作步骤,3,第三步：在弹出的“选取数据源对话框中找到D盘的数据源中任何一个工作簿如：销售部下的工作表如：销售1部，单击“确定按钮。,数据源在D盘的“汇总数据文件中,“销售1部工作表,“销售部工作簿,6,操作步骤,4,第四步：在弹出的“导入数据对话框中选择“数据透视表和数据透视图，将数据放置在“现有工作表的A1单元格，并单击“属性按钮，在弹出的“连接属性对话框下的“使用状况下勾选“翻开文件时刷新数据，去除在“定义下“命令文本的文本，输入SQL语句。,选择数据透视表和数据透视图,勾选“翻开文件时刷新数据,最关键：单击“属性按钮,在此处输入,SQL,语句,7,操作步骤,5,第五步：在“命令文本中输入以下SQL语句，然后单击“确定按钮。,SELECT * FROM D:,汇总数据,销售部,.xlsx.,销售,1,部,$,UNION ALL,SELECT * FROM D:,汇总数据,销售部,.xlsx.,销售,2,部,$,UNION ALL,SELECT * FROM D:,汇总数据,车间部,.xlsx.,一车间,$,UNION ALL,SELECT * FROM D:,汇总数据,车间部,.xlsx.,二车间,$,UNION ALL,SELECT * FROM D:,汇总数据,财务部,.xlsx.,财务部,$,输入,SQL,语句,8,操作步骤,6,第六步：直接返回“导入数据对话框，单击“确定按钮，即可创立数据透视表，然后把“月、“科目划分拖至“报表筛选，“部门拖至“行标签，“发生额拖至“数值，最终效果如下：,各部门数据已经进展汇总统计,9,附表：SQL语句原理讲解1/3,SELECT * FROM D:,汇总数据,销售部,.xlsx.,销售,1,部,$,Excel使用SQL的数据源放置的路径写法：文件路径.工作表$,文件夹：D盘的“汇总数据文件夹，其SQL写法为“D:汇总数据,工作簿：而“销售部的Excel文件2007以上的后缀为：xlsx)放置在该文件夹中，最后弄在一起的写法为：D:汇总数据销售部.xlsx,工作表：在“销售部.xlsx有2个工作表，其中一个为“销售1部工作表，最后连接在一起的写法：D:汇总数据销售部.xlsx.销售1部$,详细解释路径写法,文件夹,工作簿,工作表,一步一步地对路径进展分解,10,附表： SQL语句原理讲解2/3,SELECT语句讲解：SELECT * FROM 工作表$,表示从工作表中提取所有的数据，其中*代表所有数据,SELECT * FROM D:汇总数据销售部.xlsx.销售1部$：该语句的意思是从D盘的“汇总数据文件夹中的“销售部.xlsx工作簿中的“销售1部工作表提取所有的数据,UNION ALL,语句讲解：,表示把两个工作表的数据连接在一起，其演示如下,表,1,表,2,SELECT,*,FROM ,表,1$,UNION ALL,SELECT,*,FROM ,表,2$,UNION ALL,连接结果表,11,附表： SQL语句原理讲解3/3,温馨提示：,1、所有的输入的字符及标点都是在英文状态输入法输入的，中文除外；,2、Excel里面使用的SQL语句与SQL Server、Oracle等大型数据库的语法有所差异；,3、Excel里面通过SQL语句结合数据透视表，可以处理300万左右的数据，特别是数据透视表的功能，可以很方面地进展汇总统计分析；,4、本内容来自于?Excel 2021 SQL完全应用?第9章；,5、通过对?Excel 2021 SQL完全应用?的学习，也可以直接应用到Access等小型数据库;,6、操作源文件及操作演示请看附件。,12,附表：启用经典数据透视表布局应用网格中的字段拖放操作,13,附表：假设工作表的数据已经更新如：添加、删除数据等，那么数据透视表中的汇总数据是否也可以同时更新呢？,操作：选中数据透视表中任一单元格，在“数据选项卡的“连接组中选择“全部刷新或“刷新即可。,刷新,14,?Excel 2021 SQL完全应用?一书简介,第一本专为办公人士、报表人士、数据分析与数据挖掘人士、经营分析人士、Excel爱好者撰写的有关在Excel中如何使用SQL的书籍；,写书初衷：全面介绍在Excel中如何使用SQL，其实SQL语句真的很简单；,温馨提示：使用SQL结合数据透视表，可以快速汇总统计数据，SQL也称为Excel中的“瑞士军刀。,如果对该书有兴趣的话，可去卓越、当当、京东购置,也可以关注作者们的新浪微博：ETwise、amulee、biaotiger1、Excel技巧网_官方微博,15,Thank You !,16,谢谢大家！,