logo标签列表

在Excel中将大数转换为二进制

excelvbaexcel-formula
33

我在Excel表格中有一些大数字，我想将它们转换成二进制。

例如：

12345678  
965321458  
-12457896
你尝试过使用DEC2BIN函数吗？ - JustinJDavies
1
请参见以下链接：https://dev59.com/O2Eh5IYBdhLWcg3wtFX4 - JustinJDavies
25
DEC2BIN 无法处理大于511的数字。 - ttaaoossuuuu
user3103991，Taosique在上面的评论中提供的信息通常会被期望你自己包含进去，以显示出你在研究方面付出了一定的努力。 - JustinJDavies
1
可能是使用DEC2BIN()处理大数字的重复问题。 - phuclv
12个回答
68
如果我们讨论的是介于0和2^32-1之间的正数，您可以使用以下公式：
=DEC2BIN(MOD(QUOTIENT($A$1,256^3),256),8)&DEC2BIN(MOD(QUOTIENT($A$1,256^2),256),8)&DEC2BIN(MOD(QUOTIENT($A$1,256^1),256),8)&DEC2BIN(MOD(QUOTIENT($A$1,256^0),256),8)

注意: =DEC2BIN() 函数无法处理大于511的数字，因此您会看到我的公式将您的数字分成四个8位块，将它们转换为二进制格式，然后将结果连接起来。

理论上，您可以将此公式扩展到六个8位块。在Excel中，您可以获得的最大精度为15（十五）个小数位。当超过这个范围时，只保留最显著的15位数，其余部分被四舍五入。例如，如果您键入12345678901234567，Excel将其存储为12345678901234500。所以由于2^48-1有15个十进制位数，因此该数字不会被四舍五入。

10
这太棒了，非常感谢。谢谢！！！还有需要注意的是，Microsoft你真丢人…！ - J-Dizzle
我正在尝试使用它，我已经按照您指定的最大值进行了测试：2^32-1，然后在此页面上进行了检查，但我没有得到相同的数字。 BASE 的限制更高：2^53，因此对于大于此数字的数字，可能可以应用类似的逻辑。 - David Leal
41
也许有一个更简单的选择：
对于仅为正数，在Excel中使用BASE（如BASE2）来表示0到2^53之间的数字。以下是一些示例：
=BASE(3,2)  # returns 11

=BASE(11,2)  # returns 1011

答案归功于：https://ask.libreoffice.org/en/question/69797/why-is-dec2bin-limited-to-82bits-in-an-32-and-64-bits-world/

负数：想一想，通过基于howy61的答案，可以处理负数。他通过将所有内容移位一个二次幂（在他的情况下为2^31）来使用2的补码：

=BASE(2^31+MyNum, 2)

所以（仅使用2^8表示8位）：

=BASE(2^8+(-1),2)  # returns 11111111 

=BASE(2^8+(-3),2)  # returns 11111101

由于原始问题给出的数字需要更多的位数，因此我将使用2^31（最高可达2^53）：

=BASE(2^31+(-12457896),2)  # returns 11111111010000011110100001011000

无论是正数还是负数，这两个公式都可以合并到一个IF公式中。以下是两种方式，它们给出相同的答案，其中MyNum是您开始使用的十进制数：
=IF(MyNum<0, BASE(2^31+MyNum,2), BASE(MyNum, 2))

或者

=BASE(IF(MyNum<0, MyNum+2^32, MyNum), 2)
1
“BASE”在Excel 2011 for Mac中不可用，尽管此页面（https://support.microsoft.com/en-ie/office/base-function-2ef61411-aee9-4f29-a811-1c42456c6342）显示应该可用。至少对于我现在测试的Excel for Mac 2011版本14.7.7来说是不可用的。 - Magne
2
被低估的答案...谢谢 - AKTanara
BASE适用于Excel 2013及以上版本，非常棒！ - Michel de Ruiter
注意这里省略了前导的 '0'，所以你需要添加。 - Jamie Marshall
10

看这里发布的VBA 链接 

' The DecimalIn argument is limited to 79228162514264337593543950245
' (approximately 96-bits) - large numerical values must be entered
' as a String value to prevent conversion to scientific notation. Then
' optional NumberOfBits allows you to zero-fill the front of smaller
' values in order to return values up to a desired bit level.
Function DecToBin(ByVal DecimalIn As Variant, Optional NumberOfBits As Variant) As String
  DecToBin = ""
  DecimalIn = CDec(DecimalIn)
  Do While DecimalIn <> 0
    DecToBin = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) & DecToBin
    DecimalIn = Int(DecimalIn / 2)
  Loop
  If Not IsMissing(NumberOfBits) Then
    If Len(DecToBin) > NumberOfBits Then
      DecToBin = "Error - Number too large for bit size"
    Else
      DecToBin = Right$(String$(NumberOfBits, "0") & _
      DecToBin, NumberOfBits)
    End If
  End If
End Function
5
我刚刚尝试了上述公式，发现Microsoft在DEC2BIN函数中出现了另一种错误，这种错误使得公式无法正确处理负数。在内部，DEC2BIN使用十位结果；在文本结果中省略前导零，除非使用可选的长度参数，在这种情况下，所需数量的前导零将留在字符串中。但是问题在于：负数始终以1开头，因此没有前导零可供省略，因此DEC2BIN将始终显示全部十个比特！因此，DEC2BIN(-1,8)应该显示11111111（八个1），但实际上会显示1111111111（十个1）。
为了解决这个问题，使用RIGHT将每个八位块修剪为八位，听起来很愚蠢，但却很有效。
=RIGHT(DEC2BIN(QUOTIENT(A1,256^3),8),8) & RIGHT(...

我阅读了VBA代码，发现它没有同样的问题，但似乎完全不能处理负数。

3

为了使Taosique的优秀答案更易于阅读，您还可以将其分成4位一组，并在它们之间加上空格以获得更好的格式，尽管公式会变得非常庞大：

=DEC2BIN(MOD(QUOTIENT($A$1,16^7),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A$1,16^6),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A$1,16^5),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A$1,16^4),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A$1,16^3),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A$1,16^2),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A$1,16^1),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A$1,16^0),16),4)

1101 0100 1111 0110 0011 0001 0000 0001

当然，如果您只对16位数字感兴趣，您可以仅使用其右半部分：
=DEC2BIN(MOD(QUOTIENT($A$1,16^3),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A$1,16^2),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A$1,16^1),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A$1,16^0),16),4)

0011 0001 0000 0001
使用Excel的TEXT函数更容易和清晰：=TEXT( DEC2BIN(MOD(QUOTIENT(A1,256^3),256),8) & DEC2BIN(MOD(QUOTIENT(A1,256^2),256),8) & DEC2BIN(MOD(QUOTIENT(A1,256^1),256),8) & DEC2BIN(MOD(QUOTIENT(A1,256^0),256),8), "00000 0000 000 00 0")这也使得通过在文本字符串中放置空格来将其分成不同长度的块变得容易。例如，如果A1包含14,163，则文本字符串“00000 0000 000 00 0”将给出01101 1101 010 01 1。 - Greg
似乎无法在上面的帖子中获得格式...我想让它看起来像上面的评论。 有人可以告诉我怎么做吗？ - Greg
1

有些人可能会在这里更清楚地理解和感受到二进制移位操作的相关性。

=DEC2BIN(BITRSHIFT($A$1,24),8) & DEC2BIN(MOD(BITRSHIFT($A$1,16),256),8) & DEC2BIN(MOD(BITRSHIFT($A$1,8),256),8) & DEC2BIN(MOD($A$1,256),8)

这个公式适用于32位的值

1

这个VBA函数解决了数字大于511的二进制转换问题，这是无法使用WorksheetFunction.dec2bin完成的。
该代码利用WorksheetFunction.dec2bin函数并将其分段应用。

Function decimal2binary(ByVal decimal2convert As Long) As String
Dim rest As Long
If decimal2convert = 0 Then
   decimal2binary = "0"
   Exit Function
End If
Do While decimal2convert > 0
   rest = decimal2convert Mod 512
   decimal2binary = Right("000000000" + WorksheetFunction.Dec2Bin(rest), 9) + decimal2binary
   decimal2convert = (decimal2convert - rest) / 512
Loop
decimal2binary = Abs(decimal2binary)
End Function
1
欢迎来到SO！仅包含代码的答案是不被鼓励的，因为它们无法提供解决问题的见解。请更新您的解决方案，包括说明您的代码如何解决手头的问题 :) - Joel
1
=IF(Decimal>-1,BASE(Decimal,2,32),BASE(2^32+(Decimal),2))

支持正数和负数。 花了一点时间，哈哈。技术双关语。 不客气。

1

虽然我没有为负数或小数编写这个程序，但是修改起来应该相对容易。这个VBA将把任何超大的十进制数（如果你愿意，也可以不那么大，但这不是重点）转换为包含多达32767位数字（在VBA中的最大字符串长度）的二进制结果。

在单元格"A1"中输入十进制数作为字符串，结果将作为字符串出现在"B1"中。

Dim NBN As String

Dim Bin As String

5 Big = Range("A1")

AA = Len(Big)

For XX = 1 To AA

L1 = Mid(Big, XX, 1) + CRY

CRY = 0

If L1 = 0 Then

FN = "0"

GoTo 10

End If

If Int(L1 / 2) = L1 / 2 Then

FN = L1 / 2

GoTo 10

End If

If Int(L1 / 2) <> L1 / 2 Then

FN = Int(L1 / 2)

CRY = 10

GoTo 10

End If

10 NBN = NBN & FN

Next XX

If Left(NBN, 1) = "0" Then

NBN = Right(NBN, (Len(NBN) - 1))

End If

If CRY = 10 Then Bin = "1" & Bin Else Bin = "0" & Bin

Range("A1") = NBN

Range("A2") = Bin

If Len(NBN) > 0 Then

NBN = ""

CRY = 0

GoTo 5

End If
0
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接