Existe-t-il une fonction Excel permettant de créer une valeur de hachage ?

Vous n'avez pas besoin d'écrire votre propre fonction - d'autres l'ont déjà fait pour vous.
Par exemple, j'ai rassemblé et comparé cinq fonctions de hachage VBA sur cette réponse de débordement de pile

Personnellement, j'utilise cette fonction VBA

• elle est appelée avec =BASE64SHA1(A1) dans Excel après que vous ayez copié la macro dans un module VBA
• nécessite . NET puisqu'il utilise la bibliothèque “Microsoft MSXML” (avec reliure tardive)

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

Personnaliser la longueur du hachage

• le hachage est initialement une chaîne unicode de 28 caractères (sensible à la casse + caractères spéciaux)
• Vous personnalisez la longueur du hachage avec cette ligne : Const cutoff As Integer = 5
• hachage de 4 chiffres = 36 collisions sur 6895 lignes = 0. 5 % de taux de collision
• 5 chiffres dièse = 0 collisions dans 6895 lignes = 0 % de taux de collision

Il existe également des fonctions de hachage (les trois fonctions CRC16 ](https://stackoverflow.com/a/14749855/935614)) qui ne nécessitent pas de .NET et n'utilisent pas de bibliothèques externes. Mais le hachage est plus long et produit plus de collisions.

Vous pouvez aussi simplement télécharger ce exemple de cahier d'exercices et jouer avec les 5 implémentations de hachage. Comme vous le voyez, la première feuille contient une bonne comparaison

Je ne me soucie pas beaucoup des collisions, mais j'avais besoin d'un faible pseudo-aléatoire des lignes basé sur un champ de cordes de longueur variable. Voici une solution insensée qui a bien fonctionné :

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

Où Z2 est la cellule contenant la chaîne que vous voulez hacher.

les “MOD” sont là pour éviter de déborder sur la notation scientifique. 1009 est un nombre premier, pourrait utiliser n'importe quel X pour que X*255 < max_int_size. 10 est arbitraire ; utilisez n'importe quoi. Les “autres” valeurs sont arbitraires (ici des chiffres de pi !); utilisez n'importe quoi. L'emplacement des caractères (1,3,5,7,9) est arbitraire ; utilisez n'importe quoi.

Pour une liste raisonnablement réduite, vous pouvez créer un brouilleur (fonction de hachage du pauvre) en utilisant les fonctions Excel intégrées.

Par exemple

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

Ici A1 et B1 contiennent une lettre de départ et une longueur de chaîne aléatoires.

Un peu de bricolage et de vérification et dans la plupart des cas, vous pouvez obtenir assez rapidement un identifiant unique exploitable.

Comment ça marche : La formule utilise la première lettre de la chaîne et une lettre fixe prise au milieu de la chaîne et utilise LEN() comme “fonction d'éventail” pour réduire les risques de collision.

CAVEAT : ce n'est pas un hachage, mais lorsque vous devez faire quelque chose rapidement, et que vous pouvez inspecter les résultats pour voir qu'il n'y a pas de collisions, cela fonctionne assez bien.

Edit: Si vos chaînes doivent avoir des longueurs variables (par exemple des noms complets) mais sont tirées d'un enregistrement de base de données avec des champs de largeur fixe, vous voudrez le faire comme ceci :

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

afin que les longueurs soient un brouilleur significatif.

J'utilise ce système qui donne de bons résultats en évitant les conflits sans avoir besoin de lancer un script à chaque fois. J'avais besoin d'une valeur comprise entre 0 et 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)))

Il prend les lettres de la chaîne, prend la valeur de chacune de ces lettres, ajoute une valeur (pour éviter que les mêmes lettres à différents endroits ne donnent les mêmes résultats), multiplie/divise chacune d'entre elles et exécute une fonction COS sur le total.

Vous pouvez essayer cela. Exécutez un Pseudo# sur deux colonnes :

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

Où A1 et B1 stockent des semences aléatoires saisies manuellement : 0

À ma connaissance, il n'existe pas de fonction de hachage intégrée dans Excel - il faudrait en créer une en tant que fonction définie par l'utilisateur dans VBA.

Cependant, veuillez noter que pour votre objectif, je ne pense pas que l'utilisation d'une fonction de hachage soit nécessaire ou vraiment avantageuse ! VLOOKUP fonctionnera aussi bien sur 256 octets que sur un hachage plus petit. Bien sûr, il sera peut-être un peu plus lent - un bit qui est certainement si petit qu'il est incommensurable. Et puis, ajouter les valeurs de hachage est un effort supplémentaire pour vous - et pour Excel…