ExcelHome培训 http://t.excelhome.net

分页符

腻如玉指涂朱粉 光似金刀剪紫霞

——Excel 2007综合班I课时小结之五

 

终于坚持到第五课时了，前面的内容还好，但这节课受到的打击很大，第一遍几乎听不懂，加上天气极热，有点急躁，只好静下心来，慢慢听，看了一些SQL的参考资料，总算回过神来，就像当初学习VBA一样，见识到了另一片天空。

一、利用获取外部数据实现数据透视表的动态链接

在前面的课程中，数据透视表的操作都是基于同一个工作簿，数据源都是在同一张工作簿的连续区域；但是如果对不同工作表或不同工作簿中的多个数据源进行合并汇总或创建数据透视表，Excel也同样提供了解决方案。首先是针对要变化的外部数据，要实现数据透视表的动态链接，主要有三种方法，其中通过OFFSET函数定义名称和创建表来实现的两种方法，在第三课时已经学习过，简单的复习一下，第三种方法是利用获取外部数据实现动态链接。

1、通过函数公式定义名称实现引用动态数据源

将可以实现动态引用数据源的函数公式定义为名称，在创建透视表时数据源区域直接输入该名称创建，首先定义名称data，在引用位置输入公式：=OFFSET(数据源!$A$1,,,COUNTA(数据源!$A:$A),COUNTA(数据源!$1:$1))，在创建数据透视表时，在表/区域中直接输入data即可。如图

 

当数据源增加行/列或是删除行/列时，透视表刷新后，自动随之改变数据源区域发生变化，透视表永远跟随发生了变化后的数据源创建。

此方法要求数据源中的首列和首行不包含空单元格，否则将无法用定义名称取得正确的数据区域。

2、创建表方式

将数据源通过插入表的方式创建为表后，在创建数据透视表，07中表支持行列内容的扩展－可实现动态数据透视表。如图

在Excel选项中的“公式”中“在公式中使用表名”勾选可以去掉在选择数据时出现的“表”。

3、利用获取外部数据实现数据透视表的动态链接

在任一张空白工作表中，点击“数据”选项卡的“获取外部数据”组中的 “现有连接”命令，如图

 

在“现有连接”对话框中选择“浏览更多”选择数据源所在的工作表，如图

 

在“选择表格”对话框中选择数据源所在的工作表，一般需要比较规范的数据源，首行都包含了列标题，此时应勾选“数据首行包含列标题”，如图

 

点击“确定”后，在“导入数据”对华军中，选择创建数据的显示类型，其中“表”只进行简单的排序和筛选，后两种方式可创建数据透视表或数据透视图，同时可以选择数据的放置位置，与前面学习的类似，如图

 

这样，可以对数据源进行更改，只需刷新数据透视表即可实现动态链接。需要注意的是，若数据源和创建的数据表同时打开时，如果只对数据源增加行，只需再创建的表中直接右键刷新，即可动态更新；但若在数据源中增加列，则需要点击“数据”选项卡下 “连接”组中的“连接”命令，在“工作簿连接”对话框中选择数据源所在工作簿，点击“属性”，在“连接属性”对话框中点击“确定”，即可实现创建的数据表对增加列的更新，如图

 

 

二、利用多重合并计算数据区域来创建数据透视表

若数据源在两张或多张共组表中，或在同一张工作表中的不同区域，若需要对数据合并计算，可以手工将数据放在一起进行计算。但若是数据源非常多，显然非常麻烦，此时可通过多重合并计算数据区域来创建数据透视表。在03版中已经介绍过，但没有具体使用，07版中创建数据透视表各数据透视图向导直接体现在功能区，需要重新调出来。

1、调出“数据透视表和数据透视图向导”的两种方法

（1）通过自定义快速访问工具栏，在“Excel选项”对话框的“自定义功能区”选项卡中，选择“所有命令”找到“数据透视表和数据透视图向导”，添加到“自定义快速访问工具栏”，可直接激活。

 

 

（2）通过快捷键：Alt+D+P，按住Alt键，按D键松开后再按P键。

两种方法调出“数据透视表和数据透视图向导”，可选择“多重合并计算数据图区域”，如图

 

2、多重合并计算数据区域的两个具体应用

（1）用多重合并计算数据区域创建数据透视表并比较差额

如图，对独立区域的数据源，要合并且比较差异

 

以前的方法是将两个数据区域合并在一起，同时添加辅助列以区分是哪个区域的数据（表1或表2），利用新表创建数据透视表并进行数据分析。

多重合并数据区域创建的步骤为：首先激活“数据透视表和数据透视图向导”，在数据源类型中选择“多重合并计算数据区域”，单击“下一步”，会出现“页字段”的选择项，这与普通创建方式不同，分为“创建单页字段”和“自定义页字段”两种，如图

 

创建单页字段，选择数据源所在的区域，可多次选择数据源并添加进去，如图

 

所有数据源添加完成后，下一步选择数据透视表的显示位置，根据需要选择后单击“完成”，如图

 

创建的数据透视表，字段会自动合并，生成字段为一个行、列、值、页1的数据透视表，如图

 

新生成的字段中，行字段是将所选数据区域的首列作为行标签，除首列外，列字段的数据仅充当一个列标识，修改成为行标签后就会失去原有功能而仅作为值进行计算，即除了首列，其它列均为数据值，页字段中的“页1”作为引用所在数据区域的一个区分，如图，“项1”表示第一个选中的数据区域，如果先选表2区域，在表示表2区域。如图

 

也可以将页1添加到列标签查看或计算。

自定义页字段同样是添加不同区域，不过可指定页字段数目以及为不同区域选择一个字段名，如图

 

同时，创建的数据透视表中，页字段显示的就是所指定的字段名，如图

 

（2）在多行多列中取不重复值

多重合并计算数据区域也有其局限性，即将第一列数据作为合并基准，即使需要合并的数据列有多个，也只会第一列作为行字段，其它列作为列字段。但也可以通过这个特点在多行多列中取不重复值。如图

 

要将上表中的不重复值取出来，有很多种方法，如利用函数等，但列用多重合并计算数据区域却非常方便。与以前的不同，多重合并计算数据区域允许首行首列区域空白，将值字段放到行标签实现多行多列取不重复值。具体操作方法非常简单，只要选择数据区域时多选择前面一空白列和上面一空白行即可。

三、通过导入外部数据“编辑OLE DB查询”创建数据透视表

如果说前面的这些内容还能够理解的话，后面的内容基本上就是天书了，不过还好功夫不负有心人，多看几遍视频，到论坛中学习一下SQL语句的基本知识，发现也没有那么不能理解嘛。

这是在论坛上和百度上抓的OLE DB的一些简单知识，放在这里自己以后不用再去找了。

OLE DB是Object Linking and Embedding Database的缩写，中文为对象链接嵌入数据库，是微软为以通过以统一方式访问不同类型的数据存储设计的一种应用程序接口，被设计为ODBC的一种高级替代者和继承者，把它的功能扩展到支持到更多种类型的非关系型数据库，例如可能不支持SQL的对象数据库和电子表格（如Excel）。——总算和Excel有点联系了^_^

运用OLE DB查询技术讲不通工作表，甚至多个工作簿的不同数据区域进行合并汇总，以生成动态的数据透视表，可避免多重合并数据区域创建的数据透视表只能选择第一列作为行字段的限制。——终于知道为什么要学这玩意了。

通过导入外部数据时，编辑OLE DB的查询方法，借助OLE DB技术，对数据列表进行连接及存储，以形成新的数据源来创建数据透视表。——好像有点懂了！

应用导入外部数据，结合OLE DB查询中使用SQL语句，可以对不同工作表或不同工作簿中结构相同的数据表进行汇总，创建动态数据透视表，并且可以不受多重合并计算区域数据透视表只能选择第一列作为行字段的限制。——已经有些迫不及待想看看咋弄的嘞！

1、通过编辑OLE DB查询汇总同一工作薄中所有数据

对同一工作簿中的多张结构相同的工作表中的数据，若只想对部分数据进行汇总，同时要创建分科目、分部门的动态费用分析数据透视表。当然可以将所有的数据源合并在一张共组表中，并通过添加辅助列来标识数据原来的所在区域，创建数据透视表。还是繁琐的问题，就不多说了。

利用SQL语句的方法看似很难，但照葫芦画瓢的方法多学几次，也很好理解。操作步骤如下：

首先新建一张空白工作表以创建数据透视表，单击“数据”选项卡下“获取外部数据”组中的“现有连接”命令，选择“浏览更多”选择数据源所在的工作簿，如图

 

单击“确定”后选择创建数据透视表，同时单击“数据”选项卡下“连接”组中的“属性”命令，在“连接属性”对话框中的“使用状况”选项卡下勾选“打开文件时刷新数据”或“刷新频率”并选择间隔时间，以实现数据透视表的动态更新。如图

 

单击“定义”选项卡，在“命令文本”中清空原有内容，输入SQL连接语句，如图

 

发现写SQL语句时最好还是在文本文档中编写好再复制进去比较好，本例中的SQL语句为：

 

创建好的数据透视表字段列表中含有部门、科目名称和实际发生额三个字段，其中“部门”是数据源中没有的，作为每个数据源所在工作表的区别，通过将字段拖入相应的标签，即可创建数据透视表。如图

 

2、SQL语句的基本知识

（1）SQL语句的基本格式

SELECT ? FROM [ $] UNION ALL SELECT ? FROM [ $]

SELECT语句用于从数据库表中获取部分数据并用UNION ALL连接在一起，其中英文字母不区分大小写，但要在半角状态下输入；“?”表示金提取某几列数据，列字段用半角逗号隔开；除已用半角逗号隔开的外，每个字符后均有空格；[ ]内输入的是工作表名称，名称后需添加“$”以区别是是绝对引用还是相对引用；SELECT后的数据须一一对应，若没有，可用NULL代替。

 

（2）操作方法

单击“数据”选项卡下“连接”组中的“属性”命令，在“连接属性”对话框中的“定义”选项卡下的“命令文本”中输入SQL语句。

单一表数据：

 

多个表数据：

 

添加字段项：

 

此时“表名1”、“表名2”所属的字段名自动生成为Expr1000。

添加字段项并修改字段名称：

 

全部选取所有的字段信息：

 

3、利用数据透视表对列数不等的数据源表进行多表合并

对列数不等的两张表进行合并，如图

 

 

以上两表分别为8列和6列数据，若要将两表数据合并，需在“命令文本”中输入以下SQL语句：

 

这段语句是分别从“采购”、“付款”两张数据表中获取“金额”、“付款金额”、“日期”、“材料名称”、“供应商”五个字段，把“采购”表中的字段名“金额”和“付款”表中的字段名“付款金额”用AS分别自定义为新字段名“应付金额”和“已付金额”，并把“采购”表中所缺的“已付金额”和“付款”表中所缺的“应付金额”通过AS自定义添加，字段赋值为“NULL”。

之后可创建空白的数据透视表，通过相应的设置及添加计算字段，可以得到不等列数据源创建的“应付账款分析”的数据透视表。

四、数据透视表SQL语句实例

1、通过编辑OLE DB查询实现员工分类统计

三种方法：一是通过多次组合后连接在一起，二是添加辅助列，增加“部门”、“学历”、“年龄区间”，再添加一列“次分类”加入对应的“部门”、“年龄分段”、“学历层次”。

第三种方法是SQL语句

 

2、利用编辑OLE DB查询创建动态原材料收发存汇总表

将数据源规范后，使用SQL编写：

 

 

添加计算项：结存=期初+入库-出库

若出现数据位数有效性的问题，应事先修改数据透视表中数据的单元格格式。

3、查找复杂数据表之间的差异

利用SQL语句自动生成字段以比较采购与付款清单。

 

 

总算坚持写完了最后一课的小结，对SQL的SELECT语句的基本格式有了基本的了解。在工作中确实有很多需要用到的地方，近半年来通过不断的学习，极大提高了自己的工作效率。

感谢木兰老师，感谢两位班主任！