Excel 2003数据透视表“永恒边框”实现方法

taller · 发表于 2010-1-1 10:22

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

方法一：使用条件格式。
优点：简单易行，无需代码。
缺点1：如果行字段或者列字段中有“空项目”，那么数据透视表的公式就不像下图中那么简单了，大家可以研究一下如何完善条件格式公式。
缺点2：如果改变透视表布局，比如添加字段后透视表区域将“扩大”，那么新增加的单元格区域无法自动添加条件格式，因此没有“永恒边框”，也就是说这种实现方法无法自适应扩展。
补充：对于整个工作表应用条件格式也解决这些问题，请参考12楼BIN_YANG168版主的帖子。

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

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) ‘事件代码1
With Target.TableRange1
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub

复制代码

对于熟悉VBA代码的朋友来说，可能已经看出上面代码（35行）可以简化为下面的3行代码，完全正确！最精彩的最简洁的VBA实现方法由此衍生而来。

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

复制代码

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

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

复制代码

方法四：宏表函数+条件格式设置永恒边框 -- JSSY (27楼)

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

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

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

bibisin · 发表于 2010-1-1 11:31

谢谢版主分享,学习了

亡者天下 · 发表于 2010-1-1 11:25

版主辛苦了
新年就送上第一份礼物啊

开心萝卜惠州 · 发表于 2010-1-1 10:37

谢谢分享！

lgcmeli · 发表于 2010-1-1 10:43

这么好的方法一定要收藏。谢谢taller新年第一天的问候。

masterexcel · 发表于 2010-1-1 18:24

好方法！最后一种简便。

monvzhilei · 发表于 2010-1-1 18:38

方法好。用到了，来拷贝一下代码。

wuxiang_123 · 发表于 2010-1-1 19:27

谢谢T版分享

wuxiang_123 · 发表于 2010-1-1 20:28

我的理解：透视表边框为什么不能直接套上去呢？其实，这是有原因的。大家都知道，透视表里面有个报告的选项。嘿嘿，就是这个东东在作祟。当我们刷新透视表时，其实是将透视表的报告格式返回我们已经选择的报告格式（假如你不曾选择的话，透视表会自动帮你选择其中的“传统数据透视表”格式），所以，当我们直接套上的边框在透视表刷新后就会被当前报告格式所代替。我们的功夫也就白费了。而条件格式等手段，就是我们和透视表沟通的工具，相当于告诉透视表，“我的地盘我做主，你丫别动我的边框”

。
关于报告，有一点不知道大家有没有发现，无论是那个报告格式，其边框都是外边框，没有内边框的。路人甲：他是内是外有啥用。嘿嘿，这当然有用。在某些情况下，我们就可以不用设置行字段的边框。路人乙：具体哪些情况啊？我：自己摸索去

以上是我对透视表边框的理解，不知道是否正确。

conankid · 发表于 2010-1-1 21:11

大爱第三种！！！好简单的方法~~~多谢郗版~~~~~~~~~

PS：就是有点灵异，我第一次写出来后运行居然提示说缺少")"，完全没动再让运行又说对象不支持什么属性方法，愤然删掉，复制郗老师的代码，成功。。暴汗。。。明明就是一样的。。。。还好后面自己再写一样的也成功了，不然在这2010头一天就要怀疑自己RP还是有点伤自尊的。。。。

		自动登录	找回密码
密码			免费注册

[原创] Excel 2003数据透视表“永恒边框”实现方法

评分