要想提升Excel办公效率质的飞跃,你不可不学VBA!零基础学习VBA看过来
之前群里讨论过,为什么基础Excel函数教程之类的会成为月经首页热门,得到的结论是:基础用户比较多,看到了就想着说,以后说不定有用吧,点了收藏。
所以更高阶一点的东西,比如VBA,讨论结果是不会有什么热度,因为大部分人觉得说一辈子也用不着吧,就不会点收藏了。
这个论点是有道理的,因为我之前连载过罗技LUA脚本系列,看一下数据就知道了。
不过这次正好有征文,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妙用之自定义函数
之前的首页热门,推荐来推荐去就那几个函数,光vlookup函数我就见了几十次了。
不过也是,系统自带的函数,常用或好用的也就这些,再推荐能推荐出什么花来呢?
这里,我来教大家做自定义函数。
首先,自定义函数必须写在模块里,所以我们右击,插入,模块。
然后,我们写个简单的函数。
这个函数很简单,将引用单元格的值取出,作为文本,然后再串上“波导终结者”这个字符串,并返回。
VB函数的返回值赋值方法比较特别,其他大部分语言都是return XXX啥的,就VB是把函数本身作为一个变量去赋值。
总之,一个最简单的自定义函数就这样成了。
注意,函数前面要加上Public以方便外部调用。
回到表中,打个等号,敲出自定义函数前面俩字母,可以看到,系统已经将自定义函数自动补完。
输完回车,搞定。
可以看到,此单元格的内容,就是引用单元格的内容再串上波导终结者这个字样。
可能有的朋友会说,这函数也太简单了,来点花哨的吧?
但是,每个人的具体需求都各有不同,我只能教大家如何弄一个自定义函数,具体要做什么,肯定只能依照自己需求去写具体的代码了。
如果你有具体需求并且在编程上遇到问题,欢迎来打赏留言
四、将Excel打造成职场里的真·生产效率工具
如何才能真正的称作提高生产效率?
如果只是会用vlookup函数,或者会用CTRL+C代替右键菜单的复制,那这所谓的生产效率也太入门和没价值了。
事实上,很多时候,我们在职场上的生产效率,不仅取决于我们自己,也取决于协同岗位或者部门的效率。
举一个亲身经历,也是很多朋友会遇到的问题:程序和策划的协同。
以前我在网游公司写脚本,毫无疑问的要跟策划有非常多的沟通。很多时候,策划那边东西没定好,我们这边就没办法开做。
策划提供的东西如果太模糊,还得回头跟他重新确认。但是要太细的话,比如涉及到程序核心的一些数值,很多策划也懵。
就比如,现在要做一些新怪物,从程序的角度来讲,表里的大几十个字段各有各的用处。
从脚本的角度来讲,相关数值策划要是不给,我也不可能自己给你填。
从策划的角度来看,其实对他们有用的就几个:血蓝攻防外观等。
于是我就用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 iDim 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 dictSub OnSlideShowPageChange()
'记录当前页数
Dim iCut As Integer
iCut = SlideShowWindows(1).View.Slide.SlideIndexDim iCutTime As Long
iCutTime = timeGetTime()'初始化字典
If IsNull(dict) Or (iCut = 1) Then
Set dict = CreateObject("Scripting.Dictionary")
End If
dict.Item(iCut) = iCutTimeIf dict.Exists(iCut - 1) And (dict.Item(iCut - 1) > 0) Then
MsgBox "停留时间:" & (iCutTime - dict.Item(iCut - 1)) & "毫秒"
End IfEnd Sub
VBA自带的时间函数比较坑,只能精确到秒。而且是当天的秒,即晚上0点过后会重置,到时候还得判断是否跨天。
所以这里干脆用了系统API,timeGetTime获得的是开机到现在经过的毫秒数。
然后将PPT设置为手动播放,此时程序会自动记录每一次切换幻灯片时的时间,并且和上一张的时间相减,得出用户在上一张幻灯片里停留的时间。
由于是原型,这里就简单的减去序号上一张的时间,一般人播放就是滚轮或者鼠标一张一张点。如果是跳着播放的,比如从第1张直接跳到第3张等特殊情况,代码需要改动。
如图,我从第2张跳到第3张时,得到第二张幻灯片的停留时间是5124毫秒。
得到数据后要怎么用,那就看具体需求了。
事实上,VBA甚至可以直接连接数据库,不过写了估计没人看也看不懂,就先略过了。
七、总结与提醒
可能有的朋友听说过宏,宏与脚本的区别是什么呢?
一个宏,可以用一行或者一段脚本来实现。事实上,微软提供的“录制宏”功能,就是把操作录制成一段代码,然后作为宏来调用。
但是反过来,代码能做的事情多了去了,熟练运用了之后,比宏要强上百倍。
在应用性方面,Excel肯定是最常用的,放些文本框下拉框按钮啥的,甚至可以当成简易的程序来用。之前就帮别人做过简单的出题答题系统,太复杂应该没人看,就不提了。
Word其次,由于排版上的问题,控件不好放。而且由于不涉及太多运算,大多数功能通过软件原生就能实现。
PPT最次,需要强调的是,PPT运行时虽然能触发VBA脚本,但是却无法调试。PPT前台播放,和后台的脚本是多线程异步的,没有出错信息,也断不了断点。而且PPT支持的事件也比较少。
支持VBA的,也不一定只有微软自家的Office,像鼎鼎有名的AutoCAD也支持。
不过,由于是自家的东西,微软给VBA开放了太多的权限,VBA、VBS病毒在历史上还是有一定地位的。大家如果网上找代码啥的,一定要多加小心。
一些平日经常用的,软件却又没办法简单实现的功能,都可以考虑用VBA来实现。涉及多人、多个部门的工作,如果能达成共识统一好格式,一个脚本可以顶好几天的工作量,还不会错。以前项目里做过从策划,设计,关卡,脚本,资源,测试一条龙的VBA,涉及过深这里就不细谈了。
当然,VBA编程需要对VB语法有基础,并且不同软件的事件、属性都不同,查资料是难免的,肯定不像一些基础操作和函数一样人人都能用。但是如果能用好,那才真的是效率飞升了。
快叫我大王啊
校验提示文案
值友0000000
校验提示文案
光刃小屋
校验提示文案
值友7474445295
校验提示文案
瞎拍照的叔叔
也没多少逻辑判断和计算。最关键的是EXCEL在二次嵌套公式后无法计算,只能依靠VBA来处理。要么可以在加一列用公式,这样会增加运算速度,VBA还是比较试用于重复相同工作用。
我VBA,C#都写,但是现在相对VBA做IT的会写的人少了。学习的话学过JAVA或者C#的话就很简单了。无非就是语法不同。逻辑基本都是大同小异的。但是对于新手和逻辑性不强的人建议还是放弃吧。简单的VBA可能可以写,但是繁琐的逻辑关系和运算在嵌套在一起估计头都要晕了的
校验提示文案
zclovely
校验提示文案
山楂罐头
校验提示文案
咸云
校验提示文案
啊磊啊兰Alan
校验提示文案
小草fms
校验提示文案
醉不成欢
校验提示文案
白金汗马
校验提示文案
power19goo
校验提示文案
平谷一点红发明洗面奶
校验提示文案
燊爷
校验提示文案
Aaron_the_Baron
1.源数据是海关数据中的产品名称,要求提取的关键词是产品品牌,已有一定的关键词库,
2.相当部分(60%-70%)数据有特征字符,关键词一般出现在特征字符后,已经用MID+SEARCH函数提取了。但是由于关键词长度不固定,无法直接调用。另外,如果单个数据中出现多个品牌,需要先判断是否同一公司旗下品牌(这个也有基础数据库),如果是,则显示为该公司名,如果是不同公司品牌则显示为“无品牌”。
3.另外一部分不含特征字符,目前是用filter+手动查找,如果有吻合的就手动输入品牌名。貌似没有函数可以实现在指定列数据查找模糊关键词(关键词库中所有品牌名)并且吻合时提取。而且同上,如果出现多个品牌时做判断是否同一公司旗下。
还请楼主赐教,如果解决起来很麻烦,一点思路或者提示也非常感谢!
校验提示文案
superchao3002
校验提示文案
ibestbuy
校验提示文案
臭臭冬瓜
校验提示文案
square2c
校验提示文案
myfaint009
校验提示文案
kevin0207
校验提示文案
平安数字
校验提示文案
值友8394642856
校验提示文案
novice0513
校验提示文案
米拉小姐姐
校验提示文案
AllenGuan
校验提示文案
怎么回事
校验提示文案
Hivigo
校验提示文案
幸福泡泡001
校验提示文案
咸云
校验提示文案
lanry
校验提示文案
安安静静买买买
校验提示文案
圣托里尼迪奥
校验提示文案
你真他娘的是个天才
校验提示文案
山楂罐头
校验提示文案
外国的月亮圆
校验提示文案
qingshu66666
校验提示文案
lvuk
校验提示文案
快乐的老双鱼
校验提示文案