|
提纲：多汇总方式计算——11种汇总方式 自定义显示方式——9种显示方式 添加计算字段和计算项 利用透视表函数、SQL语句、VBA在透视表中进行计算——以后课程中介绍。 一、多汇总方式计算 多种计算方式：可对同一字段进行多次汇总，根据统计的不同需要来以不同形式来展示计算汇总结果。如对“生产数量”行四次展示，分别展示“生产数量求和”、“产数量平均值”、“最大生产数量”和“最小生产数量”等。 数据透视表字段一共有11种显示方式：求和、计数、平均值、最大值、最小值、乘积、数值计数、标准偏差、总体标准偏差、方差和总体方差。如下截图。 二、自定义显示方式 自定义显示方式：可对同一字段以不同的方式来显示，以进行数据差异分析等。如对各员工的工时数量与定额工时的数量进行比较，得出二者差异额及差异率的数值，可帮助深入分析数据。具体操作方法：选中数据字段——右键——字段设置——点击“选项”——选择数据显示方式——确定“基本字段”和“基本项” 字段自定义显示有9种方式：普通、差异、百分比、差异百分比、按某一字段汇总、占同行数据总和的百分比、占同列数据总和的百分比、占总和的百分比、指数 1、普通：数据区域字段按默认的方式计算 2、差异：数据区域字段与指定的基本字段和基本项的差值——用于展现与基础比较项之间的绝对差额，如期末实绩与年度计划之间的差额。 3、百分比：数据区域显示为基本字段和基本项的百分比——用于展现与基础比较项的相对值，如期末实绩与年度计划比较所得的进度。 4、差异百分比：数据区域字段显示为与基本字段性的差异百分比——用于展现与基础比较项的差额相对值，如期末实绩与年度计划的缺口进度。 5、按某一字段汇总：数据区域字段显示为基本字段项的汇总——如用于现金流水账簿的每天余额的计算。 6、占同行数据总和的百分比：数据区域字段显示为每个数据项占该行所有项总和的百分比——用于比较某产品市场分地区占比的显示等 7、占同列数据总和的百分比：数据区域字段显示为每个数据项占该列所有项总和的百分比——用途同上一个指标。 8、占总和的百分比：数据区域字段显示为每个数据项占该字段所有项总和的百分比。——如用于展现分项分地区的产品销售额占总销售额的比重。 9、指数——使用公式：（单元格的指*总体汇总之和）/（行汇总*列汇总）——通常用于展现数据在总体中的重要程度。 修改自定义数据段的显示方式：在“数据显示方式”下拉列表中选择其他的数据显示方式。 三、添加计算字段和计算项 如果多汇总方式计算和自定义显示方式仍无法满足我们对数据透视表数据处理的要求，我们还可以通过添加计算字段和计算项来达到某种要求。 计算字段和计算项的区别： 计算字段：通过已有字段进行计算得出的一个新字段。——对数据区域的有关字段进行重新计算得出新字段。 计算项：在某一字段计算项下，在某一计算项或不同计算项之间进行计算后得出的新数据项。——对行字段或者列字段下的具体数据项进行重新计算得出新计算项。 1、计算字段 创建计算字段的方法：点击数据透视表的任意单元格，然后在数据透视表工具栏的菜单“数据透视表”——“公式”——“计算字段，打开”插入计算字段“对话框。
删除数据透视表中的计算字段：选中数据透视表中任一单元格，然后选择数据透视表工具栏中的菜单“数据透视表”——“公式”——“计算字段”，打开“插入计算字段”对话框，在“名称”中选中要删除的计算字段，然后点击右侧的“删除”按钮即可。 计算字段的局限性：数据透视表中的计算字段，不是按照数据字段在数据透视表中所显示的数据进行计算，而是依据各个数据之和来计算。也就是说即使数据字段的汇总方式被设置为“平均值”，计算字段也会将其看做是“求和”。 主要注意的是添加字段字段后的数据透视表“总计”结果有时也会出现错误。 解决方法：通过手工添加总计行。 2、计算项 创建计算项：单击数据透视表的行字段或者列字段，然后选择“数据透视表”工具栏中的菜单“数据透视表”——“公式”——“计算项”，打开在行字段下插入计算字段的对话框。
计算项局限性：添加计算项后会导致行总计或列总计不正确。 三、在数据透视表中进行计算的常见问题 1、为什么在计算字段中使用了match等函数却得不到计算结果？ 因为在数据透视表中计算字段中可以使用简单的函数运算，但是数据透视表的计算是在透视表缓存进行的，因此不能使用函数对单元格进行引用或定义名称等。只能使用SUM、IF、AND、NOT、OR、COUNT、AVERAGE、TEXT等函数。 2、为什么添加了由“单价”ד数量”得到的“总金额”新字段后，其合计数计算不正确。 数据透视表总计结果总是先进行求和再进行其他运算，因此在计算总金额时，是先将“单价”和“数量”分别求和再进行相乘，因此计算结果不正确。 3、为什么我的数据透视表不能进行行总计？ 数据透视表的行总计仅能计算由一维表数据项形成的列字段的数据，而对于两维表创建的多列字段形式的列字段数据则需要通过添加计算字段进行求和或其他方式来实现。 这节课是我觉得目前为止应用性最强的一节课程。谢谢老师，谢谢EH。 EXCELHOME 主页：http://www./ 技术论坛：http://club./ 免费培训中心：http://t./ |
|
|
