点击上方“Excel和VBA”,选择“置顶公众号”
致力于原创分享Excel的相关知识,源码,源文件打包提供
一起学习,一起进步~~
其实Excel的所有功能中,相信大家做喜欢的还是函数了,因为函数用起来更加的简单方便,并且学习成本低,一些简单的功能,多嵌套几个函数就可以实现了,但是函数中如果出现了计算错误的情况的话,就会返回一串因为,这个结果非常的不美观,而且相信很多的领导并不喜欢自己的报表中会有这样的结果,所以我们还要花费时间来处理这个公式的错值,那么Excel的另外一个神器-VBA是否可以批量处理这个呢?
事实上是完全可以的,VBA可以在不改变单元格公是的情况下,将公式中的错值进行隐藏,最终的效果就是如果是错值,单元格看起来就是空的,但是如果你更改了正确的内容之后,新的计算结果又可以正常展示,我们来看看案例
还是我们上节的场景,这里我们随意的更改一些数据,构造一个计算错值的结果,可以看到已经有了两行数据中出现了错值
这样的错值出现在数据中看起来非常的难看,并不美观,我们不可能在提交给领导的数据表格中出现这样的结果的
如果错值不可避免,那么有没有办法隐藏错值呢?
肯定会有小伙伴说,我可以删除错值
没错,删除错值是最直接的方法,但是如果有很多的错值呢?你一个个去删除,应该也要浪费不少的时间吧。何况如果你删除了错值,那么本质上等于你也删除了单元格内的公式,如果后面你还需要用到这个单元格的话,你有需要重新手动输入公式,这是一个非常麻烦的过程,所以这个时候你需要用到VBA
Sub tests()
Dim rng As Range, a As Range
Set rng = ActiveSheet.UsedRange
For Each a In rng
a.Font.Color = a.Interior.Color
rng.NumberFormatLocal = "[黑色]G/通用格式"
Next
End Sub
看看效果
参考上面的演示,我们可以看到公式计算的错值已经成功被隐藏,但是单元格本身的公式还是存在的,并没有操作公式的删除,如果你修改为正确的数据,单元格本身的公式还是可以计算,返回正确的结果的。
这个代码最终的作用,仅仅是隐藏了错误的结果,并没有修改单元格的公式,这就是VBA真正强大的地方
来看看今天的代码,看起来代码非常的简单,如此简单的代码居然可以实现这么神奇的效果
外面的框架,我们就不再花时间讲述了,直接进入今天的主体
For Each a In rng a.Font.Color = a.Interior.Color
rng.NumberFormatLocal = "[黑色]G/通用格式"
Next
遍历整个单元格区域
首先第一步
a.Font.Color = a.Interior.Color
有认真学习过之前单元格颜色的小伙伴一定能够看到代码的,将单元格的字体颜色变成单元格本身的颜色
这个代码实现起来有什么效果呢?
不瞎猜,直接上代码
可以看到,当代码执行到这一句的时候,我们看到对应的第一个单元格内容,肉眼已经不可见了,但是单元格本身的内容还是存在的
为什么呢?因为单元格背景色是白色,而我们将字体也设置成了白色,那么肉眼肯定看不到具体的内容了,就是这个效果。
换个角度想,不管这个单元格的结果是错值还是正确的结果,经过这句代码的加工,都已经是肉眼不可见了,但是保留单元格本身的内容/公式。
是不是已经有了一点味道了。
错值不可见是我们想要的结果,但是正确的结果也不可见就不是我们想要的结果了,那么如何让正确的结果展现出来呢?
就是下一句代码了。
rng.NumberFormatLocal = "[黑色]G/通用格式"
设置单元格的数字格式,让错误值以外的值显示为黑色
在看看这句代码的效果
我们可以看到通过这句话代码之后,原来不可见的内容有可见。当然是非错值,如果是错值的话,依然还是不可见的。
简简单单的两句代码,就可以实现我们之前不可能实现的效果。
学好VBA,再搭配函数公式,这才是玩转Excel的最佳途径
联系客服