Gibt es eine Excel-Funktion, um einen Hash-Wert zu erzeugen?

Sie brauchen keine eigene Funktion zu schreiben - das haben andere schon für Sie getan.
Ich habe zum Beispiel fünf VBA-Hash-Funktionen auf dieser stackoverflow-Antwort

Persönlich benutze ich diese VBA-Funktion

• sie wird mit =BASE64SHA1(A1) in Excel aufgerufen, nachdem Sie das Makro in ein VBA Modul kopiert haben - benötigt . NET, da es die Bibliothek “Microsoft MSXML” (mit Late Binding) 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 ein 28 Zeichen langer Unicode-String (Groß-/Kleinschreibung + Sonderzeichen)
• Sie passen die Hash-Länge mit dieser Zeile an: Const cutoff As Integer = 5
• 4 Stellen 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 benötigen und keine externen Bibliotheken verwenden. Aber der Hash ist länger und erzeugt mehr Kollisionen.

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

Ich kümmere mich nicht sehr um Kollisionen, brauchte aber einen schwachen Pseudo-Zufallsgenerator für Zeilen, der auf einem String-Feld 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 Zeichenkette enthält, die Sie hashen wollen.

“MOD "s sind da, um ein Überlaufen zur wissenschaftlichen Notation zu verhindern. 1009 ist eine Primzahl, man kann alles X verwenden, so dass X*255 < max_int_size. 10 ist willkürlich; verwenden Sie irgendetwas. "Else”-Werte sind willkürlich (Ziffern von Pi hier!); verwenden Sie irgendetwas. Die Position der Zeichen (1,3,5,7,9) ist beliebig; verwenden Sie irgendetwas.

Für eine einigermaßen kleine Liste können Sie einen Scrambler (die Hash-Funktion des armen Mannes) mit eingebauten Excel-Funktionen erstellen.

Z.B.

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

Hier halten A1 und B1 einen zufälligen Anfangsbuchstaben und eine zufällige Länge der Zeichenkette.

Mit ein wenig Tüfteln und Überprüfen können Sie in den meisten Fällen recht schnell eine brauchbare eindeutige ID erhalten.

Wie es funktioniert : Die Formel verwendet den ersten Buchstaben der Zeichenkette und einen festen Buchstaben aus der Mitte der Zeichenkette und verwendet LEN() als “Auffächerungsfunktion”, um die Wahrscheinlichkeit von Kollisionen zu verringern.

CAVEAT : Dies ist kein Hash, aber wenn Sie etwas schnell erledigen müssen und die Ergebnisse überprüfen können, um zu sehen, dass es keine Kollisionen gibt, funktioniert es recht gut.

Bearbeiten: Wenn Ihre Zeichenketten variable Längen haben sollen (z. B. vollständige Namen), aber aus einem Datenbankdatensatz mit Feldern fester Breite gezogen werden, werden Sie es so machen wollen:

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

, damit die Längen ein sinnvoller Scrambler sind.

Ich verwende dies, was ziemlich gute Ergebnisse beim Verhindern von Überschneidungen liefert, ohne jedes Mal ein Skript ausführen zu müssen. Ich brauchte einen Wert zwischen 0 und 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)))

Es wählt Buchstaben aus der Zeichenfolge aus, nimmt den Wert jedes dieser Buchstaben, addiert einen Wert (um zu verhindern, dass gleiche Buchstaben an verschiedenen Stellen gleiche Ergebnisse liefern), multipliziert/dividiert jeden und führt eine COS-Funktion über die Summe aus.

Sie können dies versuchen. Führen Sie eine Pseudo# auf zwei Spalten aus:

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

Wobei A1 und B1 manuell eingegebene Zufallsseeds speichern: 0

Meines Wissens ist in Excel keine Hash-Funktion eingebaut - Sie müssten eine als benutzerdefinierte Funktion in VBA erstellen.

Beachten Sie jedoch, dass ich für Ihren Zweck die Verwendung eines Hashes nicht für erforderlich oder wirklich vorteilhaft halte! VLOOKUP wird bei 256 Bytes genauso gut funktionieren wie bei einem kleineren Hash. Sicher, es könnte ein winziges bisschen langsamer sein - aber das ist mit Sicherheit so klein, dass es unmessbar ist. Und dann ist das Hinzufügen der Hash-Werte mehr Aufwand für Sie - und für Excel…