LibreOffice Calc에 대한 Fuzzy Lookup

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