microsoft excel 2010

Esiste una funzione di Excel per creare un valore hash?

Sto lavorando con una serie di elenchi di dati che sono codificati in base al nome del documento. I nomi dei documenti, sebbene molto descrittivi, sono piuttosto ingombranti se devo visualizzarli (fino a 256 byte è molto immobile) e mi piacerebbe essere in grado di creare un campo chiave più piccolo che sia facilmente riproducibile nel caso avessi bisogno per eseguire un VLOOKUP da un altro workseet o cartella di lavoro.

Sto pensando che un hash dal titolo che sarebbe unico e riproducibile per ogni titolo sarebbe il più appropriato. È disponibile una funzione o sto cercando di sviluppare il mio algoritmo?

Qualche idea su questa o un'altra strategia?

Non è necessario scrivere la propria funzione:altri l'hanno già fatto per voi.
Ad esempio, ho raccolto e confrontato cinque funzioni hash VBA su questa risposta

Personalmente utilizzo questa funzione VBA

  • viene chiamato con =BASE64SHA1(A1) in Excel dopo aver copiato la macro in unmodulo VBA
  • richiede .NET poiché utilizza la libreria "Microsoft MSXML" (con binding tardivo)

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 (sensibile alle maiuscole e alle minuscole e ai caratteri speciali).
  • Personalizza la lunghezza dell'hash con questa riga: Const cutoff As Integer = 5
  • Hash a 4 cifre=36 collisioni in 6895 linee=tasso di collisione dello 0,5%.
  • Hash a 5 cifre=0 collisioni in 6895 linee=tasso di collisione dello 0 %.

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

Potresti anche scaricare questa cartella di lavoro di esempio e giocare con tutte e 5 le implementazioni 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 contenente la stringa di cui vuoi eseguire l'hashing.

I "MOD" servono a prevenire il traboccamento della notazione scientifica. 1009 è un numero primo, potrebbe usare qualsiasi cosa X in modo che X*255 < max_int_size . 10 è arbitrario; usa qualsiasi cosa. I valori "Else" sono arbitrari (cifre di pi qui!); usa qualsiasi cosa. La posizione dei caratteri (1,3,5,7,9) è arbitraria; usa qualsiasi cosa.