Funkcia SpecMatch – ako vyhľadať približné názvy v zozname

 

Tento článok je venovaný prevažne tým, ktorí ste si už ten rôčik vytrpeli pri spravovaní reportov a aj napriek tomu, že ovládate nahrať aké – také makro, časové dispozície Vám nedovoľujú špekulovať nad istými nástrahami pri práci.

Inak povedané deadline je deadline a niektoré rutinné veci sa stávajú zvykom alebo skôr otravou analytika.

Pre mňa osobne bola nočnou morou fakturácia. Asi každý finančný analytik (controller) sa s tým stretol. Máte zoznam dodávateľov – odberateľov, na základe ktorého fakturantky kontinuálne vystavujú faktúry, resp. evidujú platby.

Avšak žiaden z nás nie je robot a pri pracovnej vyťaženosti sa stane, že sa na faktúre / platbe firmy ABC s.r.o. vyskytne názov ABC_s.r.o alebo obdobná chybička. Tým pádom sa síce môžeme pokúsiť spoľahnúť na funkcie Match alebo Vlookup, ktoré pri zadávaní posledného parametru ponúkajú možnosť 0 – presné vyhľadávanie alebo 1 – približné vyhľadávanie. Avšak 1 - približné vyhľadávanie nie je dostatočne presné!. viď nasledujúca tabuľka:

Táto skutočnosť nás núti upozorniť fakturantky na lepšiu precíznosť alebo ručne upravovať názvy firiem v záznamoch tak, aby priebežne mohli byť použiteľné pre tvorbu finančného prehľadu, čo sa v prípade, že spravujete veľké množstvo faktúr môže stať nočnou morou.

V prípade, že máte názvy pozostávajúce z rôznorodých znakov, dlhé názvy firiem apod. môžete použiť proces, ktorý:

1) Očistí text o diakritiku

2) Zmení všetky znaky na malé (veľké)

3) Vyčistí názov o nežiaduce znaky ako bodky, čiarky, bodkočiarky, pomlčky, atd.

4) takto očistený text porovná s hodnotami zo zoznamu a priradí im pravý názov

Túto funkciu som nazval SpecMatch, kvázi špeciálna verzia štandardnej funkcie Match. VBA kód pripraví vyhľadávanú hodnotu s hodnotami zo zoznamu a porovnáva počet jednotlivých povolených znakov (celkovo ich je 36).

Inak povedané: spočíta koľko znakov “a” sa nachádza v hľadanom slove a porovná s počtom znakov “a” v prvom slove zo zoznamu. Ak sa počty zhodujú prejde na ďalší znak. V prípade, že procedúra dosiahne zhodu 36 znakov – funkcia vráti slovo zo zoznamu ako originál. VBA kód vyzerá nasledovne:

Function SpecMatch(text1 As String, RNG As Range)

'Deklarácia premenných
Dim Stare As String
Dim Nove As String
Dim i
Dim j
Dim r1
Dim r2
Dim text2
Dim text2c

'Deklarácia konštant
Const Znaky = "abcdefghijklmnopqrstuvxyzw1234567890"
Const SpinavePismenka = "áćĆĎéÉíĹĺńŃóÓŕśŔŚťúÚýźŹÁčČďěĚÍĽľňŇôÔřšŘŠŤůŮÝžŽ"
Const CistePismenka = "acCDeEiLlnNoOrsRStuUyzZAcCdeEiLlnNoOrsRSTuUYzZ"
Const NevhodneZnaky = ",.; &@%_=-/" 'Znaky, podľa vlastných potrieb - skúseností

'Očistíme hľadané slovo
For i = 1 To Len(SpinavePismenka)
    Stare = Mid(SpinavePismenka, i, 1)
    Nove = Mid(CistePismenka, i, 1)
    text1 = Replace(text1, Stare, Nove)
Next
For i = 1 To Len(NevhodneZnaky)
    Stare = Mid(NevhodneZnaky, i, 1)
    Nove = ""
    text1 = Replace(text1, Stare, Nove)
Next

text1 = LCase(text1)

'Prehľadávanie
For Each text2 In RNG

'Očisťujeme každé slovo z RNG pred porovnaním s hľadaným slovom
    text2c = text2

    For i = 1 To Len(SpinavePismenka)
        Stare = Mid(SpinavePismenka, i, 1)
        Nove = Mid(CistePismenka, i, 1)
        text2c = Replace(text2c, Stare, Nove)
    Next
   
    For i = 1 To Len(NevhodneZnaky)
        Stare = Mid(NevhodneZnaky, i, 1)
        Nove = ""
        text2c = Replace(text2c, Stare, Nove)
    Next
   
    text2c = LCase(text2c)
    r1 = 0
    r2 = 0
    For i = 1 To 36
        'Nájde množstvo znaku(i) v text1
        For j = 1 To Len(text1)
            If InStr(j, text1, Mid(Znaky, i, 1)) > 0 Then
                j = InStr(j, text1, Mid(Znaky, i, 1))
                r1 = r1 + 1
            End If
        Next j
        'Nájde množstvo znaku(i) v text2c
        For j = 1 To Len(text2c)
            If InStr(j, text2c, Mid(Znaky, i, 1)) > 0 Then
                j = InStr(j, text2c, Mid(Znaky, i, 1))
                r2 = r2 + 1
            End If
        Next j
        'Ak sa počet znakov nezhoduje, prejde na ďalšie slovo z RNG
        If r1 <> r2 Then
            GoTo DalsizRng
        End If
        'Ak nastala zhoda pre všetkých 36 možných znakov, vráti hodnotu z RNG
        If i = 36 Then
            SpecMatch = text2
            Exit Function
        End If
    Next i

DalsizRng:

Next text2

'V prípade, že nenájde žiadnu zhodu, vráti #VALUE!
SpecMatch = "#VALUE!"

End Function

Vytvorenú funkciu použijeme nasledovne: =SpecMatch(“adresa bunky”;”Adresa zoznamu”), v template súbore je napr.:

 

Výsledok – správny názov potom môžete nahradiť pôvodným a pomocou štandardných vyhľadávacích funkcií spracovať potrebné analýzy. Hotový template si môžete stiahnuť kliknutím na Download a nezabudnite na like na FB.