小数から整数にする銀行丸めをエクセル関数で作る

エクセルのround関数は四捨五入ですが、それとはちょっと違う銀行丸め（偶数まるめ）を作ってみます。

そもそも銀行丸めとは何なのかはこちらの記事を参照してください。

作成する銀行まるめの仕様

小数第一位の数値を整数値に丸めるものを作ります。仕様はこのようになります。

具体的には下記の赤文字の場合に通常の四捨五入とは異なることになります。
0.5 → 0
1.5 → 2
2.5 → 2　
3.5 → 4
4.5 → 4
5.5 → 6　
6.5 → 6
7.5 → 8　
8.5 → 8
9.5 → 10

エクセル関数を多重構造にした場合、いきなり作るとエラーになった時に原因が分かりにくいです。順番に作っていきます。

=TRUNC(A1)

整数部分が取り出せました。これを元の値から引き算すれば小数部分が求められます。

小数部分が取り出せました。

=A1-TRUNC(A1)

しかしこれだと、マイナスの場合に扱いにくい状態です。
というのは、JIS　Z8401では「絶対値で四捨五入した後、マイナスの場合にはマイナスに戻す」となっているのに対し、マイナスの値だと切り上げ、切り捨てが逆になってしまうからです。

絶対値にした方がよさそうです。

ABS関数で絶対値にします。

=ABS(A1-TRUNC(A1))

1.4でも-1.4でも、小数部分は0.4として扱えます。
そしてこれが0.5である場合に、計算を分岐させる必要があります。

分岐させるために小数部分の絶対値が0.5かどうかを確認します。
今のところは、0.5なのか、そうでないかの表示だけ。

=IF(ABS(A1-TRUNC(A1))=0.5,"0.5だよ","0.5ではないよ")

0.5ではない時は普通に四捨五入したらOKです。

0.5に関係しない時には普通にroundで四捨五入します。

=IF(ABS(A1-TRUNC(A1))=0.5,"0.5だよ",ROUND(A1,0))

0.5の場合には、元の値の端数の0.5を除いた値を偶数に切り上げます。
偶数切り上げはEVEN関数です。

=IF(ABS(A1-TRUNC(A1))=0.5,EVEN(A1-0.5),ROUND(A1,0))

元の値の端数の0.5を除いた値を算出するにあたり、ここでもマイナスの場合に備えます。ABSで絶対値にします。

マイナスの場合を考慮して、EVEN関数の中で一旦ABS関数を入れ、絶対値にしてから引き算する。

=IF(ABS(A1-TRUNC(A1))=0.5,EVEN(ABS(A1)-0.5),ROUND(A1,0))

マイナスの時にもプラスになってしまっているので、ここを直す必要があります。

SIGN関数を使えばマイナスの場合に-1が取得できるので、これを掛け算することで値をマイナスに戻せます。

=IF(ABS(A1-TRUNC(A1))=0.5,EVEN(ABS(A1)-0.5)*SIGN(A1),ROUND(A1,0))

「絶対値で四捨五入した後、マイナスの場合にはマイナスに戻す」という方針のためには

※今回の例は、あくまでも小数第一位を整数にするものです。