数据透视表教程—在数据透视表中计算.docVIP

本课培训重点：在数据透视表中计算 1、多汇总方式计算---11种汇总方式 2、自定义显示方式---9种显示方式 3、添加计算字段和计算项 4、利用透视表函数、SQL语句、VBA在透视表中进行计算（这方面的内容将在以后的学习中会讲） ? 一：多种汇总方式计算---11种汇总方式 如果要对数据区域中的同一字段同时使用多种汇总方式，只需要将字段列表中的字段多次拖到数据区域，在多次使用某字段时，数据透视表会自动在字段后面加上序列号。然后单击右键》字段设置》在“数据透视表字段”对话框的“汇总方式”下拉列表中选择不同的汇总方式。如图1、 数据透视表的11种汇总方式分别为：求和，计数，平均值，最大值，最小值，乘积，数值计数，标准偏差，总体标准偏差，方差，总体方差。 A、求和：计算所有数值数据的累加和。 B、计数：对所有单元格进行计数，包括数值、文本和错误的单元格（单元格内有空格也会计算在内）。与Excel函数=Counta()等同。 C、平均值：求平均值。 D、最大值：显示最大值。 E、最小值：显示最小值。 F、乘积：将所有单元格的数值相乘。如果数据集的单元格的值分别是3、4、5，则乘积为60。 G、数值计数：只计算数值单元格的个数，与Excel函数=Count()等同。 H、标准偏差和总体标准偏差：计算标准偏差。如果数据集包含全部成员，则要用“总体标准偏差”。如果数据集包含成员的一些样本，则使用“标准偏差”。 I、方差和总体方差：计算统计方差。如果数据集包含全部成员，则要使用“总体方差”。如果数据集只是全体成员的抽样，则使用“方差”估计方差。 “计数”和“数值计数”的区别在于，“计数”是只要不是空值的项就会进一个空格也会进行计数的统计，而“数值计数”则是只对是数值型的项进行计数统计。另外，数据透视表的汇总方式默认情况下是以求和方式汇总，但是如果数据源中有空白或是非数值类型的数据则使用计数方式汇总。 ? 实例1：多种方式统计员工的生产数量 统计不同员工的生产总量、平均产量、最大产量以及最小产量。 操作方法： 1、用数据源创建数据透视表，将“生产数量”字段拖到数据区域按“求和”汇总。即可统计各个生产员工的“生产总量”。如图2、 2、再次将“生产数量”字段拖动到数据区域，选中“生产数量2”字段，单击鼠标右键》字段设置》选择汇总方式为“平均值”》确定。即可统计各个生产员工“生产数量”的“平均产量”。如图3、 3、用同样的方法也可以对“最大产量”和“最小产量”进行汇总。只需选择不同的汇总方式即可。 ? 二：多种自定义显示方式---9种显示方式 数据透视表的自定义显示方式可用不同的方式显示数据透视表数据区域中的字段项数据。 操作方法：单击右键》字段设置》选项》在“数据显示方式”下拉框中选择9种数据显示方式中的任意一种》指定基本字段和基本项》确定。如图4、 HYPERLINK /showpic.html \l blogid=4d042yurl=/orignal/4d0a0642g97aba5883b0f \t _blank ? ?? 数据透视表的9种自定义显示方式分别为：普通、差异、百分比、差异百分比、按某一字段汇总、占同行数据总和的百分比、占同列数据总和的百分比、占总和的百分比、指数。 A、普通：数据区域字段按照默认的汇总方式计算，默认的汇总方式为求和或计数。 B、差异：数据区域字段与指定基本字段和基本项的差值，一般用来与标准进行对比时使用。 C、百分比：数据区域显示为基本字段和基本项的百分比，一般用来分析完成率和达成占比。 D、差异百分比：数据区域字段显示为与基本字段项的差异百分比。 E、按某一字段汇总：数据区域字段显示为按某基本字段项的汇总。一般用于累计和排名等。 F、占同行数据总和的百分比：数据区域字段显示为每个数据项占该行所有项总和的百分比。 G、占同列数据总和的百分比：数据区域字段显示为每个数据项占该列所有项总和的百分比。 H、占总和的百分比：数据区域字段显示为每个数据项占该字段所有项总和的百分比。 I、指数：公式：((单元格的值)*(总体汇总之和))/((行汇总)*(列汇总))，主要反映数据区域字段项的相对重要性。 ? 实例2：自定义显示方式测定员工的工时完成情况 在数据透视表里设定“定额工时”为测定基准，计算员工工时跟定额工时的差异以及差异率。 操作方法： 1、根据数据源创建数据透视表。将“工时数量”三次拖到数据区域，即对“工时数量”进行三次求和计算。 2、对数据进行降序排序。右键单击“员工姓名”字段》字段设置》在弹出的对话框中选择“高级”》按“工时数量”进行降序排序》确定。如图5、 3、对“工时数量2”进行差异量的测定分析。选中“工时数量2”单击右键》字段设置》在弹出的“数据透视表字段”对话框中单击“选项”按钮》