数据透视表之自定义计算字段和计算项
(一)自定义计算字段
自定义计算字段,是在数据透视表中增加新的计算字段,但源数据并不改变。
(学习:简单的说,添加计算字段:通过对已有的字段进行计算,来得到新的字段。)
1、添加自定义计算字段:简单公式
数据透视表工具——公式——计算字段,在插入计算字段的名称、公式中分别输入。
公式可以直接输入、粘贴以及利用原计算字段进行修改。
2、添加自定义计算字段:复杂公式
可以提前在空白单元格输入、测试好复杂的计算公式,然后采用粘贴的方式添加到自定义计算字段。
(由于数据透视表的计算是在缓存中进行,因此很多公式不能用。可用的公式有sum,avg,min,max,count,stdev,va,isnull,total等)
3、修改自定义计算字段
只有自定义计算字段和公式可以修改,源数据带来的字段可以不显示,但是不能修改。
4、删除自定义计算字段
同上,源数据带来的字段不能删除。
自定义计算字段之间有关联关系的,前一字段的删除,会造成后一字段的错误。
(二)自定义计算项
计算项是字段属下的
(学习:添加计算项:是在某一个字段下里,不同项之间进行运算得到一个新的项目。)
(学习:通过简单的计算公式,可以对透视表进行加工,得到新结果,这样透视表的计算功能得到了扩展。
值得注意的是,通过添加字段或计算项后的总计已经没有实际意义,所以我们要添加一个有意义的总计,而把原来的总计隐藏掉。)
1、添加自定义计算项
在已有字段(如:销售人员)下,将具体的某几个人原确认为自定义计算项(如:销售一部),将另几个人确认为另一自定义计算项(如:销售二部)。销售一部与销售二部两者没有交叉、互斥等逻辑关系。
自定义计算字段显示在字段列表中,自定义计算项显示在数据透视表中。
2、修改自定义计算项的单个计算公式
3、为计算项的特定单元格编辑独立的公式
允许直接在计算好数据的单元格中修改公式,但修改的只是单一单元格。
注意:对某一个计算项公式的改变,与对该计算项中某一个单元格公式的改变,两者的不同。
4、删除自定义计算项
(三)获取所有自定义计算字段和计算项的定义列出公式
通过列出公式、求解次序获取查看,注意公式的顺序。
(四)自定义计算字段和计算项的注意事项
1、有自定义计算字段和计算项的汇总问题
对于有日期、时间的字段,等可能会造成汇总与明细不符。例如考勤表中的汇总。
2、哪些情况下不能添加自定义计算字段和计算项字段
进行了分组,以及汇总方式选用平均数、方差等
四、制作动态的数据透视表
(一)定义动态数据名称
1、什么是名称
名称就是工作表单元格或单元格区域命名的一个名称。
名称分为工作簿名称和工作表名称。
定义名称时,名称前工作表名称则称为工作表名称。
有效范围不同,前者在工作簿中所有工作表都有效,后者只在定义的工作表内有效
2、定义名称的规则
1、名称中的第一个字符必须是字母、下划线 (_) 或反斜杠 ()。名称中的其余字符可以是字母、数字、句点和下划线。
2、名称不能与单元格引用(例如 Z$100 或 R1C1)相同。
3、空格无效 在名称中不允许使用空格。请使用下划线 (_) 和句点 (.) 作为单词分隔符,例如 Sales_Tax 或 First.Quarter。
4、名称长度 一个名称最多可以包含 255 个字符。
5、区分大小写 名称可以包含大写字母和小写字母。Excel 在名称中不区分大写字符和小写字符。例如,如果创建了名称 Sales,接着又在同一工作簿中创建另一个名称 SALES,则 Excel 会提示您选择一个唯一的名称。
3、定义名称的基本方法
1、插入——名称——定义
2、利用名称框
3、插入——名称——指定
4、使用offset函数和counta函数定义动态数据名称
5、获取所有名称的清单在空白处
插入——名称——粘贴
6、修改名称
7、删除名称
(二)利用动态名称制作数据透视表
适用于引用本数据簿内的数据
1、定义动态名称
2、制作数据透视表
f2键,使公式处于编辑状态,然后上下左右键开始好用
3、刷新数据透视表
(三)利用导入数据的方法制作数据透视表
适用于引用其它文件的数据。
1、基本方法和步骤
2、刷新数据透视表