Esiste una funzione di Excel per creare un valore di hash?

Non hai bisogno di scrivere la tua funzione - altri lo hanno già fatto per te.
Per esempio ho raccolto e confrontato cinque funzioni hash VBA su questo risposta di stackoverflow

Personalmente uso questa funzione VBA

• viene chiamata con =BASE64SHA1(A1) in Excel dopo aver copiato la macro in un modulo VBA
• richiede . NET poiché usa la libreria “Microsoft MSXML” (con 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

Personalizzazione della lunghezza dell'hash

• l'hash inizialmente è una stringa unicode di 28 caratteri (case sensitive + caratteri speciali)
• si personalizza la lunghezza dell'hash con questa linea: Const cutoff As Integer = 5
• hash di 4 cifre = 36 collisioni in 6895 linee = 0. 5 % tasso di collisione . 5 % tasso di collisione
• 5 cifre hash = 0 collisioni in 6895 linee = 0 % tasso di collisione

Ci sono anche funzioni hash tutte e tre le funzioni CRC16 ) che non richiedono .NET e non usano librerie esterne. Ma l'hash è più lungo e produce più collisioni.

Potresti anche solo scaricare questa cartella di lavoro di esempio e giocare con tutte e 5 le implementazioni di hash. Come vedi c'è un buon confronto sul primo foglio

Non mi importa molto delle collisioni, ma avevo bisogno di un debole pseudorandomizzatore di righe basato su un campo di stringhe di lunghezza variabile. Ecco una soluzione folle che ha funzionato bene:

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

dove Z2 è la cella che contiene la stringa che vuoi hashare. I “MOD” di

sono lì per prevenire l'overflow in notazione scientifica. 1009 è un primo, potresti usare qualsiasi cosa X in modo che X\255 < max_int_size. 10 è arbitrario; usa qualsiasi cosa. I valori “Altro” sono arbitrari (cifre di pi greco qui!); usa qualsiasi cosa. La posizione dei caratteri (1,3,5,7,9) è arbitraria; usa qualsiasi cosa.

Per una lista ragionevolmente piccola puoi creare uno scrambler (la funzione hash dei poveri) usando le funzioni integrate di Excel.

Es.

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

Qui A1 e B1 contengono una lettera iniziale casuale e la lunghezza della stringa.

Un po’ di confusione e controllo e nella maggior parte dei casi si può ottenere un ID unico abbastanza rapidamente.

Come funziona : La formula usa la prima lettera della stringa e una lettera fissa presa da metà stringa e usa LEN() come ‘funzione di scansione’ per ridurre la possibilità di collisioni.

CAVEAT : questo non è un hash, ma quando avete bisogno di fare qualcosa velocemente, e potete controllare i risultati per vedere che non ci siano collisioni, funziona abbastanza bene.

Modifica: Se le vostre stringhe devono avere lunghezze variabili (ad esempio i nomi completi) ma sono estratte da un record di database con campi di larghezza fissa, vorrete fare così:

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

in modo che le lunghezze siano uno scrambler significativo.

Sto usando questo che dà risultati abbastanza buoni con la prevenzione dei conflitti senza bisogno di eseguire uno script ogni volta. Avevo bisogno di un valore tra 0 e 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)))

Prende le lettere da tutta la stringa, prende il valore di ciascuna di quelle lettere, aggiunge un valore (per evitare che le stesse lettere in posti diversi diano gli stessi risultati), moltiplica/divide ciascuna ed esegue una funzione COS sul totale.

Puoi provare questo. Esegui un Pseudo# su due colonne:

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

Dove A1 e B1 memorizzano semi casuali inseriti manualmente: 0

Per quanto ne so non c'è una funzione di hash in Excel - dovresti costruirne una come funzione definita dall'utente in VBA.

Comunque, nota che per il tuo scopo non penso che usare un hash sia necessario o realmente vantaggioso! VLOOKUP funzionerà altrettanto bene su 256 byte che su un hash più piccolo. Certo, potrebbe essere un po’ più lento - ma è sicuramente così piccolo da essere incommensurabile. E poi aggiungere i valori dell'hash è uno sforzo maggiore per voi - e per Excel…