第6章数据分析与处理

1、ExcelExcel数据处理与分析数据处理与分析ExcelExcel数据处理与分析数据处理与分析内容提要内容提要6.2 创建数据透视表创建数据透视表6.1 概述概述6.6 多重合并计算数据区域创建数据透视表多重合并计算数据区域创建数据透视表6.7 创建数据透视图创建数据透视图6.5 数据透视表的计算数据透视表的计算6.4 数据透视表的排序和筛选数据透视表的排序和筛选6.3 数据透视表的基本操作数据透视表的基本操作6.8 数据透视图的基本操作数据透视图的基本操作ExcelExcel数据处理与分析数据处理与分析6 6.1.1 概述概述 数据透视表是数据透视表是Excel2007Excel2007提

2、供的一种交提供的一种交互式报表，可以根据不同的分析目的进行汇互式报表，可以根据不同的分析目的进行汇总、分析、浏览数据，得到想要的分析结果总、分析、浏览数据，得到想要的分析结果，它是一种动态数据分析工具。在数据透视，它是一种动态数据分析工具。在数据透视表中可以交换行和列来查看原数据的不同汇表中可以交换行和列来查看原数据的不同汇总结果。总结果。 而数据透视图则是将数据透视表中的数而数据透视图则是将数据透视表中的数据图形化，能方便地查看比较、分析数据的据图形化，能方便地查看比较、分析数据的模式和趋势。模式和趋势。ExcelExcel数据处理与分析数据处理与分析6 6.1.1.1 1 数据透视表数据透

3、视表 数据透视表是一种交互，交叉制作的数据透视表是一种交互，交叉制作的ExcelExcel报表，一组具有多个字段的数据进行报表，一组具有多个字段的数据进行多立体的分析汇总，用于对多种来源的数多立体的分析汇总，用于对多种来源的数据进行汇总和分析，从而可以快速合并和据进行汇总和分析，从而可以快速合并和比较大量数据。当数据规模比较大时，这比较大量数据。当数据规模比较大时，这种分析的意义就显得尤为突出。通过运用种分析的意义就显得尤为突出。通过运用数据透视表，用户可以通过旋转其行和列数据透视表，用户可以通过旋转其行和列查看源数据的不同汇总，进而调出需要区查看源数据的不同汇总，进而调出需要区域的明细数据。

4、域的明细数据。ExcelExcel数据处理与分析数据处理与分析6 6.1.1.2 2 数据透视图数据透视图 数据透视图以图的形式表示数据透视表中数据透视图以图的形式表示数据透视表中的数据。数据透视图通常有一个使用相应布局的数据。数据透视图通常有一个使用相应布局并与之相关联的数据透视表。和图表与数据区并与之相关联的数据透视表。和图表与数据区域之间的关系相同，各数据透视表之间的字段域之间的关系相同，各数据透视表之间的字段相互对应。如果更改了某一报表的某个字段位相互对应。如果更改了某一报表的某个字段位置，则另一报表中的相应字段的位置也会改变。置，则另一报表中的相应字段的位置也会改变。 在数据透视图中

5、，除具有标准图表的系列、在数据透视图中，除具有标准图表的系列、分类、数据标记和坐标轴以外，数据透视图还分类、数据标记和坐标轴以外，数据透视图还有一些特殊的元素，如报表筛选字段、值字段、有一些特殊的元素，如报表筛选字段、值字段、系列字段、项、分类字段等。系列字段、项、分类字段等。ExcelExcel数据处理与分析数据处理与分析6 6. .2 2 创建数据透视表创建数据透视表 若要创建数据透视表，您必须连接到一个数若要创建数据透视表，您必须连接到一个数据源，并输入报表的位置。而在创建数据透视表据源，并输入报表的位置。而在创建数据透视表之前，要确保数据源中的第一行包含列标签。根之前，要确保数据源中的

6、第一行包含列标签。根据数据源的不同，有以下据数据源的不同，有以下4 4种创建数据透视表的种创建数据透视表的方法。方法。 从从ExcelExcel工作表中创建数据透视表。工作表中创建数据透视表。 从外部数据源创建数据透视表。从外部数据源创建数据透视表。 从合并计算多个数据区域创建数据透视表。从合并计算多个数据区域创建数据透视表。 从一个数据透视表中创建另一个数据透视表。从一个数据透视表中创建另一个数据透视表。ExcelExcel数据处理与分析数据处理与分析6.2.1 6.2.1 创建数据透视表创建数据透视表 （1 1）选择数据区域中的一个单元格。）选择数据区域中的一个单元格。ExcelExcel

7、数据处理与分析数据处理与分析6.2.1 6.2.1 创建数据透视表创建数据透视表 （2 2）若要创建一个数据透视表，请在）若要创建一个数据透视表，请在“插入插入”选选项卡的项卡的“表表”组中，单击组中，单击“数据透视表数据透视表”，然后单击，然后单击“数据透视表数据透视表”命令。命令。 （3 3）在对话框中选择要分析的数据区域，再选择）在对话框中选择要分析的数据区域，再选择放置数据透视表的位置。放置数据透视表的位置。ExcelExcel数据处理与分析数据处理与分析6.2.1 6.2.1 创建数据透视表创建数据透视表 （4 4）设置好参数后，单击）设置好参数后，单击“确定确定”按钮，按钮，Exc

8、elExcel会将空会将空的数据透视表添加至指定位置并显示数据透视表字段列表，的数据透视表添加至指定位置并显示数据透视表字段列表，以便添加字段、创建布局以及自定义数据透视表以便添加字段、创建布局以及自定义数据透视表。ExcelExcel数据处理与分析数据处理与分析6.2.1 6.2.1 创建数据透视表创建数据透视表 （5 5）在右侧）在右侧“选择要添加到报表的字段选择要添加到报表的字段”中，单击选中中，单击选中“客户客户”复选框，复选框，ExcelExcel自动将其添加到下方的自动将其添加到下方的“行标签行标签”列列表框中。若要将其放到表框中。若要将其放到“列标签列标签”列表框中，可用鼠标将其

9、列表框中，可用鼠标将其拖动到列标签中。选择拖动到列标签中。选择“订单数量订单数量”复选框，复选框，ExcelExcel自动将其自动将其添加到下方的添加到下方的“数值数值”列表框中，得到如下图所示的透视表。列表框中，得到如下图所示的透视表。ExcelExcel数据处理与分析数据处理与分析6.2.1 6.2.1 创建数据透视表创建数据透视表 （6 6）将）将“地区地区”拖到拖到“报表筛选报表筛选”列表框中，得到如下列表框中，得到如下图所示的数据透视表效果。图所示的数据透视表效果。ExcelExcel数据处理与分析数据处理与分析6.2.2 6.2.2 更改数据来源更改数据来源 创建完数据透视表后，还

10、可改变其数据来源，具体操作创建完数据透视表后，还可改变其数据来源，具体操作步骤如下。步骤如下。 （1 1）单击数据透视表。）单击数据透视表。 （2 2）在）在“数据透视表工具数据透视表工具”“选项选项”“数据数据”组中，组中，单击单击“更改数据源更改数据源”按钮打开命令列表，选择按钮打开命令列表，选择“更改数据源更改数据源”命令，将打开命令，将打开“更改数据透视表数据源更改数据透视表数据源”对话框，如图所示。对话框，如图所示。 （3 3）要使用其他的）要使用其他的ExcelExcel表或单元格区域，单击表或单元格区域，单击“折叠折叠”按钮，选取表或区域。按钮，选取表或区域。ExcelExcel

11、数据处理与分析数据处理与分析6.2.3 6.2.3 刷新数据刷新数据 更改源数据将导致用于分析的数据发生变化。用户可以更改源数据将导致用于分析的数据发生变化。用户可以通过刷新报表，使用与原始数据连接信息类似的新数据来更通过刷新报表，使用与原始数据连接信息类似的新数据来更新数据透视表或数据透视图。新数据透视表或数据透视图。 操作步骤如下：在操作步骤如下：在“数据透视表工具数据透视表工具”“选选项项”“数据数据”组中单击组中单击“刷新刷新”按钮，弹出下拉命令列表，按钮，弹出下拉命令列表，选择选择“刷新刷新”或或“全部刷新全部刷新”命令来刷新数据，如图所示。命令来刷新数据，如图所示。ExcelExc

12、el数据处理与分析数据处理与分析6.2.4 6.2.4 清除数据透视表清除数据透视表 要从数据透视表中删除所有报表筛选、标要从数据透视表中删除所有报表筛选、标签、值和格式，然后重新开始设计布局，请签、值和格式，然后重新开始设计布局，请使用使用“全部清除全部清除”命令。该命令可有效地重命令。该命令可有效地重新设置数据透视表，但不会将其删除。数据新设置数据透视表，但不会将其删除。数据透视表的数据连接、位置和缓存仍保持不变。透视表的数据连接、位置和缓存仍保持不变。具体操作步骤如下。具体操作步骤如下。 （1 1）单击数据透视表。）单击数据透视表。 （2 2）在）在“数据透视表工具数据透视表工具”“选选

13、项项”“操作操作”组中，单击组中，单击“清除清除”，然后，然后单击单击“全部清除全部清除”。 ExcelExcel数据处理与分析数据处理与分析6.2.5 6.2.5 删除数据透视表删除数据透视表 数据透视表作为一个整体，允许使用快捷数据透视表作为一个整体，允许使用快捷菜单删除其中的部分数据。但要删除整个数据菜单删除其中的部分数据。但要删除整个数据透视表，具体操作如下。透视表，具体操作如下。 （1 1）首先要单击数据透视表。）首先要单击数据透视表。 （2 2）在）在“数据透视表工具数据透视表工具”“选选项项”“操作操作”组中，单击组中，单击“选择选择”按钮打开按钮打开命令列表，选择命令列表，选择

14、“整个数据透视表整个数据透视表”命令，即命令，即选择了整个数据透视表。选择了整个数据透视表。 （3 3）按）按键删除即可。键删除即可。 ExcelExcel数据处理与分析数据处理与分析6.3 6.3 数据透视表的基本操作数据透视表的基本操作6.3.1 数据透视表字段的操作数据透视表字段的操作 创建数据透视表之后，可以使用数据透视创建数据透视表之后，可以使用数据透视表字段列表来添加字段。如果要更改数据透视表字段列表来添加字段。如果要更改数据透视表，可以使用该字段列表来重新排列和删除字表，可以使用该字段列表来重新排列和删除字段。默认情况下，数据透视表字段列表显示分段。默认情况下，数据透视表字段列表

15、显示分为两部分：上方的字段部分用于添加和删除报为两部分：上方的字段部分用于添加和删除报表字段，下方的布局部分用于重新排列和重新表字段，下方的布局部分用于重新排列和重新定位字段。定位字段。ExcelExcel数据处理与分析数据处理与分析6.3.1 6.3.1 数据透视表字段的操作数据透视表字段的操作 1 1字段设置字段设置 使用使用“字段设置字段设置”对话框可以控制数据透视对话框可以控制数据透视表中字段的各种格式、分类汇总和筛选设置。字表中字段的各种格式、分类汇总和筛选设置。字段设置在段设置在“数据透视表工具数据透视表工具”“选项选项”“活活动字段动字段”组中，如图所示。组中，如图所示。Exce

16、lExcel数据处理与分析数据处理与分析6.3.1 6.3.1 数据透视表字段的操作数据透视表字段的操作 ExcelExcel数据处理与分析数据处理与分析6.3.1 6.3.1 数据透视表字段的操作数据透视表字段的操作 2 2添加字段添加字段 要将字段添加到报表，主要有以下几种方法。要将字段添加到报表，主要有以下几种方法。 （1 1）在）在“数据透视表字段列表数据透视表字段列表”中的字段部分中的字段部分选中各字段名称旁边的复选框。字段放置在布局部分选中各字段名称旁边的复选框。字段放置在布局部分的默认区域。的默认区域。 （2 2）用鼠标右键单击字段名称，在）用鼠标右键单击字段名称，在“添加到报添

17、加到报表筛选表筛选”、“添加到列标签添加到列标签”、“添加到行标签添加到行标签”和和“添加到值添加到值”中进行选择，将该字段放置在布局部分中进行选择，将该字段放置在布局部分的特定区域中。的特定区域中。 （3 3）用户还可以去击并按住某个字段名，在字）用户还可以去击并按住某个字段名，在字段部分与布局部分中的某个区域之间拖动该字段。段部分与布局部分中的某个区域之间拖动该字段。ExcelExcel数据处理与分析数据处理与分析6.3.1 6.3.1 数据透视表字段的操作数据透视表字段的操作 3 3重新排列字段重新排列字段 用户可以通过使用用户可以通过使用“数据数据透视表字段列表透视表字段列表”或利用或

18、利用“报报表筛选表筛选”、“列标签列标签”、“行行标签标签”、“数值数值”4 4个区域之个区域之一来重新排列现有字段或重新一来重新排列现有字段或重新放置这些字段，如图所示。放置这些字段，如图所示。 用户还可以单击并按住字用户还可以单击并按住字段名，在字段与布局部分之间段名，在字段与布局部分之间以及不同的区域之间拖动该字以及不同的区域之间拖动该字段。段。ExcelExcel数据处理与分析数据处理与分析6.3.1 6.3.1 数据透视表字段的操作数据透视表字段的操作 4 4删除字段删除字段 要删除字段，可以在数据透视表中要删除字段，可以在数据透视表中右键单击，使用快捷菜单中的删除命令右键单击，使用

19、快捷菜单中的删除命令删除字段。也可以在布局区域之一中单删除字段。也可以在布局区域之一中单击字段名称，从打开的菜单中选择击字段名称，从打开的菜单中选择“删删除字段除字段”命令即可；或者清除字段部分命令即可；或者清除字段部分中各个字段名称旁边的复选框。中各个字段名称旁边的复选框。ExcelExcel数据处理与分析数据处理与分析6.3.2 6.3.2 移动数据透视表数据及显示明细数据移动数据透视表数据及显示明细数据1.1.移动数据透视表移动数据透视表 用户如果要移动数据透视表，其操作步骤如下用户如果要移动数据透视表，其操作步骤如下: : （1 1）单击数据透视表。）单击数据透视表。 （2 2）在）在

20、“数据透视表工具数据透视表工具”“选项选项”“操作操作”组中，单击组中，单击“移动数据透视表移动数据透视表”按钮按钮, , 将显示将显示“移动数移动数据透视表据透视表”对话框。对话框。 （3 3）在）在“选择放置数据透视表的位置选择放置数据透视表的位置”对话框中，对话框中，如果选择如果选择“新工作表新工作表”，则将数据透视表放入一个新的，则将数据透视表放入一个新的工作表。如果选择工作表。如果选择“现有工作表现有工作表”，则将数据透视表放，则将数据透视表放入现有工作表。入现有工作表。ExcelExcel数据处理与分析数据处理与分析6.3.2 6.3.2 移动数据透视表数据及显示明细数据移动数据透

21、视表数据及显示明细数据2 2显示或隐藏明细数据显示或隐藏明细数据 为了便于查看，可以在一个操作中显示为了便于查看，可以在一个操作中显示或隐藏到数据明细的任意级别，甚至数据明或隐藏到数据明细的任意级别，甚至数据明细的所有级别。具体操作方法如下。细的所有级别。具体操作方法如下。 (1) (1) 在数据透视表中单击。在数据透视表中单击。 (2) (2) 在在“数据透视表工具数据透视表工具”“选选项项”“显示显示/ /隐藏隐藏”组中，单击相关按钮即组中，单击相关按钮即可显示或隐藏相关项目。可显示或隐藏相关项目。ExcelExcel数据处理与分析数据处理与分析6.4 6.4 数据透视表的排序和筛选数据透

22、视表的排序和筛选 6.4.1 6.4.1 筛选数据筛选数据 通过筛选数据可以筛选出符合用户指定条件的数通过筛选数据可以筛选出符合用户指定条件的数据，并隐藏不想显示的数据。据，并隐藏不想显示的数据。 在数据透视表或数据透视图中，筛选是累加式的，在数据透视表或数据透视图中，筛选是累加式的，也就是说，每次增加筛选都是基于当前筛选并进一步也就是说，每次增加筛选都是基于当前筛选并进一步减小显示的数据。减小显示的数据。 1 1、筛选标签或标签内的数值、筛选标签或标签内的数值 2 2、前几项或后几项的筛选、前几项或后几项的筛选 3 3、按选定内容筛选、按选定内容筛选 4 4、删除筛选、删除筛选ExcelEx

23、cel数据处理与分析数据处理与分析 6.4.2 6.4.2 排序数据排序数据 对数据进行排序是数据分析中不可或缺的组成对数据进行排序是数据分析中不可或缺的组成部分。部分。 1 1、对列或行标签中的数据进行排序、对列或行标签中的数据进行排序 2 2、对值区域中的数据进行排序、对值区域中的数据进行排序ExcelExcel数据处理与分析数据处理与分析6.4.3 6.4.3 数据透视表中的组合项数据透视表中的组合项 在数据透视表中在数据透视表中, ,可以可以组合数字项、组合日期或组合数字项、组合日期或时间和组合选定项。时间和组合选定项。ExcelExcel数据处理与分析数据处理与分析6.5 6.5 数

24、据透视表的计算数据透视表的计算 数据透视表和数据透视图提供了多种计算数据透视表和数据透视图提供了多种计算方式。如果汇总函数和自定义计算不能满足自方式。如果汇总函数和自定义计算不能满足自己的需要，则可在计算字段和计算项中创建自己的需要，则可在计算字段和计算项中创建自己的公式。己的公式。 1 1、添加计算字段、添加计算字段 2 2、添加计算项、添加计算项 3 3、显示公式列表、显示公式列表 4 4、编辑公式、编辑公式 5 5、删除公式、删除公式ExcelExcel数据处理与分析数据处理与分析6.6 6.6 多重合并计算数据区域创建数据透视表多重合并计算数据区域创建数据透视表 要汇总各个单独工作表区

25、域中的结果，可将要汇总各个单独工作表区域中的结果，可将单独工作表区域中的数据合并到主工作表的数据单独工作表区域中的数据合并到主工作表的数据透视表中。单独工作表区域可与主工作表位于相透视表中。单独工作表区域可与主工作表位于相同工作簿中，也可位于不同工作簿中。合并数据同工作簿中，也可位于不同工作簿中。合并数据时，将对其进行组合，以便更加轻松地聚合数据。时，将对其进行组合，以便更加轻松地聚合数据。ExcelExcel数据处理与分析数据处理与分析6.6 6.6 多重合并计算数据区域创建数据透视表多重合并计算数据区域创建数据透视表 1不使用任何页字段合并数据不使用任何页字段合并数据 （1 1）单击任意一

26、个单元格。）单击任意一个单元格。 （2 2）启动）启动“数据透视表和数据透视图向导数据透视表和数据透视图向导”，按按“Alt+D+PAlt+D+P”组合键。组合键。 （3 3）在向导的）在向导的“步骤步骤1 1”页面上单击页面上单击“多重多重合并计算数据区域合并计算数据区域”，然后单击，然后单击“下一步下一步”。ExcelExcel数据处理与分析数据处理与分析6.6 6.6 多重合并计算数据区域创建数据透视表多重合并计算数据区域创建数据透视表 （4 4）在向导的）在向导的“步骤步骤2a2a”页面上单击页面上单击“自定义自定义页字段页字段”，然后单击，然后单击“下一步下一步”。ExcelExce

27、l数据处理与分析数据处理与分析6.6 6.6 多重合并计算数据区域创建数据透视表多重合并计算数据区域创建数据透视表 （5 5）在向导的）在向导的“步骤步骤2b2b”页面上执行下列操作：选定页面上执行下列操作：选定包含汇总数据的工作表数据区域，然后单击包含汇总数据的工作表数据区域，然后单击“添加添加”。如。如果单元格区域在另一个工作簿中，请先打开工作簿以便选果单元格区域在另一个工作簿中，请先打开工作簿以便选择数据。所有汇总数据区域依次添加完后，在择数据。所有汇总数据区域依次添加完后，在“请先指定请先指定要建立在数据透视表中的页字段数目要建立在数据透视表中的页字段数目”下，选择下，选择“0 0”，

28、然，然后单击后单击“下一步下一步”。ExcelExcel数据处理与分析数据处理与分析6.6 6.6 多重合并计算数据区域创建数据透视表多重合并计算数据区域创建数据透视表 （6 6）在向导的）在向导的“步骤步骤3 3”页面，选择数据透视表页面，选择数据透视表的位置，然后单击的位置，然后单击“完成完成”。ExcelExcel数据处理与分析数据处理与分析6.6 6.6 多重合并计算数据区域创建数据透视表多重合并计算数据区域创建数据透视表 两个数据表合并计算后，其数据透视表如左图两个数据表合并计算后，其数据透视表如左图所示。可以通过单击鼠标右键，在快捷菜单中选择所示。可以通过单击鼠标右键，在快捷菜单中

29、选择“数据透视表选项数据透视表选项”“汇总和筛选汇总和筛选”，取消，取消“显显示行总计示行总计”，不显示行总计后的透视表如右图所示。，不显示行总计后的透视表如右图所示。ExcelExcel数据处理与分析数据处理与分析6.6 6.6 多重合并计算数据区域创建数据透视表多重合并计算数据区域创建数据透视表 2使用单页字段合并数据使用单页字段合并数据 3使用多页字段合并数据使用多页字段合并数据ExcelExcel数据处理与分析数据处理与分析 6.7 6.7 创建数据透视图创建数据透视图 数据透视图是数据透视表的图形显示效数据透视图是数据透视表的图形显示效果。创建数据透视图主要有以下两种方法，果。创建数

30、据透视图主要有以下两种方法， 1 1、直接通过数据表中的数据创建数据透、直接通过数据表中的数据创建数据透视图。视图。 2 2、通过已有数据透视表创建数据透视图。、通过已有数据透视表创建数据透视图。ExcelExcel数据处理与分析数据处理与分析6.7.1 6.7.1 通过数据区域创建数据透视图通过数据区域创建数据透视图通过数据区域创建数据透视图的具体步骤如下。通过数据区域创建数据透视图的具体步骤如下。 (1) (1) 选择数据区域中的一个单元格。选择数据区域中的一个单元格。 (2) (2) 在在“插入插入”选项卡的选项卡的“表表”组中，单击组中，单击“数据透数据透视表视表”，在命令列表中选择，

31、在命令列表中选择“数据透视图数据透视图”命令。命令。Excel Excel 会显示会显示“创建数据透视表及数据透视图创建数据透视表及数据透视图”对话框。对话框。 (3) (3) 将打开将打开“创建数据透视表及数据透视图创建数据透视表及数据透视图”对话框，对话框，首先选择要分析的数据区域，再选择放置数据透视图的位首先选择要分析的数据区域，再选择放置数据透视图的位置。置。 (4) (4) 设置好参数后，单击设置好参数后，单击“确定确定”按钮即可创建一个按钮即可创建一个空的数据透视表及数据透视图。空的数据透视表及数据透视图。 (5) (5) 采用与创建数据透视表相同的方法，在右侧的采用与创建数据透视

32、表相同的方法，在右侧的“数据透视表字段列表数据透视表字段列表”中选择相应的字段，将其添加到中选择相应的字段，将其添加到下方的各列表框中。下方的各列表框中。 ExcelExcel数据处理与分析数据处理与分析6.7.1 6.7.1 通过数据区域创建数据透视图通过数据区域创建数据透视图ExcelExcel数据处理与分析数据处理与分析6.7.1 6.7.1 通过数据区域创建数据透视图通过数据区域创建数据透视图ExcelExcel数据处理与分析数据处理与分析6.7.2 6.7.2 通过数据透视表创建数据透视图通过数据透视表创建数据透视图 根据数据透视表的现有数据来创建数据透视图，具体操根据数据透视表的现

33、有数据来创建数据透视图，具体操作步骤如下。作步骤如下。 (1) (1) 单击所需的数据透视表中的任意单元格。单击所需的数据透视表中的任意单元格。 (2) (2) 在在“插入插入”选项卡上的选项卡上的“图表图表”组中，单击图表类组中，单击图表类型（可以使用除型（可以使用除 XY XY 散点图、气泡图或股价图以外的任意图散点图、气泡图或股价图以外的任意图表类型）。表类型）。 或者单击或者单击“数据透视表工具数据透视表工具”“选项选项”“工具工具”组组中的中的“数据透视图数据透视图”按钮，在打开的按钮，在打开的“插入图表插入图表”对话框中对话框中选择适当的图表类型即可创建数据透视图。选择适当的图表类型即可创建数据透视图。ExcelEx