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

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

私は、ドキュメント名でキーを設定したいくつかのデータリストを扱っています。ドキュメント名は非常に記述的ではありますが、それらを表示する必要がある場合には非常に煩雑です(最大256バイトは非常に多くの領域を占めます)。

タイトルからのハッシュで、タイトルごとにユニークで再現性のあるものが最適だと考えています。利用可能な関数はありますか?それとも自分でアルゴリズムを開発することを考えていますか?

この方法や別の戦略について何か考えやアイデアがあれば教えてください。

回答 (6)

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

あなた自身の関数を書く必要はありません - 他の人がすでにあなたのためにそれをしてくれました。
例えば、私はこの stackoverflow answer

個人的にはこのVBA関数を使用しています

  • マクロをVBA モジュール
  • マクロをVBA モジュール
  • ライブラリ “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文字の長さのユニコード文字列です(大文字小文字を区別します+特殊文字)
  • ハッシュ長を次の行でカスタマイズします: =BASE64SHA1(A1)
  • 4桁のハッシュ = 36個の衝突が6895行で発生 = 0.5 %の衝突率

  • 4桁のハッシュ = 36個の衝突が6895行で発生します。 5 %の衝突率

  • 5桁のハッシュ = 0 6895行での衝突 = 0 %の衝突率

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

この ワークブックの例 をダウンロードして、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*255 < max_int_size となるように、何でもXを使うことができます。10は任意である。"Else "値は、任意の値(ここではπの数字!)である; 何でも使ってください。文字の位置(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はランダムな開始文字と文字列の長さを保持しています。

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

ここでA1とB1にはランダムな開始文字と文字列の長さが格納されています。

仕組み . この式は、文字列の最初の文字と文字列の途中から取得した固定文字を使用し、衝突の可能性を減らすために「ファニング関数」として LEN() を使用します。

CAVEAT : これはハッシュではありませんが、何かを素早く行い、衝突がないことを確認するために結果を検査する必要がある場合には、非常にうまく機能します。

編集:文字列の長さが可変(フルネームなど)で、固定幅のフィールドを持つデータベースレコードから取得する場合、このようにしたいと思うでしょう。

0x1&

のようにして、長さが意味のあるスクランブラーになるようにします。

2
2
2
2018-09-21 16:16:37 +0000

私はこれを使っていますが、毎回スクリプトを実行する必要がなく、衝突を防ぐことでかなり良い結果が得られます。

1
1
1
2013-11-05 16:24:05 +0000

これを試してみてください。2列に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のために…

関連する質問

28
13
13
16
6