每天进步一点点并不太难

lrlxxqxa

下面的话是网友菜虫对20楼的思考，十分值得借鉴！
    最近很忙，来得少了。这里真是高手云集。对数据透视表的应用这个例子反映了一定的深度，可贵的是楼主又给出了详细的动画图解。仔细看过以后，我的体会是：数据透视表的根本优势在于对数据表的横向和竖向（记录和字段）的归类交叉计算，类似于数据库中的交叉查询，又比交叉查询灵活得多。在这个例子中似乎只用到了把具有相同结构的表对应单元格的数据叠加的功能。从最终的计算结果看，我们可以采取另外一种思路得到相同的结果。
    我用原来的资料把所有的表转到同一个工作簿，然后采用三维数据处理的概念很容易得到了相同的结果。下面有几点说明：
1、我给出的这个例子并不是说原来的例子不精彩，而是想通过不同的路径得到相同的结果来加深对数据透视表的理解。 2、我们还可以通过合并计算的方法、对不同工作簿用公式叠加的方法得到相同的结果。
3、也就是说针对这个例子，用EXCEL我们至少可以有4种方法得到相同的结果。在实际工作中，每一种方法各有利弊。在数据打印方面数据透视表的结果灵活性较弱。
4、通过至少四种路径得到相同的结果，更说明了EXCEL的功能强大和灵活性。

[ 本帖最后由 lrlxxqxa 于 2010-11-26 22:25 编辑 ]

lrlxxqxa

数据透视表一次学会

练习 1：按季度组合订单日期1.        在“订单日期”字段的单元格 A7 中单击鼠标右键。注意，本步骤中不能单击报告中的任何单元格，必须单击包含在“订单日期”字段中的某个单元格。例如，不能单击列 C 的分类汇总，或者分类汇总的标题，例如 A6，否则就不能执行步骤 2。
2.        在快捷键菜单上指向“组及显示明细数据”，单击“组合”。
3.        在“组合”对话框中，单击“季度”。如果同时选择了“月”，请单击取消选择它，然后单击“确定”。
现在销售额数据在左边按季度分开，每位销售人员的姓名列在每季度的右边。
在内部字段中的项可以按需重复，但外部字段的项只能显示一次。在本报告中，“销售人员”是内部字段，所以所有销售人员的姓名列在每个季度的旁边。“订单日期”是外部字段，所以一次只列出一个季度。

练习 2：重命名订单日期字段
要使标题与新的组合匹配，需要重命名订单日期字段。
操作步骤    选择“订单日期”字段。键入新名字“季度”，然后按 Enter。

练习 3：交换内部字段、外部字段位置以获得不同的视图
在本次练习中，您将透视报告，以便按季度比较每个人的业绩。
操作步骤    将“季度”字段拖到“销售人员”字段的右边。记住，该字段在报告中是灰色框，包含“季度”。在列 B 与列 C 的中间看到灰色框时释放鼠标键。
透视报告，转换内部与外部字段的位置。在本报告中，“季度”是内部字段，依次列出了所有四个季度。现在“销售人员”是外部字段，所以销售人员的姓名只列出了一次。

[ 本帖最后由 lrlxxqxa 于 2010-11-26 22:25 编辑 ]

lrlxxqxa

http://club.excelhome.net/viewthread.php?tid=108136&extra=page%3D1

1、要确定每位销售人员每季度的销售数，方法是将“订单金额”字段再次拖动到“数据”拖放区，然后选择不同的汇总函数显示每位销售人员每季度的销售金额和订单数。

2、以总和的百分比显示数值数据
在“汇总方式”列表中，单击“求和”将汇总方式由“计数”更改为“求和”。我们希望显示总计的百分比，而不是计数。
单击“选项”按钮。
在“数据显示方式”列表中，单击箭头，向下滚动列表，然后单击“占总和的百分比”。单击“确定”。
订单数现在以订单总数的百分比显示。

[ 本帖最后由 lrlxxqxa 于 2010-11-26 22:25 编辑 ]

lrlxxqxa

[数据透视表] 如何设置允许自动生成GETPIVOTDATA公式？ http://www.exceltip.net/thread-1207-1-1-25594.html

练习 1：确定奖金百分比
销售人员每季度会得到 7% 的奖金，但今年每个销售额超过 ￥25,000 的销售人员会在任何季度获得 10% 的奖金。在本次练习中您将使用计算字段输入公式。这里是公式：
=IF('订单金额’>25000,10%,7%)

单击报告内部。然后在“数据透视表”工具栏上单击“数据透视表”，指向“公式”，然后单击“计算字段”。
（如果菜单上看不到“公式”，请单击底部的展开按钮将菜单展开。)

在“名称”框中输入公式名。例如，输入“奖金 %”。
删除“公式”框中的文字。
在框中，输入=IF(。
在“字段”列表中，单击“订单金额”，然后单击“插入字段”。
注意   尽管“订单金额”字段重新命名为“订单 ￥”，但该字段名在“插入计算字段”对话框中仍为“订单金额”。

接着输入公式的其他部分：>25000,10%,7%)，然后单击“确定”。
名为“奖金 %”的新字段将插入报告中。

结果看上去不像百分比，不是吗？带有美圆符号，而不是百分号。接着，将奖金百分比格式化。

操作步骤   单击“奖金 %”字段内部。例如，单击单元格 D6。单击“数据透视表”工具栏上的“字段设置”按钮 。然后单击“数字”。在“分类”列表中单击“百分比”，并在“小数点”位数列表中单击向下箭头到达“0”（消除小数位数）。单击“确定”两次。

练习 2：关闭自动分类汇总
Buchanan 四季度的奖金百分比是 10%，虽然她没有每个季度都达到 10%。

Excel 将列 C 中的 Buchanan 的四季度销售额累加起来，因为自动分类汇总打开了。又由于总和大于 ￥25,000，所以计算字段公式在带有分类汇总的行上输入 10%。为了避免混淆，应该关闭分类汇总。

单击“销售人员”字段中的任何地方，因为您希望关闭该字段的分类汇总。例如，单击单元格 A6。
在“数据透视表”工具栏上单击“数据透视表”，然后单击“分类汇总”清除复选标记。（如果在菜单上看不到“分类汇总”，请单击底部的展开按钮以展开菜单。）
这样将关闭分类汇总。

练习 3: 确定奖金额
创建另一个计算字段公式以确定每位销售人员每季度的奖金额。这里是公式：
='订单金额'*'奖金 %'

操作步骤   在“数据透视表”工具栏上，单击“数据透视表”，指向“公式”，然后单击“计算字段”。在“名称”框中输入公式名。例如，键入奖金 ￥。

删除“公式”框中的文本。

在“公式”框中，输入公式：='订单金额'*'奖金 %'

提示   在“字段”列表中，单击“订单金额”，然后单击“插入字段”（或者只是双击“订单金额”）。输入星号 (*) 以便相乘，单击“字段”列表中的“奖金 %”，然后单击“插入字段”。单击“确定”。

名为“奖金 ￥”的新字段将添加到报告中。

最后，将“奖金 ￥”字段中的数字格式化为货币：在该字段中的任何地方单击，再单击“数据透视表”工具栏上的“字段设置”按钮 ，然后单击“数字”，选择货币格式。在“小数位数”列表中，单击向下箭头直到显示“0”（消除小数位数）。然后单击“确定”两次。

练习 4：在数据透视表外部创建公式
本次练习中，您将汇总 Buchanan 的季度奖金额。这里是公式：
=SUM(E5:E8)

选择单元格 F9 并输入“=SUM(”。
单击单元格 E5（Buchanan 的第一季度总和），将插入点拖到单元格 E8 的底部，然后按 Enter。结果是 ￥2,200（如果结果没有自动进行无小数格式化，则结果为 ￥2,200.32）。
注意   此公式很简单，在您使用数据的范围时它很适用。然而，还有另一种方法：GETPIVOTDATA 函数。这是更高级的公式，我们将在下一练习中演示，因为也许您在透视您自己的报告时会遇到这种情况。

练习 5：GETPIVOTDATA 函数
如何设置允许自动生成GETPIVOTDATA公式？ http://www.exceltip.net/thread-1207-1-1-25594.html
在报告外部键入等号然后选择报告内部的单个单元格时，会自动输入 GETPIVOTDATA 函数。您对此可能感到惊讶。本练习中，您将看到它的结果。

操作步骤   选择单元格 D1，键入等号 (=)，然后选择单元格 C5。在工作表中会突然出现看上去复杂的公式。按 Enter 完成公式。

GETPIVOTDATA 将从数据透视表中检索数据。该函数的最大好处是，当报告布局更改时它能继续检索正确的数据。如果在布局更改时某块数据的单元格位置也更改了，函数仍将找到并返回正确的值，不管它在哪个单元格。
GETPIVOTDATA函数有2个参数。
第一个参数是对透视表本身的引用，它可以是透视表中任意单元格、分配给透视表的数据区域名或存储在表格左上角内的单元格的标志。
第二个参数则告知excel所需数据的类型，它包含在双引号中，指定各字段名，这些字段名在表中的交叉点就是要获取的数据。第2个参数中的各字段名之间要用空格隔开。
GETPIVOTDATA函数获取的数据源必须是在透视表中可见的，否则将返回错误值“＃REF！”

默认情况下 GETPIVOTDATA 函数自动打开，但可根据意愿将其关闭，在本课后面的“课程摘要卡”中您可以看到如何进行该操作。

[ 本帖最后由 lrlxxqxa 于 2010-11-26 22:26 编辑 ]

lrlxxqxa

1、用offset函数定义data动态引用数据区域
使用定义名称的方法来创建动态数据透视表，首先要要使用公式定义一个动态的数据源区域，当新的记录添加到数据源的时候，数据源区域会自动扩展。然后把定义的名称范围用于数据透视表，这样创建的透视表也将是动态的。
比如定义一个名称为data

1. =offset(数据源!$a$1,,,counta(数据源!$a$a),counta(数据源!$1$1))

复制代码

ps：“数据源”是动态数据所在工作表的名称。
在创建数据透视表时，数据区域对话框中填data即可使透视表动态更新。

2、使用列表功能
列表功能是excel2003中新增的功能，利用列表对数据源的自动扩展可以创建出动态的数据透视表。
单击数据区域任意单元格，ctrl+L或者单击“数据”－“列表”－“创建列表”根据具体情况勾选是否有标题，单击列表内创建透视表，这样列表创建的动态数据透视表就完成了。
当在数据区域添加记录时，刷新透视表即可看到新增的数据

[ 本帖最后由 lrlxxqxa 于 2010-11-26 22:26 编辑 ]

lrlxxqxa

1、普通刷新；
源数据变化时，单击“刷新数据”即可；

2、打开文件时刷新；
表格选项－－勾选“打开时刷新”复选框

3、刷新链接在一起的透视表；
当透视表用作其他数据透视表的数据源时，对其中任何一张数据透视表刷新，都会引起所有链接在一起的数据透视表进行刷新

4、刷新引用外部数据的数据透视表；
如果创建的透视表是基于对外部数据的的查询，excel可以在用户工作时在后台执行数据查询
表格选项－－外部数据选项－－勾选“后台查询”
（注意：此功能只针对外部数据，否则为灰色）

5、定时刷新
这项也只针对外部数据源创建的透视表。
表格选项－－刷新频率（这里是以分钟为单位的时间间隔）

[ 本帖最后由 lrlxxqxa 于 2010-11-26 22:27 编辑 ]

lrlxxqxa

数据透视表的项目组合

数据透视表能够能够自动在外部字段的标题下对内部字段的项进行组合，并可以按照需要对内部字段进行分类汇总。项目组合是透视表一个非常有用的特性，以下是5种选择：
一、组合数据透视表的组合项
在透视表中，按住ctrl单击内部字段下要组合的数据项，然后单击右键－－组及显示明细数据－－组合，命名。确定完成

二、数字项组合
要将字段中的数字项组合，可在分组对话框中设置。单击该字段任意项，单击右键－－组及显示明细数据－－组合，出现“组合”对话框，根据需要填写“起始于”、“终止于”、“步长”，确定完成。

三、按日期或时间项组合
前面类似上面，在日期字段中任意项单击右键，在“分组”对话框中，保持起始日期和终止日期的默认设置，单击“步长”列表框中的“年”，多选可以按住ctrl再单击“月”。确定完成。

四、取消项目组合如果不需要某个组合，可以选中它点右键－－组及显示明细数据－－取消组合，即可删除组合，恢复组合前的状态。

五、组合数据时遇到的问题当我们进行分组时，可能会弹出错误提示对话框“选定区域不能分组”，这是因为进行分组字段的数据源中包含有空白的单元格或文本型的日期、数字。
解决方式：
在空白单元格填充日期或数字；
如果日期或数字字段存在文本，移除它；
如果有文本型数字，改为常规类型

[ 本帖最后由 lrlxxqxa 于 2010-11-26 22:27 编辑 ]

lrlxxqxa

excel允许用户向透视表中添加计算字段和计算项。

计算字段：通过对表中现有字段执行计算后得到的新字段；

计算项：在已有字段中插入的新的项，是通过对该字段现有的其它项执行计算后得到的。

一旦创建了自定义的计算字段和计算项，excel就允许在透视表中使用她们，就像在数据源中真实存在的一样。

[ 本帖最后由 lrlxxqxa 于 2010-11-26 22:27 编辑 ]

lrlxxqxa

请教get.cell的参数38和63的区别

get.cell函数只用于宏表
返回关于格式化，位置或单元格内容的信息。在由特定单元格状态决定行为的宏中，使用GET.CELL。

语法

GET.CELL(type_num, reference)
Type_num    指明单元格中信息的类型。下表列出 Type_num 的可能值与其对应的结果。

Type_num        返回

1        以当前工作区文字引用类型绝对引用左上角引用单元格。
2        顶部引用单元格的行数。
3        最左边引用单元格的列数。
4        同TYPE引用
5        引用内容。
6        文字，以工作区设置决定的 A1 或 R1  C1 类型引用公式。
7        文字的单元格的数字格式(如“m/d/yy”或“General”)。
8        指示单元格水平对齐的数字
1 = 一般
2 = 左对齐
3 = 置中
4 = 右对齐
5 = 填充
6 = 两端对齐
7 = 跨列置中

9        指示分配给单元格左侧边框线的类型
0 = 无边框
1 = 细线
2 = 中等线
3 = 虚线
4 = 点线
5 = 粗线
6 = 双线
7 = 发丝线
10        指示分配给单元格右侧边框线的类型，对应指定返回值的描述参见 type_num 9
11        指示分配给单元格顶端边框线的类型，对应返回值的描述参见 type_num 9
12         指示分配给单元格底端边框线的类型，对应返回值的描述参见 type_num 9
13         是0至8之间的一个数字，当在［格式］菜单下选择［单元格］命令时，［单元格格式］对话框的［图案］标签显示被选择的单元格。这个数字指明其模式。如模式未被选择，返回0。

14        如果单元格被锁住，返回TRUE；否则，返回FALSE。
15        如果单元格中公式被隐藏，返回TRUE；否则返回FALSE。
16        一个含有两顶的水平数组，包括活动单元格的宽度和一个逻辑值,这个逻辑值指明单元格宽度是否改变，如为TRUE,标准宽度改变，如为FALSE，仍为自定义宽度。
17        用点表示的单元格的行高
18        字体名称，文字形式
19        用点表示的字体大小
20        如果单元格中所有字符或只有第一个字母为黑体，返回TRUE;否则，返回FALSE。

21        如果单元格中所有字符或只有第一个字符为斜体。返回TRUE;否则，返回FALSE。
22        如果单元格中所有字符或只有第一个字符为加底线，返回FALSE。
23        如果单元格中所有字符或只有第一个字符为加删除线,返回TRUE；否则，返回FALSE。
24        是1-56的一个数字，代表单元格中第一个字符的字体颜色。如果字体颜色为自动生成，返回0。
25        如果单元格中所有字符或只有第一个字符为空心体，返回TRUE；否则，返回FALSE。Microsoft Excel for Windows 不支持空心字体格式。

26        如果单元格中所有字符或只有第一个字符加阴影，返回TRUE;否则，返回FALSE。Microsoft Excel for Windows 不支持阴影字体格式
27         指示单元格中能否进行手动分页线的数字。
0 = 没有分页线
1 = 行
2 = 列
3 = 行和列
28        行的级数(分级显示)
29        列的级数(分级显示)
30        如果所包含的活动单元格为摘要行，返回TRUE；否则，返回FALSE。
31        如果列所包含的活动单元格为摘要列，返回TRUE；否则,返回FALSE。.

32        如窗口只包括一个表，其文件名为不含扩展名的工作簿 的文件名，为包含单元格的工作簿或工作表的文件名，以 BOOK1.XLS 的形式返回簿的文件名；否则，以"［BOOK1］sheel"的形式返回表的文件名。?
33        如单元格被折行，返回TRUE；否则，返回FALSE。
34        是1-16之间的一个数，代表左侧边框线颜色。如颜色自动生成，返回零。
35        是1-16之间的一个数，代表右侧边框线颜色。如颜色自动生成，返回零。
36        是1-16之间的一个数，代表顶端边框线颜色。如颜色自动生成，返回零。

37        是1-16之间的一个数，代表底端边框线颜色。如颜色自动生成，返回零。
38        是1-16之间的一个数，代表前景颜色。如颜色自动生成，返回零。
39        是1-16之间的一个数，代表背景颜色。如颜色自动生成，返回零。
40        单元格风格,文字形式。
41        不经翻译返回活动单元格中的公式(用于图别设定的宏表)。
42        用点表示的活动窗口左边界到单元格左边界的水平距离,如果窗口滚动到单元格外，可能为负值。

43        用点表示的活动窗口顶边界到单元格顶边界的垂直距离,如果窗口滚动到单元格外，可能为负值。?
44        用点表示的活动窗口右边界到单元格右边界的水平距离,如果窗口滚动到单元格外，可能为负值。
45        用点表示的活动窗口底边界到单元格底边界的水平距离,如果窗口滚动到单元格外，可能为负值。
46        如果单元格包含文字注释，返回TRUE；否则，返回FALSE。
47        如果单元格包含声音注释，返回TRUE；否则，返回FALSE。

48        如果单元格包含公式注释，返回TRUE；否则，返回FALSE。
49        如果单元格为数组的一部分，返回TRUE；否则，返回FALSE。
50         指示单元格垂直方向对齐方式。
1 = 顶对齐
2 = 置中
3 = 底对齐
4 = 两端对齐
51        指示单元格垂直方向。
0 = 水平
1 = 垂直
2 = 向上
3 = 向下
52         单元格前缀(或文字对齐)字符，如果单元格不包括任何内容，代表空文本(“”)。

53        当前显示的单元格的内容，文本形式，包括格式化单元格所加上去的数字或符号。
54        返回包含活动单元格的数据透视表视图的文件名。
55        返回数据透视表视图中单元格的位置。
56        在数据透视表视图中，返回包含活动单元格引用的字段名称。
57        如果单元格中所有的字符，或只有第一个字符被格式化为上标字体，返回TRUE；否则，返回FALSE。?
58        作为显示在［单元格格式］对话框中［字体］标签定位单元格中所有的字符或只第一个字符的正文返回字体风格，如 “Bold Italic”。

59        返回下加线风格的数。
        1 = 无底线?
2 = 单底线
3 = 双线
4 = (single accounting)?
5 = (double accounting)
60        如果单元格中所有的字符，或只有第一个字符被格式化为下标字体，返回TRUE；否则，返回FALSE。
61        作为文本返回活动单元格数据透视表项的文件名。
62        返回工作簿和"［book1］sheet1"形式的当前表的文件名。
63        返回单元格的填充(背景)颜色。
64        返回单元格的图案(前景)颜色。

65        如果［增加缩格］对齐选项处于开的状态( 只用于Microsoft Excel的远东版本返回TRUE; 否则，返回FALSE。
66        返回包含BOOK1.XLS 形式单元格的工作簿的簿名。

Reference    是提供信息的单元格或单元格范围。

如果引用的是单元格范围，使用引用中第一个范围的左上角的单元格
        如果引用被省略，默认为活动单元格。

[ 本帖最后由 lrlxxqxa 于 2010-11-26 22:28 编辑 ]

lrlxxqxa

http://club.excelhome.net/thread-520373-1-1.html

众所周知，在Excel 2003中如果仅仅使用格式工具栏中的“边框”功能为透视表添加边框的话，刷新数据透视表后，这种边框就消失（如下图所示），但是在Excel 2007中这种现象已经不存在了，用户设置的边框将“永恒”存在。对于广大的仍在使用Excel 2003的用户来说，其实有多种方法可以实现数据透视表的“永恒边框”。

方法一：使用条件格式。
优点：简单易行，无需代码。
缺点1：如果行字段或者列字段中有“空项目”，那么数据透视表的公式就不像下图中那么简单了，大家可以研究一下如何完善条件格式公式。
缺点2：如果改变透视表布局，比如添加字段后透视表区域将“扩大”，那么新增加的单元格区域无法自动添加条件格式，因此没有“永恒边框”，也就是说这种实现方法无法自适应扩展。
补充：对于整个工作表应用条件格式也解决这些问题，请参考12楼BIN_YANG168版主的帖子。
选中透视表区域，设置条件格式，公式为=COUNTA(A3:$IV$65536)
杨版的方法有一个弊端,就是非透视表部分,有内容时,也会自动加边框.这个要求要某个工作表专门放置一个透视表

方法二：使用数据透视表刷新事件
优点：可以实现自适应扩展，并且对于代码所在工作表之内新增加的透视表仍然有效，当然可以继续扩展为整个工作簿内的全部透视表都可以实现“永恒边框”。
缺点：打开工作簿时必须“启用宏”，否则事件代码无法生效，“永恒边框”也就消失了。

1. Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)‘事件代码2
   
2.     Target.TableRange1.Borders.LineStyle = xlContinuous
   
3. 
   
4. End Sub

复制代码

方法三：使用VBA代码一次性设置“永恒边框”（无需任何事件代码）
使用方法：选中透视表中的任何一个单元格，然后运行“PvtBordersLine”过程设置边框，这个边框将可以“永恒”存在，而不需要使用上面的事件代码，透视表刷新之后这个边框仍然存在。
优点：一次运行代码即可以实现可以自适应扩展的“永恒边框”，在此之后不再依赖“宏”功能，即使以后打开工作簿时没有启用宏，甚至已经删除“PvtBordersLine”过程代码，“永恒边框”仍然存在，是一劳永逸的实现方法。即使运行代码时，透视表只显示了部分内容，全部显示透视表内容或者添加字段改变布局之后，边框仍然是“永恒”的，参见下图。
缺点：对于新增加的透视表，无法自动具备“永恒边框”，必须手工运行一次代码设置边框。
注意：如果使用方法二中的“事件代码1”替换下面的代码则无法实现这个效果。

1. Sub PvtBordersLine()
   
2.     ActiveCell.PivotTable.TableRange1.Borders.LineStyle = xlContinuous
   
3. End Sub

复制代码

写在最后的话，方法二中两段事件代码应该是等效的，这是大家在简化录制宏产生的代码时，最经见到的代码，只是偶然中发现了第三种实现方法，我不知道其真正的实现原理。

[ 本帖最后由 lrlxxqxa 于 2010-11-26 22:29 编辑 ]