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

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

Лично я использую эту 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 цифр hash = 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 - произвольно; используйте что угодно. “Else” значения произвольны (цифры пи здесь!); используйте что угодно. Расположение символов (1,3,5,7,9) - произвольно; используйте что угодно.

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

Например,

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

Здесь A1 и B1 держат случайную стартовую букву и длину строки.

Немного скрипучей и проверкой, и в большинстве случаев вы можете получить работающий уникальный идентификатор довольно быстро.

Как это работает : Формула использует первую букву строки и фиксированную букву, взятую из средней строки, и использует LEN() в качестве “функции разворота”, чтобы уменьшить вероятность столкновений.

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

Edit: Если ваши строки должны иметь переменную длину (например, полные имена), но извлекаются из записи базы данных с полями фиксированной ширины, вы захотите сделать это вот так:

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

так, чтобы длины были значимым скремблером.

Я использую это, что дает довольно хорошие результаты с предотвращением столкновений без необходимости запускать скрипт каждый раз. Мне нужно было значение между 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)))

Он выбирает буквы со всей строки, берет значение каждой из этих букв, добавляет значение (чтобы предотвратить появление одних и тех же букв в разных местах, дающих одинаковые результаты), умножает/разделяет каждую и запускает COS-функцию на общую сумму.

Можешь попробовать. Запустите Псевдо на двух колонках:

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

Где A1 и B1 хранят случайные семена, введенные вручную: 0

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

Однако, обратите внимание, что для ваших целей использование хэша, на мой взгляд, не является обязательным или действительно выгодным! VLOOKUP будет работать на 256 байтах так же хорошо, как и на меньшем хэше. Конечно, это может быть немного медленнее - бит, который наверняка настолько мал, что это неизмеримо. И тогда добавление хэш-значений будет для вас больше усилий - и для Excel…