透视表函数GETPIVOTDATAWord文档格式.docx
- 文档编号:14419421
- 上传时间:2022-10-22
- 格式:DOCX
- 页数:10
- 大小:631.27KB
透视表函数GETPIVOTDATAWord文档格式.docx
《透视表函数GETPIVOTDATAWord文档格式.docx》由会员分享,可在线阅读,更多相关《透视表函数GETPIVOTDATAWord文档格式.docx(10页珍藏版)》请在冰豆网上搜索。
&
item1&
item2,…&
item14)
data_field&
item14是中间用空格连接成的完整的文本。
此语法的data_field可以引用多个单元格,可不用OFFSET进行引用,从而达到精简函数目的。
此语法适用于默认汇总方式,应用方法见实例5。
数据透视表函数的语法三
GETPIVOTDATA(pivot_table,"
field1[item1;
汇总方式]data_field"
)
此语法适用于自定义汇总方式,应用方法见实例6。
快速获取getpivotdata函数的方法:
在目标单元格输入等号,然后单击透视表中的任意单元格,如果没有自动生成getpivotdata函数,说明该函数功能没有打开,需要在透视表工具单击向下三角》删除和添加按钮》数据透视表》勾选“生成getpivotdata”。
这样在工具栏添加了一个新的按钮,这个按钮相当于一个开关键,将新加的按钮按下去即可自动生成getpivotdata函数。
如果想取消,再点击一次按钮即可。
一、认识数据透视表函数
实例1:
1、销售总量:
=GETPIVOTDATA("
求和项:
数量"
$A$1)
2、海口分公司销售金额:
金额"
$A$1,"
分公司"
"
海口分公司"
3、南京分公司2008年10月2日C产品的销售量:
品种"
C产品"
南京分公司"
日期"
DATE(2008,10,2))
注意:
在使用日期字段里的字段项的时候最好用DATE函数返回日期的结果,以避免日期格式的变化出现查找出错的情况。
4、A产品销售总金额:
A产品"
数据透视表函数只能查找数据透视表里面已经反应出来的数据,没有的数据将会出现错误值。
如上例,因为在数据透视表中没有A产品销售金额汇总的值。
所以出现错误的值。
另处,除了利用快速引用的方法,还可以修改函数公式的参数。
因为在公式里的参数都是具体的文本值,可以通过引用单元格的数据使公式更加灵活。
二、动态获取数据透视表的数据
实例2:
(数据透视表函数语法1应用)
原公式:
E3=GETPIVOTDATA("
)/GETPIVOTDATA("
修改的公式:
E3=GETPIVOTDATA(T($D$2),$A$1,$B$2,B3,$A$2,LOOKUP("
々"
$A$3:
A3))/GETPIVOTDATA(T
($C$2),$A$1,$B$2,B3,$A$2,LOOKUP("
A3))
这样公式即可向下拖动,由于公式向下拖动时,行字段只有第一个数据项有值。
比如海口分公司只有A3有数据,A4:
A6为空值,所以当公式向下拖动时会出现错误。
这里就用LOOKUP对其进行处理,LOOKUP("
A3)即是在$A$3:
A3这个动态的区域里面查找"
(这个字符在字码表里大于所有的汉字,输入方法:
Alt+41385),因为当LOOKUP查找的值不存在时,会返回一个小于查找值的最大值,即用A3的数据对A4:
A6进行填充。
在进行引用时要注意,引用的第一个字段要用T函数进行处理,还要注意单元格的引用方式,行字段要用绝对引用,如果行字段下的数据项是连续的数据就可采用相对引用,如果数据项有空值就要用LOOKUP函数来进行填充。
三、数据透视表函数与更多的函数的联合应用
实例3、(数据透视表函数语法1应用)
定义名称:
g_name=SUBSTITUTE(LOOKUP("
透视表!
$A$3:
$A1),"
汇总"
应用公式:
E3=GETPIVOTDATA(T($C$2),$A$1,$A$2,g_name,IF($B3=0,$A$2,$B$2),IF($B3=0,g_name,$B3)
)/GETPIVOTDATA(T($C$2),$A$1,$A$2,g_name)
定义名称的用途是填充行字段“分公司”下为空值的项。
另外,因为此透视表有汇总行,所以要用SUBSTITUTE将“汇总”替换为空文本。
四、在透视表函数中运用内存数组
实例4、(数据透视表函数语法1应用)
aa=IF(ISNUMBER(GETPIVOTDATA(T($D$2),透视表!
$A$1,$A$2,透视表!
$A$34)),
GETPIVOTDATA(T($D$2),透视表!
$A$34))
1、销售量最大的分公司:
=LOOKUP(2,1/(MAX(aa)=aa),$A$3:
$A$34)
2、销售量最小的分公司:
=LOOKUP(2,1/(MIN(aa)=aa),$A$3:
3、销售量最大的分公司C产品的销售金额:
=GETPIVOTDATA(T($E$2),$A$1,$B$2,"
$A$2,LOOKUP(2,1/(MAX(aa)=aa),$A$3:
4、销售量最大的分公司2008年10月1日A产品的销售价格:
A产
品"
$A$34),$C$2,DATE(2008,10,1))/GETPIVOTDATA(T($D$2),
$A$1,$B$2,"
$A$34),$C$2,DATE(2008,10,1))
5、销售量最小的分公司2008年10月1日B产品的销售价格:
=PRODUCT(GETPIVOTDATA(T(OFFSET(D2,,
{0,1})),$A$1,$B$2,"
B产品"
$A$2,LOOKUP(2,1/(MIN(aa)=aa),$A$3:
$A$34),$C$2,DATE(2008,10,1))^{
-1,1})
此语法不能同时引用数量和金额(E2和D2)两个单元格,所以用OFFSET(D2,,{0,1})来进行处理,然后再将两个数相除。
6、所有分公司A产品的销量:
=SUM(GETPIVOTDATA("
T(OFFSET(A2,{1,13,23},))))
公式中OFFSET(A2,{1,13,23},)即是引用三个分类汇总的行。
实例5、(数据透视表函数语法2应用)
查寻结果:
南京分公司B产品2008年10月2日数量:
=GETPIVOTDATA($A$1,$H$18&
$H$19&
$H$20&
TEXT($H$21,"
YYYY年mm月d日"
))
)是设置查找日期的格式与数据透视表日期的格式相同。
此语法在使用时会更加灵活,它的data_field参数可以引用两个或多个单元格。
比如:
销售量最大的分公司2008年10月1日A产品的销售价格:
{=PRODUCT(GETPIVOTDATA($A$1,D2:
E2&
LOOKUP(2,1/(MAX(aa)=aa),$A$3:
$A$34)&
TEXT(C11,"
yyyy年mm月d日"
))^{-1,1})}
五、获取自定义分类汇总结果
实例6、(数据透视表函数语法3应用)
根据数据透视表生成汇总表:
B4=GETPIVOTDATA(透视表!
$A$1,透视表!
$A$2&
["
$A4&
;
计数]"
B$3)
公式向下和向右拖动即可生成汇总表。
六、量身定做自己的数据表
实例7、(数据透视表函数综合应用)
可以利用透视表函数计算速度快的特点创建自定义的数据报表,对数据透视表数据比较多,数据量大的数据透视表进行摘录时,就可用数据透视表函数来完成。
本节课主要内容小结:
一、数据透视表函数三种语法结构
1、默认汇总方式下的二种。
2、自定义汇总方法下的一种。
二、数据透视表函数各参数可以是:
常量、单元格引用(单一或多个单元格)、公式、数组(常量、公式结果)等。
三、数据透视表函数应用范围
1、创建自定义数据表。
2、对数据透视表数据进行摘录。
很快紧张的一个月的学习时间过去了,回想一下,学到的东西真还不少,但是我感觉还有些问题自已掌握的还不是很牢固,还需要在课后认真的复习和应用,才能够真正的学为已用,学有所用。
其实做好笔记也是为了以后复习方便。
另外,这已经是最后一课的小节了,在这里我要向所有的同学说声再见,希望大家在今后的学习中取得更优异的成绩,希望大家的Excel水平更上一层台阶。
Welcome!
!
欢迎您的下载,
资料仅供参考!
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 透视 函数 GETPIVOTDATA
![提示](https://static.bdocx.com/images/bang_tan.gif)