excel技巧大全vba（要想提升Excel办公效率质的飞跃）
excel技巧大全vba（要想提升Excel办公效率质的飞跃）首先，出于安全考虑，OFFICE默认是不会把任何VBA的东西放出来的，连按钮都是。所以想用得顺畅，有一些前置步骤是要做的，此处以Office 2007版本为例。首先，VBA的全称是Visual Basic for Applications，本质上是嵌入于应用程序中，使用VB语法的脚本语言。脚本语言的优势是保存即生效，不用编译。所以调整一些细节的时候还是很方便的。不过，由于VB是微软自家的东西，给的权限远比LUA这种纯外部脚本大得多，所以安全问题一直是个困扰。事实上，VBS病毒一直都有。
文章来自站内生活家 波导终结者之前群里讨论过，为什么基础Excel函数教程之类的会成为月经首页热门，得到的结论是：基础用户比较多，看到了就想着说，以后说不定有用吧，点了收藏。
所以更高阶一点的东西，比如VBA，讨论结果是不会有什么热度，因为大部分人觉得说一辈子也用不着吧，就不会点收藏了。
不过这次正好有征文，Visual Basic是其中一个选题。光写VB估计更不会有人看了，所以还是结合一下实际，写写一些简单的VBA吧。
一、开启Office当中的VBA
首先，VBA的全称是Visual Basic for Applications，本质上是嵌入于应用程序中，使用VB语法的脚本语言。
脚本语言的优势是保存即生效，不用编译。所以调整一些细节的时候还是很方便的。
不过，由于VB是微软自家的东西，给的权限远比LUA这种纯外部脚本大得多，所以安全问题一直是个困扰。事实上，VBS病毒一直都有。
所以想用得顺畅，有一些前置步骤是要做的，此处以Office 2007版本为例。
首先，出于安全考虑，OFFICE默认是不会把任何VBA的东西放出来的，连按钮都是。
我们先点击左上角，选择Excel选项。
来到自定义，右边找到开发工具选项卡，找到Visual Basic，拉到你想要的地方去。
如图，我直接拉到了左上角的快捷栏，点击这个按钮，就进入VBA的编辑界面。
另外，默认的XLSX格式是不包含宏的，所以还要另存为XLSM格式。
另外，有时候我们会遇到脚本无论如何也没反应的情况，一般还是OFFICE的安全设置问题。
首先先到选项里，找到信任中心。
把你放含有脚本文件文档的目录添加进去，就可以了。
另一种方式，直接在宏设置里，默认启用所有宏。
比较适合文件很多的朋友。
二、Excel妙用之高亮行与列
首先，我们通过之前添加的按钮进入VBA界面，默认会来到这么一个地方。
Sheet1、2、3是三张工作表，可以单独拥有独立的代码。
而最后的ThisWorkbook里的代码，则是整个文档共用的。
这里我们用全局通用的Workbook。
双击ThisWorkbook这条，默认会产生一个函数，这个函数是对象的默认函数Open，我们不需要，等下可以删除。
这个例子用到的函数是SheetSelectionChange，顾名思义，当工作表选中区域改变时，触发此函数。
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object ByVal Target As Range)
Cells.FormatConditions.Delete
iColor = RGB(127 127 0)
With Target.EntireRow.FormatConditions
.Delete
.Add xlExpression "TRUE"
.Item(1).Interior.Color = iColor
End With
With Target.EntireColumn.FormatConditions
.Delete
.Add xlExpression "TRUE"
.Item(1).Interior.Color = iColor
End With
End Sub
代码如上。
颜色可以在 iColor = RGB(127 127 0) 这一句改。
这种效果只能用VBA做，很适合大量只读数据时避免眼花看错行的情况。
不过，有一点要注意的是：
VBA做的一切修改，都是无法撤销的，所以一定要慎重。就像代码里，修改了单元格的格式，如果没有额外写代码存储和恢复的话，就是无法还原的。
三、Excel妙用之自定义函数
首先，自定义函数必须写在模块里，所以我们右击，插入，模块。
然后，我们写个简单的函数。
这个函数很简单，将引用单元格的值取出，作为文本，然后再串上“波导终结者”这个字符串，并返回。
VB函数的返回值赋值方法比较特别，其他大部分语言都是return XXX啥的，就VB是把函数本身作为一个变量去赋值。
总之，一个最简单的自定义函数就这样成了。
注意，函数前面要加上Public以方便外部调用。
回到表中，打个等号，敲出自定义函数前面俩字母，可以看到，系统已经将自定义函数自动补完。
输完回车，搞定。
可以看到，此单元格的内容，就是引用单元格的内容再串上波导终结者这个字样。
可能有的朋友会说，这函数也太简单了，来点花哨的吧？
但是，每个人的具体需求都各有不同，我只能教大家如何弄一个自定义函数，具体要做什么，肯定只能依照自己需求去写具体的代码了。
四、将Excel打造成职场里的真·生产效率工具
以前我在网游公司写脚本，毫无疑问的要跟策划有非常多的沟通。很多时候，策划那边东西没定好，我们这边就没办法开做。
策划提供的东西如果太模糊，还得回头跟他重新确认。但是要太细的话，比如涉及到程序核心的一些数值，很多策划也懵。
就比如，现在要做一些新怪物，从程序的角度来讲，表里的大几十个字段各有各的用处。
从脚本的角度来讲，相关数值策划要是不给，我也不可能自己给你填。
从策划的角度来看，其实对他们有用的就几个：血蓝攻防外观等。
于是我就用VBA做了一个小工具。
首先，把所有字段列出来，默认值列出来，策划有用到的字段筛选出来。
点击左上角的“生成怪物数据表”，此时就会把这些有用的字段筛选出来，生成一个EXCEL表。
策划只需要照着这个表里的数据填好数值就行了。
不过，如果只是策划填表，那策划自己也能做，这个生产效率并没有本质性的提高。
效率提高的部分，在于一键生成刷库SQL语句。
点击生成Insert或者Update数据按钮之后，把刷库语句输出到文本文件里并自动打开，复制即可用。
原来的流程里，每个策划提供的数值格式各不同，当然，也不可能一键导入，于是每个案子，脚本都得把策划给的数值一个一个手动填，填完还得核对，然后再刷库试验……
使用了VBA之后，整个流程从策划设计数值，到脚本刷库成功的耗时，由原来的0.5至1天左右，减少至半小时（根据策划自己出数值的速度决定）。
脚本这边最麻烦的填数据步骤，耗时由3-5个小时缩短至2秒左右，数据的正确率为100％（除非策划自己填错）。
后续如果数值有变动，甚至是服务器表结构变动（比如新增字段），只需要改一下EXCEL，重新点击按钮，耗时在半分钟以内。
处理这种有规律的、矩阵形式的数据，EXCEL非常擅长，但是光靠系统函数或者宏，最多只能做做排序筛选。
我花了2天的工作时间写这个脚本，应用之后，每天都可以给策划和脚本节省几百小时的工作时间，关键的是，准确率100％。
虽然我离职已久，不过这个脚本估计现在仍然在用吧。设计的时候就已经做成通用的，不同项目只要修改表字段、表名，即可通用。
由于代码应用太过具体，这里就象征性截张图上来。
关键的是，代码不能写死。比如项目最早是A游戏做，字段有50个，你如果写死50个，后面B项目的字段不一样，代码全部得重写。
这里一定要用循环以及内容判断，我不管你字段有几个，是什么内容，反正我就按照规则，把所有字段用循环筛选一遍即可。
在EXCEL里面放按钮也很简单。
选项里先把开发工具栏放出来，插入，底下就有按钮复选框等等控件。
点击设计模式，就能像在VB6里面一样设计，之后在按钮的点击事件里写代码即可。
五、在Word里统计字频
这里用一个比较普通的例子：在Word里统计字频。
如果你要统计一个字或者一个词，在一段WORD里面出现的次数，那么你网上搜方法，肯定都是告诉你查找替换法。
但如果你要把每个字都做字频统计，这种方法就行不通了。
使用代码，我们可以很轻松的做到。
这里由于是演示，我直接用Msgbox输出结果了。
代码效果就是，选中一段话，然后到VBA窗口里执行，这时候就会自动把所选中的这段文字，每个字出来的字频统计出来。
由于只是演示，我就直接做消息框弹出了，有需求的可以另行添加，比如扔EXCEL排序等等。
Sub bdzjz_tongji()
Dim i As Integer
Dim n As Integer
n = Selection.Characters.Count
Dim dict
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To n
Dim s As String
s = Selection.Characters.Item(i)
If dict.Exists(s) Then
dict.Item(s) = dict.Item(s) 1
Else
dict.Add s 1
End If
Next i
Dim d_keys
d_keys = dict.keys
Dim d_items
d_items = dict.items
Dim sOut As String
For i = 0 To UBound(d_keys)
sOut = sOut & d_keys(i) & " 出现次数：" & d_items(i) & "次" & vbCrLf
Next
MsgBox sOut
End Sub
代码用到了字典类Dictionary，类似哈希表一样的结构。
将单字本身作为键值KEY，将字出现的次数存储和叠加，代码框架码好了之后就一劳永逸了。
六、在PPT里统计停留时间
以前在鞋厂做开发，曾经接到过运营的这么一个需求：
他们自己做了一个PPT，展示一些产品。现在，想在播放的时候，收集一下用户在具体某一页上停留的时间，以获得用户对产品的关注度。
PPT可以设置播放时各种延时效果，但是貌似没有统计的功能。
由于源文件找不到了，这里大概写一个原型。
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Dim dict
Sub OnSlideShowPageChange()
'记录当前页数
Dim iCut As Integer
iCut = SlideShowWindows(1).View.Slide.SlideIndex
Dim iCutTime As Long
iCutTime = timeGetTime()
'初始化字典
If IsNull(dict) Or (iCut = 1) Then
Set dict = CreateObject("Scripting.Dictionary")
End If
dict.Item(iCut) = iCutTime
If dict.Exists(iCut - 1) And (dict.Item(iCut - 1) > 0) Then
MsgBox "停留时间：" & (iCutTime - dict.Item(iCut - 1)) & "毫秒"
End If
End Sub
VBA自带的时间函数比较坑，只能精确到秒。而且是当天的秒，即晚上0点过后会重置，到时候还得判断是否跨天。
所以这里干脆用了系统API，timeGetTime获得的是开机到现在经过的毫秒数。
然后将PPT设置为手动播放，此时程序会自动记录每一次切换幻灯片时的时间，并且和上一张的时间相减，得出用户在上一张幻灯片里停留的时间。
由于是原型，这里就简单的减去序号上一张的时间，一般人播放就是滚轮或者鼠标一张一张点。如果是跳着播放的，比如从第1张直接跳到第3张等特殊情况，代码需要改动。
如图，我从第2张跳到第3张时，得到第二张幻灯片的停留时间是5124毫秒。
得到数据后要怎么用，那就看具体需求了。
事实上，VBA甚至可以直接连接数据库，不过写了估计没人看也看不懂，就先略过了。
七、总结与提醒
可能有的朋友听说过宏，宏与脚本的区别是什么呢？
一个宏，可以用一行或者一段脚本来实现。事实上，微软提供的“录制宏”功能，就是把操作录制成一段代码，然后作为宏来调用。
但是反过来，代码能做的事情多了去了，熟练运用了之后，比宏要强上百倍。
在应用性方面，Excel肯定是最常用的，放些文本框下拉框按钮啥的，甚至可以当成简易的程序来用。之前就帮别人做过简单的出题答题系统，太复杂应该没人看，就不提了。
Word其次，由于排版上的问题，控件不好放。而且由于不涉及太多运算，大多数功能通过软件原生就能实现。
PPT最次，需要强调的是，PPT运行时虽然能触发VBA脚本，但是却无法调试。PPT前台播放，和后台的脚本是多线程异步的，没有出错信息，也断不了断点。而且PPT支持的事件也比较少。
支持VBA的，也不一定只有微软自家的Office，像鼎鼎有名的AutoCAD也支持。
当然，VBA编程需要对VB语法有基础，并且不同软件的事件、属性都不同，查资料是难免的，肯定不像一些基础操作和函数一样人人都能用。但是如果能用好，那才真的是效率飞升了。
↓↓↓ 阅读更多内容 请点击 了解更多
