数据透视表教程6

数据透视表函数综合应用数据透视表函数的语法一培法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：为包含要检索的数据的数据字段的名称，用引号引起。也可以用T函数进 行转换，转换之后则是一个加有引号的文本。Pivot_table :在数据透视表中对任何单元格、单元格区域或定义的单元格区域的引用。 该信息用于决定哪个数据透视表包含要检索的数据。Field1,Item1,Field2,Item2：为1到14对用于描述检索数据的字段名和项名称，可以 任何次序排列。字段名和项名称(而不是日期和数字)用引号引起来。此语法适用于默认汇总方式，应用方法见实例2、实例3、实例4。数据透视表函数的语法二GETPI VOID ATA （ pi'/otjabie ,fi- & +,MSitem2，& ' "&t8Eld'Y7"/命令字 在哪查怎么查（查找条件）GEIFlVOTDATA（SA I. iJ 】&“ "&SH$19&” ”&$HS20&” "&TEXT（$HS21'YYYY年mm月。日”）GETPIVOTDATA（$A L，,南京分公司B产品2D0W年1。月2日"）GETPIVOTDATA(pivot_table,data_field& &item1& "&item2, & &item14)data_field& "&item1&" "&item2, & "&item14是中间用空格连接成的完整的文本。 此语法的data_field可以引用多个单元格，可不用OFFSET进行引用，从而达到精简函数目 的。此语法适用于默认汇总方式，应用方法见实例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函数，说明该函数功能没有打开，需要在透视表工具单击向下三角删除和添 加按钮数据透视表勾选“生成getpivotdata”。这样在工具栏添加了一个新的按钮，这 个按钮相当于一个开关键，将新加的按钮按下去即可自动生成getpivotdata函数。如果想取 消，再点击一次按钮即可。透视表函数ge tpi vo tda 的特点:r r 曷于查找引用法数.舂我&用的对隼是肴定的.即致据斐视表对膏及本身不了髀。莓赛基于精定的对象。被致诿法看上去比较复亲。使用时不毒赛考虑数据圳用的迎界。基于数据既穗表本身计算快速.的特点， 昱豹薮在计算速度方面夺明戛的曲如。实例1:文件® 躺苗密视图摘人-格宜工具挝）数据如街口册裕助 J 览£ W灯西勘盘宋件r°K27-在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即。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月2日C产品的销售量：=GETPIVOTDATA（求和项:数量，$A$1, 品种，气产品,分公司,南京分公司,日期,DATE（2008,10,2）注意：在使用日期字段里的字段项的时候最好用DATE函数返回日期的结果，以避免日期 格式的变化出现查找出错的情况。4、A产品销售总金额：=GETPIVOTDATA（"求和项:金额",$A$1, 品种","A产品）注意：数据透视表函数只能查找数据透视表里面已经反应出来的数据，没有的数据将会 出现错误值。如上例，因为在数据透视表中没有A产品销售金额汇总的值。所以出现错误的 值。另处，除了利用快速引用的方法，还可以修改函数公式的参数。因为在公式里的参数都 是具体的文本值，可以通过引用单元格的数据使公式更加灵活。二、动态获取数据透视表的数据实例2：（数据透视表函数语法1应用）借院！文件 编辑视圈世）插友 格式国 工其堡.）数据更丁窗口也）帮助但）心7 J ,览K "J灯1幽象宋体1123.AABCDEF©H1数据 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, 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二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应用）妒姑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 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年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,£S<l.(301,知0 一珈】L3Qd 一肋-1, ；0'：.：C2?, So-, njQ, 50 J.OQ6 L. 427. 004, E03.3026f 1B0.0D告N"加 E？】民QD 7, 503. 30 I 3, =：Q0_Bf20D.D0 51,495.00.一虹2?.。口如.iNStM.OO建.也。.如，(300.006, &10.00500-CO3-320.00i 州土迥_g.moo p i _ sri!L rii i wfetn 主 n 立 w 直整I丘听有上S量把§节站灵13兀L反歌碧知朦叨°L矽气.七定义名称：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,)即是引用三个分类汇总的行。只如吉甲堀月？ 口：皿T： ：. ：5源20031015 侦南。有£aowioMiB2GQ3Ml(lHia20081153 罚网晕】g冒芝弓WD8年捕月1日 勰译】明扣w诚m明国 叩归学英月？习配。.呢LQ6里一 MSGlLC： 5。啊如如口一口击位如土如9E；：:. U.S：初4,淖S&D. 00% 558. 00ItJtLM 5 汩.叩W- 去,LC-. DD海口命占忌吕曲.皿 4. T4L血?.800.0& LL&B4 一如祯应oq.叩引1心七。口%&gg?e, i co. aoATQStJO14：丑土 口口代 500. GQ40. #花.如22, 608. 0015性一 SQL4, 17(1. fJD.L3；说虽.血方.凹5 一叩2, 965. 00L 亶9- QQ5：. 496. 00"1域旭2口皿羊用月1寻2W8甲OHF日萨品汇,3,600.00土罚口一况90 D. 00 一仲吨 I.2,00.00 ?. 60 D, 0=7 %、莆良M5C0.0Q更口-g琦 2SB. 004.如 i.kOt5, 976. 00 5&皿甲1G目1日 典I形年：.月日曰汇| 2008年10月1日I.知的年J廿月用9h5Etfi.no tiatttliitAit整平呻皿扩邕F_QH.I一.字琮.*-IF (ISNUlffiE艮(GEIPITOTDATA(T($D f>2)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产品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应用）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刘小70'2S刘小建7029欧小生.7630苏家明701顶覃唐E71nnJ A MH 4* H K汇总表证直视表/数据源/根据数据透视表生成汇总表：B4=GETPIVOTDATA（透视表!$A$1,透视表!$A$2&&$A4&；计数&B$3）公式向下和向右拖动即可生成汇总表。六、量身定做自己的数据表实例7、（数据透视表函数综合应用）可以利用透视表函数计算速度快的特点创建自定义的数据报表，对数据透视表数据比较 多，数据量大的数据透视表进行摘录时，就可用数据透视表函数来完成。本节课主要内容小结:一、数据透视表函数三种语法结构1、默认汇总方式下的二种。2、自定义汇总方法下的一种。二、数据透视表函数各参数可以是：常量、单元格引用（单一或多个单元格）、公式、数组（常量、公式结果）等。三、数据透视表函数应用范围1、创建自定义数据表。2、对数据透视表数据进行摘录。