MS Excelで端数処理 2: ワークシート関数で切り上げ・切り捨て・四捨五入・JIS丸め

スポンサーサイト

上記の広告は１ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

MS Excelで端数処理 2: ワークシート関数で切り上げ・切り捨て・四捨五入・JIS丸め

[追記 ()] FLOOR/CEILING関数の仕様がExcel 2010で変わっていたようで、記事で紹介されている数式のいくつかはExcel 2007以前のExcelでは動作しないことがわかりました。まとまった時間ができたときに情報をアップデートします。

前回の記事「MS Excelで端数処理 1: Excelの端数処理関数」から既に1ヶ月以上経ってしまった。標準的な端数処理 (切り捨て・切り上げ・四捨五入の類) は実装が終わっているので、とりあえずまとめ。

今回まとめているのは、丸め対象の数値を与えられればそれだけで丸め計算ができるものだけ。つまり、確率的丸めやディザリングのように乱数を使ったり、交互丸めのように状態変数を使うものは除外。

先に言ってしまうと、最近接偶数丸め(いわゆるJIS丸め)の数式以外は大しておもしろくもない数式になってしまったので、最近接偶数丸めの数式を知りたいだけの人は、末尾のまとめまで読み飛ばしてしまってもいいかも。

凡例

以下に挙げる数式では、すべて丸め対象の数値をA1、ROUND系関数に渡す桁数を$A$2、FLOOR/CEILING系関数に渡す基準値を$A$3としてある。

それ以外のセル範囲を指定した数式をコピーしたい場合は、↓のフォームで書き換えることができます。

変数名の書き換え

切り捨て・切り上げ系

切り捨て・切り上げは基本的にはExcelで用意された切り捨て・切り上げ関数を使う。

0への丸め (一般的な切り捨て)
=ROUNDDOWN(A1,$A$2)
=SIGN(A1)*FLOOR(ABS(A1),$A$3)

一般的な切り捨てはROUNDDOWN関数として実装されているので、通常の用途であればROUNDDOWN関数を使えばOK。

FLOOR関数を使って実装する場合は、ABS関数で数値を絶対値にしてから切り捨てし、SIGN関数で符号を復元してあげればOK。基準値が負の数になると意図した動作をしなくなるので要注意。

無限大への丸め (一般的な切り上げ; 0から遠ざける丸め)
=ROUNDUP(A1,$A$2)
=SIGN(A1)*CEILING(ABS(A1),$A$3)

一般的な切り上げもROUNDUP関数として実装されているので、普通はROUNDUP関数でOK。

CEILING関数を使った実装は↑のFLOOR関数と同じ考え方。絶対値に対して切り上げしてから符号を掛け合わせる。

負の無限大への丸め
=FLOOR(A1,$A$3)

FLOOR関数は常に負の無限大に近づくように数値を切り捨てるよう実装されている。

正の無限大への丸め
=CEILING(A1,$A$3)

CEILING関数はFLOOR関数の反対で、常に正の無限大に近づくように数値を切り上げる。

最近接丸め系 (常に最も近い整数に丸める; 0.5の丸め方は複数ある)

四捨五入系の基本的なアルゴリズムは「0.5を足してから切り捨て」もしくは「0.5を引いてから切り上げ」。切り捨て/切り上げが、0に近づく/0から離れるなのか、負の無限大に近づく/正の無限大に近づくなのかでバリエーションはあるけど、基本は同じ。

0.5を0へ近づける
=SIGN(A1)*CEILING(ABS(A1)-$A$3/2,$A$3)

「0.5を引いてから切り上げ」のパターン。0.5の代わりに基準値/2を使っている。また、絶対値に対して切り上げ処理をする必要があるので、ABS関数で絶対値を取ったりSIGN関数で符号を復元したりする。

0.5を無限大へ近づける (一般的な四捨五入; 0.5を0から遠ざける)
=ROUND(A1,$A$2)
=SIGN(A1)*FLOOR(ABS(A1)+$A$3/2,$A$3)

一般的な四捨五入はROUND関数として実装されているので、何も考えずにROUND関数を使えばOK。

FLOOR関数を使った実装は、「基準値/2を引いてから、0へ近づける切り捨て」を愚直に実装したもの。

0.5を負の無限大へ近づける
=CEILING(A1-$A$3/2,$A$3)

「基準値/2を引いてから、正の無限大へ近づける切り上げ」のパターン。絶対値の処理が不要なためシンプル。

0.5を正の無限大へ近づける
=FLOOR(A1+$A$3/2,$A$3)

「基準値/2を足してから、負の無限大へ近づける切り捨て」のパターン。

0.5を最近接偶数へ近づける (最近接偶数丸め; JIS丸め)
=IF($A$3<MOD(A1,$A$3*2),FLOOR(A1+$A$3/2,$A$3),CEILING(A1-$A$3/2,$A$3))
=SIGN(A1)*IF($A$3<MOD(ABS(A1),$A$3*2),FLOOR(ABS(A1)+$A$3/2,$A$3),CEILING(ABS(A1)-$A$3/2,$A$3))

2種類の数式を挙げてあるけど、その意味するところはほぼ同じ。通常は単純な上の式を使うけど、絶対値を処理する下の式の方が理解しやすいかも。

2で割った余りが1よりも小さいとき(0に近いとき)は0.5を0へ近づける丸め、それ以外のとき(2に近いとき)は0.5を無限大へ近づける丸め、というのが基本的な考え方。↓の数直線のように、1.0を境に四捨五入の方法を変えることで、0.5を最寄りの偶数に丸めている。

Banker's Rounding Illustration

[追記 ()] FLOOR/CEILING関数の仕様がExcel 2010で変わっていたようで、上記の式はExcel 2007以前のExcelでは動かないことがわかりました。Excel 2007以前でも動く修正版が↓。

=SIGN(A1)*IF($A$3<MOD(ABS(A1),$A$3*2),FLOOR(ABS(A1)+$A$3/2,$A$3),CEILING(ABS(A1)+$A$3/2,$A$3)-$A$3)
0.5を最近接奇数へ近づける
=IF($A$3<MOD(A1,$A$3*2),CEILING(A1-$A$3/2,$A$3),FLOOR(A1+$A$3/2,$A$3))
=SIGN(A1)*IF($A$3<MOD(ABS(A1),$A$3*2),CEILING(ABS(A1)-$A$3/2,$A$3),FLOOR(ABS(A1)+$A$3/2,$A$3))

最近接偶数丸めを逆にしただけ。

サンプルファイル

以上の数式を実際にExcelに入力してみた図が↓。

Rounding Formula Examples

[上記の表のExcelファイルを見る (拡張子を.xmlに偽装してあるので.xlsxに戻して開くこと)]

最近接偶数丸め(JIS丸め)の検証

おそらく、この記事で最も有用な情報はJIS丸めの数式だと思うので、この数式が正しく機能するかどうかは詳しく検証しておいた。

VBAのRound関数は(ワークシートのROUND関数とは違って)、JIS丸めを実装しているので、

Function VbaRound(x, y As Long)
    VbaRound = Round(x, y)
End Function

↑のようなマクロを使ってワークシートからVBAのRound関数を呼び出して、ワークシート関数で実装したJIS丸め数式の結果と比較してみる。

とりあえず乱数を適当に丸めてみた結果の例が↓。

Rounding random numbers

何度か実行してみてもVBA Round関数の結果とワークシートJIS丸めの結果は基本的に一致しているので、大きな間違いはなさそう。

↓生の乱数ではなく、目的の桁数より一桁多い桁数で事前に丸めた乱数を丸めてみた図。こうすると0.5がたくさん出てくるので、正しく0.5を処理しているかどうか確かめられる。

Rounding pre-rounded random numbers

例を見ての通り、多くはないがしばしば不一致が見られる。ただ、よく見るとVBA Roundの方が間違っているような…

この辺りの不一致は浮動小数点数の丸め誤差の影響だとは思うけど、詳しいことは不明。というか、Excelが浮動小数点数の処理をどう実装しているのかわからないので検証することができず。とりあえず、目視で確認した限りでは、不一致が起きた時に間違っているのはいつもVBA Roundの方なので、ワークシート関数で実装したJIS丸めは安心して使ってよいと思う。

[上記の表のExcelファイルを見る (拡張子を.xmlに偽装してあるので.xlsmに戻して開くこと)]

まとめ

エクセルで最近接丸め(JIS丸め)を実行する場合は、

=IF($A$3<MOD(A1,$A$3*2),FLOOR(A1+$A$3/2,$A$3),CEILING(A1-$A$3/2,$A$3))

を使用する。Yahoo知恵袋とかOKWaveなんかで派手に間違っている数式が紹介されていることがあるので気を付けましょう。

[追記 ()] FLOOR/CEILING関数の仕様がExcel 2010で変わっていたようで、上記の式はExcel 2007以前のExcelでは動かないことがわかりました。Excel 2007以前でも動く修正版が↓。

=SIGN(A1)*IF($A$3<MOD(ABS(A1),$A$3*2),FLOOR(ABS(A1)+$A$3/2,$A$3),CEILING(ABS(A1)+$A$3/2,$A$3)-$A$3)

蛇足

10の倍数のJIS丸めはニーズが多いと思うので、ROUND関数のような桁数指定でJIS丸め数式を生成するフォームを作りました。計算で定数に置き換えられる部分は置き換えてあるので多少は処理が速くなるかも？

最近接丸め数式の生成 
=IF(0.1<MOD(A1,0.2),FLOOR(A1+0.05,0.1),CEILING(A1-0.05,0.1))

[追記 ()] FLOOR/CEILING関数の仕様がExcel 2010で変わっていたようで、上記の式はExcel 2007以前のExcelでは動かないことがわかりました。Excel 2007以前でも動く修正版が↓。

=SIGN(A1)*IF(0.1<MOD(ABS(A1),0.2),FLOOR(ABS(A1)+0.05,0.1),CEILING(ABS(A1)+0.05,0.1)-0.1)

続く...

たぶん...

スポンサーサイト

関連記事

トラックバック URL

http://liosk.blog103.fc2.com/tb.php/212-afff8589

トラックバック

コメント

Yahoo知恵袋で見つけたJIS丸めの式を使って、どうも間違ってておかしいと思って検索しなおしてここへ辿り着きました。

数値）　123.45001
正）　123.5
誤）　123.4

こんな感じで間違ってたんだったかな？
このページの解説で上手くできて助かりました、ありがとうございます。
  • 2013-06-05
  • by TAN
  • id:mQop/nM.
  • 編集
JIS丸め、大変参考になりました。ありがとうございます。

それにしても、そろそろExcelとしてもこの方式でまるめる関数を標準装備してもよいのでは、と思ってしまいますね。
  • 2017-04-17
  • by detteiu
  • id:-

コメントの投稿

お名前
コメント
編集キー
 
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。