一 : ExcelHome数据透视表初级班10118课时四小结

ExcelHome数据透视表10118初级班课时四小结

讲师：taller

助教：hustnzj、longwj81

学员：xiaomagayf

时间：2012-06-27至2012-07-03

培训内容：刷新数据透视表

论坛地址：http://club.excelhome.net

培训地址：http://t.excelhome.net/forum.php

随着第四课时的小结批改，我们的10118培训班成员也即将分散，在本班中学习到不少的知识，在以后的时间里也将会继续报班学习！希望大家也可以一直坚持下去！下面咱们先回顾一下本课时的学习内容。

如何刷新数据透视表

一、为什么要刷新数据透视表

Ø数据源经常变化

Ø数据透视表需要呈现最新数据

二、如何刷新？

Ø手工刷新

ü右键菜单

ü数据透视表工具栏

PS：额外收获同一工作薄不同工作表间窗口比较：窗口，新建窗口；窗口，与…..并排比较

Ø自动刷新

ü设置数据透视表属性

ü定时刷新及利用外部数据源创建数据透视表

1、外部数据导入方法：

数据，导入外部数据，导入数据：

选择外部数据源，打开：

选择表格：

导入数据，选择创建数据透视表：

创建数据透视表：

2、 右键，表格选项，勾选刷新频率进行设置

⑶使用VBA代码实现自动刷新

要求设置宏的安全性为中级，否则将为出现警告。具体设置是：宏－安全性－“中”

使用代码刷新有几种方法：打开工作表时刷新，点击命令按钮可以进行批量刷新。

a、打开工作表或工作薄时刷新

一是打开工作表时刷新：点击工作表→查看代码，会出现宏的编辑窗口，点所在的工作表，acvite中输入ActiveSheet.PivotTables("数据透视表1").PivotCache.Refresh，表示当工作工作时将激活事件，执行代码

即：Private SubWorksheet_Activate()

ActiveSheet.PivotTables("数据透视表1").PivotCache.Refresh

End Sub

注意这个数据透视表1是指要刷新的数据透视表的名称，如果不知道当前数据透视表的名称时可以将鼠标定在数据透视表区域，点表格选项，就可以看到数据透视表名称是什么了。

二是不勾选“打开时刷新”的前提下，实现打开工作薄时透视表的自动更新，具体的代码如下（不过这是对所有数据表的刷新，而不是1个数据表的刷新）

Private SubWorkbook_Open()

RefreshAll

End Sub

b、多个数据透视表的同时刷新

一是使用命令按钮可以批量刷新数据透视表。宏代码为：This　workbook.refresh。方法：建立1个矩形框，选中矩形框，右键单击→指定宏，会出现1个指定宏的框，

弹出对话框选择新建或录制，如果选择新建;如果选择录制，会出现宏录制对话框，然后录制全部刷新命令的手动操作的宏就可以，全部刷新手动操作方法如下：

添加后的工具栏：

弹出对话框选择新建或录制，如果选择新建，在End Sub前加上如下代码：ThisWorkbook.RefreshAll

：矩形1_单击的代码为

Sub矩形1_单击()

' 矩形2_单击Macro

' 宏由 1 录制，时间:2012-6-29”

End Sub

里面是空的，将This　workbook.refresh插入其中

即：Sub矩形1_单击()

This　workbook.refre(www.61k.com]sh

End Sub

“矩形2_单击 Macro　宏由 1录制，时间: 2012-6-29”是可以删除的，只是1个说明。

上述代码是刷新所有数据透视表，下列代码则是有选择的刷新数据透视表：

Sub矩形1_单击()

Sheet2.PivotTables("数据透视表2").PivotCache.Refresh

Sheet4.PivotTables("数据透视表5").PivotCache.Refresh

End Sub

三、数据透视表数据缓存

Ø数据透视表的数据缓存是计算机内存的1个指定区域用于存储报表数据

Ø共享数据缓存

ü基于同一单元格区域数据连接的2个或多个数据透视表之间自动共享缓存

ü可以实现同时刷新

ü优势：提高性能的同时减小了工作薄的大小

Ø下列情况不能共享数据缓存

ü不希望所有数据透视表中都显示计算字段和计算项（祥见后面插入计算字段）

ü不希望在所有数据透视表中都按同一方式分组字段

ü不希望同时刷新所有数据透视表：数据源更改后，非共享的各自刷新，互不影响。而共享的，1个透视表刷新，其他的也同时刷新了。

Ø共享缓存与非共享缓存：

用数据透视表向导创建数据透视表时，如果同一区域已建立数据透视表会出现提示框，

选是共享缓存

选否非共享缓存

插入计算字段的方法：

名称：销售提成，公式=销售收入*0.005，（可以双击字段名将需参加计算的字段加到公式里面）

共享缓存的数据透视表中的数据透视表字段列表中也存在计算字段销售提成，而非共享数据透视表中则没有，所以如果不想共享插入字段，应该不共享数据缓存。

三、刷新数据表的注意事项

1、海量数据将导致数据刷新非常慢；

2、如何清除“垃圾条目”。

数据透视表中如何数据作了修改，按刷新后表内数据是新的，但是下拉按钮仍有旧的相关数据，只是增加了修改后的数据，谓之“垃圾条目”。清除的方法是右键－字段列表，显示字段列表，点透视表按刷新，将修改过的字段拖回字段列表，然后再刷新透视表，再将该字段拖至透视表内，记住，字段拖回字段列表（即删除该字段）后一定要刷新透视表才能将该字段增加到透视表内。

3、数据源中新增的“行”和“列”无法更新到数据透视表中。因为数据源的区域没有改变，所以新增的“行”和“列”无法刷新到数据透视表中，需要创建动态数据透视表，这将是下一阶段的内容了。

二 : 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函数进行转换，转换之后则是1个加有引号的文本。

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”。这样在工具栏添加了1个新的按钮，这个按钮相当于1个开关键，将新加的按钮按下去就可以自动生成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))

这样公式就可以向下拖动，由于公式向下拖动时，行字段只有第1个数据项有值。比如海口分公司只有A3有数据，A4：A6为空值，所以当公式向下拖动时会出现错误。这里就用LOOKUP对其进行处理，LOOKUP("々",$A$3:A3)即是在$A$3:A3这个动态的区域里面查找"々"（这个字符在字码表里大于所有的汉字，输入方法：Alt+41385），因为当LOOKUP查找的值不存在时，会返回1个小于查找值的最大值，即用A3的数据对A4：A6进行填充。

在进行引用时要注意，引用的第1个字段要用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）2个单元格，所以用OFFSET(D2,,{0,1})来进行处理，然后再将2个数相除。

6、所有分公司A产品的销量：=SUM(GETPIVOTDATA("数量",$A$1,"品种","A产品","分公司",T(OFFSET(A2,{1,13,23},))))

公式中OFFSET(A2,{1,13,23},)即是引用3个分类汇总的行。

实例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参数可以引用2个或多个单元格。

比如：销售量最大的分公司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、（数据透视表函数综合应用）

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

本节课主要内容小结：

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

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

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

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

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

1、创建自定义数据表。

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

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

童鞋们：下个培训班再见！

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

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

学员：yonglin08

本文标题：数据透视表excelhome-ExcelHome数据透视表初级班10118课时四小结
本文地址： http://www.61k.com/1212289.html