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