Czy istnieje funkcja programu Excel do tworzenia wartości hash?

Nie musisz pisać własnej funkcji - inni już to zrobili dla ciebie.
Na przykład zebrałem i porównałem pięć funkcji haszujących VBA na tym odpowiedź stackoverflow

Osobiście używam tej funkcji VBA

• jej wywołanie z =BASE64SHA1(A1) w Excelu po skopiowaniu makra do modułu VBA **
• wymaga . NET, ponieważ używa biblioteki “Microsoft MSXML” (z późnym wiązaniem)

Public Function BASE64SHA1(ByVal sTextToHash As String)

    Dim asc As Object
    Dim enc As Object
    Dim TextToHash() As Byte
    Dim SharedSecretKey() As Byte
    Dim bytes() As Byte
    Const cutoff As Integer = 5

    Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")

    TextToHash = asc.GetBytes_4(sTextToHash)
    SharedSecretKey = asc.GetBytes_4(sTextToHash)
    enc.Key = SharedSecretKey

    bytes = enc.ComputeHash_2((TextToHash))
    BASE64SHA1 = EncodeBase64(bytes)
    BASE64SHA1 = Left(BASE64SHA1, cutoff)

    Set asc = Nothing
    Set enc = Nothing

End Function

Private Function EncodeBase64(ByRef arrData() As Byte) As String

    Dim objXML As Object
    Dim objNode As Object

    Set objXML = CreateObject("MSXML2.DOMDocument")
    Set objNode = objXML.createElement("b64")

    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.text

    Set objNode = Nothing
    Set objXML = Nothing

End Function

Dostosowanie długości hasha

• początkowo hash jest 28 znakowym łańcuchem unicode (wielkość liter + znaki specjalne)
• długość hasha można dostosować za pomocą tej linii: Const cutoff As Integer = 5
• 4 cyfry hasha = 36 kolizji w 6895 liniach = 0. 5 % współczynnik kolizji
• 5 cyfrowy hash = 0 kolizji w 6895 liniach = 0 % współczynnik kolizji

Istnieją również funkcje hashujące (wszystkie trzy funkcje CRC16), które nie wymagają .NET i nie używają zewnętrznych bibliotek. Ale hash jest dłuższy i produkuje więcej kolizji.

Możesz też po prostu pobrać ten przykładowy skoroszyt i pobawić się wszystkimi 5 implementacjami hash. Jak widzisz, na pierwszym arkuszu jest dobre porównanie

Nie zależy mi zbytnio na kolizjach, ale potrzebowałem słabego pseudorandomizatora wierszy opartego na polu łańcuchowym o zmiennej długości. Oto jedno szalone rozwiązanie, które działało dobrze:

=MOD(MOD(MOD(MOD(MOD(IF(LEN(Z2)>=1,CODE(MID(Z2,1,1))+10,31),1009)*IF(LEN(Z2)>=3,CODE(MID(Z2,3,1))+10,41),1009)*IF(LEN(Z2)>=5,CODE(MID(Z2,5,1))+10,59),1009)*IF(LEN(Z2)>=7,CODE(MID(Z2,7,1))+10,26),1009)*IF(LEN(Z2)>=9,CODE(MID(Z2,9,1))+10,53),1009)

Gdzie Z2 jest komórką zawierającą ciąg, który chcesz haszować.

“MOD "s są tam, aby zapobiec przepełnieniu do notacji naukowej. 1009 jest liczbą pierwszą, można użyć czegokolwiek X, tak aby X było mniejsze od max_int_size. 10 jest arbitralne; użyj czegokolwiek. Wartości "Else” są arbitralne (cyfry pi tutaj!); użyj czegokolwiek. Położenie znaków (1,3,5,7,9) jest dowolne; użyj czegokolwiek.

Dla stosunkowo małej listy możesz stworzyć scrambler (funkcja haszująca biednego człowieka) używając wbudowanych funkcji Excela.

Np.

=CODE(A2)*LEN(A2) + CODE(MID(A2,$A$1,$B$1))*LEN(MID(A2,$A$1,$B$1))

Tutaj A1 i B1 przechowują losową literę początkową i długość łańcucha.

Trochę zabawy i sprawdzania, a w większości przypadków można dość szybko uzyskać działający unikalny identyfikator.

Jak to działa : Wzór wykorzystuje pierwszą literę ciągu oraz stałą literę z połowy ciągu i używa LEN() jako “funkcji wachlowania”, aby zmniejszyć szansę na kolizje.

CAVEAT : to nie jest hash, ale kiedy musisz coś szybko zrobić i możesz sprawdzić wyniki, aby zobaczyć, że nie ma kolizji, działa to całkiem dobrze.

Edit: Jeśli twoje ciągi znaków powinny mieć zmienną długość (np. pełne imiona), ale są pobierane z bazy danych z polami o stałej szerokości, będziesz chciał to zrobić w ten sposób:

=CODE(TRIM(C8))*LEN(TRIM(C8))
       +CODE(MID(TRIM(C8),$A$1,1))*LEN(MID(TRIM(C8),$A$1,$B$1))

tak, że długości są znaczącym scramblerem.

Używam tego, co daje całkiem dobre wyniki z zapobieganiem kolizji bez konieczności uruchamiania skryptu za każdym razem. Potrzebowałem wartości w zakresie 0 - 1.

=ABS(COS((CODE(MID(A2,ROUNDUP(LEN(A2)/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)/5,0),1))+100)/CODE(MID(A2,ROUNDUP(LEN(A2)/3,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*8/9,0),1))+25)/CODE(MID(A2,ROUNDUP(LEN(A2)*6/9,0),1))*(CODE(MID(A2,ROUNDUP(LEN(A2)*4/9,0),1))-25))/LEN(A2)+CODE(A2)))

Wybiera litery z całego łańcucha, bierze wartość każdej z tych liter, dodaje wartość (aby zapobiec sytuacji, w której te same litery w różnych miejscach dają takie same wyniki), mnoży/rozdziela każdą z nich i uruchamia funkcję COS nad całością.

Możesz spróbować tego. Uruchom Pseudo# na dwóch kolumnach:

=+IF(AND(ISBLANK(D3),ISBLANK(E3)),“”,CODE(TRIM(D3&E3))+LEN(TRIM(D3&E3))+CODE(MID(TRIM(D3&E3), $A$1}LEN(D3&E3),1))INT(LEN(TRIM(D3&E3))$B$1))

Gdzie A1 i B1 przechowują losowe nasiona wprowadzone ręcznie: 0

Według mojej wiedzy nie ma funkcji haszującej wbudowanej w Excela - musiałbyś zbudować ją jako funkcję zdefiniowaną przez użytkownika w VBA.

Jednak proszę zauważyć, że dla twojego celu nie sądzę, aby używanie hasha było wymagane lub naprawdę korzystne! VLOOKUP będzie działał tak samo dobrze na 256 bajtach, jak i na mniejszym haszu. Jasne, może być odrobinę wolniejszy - bit, który jest na pewno tak mały, że jest niezmierzony. A potem dodawanie wartości hash to więcej wysiłku dla Ciebie - i dla Excela…