microsoft excel 2010

Существует ли функция Excel для создания хэш-значения

Я работаю с рядом списков данных, которые вводятся по имени документа. Имена документов, хотя и очень описательные, довольно громоздки, если мне нужно их просмотреть (до 256 байтов — это много недвижимости), и я хотел бы иметь возможность создать ключевое поле меньшего размера, которое легко воспроизвести в случае необходимости. для выполнения VLOOKUP из другого рабочего стола или книги.

Я думаю, что хэш из названия, который был бы уникальным и воспроизводимым для каждого названия, был бы наиболее подходящим. Доступна ли функция, или я рассматриваю возможность разработки собственного алгоритма?

Есть мысли или идеи по этой или другой стратегии?

Вам не нужно писать свою собственную функцию-другие уже сделали это за вас.
Например, в этом ответе я собрал и сравнил пять хеш-функций VBA.

Лично я использую эту функцию VBA

он вызывается с помощью =BASE64SHA1(A1) в Excel после того, как вы скопировали макрос в модуль VBA
требует .NET,так как использует библиотеку "Microsoft MSXML" (с поздним связыванием)

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

Настройка длины хэша

хэш изначально представляет собой строку юникода длиной 28 символов (с учетом регистра+специальные символы)
Вы настраиваете длину хеша с помощью этой строки: Const cutoff As Integer = 5
хэш из 4 цифр=36 коллизий в 6895 строках=0,5 % коллизий
5 разрядов хэша=0 коллизий в 6895 строках=0 % коллизий

Существуют также хэш-функции ( все три функции CRC16 ), которые не требуют .NET и не используют внешние библиотеки. Но хэш длиннее и производит больше столкновений.

Вы также можете просто загрузить этот пример рабочей книги и поиграть со всеми 5 реализациями хеширования. Как видите, на первом листе есть хорошее сравнение

Я не очень забочусь о коллизиях,но мне нужен был слабый псевдорандомизатор строк на основе строкового поля переменной длины.Вот одно безумное решение,которое хорошо сработало:

=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)

Где Z2 — это ячейка, содержащая строку, которую вы хотите хэшировать.

«MOD» существуют для предотвращения переполнения научной нотации. 1009 — простое число, можно использовать любое число X, чтобы X*255 < max_int_size . 10 произвольно; использовать что угодно. Значения «иначе» произвольны (здесь цифры числа пи!); использовать что угодно. Расположение символов (1,3,5,7,9) произвольное; использовать что угодно.