数据透视分析.doc

目录
第一章  数据透视分析 2
1.1  分类汇总 2
1.1.1  分类依据 2
1.1.2  分类汇总操作 3
1.2 合并计算 5
1.2.1  按位置合并 5
1.2.2  按分类合并 9
1.3  数据透视表 10
1.3.1  数据准备 10
1.3.2  创建数据透视表 11
1.3.3  应用数据透视表 13
1.3.4  应用数据透视图 20

第一章  数据透视分析

  在报表处理过程中，经常需要对报表中的数据进行各种汇总计算。例如，在销售管理方面，需要定期对销售情况进行分类汇总；有时还需要将多个报表进行合并计算；而对于更高层次的管理人员，可能需要从不同的分析角度，对同一张报表根据不同的指标进行分类汇总。这一过程被形象地称为“透视分析”。而这些操作在Excel 2000中都可以方便、快捷地实现。以下通过某个生产视频设备的企业的销售管理实例，说明上述工具的应用。

1.1  分类汇总
    各种报表处理中最常用的就是分类汇总。例如会计核算，需按照科目将明细账分类汇总；仓库管理，需按照库存产品类别将库存产品分类汇总；医院管理，需按照疾病进行病源病谱的分类汇总等等。例如，某企业记录1999年1月份销售明细账的工作表如图1－1所示。现需要汇总各种商品的销售额。虽然可以利用Excel 2000的公式和函数完成有关的计算，但是使用分类汇总工具更为有效。

图1－1

1.1.1  分类依据
    在进行分类汇总操作之前，首先要确定分类的依据。例如图1－1的数据，如果要考查不同销售人员的销售业绩，可以按销售员分类汇总；如果要分析不同产品的销售情况，则可以按产品编号分类汇总；还可以按销售日期、产品类别等其它指标分类汇总。这里假设需要按照产品编号分类汇总，即要统计不同产品的销售情况。
    在确定了分类依据以后，还不能直接进行分类汇总，还必须按照选定的分类依据将数据清单排序。否则可能会造成分类汇总的错误。排序操作的基本步骤如下。
    首先选定分类依据所在列的标志单元格，即C1单元格。注意，一定不要选定整个C列。选定才C1和选定C列后，执行排序操作的结果有何不同
  单击常用工具栏中的排序按钮。或是单击数据菜单中的排序命令，然后在出现的排序对话框中指定主要关键字为产品编号。
    这时整个数据清单都按照产品类别排好顺序，可以进行分类汇总操作了。

1.1.2  分类汇总操作
    分类汇总操作的基本步骤如下：
    如果当前单元格不在数据清单中，选定数据清单中的任一单元格。
    单击数据菜单中的分类汇总命令。
    这时将弹出分类汇总对话框，如图1－2所示。   

图1－2
    在分类字段下拉框中选定产品编号；在汇总方式中选定求和；在选定汇总项中选定数量和金额两项；根据需要决定是否选定汇总结果显示在数据下方选项，如果不选定该项，则汇总结果显示在数据上方。最后单击确定。
    分类汇总的结果如图1－3所示。

图1－3
    从图1－3可以看到，Excel 2000除了在每一类明细数据的下面添加了汇总数据以外，还自动在数据清单的左侧建立了分级显示符号。该区域的顶部为横向排列的级别按钮，其数目的多少取决于分类汇总时的汇总个数。区域的下面是对应不同级别数据的显示明细数据按钮 和隐藏明细数据按钮 。利用分级显示符号，可以根据需要灵活地观察所选级别的数据，也可以方便地创建显示汇总数据、隐藏细节数据的汇总报告：
    要显示指定级别的汇总数据，可单击分级显示符号上部相应的级别按钮。图1－4为单击级别2按钮 ，显示2级汇总数据的结果。

图1－4
    要显示某一个分组数据的明细数据，可单击相应汇总数据所对应的显示明细数据按钮 。这时相应的按钮变为隐藏明细数据按钮 。图1－5为显示产品编号为“C2919PK”的明细数据的结果。

图1－5
要隐藏某一个分组数据的明细数据，可单击相应汇总数据所对应的隐藏明细数据按钮 ，或是相应明细数据所对应的概要线。这时相应的按钮再次变为显示明细数据按钮 。如果要隐藏某一级别的汇总或明细数据，可单击其上一级级别按钮。
  要将分类汇总的结果单独存放在一张工作表中，可将汇总的结果复制后，利用编辑菜单中的选择性粘贴命令粘贴到新工作表中。在选择性粘贴对话框的粘贴选项中，应选择数值选项。然后利用筛选操作或是直接删除明细数据即可。
    如果需要，在分类汇总的基础上还可以再创建二级乃至多级分类汇总。如果要删除分类汇总，可单击数据菜单中的分类汇总命令，然后在分类汇总对话框中单击全部删除按钮。

1.2 合并计算

    通过以上操作，已经得到了1月的销售汇总数据。按照类似的操作方法，可以完成2月、3月……12月等各月的销售数据的汇总。显然最后还需要将全年的销售数据汇总。要将多个工作表中的数据合并汇总，需要使用Excel 2000的合并计算命令。

1.2.1  按位置合并
    假设已有1月～12月的销售汇总数据，分别存放在工作表“1月”、“2月”、……“12月”中，其中图1－6即为“1月”工作表。需要存放合并数据的工作表为“全年汇总”。假设这13个工作表的格式相同，这时可通过按位置合并的操作，将上述12个工作表的数据合并到“全年汇总”工作表中。

图1－6

    按位置合并操作的基本步骤如下：
    首先选定要存放合并数据的“全年汇总”工作表为当前工作表，并选定存放合并数据的单元格区域。如图1－7所示。

图1－7

    单击数据菜单中的合并计算命令。
    这时将弹出合并计算对话框，如图1－8所示。

图1－8

    在函数下拉框中选定求和。
    单击引用位置框中的折叠按钮 ，然后选定“1月”工作表中的B2:B16单元格区域，再单击还原按钮 。
    单击添加命令，选定的第一个要合并的单元格区域添加到所有引用位置框中。
    这时的合并计算对话框如图1－9所示。

图1－9

    逐个单击“2月”、“3月”、……“12月”工作表，并单击添加按钮，将12个工作表相同单元格区域添加到所有引用位置框中。如图1－10所示。

图1－10

    最后单击确定按钮，完成合并计算。合并计算的结果如图1－11所示。

图1－11

    如果在合并计算对话框中选定了创建连至数据源的链接选项，则存放合并数据的工作表中存放的不是单纯的合并数据，而是计算合并数据的公式。这时在工作表的左侧将出现分级显示符号。可以根据需要显示或是隐藏源数据。如图1－12所示。

图1－12

    这时的合并数据与源数据建立了链接关系，也就是说，当源数据变动时，合并数据会自动更新，保持一致。

1.2.2  按分类合并
    如果待合并的工作表格式不完全相同，例如有可能各月份销售的产品不完全相同，这时不能简单地采用上述按位置合并的方法。而需要按分类进行合并。按分类合并的操作与按位置合并大致相同，其基本步骤如下：
    首先选定要存放合并数据的“全年汇总”工作表为当前工作表，并选定存放合并数据的单元格区域。与按位置合并不同的是，这时应同时选定分类依据所在的单元格区域。如果不能确切知道有多少类，可以只选定单元格区域的第一行。如图1－13所示。

图1－13

    单击数据菜单中的合并计算命令。这时将弹出合并计算对话框。
    指定需要使用的函数，添加各工作表需要合并的源数据区域。与按位置合并操作不同的是，除了要选定待合并的数据区域外，还需要选定合并分类的依据所对应的单元格区域，而且各工作表中待合并的数据区域可能不完全相同，需要逐个地选定。如图1－14所示。

图1－14

    与按位置合并不同的还有需要指定标志位置，即分类合并的依据所在的单元格位置。这里选定最左列。根据需要决定是否选定创建连至与源数据的链接选项。为了与按位置合并对照，这里不选定该选项。单击确定。
    按分类合并计算的结果如图1－15所示。请注意，由于没有选定创建连至与源数据的链接选项，所以工作表的左侧没有出现分级显示符号，而且合并数据的单元格中存放的合并计算的结果，而不是有关的公式。

图1－15

1.3  数据透视表

当需要对明细数据做全面分析时，数据透视表是最佳工具。它有机地结合了分类汇总和合并计算的优点，Excel 2000还新增了数据透视图报告功能，使得将数据透视表的分析结果用图表方式提交更为方便。

 1.3.1  数据准备
    为了分析方便，首先将1月～12月的数据复制到一个新工作表“全年数据”中。为此，可利用Offfic 2000提供的新功能：多重剪贴板。多重剪贴板可以同时存放12份复制的内容，根据用户的需要将其中的任意一项或是全部粘贴到指定的位置。具体操作步骤如下：
    选定“1月”工作表，选中整个数据清单。右击任意单元格，在弹出的快捷菜单中，单击复制命令。也可单击复制工具按钮，或是按[Ctrl]+[C]复合键。将1月份的数据复制到剪贴板中。
    按照类似的方法，将“2月”、“3月”、……“12月”工作表的数据复制到剪贴板中。在进行第2次复制操作时，将弹出剪贴板对话框。多重剪贴板最多可存放12份不同的内容。当复制完12个月的数据后，剪贴板对话框如图1－16所示。

图1－16
    创建一个新的工作表“全年数据”，并选定A1单元格为当前单元格。单击剪贴板对话框中的全部粘贴按钮，复制的12个月的数据依次粘贴到新工作表中。如果次序不对，可将该工作表按日期项排序。

1.3.2  创建数据透视表
    创建数据透视表的操作是比较复杂的，但是Excel 2000为此提供了数据透视表和数据透视图向导。在该向导的指导下可以轻松地创建数据透视表。其操作步骤如下：
    选定数据清单中的任意单元格，单击数据菜单中的数据透视表和图表报告命令。
    这时将弹出数据透视表和数据透视图向导--3步骤之1对话框，如图1－17所示。

图1－17
    该步骤主要确定数据源类型和报表类型。这里指定默认选项，即数据源类型为Excel数据清单，报表类型为数据透视表。单击下一步。
    这时将弹出数据透视表和数据透视图向导--3步骤之2对话框，如图1－18所示。   

图1－18
    该步骤选定数据源区域。由于数据清单都是位于某个连续的单元格区域，所以，一般情况下Excel会自动识别数据源所在的单元格区域，并填入到选定区域框中。单击下一步。
    这时将弹出数据透视表和数据透视图向导--3步骤之3对话框，如图1－19所示。   

图1－19
    该步骤指定数据透视表显示的位置。这里选定新建工作表，单击完成。
    这时，Excel 2000自动创建一新的工作表，在该工作表中显示创建的数据透视表的雏形，同时自动显示数据透视表工具栏。如图1－20所示。

图1－20

    这时应根据分析要求，设置数据透视表的版式。该步骤也是创建数据透视表的最关键的一步。假设要分析各销售员不同时期的销售业绩，可以日期作为行字段，销售员作为列字段，而将金额作为数据项。从数据透视表工具栏中，将相应的字段拖放到行字段、列字段和数据项位置。
    这时创建的数据透视图如图1－21所示。其中最右一列有每天的销售合计，最下一行有每个销售员的销售合计。

图3－21

1.3.3  应用数据透视表
    数据透视表的突出优点是可以利用它对数据进行透视分析，可以根据不同的分析要求，对数据透视表进行各种操作。例如进行不同级别的概括汇总，添加或删除分析指标，显示或隐藏细节数据，改变数据透视表的版式等。

1. 调整分析步长
    对于日期型字段可以根据需要调整分析的步长，可以指定其按月、季度或年重新进行分组。例如，对于图1－21所示的数据透视表，为了更清楚地比较企业的月度销售数据，指定其按月进行分类汇总。其具体操作步骤是：
    右击日期字段中的任一日期数据单元格，在弹出的快捷菜单中指向组及分级显示命令，单击其中的组合子命令。
    这时将出现分组对话框，如图1－22所示。

图1－22

    在依据列表框中指定月，单击确定。按月分类汇总的数据透视表如图1－23所示。

图1－23

    如果需要分析更宏观的情况，可以在分组对话框中的依据列表框中，清除月选项，选定季度选项。如果希望同时查看月度和季度的数据，也可以同时选定月和季度选项。图1－24是按季度分类汇总的数据透视表。

图1－24

2． 添加/删除字段
    当需要分析不同的指标时，不需要重新制作报表，只需在原数据透视表的基础上，根据需要简单地添加或删除字段即可。例如对于图1－24所示的数据透视表，需要进一步分析不同产品类别的销售情况，即可以将产品类别字段添加到数据透视表的行字段或列字段中去。将产品类别字段添加到行字段的具体操作步骤如下：
    选定数据透视表中的任意单元格，这时的数据透视表工具栏出现源数据清单的各个字段按钮。当鼠标指针指向产品类别字段按钮时，自动弹出有关的操作提示。如图1－25所示。

图1－25
    将产品类别字段按钮拖放到行字段区域，即可将该字段添加到数据透视表的行中。
    添加了产品类别字段的数据透视表如图1－26所示。

图1－26
    类似地，如果要将某个字段添加到列字段时，则将相应字段拖放到列字段区域。如果要从数据透视表中删除某个字段拖放到数据透视表工具栏即可。在用鼠标拖动某个字段时，鼠标指针会随着鼠标指针所处的位置变化，提示用户这时放开鼠标按键时的操作结果。各种鼠标指针的形状如图1－27所示。

图1－27

2. 分类显示数据
    在Excel 2000的数据透视表中，对所有的行字段、列字段都增加了分类选项箭头。如果只是希望了解数据透视表中某个分类的数据，可以单击相应字段的下拉箭头，然后选择需要显示的分类数据。例如，如果要只显示影碟机产品类别的数据，可以单击产品类别字段的下拉箭头。如图1－28所示。清除彩电选项，然后单击确定按钮。

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

图1－29

4． 显示或隐藏汇总数据
    从图1－29可以看到，这时的产品类别只有一类，所以相应的汇总行已无意义。数据透视表中的行、列汇总数据，实际都是可选项。用户可以根据需要决定显示还是隐藏某个字段的汇总数据。要显示或是隐藏某个字段的汇总数据时，首先选定该字段，再单击数据透视表工具栏的数据透视表按钮，在弹出的下拉框中选字段设置命令。或是右击相应的字段，在弹出的快捷菜单中选字段设置命令。这时将出现相应字段的数据透视表字段对话框。如图1－30所示。

图1－30
    选定分类汇总选项中的无，单击确定。隐藏了分类汇总数据的数据透视表如图1－31所示。
    如果要显示或是隐藏总计数据，可单击数据透视表工具栏的数据透视表按钮，在弹出的下拉框中选表选项命令。或是右击数据透视表，在弹出的快捷菜单中选表选项命令。这时将出现数据透视表选项对话框。选定或清除其中的总计选项即可。

图1－31

5.  显示明细数据
    数据透视表中的数据一般都是由多项数据汇总得来的，如果需要，可以方便地查看明细数据。例如，从图1－31所示的数据透视表可以看到，销售员“方一心”一季度的销售金额较高，如果希望查看其明细数据，可以双击该数据。这时，Excel将自动创建一个新的工作表，显示该数据所对应的明细数据。图1－32是双击“方一心”一季度销售数据后Excel自动新建的明细数据工作表。

图1－32
6． 调整显示方向
    在图1－26所示的数据透视表中，日期和产品类别是作为行字段，其数据分别显示在不同的行；称作行方向显示；而销售员是作为列字段，其数据显示在不同的列，称作列方向显示。此外，还也可以设置页字段，让指定的数据按页方向显示。例如要详尽地分析每个销售员的业绩，可以设置销售员字段以页方向显示。其操作方法是直接用鼠标将销售员字段从列字段处拖放到页字段处即可。以销售员作为页字段的数据透视表如图1－33所示。
    目前显示的是销售员“杨韬”的销售业绩。单击销售员字段右边的下拉箭头，可以选择显示其他销售员，或是选择全部，可以显示其他销售员或是全体销售员的销售业绩。

图1－33

    图1－33的数据透视表由于将销售员字段从原来的列方向改变为页方向显示，所以没有字段按列方向显示，但却有两个字段是按行方向显示的，因而可读性不佳。为此，按照类似的方法，将日期字段改为按列方向显示。将日期字段拖放到列字段后相应的数据透视表如图1－34所示。

图1－34

7.  改变计算函数
    默认情况下，在数据透视表的数值型字段的计算函数是求和函数，非数值型字段的计算函数是计数函数。实际应用中可以根据需要，选择其它函数进行多种计算。Excel 2000对数据透视表提供的计算函数有计数、平均值、最大值、最小值以及乘积等。例如，需要统计图1－34所示数据透视表中的合同数目，而不是金额合计，可以使用计数函数。其具体操作步骤如下：
    选定某个数据字段，再单击数据透视表工具栏的数据透视表按钮，在弹出的下拉框中选字段设置命令。或是右击某个数据字段，在弹出的快捷菜单中选字段设置命令。
    这时将出现相应字段的数据透视表字段对话框。如图1－35所示。

图1－35

    单击汇总方式列表框中的计数选项。再单击确定。
    按计数函数计算的数据透视表如图1－36所示。

图1－36

8. 改变数据显示方式
一般情况下，数据透视表中显示的都是实际的汇总数据。为了更清晰地分析数据间的关系，例如比例或构成关系、差异关系等，可以指定数据透视表以特殊的显示方式来显示数据。Excel 2000提供了差异、百分比、差异百分比、按列递加、指数等不同的数据显示方式。用户可以根据分析的要求选择最为合适的数据显示方式。
  例如，要分析不同时期的销售增长情况，可以选择按差异来显示汇总数据。其具体操作如下：
调出图1-35所示的数据透视表字段对话框。
  单击其中的选项按钮。
  这时数据透视表字段对话框向下展开，如图1-37所示。
  在数据显示方式下拉框中，选择差异显示方式。
  在基本字段列表框中选定日期，在基本项列表框中选定上一个。单击确定。
    差异显示的数据透视表如图1－38所示。

图1－37

图1－38

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

9．设置报告格式
    Excel 2000为数据透视表新增了报表1～报表10和表1～表10共20种自动套用格式，利用它们可以快捷方便地修饰数据透视表，使其更具可读性。其具体操作是：
    选定数据透视表中任意单元格，再单击数据透视表工具栏的设置报告格式按钮。或是右击数据透视表的任意单元格，在弹出的快捷菜单中选设置报告格式命令。将弹出有关数据透视表的自动套用格式对话框。
    从中选择需要的报表格式后，单击确定。
    图1－39为对图1－26所示的数据透视表使用表7格式套用的结果。

图1－39

10.  更新数据
    数据透视表中的数据都是汇总计算的结果，所以如果数据透视表中的数据有误时，不能直接在其上进行修改，而需要修改数据来源工作表，然后通过更新数据命令，使数据透视表更新为依据修改后的数据计算的结果。其具体操作如下：
    单击存放源数据的工作表标签，切换到该工作表。
    修改工作表中有误的单元格数据。
    单击数据透视表的工作表标签，切换到数据透视表。
    单击数据透视表工具栏的数据透视表按钮，在弹出的下拉框中选更新数据命令。或是右击某个数据字段，在弹出的快捷菜单中选更新数据命令。
    这时，数据透视表中的数据将根据修改的源数据自动更新。
    通过以上介绍可以看出，从形式上看数据透视表与一般的工作表没有什么明显的差别，但是实际上它有两个重要特性。
    “透视”性：虽然数据透视表也是一个二维表，但是由于其每个数据都是汇总计算的结果，实际上可以说是一个三维表格。而且可以根据用户的需要，对数据透视表的汇总方式、显示方式进行调整，从而为用户从多角度分析数据提供了极大的方便。
    “只读”性：数据透视表可以向一般工作表一样进行修饰或是制作图表，但是不能直接修改，而必须通过修改源数据和更新数据的方法进行编辑。

1.3.4  应用数据透视图
    使用数据透视表可以准确计算和分析数据，但有时候很难从字面上把握数据的全部含义。Excel 2000新增了数据透视图功能，可以方便地将数据透视表的分析结果以更直观的图表方式提交。与数据透视表相比，数据透视图可以一种更加可视化和易于理解的方式展示数据和数据之间的关系。

1. 创建数据透视图
    在现有数据透视表的基础上创建数据透视图十分方便。在Excel 2000的数据透视表工具栏上新增了图表向导按钮。只要将指定数据透视表中的任意单元格为当前单元格，然后单击该按钮，即可自动建立数据透视图。
    图1－40即是根据1－34中的数据透视表创建的数据透视图。

图1－40

2.  调整数据透视图
    数据透视图创建以后，可以像数据透视表一样方便地进行调整。从图1－40可以看到，行字段、列字段和页字段都有相应的下拉箭头。如果要分类显示某种产品类别的数据，某个季度的数据，或是某个销售员的数据，都可以单击相应字段的下拉箭头，然后去除不需显示的选项即可。图上数据字段还有函数按钮，如果要改变计算函数，双击该按钮，然后再选择计算函数即可。这时的数据透视图和数据透视表都将自动变换。
    因为数据透视图与包含其源数据的数据透视表是相链接的，当数据透视表中的数据改变后，数据透视图也会自动随之改变。也就是说数据透视图具有自动更新功能。例如，当将数据透视表中的日期字段分组有季度改为月时，相应的数据透视图也会自动更新。图1－41即变更数据透视表的日期字段分组后，数据透视图的更新结果。

3.  趋势线分析
Excel 2000为图表提供了添加趋势线功能。在数据透视图中添加趋势线可以使得图形化的数据更为有用。例如，可以用现有12个月的影碟机销售数据，来预测未来1个月影碟机的销售额。其具体操作步骤如下：

图1－41

    用鼠标右击影碟机数据系列中的任意柱形标志，在弹出的快捷菜单中单击添加趋势线命令。
    将弹出趋势线格式对话框，如图1－42所示。

图1－42

    选择类型选项卡，根据数据的特点选择预测趋势或是回归分析的类型。这里在预测趋势/回归分析类型选项中选多项式类型；在阶数中选2。
    选择选项选项卡，设置前推预测的周期数。这里在趋势预测的前推选项中选1。单击确定。
    添加了外推周期为1的趋势线的数据透视图如图1－43所示。    

图1－43

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