数据透视表与SQL典型运用

重庆工商大学会计学院赵青华数据透视表与Query查询和SQL典型运用Excel2016QUERY数据查询SQL典型运用PowerPivot和PowerQuerycONTENTS目录第章1QUERY数据查询MicrosoftQuery的基本概念MicrosoftQuery，可以连接到外部数据源，从那些外部数据源选择数据，将该数据导入到工作表中，以及根据需要刷新数据以使工作表数据与外部源中的数据保持同步。•可以访问的数据库类型您可以从多种类型的数据库中检索数据，包括MicrosoftOfficeAccess、MicrosoftSQLServer和MicrosoftSQLServerOLAPServices。还可以从Excel工作簿和文本文件中检索数据。MicrosoftQuery查询之数据源•是存储起来的一组信息，它允许Excel和MicrosoftQuery连接到外部数据库。使用MicrosoftQuery设置数据源时，可为数据源指定名称，然后提供数据库或服务器的名称和位置、数据库的类型，以及您的登录和密码信息。该信息还包括OBDC驱动程序或数据源驱动程序的名称，驱动程序是连接到特定类型的数据库的程序。数据源的概念MicrosoftQuery数据查询1.在“数据”选项卡上的“获取外部数据”组中，单击“自其他来源”，然后单击“来自MicrosoftQuery”。2.执行下列操作之一：若要为数据库、文本文件或Excel工作簿指定数据源，请单击“数据库”选项卡。要指定OLAP多维数据集数据源，请单击“OLAP多维数据集”选项卡。只有当从Excel运行MicrosoftQuery时，该选项卡才可用。3.双击“新数据源”。或单击“新数据源”，然后单击“确定”。“创建新数据源”对话框即会显示。MicrosoftQuery数据查询（多维数据集）MicrosoftQuery查询之数据源（Excel多表）MicrosoftQuery查询4.在步骤1中，键入名称来标识数据源。5.在步骤2中，单击要用作数据源的数据库类型的驱动程序。6.单击“连接”，然后提供连接到数据源所需的信息。7.输入所需的信息之后，单击“确定”或“完成”，返回到“创建新数据源”对话框。8.如果数据库包含表并且您希望某个特定表在“查询向导”中自动显示，请单击步骤4中的相应框，然后单击所需的表。9.如果不想在使用数据源时键入登录名和密码，请选中“在数据源定义中保存我的用户标识符和密码”复选框。第章2SQL典型运用同一文件夹下不同工作簿表头相同的工作表这些数据放在D盘的“汇总数据”文件夹包含“财务部”、“车间部”、“销售部”三个工作簿“销售部”工作簿包含“销售1部”、“销售2部”2个工作表注：所有工作簿的工作表的表头都是相同的①②③表头相同如何把这些工作表的所有数据汇总在一起进行统计计算常规做法：把各个工作簿的工作表的数据一个一个地复制到同一个工作表中，然后使用数据透视表进行汇总•如果这些工作表更新了数据或删除部分数据，此时的结果是怎么样？•答案或许是这样：重新复制数据，重新操作一次，心里很痛苦后期面临的问题•此时你会想到为什么Excel里面没有一种方法“只需要刷新一下即可得到更新后数据的统计结果”呢？期待的方法通过SQL与数据透视表结合使用，可快速汇总同一文件夹不同工作簿不同工作表相同字段的数据，并且可以随时刷新+=SQL与把同一文件夹下不同工作簿表头相同的工作表汇总（1）14第一步：新建一个名字为“汇总”的工作簿。新建一个“汇总”工作簿操作步骤215第二步：打开“汇总”工作簿，在“Sheet1”工作表中任意单击一个单元格，选择“数据”选项卡，单击“获取外部数据”组中的“现有连接”按钮，弹出“现有连接”对话框，单击“浏览更多”按钮。“现有连接”按钮注：该界面是Excel2010的，对于Excel2010以上版本也适用“浏览更多”按钮操作步骤3第三步：在弹出的“选取数据源”对话框中找到D盘的数据源中任何一个工作簿（如：销售部）下的工作表（如：销售1部），单击“确定”按钮。数据源在D盘的“汇总数据”文件中“销售1部”工作表“销售部”工作簿操作步骤4第四步：在弹出的“导入数据”对话框中选择“数据透视表和数据透视图”，将数据放置在“现有工作表”的A1单元格，并单击“属性”按钮，在弹出的“连接属性”对话框下的“使用状况”下勾选“打开文件时刷新数据”，清除在“定义”下“命令文本”的文本，输入SQL语句。选择数据透视表和数据透视图勾选“打开文件时刷新数据”最关键：单击“属性”按钮在此处输入SQL语句操作步骤518第五步：在“命令文本”中输入以下SQL语句，然后单击“确定”按钮。SELECT*FROM[D:\汇总数据\销售部.xlsx].[销售1部$]UNIONALLSELECT*FROM[D:\汇总数据\销售部.xlsx].[销售2部$]UNIONALLSELECT*FROM[D:\汇总数据\车间部.xlsx].[一车间$]UNIONALLSELECT*FROM[D:\汇总数据\车间部.xlsx].[二车间$]UNIONALLSELECT*FROM[D:\汇总数据\财务部.xlsx].[财务部$]输入SQL语句操作步骤6第六步：直接返回“导入数据”对话框，单击“确定”按钮，即可创建数据透视表，然后把“月”、“科目划分”拖至“报表筛选”，“部门”拖至“行标签”，“发生额”拖至“数值”，最终效果如下：各部门数据已经进行汇总统计附表：SQL语句原理讲解（1/3）SELECT*FROM[D:\汇总数据\销售部.xlsx].[销售1部$]Excel使用SQL的数据源放置的路径写法：[文件路径].[工作表$]文件夹：D盘的“汇总数据”文件夹，其SQL写法为“D:\汇总数据”工作簿：而“销售部”的Excel文件（2007以上的后缀为：xlsx)放置在该文件夹中，最后弄在一起的写法为：D:\汇总数据\销售部.xlsx工作表：在“销售部.xlsx”有2个工作表，其中一个为“销售1部”工作表，最后连接在一起的写法：[D:\汇总数据\销售部.xlsx].[销售1部$]详细解释路径写法文件夹工作簿工作表一步一步地对路径进行分解附表：SQL语句原理讲解（2/3）21SELECT语句讲解：SELECT*FROM[工作表$]表示从工作表中提取所有的数据，其中“*”代表所有数据SELECT*FROM[D:\汇总数据\销售部.xlsx].[销售1部$]：该语句的意思是从D盘的“汇总数据”文件夹中的“销售部.xlsx”工作簿中的“销售1部”工作表提取所有的数据UNIONALL语句讲解：表示把两个工作表的数据连接在一起，其演示如下班级姓名1班张三1班李四1班王五班级姓名2班王刘2班柳析表1表2SELECT*FROM[表1$]UNIONALLSELECT*FROM[表2$]班级姓名1班张三1班李四1班王五2班王刘2班柳析UNIONALL连接结果表附表：SQL语句原理讲解（3/3）22•1、所有的输入的字符及标点都是在英文状态输入法输入的，中文除外；•2、Excel里面使用的SQL语句与SQLServer、Oracle等大型数据库的语法有所差异；•3、Excel里面通过SQL语句结合数据透视表，可以处理300万左右的数据，特别是数据透视表的功能，可以很方面地进行汇总统计分析；•4、Excel2010以上版本SQL，也可以直接应用到Access等小型数据库;•6、操作源文件及操作演示请看附件。温馨提示：附表：启用经典数据透视表布局（应用网格中的字段拖放）操作23附表：若工作表的数据已经更新（如：添加、删除数据等），那么数据透视表中的汇总数据是否也可以同时更新呢？24操作：选中数据透视表中任一单元格，在“数据”选项卡的“连接”组中选择“全部刷新”或“刷新”即可。刷新示例与解释Select销售人员,订购量from[销售数据$A2:E19•select列字段名1，列字段名2from[工作表名$单元格区域]SELECTSIDAS学生编码,SexAS性别,ChineseAS语文,Chinese/10AS10分制语文成绩FROM[学生成绩$]•SELECT列字段名1AS列别名,列字段名2AS列别名,列字段名3AS列别名,FROM[工作表名$]SELECT*FROM[手机上网$]WHERE[上网流量（M）]60•SELECT字段FROM数据源WHERE条件表达式示例与解释SELECT*FROM[考试成绩$]WHERE等级LIKE[B-E]•SELECT列字段名FROM[表名$]WHERE列字段名LIKE[值1-值2]SELECT*FROM[登记表$]WHERE姓名LIKE林%•SELECT所有列字段FROM[表名$]WHERE列字段名LIKE匹配模式SELECT姓名,语文,数学,英语,(语文+数学+英语)AS总成绩FROM[成绩单$]ORDERBY语文+数学+英语DESC•增加总成绩字段同并将(语文+数学+英语)作为总成绩的值。ORDERBY…DESC（ASC)字段按降序（升序）排序LIKE模糊匹配，方括号表示某一范围的字符。或者“%林”，“林_”等ORDERBY…DESC（ASC)降（升）排序示例与解释•SELECT*FROM[申请单$]WHERE[家庭年收入(元)]/123000AND[家庭人均住房面积(平方米)]15AND市区常住户口年限=3同时满足多条件查询•SELECT*FROM[申请单$]WHERE[家庭年收入(元)]/123000OR[家庭人均住房面积(平方米)]15OR市区常住户口年限=3满足多条件之一的查询示例与解释SELECT姓名,籍贯,LEFT(籍贯,INSTR(籍贯,“市”))AS城市,RIGHT(籍贯,LEN(籍贯)-INSTR(籍贯,“市”))AS区县FROM[名单$]•增加字段“城市”，把从“籍贯”的左边提取“籍贯”字段中到“市”为止的字符长度为城市的值；增加字段“区县”，把从“籍贯”的右边提取“籍贯”字段的总长度减却到“市”为止的字符长度的长度为“区县”字段的值SELECTA.商品名称,A.单位,A.商品代码,A.商品数量,B.商品单价,C.供货商,A.商品数量*B.商品单价AS商品金额FROM[销售数量$]A,[价格$]B,[货源提供商$]CWHEREA.商品代码=B.商品代码ANDA.商品代码=C.商品代码•关联两张以上的表格，关联的条件是A表的商品代码与B表的商品代码相等，同时A表的商品代码与C表的商品代码也一致。第章3PowerPivot和PowerQuery1.用链接表向PowerPivot添加数据•选择表区任一单元格→【PowerPivot】/【添加到数据模型】如是Excel数据源•在PowerPivot窗口【开始】→【获取外部数据】/【从数据库】中选择“从Access（A)数据库→在【表导入向导】中选择数据路径→勾选所要导入的表如是Access数据源•插入数据透视表，实现多表透视再创建关系2.PowerQuery数据查询•【数据】→【新建查询】/【从文件】/【从文件夹】→找到放多个Excel工作薄的文件，自动选择多个文件夹→【合并】即可。合并文件夹•【数据】→【新建查询】/【从文件】/【Excel】→Excel工作薄，选择工作表→【合并】即可。合并工作表3.网络数据查询