一:前言
很难想像在2022年的今天,还有同志在使用vba来进行编程(如果有,给同志们致以我的敬意)。同时将此案例记录一下,通过vb实现excel单元格内容的匹配拼接。给有需要的同学提供个思路。
1: 效果图:
如图:A列是一个标签库、D列是一些短语
我需要将D列中逗号隔开的短语, 在标签库中找到与之匹配的句子,然后将句子用加号连接起来
2: 实现思路(一些废话)
思路如下:
- 遍历整个A列,获取到A列每个单元格的值
- 将单元格用逗号分割成数组,遍历数组,将数组中的每一个值分别去匹配句子
- 将句子通过加号连接,拼成一个新字符串,并赋值给该单元格右边的空白单元格
如上,其实就是一个简单的双层for循环切割重组的问题,用Java很快就能实现,但需要部署发布,有点复杂。所以我决定用python,但考虑到还要给同事电脑上安装一个python环境,又得配半天环境。那还是回归到excel本身吧,起初我想用excel函数实现,但我只会vlookup之类的简单函数,找几位朋友看过之后,均表示实现不了这个需求。此时一个名叫vba的名词出现在了我的搜索引擎中....
二:步骤
1:在VBA开发之前
进行vba宏编程或者打开宏编程的文件, 需要能支持vba的excel程序,wps只有企业版会员能启用宏编程,我用的是office的excel。excel开启宏编程的步骤如下:
1.1:点击 excle左上角的 “文件” 》 “选项”》 “信任中心”》 “信任中心设置”》 “宏设置”
勾选“启用所有宏” 和 “信任对VBA工程对象模型的访问”
1.2:点击 excle左上角的 “文件” 》 “选项”》 “自定义功能区”
选择开发工具
选择完成之后就可以在功能区看到“开发工具”了
2:宏编程
2.1:编辑器介绍
2.1.1:如上图,我先将 D列定义为 我需要进行匹配的列,同时把需要匹配的短语拷贝到这一列。将E列定义为 匹配结果输出的一列,暂时不做操作
2.1.2:点击左上角的 Visual Basic,此时会弹出宏编程的窗口,如下图所示
这个页面其实跟idea的页面很像,经过简单的摸索,发现
2.1.3:正中间 “通用窗口” 就是我们敲代码的地方
2.1.4:正下方 的 “立即窗口” 就是console输出窗口
2.1.5:右下方的监视窗口就是 debug窗口
2.1.6:在通用窗口代码前面可以添加断点,监视变量的值
2.1.7:在代码敲完之后,鼠标移动到左上角 绿色的 运行按钮, 右键选择 调试,然后点击运行。
2.2:编程
2.2.1:代码:
Sub 格式化标签()
Dim endRow
Dim splitList
Dim cellTitle
cellTitle = "D"
Set endRow = Worksheets("标签库").Range("D999").End(xlUp)
For i = 2 To endRow.Row Step 1
splitList = Split(Range((cellTitle & i)).Value, ",")
Dim tempStr As String
For j = 0 To UBound(splitList) Step 1
Dim fullStr
fullStr = Application.WorksheetFunction.IfError(Application.VLookup(("*" & splitList(j) & "*"), Worksheets("标签库").Range("A2:A99"), 1, 0), splitList(j))
If j = UBound(splitList) Then
tempStr = tempStr + fullStr
Else
tempStr = tempStr + fullStr & "+"
End If
Next
Range((cellTitle & i)).Offset(0, 1).Value = tempStr
tempStr = ""
Next
End Sub
2.2.2:代码解释:
# 定义一个程序,名称是格式化标签
Sub 格式化标签()
# 定义三个变量
Dim endRow
Dim splitList
Dim cellTitle
# cellTitle变量赋值
cellTitle = "D"
# endRow变量赋值,找到D列第999行(这个行数自定)
Set endRow = Worksheets("标签库").Range("D999").End(xlUp)
# 开始整列for循环遍历,从第二行开始,直到最后一行数据,步长为1,表示每次循环 +1
For i = 2 To endRow.Row Step 1
# 使用Split函数将指定单元格的内容按 逗号 分割成多个关键词,通过 Range((cellTitle & i)) 获取单元格
splitList = Split(Range((cellTitle & i)).Value, ",")
# 定义个临时变量,存贮长句子拼接之后的字符串
Dim tempStr As String
# 开始单元格for循环遍历,从单元格的第一个关键词 循环到最后一个值
For j = 0 To UBound(splitList) Step 1
# 定义个临时变量,存贮关键词匹配到的长句子
Dim fullStr
# 先使用VLookup函数 模糊匹配到标签库中A列 第2行开始到第99行的句子,如果匹配到 则会把 句子 赋值给 临时变量 fullStr
# 如果匹配不到则会抛出error,我在前面加了IfError函数,意思是当匹配不到的时候,将error替换成 原关键词,即未找到句子标签时 关键词不变
fullStr = Application.WorksheetFunction.IfError(Application.VLookup(("*" & splitList(j) & "*"), Worksheets("标签库").Range("A2:A99"), 1, 0), splitList(j))
# 为防止多个句子拼接之后末尾 有多余的连接符+号,所以做个判断
# 当循环进行到到 指针j与单元格所有关键词的长度相等,即最后一个关键词时,不添加+号
If j = UBound(splitList) Then
tempStr = tempStr + fullStr
Else
tempStr = tempStr + fullStr & "+"
End If
# 跳出单元格for循环
Next
# 将长句子拼接的字符串tempStr 存到 当前单元格 右侧的空白单元格中。Offset(y,x),y表示上线移动,x表示左右移动
Range((cellTitle & i)).Offset(0, 1).Value = tempStr
# 清空长句子拼接的字符串tempStr,为下一次循环做准备
tempStr = ""
# 跳出整列for循环
Next
# 程序结束
End Sub
如上面的代码所示:一个简单vba程序就好了,在代码中经常出现的函数我列一下,:
- Range("A2")函数:这个函数可以获得指定单元格的内容,函数的参数是 单元格的名字如:A2。如:
Range("A2").Row可以获得单元格A2的下标;
Range("A2").Value可以获得单元格A2的内容; - Offset(0,1)函数:第一个参数是y轴,第二个参数是x轴
如:Range("A2").Offset(0, 1).Value可通过A2的x坐标+1来获得B2的值 - Split("str",",")函数:第一个参数是原值,第二个参数是分隔符,将str字符串通过逗号分隔成数组
- IfError(#N/A,"未匹配")函数:第一个参数是可能出错的值,第二个参数是出错后替换的值,可将报错的函数替换成指定的字符
- VLookup("A2","B2:B9",1,0)函数:第一个参数是待匹配的单元格,第二个参数是要匹配的区域,第三个参数是匹配区域的第几列,最后一个参数0是模糊匹配,1是精确匹配
- UBound(list)函数:参数是数组,主要用来计算数组长度
2.3:运行
直接点击左上角的 宏,找到写好的程序,点击执行,就可以看到在 E列出现了匹配重组之后的长句子啦
三:结语
在办公室敲着这古老的代码,感受着窗外的阳光在叶隙间闪烁跳跃,看到的很多宏编程相关的文章多是数年前或者数十年前的文章。听闻宏编程流行于千禧年之际,那时盛况仿佛此时林间的蝉鸣一样热烈,恍然间自己也在进行一场穿越,见证着计算机语言发展的历史...
(编写不易,转载留名,出入平安!!!)