查找哪些单元格的levenshtein距离最小 [英] Find which cells have the smallest levenshtein distance
问题描述
所以,我有这个函数,它将快速返回两个字符串之间的Levenshtein距离:
So, I have this Function which will quickly return the Levenshtein Distance between two Strings:
Function Levenshtein(ByVal string1 As String, ByVal string2 As String) As Long
Dim i As Long, j As Long
Dim string1_length As Long
Dim string2_length As Long
Dim distance() As Long
string1_length = Len(string1)
string2_length = Len(string2)
ReDim distance(string1_length, string2_length)
For i = 0 To string1_length
distance(i, 0) = i
Next
For j = 0 To string2_length
distance(0, j) = j
Next
For i = 1 To string1_length
For j = 1 To string2_length
If Asc(Mid$(string1, i, 1)) = Asc(Mid$(string2, j, 1)) Then
distance(i, j) = distance(i - 1, j - 1)
Else
distance(i, j) = Application.WorksheetFunction.Min _
(distance(i - 1, j) + 1, _
distance(i, j - 1) + 1, _
distance(i - 1, j - 1) + 1)
End If
Next
Next
Levenshtein = distance(string1_length, string2_length)
End Function
我想在"A"列中的所有单元格之间进行快速比较,并返回其中Levenshtein距离较小的单元格.我将如何进行所有这些比较?
I want to perform a fast comparison between all cells in the "A" column and return which ones have a "small" Levenshtein distance. How would I make all these comparisons?
推荐答案
您是否要查找哪些字符串组合的levenshtein距离较小,或者总体而言,每个字符串与所有其他字符串的相似度/相似度如何?
Do you want to find which combinations of strings have small levenshtein distances or just overall how similar/disimilar each string is with all the other strings?
如果是前者,则应该可以正常工作:
If it is the former this should work fine:
您只需复制并粘贴转置值即可创建所有这些标头(如Dale所述).您可以使用条件格式突出显示最低结果.
You just copy and paste transposed values to create all those headers(as Dale commented). You can use the conditional formatting to highlight the lowest results.
或者,如果您希望返回实际的字符串,则应该可以使用以下命令:
Or if you want the actual strings to return you should be able to use this:
=IF(AND(Levenshtein($A28,B$27)>0,Levenshtein($A28,B$27)<=3),$A28&"/"&B$27,"")
如果要将返回的组合放在单个列中,只需复制并粘贴唯一值.
Just copy and paste unique values if you want the returned combinations in a single column.
祝你好运.
这篇关于查找哪些单元格的levenshtein距离最小的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!