ExcelHome数据透视表中级班_yonglin 日语中级班

10207第4课时:yonglin08小结

数据透视表函数综合应用

班级:中级班(10207班)课时:四

讲师:JSSY 助教:lrlxxqxa、zhangjimfu

培训开始时间:2010-12-22培训结束时间:2010-12-28

数据透视表函数的语法一

GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,…field14,item14)

Data_field:为包含要检索的数据的数据字段的名称,用引号引起。也可以用T函数进行转换,转换之后则是一个加有引号的文本。

Pivot_table:在数据透视表中对任何单元格、单元格区域或定义的单元格区域的引用。该信息用于决定哪个数据透视表包含要检索的数据。

Field1,Item1,Field2,Item2:为1到14对用于描述检索数据的字段名和项名称,可以任何次序排列。字段名和项名称(而不是日期和数字)用引号引起来。

此语法适用于默认汇总方式,应用方法见实例2、实例3、实例4。

数据透视表函数的语法二

GETPIVOTDATA(pivot_table,data_field&""&item1&""&item2,…&""&item14)

data_field&""&item1&""&item2,…&""&item14是中间用空格连接成的完整的文本。此语法的data_field可以引用多个单元格,可不用OFFSET进行引用,从而达到精简函数目的。

此语法适用于默认汇总方式,应用方法见实例5。

数据透视表函数的语法三

GETPIVOTDATA(pivot_table,"field1[item1;汇总方式]data_field")

此语法适用于自定义汇总方式,应用方法见实例6。

快速获取getpivotdata函数的方法:

在目标单元格输入等号,然后单击透视表中的任意单元格,如果没有自动生成getpivotdata函数,说明该函数功能没有打开,需要在透视表工具单击向下三角》删除和添加按钮》数据透视表》勾选“生成getpivotdata”。这样在工具栏添加了一个新的按钮,这个按钮相当于一个开关键,将新加的按钮按下去即可自动生成getpivotdata函数。如果想取消,再点击一次按钮即可。

一、认识数据透视表函数

实例1:

1、销售总量:=GETPIVOTDATA("求和项:数量",$A$1)

2、海口分公司销售金额:=GETPIVOTDATA("求和项:金额",$A$1,"分公司","海口分公司")

3、南京分公司2008年10月2日C产品的销售量:=GETPIVOTDATA("求和项:数量",$A$1,"品种","C产品","分公司","南京分公司","日期",DATE(2008,10,2))

注意:在使用日期字段里的字段项的时候最好用DATE函数返回日期的结果,以避免日期格式的变化出现查找出错的情况。

4、A产品销售总金额:=GETPIVOTDATA("求和项:金额",$A$1,"品种","A产品")

注意:数据透视表函数只能查找数据透视表里面已经反应出来的数据,没有的数据将会出现错误值。如上例,因为在数据透视表中没有A产品销售金额汇总的值。所以出现错误的值。

另处,除了利用快速引用的方法,还可以修改函数公式的参数。因为在公式里的参数都是具体的文本值,可以通过引用单元格的数据使公式更加灵活。

二、动态获取数据透视表的数据

实例2:(数据透视表函数语法1应用)

原公式:E3=GETPIVOTDATA("求和项:金额",$A$1,"品种","A产品","分公司","海口分公司")/GETPIVOTDATA("求和项:数量",$A$1,"品种","A产品","分公司","海口分公司")

修改的公式: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("々",$A$3:A3))

这样公式即可向下拖动,由于公式向下拖动时,行字段只有第一个数据项有值。比如海口分公司只有A3有数据,A4:A6为空值,所以当公式向下拖动时会出现错误。这里就用LOOKUP对其进行处理,LOOKUP("々",$A$3: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$3:$A$34)),

GETPIVOTDATA(T($D$2),透视表!$A$1,$A$2,透视表!$A$3:$A$34))

1、销售量最大的分公司:=LOOKUP(2,1/(MAX(aa)=aa),$A$3:$A$34)

2、销售量最小的分公司:=LOOKUP(2,1/(MIN(aa)=aa),$A$3:$A$34)

3、销售量最大的分公司C产品的销售金额:=GETPIVOTDATA(T($E$2),$A$1,$B$2,"C产品",$A$2,LOOKUP(2,1/(MAX(aa)=aa),$A$3:$A$34))

4、销售量最大的分公司2008年10月1日A产品的销售价格:=GETPIVOTDATA(T($E$2),$A$1,$B$2,"A产

品",$A$2,LOOKUP(2,1/(MAX(aa)=aa),$A$3:$A$34),$C$2,DATE(2008,10,1))/GETPIVOTDATA(T($D$2),

$A$1,$B$2,"A产品",$A$2,LOOKUP(2,1/(MAX(aa)=aa),$A$3:$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("数量",$A$1,"品种","A产品","分公司",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日"))

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)&""&"A产品"&""&TEXT(C11,"yyyy年mm月d日"))^{-1,1})}

五、获取自定义分类汇总结果

实例6、(数据透视表函数语法3应用)


根据数据透视表生成汇总表:B4=GETPIVOTDATA(透视表!$A$1,透视表!$A$2&"["&$A4&";计数]"&B$3)

公式向下和向右拖动即可生成汇总表。

六、量身定做自己的数据表

实例7、(数据透视表函数综合应用)

可以利用透视表函数计算速度快的特点创建自定义的数据报表,对数据透视表数据比较多,数据量大的数据透视表进行摘录时,就可用数据透视表函数来完成。

本节课主要内容小结:

一、数据透视表函数三种语法结构

1、默认汇总方式下的二种。

2、自定义汇总方法下的一种。

二、数据透视表函数各参数可以是:常量、单元格引用(单一或多个单元格)、公式、数组(常量、公式结果)等。

三、数据透视表函数应用范围

1、创建自定义数据表。

2、对数据透视表数据进行摘录。

很快紧张的一个月的学习时间过去了,回想一下,学到的东西真还不少,但是我感觉还有些问题自已掌握的还不是很牢固,还需要在课后认真的复习和应用,才能够真正的学为已用,学有所用。其实做好笔记也是为了以后复习方便。另外,这已经是最后一课的小节了,在这里我要向所有的同学说声再见,希望大家在今后的学习中取得更优异的成绩,希望大家的Excel水平更上一层台阶。

童鞋们:下个培训班再见!

Excel Home论坛:http://club.excelhome.net/

Excel Home免费在线培训中心:http://t.excelhome.net

学员:yonglin08

  

爱华网本文地址 » http://www.413yy.cn/a/25101014/194826.html

更多阅读

数据透视表怎么用 精 2010数据透视表怎么用

数据透视表怎么用 精——简介数据透视表是microsoft excel表格提供的又一便捷的数据分析工具。能够较快的将所需数据呈现在表格或者图形中。来看看具体是如何操作的吧。 数据透视表怎么用 精——工具/原料Microsoft Excel 2003数

EXCEL透视表怎么做 精 excel表格怎么透视

EXCEL透视表怎么做 精——简介数据透视表在实际工作中,应用得非常广泛,对于统计工作的人员来说,那是非常熟悉了,而对于初涉职场的人员来说,终究还是有些陌生,下面,介绍数据透视表的步骤及注意事项EXCEL透视表怎么做 精——方法/步骤EXCEL

Excel2007-如何做数据透视表 excel2007数据透视图

相信各位一定会面临如何对电子表格内的数据进行分类汇总,借住Excel内置的函数,或者有很多种处理方法,但是效率最好的方法只有一种,那就是对数据进行透视分析,也就是制作数据透视表,以下是需要进行透视的表格,需要汇总各个品种商品的销售额:

Excel中数据透视表的使用 excel中的透视表怎么用

Excel中数据透视表的使用——简介Excel中数据透视表的使用 现在Excel在办公软件中已经很实用,得到了多数人的认可,里面非常强大的功能总能够给我们的工作带来方面。就像Excel中的公式、函数非常强大,可以帮助我们完成很多数据整理工作

Excel数据透视表怎么做 excel数据透视图怎么做

Excel数据透视表怎么做——简介Excel数据透视表是数据汇总、优化数据显示和数据处理的强大工具。本例向大家介绍如何在excel中创建数据透视表并对数据进行统计和分析。Excel数据透视表怎么做——工具/原料ExcelExcel数据透视表怎

声明:《ExcelHome数据透视表中级班_yonglin 日语中级班》为网友给朕跪下分享!如侵犯到您的合法权益请联系我们删除