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