Usuario del software
2013-02-13 14:35:30 +0000 2013-02-13 14:35:30 +0000
27

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

Estoy trabajando con una serie de listas de datos que se teclean 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 hacer un VLOOKUP desde otro conjunto de trabajo o libro de trabajo.

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

¿Alguna idea sobre esta u otra estrategia?

Respuestas [6]

35
2013-02-13 14:58:13 +0000

No necesitas escribir tu propia función - otros ya lo hicieron por ti.
Por ejemplo, he recopilado y comparado cinco funciones hash de VBA en esta respuesta de stackoverflow

Personalmente uso esta función de VBA

  • se llama con =BASE64SHA1(A1) en Excel después de copiar la macro a un mó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)
  • Se personaliza 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 % tasa de colisiones
  • 5 dígitos hash = 0 colisiones en 6895 líneas = 0 % tasa de colisiones

También hay funciones hash las tres funciones CRC16 ) que no requiere .NET y no utiliza librerías externas. Pero el hash es más largo y produce más colisiones.

También puedes descargar este libro de ejemplo y jugar con las 5 implementaciones de hash. Como ves hay una buena comparación en la primera hoja

35
9
2016-05-13 19:56:41 +0000

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 quieres hashear.

"MOD "s están ahí para evitar el desbordamiento a la notación científica. 1009 es un primo, podría usar cualquier X de manera que X*255 |< max_int_size. 10 es arbitrario; use 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; use cualquier cosa.

9
3
2013-06-13 14:48:09 +0000

Para una lista razonablemente pequeña puedes crear un codificador (la función hash de los pobres) utilizando 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 contienen una letra inicial aleatoria y la longitud de la cadena.

Con un poco de esfuerzo y comprobación, en la mayoría de los casos se puede obtener un identificador único bastante rápido.

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

CAVEAT : esto no es un hash, pero cuando necesitas hacer algo rápidamente, y puedes inspeccionar los resultados para ver que no hay colisiones, funciona bastante bien.

Edición: Si sus cadenas deben tener longitudes variables (por ejemplo, nombres completos) pero se extraen de un registro de la base de datos con campos de ancho fijo, querrá hacerlo así:

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

para que las longitudes sean un revuelto significativo.

3
2
2018-09-21 16:16:37 +0000

Estoy usando esto que da muy buenos resultados con la prevención de choque sin necesidad de ejecutar una secuencia de comandos 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)))

Elimina las letras de 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.

2
1
2013-11-05 16:24:05 +0000

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

1
0
2013-02-13 14:40:20 +0000

Hasta donde yo sé, no hay una función hash incorporada a Excel. Tendría que crear una como función definida por el usuario en VBA.

Sin embargo, tenga en cuenta que para su propósito no creo que el uso de un hash sea necesario o realmente ventajoso. VLOOKUP funcionará tan bien en 256 bytes como en un hash más pequeño. Claro, puede ser un poco más lento - pero eso es tan pequeño que es inconmensurable. Y luego añadir los valores hash es más esfuerzo para ti - y para Excel...

0

Preguntas relacionadas