21.14 在Excel里进行16进制运算

https://scz.617.cn/windows/201504291136.txt

Q:

我需要利用Excel进行一些数据分析工作，这些数据基本都是16进制表示，如何在
Excel里进行16进制运算？

A: scz 2015-04-29 11:18

Excel的单元格(Cell)不支持0xFFFFFFF7这种16进制表述，实际是不认0x前缀，只能
输入FFFFFFF7，同时将其格式设为"文本"，而不是"常规"或"数值"，这样可以避免很
多不必要的自动转换。

如果不考虑VBA介入，单元格里的公式只支持10进制四则运算，幸好Excel提供了如下
函数:

HEX2DEC()
DEC2HEX()
MOD()

考虑这个例子:

A1(16)      B1(10)  C1(16)
FFFFFFF7    10      1

本意是32-bits回绕下的加法运算:

0xFFFFFFF7 + 10 = 0x00000001

为达此目的，C1处的公式应该写成:

=DEC2HEX(MOD(HEX2DEC(A1)+B1,2^32))

Excel本身支持0x100000000对应的10进制数，必须MOD()才能确保32-bits回绕。

A: zyh & scz 2015-04-28

编辑HexHelper.bas如下:

--------------------------------------------------------------------------
Option Explicit

Private Function HexStr2Num ( hexstr As String ) As Currency
    Dim num As Currency
    Dim start As Boolean
    Dim n As Integer
    Dim tmp As String

    Select Case Left$( hexstr, 2 )
        Case "0x", "0X", "&H", "&h"
            hexstr  = Right$ ( hexstr, Len$( hexstr ) - 2 )
    End Select
    num     = 0
    start   = False
    For n = 1 To Len$( hexstr )
        If start Then
            num = num * 16
        End If
        tmp = Mid$( hexstr, n, 1 )
        Select Case tmp
            Case "A", "B", "C", "D", "E", "F", "a", "b", "c", "d", "e", "f"
                num     = num + ( Asc( tmp ) - 55 )
                start   = True
            Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9"
                num     = num + Val( tmp )
                start   = True
        End Select
    Next n
    HexStr2Num  = num
End Function

Private Function DecStr2Num ( decstr As String ) As Currency
    Dim num As Currency
    Dim start As Boolean
    Dim n As Integer
    Dim tmp As String

    num     = 0
    start   = False
    For n = 1 To Len$( decstr )
        If start Then
            num = num * 10
        End If
        tmp = Mid$( decstr, n, 1 )
        Select Case tmp
            Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9"
                num     = num + Val( tmp )
                start   = True
        End Select
    Next n
    DecStr2Num  = num
End Function

Private Function NumStr2Num ( numstr As String ) As Currency
    Dim num As Currency

    If "0X" = Mid$( UCase$( numstr ), 1, 2 ) Then
        num = HexStr2Num( numstr )
    Else
        num = DecStr2Num( numstr )
    End If
    NumStr2Num  = num
End Function

Private Function Num2HexStr ( num As Currency ) As String
    Dim start As Boolean
    Dim digit As Long
    Dim hexstr As String
    Dim power As Integer

    hexstr  = ""
    For power = 18 To 0 Step -1
        digit   = Int( num / ( 16 ^ power ) )
        If num >= 16 ^ power Then
            start   = True
            If digit > 9 Then
                hexstr  = hexstr + Chr$( digit + 55 )
            Else
                hexstr  = hexstr + LTrim$( Str$( digit ) )
            End If
            num     = num - ( digit * ( 16 ^ power ) )
        Else
            If start Then
                hexstr  = hexstr + "0"
            End If
        End If
    Next power
    If 0 = Len$( hexstr ) Then
        hexstr  = "00"
    End If
    If 1 = Len$( hexstr ) Mod 2 Then
        hexstr  = "0" + hexstr
    End If
    Num2HexStr  = "0x" + hexstr
End Function

Private Function SuperMod ( n As Currency, m As Currency ) As Currency
    SuperMod    = n - ( Int( n / m ) * m )
End Function

'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'

Public Function NumStr2HexStr ( numstr As String ) As String
    NumStr2HexStr   = Num2HexStr( NumStr2Num( numstr ) )
End Function

Public Function AddMod32 ( x1 As String, x2 As String ) As String
    Dim n1@, n2@, n3@

    n1          = SuperMod( NumStr2Num( x1 ), 2 ^ 32 )
    n2          = SuperMod( NumStr2Num( x2 ), 2 ^ 32 )
    n3          = SuperMod( n1 + n2, 2 ^ 32 )
    AddMod32    = Num2HexStr( n3 )
End Function

Public Function SubMod32 ( x1 As String, x2 As String ) As String
    Dim n1@, n2@, n3@

    n1          = SuperMod( NumStr2Num( x1 ), 2 ^ 32 )
    n2          = SuperMod( NumStr2Num( x2 ), 2 ^ 32 )
    If n1 >= n2 Then
        n3  = SuperMod( n1 - n2, 2 ^ 32 )
    Else
        n3  = n1 - n2 + 4294967296@
    End If
    SubMod32    = Num2HexStr( n3 )
End Function

Public Function MulMod32 ( x1 As String, x2 As String ) As String
    Dim n1@, n2@, n3@

    n1          = SuperMod( NumStr2Num( x1 ), 2 ^ 32 )
    n2          = SuperMod( NumStr2Num( x2 ), 2 ^ 32 )
    n3          = SuperMod( n1 * n2, 2 ^ 32 )
    MulMod32    = Num2HexStr( n3 )
End Function

Public Function DivMod32 ( x1 As String, x2 As String ) As String
    Dim n1@, n2@, n3@

    n1          = SuperMod( NumStr2Num( x1 ), 2 ^ 32 )
    n2          = SuperMod( NumStr2Num( x2 ), 2 ^ 32 )
    n3          = SuperMod( Int( n1 / n2 ), 2 ^ 32 )
    DivMod32    = Num2HexStr( n3 )
End Function

Public Function ModMod32 ( x1 As String, x2 As String ) As String
    Dim n1@, n2@, n3@

    n1          = SuperMod( NumStr2Num( x1 ), 2 ^ 32 )
    n2          = SuperMod( NumStr2Num( x2 ), 2 ^ 32 )
    n3          = SuperMod( SuperMod( n1, n2 ), 2 ^ 32 )
    ModMod32    = Num2HexStr( n3 )
End Function

Public Function CmpNumStr ( x1 As String, x2 As String ) As Integer
    Dim n1@, n2@, n3%

    n1          = NumStr2Num( x1 )
    n2          = NumStr2Num( x2 )
    If n1 > n2 Then
        n3  = 1%
    ElseIf n1 = n2 Then
        n3  = 0%
    Else
        n3  = -1%
    End If
    CmpNumStr   = n3
End Function
--------------------------------------------------------------------------

上述代码提供了32-bits回绕下的加、减、乘、除、模运算:

AddMod32
SubMod32
MulMod32
DivMod32
ModMod32

下面简单介绍如何使用上述代码。

--------------------------------------------------------------------------
1)

"Alt-F11"呼出VBA编辑器

2)

菜单->插入->模块

3)

复制、粘贴上述代码到右侧代码区

4)

"Alt-Q"退出VBA编辑器

5)

至此，在Excel单元格里可以直接使用AddMod32()这些函数。这些函数的形参是字符
串类型，内部处理了0x前缀。
--------------------------------------------------------------------------

考虑这个例子:

A1(16)      B1(10)  C1(16)
0xFFFFFFF7  10      0x01

C1处的公式:

=AddMod32(A1,B1)