microsoft excel 2010

是否有一个Excel函数可以创建一个哈希值

我正在处理许多按文档名称键入的数据列表。文档名称虽然非常具有描述性,但如果我需要查看它们会非常麻烦(最多 256 个字节是很多空间),我希望能够创建一个更小的键域,以便在需要时轻松重现从另一个工作集或工作簿执行 VLOOKUP

我认为标题中的哈希值对于每个标题都是唯一且可重复的,这将是最合适的。是否有可用的功能,或者我正在考虑开发自己的算法?

对这个问题有什么想法或主意,或者其他策略?

你不需要写你自己的函数--别人已经为你做了这个。
例如,我在这个答案上收集并比较了五个 VBA 哈希函数

我个人使用这个VBA函数

  • 将宏复制到 VBA模块后,在 Excel 中使用 =BASE64SHA1(A1) 调用它
  • 需要.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位数哈希=6895行中有36次碰撞=0.5%的碰撞率
  • 5位数哈希=6895行中有0次碰撞=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是任意的;使用任何东西。“其他”值是任意的(这里是 pi 的数字!);使用任何东西。字符(1,3,5,7,9)的位置是任意的;使用任何东西。