microsoft excel 2010

Existe uma função Excel para criar um valor hash

Estou trabalhando com várias listas de dados que são codificadas pelo nome do documento. Os nomes dos documentos, embora muito descritivos, são bastante complicados se eu precisar visualizá-los (até 256 bytes é muito espaço) e adoraria poder criar um campo-chave menor que seja prontamente reproduzível caso eu precise para fazer um VLOOKUP de outro workset ou pasta de trabalho.

Estou pensando que um hash do título que seria único e reproduzível para cada título seria mais apropriado. Existe uma função disponível ou estou pensando em desenvolver meu próprio algoritmo?

Alguma reflexão ou ideia sobre esta ou outra estratégia?

Não precisa de escrever a sua própria função-outros já o fizeram por si.
Por exemplo, coletei e comparei cinco funções de hash VBA nesta resposta

Pessoalmente,uso esta função VBA

é chamado com =BASE64SHA1(A1) no Excel depois que você copiou a macro para um módulo VBA
requer .NET uma vez que utiliza a biblioteca "Microsoft MSXML" (com encadernação tardia)

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

Personalização do comprimento do haxixe

o hash é inicialmente um fio unicode de 28 caracteres de comprimento (sensível a maiúsculas e minúsculas+caracteres especiais)
Você personaliza o comprimento do hash com esta linha: Const cutoff As Integer = 5
hash de 4 dígitos=36 colisões em 6895 linhas=0,5 % taxa de colisão
hash de 5 dígitos=0 colisões em 6895 linhas=0 % de taxa de colisão

Existem também funções de hash ( todas as três funções CRC16 ) que não requerem .NET e não usam bibliotecas externas. Mas o hash é mais longo e produz mais colisões.

Você também pode baixar esta pasta de trabalho de exemplo e brincar com todas as 5 implementações de hash. Como você vê, há uma boa comparação na primeira folha

Não me interessa muito as colisões,mas precisava de um fraco pseudorandomizador de linhas com base num campo de cordas de comprimento variável.Aqui está uma solução insana que funcionou bem:

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

Onde Z2 é a célula que contém a string que você deseja hash.

"MOD"s existem para evitar o transbordamento para notação científica. 1009 é um primo, pode usar qualquer coisa X para que X*255 < max_int_size . 10 é arbitrário; usar qualquer coisa. Os valores "Else" são arbitrários (dígitos de pi aqui!); usar qualquer coisa. A localização dos caracteres (1,3,5,7,9) é arbitrária; usar qualquer coisa.