36 votos

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

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

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

¿Alguna idea sobre esta u otra estrategia?

39voto

KevinM1 Puntos 181

No necesitas escribir tu propia función, otros ya lo han hecho por ti.
Por ejemplo, recogí y comparé cinco funciones de hash de VBA en este respuesta al desbordamiento de la pila

Personalmente uso esta función VBA

  • se llama con =BASE64SHA1(A1) en Excel después de haber copiado la macro a un VBA módulo
  • requiere .NET ya que utiliza la biblioteca "Microsoft MSXML" (con encuadernación tardía)

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

Personalizar la longitud del hash

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

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

También podrías descargar esto ejemplo de libro de trabajo y jugar con las 5 implementaciones de hachís. Como ves, hay una buena comparación en la primera hoja

1 votos

Se ve muy bien. Sin embargo, no tengo suficiente experiencia en VBA para evitar que Excel devuelva #NAME? . Ver código > cortar y pegar el código en una nueva ventana -- dentro de la hoja de trabajo correcta en el navegador > guardar como hoja de trabajo habilitada para macros > cerrar y volver a excel... ¿algo más que me esté perdiendo? ¿Necesito compilarlo de alguna manera?

0 votos

Sí... para aclarar... lo pegué en la nueva ventana de código que apareció cuando fui a la pestaña de hoja de cálculo > ver código... Descargando el ejemplo ahora mismo, pero me gustaría entender por qué excel no reconoce mi código

0 votos

WooHoo ... la hoja de muestra ayudó. Me di cuenta de que había pegado el código en una ventana OBJECT de Excel, no en una ventana MÓDULO. ¡Estoy recibiendo hash en mi libro de trabajo ahora!

15voto

Anonymous Coward Puntos 101

No me importan mucho las colisiones, pero necesitaba un pseudorandomizador débil de filas basado en un campo de cadena 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)

Dónde Z2 es la celda que contiene la cadena de caracteres que se quiere convertir en hash.

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

5voto

Nick Puntos 11

Para una lista razonablemente pequeña se puede crear un codificador (la función de hash del pobre) usando las funciones incorporadas de Excel.

Por ejemplo.

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

Aquí A1 y B1 tienen una letra de inicio y una longitud de cadena aleatorias.

Un poco de manipulación y verificación y en la mayoría de los casos se puede obtener una identificación única factible bastante rápido.

Cómo funciona : La fórmula utiliza la primera letra de la cuerda y una letra fija tomada de la mitad de la cuerda y utiliza LEN() como una "función de abanico" para reducir la posibilidad de colisiones.

CAVEAT Esto es no un hachís, pero cuando necesitas hacer algo rápidamente, y puedes inspeccionar los resultados para ver que no hay colisiones, funciona bastante bien.

Editar: Si sus cuerdas deben tener longitudes variables (por ejemplo, nombres completos) pero se extraen de un registro de la base de datos con campos de anchura fija, querrá hacerlo así:

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

de modo que las longitudes son un codificador significativo.

1 votos

¡Gran respuesta! (: "función hash del pobre", "advertencia", "cómo funciona" :)

1 votos

A "inspeccionar los resultados para ver que no hay colisiones" podría simplemente intentar / probar esto ejecutando DATA > REMOVE DUPLICATES y ver si hay alguno. Obviamente, si encuentra duplicados, puede volver a ejecutar la función anterior de forma iterativa hasta que no queden duplicados.

2voto

Ant Cole Puntos 21

Estoy usando esto que da bastante buenos resultados con la prevención de choque sin necesidad de ejecutar un script cada vez. Necesitaba un valor entre 0 y 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)))

Elige letras de toda la cadena, toma el valor de cada una de esas letras, añade un valor (para evitar que las mismas letras en diferentes lugares den los mismos resultados), multiplica/divide cada una y ejecuta una función COS sobre el total.

1voto

Puedes probar esto. Ejecute un Pseudo# en dos columnas:

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

Donde A1 y B1 almacenan semillas aleatorias introducidas manualmente: 0

EnMiMaquinaFunciona.com

EnMiMaquinaFunciona es una comunidad de administradores de sistemas en la que puedes resolver tus problemas y dudas.
Puedes consultar las preguntas de otros sysadmin, hacer tus propias preguntas o resolver las de los demás.

Powered by: