数据透视分析(精品).doc

目录第一章  数据透视分析 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第一章  数据透视分析  在报表处理过程中，经常需要对报表中的数据进行各种汇总计算例如，在销售管理方面，需要定期对销售情况进行分类汇总；有时还需要将多个报表进行合并计算；而对于更高层次的管理人员，可能需要从不同的分析角度，对同一张报表根据不同的指标进行分类汇总这一过程被形象地称为“透视分析”而这些操作在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－151.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所示其中最右一列有每天的销售合计，最下一行有每个。