vba Excel中大数的十进制到二进制转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24387894/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Decimal to binary conversion for large numbers in Excel
提问by user3103991
I have some large numbers in an Excel sheet and I want to convert them to binary.
我在 Excel 工作表中有一些大数字，我想将它们转换为二进制。
e.g.
例如
12345678
965321458
-12457896
回答by ttaaoossuu
If we are talking positive number between 0and 2^32-1you can use this formula:
如果我们谈论的是正数0，2^32-1你可以使用这个公式：
=DEC2BIN(MOD(QUOTIENT($A,256^3),256),8)&DEC2BIN(MOD(QUOTIENT($A,256^2),256),8)&DEC2BIN(MOD(QUOTIENT($A,256^1),256),8)&DEC2BIN(MOD(QUOTIENT($A,256^0),256),8)
NOTE:=DEC2BIN()function cannot handle numbers larger than 511 so as you see my formula breaks your number into four 8-bit chunks, converts them to binary format and then concatenates the results.
注意：=DEC2BIN()函数无法处理大于 511 的数字，因此您会看到我的公式将您的数字分成四个 8 位块，将它们转换为二进制格式，然后连接结果。
Well, theoretically you can extend this formula up to six 8-bit chunks. Maximum precision you can get in Excel is 15 (fifteen) decimal digits. When exceeded, only the most significant 15 digits remain, the rest is rounded. I.e. if you type 12345678901234567Excel will store it as 12345678901234500. So since 2^48-1is 15 decimal digits long the number won't get rounded.
好吧，理论上您可以将此公式扩展到六个 8 位块。您可以在 Excel 中获得的最大精度为 15（十五）个十进制数字。超过时，只剩下最重要的 15 位数字，其余的四舍五入。即，如果您键入12345678901234567Excel 会将其存储为12345678901234500. 因此，由于2^48-1长度为 15 位十进制数字，因此该数字不会四舍五入。
回答by JustinJDavies
See VBA posted here
请参阅此处发布的 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
回答by Joe Avins
I just tried the formula above, and found that Microsoft screwed up the DEC2BIN function in another way that keeps the formula from working correctly with negative numbers. Internally, DEC2BIN uses a ten bit result; leading zeroes are dropped from the text result, unless the optional length parameter is used, in which case the required number of leading zeroes are left in the string. But here's the rub: a negative number always starts with a one, so there are no leading zeroes to drop, so DEC2BIN will always show all ten bits! Thus, DEC2BIN(-1,8), which should show 11111111 (eight ones) will instead show 1111111111 (ten ones.)
我刚刚尝试了上面的公式，发现 Microsoft 以另一种方式搞砸了 DEC2BIN 函数，从而使公式无法正确处理负数。在内部，DEC2BIN 使用十位结果；从文本结果中删除前导零，除非使用了可选的长度参数，在这种情况下，字符串中会保留所需数量的前导零。但问题是：负数总是以 1 开头，因此没有前导零要删除，因此 DEC2BIN 将始终显示所有十位！因此，应该显示 11111111（8 个）的 DEC2BIN(-1,8) 将显示 1111111111（10 个）。
To fix this, use RIGHT to trim each eight bit chunk to eight bits, dumb as that sounds.
要解决此问题，请使用 RIGHT 将每个八位块修剪为八位，这听起来很愚蠢。
=RIGHT(DEC2BIN(QUOTIENT(A1,256^3),8),8) & RIGHT(...
(I read through the VBA, and it does not have the same problem, but it doesn't look like it will handle negatives at all.)
（我通读了 VBA，它没有同样的问题，但看起来它根本无法处理底片。）
回答by deasserted
Perhaps a simpler option:
也许一个更简单的选择：
For positive numbers only, just use BASE (as in BASE2) for numbers between 0 to 2^53 in Excel. Here are some examples:
仅对于正数，只需将 BASE（如在 BASE2 中）用于 Excel 中 0 到 2^53 之间的数字。这里有些例子：
=BASE(3,2) # returns 11
=BASE(11,2) # returns 1011
Credit for answer goes here: https://ask.libreoffice.org/en/question/69797/why-is-dec2bin-limited-to-82bits-in-an-32-and-64-bits-world/
答案的功劳在这里：https: //ask.libreoffice.org/en/question/69797/why-is-dec2bin-limited-to-82bits-in-an-32-and-64-bits-world/
Negative numbers: Come to think of it, negative numbers could be handled as well by building upon howy61's answer. He shifts everything by a power of two (2^31 in his case) to use the 2's complement:
负数：想想看，负数也可以通过建立在 howy61 的答案来处理。他用 2 的幂（在他的例子中是 2^31）来移动所有东西以使用 2 的补码：
=BASE(2^31+MyNum, 2)
so (using 2^8 for only 8 bits):
所以（仅对 8 位使用 2^8）：
=BASE(2^8+(-1),2) # returns 11111111
=BASE(2^8+(-3),2) # returns 11111101
The numbers given by the OP requires more bits, so I'll use 2^31 (could go up to 2^53):
OP 给出的数字需要更多位，因此我将使用 2^31（最多可达 2^53）：
=BASE(2^31+(-12457896),2) # returns 11111111010000011110100001011000
For either positive or negative, both formulas could be coupled in a single IF formula. Here are two ways you could do it that give the same answer, where MyNum is the decimal number you start with:
对于正数或负数，两个公式都可以耦合在一个 IF 公式中。这里有两种方法可以给出相同的答案，其中 MyNum 是您开始的十进制数：
=IF(MyNum<0, BASE(2^31+MyNum,2), BASE(MyNum, 2))
or
或者
=BASE(IF(MyNum<0, MyNum+2^32, MyNum), 2)
回答by Nate
To add easier to read formatting to Taosique's great answer, you can also break it up into chunks of 4 bits with spaces in between, although the formula grows to be a monster:
为了使 Taosique 的出色答案更易于阅读，您还可以将其分解为 4 位的块，中间有空格，尽管公式变得很复杂：
=DEC2BIN(MOD(QUOTIENT($A,16^7),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A,16^6),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A,16^5),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A,16^4),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A,16^3),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A,16^2),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A,16^1),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A,16^0),16),4)
1101 0100 1111 0110 0011 0001 0000 0001
Of course, you can just use the right half of it, if you're just interested in 16 bit numbers:
当然，如果你只对 16 位数字感兴趣，你可以只使用它的右半部分：
=DEC2BIN(MOD(QUOTIENT($A,16^3),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A,16^2),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A,16^1),16),4)&" "&DEC2BIN(MOD(QUOTIENT($A,16^0),16),4)
0011 0001 0000 0001
回答by Geom
Someone can find binary shift operations more clear and relevant here
有人可以在这里找到更清晰和相关的二进制移位操作
=DEC2BIN(BITRSHIFT($A,24),8) & DEC2BIN(MOD(BITRSHIFT($A,16),256),8) & DEC2BIN(MOD(BITRSHIFT($A,8),256),8) & DEC2BIN(MOD($A,256),8)
This formula is for 32-bit values
此公式适用于 32 位值
回答by Diego
This vba function solves the problem of binary conversion of numbers greater than 511 that can not be done with WorksheetFunction.dec2bin.
The code takes advantage of the WorksheetFunction.dec2bin function by applying it in pieces.
这个vba函数解决了WorksheetFunction.dec2bin无法做到的大于511的数字的二进制转换问题。
该代码通过分段应用 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
回答by jbs3141
While I didn't write this for negatives or decimals, it should be relatively easy to modify. This VBA will convert any super large (or not so large if you want, but that wasn't the point) decimal up to the converted binary result containing up to 32767 digits (maximum string length in VBA).
虽然我没有为负数或小数写这个，但它应该相对容易修改。此 VBA 将任何超大（或不那么大，如果您愿意，但不是那么大）十进制转换为包含最多 32767 位数字（VBA 中的最大字符串长度）的转换后的二进制结果。
Enter decimal in cell "A1" as a string, result will be in "B1" as a string.
在单元格“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
回答by Víctor Urbina
Here's another way. It's not with a single formula, but I have tried and converted up to the number 2,099,999,999,999. My first intention was to build a 51 bit counter, but somehow it does not work with numbers beyond the one I mentioned. Download from http://www.excelexperto.com/content/macros-production/contador-binario-de-51-bits/
这是另一种方式。它不是一个单一的公式，但我已经尝试并转换为数字 2,099,999,999,999。我的第一个意图是构建一个 51 位计数器，但不知何故它不适用于我提到的数字之外的数字。从http://www.excelexperto.com/content/macros-production/contador-binario-de-51-bits/下载
I hope it's useful. Regards.
我希望它有用。问候。
回答by howy61
There maybe a simple solution. I have several 4.2 billion cells that are actually a negative Two's Complement and this works to get the correct value: =SUM(2^31-(A1-2^31))
也许有一个简单的解决方案。我有几个 42 亿个单元格，它们实际上是负的二进制补码，这可以得到正确的值：=SUM(2^31-(A1-2^31))
