Czy istnieje funkcja Excela do tworzenia wartości skrótu?

Pracuję z wieloma listami danych, które są wpisane według nazwy dokumentu. Nazwy dokumentów, choć bardzo opisowe, są dość kłopotliwe, jeśli muszę je przeglądać (do 256 bajtów to dużo nieruchomości) i chciałbym móc stworzyć mniejsze pole klucza, które można łatwo odtworzyć w razie potrzeby zrobić VLOOKUPz innego arkusza roboczego lub skoroszytu.

Myślę, że skrót z tytułu, który byłby unikalny i odtwarzalny dla każdego tytułu, byłby najbardziej odpowiedni. Czy dostępna jest funkcja, czy szukam opracowania własnego algorytmu?

Wszelkie przemyślenia lub pomysły na temat tej lub innej strategii?

Nie musisz pisać własnej funkcji - inni już to zrobili.
Na przykład zebrałem i porównałem pięć funkcji mieszających VBA w tej odpowiedzi przepełnienia stosu

Osobiście korzystam z tej funkcji VBA

• jest wywoływany =BASE64SHA1(A1)w programie Excel po skopiowaniu makra do modułu VBA
• wymaga .NET, ponieważ korzysta z 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

Dostosowywanie długości skrótu

• skrót jest początkowo ciągiem znaków o długości 28 znaków (rozróżnia małe i wielkie litery + znaki specjalne)
• Dostosowujesz długość skrótu za pomocą tego wiersza: Const cutoff As Integer = 5
• 4-cyfrowy skrót = 36 kolizji w 6895 liniach = 0,5% wskaźnika kolizji
• 5 cyfr hash = 0 kolizji w 6895 liniach = 0% częstości kolizji

Istnieją również funkcje skrótu ( wszystkie trzy funkcje CRC16 ), które nie wymagają .NET i nie korzystają z zewnętrznych bibliotek. Ale skrót jest dłuższy i powoduje więcej kolizji.

Możesz także pobrać ten przykładowy skoroszyt i bawić się wszystkimi 5 implementacjami skrótu. Jak widać, na pierwszym arkuszu jest dobre porównanie

Nie przejmuję się zbytnio kolizjami, ale potrzebowałem słabego pseudolosowego wiersza 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 Z2jest komórka zawierająca ciąg, który chcesz mieszać.

„MOD” mają na celu zapobieganie przepełnieniu notacji naukowej. 1009jest liczbą pierwszą, mógłby użyć dowolnego X, więc X * 255 < max_int_size. 10 jest arbitralne; użyj czegokolwiek. „Pozostałe” wartości są dowolne (cyfry pi tutaj!); użyj czegokolwiek. Lokalizacja znaków (1,3,5,7,9) jest dowolna; użyj czegokolwiek.

W przypadku stosunkowo małej listy można utworzyć szyfrator (funkcję skrótu biedaka) za pomocą wbudowanych funkcji programu Excel.

Na przykład

 =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ę majstrowania i sprawdzania, aw większości przypadków dość szybko można uzyskać funkcjonalny unikalny identyfikator.

Jak to działa : formuła wykorzystuje pierwszą literę ciągu i stałą literę zaczerpniętą z ciągu środkowego i używa LEN () jako „funkcji wachlowania” w celu zmniejszenia ryzyka kolizji.

CAVEAT : nie jest to skrót, ale gdy musisz szybko coś zrobić i możesz sprawdzić wyniki, aby zobaczyć, czy nie ma kolizji, działa całkiem dobrze.

Edycja: Jeśli ciągi powinny mieć zmienne długości (np. Pełne nazwy), ale zostaną pobrane z rekordu bazy danych o polach o stałej szerokości, będziesz chciał to zrobić w następujący 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 mieszaczem.

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

= + JEŻELI (ORAZ (ISBLANK (D3), ISBLANK (E3)), „”, KOD (TRIM (D3 i E3)) * LEN (TRIM (D3 i E3)) + KOD (MID (TRIM (D3 i E3), $ A $ 1 * * LEN (D3 i E3), 1)) INT (LEN (TRIM (D3 i E3)) $ B $ 1))

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

Używam tego, co daje całkiem dobre wyniki, zapobiegając kolizjom bez konieczności uruchamiania skryptu za każdym razem. Potrzebowałem wartości od 0 do 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 ciągu, bierze wartość każdej z tych liter, dodaje wartość (aby zapobiec tym samym literom w różnych miejscach, dając takie same wyniki), mnoży / dzieli każdą i uruchamia funkcję COS nad sumą.

O ile mi wiadomo, w Excelu nie ma wbudowanej funkcji skrótu - trzeba ją zbudować jako funkcję zdefiniowaną przez użytkownika w VBA.

Należy jednak pamiętać, że dla twojego celu nie sądzę, aby używanie skrótu było wymagane lub naprawdę korzystne! VLOOKUPbędzie działał równie dobrze na 256 bajtach, jak i na mniejszym haszu. Jasne, może być trochę wolniejszy - bit, który jest na pewno tak mały, że jest niezmierzony. A następnie dodanie wartości skrótu jest większym wysiłkiem dla Ciebie - i dla Excela ...