microsoft excel 2010

해시 값을 만드는 Excel 함수가 있습니까?

저는 문서 이름으로 키가 지정된 여러 데이터 목록으로 작업하고 있습니다. 문서 이름은 매우 설명적이지만 내가 문서를 볼 필요가 있는 경우 상당히 번거롭고(최대 256바이트는 많은 공간을 차지함) 필요한 경우 쉽게 재현할 수 있는 더 작은 키 필드를 생성할 수 있기를 바랍니다. 다른 작업 시트 또는 통합 문서에서 VLOOKUP 을 수행합니다 .

각 제목 에 대해 고유하고 재현할 수 있는 제목의 해시 가 가장 적절할 것이라고 생각합니다. 사용 가능한 기능이 있습니까? 아니면 나만의 알고리즘을 개발하려고 합니까?

이 전략이나 다른 전략에 대한 생각이나 아이디어가 있습니까?

당신은 당신 자신의 함수를 작성할 필요가 없습니다 - 다른 사람들이 이미 당신을 위해 그렇게 했습니다.
예를 들어 이 답변 에서 5개의 VBA 해시 함수를 수집하고 비교했습니다.

개인적으로 이 VBA 기능을 사용합니다.

  • 매크로를 VBA 모듈 에 복사한 후 Excel에서 =BASE64SHA1(A1) 로 호출됩니다 .
  • 라이브러리 "Microsoft MSXML"(후기 바인딩 포함)을 사용하기 때문에 .NET이 필요합니다.

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

해시 길이 사용자 정의

  • 해시는 처음에 28자 길이의 유니코드 문자열(대소문자 구분 + 특수 문자)입니다.
  • 다음 줄을 사용하여 해시 길이를 사용자 지정합니다. Const cutoff As Integer = 5
  • 4자리 해시 = 6895줄에서 36번 충돌 = 0.5% 충돌률
  • 5자리 해시 = 6895개 라인에서 충돌 0개 = 충돌률 0%

.NET이 필요하지 않고 외부 라이브러리를 사용하지 않는 해시 함수( 세 가지 CRC16 함수 모두 )도 있습니다. 그러나 해시는 더 길고 더 많은 충돌을 생성합니다.

예제 통합 문서 를 다운로드 하고 5가지 해시 구현을 모두 사용할 수도 있습니다. 보시다시피 첫 번째 시트에 좋은 비교가 있습니다




나는 충돌에 대해 별로 신경 쓰지 않지만 가변 길이 문자열 필드를 기반으로 하는 행의 약한 의사 난수 처리기가 필요했습니다. 다음은 잘 작동하는 미친 솔루션입니다.

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

여기서 Z2 는 해시하려는 문자열이 포함된 셀입니다.

"MOD"는 과학적 표기법에 오버플로를 방지하기 위해 있습니다. 1009 는 소수이고 X*255 < max_int_size 가 되도록 X를 사용할 수 있습니다 . 10은 임의적입니다. 무엇이든 사용하십시오. "Else" 값은 임의적입니다(여기서는 pi의 숫자입니다!). 무엇이든 사용하십시오. 문자(1,3,5,7,9)의 위치는 임의적입니다. 무엇이든 사용하십시오.