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

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

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

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

24
задан Andrea
11.12.2022 12:29 Количество просмотров материала 3057
Распечатать страницу

6 ответов

вам не нужно писать свою собственную функцию - другие уже сделали это за вас.

Например, я собрал и сравнил пять хэш-функций 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 символов Unicode строку (регистр + спецсимволы)
  • настройки длины хэш-кода со строки: Const cutoff As Integer = 5
  • 4 цифры hash = 36 коллизий в 6895 линиях = 0.5 % частота коллизий
  • 5 цифр хэш = 0 коллизий в 6895 строках = 0% коллизий

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

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

29
отвечен nixda 2022-12-12 20:17

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

=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" S там предотвратить переполнить к научной нотации. 1009 является простым, может использовать что-нибудь X, так что X*255 < max_int_size. 10 произвольно; используйте что угодно. Значения "Else" являются произвольными (цифры pi сюда!); используйте что угодно. Расположение символов (1,3,5,7,9) произвольное; используйте что угодно.

4
отвечен Anonymous Coward 2022-12-12 22:34

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

Е. Г. 

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

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

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

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

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

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

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

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

3
отвечен Assad Ebrahim 2022-12-13 00:51

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

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

1
отвечен Michael Polubinski 2022-12-13 03:08

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

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

0
отвечен Peter Albert 2022-12-13 05:25

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

0
отвечен Ant Cole 2022-12-13 07:42

Постоянная ссылка на данную страницу: [ Скопировать ссылку | Сгенерировать QR-код ]

Ваш ответ

Опубликуйте как Гость или авторизуйтесь

Имя
Мы используем cookie. Это позволяет нам анализировать взаимодействие посетителей с сайтом и делать его лучше. Продолжая пользоваться сайтом, вы соглашаетесь с использованием файлов cookie. Политика конфиденциальности
Согласен
Вверх