Десятичное двоичное преобразование для больших чисел в Excel

Ответ 1

Если мы говорим о положительном числе между 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 десятичных цифр, число не будет округлено.

Ответ 2

См. 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

Ответ 3

Я просто попробовал формулу выше и обнаружил, что Microsoft испортила функцию DEC2BIN по-другому, что позволяет правильной работе формулы с отрицательными числами. Внутри DEC2BIN использует десятибитовый результат; ведущие нули удаляются из текстового результата, если не используется необязательный параметр длины, и в этом случае необходимое количество ведущих нулей остается в строке. Но здесь rub: отрицательное число всегда начинается с одного, поэтому нет начальных нулей, поэтому DEC2BIN всегда будет показывать все десять бит! Таким образом, DEC2BIN (-1,8), который должен показать 11111111 (восемь), вместо этого отобразит 1111111111 (десять единиц).

Чтобы исправить это, используйте ПРАВИЛЬНО, чтобы обрезать каждый восьмибитовый кусок до восьми бит, тупой, как это звучит.

=RIGHT(DEC2BIN(QUOTIENT(A1,256^3),8),8) & RIGHT(...

(Я читаю VBA, и у него нет такой же проблемы, но не похоже, что он вообще будет обрабатывать негативы.)

Ответ 4

Чтобы упростить чтение форматирования на 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

Ответ 5

Кто-то может найти здесь операции двоичного сдвига более понятными и актуальными

=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-битных значений

Ответ 6

Эта функция 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

Ответ 7

Возможно, более простой вариант:

Для только положительных чисел, просто используйте BASE (как в BASE2) для чисел от 0 до 2 ^ 53 в Excel. Вот несколько примеров:

=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

Числа, указанные в OP, требуют больше битов, поэтому я буду использовать 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)

Ответ 8

Здесь другой путь. Это не с одной формулой, но я пробовал и конвертировал до номера 2 099 999 999 999. Мое первое намерение состояло в том, чтобы построить 51-битный счетчик, но почему-то он не работает с числами за пределами того, о котором я упоминал. Загрузить с http://www.excelexperto.com/content/macros-production/contador-binario-de-51-bits/

Надеюсь, это полезно. С наилучшими пожеланиями.

Ответ 9

Хотя я не писал это для отрицательных или десятичных знаков, его относительно легко изменить. Этот 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

Ответ 10

Там может быть простое решение. У меня есть несколько 4,2 миллиарда ячеек, которые на самом деле являются отрицательными двумя дополнениями, и это работает, чтобы получить правильное значение: = СУММ (2 ^ 31- (А1-2 ^ 31))