microsoft excel 2010

ハッシュ値を作成するExcelの関数はありますか?

ドキュメント名でキー設定された多数のデータリストを使用しています。ドキュメント名は非常にわかりやすいものですが、表示する必要がある場合は非常に面倒です(最大256バイトは多くの領域です)。必要な場合に簡単に再現できる、より小さなキーフィールドを作成できるようにしたいと思います。別のワークシートまたはワークブックから VLOOKUP を実行します。

タイトルごとに一意で再現性のあるハッシュが最適だと思います。利用できる機能はありますか、それとも独自のアルゴリズムの開発を検討していますか?

この方法、または別の戦略について、ご意見やアイデアがあればお聞かせください。

自分で関数を書く必要はなく、すでに他の人がやってくれています。
たとえば、この回答で5つのVBAハッシュ関数を収集して比較しました

個人的には、以下のVBA関数を使用しています。

  • マクロをVBAモジュールにコピーした後、Excelで =BASE64SHA1(A1) を使用して呼び出されます
  • Microsoft MSXML" (レイトバインディング)ライブラリを使用するため、.NET が必要です。

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%の衝突率

.NETを必要とせず、外部ライブラリを使用しないハッシュ関数(3つのCRC16関数すべて)もあります。しかし、ハッシュはより長く、より多くの衝突を生成します。

このサンプルワークブックをダウンロードして、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 *255< max_int_size となるように任意のXを使用できます。10は任意です。何でも使用してください。「Else」の値は任意です(ここでは円周率の数字です!)。何でも使用してください。文字の位置(1、3、5、7、9)は任意です。何でも使用してください。