Bestaat er een Excel-functie om een hash-waarde te creëren?

Je hoeft je eigen functie niet te schrijven - anderen hebben dat al voor je gedaan.
Ik heb bijvoorbeeld vijf VBA hash functies verzameld en vergeleken op deze stackoverflow answer

Persoonlijk gebruik ik deze VBA functie

• wordt aangeroepen met =BASE64SHA1(A1) in Excel nadat je de macro naar een VBA module hebt gekopieerd

• vereist . NET nodig omdat het de bibliotheek “Microsoft MSXML” gebruikt (met late binding)

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

De hashlengte aanpassen

• de hash is aanvankelijk een 28 tekens lange unicode string (hoofdlettergevoelig + speciale tekens)
• Je past de hashlengte aan met deze regel: Const cutoff As Integer = 5
• 4-cijferige hash = 36 botsingen in 6895 regels = 0. 5 % botsingspercentage
• 5-cijferige hash = 0 botsingen in 6895 regels = 0 % botsingspercentage

Er zijn ook hash functies alle drie CRC16 functies ) die geen .NET vereisen en geen externe bibliotheken gebruiken. Maar de hash is langer en produceert meer botsingen.

Je kunt ook gewoon dit voorbeeld werkboek downloaden en met alle 5 hash implementaties spelen. Zoals je ziet is er een goede vergelijking op het eerste blad

Ik geef niet veel om botsingen, maar ik had een zwakke pseudorandomizer van rijen nodig, gebaseerd op een variabele-lengte string veld. Hier is een krankzinnige oplossing die goed werkte:

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

Waarbij Z2 de cel is die de string bevat die je wilt hashen.

“MOD "s zijn er om overlopen naar wetenschappelijke notatie te voorkomen. 1009 is een priemgetal, u kunt om het even welke X gebruiken zodat X*255 < max_int_size. 10 is willekeurig; gebruik om het even wat. "Andere” waarden zijn willekeurig (cijfers van pi hier!); gebruik om het even wat. Plaats van karakters (1,3,5,7,9) zijn willekeurig; gebruik om het even wat.

Voor een redelijk kleine lijst kunt u een scrambler (poor man’s hash function) maken met ingebouwde Excel functies.

Bijv.

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

Hier houden A1 en B1 een willekeurige beginletter en tekenreekslengte in.

Een beetje rommelen en controleren en in de meeste gevallen kun je vrij snel een werkbaar uniek ID krijgen.

Hoe het werkt : De formule gebruikt de eerste letter van de string en een vaste letter uit het midden van de string en gebruikt LEN() als een ‘uitwaaierende functie’ om de kans op botsingen te verkleinen.

CAVEAT : dit is geen hash, maar als je snel iets gedaan moet krijgen, en de resultaten kunt inspecteren om te zien dat er geen botsingen zijn, werkt het vrij goed.

Aanvullen: Als je strings variabele lengtes moeten hebben (b.v. volledige namen) maar uit een database record met vaste breedte velden worden gehaald, zul je het als volgt willen doen:

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

zodat de lengtes een zinvolle scrambler zijn.

Ik gebruik deze methode die vrij goede resultaten geeft met het voorkomen van botsingen zonder dat ik elke keer een script hoef te draaien. Ik had een waarde tussen 0 - 1 nodig.

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

Het kiest letters uit de string, neemt de waarde van elk van die letters, voegt een waarde toe (om te voorkomen dat dezelfde letters op verschillende plaatsen dezelfde resultaten geven), vermenigvuldigt/verdeelt elk en voert een COS functie uit over het totaal.

U kunt dit proberen. Voer een Pseudo# uit op twee kolommen:

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

Waar A1 en B1 handmatig ingevoerde willekeurige zaadjes opslaan: 0

Voor zover ik weet is er geen hash functie ingebouwd in Excel - je zou er een moeten maken als een User Defined Function in VBA.

Merk echter op dat voor uw doel ik denk dat het gebruik van een hash niet nodig of echt voordelig is! VLOOKUP werkt net zo goed op 256 bytes als op een kleinere hash. Zeker, het is misschien een heel klein beetje trager - maar dat is zeker zo klein dat het onmeetbaar is. En dan is het toevoegen van de hash waarden meer moeite voor jou - en voor Excel…