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

21

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 mais) e eu adoraria ser capaz de criar um keyfield menor que seja facilmente reproduzível no caso de eu precisar para fazer um VLOOKUP de outro workset ou pasta de trabalho.

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

Quaisquer pensamentos ou ideias sobre esta ou outra estratégia?

    
por dwwilson66 13.02.2013 / 15:35

6 respostas

29

Você não precisa escrever sua própria função - outros já fizeram isso por você.
Por exemplo, coletei e comparei cinco funções de hash do VBA nessa resposta do stackoverflow

Pessoalmente, eu uso essa função do VBA

  • é chamado com =BASE64SHA1(A1) no Excel depois que você copiou a macro para um módulo do VBA
  • requer o .NET, pois usa a biblioteca "Microsoft MSXML" (com ligaçã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

Personalizando o tamanho do hash

  • o hash é inicialmente uma cadeia unicode de 28 caracteres (case sensitive + caracteres especiais)
  • Você personaliza o tamanho do hash com esta linha: Const cutoff As Integer = 5
  • 4 dígitos hash = 36 colisões em 6895 linhas = 0,5% taxa de colisão
  • 5 dígitos hash = 0 colisões em 6895 linhas = 0% taxa de colisão

Também há funções hash ( todas as três funções CRC16 ) que não exigem .NET e não usam bibliotecas externas. Mas o hash é mais longo e produz mais colisões.

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

    
por 13.02.2013 / 15:58
4

Eu não me importo muito com colisões, mas precisava de um pseudo-aleatorizador fraco de linhas com base em um campo de cadeia 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)

Em que Z2 é a célula que contém a string que você deseja usar como hash.

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

    
por 13.05.2016 / 21:56
3

Para uma lista razoavelmente pequena, você pode criar um embaralhador (função hash do homem pobre) usando funções integradas do Excel.

Por exemplo

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

Aqui A1 e B1 contêm uma letra inicial e um tamanho de string aleatórios.

Um pouco de correção e verificação e, na maioria dos casos, você pode obter uma identificação única viável rapidamente.

Como funciona : A fórmula usa a primeira letra da string e uma letra fixa tirada do meio da string e usa LEN () como uma 'função de fanning' para reduzir a chance de colisões.

CAVEAT : isso é não um hash, mas quando você precisa fazer algo rapidamente e pode inspecionar os resultados para ver se não há colisões, funciona muito bem.

Editar: Se suas strings tiverem comprimentos variáveis (por exemplo, nomes completos), mas forem extraídas de um registro de banco de dados com campos de largura fixa, convém fazer da seguinte maneira:

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

para que os comprimentos sejam um codificador significativo.

    
por 13.06.2013 / 16:48
1

Você pode tentar isso. Execute um pseudo # em duas colunas:

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

Onde A1 e B1 armazenam sementes aleatórias inseridas manualmente: 0     

por 05.11.2013 / 17:24
0

Até onde sei, não há nenhuma função hash construída no Excel - você precisaria criar uma função definida pelo usuário no VBA.

No entanto, observe que, para o seu propósito, não acho que usar um hash seja necessário ou realmente vantajoso! VLOOKUP funcionará tão bem em 256 bytes quanto em um hash menor. Claro, pode ser um pouquinho mais lento - pouco que é tão pequeno que é imensurável. E, em seguida, adicionar os valores de hash é mais um esforço para você - e para o Excel ...

    
por 13.02.2013 / 15:40
0

Estou usando isso, o que resulta em bons resultados na prevenção de conflitos sem a necessidade de executar um script a cada vez. Eu precisava de um valor entre 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)))

Ele seleciona letras de toda a string, pega o valor de cada uma dessas letras, adiciona um valor (para evitar que as mesmas letras em diferentes lugares forneçam os mesmos resultados), multiplica / divide cada uma e executa uma função COS sobre o total.

    
por 21.09.2018 / 18:16