数据透视表教程5在数据透视表中计算.docx

数据透视表教程5在数据透视表中计算.docx

《数据透视表教程5在数据透视表中计算.docx》由会员分享，可在线阅读，更多相关《数据透视表教程5在数据透视表中计算.docx（14页珍藏版）》请在冰豆网上搜索。

数据透视表教程5在数据透视表中计算

本课培训重点：

在数据透视表中计算

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、

数据透视表的9种自定义显示方式分别为：

普通、差异、百分比、差异百分比、按某一字段汇总、占同行数据总和的百分比、占同列数据总和的百分比、占总和的百分比、指数。

A、普通：

数据区域字段按照默认的汇总方式计算，默认的汇总方式为求和或计数。

B、差异：

数据区域字段与指定基本字段和基本项的差值，一般用来与标准进行对比时使用。

C、百分比：

数据区域显示为基本字段和基本项的百分比，一般用来分析完成率和达成占比。

D、差异百分比：

数据区域字段显示为与基本字段项的差异百分比。

E、按某一字段汇总：

数据区域字段显示为按某基本字段项的汇总。

一般用于累计和排名等。

F、占同行数据总和的百分比：

数据区域字段显示为每个数据项占该行所有项总和的百分比。

G、占同列数据总和的百分比：

数据区域字段显示为每个数据项占该列所有项总和的百分比。

H、占总和的百分比：

数据区域字段显示为每个数据项占该字段所有项总和的百分比。

I、指数：

公式：

（（单元格的值）*（总体汇总之和））/（（行汇总）*（列汇总）），主要反映数据区域字段项的相对重要性。

实例2：

自定义显示方式测定员工的工时完成情况

在数据透视表里设定“定额工时”为测定基准，计算员工工时跟定额工时的差异以及差异率。

操作方法：

1、根据数据源创建数据透视表。

将“工时数量”三次拖到数据区域，即对“工时数量”进行三次求和计算。

2、对数据进行降序排序。

右键单击“员工姓名”字段》字段设置》在弹出的对话框中选择“高级”》按“工时数量”进行降序排序》确定。

如图5、

3、对“工时数量2”进行差异量的测定分析。

选中“工时数量2”单击右键》字段设置》在弹出的“数据透视表字段”对话框中单击“选项”按钮》选择“差异”（根据需要也可选其它9种显示方式）》基本字段选择“员工姓名”》基本项选择“定额工时”》确定。

如图6、

4、除了差异量的分析，如果还需要得到差异百分率的分析，其操作方式与上面基本相同，只是在“选项”列表中，数据显示方式选择“百分比”即可。

三、计算字段和计算项

计算字段是通过对数据透视表中现有字段进行计算后得到的新字段。

计算项是通过对数据透视表中现有字段的项进行计算后得到的新项。

添加了新的计算字段后，新字段会同时添加在字段列表中，与原来的数据源字段同样使用，具有等同的作用。

计算字段和计算项可以对数据透视表中现有的字段和字段项进行引用运算，也可进行简单的函数运算，但是不能引用数据透视表以外的单元格和数据，也不能使用自定义名称的引用。

操作方法：

光标定位到需插入计算项或计算字段的位置》单击数据透视表工具栏的“数据透视表”》公式》“计算项”或“计算字段”》在弹出的对话框的“名称”处输入新名称，在“公式”处输入新字段的数据运算公式》单击“添加”》确定，即可添加新的计算字段或计算项。

如图7、8、

注意：

A、如果光标定位在列字段或数据区域时只能添加计算字段而无法添加计算项，只有光标定位在行字段或行字段下的项时才能添加计算项。

B、透视表中如果有组合的项目，将不能添加计算项，会出现“由于某数据透视表字段已组合，因此不能向此报表中添加计算项”的警告提示框。

需要先取消组合的项目，然后再插入计算项，最后再对字段中的项目进行分组。

如图9

修改和删除已添加的计算字段和计算项方法：

A、修改计算字段：

选中数据透视表中要修改的字段》单击数据透视表工具栏“数据透视表”》公式》计算字段》单击名称下拉按钮》选择要修改的字段名称》在名称框输入新的名称》在公式框输入新的公式》修改》确定。

即可对计算字段进行修改。

B、删除计算字段：

选中数据透视表中要删除的字段》单击数据透视表工具栏“数据透视表”》公式》计算字段》单击名称下拉按钮》选择要删除的字段名称》删除》确定。

即可对计算字段进行删除。

修改计算项和删除计算项可参照修改计算字段的删除计算字段的方法。

如图10、

实例3：

添加字段进行工时完成情况排序

根据不同小组的员工的工时完成情况，进行名次的排序。

操作方法：

1、根据数据源生成数据透视表，对“工时数量”字段进行求和汇总。

并将“员工姓名”字段根据“工时数量”进行降序排序。

如图11、

2、利用添加计算字段方式实现名次的排序。

选中数据区任意一个单元格》单击数据透视表工具栏选择“公式”》计算字段》在“插入计算字段”对话框。

将需要添加的计算字段名称定义为“名次”，在“公式”中输入“1”》确定。

即新增加了值为“1”的“名次”的字段。

如图12、

3、用自定义显示方式进行名次排序。

选择“名次”字段单击右键》字段设置》选项》在“数据显示方式”列表中选择“按某一字段汇总”》基本字段选择“员工姓名”》确定。

如图13、

注意：

在进行名次的排序前一定要先进行降序的排序。

实例4：

用百分比方式显示计算结果

如果需要用百分比显示方式来表达对数据的计算结果，通过自定义显示方式可以计算数据项在行或列数据中的百分比，也可计算数据项在所有数据总和中的百分比。

操作方法：

根据数据源生成数据透视表，选中数据区域的单元格》右键单击》字段设置》在数据显示方式的下拉列表中选择“占同行数据总和的百分比”》确定。

即可得到某一商品在不同地区的销售百分比的情况。

如图14、

也可在数据显示方式的下拉列表中选择“占同列数据总和的百分比”或“占总和的百分比。

实例5：

添加计算字段和计算项进行分析

需要同时显示数据和所占费用的百分比，并对两年数据差异分析，就需要用到计算字段和计算项。

操作方法：

1、添加计算字段：

选中要插入计算字段的单元格》单击数据透视表工具栏“数据透视表”》公式》计算字段》在插入计算字段的名称框中输入“差异”》在公式里输入“='2007年'-'2006年'”，（也可以在字段列表内单击再单击插入字段或双击字段名）》添加》确定。

即完成计算字段添加。

如图15、

2、添加计算项：

选中要插入字段项的行字段》单击数据透视表工具栏中“数据透视表”》公式》计算项》在名称框中输入计算项的新名称》在字段选择框中选择要添加计算项的隶属字段》在项中选择计算的项》添加》确定。

完成计算项添加。

如图16、

注意：

在添加了计算项后列总计的数据可能不准确，解决办法是另外再添加一个计算项进行总计的计算。

如图17、18

四、常见问题分析

1、为什么在透视表中使用match等函数却得不到运算结果？

在数据透视表计算字段中可以使用简单的函数运算（如sum，if，text等），但数据透视表的运算是在透视表缓存中进行的，因此不能使用函数对单元格进行引用或自定义名称等。

也就是说，可以使用不需要使用单元格引用或自定义名称的工作表函数，而Match则需要使用单元格引用。

因此，不能在计算字段或计算项中使用。

2、为什么添加了由“单价”*“数量”得到的“总金额”新字段后，其合计数计算不正确？

数据透视表总计结果总是先进行求和再进行其它运算，因此，在计算总金额时，是将“单价”和“数量”先分别求和，然后再进行相乘，因此计算结果不正确。

如图19、

3、为什么我的透视表不能进行行总计？

数据透视表的行总计或列总计仅能计算由一维表数据项形成的列字段的数据，而对于二维的数据表创建的多列字段形成的列字段数据，则需要通过添加计算字段进行求和或其它方式来实现。

一维表就是只有一个可供计算的数据项，对由一维表形成的透视表只需打开“表格选项”中行列总计即可自动进行行列汇总。

小结：

本节课主要介绍了以下几个方面的内容

1、多种方式的计算，如（求和、平均值、最大值、最小值等）。

2、常用的汇总显示方式，如（差额、占同行百分比、占同列百分比、占总和百分比、按某一字段汇总）

3、添加计算字段和计算项。

4、数据透视表常见的三种问题。