Excel数据透视表与SQL典型应用.ppt

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