2013-02-13 14:35:30 +0000 2013-02-13 14:35:30 +0000
27
27

有没有一个Excel函数来创建一个哈希值？

我正在处理一些数据列表，这些数据列表是按文档名称键入的。文档名称虽然具有很强的描述性，但如果我需要查看它们，就会相当麻烦（高达256字节的空间很大），我希望能够创建一个较小的键域，以便在我需要从另一个工作集或工作簿中进行VLOOKUP的时候，可以随时重现。

我想从标题中提取一个唯一的、可重复**每个标题的哈希值是最合适的。是否有可用的函数，或者我是否要开发自己的算法？

对这个或其他策略有什么想法或创意吗？
来源

答案 (6)

35
35
35
2013-02-13 14:58:13 +0000

你不需要自己写函数，别人已经帮你写好了。 

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字符串（区分大小写+特殊字符）
  • 你可以用这行自定义哈希长度：=BASE64SHA1(A1)
  • 4位哈希=36次碰撞，6895行=0. 5%碰撞率
  • 5位数哈希=6895行中的0次碰撞=0%碰撞率

还有一种哈希函数所有三个CRC16函数)，它不需要.NET，也不使用外部库。但是哈希值比较长，产生的碰撞也比较多。

你也可以直接下载这个示例工作簿，然后玩玩所有5种哈希实现。正如你所看到的，在第一张纸上有一个很好的比较。
来源
9
9
9
2016-05-13 19:56:41 +0000

我不是很在意碰撞，但需要一个基于可变长度字符串字段的弱伪随机器的行。这里有一个疯狂的解决方案，效果不错。

=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 "的值是任意的（这里是pi的数字！）；可以使用任何东西。字符的位置(1,3,5,7,9)是任意的，可以使用任何东西。
来源
3
3
3
2013-06-13 14:48:09 +0000

对于一个相当小的列表，你可以使用内置的Excel函数创建一个扰频器（穷人的哈希函数）。

例如：

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

这里A1和B1是随机的起始字母和字符串长度。

稍微调整和检查一下，在大多数情况下，你可以很快得到一个可行的唯一ID。

如何工作 。公式使用字符串的第一个字母和从字符串中间抽取的固定字母，并使用LEN()作为 “扇形函数 "来减少碰撞的机会。

CAVEAT : 这不是**的哈希，但是当你需要快速完成一些事情，并且可以检查结果是否有碰撞时，它的效果相当好。

编辑:如果你的字符串应该有可变长度（例如全名），但是从一个有固定宽度字段的数据库记录中拉出来的，你会想这样做。

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

这样长度才是一个有意义的扰频器。
来源
2
2
2
2018-09-21 16:16:37 +0000

我正在使用这个，它在防止冲突方面有很好的效果，不需要每次都运行脚本。我需要一个介于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函数。
来源
1
1
1
2013-11-05 16:24:05 +0000

你可以试试这个。在两列上运行一个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
来源
0
0
0
2013-02-13 14:40:20 +0000

据我所知，Excel中并没有内置的哈希函数–你需要在VBA中以用户定义函数的形式创建一个。

然而，请注意，对于你的目的，我认为使用哈希函数并不是必须的，也不是真的有好处。VLOOKUP在256字节上的效果和在更小的哈希值上的效果一样好。当然，它可能会慢一点–位是肯定的，小到不可估量。然后添加哈希值对你来说更费劲–对Excel来说也是如此……
来源