Существует ли функция Excel для создания хэш-значения?
Я работаю с несколькими списками данных, которые имеют ключ по имени документа. Имена документов, хотя и очень описательные, довольно громоздки, если мне нужно их просмотреть (до 256 байт-это много недвижимости), и мне бы хотелось создать меньшее ключевое поле, которое легко воспроизводится в случае, если мне нужно сделать VLOOKUP с другой workseet или книги.
Я думаю хэш из названия, который был бы уникальным и воспроизводимым на каждое название было бы больше всего соответствующий. Есть ли функция, или я смотрю на разработку собственного алгоритма?
есть мысли или идеи по той или иной стратегии?
6 ответов
вам не нужно писать свою собственную функцию — другие уже сделали это за вас.
Например, я собрал и сравнил пять хэш-функций VBA на этом ответе на StackOverflow
лично я использую эту функцию VBA
- ее назвали с =BASE64SHA1(A1) в Excel после копирования макроса в VBA модуль
- требуется .NET, так как он использует библиотеку » Microsoft MSXML «(с опозданием обязательный)
настройка длины хэша
- хэш-изначально это 28 символов Unicode строку (регистр + спецсимволы)
- настройки длины хэш-кода со строки: Const cutoff As Integer = 5
- 4 цифры hash = 36 коллизий в 6895 линиях = 0.5 % частота коллизий
- 5 цифр хэш = 0 коллизий в 6895 строках = 0% коллизий
есть также хэш функции (все три функции CRC16), который не требует .NET и не использует внешние библиотеки. Но хэш длиннее и создает больше коллизий.
вы также можете просто скачать это пример книги и поиграйте со всеми 5 реализациями хэша. Как видите, на первом листе хорошее сравнение
Я не очень забочусь о столкновениях, но мне нужен слабый псевдорандомайзер строки, основанные на строковом поле переменной длины. Вот одно безумное решение, которое хорошо работает:
здесь Z2 — это ячейка, содержащая строку, которую вы хотите хэш.
«MOD» S там предотвратить переполнить к научной нотации. 1009 является простым, может использовать что-нибудь X, так что X*255 max_int_size . 10 произвольно; используйте что угодно. Значения «Else» являются произвольными (цифры pi сюда!); используйте что угодно. Расположение символов (1,3,5,7,9) произвольное; используйте что угодно.
для достаточно небольшого списка вы можете создать скремблер (хэш-функцию бедного человека), используя встроенные функции Excel.
здесь A1 и B1 содержат случайную начальную букву и длину строки.
немного возиться и проверки, и в большинстве случаев вы можете получить работоспособный уникальный идентификатор довольно быстро.
как это работает: формула использует первую букву строки и фиксированную букву, взятую из середины строки, и использует LEN () как «функция раздувания», чтобы уменьшить вероятность столкновений.
предостережение: это не хэш, но когда вам нужно что-то сделать быстро, и можете проверить результаты, чтобы увидеть, что нет никаких столкновений, он работает довольно хорошо.
изменить: Если ваши строки должны иметь переменную длину (например, полные имена), но извлекаются из записи базы данных с полями фиксированной ширины, вам нужно сделать это так это:
Как захешировать данные в md5 excel
Регистрация на форуме тут, о проблемах пишите сюда — alarforum@yandex.ru, проверяйте папку спам! Обязательно пройдите восстановить пароль
| Поиск по форуму |
| Расширенный поиск |
| К странице. |
| Страница 1 из 4 | 1 | 2 | 3 | 4 | Следующая > |
__ Полезные надстройки для Excel. Парсинг сайтов и файлов. 
Макросы любой сложности на заказ. Мониторинг цен конкурентов
| EducatedFool |
| Посмотреть профиль |
| Найти ещё сообщения от EducatedFool |
это — стандартный модуль.
пустой
а вы вставили код в модуль листа
куда не надо было вставлять
__ Полезные надстройки для Excel. Парсинг сайтов и файлов. Макросы любой сложности на заказ. Мониторинг цен конкурентов
| EducatedFool |
| Посмотреть профиль |
| Найти ещё сообщения от EducatedFool |
и все-же что-то не так
ATT.jpg
а с других модулей убрали ее?
в коде название функции GetHash а в формуле Gethash. Разве после написания в строке формул функции не приводятся к "дефолтным" именам?
Is there an Excel function to create a hash value?
I’m working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real estate) and I’d love to be able to create a smaller keyfield that’s readily reproducible in case I need to do a VLOOKUP from another workseet or workbook.
I’m thinking a hash from the title that’d be unique and reproducible for each title would be most appropriate. Is there a function available, or am I looking at developing my own algorithm?
Any thoughts or ideas on this or another strategy?
9 Answers 9
You don’t need to write your own function — others already did that for you.
For example I collected and compared five VBA hash functions on this stackoverflow answer
Personally I use this VBA function
- its called with =BASE64SHA1(A1) in Excel after you copied the macro to a VBA module
- requires .NET since it uses the library «Microsoft MSXML» (with late binding)
Customizing the hash length
- the hash initially is a 28 characters long unicode string (case sensitive + special chars)
- You customize the hash length with this line: Const cutoff As Integer = 5
- 4 digits hash = 36 collisions in 6895 lines = 0.5 % collision rate
- 5 digits hash = 0 collisions in 6895 lines = 0 % collision rate
There are also hash functions (all three CRC16 functions) which doesn’t require .NET and doesn’t use external libraries. But the hash is longer and produces more collisions.
You could also just download this example workbook and play around with all 5 hash implementations. As you see there is a good comparison on the first sheet
I don’t care very much about collisions, but needed a weak pseudorandomizer of rows based on a variable-length string field. Here’s one insane solution that worked well:
Where Z2 is the cell containing the string you want to hash.
«MOD»s are there to prevent overflowing to scientific notation. 1009 is a prime, could use anything X so that X*255 < max_int_size . 10 is arbitrary; use anything. «Else» values are arbitrary (digits of pi here!); use anything. Location of characters (1,3,5,7,9) are arbitrary; use anything.
For a reasonably small list you can create a scrambler (poor man’s hash function) using built-in Excel functions.
Here A1 and B1 hold a random start letter and string length.
A little fiddling and checking and in most cases you can get a workable unique ID quite quickly.
How it Works: The formula uses the first letter of the string and a fixed letter taken from mid-string and uses LEN() as a ‘fanning function’ to reduce the chance of collisions.
CAVEAT: this is not a hash, but when you need to get something done quickly, and can inspect the results to see that there are no collisions, it works quite well.