10進数を2進数に変換するDEC2BIN()関数の上限

関数:DEC2BIN、IF、TEXT、MOD、VALUE
・関数DEC2BIN
・書式=DEC2BIN(10進数の数値)
・内容DEC2BIN 関数を使用して10進数を2進数に変換します。
使用例
=DEC2BIN(31)
=11111

=DEC2BIN(21)
=10101

10進数を2進数に変換するDEC2BIN()関数は上限がります
=DEC2BIN(511)
=111111111(←1が9個

DEC2BIN(512)以上だと#NUM!エラーになります

2進数から10進数のように512以上を数式にしてみます

エクセルでは15桁以上は値が丸められてしまうので
限界の15桁まで考えます

表示文字数の限界は2進数の
111111111111111(←1が15個になります
これは10進数の32767です

通常に10進数を2進数にする方法は数式でもありますが
ここはエクセルですから
数式にとらわれず数学ではできない柔軟な発想で解きます

流れ
32768以上はエラーにします

511以下はDEC2BIN関数を使います

10桁から15桁を各桁1か0かを判定して&でつなげます

0と1の15桁をVALUE関数で数値化します

完成

では最初に元の数字
セルH2)に「32767」10進数を入れます



結果が11桁を超えると1.11111E+11と短縮されるので
111111111111111と表示させるには
セルの書式設定を数式にしておく必要があります

まず10進数が32767以下で32768以上でエラーにします
=IF(J2>32767,"32768以上は計算できません","")

511以下はDEC2BIN関数を使います
=IF(J2<512,DEC2BIN(J2),"0")

先頭の「0」が消えてしまうのでTEXT関数で強制的に9文字表示
=TEXT(DEC2BIN(J2),"000000000")

全体を512で割った余りが511以下の数字になるのでMOD関数で余りを取得
=MOD(J2,512)

上記を組み合わせて、下9桁の2進数は
=TEXT(IF(J2<512,DEC2BIN(J2),DEC2BIN(MOD(J2,512))),"000000000")


2進数111111111111111
各桁1かを判定

=IF(J2>=16384,"1","0")←15桁目
=IF(MOD(J2,16384)>=8192,"1","0")←14桁目
=IF(MOD(J2,8192)>=4096,"1","0")←13桁目
=IF(MOD(J2,4096)>=2048,"1","0")←12桁目
=IF(MOD(J2,2048)>=1024,"1","0")←11桁目
=IF(MOD(J2,1024)>=512,"1","0")←10桁目
9桁以下は
=TEXT(IF(J2<512,DEC2BIN(J2),DEC2BIN(MOD(J2,512))),"000000000")

これを全部「&」でくっつけると文字列になってしまうので
全部をVALUE()でくくって数値にして
最初の32768以上のエラーメッセージを組み込みます
=IF(J2>32767,"32768以上は計算できません",VALUE())


287文字です。BIN2DECの15桁よりはかなり短いです。


上記のエラーメッセージは
=IF(J2>32767,"32768以上は計算できません",VALUE())

32768以上で「32768以上は計算できません」と結果として表示しますが
入力規則で32768以上で警告がでて入力できないようにすることができます



データタブの中の「データの入力規則」を選択します



設定タブの「入力値の種類」の整数を選択します



データを「次の値の間」
最小値「0」
最大値「32767」にします



32768以上の数値を入力すると
ポップアップでエラーメッセージが表示されます

このポップアップメッセージは変更することができます



「データの入力規則」で「エラーメッセージ」タブを選択

タイトルに「32768以上は計算できません」

エラーメッセージに
「2進数が16桁以上は計算できません。0~32767の整数を入力してください」
と入力します



ポップアップのエラーメッセージが変更されます

以上 10進数を2進数に変換でした


2進数を10進数に変換TOP
(C) 2001-2018 Digital World