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

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

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

Любые мысли или идеи по этому поводу или другой стратегии?

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

Вот FNV-1a в 32-битах как одна формула для Excel, где в ячейке A1 находится строка, которую вы хотите хэшировать:

=LET(
  Y, LAMBDA(G,a,b,m,res,
    IF(0<b,
      G(
        G,
        MOD(a, m) * 2,
        TRUNC(b / 2),
        m,
        IF(MOD(b, 2) = 1,
          MOD(res + MOD(a, m), m),
          res)),
      res)),
  mulmod, LAMBDA(a,b,m, Y(Y,a,b,m,0)),
  p, 16777619    +N("FNV_prime for 32 bits"),
  o, 2166136261  +N("FNV_offset_basis for 32 bits"),
  m, POWER(2,32) +N("modulus for 32 bits"),
  s, A1,
  IF(ISBLANK(s),
    0,
    REDUCE(o,
      ROW(INDIRECT("1:"&LEN(s))),
      LAMBDA(acc,i,mulmod(p, BITXOR(acc, CODE(MID(s,i,1))), m)))))

Я защищаю от арифметического переполнения в рекурсивном mulmod. a * 2 не вызовет переполнения, так как m == MAX_UNIT == 4294967295, в то время как Excel использует double для математики, и они безопасны до 9,007,199,254,740,993

Оборачивайте термин REDUCE(...) в DEC2HEX() для типичного шестнадцатеричного вывода.

Примеры:

"BAD" => 2775452120 или A56E09D8 (в шестнадцатеричном)
"DAB" => 772135228 или 2E05D93C
"ab" => 1294271946 или 4D2505CA
"AB" => 752165258 или 2CD5218A
"" => 0
"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum." => 2080740573 или 7C0594DD

Для сравнительно небольшого списка вы можете создать шифратор (простая функция хэширования) с использованием встроенных функций 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))

чтобы длины были содержательным шифратором.

В последних версиях Excel (март 2022 и позже) новые формулы массива позволяют создавать функции хэширования без VBA.

Вот формула для хэш-функции Берштейна djb2 (см. например http://www.cse.yorku.ca/~oz/hash.html):

hash_djb2 = LAMBDA(v,
    MAP(
        v,
        LAMBDA(x,
            LET(
                y, VALUETOTEXT(x, 0),
                l, LEN(y),
                REDUCE(
                    5381,
                    SEQUENCE(l),
                    LAMBDA(a, j,
                        LET(
                            z, CODE(MID(y, j, 1)),
                            MOD(a * 33 + z, 2 ^ 32)
                        )
                    )
                )
            )
        )
    )
);

Выход — это целое число меньше 2^32 (~4e9). Его можно дополнительно сократить до 8 символов с помощью DEC2HEX, или до 6 символов с реализацией Base64.

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

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

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

Я реализовал переносимую версию Функции хэша FNV1a 32 бит для Microsoft Excel/LibreOffice Calc/Google Spreadsheets в двух вариантах: на основе формулы и на основе макроса (этот не работает для Google таблиц).

Если вам интересно, посмотрите здесь: https://github.com/leogama/spreadsheet-hash

Вот функция, которая просто подсчитывает значения code() всех символов в ячейке E2:

=IFERROR(SUM(1*CODE(MID(E2;ROW(OFFSET(A1;0;0;LEN(E2);1));1)));0)

ЭТО функция массива, поэтому обязательно нажмите CTRL-Shift-Enter после ее ввода. Думаю, оттуда вы можете пойти и сделать так, чтобы она имела меньше совпадений.

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

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

Вот улучшение ответа @AndreA:

• Возвращает правильное значение для пустой строки (должно возвращать смещение, а не ноль)
• Не зависит от настроек кодировки машины. Вместо этого хэширует utf32-BE кодировку строки.
• Избавляемся от INDIRECT и используем SEQUENCE

Совет: Определите именованную ссылку, чтобы использовать ее как функцию!

=LAMBDA(s,
  LET(
    Y,
      LAMBDA(G,a,b,m,res,
        IF(b > 0,
          G(G, MOD(a, m)*2, TRUNC(b/2), m, IF(MOD(b, 2) = 1, MOD(res + MOD(a, m), m), res)),
          res
        )
      ),
    mulmod,
      LAMBDA(a, b, m, Y(Y, a, b, m, 0)),
    utf32be_byte,
      LAMBDA(s,i,
        BITAND(
          BITRSHIFT(
            UNICODE(MID(s, TRUNC(i/4) + 1, 1)),
            8*(3 - MOD(i, 4))
          ),
          255
        )
      ),
    p,
      16777619    +N("FNV_prime for 32 bits"),
    o,
      2166136261  +N("FNV_offset_basis for 32 bits"),
    m,
      POWER(2,32) +N("modulus for 32 bits"),

    IF(s="",
      o,
      REDUCE(
        o,
        SEQUENCE(4*LEN(s), 1, 0, 1),
        LAMBDA(acc, i, mulmod(p, BITXOR(acc, utf32be_byte(s, i)), m))
      )
    )
  )
)