数据透视表与SQL典型应用
- 格式：ppt
- 大小：1.55 MB
- 文档页数：18
当前位置：文档之家 › 数据透视表与SQL典型应用
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
报表人士必杀技
2020/11/30
3
请看如何操作：如何使用SQL与数据透视表把同一文件夹下 不同工作簿表头相同的工作表进行汇总统计（1）
第一步：新建一个名字为“汇总”的工作簿。
2020/11/30
新建一个“汇总”工 作簿
4
操作步骤2
第二步：打开“汇总”工作簿，在“Sheet1”工作表中任意单击一 个单元格，选择“数据”选项卡，单击“获取外部数据”组中的“现 有连接”按钮，弹出“现有连接”对话框，单击“浏览更多”按钮。
1
如何把这些工作表的所有数据汇总在一起进行统计计算
操作过程
结果
常规做法：把各个工作簿的工作 表的数据一个一个地复制到同一 个工作表中，然后使用数据透视 表进行汇总
后期 如果这些工作表更新了数据或删除部分数据，此时的结果
面临
是怎么样？
的问 答案或许是这样：重新复制数据，重新操作一次，心里很
题
痛苦
“销售部” 工作簿
“销售1部” 工作表
2020/11/30
6
操作步骤4
第四步：在弹出的“导入数据”对话框中选择“数据透视表和数据透视 图”，将数据放置在“现有工作表”的A1单元格，并单击“属性”按钮， 在弹出的“连接属性”对话框下的“使用状况”下勾选“打开文件时刷 新数据”，清除在“定义”下“命令文本”的文本，输入SQL语句。
后连接在一起的写法：[D:\汇总数据\销售部.xlsx].[销售1部$]
SELECT * FROM [D:\汇总数据\销售部.xlsx].[销售1部$] 详细解释路径写法
2020/11/30
一步一步地对路径进行分解 文件夹
工作簿 工作表
10
附表： SQL语句原理讲解（2/3）
SELECT语句讲解：SELECT * FROM [工作表$] 表示从工作表中提取所有的数据，其中”*”代表所有数据 SELECT * FROM [D:\汇总数据\销售部.xlsx].[销售1部$]：该语句的意思是从
期待 此时你会想到为什么Excel里面没有一种方法“只需要刷
的方
新一下即可得到更新后数据的统计结果”呢？
法
2020/11/30
2
通过SQL与数据透视表结合使用，可快速汇总同一文件夹不 同工作簿不同工作表相同字段的数据，并且可以随时刷新
数据透视表
Excel必修课
+
SQL语句
数据处理利器
= 数据分析人士工具箱
D盘的“汇总数据”文件夹中的“销售部.xlsx”工作簿中的“销售1部”工作表 提取所有的数据
UNION ALL语句讲解： 表示把两个工作表的数据连接在一起，其演示如下
表1
表2
2020/11/30
班级 1班 1班 1班
班级 2班 2班
姓名 张三 李四 王五
姓名 王刘 柳析
SELECT * FROM [表1$] UNION ALL SELECT * FROM [表2$]
“现有Байду номын сангаас接”按 钮
2020/11/30
“浏览更多”按 钮
注：该界面是Excel2010的， 对于Excel2007也适用
5
操作步骤3
第三步：在弹出的“选取数据源”对话框中找到D盘的数据源中任何 一个工作簿（如：销售部）下的工作表（如：销售1部），单击“确 定”按钮。
数据源在D盘的“汇总数 据”文件中
数据透视表与SQL典型应用
工作中常收集到的数据：同一文件夹下不同工作簿表头相同 的工作表
①
这些数据放在D盘的 “汇总数据”文件夹
包含“财务部”、
“车间部”、“销售
②
部”三个工作簿
表头相同
注：2所020有/1工1/作30 簿的工作表的表头都是相同的
③
“销售部”工作簿包含 “销售1部”、“销售 2部”2个工作表
称为Excel中的“瑞士军刀”。
2020/11/30
如果对该书有兴趣的话，可去卓越、当当、 京东购买
也可以关注作者们的新浪微博： @ETwise、@amulee、@biaotiger1、 @Excel技巧网_官方微博
15
Thank You !
2020/11/30
16
谢谢大家！
选择数据透视表 和数据透视图
最关键：单击 “属性”按钮
2020/11/30
勾选“打开文件 时刷新数据”
在此处输入SQL 语句
7
操作步骤5
第五步：在“命令文本”中输入以下SQL语句，然后单击“确定”按钮。
SELECT * FROM [D:\汇总数据\销售部.xlsx].[销售1部$] UNION ALL SELECT * FROM [D:\汇总数据\销售部.xlsx].[销售2部$] UNION ALL SELECT * FROM [D:\汇总数据\车间部.xlsx].[一车间$] UNION ALL SELECT * FROM [D:\汇总数据\车间部.xlsx].[二车间$] UNION ALL SELECT * FROM [D:\汇总数据\财务部.xlsx].[财务部$]
输入SQL语句
2020/11/30
8
操作步骤6
第六步：直接返回“导入数据”对话框，单击“确定”按钮，即可创建 数据透视表，然后把“月”、“科目划分”拖至“报表筛选”，“部门” 拖至“行标签”，“发生额”拖至“数值”，最终效果如下：
各部门数据已经 进行汇总统计
2020/11/30
9
附表：SQL语句原理讲解（1/3）
2020/11/30
12
附表：启用经典数据透视表布局（应用网格中的字段拖放） 操作
2020/11/30
13
附表：若工作表的数据已经更新（如：添加、删除数据等）， 那么数据透视表中的汇总数据是否也可以同时更新呢？
操作：选中数据透视表中任一单元格，在“数据”选项卡的“连接”组 中选择“全部刷新”或“刷新”即可。
UNION ALL 连接结果表
班级 1班 1班 1班 2班 2班
姓名 张三 李四 王五 王刘 柳析 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、操作源文件及操作演示请看附件。
Excel使用SQL的数据源放置的路径写法：[文件路径].[工作表$] 文件夹：D盘的“汇总数据”文件夹，其SQL写法为“D:\汇总数据” 工作簿：而“销售部”的Excel文件（2007以上的后缀为：xlsx)放置在该文件
夹中，最后弄在一起的写法为：D:\汇总数据\销售部.xlsx 工作表：在“销售部.xlsx”有2个工作表，其中一个为“销售1部”工作表，最
刷新
2020/11/30
14
《Excel 2010 SQL完全应用》一书简介
第一本专为办公人士、报表人士、数据分析与数据挖掘人士、经营分析 人士、Excel爱好者撰写的有关在Excel中如何使用SQL的书籍；
写书初衷：全面介绍在Excel中如何使用SQL，其实SQL语句真的很简单； 温馨提示：使用SQL结合数据透视表，可以快速汇总统计数据，SQL也
2020/11/30
3
请看如何操作：如何使用SQL与数据透视表把同一文件夹下 不同工作簿表头相同的工作表进行汇总统计（1）
第一步：新建一个名字为“汇总”的工作簿。
2020/11/30
新建一个“汇总”工 作簿
4
操作步骤2
第二步：打开“汇总”工作簿，在“Sheet1”工作表中任意单击一 个单元格，选择“数据”选项卡，单击“获取外部数据”组中的“现 有连接”按钮，弹出“现有连接”对话框，单击“浏览更多”按钮。
1
如何把这些工作表的所有数据汇总在一起进行统计计算
操作过程
结果
常规做法：把各个工作簿的工作 表的数据一个一个地复制到同一 个工作表中，然后使用数据透视 表进行汇总
后期 如果这些工作表更新了数据或删除部分数据，此时的结果
面临
是怎么样？
的问 答案或许是这样：重新复制数据，重新操作一次，心里很
题
痛苦
“销售部” 工作簿
“销售1部” 工作表
2020/11/30
6
操作步骤4
第四步：在弹出的“导入数据”对话框中选择“数据透视表和数据透视 图”，将数据放置在“现有工作表”的A1单元格，并单击“属性”按钮， 在弹出的“连接属性”对话框下的“使用状况”下勾选“打开文件时刷 新数据”，清除在“定义”下“命令文本”的文本，输入SQL语句。
后连接在一起的写法：[D:\汇总数据\销售部.xlsx].[销售1部$]
SELECT * FROM [D:\汇总数据\销售部.xlsx].[销售1部$] 详细解释路径写法
2020/11/30
一步一步地对路径进行分解 文件夹
工作簿 工作表
10
附表： SQL语句原理讲解（2/3）
SELECT语句讲解：SELECT * FROM [工作表$] 表示从工作表中提取所有的数据，其中”*”代表所有数据 SELECT * FROM [D:\汇总数据\销售部.xlsx].[销售1部$]：该语句的意思是从
期待 此时你会想到为什么Excel里面没有一种方法“只需要刷
的方
新一下即可得到更新后数据的统计结果”呢？
法
2020/11/30
2
通过SQL与数据透视表结合使用，可快速汇总同一文件夹不 同工作簿不同工作表相同字段的数据，并且可以随时刷新
数据透视表
Excel必修课
+
SQL语句
数据处理利器
= 数据分析人士工具箱
D盘的“汇总数据”文件夹中的“销售部.xlsx”工作簿中的“销售1部”工作表 提取所有的数据
UNION ALL语句讲解： 表示把两个工作表的数据连接在一起，其演示如下
表1
表2
2020/11/30
班级 1班 1班 1班
班级 2班 2班
姓名 张三 李四 王五
姓名 王刘 柳析
SELECT * FROM [表1$] UNION ALL SELECT * FROM [表2$]
“现有Байду номын сангаас接”按 钮
2020/11/30
“浏览更多”按 钮
注：该界面是Excel2010的， 对于Excel2007也适用
5
操作步骤3
第三步：在弹出的“选取数据源”对话框中找到D盘的数据源中任何 一个工作簿（如：销售部）下的工作表（如：销售1部），单击“确 定”按钮。
数据源在D盘的“汇总数 据”文件中
数据透视表与SQL典型应用
工作中常收集到的数据：同一文件夹下不同工作簿表头相同 的工作表
①
这些数据放在D盘的 “汇总数据”文件夹
包含“财务部”、
“车间部”、“销售
②
部”三个工作簿
表头相同
注：2所020有/1工1/作30 簿的工作表的表头都是相同的
③
“销售部”工作簿包含 “销售1部”、“销售 2部”2个工作表
称为Excel中的“瑞士军刀”。
2020/11/30
如果对该书有兴趣的话，可去卓越、当当、 京东购买
也可以关注作者们的新浪微博： @ETwise、@amulee、@biaotiger1、 @Excel技巧网_官方微博
15
Thank You !
2020/11/30
16
谢谢大家！
选择数据透视表 和数据透视图
最关键：单击 “属性”按钮
2020/11/30
勾选“打开文件 时刷新数据”
在此处输入SQL 语句
7
操作步骤5
第五步：在“命令文本”中输入以下SQL语句，然后单击“确定”按钮。
SELECT * FROM [D:\汇总数据\销售部.xlsx].[销售1部$] UNION ALL SELECT * FROM [D:\汇总数据\销售部.xlsx].[销售2部$] UNION ALL SELECT * FROM [D:\汇总数据\车间部.xlsx].[一车间$] UNION ALL SELECT * FROM [D:\汇总数据\车间部.xlsx].[二车间$] UNION ALL SELECT * FROM [D:\汇总数据\财务部.xlsx].[财务部$]
输入SQL语句
2020/11/30
8
操作步骤6
第六步：直接返回“导入数据”对话框，单击“确定”按钮，即可创建 数据透视表，然后把“月”、“科目划分”拖至“报表筛选”，“部门” 拖至“行标签”，“发生额”拖至“数值”，最终效果如下：
各部门数据已经 进行汇总统计
2020/11/30
9
附表：SQL语句原理讲解（1/3）
2020/11/30
12
附表：启用经典数据透视表布局（应用网格中的字段拖放） 操作
2020/11/30
13
附表：若工作表的数据已经更新（如：添加、删除数据等）， 那么数据透视表中的汇总数据是否也可以同时更新呢？
操作：选中数据透视表中任一单元格，在“数据”选项卡的“连接”组 中选择“全部刷新”或“刷新”即可。
UNION ALL 连接结果表
班级 1班 1班 1班 2班 2班
姓名 张三 李四 王五 王刘 柳析 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、操作源文件及操作演示请看附件。
Excel使用SQL的数据源放置的路径写法：[文件路径].[工作表$] 文件夹：D盘的“汇总数据”文件夹，其SQL写法为“D:\汇总数据” 工作簿：而“销售部”的Excel文件（2007以上的后缀为：xlsx)放置在该文件
夹中，最后弄在一起的写法为：D:\汇总数据\销售部.xlsx 工作表：在“销售部.xlsx”有2个工作表，其中一个为“销售1部”工作表，最
刷新
2020/11/30
14
《Excel 2010 SQL完全应用》一书简介
第一本专为办公人士、报表人士、数据分析与数据挖掘人士、经营分析 人士、Excel爱好者撰写的有关在Excel中如何使用SQL的书籍；
写书初衷：全面介绍在Excel中如何使用SQL，其实SQL语句真的很简单； 温馨提示：使用SQL结合数据透视表，可以快速汇总统计数据，SQL也