数据透视表与SQL典型运用ppt课件.pptx
重庆工商大学会计学院 赵青华,数据透视表与Query查询和SQL典型运用,Excel 2016,QUERY数据查询,SQL典型运用,Power Pivot和Power Query,cONTENTS,c,目录,第,章,1,QUERY数据查询,Microsoft Query的基本概念,MicrosoftQuery查询之数据源,Microsoft Query数据查询,Microsoft Query数据查询（多维数据集）,MicrosoftQuery查询之数据源（Excel多表）,MicrosoftQuery查询,第,章,2,SQL典型运用,同一文件夹下不同工作簿表头相同的工作表,这些数据放在D盘的“汇总数据”文件夹,包含“财务部”、“车间部”、“销售部”三个工作簿,“销售部”工作簿包含“销售1部”、“销售2部”2个工作表,注：所有工作簿的工作表的表头都是相同的,表头相同,如何把这些工作表的所有数据汇总在一起进行统计计算,常规做法：把各个工作簿的工作表的数据一个一个地复制到同一个工作表中，然后使用数据透视表进行汇总,操作过程,结果,通过SQL与数据透视表结合使用，可快速汇总同一文件夹不同工作簿不同工作表相同字段的数据，并且可以随时刷新,数据透视表,SQL语句,+,=,数据处理利器,Excel必修课,SQL与把同一文件夹下不同工作簿表头相同的工作表汇总（1）,14,第一步：新建一个名字为“汇总”的工作簿。,新建一个“汇总”工作簿,操作步骤2,15,第二步：打开“汇总”工作簿，在“Sheet1”工作表中任意单击一个单元格，选择“数据”选项卡，单击“获取外部数据”组中的“现有连接”按钮，弹出“现有连接”对话框，单击“浏览更多”按钮。,“现有连接”按钮,注：该界面是Excel2010的，对于Excel2010以上版本也适用,“浏览更多”按钮,操作步骤3,第三步：在弹出的“选取数据源”对话框中找到D盘的数据源中任何一个工作簿（如：销售部）下的工作表（如：销售1部），单击“确定”按钮。,数据源在D盘的“汇总数据”文件中,“销售1部”工作表,“销售部”工作簿,操作步骤4,第四步：在弹出的“导入数据”对话框中选择“数据透视表和数据透视图”，将数据放置在“现有工作表”的A1单元格，并单击“属性”按钮，在弹出的“连接属性”对话框下的“使用状况”下勾选“打开文件时刷新数据”，清除在“定义”下“命令文本”的文本，输入SQL语句。,选择数据透视表和数据透视图,勾选“打开文件时刷新数据”,最关键：单击“属性”按钮,在此处输入SQL语句,操作步骤5,18,第五步：在“命令文本”中输入以下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语句,操作步骤6,第六步：直接返回“导入数据”对话框，单击“确定”按钮，即可创建数据透视表，然后把“月”、“科目划分”拖至“报表筛选”，“部门”拖至“行标签”，“发生额”拖至“数值”，最终效果如下：,各部门数据已经进行汇总统计,附表：SQL语句原理讲解（1/3）,Excel使用SQL的数据源放置的路径写法：文件路径.工作表$文件夹：D盘的“汇总数据”文件夹，其SQL写法为“D:汇总数据”工作簿：而“销售部”的Excel文件（2007以上的后缀为：xlsx)放置在该文件夹中，最后弄在一起的写法为：D:汇总数据销售部.xlsx工作表：在“销售部.xlsx”有2个工作表，其中一个为“销售1部”工作表，最后连接在一起的写法：D:汇总数据销售部.xlsx.销售1部$,详细解释路径写法,文件夹,工作簿,工作表,一步一步地对路径进行分解,附表： SQL语句原理讲解（2/3）,21,SELECT语句讲解：SELECT * FROM 工作表$表示从工作表中提取所有的数据，其中“*”代表所有数据SELECT * FROM D:汇总数据销售部.xlsx.销售1部$：该语句的意思是从D盘的“汇总数据”文件夹中的“销售部.xlsx”工作簿中的“销售1部”工作表提取所有的数据,UNION ALL语句讲解：表示把两个工作表的数据连接在一起，其演示如下,表1,表2,SELECT * FROM 表1$UNION ALLSELECT * FROM 表2$,UNION ALL连接结果表,附表： SQL语句原理讲解（3/3）,22,附表：启用经典数据透视表布局（应用网格中的字段拖放）操作,23,附表：若工作表的数据已经更新（如：添加、删除数据等），那么数据透视表中的汇总数据是否也可以同时更新呢？,24,操作：选中数据透视表中任一单元格，在“数据”选项卡的“连接”组中选择“全部刷新”或“刷新”即可。,刷新,示例与解释,示例与解释,LIKE模糊匹配，方括号表示某一范围的字符。,或者“%林”，“林_”等,ORDER BY DESC（ASC)降（升）排序,示例与解释,示例与解释,第,章,3,Power Pivot和Power Query,1.用链接表向PowerPivot添加数据,2.PowerQuery数据查询,3.网络数据查询,http:/,THANK YOU .,
