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

Question

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

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

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

Answer 1

Вам не нужно писать свою собственную функцию - другие уже сделали это для вас.
Например, я собрал и сравнил пять хеш-функций 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

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

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

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

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

Answer 2

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

=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) произвольно; использовать что угодно.

Answer 3

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

Например

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

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

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

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

ПРЕДУПРЕЖДЕНИЕ: это не хеш, но когда вам нужно что-то сделать быстро и проверить результаты, чтобы убедиться, что нет столкновений, это работает довольно хорошо.

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

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

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

Answer 4

Вы можете попробовать это. Запустите псевдо # на двух столбцах:

=+ ЕСЛИ (И (ЕПУСТО (D3), ЕПУСТО (Е3)), "", КОД (TRIM (D3 & Е3))* LEN (TRIM (D3 & Е3))+ код (MID (TRIM (D3 & Е3), $ A $ 1 * LEN (D3 и E3), 1))INT (LEN (TRIM (D3 и E3)) $ B $ 1))

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

Answer 5

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

Answer 6

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

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