LibreOffice 的 Fuzzy Lookup Calc

YLC

FUZZYLOOKUP() 描述

我们都知道著名的 VLOOKUP() 函数可以帮助我们组合来自不同表的数据。然而，这个函数有一个显着的缺点——它不能组合相似的值，也就是说，如果单词中有错误，那么就不会匹配。

为了能够组合近似值，我们可以创建自己的函数。我们将其称为 FuzzyLookup()。

假设我们有两个列表。两者具有大致相同的元素，但它们的写法可能略有不同。任务是为第一个列表中的每个元素找到第二个列表中最相似的元素，即实现对最近的最大相似文本的搜索。

在这种情况下，最大的问题是如何考虑“相似性”的标准。只是匹配字符的数量？是连续比赛的次数吗？应该考虑字符大小写还是空格？如何处理短语中单词的不同排列？有很多选择，并且没有单一的解决方案 - 对于每种情况，其中一个会比其他更好。

在我们的例子中，我们实现了最简单的选项 - 按字符匹配的最大数量进行搜索。它并不完美，但在大多数情况下都可以很好地工作。

BASIC 函数 FuzzyLookup 的代码

要添加 FuzzyLookup 函数，请打开 Tools - Macros - Edit Macros... 菜单，选择 Module1 并将以下文本复制到模块中：


Function FuzzyLOOKUP(LookupValue As String, SrcTable As Variant, Optional SimThreshold As Single) As String 
  ' moonexcel.com.ua 
  Dim Str       As String  
  Dim CellArray As Variant
  Dim StrArray  As Variant
  
  If IsMissing(SimThreshold) Then SimThreshold  = 0
  
  Str      = LCase(LookupValue)
  StrArray = Split(Str)
  StrExt   = UBound(StrArray)  
            
  For Each Cell In SrcTable
                          
    CellArray = Split(LCase(Cell))
    CellExt   = UBound(CellArray)    	    
    CellRate  = 0
	
    ' 我们检查搜索短语中的每个单词 
    For x = 0 To StrExt 
    
      StrWord = StrArray(x)	  
      If Len(StrWord) = 0 Then GoTo continue_x
	  MaxStrWordRate = 0
	  
      ' 我们检查原始值表中下一个单元格中的每个单词 
      For i = 0 To CellExt
        
        CellWord = CellArray(i)
		If Len(CellWord) = 0 Then GoTo continue_i
   
        FindCharNum = OccurrenceNum(StrWord, CellWord)
        StrWordRate = FindCharNum / Max(Len(StrWord),Len(CellWord))
		
        If StrWordRate > MaxStrWordRate Then MaxStrWordRate = StrWordRate
		continue_i:
      Next i		    
		    		    
      CellRate = CellRate + MaxStrWordRate
	  continue_x:
    Next x               
        
    ' 我们保留最佳匹配 
    If CellRate > MaxCellRate Then    
      MaxCellRate = CellRate
      BestCell    = Cell          
       
      FindCharNum = OccurrenceNum(Str, Cell)
      SimRate     = FindCharNum / Max(Len(Str),Len(Cell))
    End If       
        
  Next Cell
    
  IF SimRate >= SimThreshold Then 
    IF SimThreshold = -1 Then
      ReturnValue = BestCell + " (" + Format(SimRate, "0.00") + ")"
    ElseIf SimThreshold = -2 Then
      ReturnValue = Format(SimRate, "0.00")
    Else
      ReturnValue = BestCell
    End If
  Else 
    ReturnValue = ""
  End If    
  
  FuzzyLOOKUP = ReturnValue
End Function


Function OccurrenceNum(ByVal SourceString As String, ByVal TargetString As String)
  For i = 1 To Len(SourceString)	         		        	        
    ' 我们正在寻找每个符号的出现次数 
    Position = InStr(1, TargetString, Mid(SourceString, i, 1), 1)  
    ' 我们增加巧合的计数器 
    If Position > 0 Then		  
      Count = Count + 1
      ' 删除找到的符号 
      TargetString = Left(TargetString, Position - 1) + Right(TargetString, Len(TargetString) - Position)   
    End If
  Next i    
  OccurrenceNum = Count
End Function


Function Max(ByVal value1 As Variant, ByVal value2 As Variant)  
  If value1 > value2 Then
	Result = value1
  Else
	Result = value2
  End If
  Max = Result
End Function

接下来，关闭 Macro Editor 并返回到 LibreOffice Calc 工作表 - 现在您可以使用我们新的 FuzzyLookup() 函数。

使用 YouLibreCalc 扩展

您还可以通过设置扩展名“来使用函数 FUZZYLOOKUP() YouLibreCalc.oxt ”。此后，该功能将在所有在 LibreOffice Calc 中打开的文件中可用。

VIDEO TUTORIAL