实验3 透视分析.docx

实验3 透视分析.docx

《实验3 透视分析.docx》由会员分享，可在线阅读，更多相关《实验3 透视分析.docx（13页珍藏版）》请在冰豆网上搜索。

实验3透视分析

透视分析

在报表处理过程中，经常需要对报表中的数据进行各种汇总计算。

例如，在销售管理方面，需要定期对销售情况进行分类汇总；有时还需要将多个报表进行合并计算；而对于更高层次的管理人员，可能需要从不同的分析角度，对同一张报表根据不同的指标进行分类汇总。

这一过程被形象地称为“透视分析”。

一、数据透视表

当需要对明细数据做全面分析时，数据透视表是最佳工具。

它有机地结合了分类汇总和合并计算的优点，可以方便地调整分类汇总的依据，灵活地以多种不同的方式来展示数据的特征。

Excel2000还新增了数据透视图报告功能，使得将数据透视表的分析结果用图表方式提交更为方便。

1.创建数据透视表

创建数据透视表的操作是比较复杂的，但是Excel2000为此提供了数据透视表和数据透视图向导。

在该向导的指导下可以轻松地创建数据透视表。

其操作步骤如下：

选定数据清单中的任意单元格，单击【插入】选项卡中数据透视表和图表报告命令。

该步骤指定数据透视表显示的位置。

这里选定新建工作表，单击完成。

这时，Excel2007自动创建一新的工作表，在该工作表中显示创建的数据透视表的雏形，同时自动显示数据透视表工具栏。

如图3－20所示。

图3－20

这时应根据分析要求，设置数据透视表的版式。

该步骤也是创建数据透视表的最关键的一步。

假设要分析各销售员不同时期的销售业绩，可以日期作为行字段，销售员作为列字段，而将金额作为数据项。

从数据透视表工具栏中，将相应的字段拖放到行字段、列字段和数据项位置。

这时创建的数据透视图如图3－21所示。

其中最右一列有每天的销售合计，最下一行有每个销售员的销售合计。

2.应用数据透视表

数据透视表的突出优点是可以利用它对数据进行透视分析，可以根据不同的分析要求，对数据透视表进行各种操作。

例如进行不同级别的概括汇总，添加或删除分析指标，显示或隐藏细节数据，改变数据透视表的版式等。

A、调整分析步长

对于日期型字段可以根据需要调整分析的步长，可以指定其按月、季度或年重新进行分组。

例如，对于图3－21所示的数据透视表，为了更清楚地比较企业的月度销售数据，指定其按月进行分类汇总。

其具体操作步骤是：

右击日期字段中的任一日期数据单元格，在弹出的快捷菜单中指向组及分级显示命令，单击其中的分组子命令。

这时将出现分组对话框，如图3－22所示。

图3－22

在依据列表框中指定月，单击确定。

按月分类汇总的数据透视表如图3－23所示。

图3－23

如果需要分析更宏观的情况，可以在分组对话框中的依据列表框中，清除月选项，选定季度选项。

如果希望同时查看月度和季度的数据，也可以同时选定月和季度选项。

图3－24是按季度分类汇总的数据透视表。

图3－24

B、添加/删除字段

当需要分析不同的指标时，不需要重新制作报表，只需在原数据透视表的基础上，根据需要简单地添加或删除字段即可。

例如对于图3－24所示的数据透视表，需要进一步分析不同产品类别的销售情况，即可以将产品类别字段添加到数据透视表的行字段或列字段中去。

将产品类别字段添加到行字段的具体操作步骤如下：

选定数据透视表中的任意单元格，这时的数据透视表工具栏出现源数据清单的各个字段按钮。

当鼠标指针指向产品类别字段按钮时，自动弹出有关的操作提示。

如图3－25所示。

图3－25

将产品类别字段按钮拖放到行字段区域，即可将该字段添加到数据透视表的行中。

添加了产品类别字段的数据透视表如图3－26所示。

图3－26

类似地，如果要将某个字段添加到列字段时，则将相应字段拖放到列字段区域。

如果要从数据透视表中删除某个字段拖放到数据透视表工具栏即可。

在用鼠标拖动某个字段时，鼠标指针会随着鼠标指针所处的位置变化，提示用户这时放开鼠标按键时的操作结果。

各种鼠标指针的形状如图3－27所示。

图3－27

C、分类显示数据

在Excel2000的数据透视表中，对所有的行字段、列字段都增加了分类选项箭头。

如果只是希望了解数据透视表中某个分类的数据，可以单击相应字段的下拉箭头，然后选择需要显示的分类数据。

例如，如果要只显示影碟机产品类别的数据，可以单击产品类别字段的下拉箭头。

如图3－28所示。

清除彩电选项，然后单击确定按钮。

图3－28

有关影碟机产品类别的销售数据，如图3－29所示。

图3－29

D、显示或隐藏汇总数据

从图3－29可以看到，这时的产品类别只有一类，所以相应的汇总行已无意义。

数据透视表中的行、列汇总数据，实际都是可选项。

用户可以根据需要决定显示还是隐藏某个字段的汇总数据。

要显示或是隐藏某个字段的汇总数据时，首先选定该字段，再单击数据透视表工具栏的数据透视表按钮，在弹出的下拉框中选字段设置命令。

或是右击相应的字段，在弹出的快捷菜单中选字段设置命令。

这时将出现相应字段的数据透视表字段对话框。

如图3－30所示。

图3－30

选定分类汇总选项中的无，单击确定。

隐藏了分类汇总数据的数据透视表如图3－31所示。

如果要显示或是隐藏总计数据，可单击数据透视表工具栏的数据透视表按钮，在弹出的下拉框中选表选项命令。

或是右击数据透视表，在弹出的快捷菜单中选表选项命令。

这时将出现数据透视表选项对话框。

选定或清除其中的总计选项即可。

图3－31

E、显示明细数据

数据透视表中的数据一般都是由多项数据汇总得来的，如果需要，可以方便地查看明细数据。

例如，从图3－31所示的数据透视表可以看到，销售员“方一心”一季度的销售金额较高，如果希望查看其明细数据，可以双击该数据。

这时，Excel将自动创建一个新的工作表，显示该数据所对应的明细数据。

图3－32是双击“方一心”一季度销售数据后Excel自动新建的明细数据工作表。

图3－32

F、调整显示方向

在图3－26所示的数据透视表中，日期和产品类别是作为行字段，其数据分别显示在不同的行；称作行方向显示；而销售员是作为列字段，其数据显示在不同的列，称作列方向显示。

此外，还也可以设置页字段，让指定的数据按页方向显示。

例如要详尽地分析每个销售员的业绩，可以设置销售员字段以页方向显示。

其操作方法是直接用鼠标将销售员字段从列字段处拖放到页字段处即可。

以销售员作为页字段的数据透视表如图3－33所示。

目前显示的是销售员“杨韬”的销售业绩。

单击销售员字段右边的下拉箭头，可以选择显示其他销售员，或是选择全部，可以显示其他销售员或是全体销售员的销售业绩。

图3－33

图3－33的数据透视表由于将销售员字段从原来的列方向改变为页方向显示，所以没有字段按列方向显示，但却有两个字段是按行方向显示的，因而可读性不佳。

为此，按照类似的方法，将日期字段改为按列方向显示。

将日期字段拖放到列字段后相应的数据透视表如图3－34所示。

G、改变计算函数

默认情况下，在数据透视表的数值型字段的计算函数是求和函数，非数值型字段的计算函数是计数函数。

实际应用中可以根据需要，选择其它函数进行多种计算。

Excel2000对数据透视表提供的计算函数有计数、平均值、最大值、最小值以及乘积等。

例如，需要统计图3－34所示数据透视表中的合同数目，而不是金额合计，可以使用计数函数。

其具体操作步骤如下：

选定某个数据字段，再单击数据透视表工具栏的数据透视表按钮，在弹出的下拉框中选字段设置命令。

或是右击某个数据字段，在弹出的快捷菜单中选字段设置命令。

这时将出现相应字段的数据透视表字段对话框。

如图3－35所示。

图3－35

单击汇总方式列表框中的计数选项。

再单击确定。

按计数函数计算的数据透视表如图3－36所示。

图3－36

H、改变数据显示方式

一般情况下，数据透视表中显示的都是实际的汇总数据。

为了更清晰地分析数据间的关系，例如比例或构成关系、差异关系等，可以指定数据透视表以特殊的显示方式来显示数据。

Excel2000提供了差异、百分比、差异百分比、按列递加、指数等不同的数据显示方式。

用户可以根据分析的要求选择最为合适的数据显示方式。

例如，要分析不同时期的销售增长情况，可以选择按差异来显示汇总数据。

其具体操作如下：

调出图3-35所示的数据透视表字段对话框。

单击其中的选项按钮。

这时数据透视表字段对话框向下展开，如图3-37所示。

在数据显示方式下拉框中，选择差异显示方式。

在基本字段列表框中选定日期，在基本项列表框中选定上一个。

单击确定。

按差异显示的数据透视表如图3－38所示。

图3－37

图3－38

从该数据透视表中清晰地反映出二季度销售数据较季度有较大幅度的下降，而三季度下降幅度减小，四季度则有了较大幅度的回升。

I、设置报告格式

Excel2000为数据透视表新增了报表1～报表10和表1～表10共20种自动套用格式，利用它们可以快捷方便地修饰数据透视表，使其更具可读性。

其具体操作是：

选定数据透视表中任意单元格，再单击数据透视表工具栏的设置报告格式按钮。

或是右击数据透视表的任意单元格，在弹出的快捷菜单中选设置报告格式命令。

将弹出有关数据透视表的自动套用格式对话框。

从中选择需要的报表格式后，单击确定。

图3－39为对图3－26所示的数据透视表使用表7格式套用的结果。

图3－39

3.应用数据透视图

使用数据透视表可以准确计算和分析数据，但有时候很难从字面上把握数据的全部含义。

Excel2000新增了数据透视图功能，可以方便地将数据透视表的分析结果以更直观的图表方式提交。

与数据透视表相比，数据透视图可以一种更加可视化和易于理解的方式展示数据和数据之间的关系。

1.创建数据透视图

在现有数据透视表的基础上创建数据透视图十分方便。

在Excel2000的数据透视表工具栏上新增了图表向导按钮。

只要将指定数据透视表中的任意单元格为当前单元格，然后单击该按钮，即可自动建立数据透视图。

图3－40即是根据3－34中的数据透视表创建的数据透视图。

图3－40

2.调整数据透视图

数据透视图创建以后，可以像数据透视表一样方便地进行调整。

从图3－40可以看到，行字段、列字段和页字段都有相应的下拉箭头。

如果要分类显示某种产品类别的数据，某个季度的数据，或是某个销售员的数据，都可以单击相应字段的下拉箭头，然后去除不需显示的选项即可。

图上数据字段还有函数按钮，如果要改变计算函数，双击该按钮，然后再选择计算函数即可。

这时的数据透视图和数据透视表都将自动变换。

因为数据透视图与包含其源数据的数据透视表是相链接的，当数据透视表中的数据改变后，数据透视图也会自动随之改变。

也就是说数据透视图具有自动更新功能。

例如，当将数据透视表中的日期字段分组有季度改为月时，相应的数据透视图也会自动更新。

图3－41即变更数据透视表的日期字段分组后，数据透视图的更新结果。

3.趋势线分析

Excel2000为图表提供了添加趋势线功能。

在数据透视图中添加趋势线可以使得图形化的数据更为有用。

例如，可以用现有12个月的影碟机销售数据，来预测未来1个月影碟机的销售额。

其具体操作步骤如下：

图3－41

用鼠标右击影碟机数据系列中的任意柱形标志，在弹出的快捷菜单中单击添加趋势线命令。

将弹出趋势线格式对话框，如图3－42所示。

添加了外推周期为1的趋势线的数据透视图如图3－43所示。

图3－43

通过本章内容的学习，应掌握分类汇总、合并计算和数据透视表的操作方法，特别是数据透视表功能强大，操作方便，是完成日常统计、汇总数据的最佳工具。

应能熟练地应用数据透视表和数据透视图对数据进行多方面分析。