VBA实现关键词匹配

最新推荐文章于 2022-11-02 19:21:08 发布

爱吃绿豆糕

最新推荐文章于 2022-11-02 19:21:08 发布

阅读量1.8k

点赞数 1

分类专栏： # 0011-Excel 文章标签： vba excel

本文链接：https://blog.csdn.net/HIMOJITO/article/details/117783569

版权

这篇博客介绍了如何使用VBA在Excel中进行关键词匹配。在匹配sheet页，将源数据放在A/B列，配置好词库后，通过选择不同匹配模式（模式一到模式六），可以在C列得到匹配结果。词库sheet页则用于设置关键词和标记词句，通过B/(F&G)/(K&L)列和C/H/M列完成配置。

摘要由CSDN通过智能技术生成

一、界面展示

匹配sheet页：在A/B列粘贴需要打标的源数据，然后在词库页面配置好关键词，最后在H列选择匹配模式，即可在C列得到达标结果
词库sheet页：按识别逻辑，在B/(F&G)/(K&L)列设置需识别的关键词，然后在C/H/M列匹配上想标记的词句，即完成词库配置

二、代码详情

1. 单列匹配

Sub 单列匹配()

Dim tiaojian1 As Range

Dim tiaojian2 As Range

Dim neirong1 As Range

Dim neirong2 As Range

Dim ZZ1 As String

Dim ZZ2 As String

Dim KK1 As String

Dim KK2 As String

Range("A1") = "内容1"

Range("B1") = "内容2"

Range("C1") = "打标"	

Set neirong1 = [a2]

Set neirong2 = [B2]


Do Until Trim(neirong1) = ""

Set tiaojian1 = Worksheets("词库").Range("b4")

ZZ1 = CStr(neirong1)

KK1 = CStr(tiaojian1)

ZZ1 = VBA.UCase(ZZ1)

KK1 = VBA.UCase(KK1)


Do Until Trim(KK1) = "" Or (ZZ1 Like "*" & KK1 & "*")

Set tiaojian1 = tiaojian1.Offset(1, 0)

KK1 = CStr(tiaojian1)

KK1 = VBA.UCase(KK1)

Loop

If tiaojian1 <> "" Then

neirong1.Offset(0, 2) = tiaojian1.Offset(0, 1)

Else

neirong1.Offset(0, 2) = "其他"

End If

Set neirong1 = neirong1.Offset(1, 0)

ZZ1 = CStr(neirong1)

ZZ1 = VBA.UCase(ZZ1)

Loop

MsgBox "OK"

End Sub

2. 模式一

Sub 模式一()

Dim tiaojian1 As Range

Dim tiaojian2 As Range

Dim neirong1 As Range

Dim neirong2 As Range

Dim ZZ1 As String

Dim ZZ2 As String

Dim KK1 As String

Dim KK2 As String

Range("A1") = "内容1"

Range("B1") = "内容2"

Range("C1") = "打标"


Set neirong1 = [a2]

Set neirong2 = [B2]

Do Until Trim(ne