microsoft excel 2010

¿Existe una función de Excel para crear un valor hash?

Estoy trabajando con una serie de listas de datos que están codificadas por nombre de documento. Los nombres de los documentos, aunque son muy descriptivos, son bastante engorrosos si necesito verlos (hasta 256 bytes es mucho espacio) y me encantaría poder crear un campo clave más pequeño que sea fácilmente reproducible en caso de que necesite para hacer una VLOOKUP desde otra hoja de trabajo o libro de trabajo.

Estoy pensando que un hash del título que sería único y reproducible para cada título sería lo más apropiado. ¿Hay una función disponible o estoy pensando en desarrollar mi propio algoritmo?

¿Alguna idea sobre esto u otra estrategia?

No es necesario que escriba su propia función:otros ya lo han hecho por usted.
Por ejemplo, recopilé y comparé cinco funciones hash de VBA en esta respuesta

Personalmente uso esta función VBA

se llama con =BASE64SHA1(A1) en Excel después de copiar la macro en unmódulo de VBA
requiere .NET ya que utiliza la librería "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

Personalización de la longitud del hash

el hash inicialmente es una cadena unicode de 28 caracteres (sensible a mayúsculas y minúsculas+caracteres especiales)
Personalizas la longitud del hash con esta línea: Const cutoff As Integer = 5
Hash de 4 dígitos=36 colisiones en 6895 líneas=0,5 % de tasa de colisión
hash de 5 dígitos=0 colisiones en 6895 líneas=0 % de tasa de colisión

También hay funciones hash ( las tres funciones CRC16 ) que no requieren .NET y no usan bibliotecas externas. Pero el hash es más largo y produce más colisiones.

También puede simplemente descargar este libro de trabajo de ejemplo y jugar con las 5 implementaciones de hash. Como veis hay una buena comparativa en la primera hoja

No me importan mucho las colisiones,pero necesitaba un pseudorandomizador débil de filas basado en un campo de cadenas de longitud variable.Aquí hay una solución loca que funcionó bien:

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

Donde Z2 es la celda que contiene la cadena que desea codificar.

Los "MOD" están ahí para evitar el desbordamiento a la notación científica. 1009 es un número primo, podría usar cualquier X para que X*255 < max_int_size . 10 es arbitrario; usa cualquier cosa Los valores "Else" son arbitrarios (¡dígitos de pi aquí!); usa cualquier cosa La ubicación de los caracteres (1,3,5,7,9) es arbitraria; usa cualquier cosa