数据透视表与SQL典型应用——数据分析人士必杀技

@ETwise

工作中常收集到的数据：同一文件夹下不同工作簿表头相同的工作表

这些数据放在D盘的“汇总数据”文件夹

包含“财务部”、“车间部”、“销售部”三个工作簿

“销售部”工作簿包含“销售1部”、“销售2部”2个工作表

注：所有工作簿的工作表的表头都是相同的

①

②

③

1

表头相同

如何把这些工作表的所有数据汇总在一起进行统计计算

常规做法：把各个工作簿的工作表的数据一个一个地复制到同一个工作表中，然后使用数据透视表进行汇总

操作过程

结果

如果这些工作表更新了数据或删除部分数据，此时的结果是怎么样？
答案或许是这样：重新复制数据，重新操作一次，心里很痛苦

此时你会想到为什么Excel里面没有一种方法“只需要刷新一下即可得到更新后数据的统计结果”呢？

后期面临的问题

期待的方法

2

通过SQL与数据透视表结合使用，可快速汇总同一文件夹不同工作簿不同工作表相同字段的数据，并且可以随时刷新

数据透视表

SQL语句

+

=

数据处理利器

报表人士必杀技

Excel必修课

数据分析人士工具箱

3

请看如何操作：如何使用SQL与数据透视表把同一文件夹下不同工作簿表头相同的工作表进行汇总统计（1）

第一步：新建一个名字为“汇总”的工作簿。

新建一个“汇总”工作簿

4

操作步骤2

第二步：打开“汇总”工作簿，在“Sheet1”工作表中任意单击一个单元格，选择“数据”选项卡，单击“获取外部数据”组中的“现有连接”按钮，弹出“现有连接”对话框，单击“浏览更多”按钮。

“现有连接”按钮

注：该界面是Excel2010的，对于Excel2007也适用

“浏览更多”按钮

5

操作步骤3

第三步：在弹出的“选取数据源”对话框中找到D盘的数据源中任何一个工作簿（如：销售部）下的工作表（如：销售1部），单击“确定”按钮。

数据源在D盘的“汇总数据”文件中

“销售1部”工作表

“销售部”工作簿

6

操作步骤4

第四步：在弹出的“导入数据”对话框中选择“数据透视表和数据透视图”，将数据放置在“现有工作表”的A1单元格，并单击“属性”按钮，在弹出的“连接属性”对话框下的“使用状况”下勾选“打开文件时刷新数据”，清除在“定义”下“命令文本”的文本，输入SQL语句。

选择数据透视表和数据透视图

勾选“打开文件时刷新数据”

最关键：单击“属性”按钮

在此处输入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语句

8

操作步骤6

第六步：直接返回“导入数据”对话框，单击“确定”按钮，即可创建数据透视表，然后把“月”、“科目划分”拖至“报表筛选”，“部门”拖至“行标签”，“发生额”拖至“数值”，最终效果如下：

各部门数据已经进行汇总统计

9

附表：SQL语句原理讲解（1/3）

Excel使用SQL的数据源放置的路径写法：[文件路径].[工作表$]
文件夹：D盘的“汇总数据”文件夹，其SQL写法为“D:\汇总数据”
工作簿：而“销售部”的Excel文件（2007以上的后缀为：xlsx)放置在该文件夹中，最后弄在一起的写法为：D:\汇总数据\销售部.xlsx
工作表：在“销售部.xlsx”有2个工作表，其中一个为“销售1部”工作表，最后连接在一起的写法：[D:\汇总数据\销售部.xlsx].[销售1部$]

详细解释路径写法

文件夹

工作簿

工作表

一步一步地对路径进行分解

10

附表： SQL语句原理讲解（2/3）

SELECT语句讲解：SELECT * FROM [工作表$]
表示从工作表中提取所有的数据，其中”*”代表所有数据
SELECT * FROM [D:\汇总数据\销售部.xlsx].[销售1部$]：该语句的意思是从D盘的“汇总数据”文件夹中的“销售部.xlsx”工作簿中的“销售1部”工作表提取所有的数据

UNION ALL语句讲解：
表示把两个工作表的数据连接在一起，其演示如下

表1

表2

SELECT * FROM [表1$]
UNION ALL
SELECT * FROM [表2$]

UNION ALL
连接结果表

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、操作源文件及操作演示请看附件。

12

附表：启用经典数据透视表布局（应用网格中的字段拖放）操作

13

附表：若工作表的数据已经更新（如：添加、删除数据等），那么数据透视表中的汇总数据是否也可以同时更新呢？

操作：选中数据透视表中任一单元格，在“数据”选项卡的“连接”组中选择“全部刷新”或“刷新”即可。

14

刷新

《Excel 2010 SQL完全应用》一书简介

第一本专为办公人士、报表人士、数据分析与数据挖掘人士、经营分析人士、Excel爱好者撰写的有关在Excel中如何使用SQL的书籍；
写书初衷：全面介绍在Excel中如何使用SQL，其实SQL语句真的很简单；
温馨提示：使用SQL结合数据透视表，可以快速汇总统计数据，SQL也称为Excel中的“瑞士军刀”。

如果对该书有兴趣的话，可去卓越、当当、京东购买
也可以关注作者们的新浪微博：@ETwise、@amulee、@biaotiger1、@Excel技巧网_官方微博

15

16

Thank You !