数据透视表教程6

《数据透视表教程6》由会员分享，可在线阅读，更多相关《数据透视表教程6（17页珍藏版）》请在装配图网上搜索。

1、数据透视表函数综合应用数据透视表函数的语法一培法nGETPIVOTDATA (上； ,pivot table ,，,field,航m14 VY、y命令字 查什么 在卵查怎么查(查技条件)GETPIVOTD ATATR $), $ A$ 牝$ C S 2, $C S4 S A$ 2 .$ A$ 11上已 S$ B$6)GETPIVOTDATAf.明钏,”品种,丫产品二分公司L南京分公司,日期,DATE住38.10,2)GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2, field14,item14)Data_field：为

2、包含要检索的数据的数据字段的名称，用引号引起。也可以用T函数进 行转换，转换之后则是一个加有引号的文本。Pivot_table :在数据透视表中对任何单元格、单元格区域或定义的单元格区域的引用。 该信息用于决定哪个数据透视表包含要检索的数据。Field1,Item1,Field2,Item2：为1到14对用于描述检索数据的字段名和项名称，可以 任何次序排列。字段名和项名称(而不是日期和数字)用引号引起来。此语法适用于默认汇总方式，应用方法见实例2、实例3、实例4。数据透视表函数的语法二GETPI VOID ATA （ pi/otjabie ,fi- & +,MSitem2，& &t8EldY7

3、/命令字 在哪查怎么查（查找条件）GEIFlVOTDATA（SA I. iJ 】&“ &SH$19&” ”&$HS20&” &TEXT（$HS21YYYY年mm月。日”）GETPIVOTDATA（$A L，,南京分公司B产品2D0W年1。月2日）GETPIVOTDATA(pivot_table,data_field& &item1& &item2, & &item14)data_field& &item1& &item2, & &item14是中间用空格连接成的完整的文本。 此语法的data_field可以引用多个单元格，可不用OFFSET进行引用，从而达到精简函数目 的。此语法适用于默认汇总

4、方式，应用方法见实例5。数据透视表函数的语法三语法3 =GETPIVOTDATA （f暗Id1item1;汇总方式“V7v命令字 在哪查 怎么查（查找条件）查什么GETPIV0TDATA（MMSA$2&T&$A$3&“：W教&）GETPIVOTDATA（$At1.成绩60巧9;计数i ）GETPIVOTDATA(pivot_table,field1item1;汇总方式data_field)此语法适用于自定义汇总方式，应用方法见实例6。快速获取getpivotdata函数的方法：在目标单元格输入等号，然后单击透视表中的任意单元格，如果没有自动生成 getpivotdata函数，说明该函数功能没有

5、打开，需要在透视表工具单击向下三角删除和添 加按钮数据透视表勾选“生成getpivotdata”。这样在工具栏添加了一个新的按钮，这 个按钮相当于一个开关键，将新加的按钮按下去即可自动生成getpivotdata函数。如果想取 消，再点击一次按钮即可。透视表函数ge tpi vo tda 的特点:r r 曷于查找引用法数.舂我&用的对隼是肴定的.即致据斐视表对膏及本身不了髀。莓赛基于精定的对象。被致诿法看上去比较复亲。使用时不毒赛考虑数据圳用的迎界。基于数据既穗表本身计算快速.的特点， 昱豹薮在计算速度方面夺明戛的曲如。实例1:文件 躺苗密视图摘人-格宜工具挝）数据如街口册裕助 J 览 W灯西

6、勘盘宋件rK27-在ABCDEF 1弓L H11数据.1-2分公司1日舶 神；求利项:金额装茄费薮置3 海口益公哥2U0B-10-1 E产吕弱前60Qh销匪金互嚣辰而品品 产产 CD S2260S59763600900L海口分公司辅售金额57, 360. 0062O0S-I0-2 AT 品4如59003、南京时司的08年m月.ann nn7E产品593100溟艾肮的销售量 明上功SC产品1507224004寿品带隹吝全辄,涂珏19D产品464E70010海口分公司汇总573609200H 南京分公胃 200S-10-1 AT品14170200012B产品29655G013C产品175842即。

7、K2003-10-2 品13G25250015E产命177930016C产品113041800H南京命会司表总014271050018上海分公司 2G0&-10-1 AT品2616048G0,1.9_C产品51496820020D产甜如如1QGQ21如丽TO艺航品147152700就.C产品27004430023D产品33205Q024 :上海分公司汇总W笋s21500曲总计24812241200261、销售总量:=GETPIVOTDATA（ 求和项:数量，$A$1）2、海口分公司销售金额：=GETPIVOTDATA（ 求和项:金额，$A$1,分公司，海口分公司）3、南京分公司2008年10月

8、2日C产品的销售量：=GETPIVOTDATA（求和项:数量，$A$1, 品种，气产品,分公司,南京分公司,日期,DATE（2008,10,2）注意：在使用日期字段里的字段项的时候最好用DATE函数返回日期的结果，以避免日期 格式的变化出现查找出错的情况。4、A产品销售总金额：=GETPIVOTDATA（求和项:金额,$A$1, 品种,A产品）注意：数据透视表函数只能查找数据透视表里面已经反应出来的数据，没有的数据将会 出现错误值。如上例，因为在数据透视表中没有A产品销售金额汇总的值。所以出现错误的 值。另处，除了利用快速引用的方法，还可以修改函数公式的参数。因为在公式里的参数都 是具体的文本

9、值，可以通过引用单元格的数据使公式更加灵活。二、动态获取数据透视表的数据实例2：（数据透视表函数语法1应用）借院！文件 编辑视圈世）插友 格式国 工其堡.）数据更丁窗口也）帮助但）心7 J ,览K J灯1幽象宋体1123.AABCDEFH1数据 T2分司品种T求和项:数重求和项知金额3海口司妒品900.004, 905. 005.45 I4C产品6, 000. 0037,680.00互宓5D产品1, 600. 0010, 624. 006. 646B产品700.004, 151. 005. 937南斥分公司妒品5, 100. 0027, 795. 005.458C产品4 600. 00-28,

10、 888. 00 6. 289E产品800.004744. 005. 9310上海分公司妒品2, 700. 0014, 715. 005.4511C产品12, 500. 0078, 500. 006.-2812D产品1, 500. 00% 960.006. 6413E产品4, 800. 0026, 160. 005.4514总计41200. 00248, 122. 001-二 r.-3原公式：E3=GETPIVOTDATA（求和项:金额，$A$1,品种，A产品，分公司，海口分公司 ）/GETPIVOTDATA（求和项:数量，$A$1,品种，A产品，分公司，海口分公司）修改的公式：E3二GETP

11、IVOTDATA（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查找的值不存在 时，会返回一个小

12、于查找值的最大值，即用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

13、,$B3)/GETPIVOTDATA(T($C$2),$A$1,$A$2,g_name)定义名称的用途是填充行字段“分公司”下为空值的项。另外，因为此透视表有汇总行，所以要用SUBSTITUTE将“汇总”替换为空文本。四、在透视表函数中运用内存数组实例4、（数据透视表函数语法1应用）妒姑I拓疝年LQR之百叫汇参E 产晶2008101 J 20031020海蜒 至的而。一如竟的.3aaikfia60D. 005, 55E. COi 00.00.593.00南巨分会司SOQSLOtriE四明年LU月W日5FqpTzoDBtofl iaIw。明年LtlHW日TODiOg _ 1, 15 I., co

14、 3,503.0022,608.00.如如玖加一 JiMtJia.明瓦JO J. ；0址K.典SOD. DO 5,妲虱 00 70C.OO 4.6dft.00L, fiu Jr .Uj 妃以hd3.二。司b7.：.：02,600. CO 4,17-?r)o 2.600.0013.625. CO一也守史jfe. e=TF (I ?NU1!EER (CETPI TO TDATA (T ($D$2),诿视疵 i?tn $&町透祝 表! SAE3:$M34) L GETPIVDTDArA(T (SDS2),逐机表! SAE1, U$2,逶视袈I W3成城的。8年L口月I巳汇忌Ig.崩鼠 B产品迎。8年

15、10月IR _叩的年L。月！?日 汇怠网朋坪LQ月iE 切明浣LU月汨 toDsBToKLE _器理旬且沮实。稗m月I日网愤隼10月1日W能年LQ月。日% I ILL 司WLN5一侦&见 CO2,365,四如 口-9口1.9-。口祯队加 土 T44-。2J EOC. 0017,S2)f 透褪虱：?AFtn U52,透双 表！玷$% W 典)h GE TPIVOrDALA (USD 址),通视夜 1AE L fAS2 透整游司E分显W20Q-lQ-22l.50C.0S 工心叫血bUQOrOO6, MU, do.明艮啤一.土.S加,.皿.一1.600.009.就爪 DD查寻结果：南京分公司B产品2

16、008年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 月

17、d 日 )/1,1)五、获取自定义分类汇总结果实例6、（数据透视表函数语法3应用）ABCDEFG1求和项:成赣学科-2成绩 1-姓名 ，语文-数学 -英语FML-360-89陈萍604陈萍梅605陈小燕606李忠开687S98林志恒699606510刘小建606511徐伟6512徐伟南6513;6014赳小志6015卓明理631660-69 i1数1051770-79陈萍7518陈萍梅7519陈天星74320邓 珠-7521何守江77/22黄小媚布切rt79rr1lLi 024林幌而25林志75林志恒T527刘小702S刘小建7029欧小生.7630苏家明701顶覃唐E71nnJ A MH 4

18、* H K汇总表证直视表/数据源/根据数据透视表生成汇总表：B4=GETPIVOTDATA（透视表!$A$1,透视表!$A$2&$A4&；计数&B$3）公式向下和向右拖动即可生成汇总表。六、量身定做自己的数据表实例7、（数据透视表函数综合应用）可以利用透视表函数计算速度快的特点创建自定义的数据报表，对数据透视表数据比较 多，数据量大的数据透视表进行摘录时，就可用数据透视表函数来完成。本节课主要内容小结:一、数据透视表函数三种语法结构1、默认汇总方式下的二种。2、自定义汇总方法下的一种。二、数据透视表函数各参数可以是：常量、单元格引用（单一或多个单元格）、公式、数组（常量、公式结果）等。三、数据透视表函数应用范围1、创建自定义数据表。2、对数据透视表数据进行摘录。