知识介绍——9.3.4 数据透视资料文件与下载

子任务2  知识介绍——数据透视

（一）名词解释

1．数据透视表：一种交互的、交叉制表的Excel报表，用于对多种来源（包括Excel的外部数据）的数据（如数据库记录）进行汇总和分析。

2．数据字段：源数据清单、表或数据库中的字段，其中包含在数据透视表或数据透视图报表中汇总的数据。数据字段通常包含数字型数据，如统计或销售数量。

3．数据区域：数据透视表中包含汇总数据的部分。数据区域中每个单元格的值代表源记录或行的汇总数据。

4．行字段：数据透视表中按行显示的字段。与行字段相关的项显示为行标志。

5．列字段：数据透视表中按列显示的字段。与列字段相关的项显示为列标志。）

6．页字段：在数据透视表或数据透视图报表中指定为页方向的字段。在页字段中，既可以显示所有项的汇总，也可以一次显示一个项，而筛选掉所有其他项的数据。

7．项：数据透视表和数据透视图中字段的子分类。例如，“奖学金等级”字段有“一等奖学金”、“二等奖学金”、“三等奖学金”项。）

8．计算项：数据透视表字段或数据透视图字段中的项，该项使用用户创建的公式。计算项使用数据透视表或数据透视图报表中相同字段的其他项的内容进行计算。）

9．汇总函数：是一种计算类型，用于在数据透视表或合并计算表中合并源数据，或在数据清单或数据库中插入自动分类汇总。汇总函数包括 Sum、Count 和 Average。

10．源数据：用于创建数据透视表或数据透视图的数据清单或表。源数据可以来自 Excel 数据清单或区域、外部数据库或多维数据集，或者另一张数据透视表。

11．字段按钮：用于识别数据透视表或数据透视图报表中的字段的按钮。拖动字段按钮可更改报表的布局，或者单击按钮旁的箭头可更改在报表中显示的明细数据的级别。

（二）数据透视表的应用

如果要分析相关的汇总值，尤其是在要合计较大的列表并对每个数字进行多种比较时，可以使用数据透视表。由于数据透视表是交互式的，因此，可以更改数据的视图以查看更多明细数据或计算不同的汇总额，如计数或平均值。

（三）数据是如何组织的

在数据透视表中，源数据中的每列或字段都成为汇总多行信息的数据透视表字段。数据字段（如“求和项：金额”）提供要汇总的值。图7-20报表中的单元格I3包含的“奖学金小计”值来自源数据中“奖学金等级”为“二等奖学金”且“学院名称”为“机电学院”的“金额”的每一行。

（四）如何创建数据透视表

若要创建数据透视表，请运行“数据透视表和数据透视图向导”。在向导中，从工作表列表或外部数据库选择源数据。向导会提供报表的工作表区域和可用字段的列表。将字段从列表窗口拖到分级显示区域时，Microsoft Excel 自动汇总并计算报表。

创建数据透视表后，可对其进行自定义以集中在所需信息上。自定义包括更改布局、更改格式或深化以显示更详细的数据。

1．打开要创建数据透视表的工作簿。

2．在“数据”菜单上，单击“数据透视表和数据透视图”。

3．在“数据透视表和数据透视图向导”的步骤1中，遵循指令，并单击“所需创建的报表类型”下的“数据透视表”。

按向导步骤2中的指示进行操作。

按向导步骤3中的指示进行操作，然后决定是在屏幕上还是在向导中设置报表版式。

通常，可以在屏幕上设置报表的版式（推荐使用这种方法）。只有从大型的外部数据源缓慢地检索信息，或需要设置一次一页地检索数据时，才使用向导设置报表版式。如果不能确定，请尝试在屏幕上设置报表版式。如有必要，可以返回向导。

在屏幕上设置报表版式步骤如下：

A．从“数据透视表字段列表”窗口中，将要在行中显示数据的字段拖到标有“将行字段拖至此处”的拖放区域。

B．对于要将其数据显示在整列中的字段，请将这些字段拖到标有“将列字段拖至此处”的拖放区域。

C．对于要汇总其数据的字段，请将这些字段拖到标有“请将数据项拖至此处”的区域。

如果要添加多个数据字段，则应按所需顺序排列这些字段，方法是：用鼠标右键单击数据字段，指向快捷菜单上的“顺序”，然后使用“顺序”菜单上的命令移动该字段。

D．将要作为页字段的字段拖动到标有“请将页字段拖至此处”的区域。

E．若要重排字段，请将这些字段拖到其他区域。若要删除字段，请将其拖出数据透视表。

若要隐藏拖放区域的外边框，请单击数据透视表外的某个单元格。

说明：如果在设置报表版式时数据出现得很慢，则单击“数据透视表”工具栏上的“始终显示项目”来关闭初始数据显示；如果检索还是很慢或出现错误信息，则单击“数据”菜单上的“数据透视表和数据透视图”，在向导中设置报表布局。

1．确定报表所用的的类型（单击该报表，如果字段列表未显示，请单击“数据透视表”工具栏上的“显示字段列表”，然后查看“数据透视表字段列表”窗口）。

2．显示或隐藏项目的明细数据

A．单击该项目。

B．在“数据透视表”工具栏上，单击“显示明细数据”或“隐藏明细数据”。

C．如果有提示，请单击含有要查看的明细数据的字段。

3．显示或隐藏数据单元格的明细数据

A．双击数据区域中的某个单元格。

Microsoft Excel 会将汇总的明细数据放到新工作表的单元格中。

B．若要隐藏明细数据，请删除新工作表。

说明：若要防止对该明细数据进行访问，请单击“数据透视表”工具栏上的“数据透视表”，单击“表选项”，再清除“显示明细数据”复选框。

如果将数据直接从“Office 数据连接”文件（.doc 扩展名）返回到数据透视表，或者报表是基于 OLAP 源数据的，那么就不能在报表中使用不同的源数据。

1．单击相关联的数据透视表。

2．在“数据”菜单上，单击“数据透视表和数据透视图”。

3．在向导的步骤3中，单击“上一步”按钮。

4．如果“上一步”按钮不可用，那么报表是基于OLAP数据的，并且不能将其更改成使用不同的源数据。如果需要报表是基于另外的OLAP，请创建一个新报表。

在向导的第二步中，请注意报表是否基于 Microsoft Excel区域（该步骤具有标注了编辑框的“区域”），或者外部数据（该步骤具有“获取数据”按钮），并且执行下列操作之一：

（1）指定不同的 Excel 区域

A．如果所需的数据在不同的工作表上，请单击该工作表。

B．选择新的源数据区域。

（2）指定不同的外部数据

A．单击“获取数据”。

B．在 Microsoft Query 中，对查询进行更改。有关包含更改查询详细说明的帮助，请参阅 Microsoft Query 中的“帮助”。

在 Query 的“文件”菜单上，单击“将数据返回 Microsoft Office Excel”。

5．单击“完成”按钮。

6．将需要显示的任一新字段，从“数据透视表字段列表”窗口拖动到报表上。

注释：对数据透视图报表或与其相关的数据透视表进行更改后，可能会丢失一些图表格式设置。

1．重命名字段的操作步骤：

A．双击要重命名的字段的。

B．在“数据透视表字段”对话框的“字段名”框中键入新名称。

2．重命名项目的操作步骤：

A．单击与数据透视图报表的相关联数据透视表。

B．单击要重命名的项目。

C．键入新名称，再按 Enter。

3．在向导中设置报表布局的操作步骤：

如果已经从向导中退出，则请单击“数据”菜单上的“数据透视表和数据透视图”以返回该向导中。在向导的步骤3中，单击“布局”。

A．将所需字段从右边的字段按钮组拖动到图示的“行”和“列”区域中。

B．对于要汇总其数据的字段，请将这些字段拖动到“数据”区。

C．将要作为页字段使用的字段拖动到“页”区域中。

D．如果希望 Excel 一次检索一页数据，以便可以处理大量的源数据，请双击页字段，单击“高级”，再单击“当选择页字段项时，检索外部数据源”选项，再单击“确定”按钮两次（该选项不可用于某些源数据，包括 OLAP 数据库和“Office 数据连接”）。

E．若要重排字段，请将它们拖到其他区域。某些字段只能用于某些区域；如果将一个字段拖动到其不能使用的区域，该字段将不会显示。

F．若要删除字段，请将其拖到图形区之外。

G．如果对版式满意，可单击“确定”，然后单击“完成”。

如果隐藏行字段或列字段中的项，那么这些项将从报表中消失，但仍然显示在字段下拉列表中。如果隐藏页字段中的项，那么这些项不仅从报表中消失，而且还会在字段下拉列表中消失。

1．显示字段的前几项或后几项的操作步骤：

A．单击该字段。对于数据透视图报表，请单击相关联的数据透视表中的字段。

B．在“数据透视表”工具栏上，单击“数据透视表”，再单击“排序并列出前10个”。

C．在“自动显示前10项”之下，单击“打开”。

D．在“显示”框中，单击“最大”或“最小”，并在右侧的框中，输入要显示项的个数。

E．在“使用字段”框中，单击数据字段来计算最大或最小项。

2．显示或隐藏行（分类）或列（系列）字段中的项的操作步骤：

A．单击字段中的下拉箭头。

B．选择每个要显示的项的复选框，并清除要隐藏的项的复选框。

3．显示或隐藏页字段中的项的操作步骤：

A．双击页字段。

B．如果出现了“隐藏项”框，请选择要隐藏的各项，并清除要显示的各项。

C．如果“隐藏项”框消失，则源数据将在页字段下拉列表中显示所有可用项。

如果要显示或隐藏没有数据的项，请选中或清除“显示空数据项”复选框（某些类型的源数据不支持该选项）。

例如，如果机电学院没有三等奖学金，则只有在选择了“显示空数据项”时，才能显示机电学院三等奖学金的汇总值及个数值0。

4．重新显示字段中的隐藏项的操作步骤：

A．双击字段。

B．在“数据透视表字段”对话框中，单击“高级”。

C．在“自动显示前 10 项”之下，单击“关闭”。

D．单击“确定”，如果出现了“隐藏项”框，并有已选中的项，请取消对这些项的选择。

E．如果该字段是行（分类）或列（系列）字段，请单击下拉箭头，再单击“全部显示”。

说明：在显示或隐藏透视图表报表或与其相关联的透视表报表中的项时，将丢失某些图表格式。

当计算数据透视表中的分类汇总和总计时，可包括或排除隐藏项。在“数据透视表”工具栏上，单击“在汇总中包含隐藏项”。如果此按钮无效，则利用源数据可在页字段中包括或排除隐藏项，其方法是：单击“数据透视表”，单击“表选项”，再选中或清除“隐藏的子汇总项”复选框。

1．显示单个字段的分类汇总的操作步骤

（1）双击该字段。

（2）请执行下列操作之一：

A．对外部行或列字段进行分类汇总：在“分类汇总”之下，单击“自动”。若要使用不同的汇总函数）或显示多种类型的分类汇总，请在“自定义”（此选项对某些类型的源数据无效）右边的框中单击所需的汇总函数。

B．对内部行或列字段分类汇总：如果“分类汇总”下的“自定义”选项有效，请单击该选项，然后在右边的框中单击汇总函数。

C．取消分类汇总：在“分类汇总”之下，单击“无”。

说明：如果字段包含计算项，则无法更改分类汇总函数。

对于外部行字段，可在其项的上方或下方显示分类汇总。双击字段，单击“布局”，单击“以大纲形式显示项目”，再选中或清除“在组的顶部显示分类汇总”复选框。

2．显示整个报表的总计的操作步骤

单击报表。在“数据透视表”工具栏上，单击“数据透视表”/“表选项”。

（1）显示总计：请选中“列总计”复选框或“行总计”复选框，或选中两个复选框。

（2）隐藏总计：请清除“行总计”复选框或“列总计”复选框，或两者都清除。

（3）说明：字段总计所用的汇总函数与字段分类汇总所用的函数相同。

3．计算有隐藏项或没有隐藏项的汇总

（1）单击报表。在“数据透视表”工具栏上，单击“在汇总中包含隐藏项”按钮。

（2）如果此按钮不可用，则利用源数据可在页字段中包含或排除隐藏项，方法是：单击“数据透视表”，单击“表选项”，再选中或清除“隐藏的子汇总项”复选框。

说明：显示或隐藏汇总时，在基于数据透视表的数据透视图报表中可能会丢失某些格式。

1．如果工作表中有多份数据透视表，则请设置一个只包含要打印的报表的打印区域（单击报表；在“数据透视表”工具栏上，单击“数据透视表”/“选定”/“整张表格”；在“文件”/“打印区域”/“设置打印区域”）。

2．在“文件”菜单上，单击“页面设置”，然后调整页面设置、工作表设置、页边距、页眉和页脚。

3．如果要使报表的行列标志作为每页的打印标题，请清除“顶端标题行”和“左端标题列”框，然后设置数据透视表打印标题（在“数据透视表”工具栏上，单击“数据透视表”/“表选项”。在“格式选项”之下，选中“设置打印标题”复选框。如果报表有多个行字段，并要在每页上都重复使用外部行字段项，则请选中“每一打印页上重复项目”复选框。）

4．如果报表有多个行字段，并要在一个或多个外部字段中的每项后自动分页，则请设置自动分页符（双击外部行字段，这些字段必须包含要打印到其他页上的项。单击“布局”按钮。选中“每项后面插入分页符”复选框）。

5．在“视图”菜单上，单击“分页预览”，然后可根据需要调整分页符（可插入新的手动分页符，移动或删除自动分页符）。

6．在“文件”菜单上，单击“打印预览”并检查打印页面的版式。若要进行调整，可重复前面的任何步骤。

7．如果对打印预览感到满意，请单击“打印”。

1．单击该报表。

2．单击“数据透视表”工具栏上的“刷新数据”。

如果希望打开工作簿时 Microsoft Excel 自动刷新报表，请设置此选项（在“数据透视表”工具栏上，单击“数据透视表”或“数据透视图”，再单击“表选项”或“选项”。选中“打开时刷新”复选框）。

设置此选项可能会导致在报表自动刷新时执行任意的SQL脚本，从而引起安全性风险。

如果报表基于外部数据，而且您希望 Excel 按固定的时间间隔刷新它，请设置此选项（在“数据透视表”工具栏上，单击“数据透视表”或“数据透视图”，再单击“表选项”或“选项”。选中“刷新频率”复选框，然后在“分钟”框中输入所需时间间隔。）

刷新数据透视表或其相关联的数据透视表时，可能丢失某些图表格式。

如果每次刷新时数据库提示输入密码，可让 Excel 保存密码，以便不必重复输入密码。方法是：在“数据透视表”工具栏上，单击“数据透视表”或“数据透视图”，单击“表选项”或“选项”，然后选中“保存密码”复选框。此选项以可读文本形式保存密码，所以如果安全性比方便更重要的话，请不要使用此选项。

1．单击数据透视表。

2．在“数据透视表”工具栏上，单击“数据透视表”/“选定”/“整张表格”。

3．单击“编辑”/“清除”/“全部”。

说明：对于数据透视图报表，删除与其相关的数据透视表，将会冻结图表，使得不可再对其进行更改。