Chiquilin Site■11.03.09_Excel：最近接偶数への丸め（JIS丸め）を行う
ChiquilinSite
ご存知の人からすると 何を今更という話ですが ワークシート関数の ROUNDと VBAの Round関数は仕様が異なります。ワークシート関数の ROUND関数はいわゆる四捨五入（算術丸め）で VBAの Round関数は JIS丸め（偶捨奇入／銀行型丸め／五捨五入／最近接偶数への丸め）です。問題がない訳ではありませんが 丸め誤差が発生しにくいのは VBAの Round関数です。これを数式でやってしまおうというのが今回の趣旨です。
★その１：ROUNDと Roundの違い
まずはどう違うのかを知っておく必要がありますね。
B列がワークシート関数の Round関数。「=ROUND(A1,0)」を下方向にオートフィルコピーしたものです。こちらは解説いりませんね。C列が VBAの Round関数の結果です。オレンジのところが違っています。13行目の合計を見れば分かるのですが 四捨五入の場合 元の値とずれが大きくなっているのが分かるかと思います。これが丸め誤差です。この誤差を少なくする為に 丸める単位の値が「５」の時は偶数側に丸めるというのが VBAの Round関数で利用される丸め方式です。「JIS Z 8401」の規則Aや「ISO-31」で定められているので「JIS丸め」「ISO丸め」とも呼ばれますが（JIS基準では有効桁数を指定して丸めるので多少ルールが異なる） 定まった呼ばれ方がないのも有る意味特徴と云えます。銀行の丸め処理に利用されていたので「銀行型丸め」「銀行家丸め」とも云われますし 四捨五入に対応する形で「五捨五入」「四捨六入」「偶捨奇入」などとも呼ばれます。
★その２：ワークシート関数で五捨五入を行う
ではやってみます。短くしようと思えばできますが 桁数指定と負の値に対応させようと思うと ある程度長くはなります。
Ａ１セルに値があるとして 桁数が「０」なら
=IF(MOD(ABS(A1),1)=0.5,EVEN(ABS(A1)-0.5)*SIGN(A1),ROUND(A1,0))
となります。もし桁数を数式で指定できるようにしようと思ったら
数値を「値」 桁数を「桁数」と名前定義した場合
=IF(MOD(ROUND(値*10^(桁数+1),10),10)=5,EVEN(ABS(値)*10^桁数-0.5)/10^桁数*SIGN(値),ROUND(値,桁数))
もしくは
=IF(MOD(ROUND(値*10^(桁数+1),10),10)=5,EVEN(TRUNC(値,桁数)*10^桁数)/10^桁数,ROUND(値,桁数))
よく考えたら
=IF(MOD(ROUND(値*10^(桁+1),0),10)=5,ROUND(値/2,桁)*2,ROUND(値,桁))
これで良かったですね。
こんな形になるかと思います(11.07.24 修正)。ユーザー定義関数を作った方が断然楽ですが VBAの Round関数については浮動小数点による演算誤差対策が必要なのと負の方向の桁数指定非対応など これはこれで使いにくいところがあります。数式は名前定義しておけますので こういう方法も一つ手段としてはありかなと思います。尚 上記の数式はいずれも小数点位置が決まっている場合の方法ですのでご注意下さい。
有効数字を指定して丸める場合はやり方を変えたほうがいいかもしれませんが。
=IF(RIGHT(LEFT(TEXT(値,"0."&REPT(0,有効数字+1)&"E+00"),有効数字+3),2)="50",ROUND(LEFT(TEXT(値,"0."&REPT(0,有効数字+1)&"E+00"),有効数字+3)/2,有効数字-1)*2,ROUND(LEFT(TEXT(値,"0."&REPT(0,有効数字+1)&"E+00"),有効数字+3),有効数字-1))*10^RIGHT(TEXT(値,"0."&REPT(0,有効数字+1)&"E+00"),3)
Excel2019以上なら
=LET(_a,TEXT(値,"0."&REPT(0,有効数字+1)&"E+00"),_b,LEFT(_a,有効数字+3),IF(RIGHT(_b,2)="50",ROUND(_b/2,有効数字-1)*2,ROUND(_b,有効数字-1))*10^RIGHT(_a,3))
ひとまず。
