Excel中大数的十进制到二进制转换 [英] Decimal to binary conversion for large numbers in Excel

问题描述

我在 Excel 工作表中有一些大数字，我想将它们转换为二进制.

I have some large numbers in an Excel sheet and I want to convert them to binary.

例如

12345678  
965321458  
-12457896

推荐答案

如果我们说的是 0 和 2^32-1 之间的正数，你可以使用这个公式:

If we are talking positive number between 0 and 2^32-1 you can use this formula:

=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 位块，然后将它们转换为二进制格式化，然后连接结果.

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.

好吧，理论上您可以将此公式扩展到六个 8 位块.您可以在 Excel 中获得的最大精度为 15(十五)个十进制数字.超过时，只剩下最重要的 15 位数字，其余的四舍五入.IE.如果您键入 12345678901234567，Excel 会将其存储为 12345678901234500.因此，由于 2^48-1 是 15 位十进制数字，因此该数字不会四舍五入.

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 12345678901234567 Excel will store it as 12345678901234500. So since 2^48-1 is 15 decimal digits long the number won't get rounded.

这篇关于Excel中大数的十进制到二进制转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！