Skip to content

Latest commit

 

History

History
256 lines (199 loc) · 6.47 KB

201504291136.txt.md

File metadata and controls

256 lines (199 loc) · 6.47 KB
Raw

21.14 在Excel里进行16进制运算

http://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

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

"Alt-F11"呼出VBA编辑器

菜单->插入->模块

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

"Alt-Q"退出VBA编辑器

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

考虑这个例子:

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

C1处的公式:

=AddMod32(A1,B1)