Cu toții cunoaștem binecunoscuta funcție VLOOKUP() care ne ajută să combinăm datele din diferite tabele. Cu toate acestea, această funcție are un dezavantaj semnificativ - nu poate combina valori similare, adică dacă există o eroare în cuvânt, atunci nu va exista nicio potrivire.
Pentru a putea combina valorile aproximative, putem crea propria noastră funcție. Să-i spunem FuzzyLookup().
Să ne imaginăm că avem două liste. Ambele au aproximativ aceleași elemente, dar pot fi scrise ușor diferit. Sarcina este de a găsi pentru fiecare element din prima listă cel mai asemănător element din a doua listă, adică. implementați o căutare pentru cel mai apropiat text maxim similar.
Marea întrebare, în acest caz, este ce să ia în considerare criteriul „asemănării”. Doar numărul de caractere care se potrivesc? Este numărul de meciuri consecutive? Ar trebui luate în considerare majuscule sau spații? Ce să faci cu aranjarea diferită a cuvintelor într-o frază? Există multe opțiuni și nu există o soluție unică - pentru fiecare situație una sau alta va fi mai bună decât altele.
În cazul nostru, implementăm cea mai simplă opțiune - căutarea după numărul maxim de potriviri de caractere. Nu este perfect, dar funcționează destul de bine în majoritatea situațiilor.
Pentru a adăuga funcția FuzzyLookup, deschideți meniul Tools - Macros - Edit Macros..., selectați Module1 și copiați următorul text în modul:
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
' Verificăm fiecare cuvânt din expresia de căutare
For x = 0 To StrExt
StrWord = StrArray(x)
If Len(StrWord) = 0 Then GoTo continue_x
MaxStrWordRate = 0
' Verificăm fiecare cuvânt din celula următoare din tabelul original de valori
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
' Păstrăm cel mai bun meci
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)
' Căutăm apariția fiecărui simbol
Position = InStr(1, TargetString, Mid(SourceString, i, 1), 1)
' Mărim contorul de coincidențe
If Position > 0 Then
Count = Count + 1
' Eliminați simbolul găsit
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
Apoi, închideți Macro Editor și reveniți la foaia de lucru LibreOffice Calc - acum puteți utiliza noua noastră funcție FuzzyLookup().
De asemenea, puteți utiliza funcția FUZZYLOOKUP() setând extensia " YouLibreCalc.oxt „. După aceea, această funcție va fi disponibilă în toate fișierele care vor fi deschise în LibreOffice Calc.