☰

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

tag-icon • tag-icon microsoft-excel-2010 worksheet-function hashing
Excel 是否有函数可以创建哈希值？

我正在处理大量以文档名称为关键字的数据列表。文档名称虽然非常具有描述性，但如果我需要查看它们，它们会非常麻烦（最多 256 个字节，占用很多空间），我希望能够创建一个较小的关键字字段，以便在需要VLOOKUP从另一个工作表或工作簿中执行操作时可以轻松重现。

我正在考虑从标题中提取一个唯一且可复制的哈希值为了每个标题都最合适。是否有可用的函数，或者我是否正在考虑开发自己的算法？

对此或其他策略有什么想法或意见吗？

答案1

您无需编写自己的函数 - 其他人已经为您编写了。
例如，我在此收集并比较了五个 VBA 哈希函数stackoverflow 答案

我个人使用这个 VBA 函数

  • =BASE64SHA1(A1)将宏复制到 VBA 后，它会在 Excel 中调用模块
  • 需要 .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 种哈希实现。如您所见，第一张表上有一个很好的比较

答案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 是任意的；使用任何东西。“Else”值是任意的（这里是圆周率的数字！）；使用任何东西。字符的位置（1、3、5、7、9）是任意的；使用任何东西。

答案3

以下是病毒核型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)))))

我防止算术溢出递归 多重模式. a * 2不会溢出，m == MAX_UNIT == 4294967295因为excel 使用双精度数进行数学运算并且它们安全到9,007,199,254,740,993

将REDUCE(...)术语包装起来DEC2HEX()以获得典型的十六进制输出。

Examples:

"BAD" => 2775452120 or A56E09D8 (in hex)
"DAB" => 772135228 or 2E05D93C
"ab" => 1294271946 or 4D2505CA
"AB" => 752165258 or 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 or 7C0594DD

答案4

在较新版本的 Excel（2022 年 3 月及更高版本）中，新的数组公式使得无需 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，或使用 Base64 实现将其缩短为 6 个字符。

相关内容