2進数と16進数の相互変換！それExcelで出来るから・・その2

こんにちは・・ちちくろです♪

前回・・過去に面倒な作業が有って・・Excelで進数の置き換えを使って効率よくできないか・・ということでExcelの関数で進数を置き換える方法を説明したのですが・・。

その関数とは・・BIN・DEC・HEXの、それぞれが2進数・10進数・16進数に対応するということで・・それぞれを・・【置き換え枚の進数】【2】【置き換え後の進数】で可能ということを書いてみたのですが・・。

今回はその関数を使ってテンプレートを作ってみることに・・。

どのように作成したのかご説明します♪

テンプレートの概要

図のようなワークシートを作成

図の囲み①に・・読みだした16進数を書き込むと・・。

図の囲み②に囲み①に書き込んだ16進数を置き換えた2進数が・・ひとつのセルに1ビット毎に表示され・・。

囲み③に囲み②を置き換えた16進数が常時される・・当然この時点では・・囲み③と囲み①同じ数値になる・・。

そして・・図の③の2進数の数値を変更する・・特定のビットを0→1もしくは1→0に変更すると・・。

図の④変更された2進数に対応した16進数の数値が表示される・・。

と・・ま・・こんな感じのテンプレートを作ってみようかなと・・。

1バイトを2つ

2進数の表示は8桁とする・・要するに1バイト単位で表示するということ・・2進数は8ビット単位で1バイトというので・・。

で・・私が過去に経験した作業は・・データは16ビットの単位だったので・・これを2組組み合わせて作ってみようかと・・。

2進数の表示

2進数の表示は1セルに1文字・・0もしくは1を表示させることに・・同様のセルを8個1組・・これで1バイト・・さらに8ビットのセル2組で合計2バイト・・。

入力セル範囲作成

では具体的なテンプレートの作成方法は・・まずは読みだした18進数を入力セルの作成・・。

8個のセルを結合・・これを図のように4つ作成・・。

2行目のセルC2:J2に16進数を書き込み・・そして3行目のセルC3:J3に関数を書き込む・・。

使用する関数は・・HEX2BIN関数・・セルC3の数式は・・【=HEX2BIN($C$2,8)】・・。

この数式の内容は・・セルC2の16進数を8桁の2進数に・・というもの・・。

連結セルK3;R3も同様に・・。

そして・・16進数を書き込み・・連結したセルC2：J2に    【F2】・・連結したセルK2:R2には・・【FF】を入力・・。

すると3行目のセルそれぞれに・・2進数に置き換えられた数値が・・。

連結セルC3:J3には・・16進数【F2】を2進数に置き換えた【11110010】が表示され・・連結セルK3：R2には・・【11111111】が表示されます・・。

2進数を1セル1文字

次に・・2進数の表示を1セル1文字にするには・・今回はこれが一番の難問・・複雑なので説明するのも・・これまた難問！！

考え方としては・・8桁の数の左から任意の文字数を取り出し・・末尾の文字を取り出せばよいことに・・。

使用する関数は・・。

・RIGHT関数

・LEFT関数

• B4のセルにはC3:J3セルの1文字目
• C4のセルにはC3:J3セルの2文字目

実際の数式は・・セルC4の数式は【=RIGHT(LEFT($B$3,1),1)】となり・・セルD4の数式は【=RIGHT(LEFT($B$3,2),1））となります

数式の説明は・・分かりやすいようにセルF4の【= RIGHT(LEFT($B$3,4),1)】で説明を・・。

LEFT関数で左から4文字を取り出し・・取り出した文字は【1111】・・。

取り出した【1111】から右から1文字を取り出す・・取り出した結果【1】となります。

これをセルC4からJ4に同様に書き込み・・。

数式をそろえる

セルに数式を書き込む場合・・できれば数式は同じほうが楽ちん♪

何故なら・・一つのセルに数式を書き込み・・コピーして貼り付けることが出来るから♪

そんなことが出来るのかというと・・セルの参照方法を・・絶対参照と相対参照を上手く使えば可能・・。

セルの絶対参照は・・数式をコピーして貼り付けても・・参照先セルの位置は変わらない・・。

相対参照の場合は・・数式をコピーして貼り付けると・・コピー元から移動した分だけ参照先セルも移動することに・・。

そこで・・LEFT関数で取り出す文字数を計算式で変えてしまえばOK

そこで4行目に新たに行を追加し・・2進数の桁数（BIT）を追加

セルB4に・・【16】セルC4に・・【15】という感じで・・。

そしてセルB5の数式を【=RIGHT(LEFT($B$3,17-B4),1)】に変更・・。

LEFT関数の【LEFT($B$3,17-B4)】は 固定した参照先セルB3の文字から･･17からB4の値を引いた数取り出す・・17からB4のセルの数（16を引く）と・・1になる・・。

こうやって・・LEFT関数で取り出す文字数を変更できます♪

実際に見てみると・・セルB5の数式をセルC5からI5にコピーして・・セルC5の数式を見てみると・・セルC5の数式は【=RIGHT(LEFT($B$3,17-C4),1)】・・になっており。

17からセルC4の値15を引くと・・2になるので・・セルB3～取り出すのは左から2文字になります。

更にセルD5の数式は・・【=RIGHT(LEFT($B$3,17-D4),1)】となり・・。

17からセルD4の値14を引くと・・4になりセルB3から取り出すのは・・左から3文字に・・。

数式を合体

ワークシートはシンプルなのが良いですよね・・。

はっきり言って・・このワークシートで3行目は・・表示する必要ないかと・・。

3行目を非表示にすればよいのですが・・それでは面白くないので3行目を不要にしようかと・・。

現在のセルC5の数式は【=RIGHT(LEFT($B$3,17-C4),1)】・・絶対参照先のセルB3の数式は【=HEX2BIN($B$2,8)】なので・・【$B$3】の部分を・・【HEX2BIN($B$2,8)】に置き換えれば・・いいことに・・。

最終的にセルB5の数式は・・【=RIGHT(LEFT(HEX2BIN($B$2,8),17-B4),1)】に変更。

そして・・セルG5の数式をもう一度セルD5からJ5にコピー・・セルK5IからR5も同様に・・。

不要になった3行目を削除しても数式の結果に影響なしなので・・これでOKということに・・。

数式が複雑になるけど・・ワークシートがスッキリするので良いかと・・数式が複雑になるのを嫌うのであれば・・3行目を非表示にするだけでも可能です。

お好みでどうぞ♪

空白時は空白に

16進数を書き込む連結セルC2:J2　K2:R2が空白の時・・表示は図のように・・。

セルC4からR4まで・・すべて【0】が表示されています・・。

これ・・なんかスッキリしない・・参照先セルが空白ならば参照元セルも空白になってほしいですよね・・。

そこで・・お約束の・・【IF（ISBLANK（参照先セル）】を使うことに・・。

セルC4の数式を【=IF(ISBLANK($B$2),””,RIGHT(LEFT(HEX2BIN($B$2,8),17-B3),1))】に変更・・と言っても・・図の赤い部分を付け足しただけ♪

ISBLSNKでセルB2を参照して・・空白ならば・・空白にすればOK

セルD4からR4も同様に・・。

これで16進数を入力するセルが空白なら・・2進数が表示されるセルも空白に・・。

編集出力用セルの作成

次は・・2進数編集するセルを作成しようかと・・。

セルC4からR4のセルには数式が書きこまれているので・・これを直接編集することはできないので・・。

そこで・・セルC4からR4を・・セルC5からR5にリンクコピーすることに・・。

2進数を編集するのは・・C5からR5のセルで編集するようにしたいと思います♪

2進数を連結

今2進数の値は・・バラバラの状態・・これだと扱いにくいので・・せっかく苦労して分解したけど今度は合体させることに・・。

合体させるのは・・かんたん・・CONCAT関数を使えば一発！！

連結会えるC6:J6にCONCAT関数で数式を書き込む・・。

書き込む数式は【=CONCAT(C5,D5,E5,F5,G5,H5,I5,J5)】というもの・・。

同じく連結セルK6:R6に数式【=CONCAT(K5,L5,M5,N5,O5,P5,Q5,R5)】を書き込むと・・。

連結セルC6:J6に表示されるのは・・【11110010】と表示・・連結セルK6:R6に表示されるのは11111111】が・・。

16進数に置き換え

9行目に6行目の数値を16進数に置き換えることに・・。

使用する関数はBIN2HEX。

連結セルC7:J7に書き込む数式は【=BIN2HEX(C6,2)】・・すると・・連結セルC7:J7に表示されるのは【F2】

                   連結セルK6:R6に書き込む数式は・・【=BIN2HEX(J8,2)】・・そして連結セルK6:R6に表示されるのは【FF】が表示されます。

これは・・連結セルC2:J2とK2:R2に書き込んだものと同じ・・しかし・・セルC5からR5の値を・・0→1や1→0に変更すると連結セルC7:J7とK7:R7の表示が変化します♪

数式を結合

入力セルと同じように・・7行目の2進数は表示させる必要がないので・・入力用セル範囲の時と同様に数式を結合させる・・。

結合セルC6の数式は【=CONCAT(B7,C7,D7,E7,F7,G7,H7,I7)】なので・・数式を【=BIN2HEX(CONCAT(C5,D5,E5,F5,G5,H5,I5,J5),2)】に変更

連結セルK7:R7も同様に変更します・・。

その後6行目を削除しても・・特に問題なく表示されます♪

更に空白時は空白にするために・・数式に【IF（ISBLANK（参照先セル）】を追加・・。

連結セルC6:J6の数式は【=IF(ISBLANK(C2),””,BIN2HEX(CONCAT(C5,D5,E5,F5,G5,H5,I5,J5),2))】に変更

そして連結セルK8:E8の数式は【=IF(ISBLANK(K2),””,BIN2HEX(CONCAT(K5,L5,M5,N5,O5,P5,Q5,R5),2))】に変更しましょう

動作確認

動作を確認として・・セルC5からR5の2進数の数値を変更してみると・・それに連動して連結セルC6:J6とK6:R6の表示も変わる・・。

問題点発覚

取りあえずの動作はOK・・しかし問題点が                ・・。

新たに16進数を読みだして再度実行しようチスルと・・。

上手く動かなくなる・・これはセルC5からR5の数式が変わってしまうため・・。

まぁ・・意図的に2進数の数値を変更しているので当たり前・・。

本来・・例えばセルC5には・・【=C4】という数式が入っていないとダメなのですが・・

2進数の数値を変更すると・・【0】もしくは【1】の数値が入っていることに・・。

これを回避するには・・セルC4からR4をコピーして・・C5からR5にリンク貼り付けすればよいのですが・・。

まとめ

16進数を2進数に変換して・・1つのセルに1文字毎表示させるには・・RIGHT関数とLEFT関数を使って・・目的のビット一の数値を取り出せばOK

問題点としては・・2進数編集用セルの数式が変わってしまい・・繰り返し作業するには・・連結セルC2:J2もしくはJ2:R2に新しく16進数を書き込んだ後に・・セルC4kからR4をコピーして・・セルC5からR5にリンク貼り付けする必要が・・。

それだとチョット・・スマートじゃない・・。

回避策をどうしようか・・試案が必要・・。

はっきり言って関数では無理っぽい感じですが・・次回で何とか回避できるように頑張ります。