在VBA Excel中计算汉明重量和/或距离

I’m trying to compare clients, two by two, whose qualities can be defined by binary choices (for example a client uses a product or not).
After much search online, it looks like I’d need to use the Hamming Distance for that, or its equivalent: find the Hamming Weight to the result of an XOR operation between two words.

我试图比较客户端,两个一个,其质量可以通过二元选择来定义(例如客户端使用或不使用产品)。经过大量的在线搜索后,看起来我需要使用汉明距离或其等价物:找到汉明重量到两个单词之间的XOR运算结果。

For a concrete example, Hamming distance between 1001 & 1011:

举一个具体的例子,汉明距离在1001和1011之间:

Calculate the number 1001 XOR 1011= 0010
Hamming weight of 0010 = 1 (numbers of bit set to 1 in 0010)

计算数字1001 XOR 1011 = 0010汉明重量为0010 = 1(在0010中设置为1的位数)

I need to do that in for words up to 96 bits.

对于高达96位的字,我需要这样做。

I found some information on

我找到了一些信息

http://people.revoledu.com/kardi/tutorial/Similarity/HammingDistance.html

http://trustedsignal.blogspot.ca/2015/06/xord-play-normalized-hamming-distance.html

and plenty of pieces of code , for example

例如,有很多代码片段

Hamming weight written only in binary operations?

汉明重量只写在二元运算中?

but only in C, Java , Perl, O, opencl... anything but Excel VBA.

但只能在C,Java,Perl,O,opencl ......除了Excel VBA之外的任何东西。

So far, here’s what I manage to put together.

到目前为止,这是我设法整理的内容。

It works, but unfortunately only for words of 30 bits or less, and uses a somewhat crude method: XOR on the two numbers X and Y, then convert to a string that represent the binary number. Then count the length of the string once the 1’s are taken out. I guess there’s a more elegant and efficient way.

它可以工作,但遗憾的是只有30位或更少的字,并使用一些粗略的方法:对两个数字X和Y进行XOR,然后转换为表示二进制数的字符串。一旦取出1,就计算字符串的长度。我想这是一种更优雅,更有效的方式。

Public Function HamDist(x As Long, y As Long, NbBit As Integer)

Dim i As Long, BinStrg As String, bxor As Long 

bxor = x Xor y 

BinStrg = "" 

For i = NbBit To 0 Step -1 ‘going from left to right 
         If bxor And (2 ^ i) Then
            BinStrg = BinStrg + "1" ‘add a 1 to the string 
         Else
            BinStrg = BinStrg + "0"
         End If
      Next

 HamDist = Len(BinStrg) - Len(Replace(BinStrg, "1", "")) ' replace the 1 by nothing and count  the length of the resulting string 
End Function

Can you help make it works for 96 bit words in VBA for Excel 2010 and below (udf or sub), either by calculating the hamming weight or distance ?

你可以通过计算汉明重量或距离来帮助它在VBA for Excel 2010及更低版本(udf或sub)中使用96位字吗?

1 个解决方案

#1

If you store the chain of qualities in String form (e.g. a String consisting only of the letters 'T' and 'F'), this could quite easily be done using a loop.

如果以String形式存储品质链(例如,仅包含字母“T”和“F”的字符串),则可以使用循环轻松完成。

Function hammingDistance(qualities1 As String, qualities2 As String) As Integer

    If Len(qualities1) <> Len(qualities2) Then
        hammingDistance = -1
        Exit Function
    End If

    Dim i, result As Integer
    result = 0

    For i = 1 To Len(qualities1)
        If Mid(qualities1, i, 1) <> Mid(qualities2, i, 1) Then result = result + 1
    Next

    hammingDistance = result

End Function