vba 数字转文本_基于编辑距离算法的文本相似度3种解决方案

   点击 “ 数字化审计 ”，可以关注哦！

开篇啰嗦话

如何有效的对两个有关联、但内容相似的文本进行模糊对比分析以发现审计疑点是审计数据分析业务中经常要面临的问题。本文基于文本相似度的编辑距离算法实现了对两个相似文本的进行了模糊对比分析，能有效地解决了审计实务中相似文本模糊匹配的难题，为快速锁定审计线索提供有效帮助

基于编辑距离的文本相似度3种解决方案 文本相似度对于审计的必要性

大数据时代，企业的各项生产经营业务大多通过各类信息系统加以管控。审计在借助信息技术开展业务数据分析时，常常需要将多个业务关联的系统数据进行对比分析，以期发现异常线索。

例如，通过对比检查财务系统中应收、应付帐款的客户名称和合同系统中的合同相对人来分析合同收付款实际执行情况，通过对比检查投资管理系统和财务系统中的投资项目名称来分析项目实际投资情况，通过对比检查合同系统和招投标系统的厂商名称来分析合同招投标情况。

但由于信息系统设计缺乏有效的统一规划，导致不同信息系统之间的数据关联性差，同一信息在不同的信息系统之间会存在不一致现象。例如上文中提到的合同收付款单位名称与合同相对人名称不完全一致;投资管理系统与财务系统中的项目名称也不完全一致;合同系统与招投标系统中的项目名称(合同名称)、厂商名称也不完全一致。对这种不完全一致的对比项进行对比如果用电子表格Vlookup函数和sql语言的like句式是无法有效解决，而文本相似度算法可以解决这种模糊匹配的问题。

文本相似度相关的算法有很多，编辑距离是其中一个有代表性的算法。

本文基于编辑距离算法，给出了excel中VBA、SQlServer中的sql、python中的包，三种不同的解决方案。你可以根据你的应用场景选择相应的解决方案。 这三种算法，你可以直接复制代码，解决你的实际问题。

01.编辑距离

编辑距离(Edit Distance)，又称Levenshtein距离，俄罗斯科学家Vladimir Levenshtein在1965年提出这个概念，又叫 Levenshtein 距离。是指两个字串之间，由一个转成另一个所需的最少编辑操作次数。许可的编辑操作包括将一个字符替换成另一个字符，插入一个字符，删除一个字符。如比较S1=“内审协会”和s2=“中国内审协会”，就可以通过在S1中插入“中”、“国”两个汉字实现与S2一直，故S1和S2编辑距离为2。

一般来说，编辑距离越小，两个串的相似度越大。实际应用中转换成相似度。

编辑距离相似度=1-编辑距离/max(字符串1长度，字符串2的长度)

其核心算法：

设计一个二维表格，表格列数为字符串1的长度加1，行数为字符串2的长度加1。

表格的第1行按照列，自左往右，依序填列0,1,2，...字符串1的长度n;

表格的第1列按照行，自上往下，依序填列0,1,2，...字符串2的长度m;

然后自第2行，第2列开始，自左往右，填充数据，该数据的规则是：

如果两个字符串对应位置的字符相同，则取左上角单元格的值；如果不同，则取该单元格左方、左上方、上方的三个但单元格的值的最小值+1。

重复上述操作，直到填满最后一个单元格，其数字就是编辑距离。

下图是在excel中利用VBA，编写的DigitalAuditEditDistance自定义函数的引用结果。怎么样，这种场景你是否经常面临，例如转分包合同判定。

为了更好的理解，我制作了操作视频，演示了字符串“大号中山河”与“大美河山”、“大号中河山”与“大美河山”的编辑距离及相似度计算过程。

先来一个代码量最简单的方案，用python解决！因为python有现成的、可以直接利用的包。

02.Python中的Levenshtein方案

python最大的优势这里再次体现。无需你编程，因为已经有人提供了相应的包，这个包就是python-Levenshtein，他可以用于计算字符串编辑距离和相似度。 该包只是给出了编辑距离，要计算相似度，需要你自行编写,不过我已经给你写成了详细代码。

02.Excel中的VBA方案

打开excel，同时输入“ALT+F11”键，启动VBA编辑器。选择“插入”-“模块”。

详细代码 ，如下所示。

Function DigitalAuditEditDistance(ByVal string1 As String, ByVal string2 As String) As Long
Dim i As Long, j As Long, lenOfStr1 As Long, lenOfStr2 As Long
Dim ed() As Long, AsciiOfStr1() As Long, AsciiOfStr2() As Long
Dim leftValue As Long, TopValue  As Long, leftAndTopValue As Long, minValue As Long, MaxL As Long
lenOfStr1 = Len(string1)
lenOfStr2 = Len(string2)
ReDim ed(0 To lenOfStr1, 0 To lenOfStr2)
ReDim AsciiOfStr1(lenOfStr1)
ReDim AsciiOfStr2(lenOfStr2)ed(0, 0) = 0
For i = 1 To lenOfStr1ed(i, 0) = iAsciiOfStr1(i) = Asc(LCase(Mid$(string1, i, 1)))
Next
For j = 1 To lenOfStr2ed(j, 0) = jAsciiOfStr2(j) = Asc(LCase(Mid$(string2, j, 1)))
Next

For i = 1 To lenOfStr1
    For j = 1 To lenOfStr2
        If AsciiOfStr1(i) = AsciiOfStr2(j) Thened(i, j) = ed(i - 1, j - 1)
        Else
            leftValue = ed(i - 1, j) + 1
            TopValue = ed(i, j - 1) + 1
            leftAndTopValue = ed(i - 1, j - 1) + 1
            If TopValue                 If TopValue                     minValue = TopValue
                Else
                    minValue = leftAndTopValue
                End If
            Else
                If leftValue                     minValue = leftValue
                Else
                    minValue = leftAndTopValue
                End If
            End Ifed(i, j) = minValue
        End If
    Next
Next

MaxL = lenOfStr1
If lenOfStr2 > MaxL Then
    MaxL = lenOfStr2
End If
DigitalAuditEditDistance = 100 - CLng((ed(lenOfStr1, lenOfStr2) / MaxL) * 100)

End Function

然后可以在excel里直接引用上面定义的编辑距离函数，如下图所示。 这是一个非常棒的功能！你可以自定义属于你自己的函数

03.SqlServer中的sql方案

其原理是一样的，只不过是需要用sql的方式去实现,直接给出代码吧。

ALTER  FUNCTION [dbo].[editdistance_sql](
  @string1 nvarchar(3999),
  @string2 nvarchar(3999))
RETURNS int
AS
BEGIN
  DECLARE @string1_len int, @string2_len int,@maxL int,@result int,@result1 decimal(5,2)
  DECLARE @i int, @j int, @s1_char nchar, @c int, @c_temp int
  DECLARE @cv0 varbinary(8000), @cv1 varbinary(8000)
  SELECT @string1_len = LEN(@string1),@string2_len = LEN(@string2),@cv1 = 0x0000, @j = 1, @i = 1, @c = 0

  WHILE @j <= @string2_len
    SELECT @cv1 = @cv1 + CAST(@j AS binary(2)), @j = @j + 1
  WHILE @i <= @string1_len

  BEGIN
    SELECT
      @s1_char = SUBSTRING(@string1, @i, 1), @c = @i,@cv0 = CAST(@i AS binary(2)),@j = 1
    WHILE @j <= @string2_len
    BEGIN
      SET @c = @c + 1
      SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j-1, 2) AS int) +
        CASE WHEN @s1_char = SUBSTRING(@string2, @j, 1) THEN 0 ELSE 1 END
      IF @c > @c_temp SET @c = @c_temp
      SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j+1, 2) AS int)+1
      IF @c > @c_temp SET @c = @c_temp
      SELECT @cv0 = @cv0 + CAST(@c AS binary(2)), @j = @j + 1
    END
    SELECT
      @cv1 = @cv0,
      @i = @i + 1
  END
  select @maxL=case when @string1_len>@string2_len then @string1_len else @string2_len end
  select @result1=CASE WHEN @maxL = 0 THEN 1-@c/1 ELSE 1-(@c *1.0 /@maxL) END
  select @result=@result1*100
  RETURN @result
  --select dbo.editdistance_sql('大号中山河','大美河山')
END

拓展延伸——文本的伪相似度

在文本相似度处理过程中，有些词出现次数较多，但对于相似度的判断不起核心作用，例如"的"、"是"、"在"等；有些词的存在明显增加相似度但实际上却不应该判定为相似的，导致出现伪相似度现象，例如合同台账的合同名称中经常出现“合同”、“建设工程”、“工程建设”、“技术服务”，单位名称中经常出现“公司”、“集团”、“中国”等。这两类词对判定相似度有反向影响，对找到结果毫无帮助，必须过滤掉，这些词叫做" 停用词"(stopwords) 。 因此，上述三种文本相似度的解决方案在实际使用过程中，还存在需要完善的地方。

友情提示：replace!

随着轻轻的风轻轻地飘

历经的路都会刻骨铭心

如果你觉得有用，记得点击右下角“在看”按钮，并写下宝贵的意见，也可在公众号下扫码加群。

欲见详情，静候下文

原创文章欢迎转载！

请注明:本文首发于

"数字化审计"公众号