每天进步一点点并不太难

lrlxxqxa

数据透视表中条件格式优先级大于字段设置自定义格式

做作业过程中发现对透视表中数据字段进行自定义设置时，如果同时用条件格式和字段设置定义，那么优先显示条件格式的设置。比如：条件格式设置为红色，字段设置自定义设置为蓝色，那么显示红色。

拓展思考：
1、个人猜测原因：字段的自定义功能是excel早期版本就有的功能，后来版本才添加了条件格式的设置，所以按照后封装进去的功能优先显示

2、这个发现能否应用：自定义格式只能在输入框写一个公式；条件格式最多满足三个条件公式；如果组合起来能否打破3个条件的局限呢？在完全覆盖时只显示条件格式，如果局域覆盖呢？留作疑点今后解决吧。

[ 本帖最后由 lrlxxqxa 于 2010-12-4 12:39 编辑 ]

lrlxxqxa

编辑公式之函数提示工具

作用方向为编辑公式对“函数提示工具”的影响。反方向的应用同样具有极强的实用性。单击“函数提示工具”上的形式参数，就会选定对应的参数字符，这是一项非常有用的功能，不仅可以用来审查嵌套层次是否符合编辑思路

F3功能键，按F3功能键就能弹出当前可用的名称（“名称”可以理解为一个变量，封装了一个公式），然后就可以直接使用鼠标选取需要的名称到正在编辑的公式中了。这么做同样是提高了录入时的准确率...同时，另一个好处依然是不会打断思路，因为不用去思考到底为某个特定功能的公式取了什么名字。（这里应该是面向2007版，2003版是ctrl+F3)

最后再介绍一个功能键，F2，F2的功能就是进入编辑状态，虽然在单元格编辑公式时显示不出有什么用，当在基本菜单功能的对话框编辑公式时就比较有用。省去了腾出手去控制鼠标进入对应公式编辑框中，这看上去没有什么，但相信我，以后你会觉得很好用的。

如果单纯是需要停顿，有一个比较简单的方法，方法简单，但比较解决问题，在公式前导符“=”之前插入一个空格即可。如此，单元格的格式属性就变了，原先是功能性的公式，现在就变成了普通文本，这样就为中场休息提供了保障。(也可以插入'来把公式变成文本）

第二，不是时间上需要停顿，而是思维上需要停顿，这个也是很正常的。在写一个比较负责的条件公式时，分支多，层次深，很难一下子把公式全都书写到位的，这个时候可以使用“空文本”的方式先来填充，或者使用一段有意义的字符串先来占一个位，这个也是非常重要的。这样就在思维上将一个复杂的问题切割成几块来解决，降低了思维上的难度。

[ 本帖最后由 lrlxxqxa 于 2010-12-4 12:40 编辑 ]

lrlxxqxa

如何输入数字后，自动填充相应预设文字

1. [=1]"男";[=2]"女";"人妖"

复制代码

这个公式写的很妙

[ 本帖最后由 lrlxxqxa 于 2010-12-4 12:40 编辑 ]

lrlxxqxa

http://t.excelhome.net/viewthread.php?tid=2089&page=3&extra=page%3D1
同学提出的问题：

simple666
10-4-27 09:18
打开你的作业1，有宏，但在代码模块里看不见，怎么做的？在当前工作表里我也粘贴了你的列宽代码数据，为什么我的就不行？能详细说明吗？谢谢！

我没注意过这个细节。我做的过程是自己录了宏分别设定列宽为12,12,12,20，可是发现所有列宽都变成20了，于是我修改了宏代码为目前呈现的，实现了恒定列宽。
具体过程是：录制宏。代码如下

1. Sub Macro1()
   
2.     Columns("B:D").Select
   
3.     Selection.ColumnWidth = 12
   
4.     Columns("E:E").Select
   
5.     Selection.ColumnWidth = 20
   
6. End Sub

复制代码

然后复制到透视表的刷新事件里面

1. Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
   
2. Columns("B:D").Select
   
3.     Selection.ColumnWidth = 12
   
4.     Columns("E:E").Select
   
5.     Selection.ColumnWidth = 20   
   
6. End Sub

复制代码

结果发现列宽都变成20了。
后来修改为：

1. Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
   
2.     Columns("B:D").ColumnWidth = 12
   
3.     Columns("e:e").ColumnWidth = 20
   
4. End Sub

复制代码

打开文件时要启用宏才能实现功能
设置日期列的单元格格式为要求的格式。
如果不设置单元格格式，也可以直接在代码里写Columns("e:e").NumberFormatLocal = "yyyy""年""m""月""d""日"";@"
完整代码如下：

1. Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
   
2.     Columns("B:D").ColumnWidth = 12
   
3.     Columns("e:e").ColumnWidth = 20
   
4.     Columns("e:e").NumberFormatLocal = "yyyy""年""m""月""d""日"";@"
   
5. End Sub

复制代码

lrlxxqxa

必做题比较简单，直接隐藏透视表字段的下拉按钮显示即可：

1. Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
   
2. Dim i As Integer
   
3. For i = 1 To Sheets(1).PivotTables(1).PivotFields.Count
   
4. Sheets(1).PivotTables(1).PivotFields(i).EnableItemSelection = false
   
5. Next
   
6. End Sub

复制代码

下面要说说选做题了：
先说下我的思路：要求根据点击按钮后切换下拉按钮的显示与否，我们利用if函数作为开关来构建一个分支判断，这样找到了透视表字段的EnableItemSelection属性；然后仔细观察发现在隐藏下拉按钮的同时把透视表页字段也隐藏了，思考后我发现可以通过隐藏页字段所在列来实现，于是找到了rows的EntireRow.Hidden属性。
完整代码如下：

1. Private Sub CommandButton1_Click()
   
2. If CommandButton1.Caption = "去掉下拉箭头" Then yes Else no
   
3. End Sub
   
4. 
   
5. Sub no()
   
6.     Rows("5:5").EntireRow.Hidden = False
   
7.     Dim i As Integer
   
8.         For i = 1 To Sheets(2).PivotTables(1).PivotFields.Count
   
9.             Sheets(2).PivotTables(1).PivotFields(i).EnableItemSelection = True
   
10.   CommandButton1.Caption = "去掉下拉箭头"
    
11.         Next
    
12. End Sub
    
13. 
    
14. Sub yes()
    
15.     Rows("5:5").EntireRow.Hidden = True
    
16.     Dim i As Integer
    
17.                  For i = 1 To Sheets(2).PivotTables(1).PivotFields.Count
    
18.             Sheets(2).PivotTables(1).PivotFields(i).EnableItemSelection = False
    
19. CommandButton1.Caption = "恢复下拉箭头"
    
20.         Next
    
21. End Sub

复制代码

最后检查时发现按钮的背景是灰色，而题目要求是透明，打开按钮属性，在“Backcolor”属性中从默认的更改为突出显示文本，关闭属性窗口，退出设计模式。
调试代码运行，无误，保存。

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

lrlxxqxa

boatwxp同学的代码合理的利用了for循环的嵌套和变量的声明，使条理更易读、结构更清晰。来看：

1. Private Sub CommandButton1_Click()
   
2.     Dim pvt As PivotTable
   
3.     Dim pvtfld As PivotField
   
4.     If Me.CommandButton1.Caption = "去掉下拉箭头" Then
   
5.         For Each pvt In PivotTables
   
6.             For Each pvtfld In pvt.PivotFields
   
7.                 pvtfld.EnableItemSelection = False
   
8.             Next
   
9.         Next
   
10.         Rows(2).Hidden = True
    
11.         Me.CommandButton1.Caption = "恢复下拉箭头"
    
12.     Else
    
13.         For Each pvt In PivotTables
    
14.             For Each pvtfld In pvt.PivotFields
    
15.                 pvtfld.EnableItemSelection = True
    
16.             Next
    
17.         Next
    
18.         Rows(2).Hidden = False
    
19.         Me.CommandButton1.Caption = "去掉下拉箭头"
    
20.     End If
    
21. End Sub
    

复制代码

不足2点：
1、按钮没有修饰，题目中按钮是“突出显示文本”，在按钮属性的BackColor中设置。然后就变白了；
2、按钮放右边后，隐藏第二行导致点击按钮时候上下跳动；解决方法很简单，把按钮放置在隐藏行上方即可。
但瑕不掩瑜，boatwxp的方法非常值得借鉴！

ps：对于他必做题中没有代码，照样隐藏按钮的功能，小T老师提出了疑问。
http://t.excelhome.net/thread-1960-1-1.html
我个人理解是他先写好代码，然后刷新透视表执行，之后把代码删除了。大家可以试一下，按钮依然隐藏。不知boatwxp是不是这么做的？

lrlxxqxa

http://t.excelhome.net/viewthread.php?tid=1960&page=2#pid40174

jcgswxq11同学通过在公用的过程里声明了名叫“sh”的对象，使Shape对象响应编写的宏代码，其他跟之前方法相同，就是把yes和no过程改了个名为xiala1和xiala2

1. Public Sub 按钮1_单击()
   
2. Dim sh As Shape
   
3. Set sh = ActiveSheet.Shapes("Button 1")
   
4. If sh.TextFrame.Characters.Text = "去掉下拉箭头" Then xiala1 Else xiala2
   
5. End Sub
   
6. 
   
7. Sub xiala1()
   
8. Dim sh As Shape
   
9. Set sh = ActiveSheet.Shapes("Button 1")
   
10. ActiveSheet.PivotTables(1).PivotFields("销售地区").EnableItemSelection = False
    
11. ActiveSheet.PivotTables(1).PivotFields("销售人员").EnableItemSelection = False
    
12. sh.TextFrame.Characters.Text = "恢复下拉箭头"
    
13. End Sub
    
14. 
    
15. Sub xiala2()
    
16. Dim sh As Shape
    
17. Set sh = ActiveSheet.Shapes("Button 1")
    
18. ActiveSheet.PivotTables(1).PivotFields("销售地区").EnableItemSelection = True
    
19. ActiveSheet.PivotTables(1).PivotFields("销售人员").EnableItemSelection = True
    
20. sh.TextFrame.Characters.Text = "去掉下拉箭头"
    
21. End Sub

复制代码

不足：在代码中更改EnableItemSelection时没有使用for循环，如果需要隐藏的按钮多了一个个写很麻烦的

lrlxxqxa

为节省资源不重复传附件了，具体实例在下面链接：
数据透视表的内部字段能排序吗？

问题1，2在本班su11wei18同学和论坛的抹香鲸2009的帮助下已经解决，我录了操作过程以便分享，见附件动画。

问题3在小T老师和论坛的whsfhwm帮助下解决，使用的是终极武器vba，呵呵，越来越觉得不学不知道，实在真奥妙！代码如下：

1. Sub test()
   
2.     Dim Sht As Worksheet, i As Integer
   
3.     For Each Sht In Worksheets
   
4.       If Sht.Name <> "总报表" Then
   
5.         If Sht.PivotTables.Count Then
   
6.         Sht.PivotTables(1).HasAutoFormat = False
   
7.         Sht.PivotTables(1).EnableDrilldown = False
   
8.         End If
   
9.       End If
   
10.     Next  
    
11. End Sub

复制代码

这样来看，只要合理利用辅助列来构建计算字段和计算项，数据透视表的功能还是相当强大的，非分类汇总所能笔迹，加之vba这双翅膀，真是如虎添翼。
早就听说数据透视表结合vba和sql才能发挥出最强大的威力，由此例看来可见一斑

lrlxxqxa

教程内容概要：
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

一、Excel中刷新数据透视表的两种方法
    1.手工刷新
    2.自动刷新
二、数据透视表数据缓存
三、刷新数据透视表的注意事项
注：参考内容《Excel数据透视表大全》第3章
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

一、为什么需要刷新数据透视表？
数据源经常变化、数据透视表需要呈现最新数据。

二、Excel中刷新数据透视表的两种方法
1、手工刷新
（1）右键菜单，刷新数据
（2）数据透视表工具栏，刷新数据按钮
窗口-新建窗口，此时会打开一个同样的工作簿，窗口中选择“与工作簿**并排比较”，这样可在并排的两个工作簿中将数据源与透视表刷新进行直观对比
2、自动刷新
（1）打开时刷新
     表格选项，勾选“打开时刷新”，确定。
（2）定时刷新（仅用于外部数据源）刷新频率
     表格选项，刷新频率，以分钟为单位设定。
[color=Red]3、使用vba代码来执行刷新
如VBA不能使用，工具，宏，安全性，将宏安全性设置为“中”，确定。
（1）激活工作表时刷新
在工作表标签单击鼠标右键，查看代码，或者直接按Alt+F11打开VBE窗口，在工程窗口中选中相应的工作表名称，在右侧的代码窗口中输入

1. Private Sub Worksheet_Activate()
   
2.     ActiveSheet.PivotTables("数据透视表").PivotCache.Refresh
   
3. End Sub

复制代码

代码中的PivotTables("数据透视表")可以直接指定，如根据需要指定工作表中第几个透视表：PivotTables(1)或PivotTables(2)
（2）打开工作簿时刷新

1. Private Sub Workbook_Open()
   
2. Sheets(1).PivotTables(1).PivotCache.Refresh
   
3. End Sub

复制代码

其中的Sheets(1)可以根据需要灵活指定
（3）批量刷新：
插入图片-自选图形-矩形框，在工作表中拖动，在矩形框上单击右键菜单，指定宏，选择宏进行编辑，单击确定。代码如下：

1. Sub 矩形1_单击()
   
2. ThisWorkbook.RefreshAll
   
3. End Sub
   

复制代码

4、使用EXCEL自带按钮进行批量刷新
直接点击透视表工具栏上“全部刷新”的按钮
添加按钮的两种方法：
（1）单击数据透视表工具栏上的“工具栏选项”按钮，自定义，命令选项卡，类别选项下选择“数据”，命令选项下拖动“全部刷新”，使其添加到数据透视表工具栏（或EXCEL工具栏中），关闭自定义。
（2）如果只想添加到数据透视表工具栏，在数据透视表工具栏中，单击“工具栏选项”，“添加或删除按钮”，“数据透视表”，弹出菜单中，勾选“全部刷新”，或者取消勾选，以实现添加和删除按钮。
5、如何使用外部数据源，创建透视表。
    点击数据-导入外部数据-导入数据-选中外部数据源，选择相应的表格，确定，选择数据的放置位置（现有工作表、新建工作表）。
或直接单击创建数据透视表，数据透视表显示位置（新建工作表、现有工作表），完成，添加相应的字段。

二、共享数据缓存
数据透视表的数据缓存是计算机内存中的一个指定区域用于存储报表数据。
基于同一单元格区域或数据连接的两个或多个数据透视表之间自动共享缓存，可以实现同时刷新。当透视表用作其他数据透视表的数据源时，对其中任何一张数据透视表刷新，都会引起所有链接在一起的数据透视表进行刷新
优势：提高性能的同时减小了工作簿的大小。
1、下列情况不能共享数据缓存：
不希望所有数据透视表中都显示计算字段和计算项。
不希望在所有数据透视表中都按同一方式分组字段。
不希望同时刷新所有数据透视表。
2、如何使用数据缓存
已经有数据透视表，再创建新的数据透视表的时候会有提示：“如果新报表基于现在的、创建自同一源数据的报表，则新报表可以使用较少的内存。是否让新报表建立在与现在报表相同的数据上？
单击“是”按钮时可以节省内存并使工作表较小。
单击“否”按钮时，两个报表各自独立。
需要注意的是：使用共享数据缓存建立的透视表，当其中一个透视表更新时所有透视表同步更新。
具体过程如下：选择数据透视表中的任意一个单元格，单击数据透视表，公式，计算字段，添加一个新的字段，名称“销售提成”，公式“＝销售收入*0.005”，确定。修改字段标题（将求和项替换为空格）。
再看其他的数据透视表，这时候可以看到共享缓存的透视表添加了“销售提成”的字段，非共享的透视则没有“销售提成”的字段。
修改数据源，单击非共享透视表进行刷新按钮，透视表进行了更新，共享的透视表则没有进行刷新，选择共享数据透视表，单击刷新按钮，此时两个共享的数据透视表，内容都进行了刷新。
三、刷新数据透视表的注意事项
1、海量数据源将导致刷新速度非常慢
2、如何清除“垃圾条目”
比如：将源数据中C更改为F，刷新透视表，透视表中的C已经更改为F，在类别的下拉菜单中，C却还存在，也就是说下拉菜单中只是添加了新的类别，而没有删除原来的类别，如何解决？
字段列表，删除类别，刷新透视表，将类别重新添加到行字段
3、数据源中新增的“行”和“列”用刷新的方法无法更新到数据透视表中（需要创建动态的数据透视表）
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

从教程扩展开来：
一、刷新引用外部数据的数据透视表；
如果创建的透视表是基于对外部数据的的查询，excel可以在用户工作时在后台执行数据查询
表格选项－－外部数据选项－－勾选“后台查询”
（注意：此功能只针对外部数据，否则为灰色）
二、关于创建动态数据透视表
1、用offset函数定义data动态引用数据区域
使用定义名称的方法来创建动态数据透视表，首先要要使用公式定义一个动态的数据源区域，当新的记录添加到数据源的时候，数据源区域会自动扩展。然后把定义的名称范围用于数据透视表，这样创建的透视表也将是动态的。
比如定义一个名称为data

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

复制代码

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

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

[ 本帖最后由 lrlxxqxa 于 2010-4-29 21:47 编辑 ]

aidixiao

谢谢分享。