dec2bin 512以上を式を使って 0 ～ 65535と-32768 ～ 32767まで可能にしてみた。
0 ～ 65535
=DEC2BIN(QUOTIENT(A3,256),8)&DEC2BIN(MOD(A3,256),8)
0 0000000000000000
1 0000000000000001
2 0000000000000010
4 0000000000000100
8 0000000000001000
16 0000000000010000
32 0000000000100000
64 0000000001000000
128 0000000010000000
256 0000000100000000
512 0000001000000000
1024 0000010000000000
2048 0000100000000000
4096 0001000000000000
8192 0010000000000000
16384 0100000000000000
32768 1000000000000000
65535 1111111111111111
-32768 ～ 32767
範囲超過時のエラー出力無し
=IF(A22>=0,"0"&DEC2BIN(QUOTIENT(A22,256),7)&DEC2BIN(MOD(A22,256),8),
"1"&DEC2BIN(QUOTIENT(32768+A22,256),7)&DEC2BIN(MOD(32768+A22,256),8))
多分合ってると思う ｜----ここは、↑上の関数式を
↓ BIN2HEX("?"&DEC2BIN),2)としただけ
-1 1111111111111111 FFFF
-2 1111111111111110 FFFE
-3 1111111111111101 FFFD
-4 1111111111111100 FFFC
-5 1111111111111011 FFFB
-6 1111111111111010 FFFA
-7 1111111111111001 FFF9
-8 1111111111111000 FFF8
-9 1111111111110111 FFF7
-15 1111111111110001 FFF1
-16 1111111111110000 FFF0
-17 1111111111101111 FFEF
-31 1111111111100001 FFE1
-32 1111111111100000 FFE0
-33 1111111111011111 FFDF
-63 1111111111000001 FFC1
-64 1111111111000000 FFC0
-65 1111111110111111 FFBF
-127 1111111110000001 FF81
-128 1111111110000000 FF80
-129 1111111101111111 FF7F
-255 1111111100000001 FF01
-256 1111111100000000 FF00
-257 1111111011111111 FEFF
-511 1111111000000001 FE01
-512 1111111000000000 FE00
-513 1111110111111111 FDFF
-1023 1111110000000001 FC01
-1024 1111110000000000 FC00
-1025 1111101111111111 FBFF
-2047 1111100000000001 F801
-2048 1111100000000000 F800
-2049 1111011111111111 F7FF
-4095 1111000000000001 F001
-4096 1111000000000000 F000
-4097 1110111111111111 EFFF
-8191 1110000000000001 E001
-8192 1110000000000000 E000
-8193 1101111111111111 DFFF
-16383 1100000000000001 C001
-16384 1100000000000000 C000
-16385 1011111111111111 BFFF
-32767 1000000000000001 8001
-32768 1000000000000000 8000
0 0000000000000000 0000
1 0000000000000001 0001
2 0000000000000010 0002
4 0000000000000100 0004
8 0000000000001000 0008
16 0000000000010000 0010
32 0000000000100000 0020
64 0000000001000000 0040
128 0000000010000000 0080
256 0000000100000000 0100
512 0000001000000000 0200
1024 0000010000000000 0400
2048 0000100000000000 0800
4096 0001000000000000 1000
8192 0010000000000000 2000
16384 0100000000000000 4000
32767 0111111111111111 7FFF
＜20160817追記sta＞HEX部分。使う機会が訪れるとは、思わなかった。どうすればいいのか忘れてた。
=IF(A22>=0,BIN2HEX("0"&DEC2BIN(QUOTIENT(A22,256),7),2)&BIN2HEX(DEC2BIN(MOD(A22,256),8),2),
BIN2HEX("1"&DEC2BIN(QUOTIENT(32768+A22,256),7),2)&BIN2HEX(DEC2BIN(MOD(32768+A22,256),8),2))
＜20160817追記end＞
＜20160921追記sta＞
16bit用 BIN2DEC 負符号用
=IF(LEFT(F7,1)="1",(32768-(MID(F7,2,1)*12684
+MID(F7,3,1)*8192
+MID(F7,4,1)*4096
+MID(F7,5,1)*2048
+MID(F7,6,1)*1024
+MID(F7,7,1)*512
+MID(F7,8,1)*256
+MID(F7,9,1)*128
+MID(F7,10,1)*64
+MID(F7,11,1)*32
+MID(F7,12,1)*16
+MID(F7,13,1)*8
+MID(F7,14,1)*4
+MID(F7,15,1)*2
+MID(F7,16,1)*1))*-1,BIN2DEC(LEFT(F7,8))*256+BIN2DEC(RIGHT(F7,8)))
さらに
=IF(LEFT(F7,1)="1",(32768-(BIN2DEC("0"&MID(F7,2,7))*256+BIN2DEC(MID(F7,9,8))))*-1,BIN2DEC(MID(F7,1,8))*256+BIN2DEC(MID(F7,9,8)))
もうちょこっと
=IF(LEFT(F7,1)="1",-32768+(BIN2DEC(MID(F7,2,7))*256+BIN2DEC(MID(F7,9,8))),BIN2DEC(MID(F7,1,8))*256+BIN2DEC(MID(F7,9,8)))
8bit用 BIN2DEC 負符号用
=IF(LEFT(F71,1)="1",(128-(MID(F71,2,1)*64
+MID(F71,3,1)*32
+MID(F71,4,1)*16
+MID(F71,5,1)*8
+MID(F71,6,1)*4
+MID(F71,7,1)*2
+MID(F71,8,1)*1))*-1,BIN2DEC(F71))
=IF(LEFT(F7,1)="1",-128+BIN2DEC(MID(F7,2,7)),BIN2DEC(F7))
HEX2DECは、別セルにHEX2BINして、上記をかければいくはず。
＜20160921追記end＞
<20160926追記sta>
calc.exe電卓 にsendkeysでDEC2HEX DEC2BINをやらせる
http://blog.goo.ne.jp/tkhs1732/e/d96c80aff91c02c26784c4533cfea718
<20160926追記end>
<20171217追記sta>
後はMIDとHEX2BINでなんとかなるじゃないかっぁぁ～！！しらんかった。
Excel2007
Libre
逆がむずかしいとおもわれます。
E27とE30
=HEX2BIN(MID(DEC2HEX(B30,10),1,2),8)&HEX2BIN(MID(DEC2HEX(B30,10),3,2),8)&HEX2BIN(MID(DEC2HEX(B30,10),5,2),8)&HEX2BIN(MID(DEC2HEX(B30,10),7,2),8)&HEX2BIN(MID(DEC2HEX(B30,10),9,2),8)
F25とF29 文字列にしてます。今思い浮かぶのは、こんなとこ。
=IF(LEFT(E29,1)="1","-"&TEXT(2^39-(BIN2DEC("0"&MID(E29,2,7))*2^32+BIN2DEC(MID(E29,9,8))*2^24+BIN2DEC(MID(E29,17,8))*2^16+BIN2DEC(MID(E29,25,8))*2^8+BIN2DEC(MID(E29,33,8))),"0"),TEXT(BIN2DEC("0"&MID(E29,2,7))*2^32+BIN2DEC(MID(E29,9,8))*2^24+BIN2DEC(MID(E29,17,8))*2^16+BIN2DEC(MID(E29,25,8))*2^8+BIN2DEC(MID(E29,33,8)),"0"))
32bit整形は、また後日。
64bitだとVBAでDecimal使ってやるしかないすね
これも、また後日。
下記は、単にDecimal型で計算できるかのサンプルです。
Sub aaa()
Dim x01 As Variant
Dim x02 As Variant
x01 = CDec(2 ^ 64)
MsgBox x01
Sheets("Sheet2").Range("A1").Value = "'" & x01
x02 = Int(CDec((2 ^ 64 - 1) / (2 ^ 32)))
MsgBox x02
Sheets("Sheet2").Range("A2").Value = "'" & x02
x03 = CDec(2 ^ 64 - 1) - Int(CDec((2 ^ 32) * x02))
MsgBox x03
Sheets("Sheet2").Range("A3").Value = "'" & x03
End Sub
<20171217追記end>
<20171221追記sta>
Sub aaa02()
Dim x01 As Variant
Dim x02 As Variant
Dim const_2_64 As Variant
Dim const_2_64_1 As Variant
Dim const_2_50 As Variant
const_2_64 = CDec(2 ^ 32) * CDec(2 ^ 32)
MsgBox const_2_64
Sheets("Sheet2").Range("B1").Value = "2^64 ="
Sheets("Sheet2").Range("C1").Value = "'" & const_2_64
const_2_64_1 = CDec(2 ^ 32) * CDec(2 ^ 32) - 1
MsgBox const_2_64_1
Sheets("Sheet2").Range("B2").Value = "2^64 - 1="
Sheets("Sheet2").Range("C2").Value = "'" & const_2_64_1
x02 = Int(CDec(const_2_64_1 / (2 ^ 32)))
MsgBox x02
Sheets("Sheet2").Range("B3").Value = "(2^64 - 1) \ 2^32="
Sheets("Sheet2").Range("C3").Value = "'" & x02
Sheets("Sheet2").Range("D3").Formula = "=Dec2HEX(C3,10)"
x03 = CDec(const_2_64_1 - CDec((2 ^ 32) * x02))
MsgBox x03
Sheets("Sheet2").Range("B4").Value = "(2^64 - 1) Mod 2^32="
Sheets("Sheet2").Range("C4").Value = "'" & x03
Sheets("Sheet2").Range("D4").Formula = "=Dec2HEX(C4,10)"
'************************************************
const_2_50 = CDec(2 ^ 32) * CDec(2 ^ 18)
MsgBox const_2_50
Sheets("Sheet2").Range("B6").Value = "2^50 ="
Sheets("Sheet2").Range("C6").Value = "'" & const_2_50
const_2_50 = CDec(2 ^ 32) * CDec(2 ^ 18) + 123
MsgBox const_2_50
Sheets("Sheet2").Range("B7").Value = "2^50+123 ="
Sheets("Sheet2").Range("C7").Value = "'" & const_2_50
x02 = Int(CDec(const_2_50 / (2 ^ 32)))
MsgBox x02
Sheets("Sheet2").Range("B8").Value = "(2^50+123) \ 2^32="
Sheets("Sheet2").Range("C8").Value = "'" & x02
Sheets("Sheet2").Range("D8").Formula = "=Dec2HEX(C8,10)"
x03 = CDec(const_2_50 - CDec((2 ^ 32) * x02))
MsgBox x03
Sheets("Sheet2").Range("B9").Value = "(2^50+123) Mod 2^32="
Sheets("Sheet2").Range("C9").Value = "'" & x03
Sheets("Sheet2").Range("D9").Formula = "=Dec2HEX(C9,10)"
End Sub
VBAでの精度は、2^49まで、2^50からくるいます
<20171221追記end>
<20171224追記sta>MS Office2007にBASE関数はありません。2013からあるらしい。
Libreは64使わなければ大丈夫かと
下記Google
下記Libre
<20171224追記end>
<20171225追記sta>
とりあえず10桁の符号付き
DEC2BINとBIN2DEC -2147483648～2147483647まで
C5 =DEC2HEX(B5,10)
D5 =HEX2BIN(MID(C5,3,2),8)&HEX2BIN(MID(C5,5,2),8)&
HEX2BIN(MID(C5,7,2),8)&HEX2BIN(MID(C5,9,2),8)
E5 =BIN2HEX(MID(D5,1,8),2)&BIN2HEX(MID(D5,9,8),2)&
BIN2HEX(MID(D5,17,8),2)&BIN2HEX(MID(D5,25,8),2)
F5 =HEX2DEC("FF"&E5)
F6 =HEX2DEC(E6)
G7 =IF(LEN(D5)=32,IF(LEFT(D5,1)="1",HEX2DEC("FF"&E5),HEX2DEC("E5")),"Please32digits")
D8 DEC2BIN
=HEX2BIN(MID(DEC2HEX(B8,10),3,2),8)&HEX2BIN(MID(DEC2HEX(B8,10),5,2),8)&HEX2BIN(MID(DEC2HEX(B8,10),7,2),8)&HEX2BIN(MID(DEC2HEX(B8,10),9,2),8)
範囲チェックいれておいたほうが無難でしょう。
=IF(AND(B8>=-2147483648,B8<=2147483647,B8<>""),"関数を入れてください","範囲外です")
G8 BIN2DEC =IF(LEN(D8)=32,IF(LEFT(D8,1)="1",HEX2DEC("FF"&BIN2HEX(MID(D8,1,8),2)&BIN2HEX(MID(D8,9,8),2)&BIN2HEX(MID(D8,17,8),2)&BIN2HEX(MID(D8,25,8),2)),HEX2DEC(BIN2HEX(MID(D8,1,8),2)&BIN2HEX(MID(D8,9,8),2)&BIN2HEX(MID(D8,17,8),2)&BIN2HEX(MID(D8,25,8),2))),"Please32digits")
<20171225追記end>
