Excel関数にてdouble型16進数の浮動小数点を10進数へ変換する

ExcelのVBAにてdouble型16進数の浮動小数点を10進数へ変換する のエクセル関数版です。
マクロの使用が禁止されている場所にて使うために作りました。

ただし、標準関数のみを使っているためかなり無理やり変換しています。
VBA版と同じ方法で検算していますが。かなり無理やりなので正確性は保証しません。

こちらはもう説明するよりも使ってみたほうがいいと思うので、先にダウンロードリンクを貼っておきます。

• 16進浮動小数点を数値に変換関数版 [XLS形式‎ 2013‎年‎2‎月‎23‎日 62.0 KB (63,488 バイト)]

なお、このシートについては特に使用制限等ありません。ただし、このシートをこのまま転載や配布を行う場合、作者情報は消さないようにお願いします。

このシートについて

リトルエンディアン形式のみ対応しています。

ここでは各数値の詳細の説明はしません。詳細な説明はVBA版を参考にしてください。

=VLOOKUP(MID(＜値が代入されたセル＞,＜桁数＞,1),＜16進数がセットされた表＞,2,0)&・・・

任意のセルに入力された数値を1文字ずつ切り取り、2進数に変換するためにまずはMID関数で文字を1つずつ切り出しその値でVLOOKUPで2進数値を選択します。サンプルの説明シートD7セルに該当します。

このシートは分析ツールを有効にすることにより、以下のように書き換えることも可能です。

=HEX2BIN(MID(＜値が代入されたセル＞,＜桁数＞,1),4)&・・・

VBA版では特に変更する必要がないため使用しませんでしたが、関数版ではもし分析ツールが使用可能な場合はこちらを使用することをおすすめします。そう すれば16進数→2進数の表を用意する必要がなくなり、シートがスッキリします。この部分はサンプルの説明シートD44に該当します。この部分が 『#NAME?』となっていない場合はこちらが使用出来ます。

2進数が演算できたら、2進数の文字列より2文字目から12文字目までをMID関数で1文字ずつ抜き出し、2文字目を2¹⁰（2^10）12文字目を2⁰（2^0）でかけてそれをすべて足します。

=(MID(＜2進数に変換したセル＞,＜文字数＞,1)*2^＜階乗（10～0）＞)+・・・

ここはExcelの制限内に収まるので1つのセルで行けます。（サンプルの説明シートE31に該当）この部分は10乗から0乗の降順となっています。0から10ではないのでご注意ください。

次に13文字目から64文字目をMID関数で1文字ずつ抜き出し、2文字目を1÷2¹（1/2^1）64文字目を1÷2⁵²（1/2^52）でかけ、ソレをすべて足します。

=(MID(＜2進数に変換したセル＞,＜文字数＞,1)*(1/2^＜階乗（1～52）＞))+・・・

この部分ではExcelの制限数より関数が長くなるので複数のセルに分割して書かなければエラーが出ます。また、こちらの部分は上と違って1乗から52乗の昇順となっています。サンプルでは10乗毎に6分割しています。（サンプルの説明シートのE19～J19に該当）

そして最後にすべての計算結果を計算して結果を出します。

=((-1^MID(＜2進数に変換したセル＞,1,1))*(2^(＜指数部の解＞-1023))*(1+SUM(＜仮数部の解＞)))

以上で変数部分は終わります。やっていることは至って単純で複雑な計算はありませんが。なにせ計算量が多いので非常に面倒くさい構造となっています。もし かしたらExcel2020くらいまで待てば16進数倍精度浮動小数点を10進数や2進数に変換する関数が実装されるかもしれませんがMS次第なのでなん とも言えません。 今時点ではこの方法でしか計算することができません。多分。

改版履歴

• 2013/02/23　初出Rev1
• 2016/03/11　ブログに統合するに伴い、ちょっとだけ修正して再掲載