Gibt es eine Excel-Funktion, um einen Hash-Wert zu erstellen?
Ich arbeite mit einer Reihe von Datenlisten, die durch den Dokumentnamen verschlüsselt sind. Die Dokumentnamen sind zwar sehr beschreibend, aber recht umständlich, wenn ich sie anzeigen muss (bis zu 256 Byte sind viel Immobilien), und ich würde gerne ein kleineres Schlüsselfeld erstellen können, das bei Bedarf leicht reproduzierbar ist eine VLOOKUP aus einem anderen workseet oder einer anderen arbeitsmappe erstellen.
Ich denke, ein Hash aus dem Titel, der eindeutig und reproduzierbar ist , für jeden Titel wäre am besten geeignet. Ist eine Funktion verfügbar oder möchte ich einen eigenen Algorithmus entwickeln?
Irgendwelche Gedanken oder Ideen zu dieser oder einer anderen Strategie?
Sie müssen keine eigene Funktion schreiben - andere haben das bereits für Sie getan.
Zum Beispiel habe ich fünf VBA-Hash-Funktionen für diese Stackoverflow-Antwort gesammelt und verglichen
Ich persönlich benutze diese VBA-Funktion
- es 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" verwendet (mit später Bindung)
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 anfangs eine 28 Zeichen lange Unicode-Zeichenfolge (Groß- und Kleinschreibung + Sonderzeichen).
- 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 interessiere mich nicht sehr für Kollisionen, benötige aber einen schwachen Pseudo-Zufallsgenerator für Zeilen, der auf einem Zeichenkettenfeld 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 zu hashende Zeichenfolge enthält.
"MOD" sollen ein Überlaufen der wissenschaftlichen Notation verhindern. 1009 ist eine Primzahl, könnte alles X verwenden, so dass X * 255 <max_int_size. 10 ist beliebig; benutze alles. "Else" -Werte sind willkürlich (hier Ziffern von pi!); benutze alles. Die Position der Zeichen (1,3,5,7,9) ist beliebig. benutze alles.
Für eine einigermaßen kleine Liste können Sie mithilfe der integrierten Excel-Funktionen einen Scrambler (die Hash-Funktion für Arme) erstellen.
Z.B.
=CODE(A2)*LEN(A2) + CODE(MID(A2,$A$1,$B$1))*LEN(MID(A2,$A$1,$B$1))
Hier enthalten A1 und B1 einen zufälligen Anfangsbuchstaben und eine zufällige Zeichenkettenlänge.
Ein bisschen herumspielen und prüfen und in den meisten Fällen können Sie schnell eine brauchbare eindeutige ID erhalten.
Funktionsweise : Die Formel verwendet den ersten Buchstaben der Zeichenfolge und einen festen Buchstaben aus der Mitte der Zeichenfolge und verwendet LEN () als Fanning-Funktion ', um die Wahrscheinlichkeit von Kollisionen zu verringern.
EINSCHRÄNKUNG: Dies ist kein Hash, aber wenn Sie brauchen Um schnell etwas zu erledigen und die Ergebnisse auf Kollisionen zu überprüfen, funktioniert es ganz gut.
Bearbeiten: Wenn Ihre Zeichenfolgen variable Längen haben sollten (z. B. vollständige Namen), aber aus einem Datenbankdatensatz mit Feldern fester Breite stammen, möchten Sie dies tun so was:
=CODE(TRIM(C8))*LEN(TRIM(C8))
+CODE(MID(TRIM(C8),$A$1,1))*LEN(MID(TRIM(C8),$A$1,$B$1))
damit sind die längen ein sinnvoller scrambler.
Ich verwende dies, was ziemlich gute Ergebnisse beim Verhindern von Konflikten liefert, ohne jedes Mal ein Skript ausführen zu müssen. Ich brauchte einen Wert zwischen 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)))
Es wählt Buchstaben aus der gesamten Zeichenfolge aus, nimmt den Wert jedes dieser Buchstaben, addiert einen Wert (um zu verhindern, dass dieselben Buchstaben an verschiedenen Stellen zu denselben Ergebnissen führen), multipliziert/dividiert jeden Buchstaben und führt eine COS-Funktion über die Summe aus.
Sie können dies versuchen. Führen Sie eine Pseudo-Nr. In zwei Spalten aus:
= + WENN (UND (ISBLANK (D3), ISBLANK (E3)), "", CODE (TRIM (D3 & E3)) * LEN (TRIM (D3 & E3)) + CODE (MITTEL (TRIM (D3 & E3), $ A $ 1 * LEN (D3 & E3), 1)) INT (LEN (TRIM (D3 & E3)) $ B $ 1))
Wo A1 und B1 zufällige Samen speichern, die manuell eingegeben wurden: 0
Meines Wissens gibt es in Excel keine Hash-Funktion - Sie müssen eine als benutzerdefinierte Funktion in VBA erstellen.
Bitte beachten Sie jedoch, dass für Ihren Zweck die Verwendung eines Hashs meiner Meinung nach nicht erforderlich oder wirklich vorteilhaft ist! VLOOKUP funktioniert mit 256 Bytes genauso gut wie mit einem kleineren Hash. Sicher, es könnte ein kleines bisschen langsamer sein - ein bisschen, das mit Sicherheit so klein ist, dass es nicht messbar ist. Und dann ist das Hinzufügen der Hash-Werte für Sie und für Excel mühsamer ...