microsoft excel 2010

Gibt es eine Excel-Funktion zur Erstellung eines Hash-Wertes

Ich arbeite mit einer Reihe von Datenlisten, die nach Dokumentnamen verschlüsselt sind. Die Dokumentnamen sind zwar sehr aussagekräftig, aber ziemlich umständlich, wenn ich sie anzeigen muss (bis zu 256 Bytes sind viel Platz), und ich würde gerne ein kleineres Schlüsselfeld erstellen können, das bei Bedarf leicht reproduzierbar ist um einen VLOOKUP von einem anderen Workset oder einer anderen Arbeitsmappe durchzuführen.

Ich denke, ein Hash aus dem Titel, der für jeden Titel einzigartig und reproduzierbar wäre, wäre am besten geeignet. Gibt es eine Funktion oder möchte ich einen eigenen Algorithmus entwickeln?

Haben Sie eine Idee für diese oder eine andere Strategie?

Sie brauchen keine eigene Funktion zu schreiben-das haben schon andere für Sie getan.
Zum Beispiel habe ich fünf VBA-Hash-Funktionen für diese Antwort gesammelt und verglichen

Ich persönlich verwende diese VBA-Funktion

Es wird mit =BASE64SHA1(A1) in Excel aufgerufen, nachdem Sie das Makro in ein VBA -Modul kopiert haben
erfordert .NET,da es die Bibliothek "Microsoft MSXML" (mit später Bindung)verwendet

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

Anpassen der Hash-Länge

der Hash ist zunächst eine 28 Zeichen lange Unicode-Zeichenfolge (Groß-und Kleinschreibung sowie Sonderzeichen werden berücksichtigt)
Sie passen die Hash-Länge mit dieser Zeile an: Const cutoff As Integer = 5
4-stelliger Hash=36 Kollisionen in 6895 Zeilen=0,5 % Kollisionsrate
5-stelliger Hash=0 Kollisionen in 6895 Zeilen=0 % Kollisionsrate

Es gibt auch Hash-Funktionen ( alle drei CRC16-Funktionen ), die kein .NET erfordern und keine externen Bibliotheken verwenden. Aber der Hash ist länger und erzeugt mehr Kollisionen.

Sie können auch einfach diese Beispielarbeitsmappe herunterladen und mit allen 5 Hash-Implementierungen herumspielen. Wie Sie sehen, gibt es auf dem ersten Blatt einen guten Vergleich

Ich mache mir nicht viel aus Kollisionen,brauchte aber einen schwachen Pseudozufallsgenerator für Zeilen,der auf einem Stringfeld variabler Länge basiert.Hier ist eine verrückte Lösung,die gut funktioniert hat:

=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)

Wobei Z2 die Zelle ist, die die Zeichenfolge enthält, die Sie hashen möchten.

"MOD"s sind da, um ein Überlaufen in die wissenschaftliche Notation zu verhindern. 1009 ist eine Primzahl, könnte alles X verwenden, sodass X*255 < max_int_size . 10 ist willkürlich; irgendetwas verwenden. "Else"-Werte sind willkürlich (Ziffern von pi hier!); irgendetwas verwenden. Die Position der Zeichen (1,3,5,7,9) ist willkürlich; irgendetwas verwenden.