第十四章数据透视表和外部数据库文件的使用

1、 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用数据透视表和外部数据文件的使用第十四章第十四章数据透视表和外部数据文件数据透视表和外部数据文件的使用的使用 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用数据透视表和外部数据文件的使用主主 要要 内内 容容 14.2 14.2 数据透视表的创建和设置数据透视表的创建和设置 14.1 14.1 数据透视表相关概念数据

2、透视表相关概念 14.3 14.3 数据透视表内部重要操作数据透视表内部重要操作 14.5 14.5 利用文本文件获取数据利用文本文件获取数据 14.6 14.6 导入外部数据导入外部数据 14.4 14.4 了解外部数据库文件了解外部数据库文件 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用数据透视表和外部数据文件的使用 14.1 14.1 数据透视表相关概念数据透视表相关概念1.1.关于数据透视表关于数据透视表 数据透视表是用来从数据透视表是用来从ExcelExcel数据列

3、表、关系数据库文件或数据列表、关系数据库文件或OLAPOLAP多维多维数据集中的特殊字段中总结信息的分析工具。它是一种交互式报表，可数据集中的特殊字段中总结信息的分析工具。它是一种交互式报表，可以快速分类汇总和比较大量的数据，并可以随时选择其中页、行和列中以快速分类汇总和比较大量的数据，并可以随时选择其中页、行和列中的不同元素，以快速查看源数据的不同统计结果，同时还可以随意显示的不同元素，以快速查看源数据的不同统计结果，同时还可以随意显示和打印出用户选择区域的明细数据。和打印出用户选择区域的明细数据。 数据透视表的名字来源于它有数据透视表的名字来源于它有“透视透视”表格的能力，从大量看似无表格

4、的能力，从大量看似无关的数据中寻找背后的联系，从而将纷繁的数据转化为有价值的信息，关的数据中寻找背后的联系，从而将纷繁的数据转化为有价值的信息，以供研究和决策使用。以供研究和决策使用。 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用数据透视表和外部数据文件的使用2.2.数据透视表的数据组织数据透视表的数据组织用户可以从用户可以从4 4种类型的数据源中来创建数据透视表。种类型的数据源中来创建数据透视表。（1 1）ExcelExcel数据列表数据列表（2 2）外部数据源）外部数据源

5、（3 3）进行多重合并计算的独立）进行多重合并计算的独立ExcelExcel数据列表数据列表（4 4）其他的数据透视表）其他的数据透视表 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用数据透视表和外部数据文件的使用数据透视表中的术语：数据透视表中的术语：数据源数据源：从中创建数据透视表的数据列表或多维数据集：从中创建数据透视表的数据列表或多维数据集轴轴：数据透视表中的一维，如行、列、页：数据透视表中的一维，如行、列、页列字段列字段：信息的种类，等价于数据列表中列：信息的种类，等

6、价于数据列表中列行字段行字段：在数据透视表中具有行方向的字段：在数据透视表中具有行方向的字段分页符分页符：数据透视表中进行分页的字段：数据透视表中进行分页的字段字段标题字段标题：描述字段内容的标志。可以通过拖动字段标题对数据透视表进：描述字段内容的标志。可以通过拖动字段标题对数据透视表进 行透视。行透视。项目项目：组成字段的成员：组成字段的成员组组：一组项目的集合：一组项目的集合透视透视：通过改变一个或多个字段的位置来重新安排数据透视表：通过改变一个或多个字段的位置来重新安排数据透视表汇总函数汇总函数：ExcelExcel计算表格中数据的值的函数计算表格中数据的值的函数分类汇总分类汇总：数据透

7、视表中对一行或一列单元格的分类汇总：数据透视表中对一行或一列单元格的分类汇总刷新刷新：重新计算数据透视表，反映目前数据源的状态：重新计算数据透视表，反映目前数据源的状态 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用数据透视表和外部数据文件的使用3.3.数据透视表的工具栏数据透视表的工具栏例例14.1 14.1 数据透视表的创建数据透视表的创建 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章

8、数据透视表和外部数据文件的使用数据透视表和外部数据文件的使用 14.2 14.2 数据透视表的创建和设置数据透视表的创建和设置1.1.创建数据列表创建数据列表例例14.1 14.1 数据透视表的创建数据透视表的创建2.2.创建动态的数据透视表创建动态的数据透视表例例14.2 14.2 使用数据列表功能创建动态数据透视表使用数据列表功能创建动态数据透视表 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用数据透视表和外部数据文件的使用3.3.改变数据透视表的布局改变数据透视表的布局（

9、1 1）显示页面区域中字段的汇总）显示页面区域中字段的汇总（2 2）数据透视表的分页显示功能）数据透视表的分页显示功能例例14.3 14.3 分页显示数据透视表分页显示数据透视表（3 3）设置数据透视表的数据字段并排显示）设置数据透视表的数据字段并排显示例例14.4 14.4 设置数据透视表的数据字段并排显示设置数据透视表的数据字段并排显示（4 4）重命名字段）重命名字段 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用数据透视表和外部数据文件的使用4.4.整理数据透视表数据整理

10、数据透视表数据（1 1）显示所有数据项）显示所有数据项（2 2）隐藏数据项）隐藏数据项（3 3）隐藏页字段数据）隐藏页字段数据例例14.4 14.4 隐藏页字段数据操作隐藏页字段数据操作（4 4）清除数据透视表原有数据项）清除数据透视表原有数据项 数据透视表的数据源改变后，会导致字段下拉列表中有些无用的数据项存数据透视表的数据源改变后，会导致字段下拉列表中有些无用的数据项存在。例如有些销售人员已经离开公司，但他们的名字仍然会在数据透视表在。例如有些销售人员已经离开公司，但他们的名字仍然会在数据透视表的数据项中存在。即使用户对数据透视表进行刷新，这些名字仍然会与新的数据项中存在。即使用户对数据透

11、视表进行刷新，这些名字仍然会与新名字同时显示。清除原有数据项的操作方法：名字同时显示。清除原有数据项的操作方法：步骤步骤1 1：讲存在无用数据的字段拖到数据透视表以外的区域，即从表中清除：讲存在无用数据的字段拖到数据透视表以外的区域，即从表中清除步骤步骤2 2：在数据透视表内单击右键，刷新数据：在数据透视表内单击右键，刷新数据步骤步骤3 3：在从字段列表中将该字段拖回到原来的区域位置：在从字段列表中将该字段拖回到原来的区域位置 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用数据

12、透视表和外部数据文件的使用5.5.数据透视表的刷新数据透视表的刷新源数据发生变化后，数据透视表本身并不会自动刷新。要对数据透视表源数据发生变化后，数据透视表本身并不会自动刷新。要对数据透视表进行刷新，只需在数据透视表内单击右键，选择进行刷新，只需在数据透视表内单击右键，选择“刷新数据刷新数据”即可。即可。例例14.5 14.5 在打开文件时刷新的设置在打开文件时刷新的设置6.6.设置数据透视表的格式设置数据透视表的格式用户可以用设置单元格格式的方法来修改数据透视表中单元格的外观，用户可以用设置单元格格式的方法来修改数据透视表中单元格的外观，如改变字体、字号、设置数字格式、填充颜色等。如改变字体

13、、字号、设置数字格式、填充颜色等。例例14.6 14.6 防止刷新数据后格式丢失的设置防止刷新数据后格式丢失的设置（使用例（使用例14.514.5文件演示，下同）文件演示，下同）例例14.7 14.7 防止刷新数据后格式丢失的设置防止刷新数据后格式丢失的设置例例14.8 14.8 数据透视表自动套用格式数据透视表自动套用格式例例14.9 14.9 改变数据透视表中所有单元格的数字格式改变数据透视表中所有单元格的数字格式 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用数据透视表和

14、外部数据文件的使用 14.3 14.3 数据透视表内部重要操作数据透视表内部重要操作1.1.在数据透视表中排序在数据透视表中排序（1 1）使用手动排序）使用手动排序例例14.10 14.10 在数据透视表中进行手动排序在数据透视表中进行手动排序 （2 2）使用自动排序）使用自动排序例例14.11 14.11 在数据透视表中进行自动排序在数据透视表中进行自动排序 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用数据透视表和外部数据文件的使用2.2.数据透视表的项目组合数据透视表的项

15、目组合数据透视表能够自动地在外部字段的标题下对内部字段的项进行组合，数据透视表能够自动地在外部字段的标题下对内部字段的项进行组合，并可按照用户的要求来创建各个内部字段的分类汇总。并可按照用户的要求来创建各个内部字段的分类汇总。（1 1）组合数据透视表的指定项）组合数据透视表的指定项例例14.12 14.12 组合数据透视表的指定项组合数据透视表的指定项（2 2）数字项组合）数字项组合例例14.13 14.13 按数字项组合按数字项组合（3 3）按日期或时间项组合）按日期或时间项组合例例14.14 14.14 按日期或时间项组合按日期或时间项组合（4 4）取消项目组合）取消项目组合 ExcelE

16、xcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用数据透视表和外部数据文件的使用3.3.在数据透视表中执行计算在数据透视表中执行计算在默认设置下，在默认设置下，ExcelExcel对数据透视表数据区域的数字字段应用求和函数，对数据透视表数据区域的数字字段应用求和函数，对非数字字段应用计数函数。对非数字字段应用计数函数。ExcelExcel提供了多种汇总方式供用户选择，提供了多种汇总方式供用户选择，包括包括“求和求和”、“计数计数”、“平均值平均值”、“最大值最大值”、“最小值最小值”、“乘积乘

17、积”等。等。（1 1）对同一字段应用多种汇总方式）对同一字段应用多种汇总方式例例14.15 14.15 对同一字段应用多种汇总方式对同一字段应用多种汇总方式（2 2）自定义计算）自定义计算例例14.16 14.16 应用自定义计算应用自定义计算 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用数据透视表和外部数据文件的使用4.4.在数据透视表中使用计算字段和计算项在数据透视表中使用计算字段和计算项除了刚才介绍的多种汇总方式以外，除了刚才介绍的多种汇总方式以外，ExcelExcel

18、还允许用户向数据透视表中添还允许用户向数据透视表中添加计算字段和计算项。计算字段是通过对表中现有的字段执行计算后得到加计算字段和计算项。计算字段是通过对表中现有的字段执行计算后得到的新字段；计算项则是在已有的字段中插入新的项，是通过对该字段现有的新字段；计算项则是在已有的字段中插入新的项，是通过对该字段现有的其他项执行计算后得到的。一旦创建了自定义的字段或项，的其他项执行计算后得到的。一旦创建了自定义的字段或项，ExcelExcel就允就允许在表格中使用它们，它们就像是在数据源中真实存在的一样。许在表格中使用它们，它们就像是在数据源中真实存在的一样。1.1.创建计算字段创建计算字段例例14.1

19、7 14.17 创建计算字段创建计算字段2.2.添加计算项添加计算项例例14.18 14.18 添加计算项添加计算项 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用数据透视表和外部数据文件的使用 14.4 14.4 了解外部数据库文件了解外部数据库文件用户在使用用户在使用ExcelExcel进行工作的时候，不但可以使用工作表中的数据，还可进行工作的时候，不但可以使用工作表中的数据，还可以访问外部数据库文件。使用外部数据库文件有很多有优点，其中最大的以访问外部数据库文件。使用外部

20、数据库文件有很多有优点，其中最大的优点就是用户可以利用优点就是用户可以利用导入和查询导入和查询，在，在ExcelExcel中使用熟悉的工具对外部数中使用熟悉的工具对外部数据进行处理和分析。据进行处理和分析。由于由于Excel 2003Excel 2003工作表的行不能超过工作表的行不能超过6553665536行，列不能超过行，列不能超过256256列，因此用列，因此用户即使拥有最先进的计算机系统，也无法处理非常巨大的表格。而许多外户即使拥有最先进的计算机系统，也无法处理非常巨大的表格。而许多外部数据库文件在操作系统的允许下几乎可以部数据库文件在操作系统的允许下几乎可以无限大无限大，其保存的记录

21、数量也，其保存的记录数量也可以是可以是ExcelExcel工作表的千万倍。外部数据库文件可以是工作表的千万倍。外部数据库文件可以是文本文件文本文件、MicorsoftMicorsoft Access Access数据库数据库、MicorsoftMicorsoft SQL Server SQL Server数据库数据库、MicorsoftMicorsoft LOAPLOAP多维数据集多维数据集、dBasedBase数据库数据库等。等。多数情况下，用户并不需要导入整个外部数据文件，只需通过对外部数据多数情况下，用户并不需要导入整个外部数据文件，只需通过对外部数据库执行查询，就可以将外部数据库中的某

22、一个子集载入到库执行查询，就可以将外部数据库中的某一个子集载入到ExcelExcel工作表中，工作表中，其中可以其中可以只包含用户所需要的某些字段或是某些数据只包含用户所需要的某些字段或是某些数据。 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用数据透视表和外部数据文件的使用 14.5 14.5 利用文本文件获取数据利用文本文件获取数据1.1.编辑文本导入编辑文本导入导入文本文件时，虽然不能满足用户只导入指定记录的需要，但它却导入文本文件时，虽然不能满足用户只导入指定记录的需要

23、，但它却能够向用户提供其他形式上的控制。能够向用户提供其他形式上的控制。例例14.19 14.19 导入文本文件到导入文本文件到ExcelExcel工作表中（文本文件位于本章文件夹中）工作表中（文本文件位于本章文件夹中）2.Excel2.Excel中的分列功能中的分列功能ExcelExcel的分列功能在处理大量数据时十分方便、快捷。它能将一列带有的分列功能在处理大量数据时十分方便、快捷。它能将一列带有统一分隔符号的数据，快速地按照分隔符号分割成多列来达到用户的统一分隔符号的数据，快速地按照分隔符号分割成多列来达到用户的需要，也可以一次性地将不能计算的文本型数值改变为可以参与计算需要，也可以一次性地将不能计算的文本型数值改变为可以参与计算的常规型数值。的常规型数值。例例14.20 Excel14.20 Excel分列功能分列功能 ExcelExcel高级应用高级应用 河南工程学院公共选修课河南工程学院公共选修课 张帆张帆 副教授副教授 主讲主讲第第1414章章 数据透视表和外部数据文件的使用