数据透视分析.doc

《数据透视分析.doc》由会员分享，可在线阅读，更多相关《数据透视分析.doc（23页珍藏版）》请在金锄头文库上搜索。

1、目录第一章 数据透视分析21.1 分类汇总21.1.1 分类依据21.1.2 分类汇总操作31.2合并计算51.2.1 按位置合并51.2.2 按分类合并91.3 数据透视表101.3.1 数据准备101.3.2 创建数据透视表111.3.3 应用数据透视表131.3.4 应用数据透视图20第一章 数据透视分析 在报表处理过程中，经常需要对报表中的数据进行各种汇总计算。例如，在销售管理方面，需要定期对销售情况进行分类汇总；有时还需要将多个报表进行合并计算；而对于更高层次的管理人员，可能需要从不同的分析角度，对同一张报表根据不同的指标进行分类汇总。这一过程被形象地称为“透视分析”。而这些操作在E

2、xcel 2000中都可以方便、快捷地实现。以下通过某个生产视频设备的企业的销售管理实例，说明上述工具的应用。 1.1 分类汇总 各种报表处理中最常用的就是分类汇总。例如会计核算，需按照科目将明细账分类汇总；仓库管理，需按照库存产品类别将库存产品分类汇总；医院管理，需按照疾病进行病源病谱的分类汇总等等。例如，某企业记录1999年1月份销售明细账的工作表如图11所示。现需要汇总各种商品的销售额。虽然可以利用Excel 2000的公式和函数完成有关的计算，但是使用分类汇总工具更为有效。 图11 1.1.1 分类依据 在进行分类汇总操作之前，首先要确定分类的依据。例如图11的数据，如果要考查不同销售

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

4、单都按照产品类别排好顺序，可以进行分类汇总操作了。 1.1.2 分类汇总操作 分类汇总操作的基本步骤如下： 如果当前单元格不在数据清单中，选定数据清单中的任一单元格。 单击数据菜单中的分类汇总命令。 这时将弹出分类汇总对话框，如图12所示。 图12 在分类字段下拉框中选定产品编号；在汇总方式中选定求和；在选定汇总项中选定数量和金额两项；根据需要决定是否选定汇总结果显示在数据下方选项，如果不选定该项，则汇总结果显示在数据上方。最后单击确定。 分类汇总的结果如图13所示。图13 从图13可以看到，Excel 2000除了在每一类明细数据的下面添加了汇总数据以外，还自动在数据清单的左侧建立了分级显示

5、符号。该区域的顶部为横向排列的级别按钮，其数目的多少取决于分类汇总时的汇总个数。区域的下面是对应不同级别数据的显示明细数据按钮 和隐藏明细数据按钮 。利用分级显示符号，可以根据需要灵活地观察所选级别的数据，也可以方便地创建显示汇总数据、隐藏细节数据的汇总报告： 要显示指定级别的汇总数据，可单击分级显示符号上部相应的级别按钮。图14为单击级别2按钮 ，显示2级汇总数据的结果。 图14 要显示某一个分组数据的明细数据，可单击相应汇总数据所对应的显示明细数据按钮 。这时相应的按钮变为隐藏明细数据按钮 。图15为显示产品编号为“C2919PK”的明细数据的结果。 图15要隐藏某一个分组数据的明细数据，

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

7、。按照类似的操作方法，可以完成2月、3月12月等各月的销售数据的汇总。显然最后还需要将全年的销售数据汇总。要将多个工作表中的数据合并汇总，需要使用Excel 2000的合并计算命令。 1.2.1 按位置合并 假设已有1月12月的销售汇总数据，分别存放在工作表“1月”、“2月”、“12月”中，其中图16即为“1月”工作表。需要存放合并数据的工作表为“全年汇总”。假设这13个工作表的格式相同，这时可通过按位置合并的操作，将上述12个工作表的数据合并到“全年汇总”工作表中。 图16 按位置合并操作的基本步骤如下： 首先选定要存放合并数据的“全年汇总”工作表为当前工作表，并选定存放合并数据的单元格区域

8、。如图17所示。 图17 单击数据菜单中的合并计算命令。 这时将弹出合并计算对话框，如图18所示。 图18 在函数下拉框中选定求和。 单击引用位置框中的折叠按钮 ，然后选定“1月”工作表中的B2:B16单元格区域，再单击还原按钮 。 单击添加命令，选定的第一个要合并的单元格区域添加到所有引用位置框中。 这时的合并计算对话框如图19所示。 图19 逐个单击“2月”、“3月”、“12月”工作表，并单击添加按钮，将12个工作表相同单元格区域添加到所有引用位置框中。如图110所示。图110 最后单击确定按钮，完成合并计算。合并计算的结果如图111所示。 图111 如果在合并计算对话框中选定了创建连至数

9、据源的链接选项，则存放合并数据的工作表中存放的不是单纯的合并数据，而是计算合并数据的公式。这时在工作表的左侧将出现分级显示符号。可以根据需要显示或是隐藏源数据。如图112所示。 图112 这时的合并数据与源数据建立了链接关系，也就是说，当源数据变动时，合并数据会自动更新，保持一致。 1.2.2 按分类合并 如果待合并的工作表格式不完全相同，例如有可能各月份销售的产品不完全相同，这时不能简单地采用上述按位置合并的方法。而需要按分类进行合并。按分类合并的操作与按位置合并大致相同，其基本步骤如下： 首先选定要存放合并数据的“全年汇总”工作表为当前工作表，并选定存放合并数据的单元格区域。与按位置合并不

10、同的是，这时应同时选定分类依据所在的单元格区域。如果不能确切知道有多少类，可以只选定单元格区域的第一行。如图113所示。 图113 单击数据菜单中的合并计算命令。这时将弹出合并计算对话框。 指定需要使用的函数，添加各工作表需要合并的源数据区域。与按位置合并操作不同的是，除了要选定待合并的数据区域外，还需要选定合并分类的依据所对应的单元格区域，而且各工作表中待合并的数据区域可能不完全相同，需要逐个地选定。如图114所示。 图114 与按位置合并不同的还有需要指定标志位置，即分类合并的依据所在的单元格位置。这里选定最左列。根据需要决定是否选定创建连至与源数据的链接选项。为了与按位置合并对照，这里不

11、选定该选项。单击确定。 按分类合并计算的结果如图115所示。请注意，由于没有选定创建连至与源数据的链接选项，所以工作表的左侧没有出现分级显示符号，而且合并数据的单元格中存放的合并计算的结果，而不是有关的公式。 图1151.3 数据透视表当需要对明细数据做全面分析时，数据透视表是最佳工具。它有机地结合了分类汇总和合并计算的优点，Excel 2000还新增了数据透视图报告功能，使得将数据透视表的分析结果用图表方式提交更为方便。 1.3.1 数据准备 为了分析方便，首先将1月12月的数据复制到一个新工作表“全年数据”中。为此，可利用Offfic 2000提供的新功能：多重剪贴板。多重剪贴板可以同时存

12、放12份复制的内容，根据用户的需要将其中的任意一项或是全部粘贴到指定的位置。具体操作步骤如下： 选定“1月”工作表，选中整个数据清单。右击任意单元格，在弹出的快捷菜单中，单击复制命令。也可单击复制工具按钮，或是按Ctrl+C复合键。将1月份的数据复制到剪贴板中。 按照类似的方法，将“2月”、“3月”、“12月”工作表的数据复制到剪贴板中。在进行第2次复制操作时，将弹出剪贴板对话框。多重剪贴板最多可存放12份不同的内容。当复制完12个月的数据后，剪贴板对话框如图116所示。 图116 创建一个新的工作表“全年数据”，并选定A1单元格为当前单元格。单击剪贴板对话框中的全部粘贴按钮，复制的12个月的

13、数据依次粘贴到新工作表中。如果次序不对，可将该工作表按日期项排序。 1.3.2 创建数据透视表 创建数据透视表的操作是比较复杂的，但是Excel 2000为此提供了数据透视表和数据透视图向导。在该向导的指导下可以轻松地创建数据透视表。其操作步骤如下： 选定数据清单中的任意单元格，单击数据菜单中的数据透视表和图表报告命令。 这时将弹出数据透视表和数据透视图向导-3步骤之1对话框，如图117所示。图117 该步骤主要确定数据源类型和报表类型。这里指定默认选项，即数据源类型为Excel数据清单，报表类型为数据透视表。单击下一步。 这时将弹出数据透视表和数据透视图向导-3步骤之2对话框，如图118所示

14、。 图118 该步骤选定数据源区域。由于数据清单都是位于某个连续的单元格区域，所以，一般情况下Excel会自动识别数据源所在的单元格区域，并填入到选定区域框中。单击下一步。 这时将弹出数据透视表和数据透视图向导-3步骤之3对话框，如图119所示。 图119 该步骤指定数据透视表显示的位置。这里选定新建工作表，单击完成。 这时，Excel 2000自动创建一新的工作表，在该工作表中显示创建的数据透视表的雏形，同时自动显示数据透视表工具栏。如图120所示。图120 这时应根据分析要求，设置数据透视表的版式。该步骤也是创建数据透视表的最关键的一步。假设要分析各销售员不同时期的销售业绩，可以日期作为行字段，销售员作为列字段，而将金额作为数据项。从数据透视表工具栏中，将相应的字段拖放到行字段、列字段和数据项位置。 这时创建的数据透视图如图121所示。其中最右一列有每天的销售合计，最下一行有每个