MS Excelで端数処理 1: Excelの端数処理関数

スポンサーサイト

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

MS Excelで端数処理 1: Excelの端数処理関数

Excelで四捨五入をしたいときは、

=ROUND(A1,0)

と入力します。

というような無内容なことを書きたいのではなくて、端数処理 - Wikipediaに載っているような各種の端数処理をExcelで実装するにはどうすればよいかという話。複数の丸め方をパッと試してみたくなることが結構あるので、各端数処理の数式をまとめておく感じ。

各種各様の端数処理

日本語Wikipediaの端数処理や英語WikipediaのRoundingの頁を見た感じだと、とりあえず押さえておかなければいけない処理方法は↓のような感じ。

切り捨て・切り上げ系
0への丸め (一般的な切り捨て)
1.5 => 1, -1.5 => -1
無限大への丸め (一般的な切り上げ; 0から遠ざける丸め)
1.5 => 2, -1.5 => -2
負の無限大への丸め
1.5 => 1, -1.5 => -2
正の無限大への丸め
1.5 => 2, -1.5 => -1
最近接丸め系 (常に最も近い整数に丸める; 0.5の丸め方は複数ある)
0.5を0へ近づける
2.5 => 2, 1.5 => 1, -1.5 => -1, -2.5 => -2
0.5を無限大へ近づける (一般的な四捨五入; 0.5を0から遠ざける)
2.5 => 3, 1.5 => 2, -1.5 => -2, -2.5 => -3
0.5を負の無限大へ近づける
2.5 => 2, 1.5 => 1, -1.5 => -2, -2.5 => -3
0.5を正の無限大へ近づける
2.5 => 3, 1.5 => 2, -1.5 => -1, -2.5 => -2
0.5を最近接偶数へ近づける (最近接偶数丸め; JIS丸め)
2.5 => 2, 1.5 => 2, -1.5 => -2, -2.5 => -2
0.5を最近接奇数へ近づける
2.5 => 3, 1.5 => 1, -1.5 => -1, -2.5 => -3
確率的丸め (Stochastic rounding)
0.5には、それぞれ1/2の確率で+0.5もしくは-0.5を加える。
交互丸め (Alternating tie-breaking)
0.5には、それぞれ交互に+0.5もしくは-0.5を加える。
その他

五捨六入やディザリング等

それぞれの端数処理方法の特徴・バイアスや計算方法は英語Wikipediaに詳しいので、興味があればそちらをご参照のこと。

すごく多くの種類があるように見えるけど、基本的には丸めの戦略(切り上げ切り捨て or 最近接丸め)と丸めの方向(小さくする or 大きくする)と丸めの基準(絶対値 or 符号付き実数)の掛け算で8パターンできるから、それで水増しされている感じ。

Excelの端数処理関数たち

手元にあるExcel 2010で調べたところ、Excelには↓のような端数処理関数がある模様。基本的にはこれらの関数を組み合わせて↑の端数処理方法を実装していくことになるはず。{CEILING,FLOOR}.PRECISEは、どうやらExcel 2010で追加された関数らしい。

桁数指定系
ROUND(数値, 桁数)
数値を四捨五入して指定された桁数にします。
ROUNDDOWN(数値, 桁数)
数値を指定された桁数で切り捨てます。
ROUNDUP(数値, 桁数)
数値を指定された桁数に切り上げます。
TRUNC(数値, [桁数])
数値の小数部を切り捨てて、整数または指定された桁数にします。
基準値指定系
CEILING(数値, 基準値)
指定された基準値の倍数のうち、最も近い値に数値を切り上げます。
CEILING.PRECISE(数値, [基準値])
指定された基準値の倍数のうち、最も近い値に数値を切り上げます。数値は正負に関係なく切り上げられます。
FLOOR(数値, 基準値)
指定された基準値の倍数のうち、最も近い値に数値を切り捨てます。
FLOOR.PRECISE(数値, [基準値])
指定された基準値の倍数のうち、最も近い値に数値を切り捨てます。数値は正負に関係なく切り捨てられます。
MROUND(数値, 倍数)
指定された値の倍数になるように、数値を切り上げまたは切り捨てます。
おまけ
INT(数値)
指定された数値を最も近い整数に切り捨てます。

関数のヘルプを眺めていてもさっぱりイメージできないので、とりあえずテスト値を突っ込んで計算させてみることにした。

↓まずは各関数の比較。ROUND系は正負で結果が対称になっているので、絶対値を基準に丸めている(0への丸め or 無限大への丸め)様子。CEILING/FLOOR系とINTは正負で結果が食い違っているので、符号付き実数を基準に丸めている(負の無限大への丸め or 正の無限大への丸め)みたい。

formula ROUND(x,y) ROUNDDOWN(x,y) ROUNDUP(x,y) TRUNC(x,y) CEILING(x,y) CEILING.PRECISE(x,y) FLOOR(x,y) FLOOR.PRECISE(x,y) MROUND(x,y) INT(x/y)*y
y 11110.10.10.10.10.10.1
x +1.4 +1.4 +1.4 +1.4 +1.4 +1.4 +1.4 +1.4 +1.4 +1.4 +1.4
+1.38 +1.4 +1.3 +1.4 +1.3 +1.4 +1.4 +1.3 +1.3 +1.4 +1.3
+1.35 +1.4 +1.3 +1.4 +1.3 +1.4 +1.4 +1.3 +1.3 +1.4 +1.3
+1.33 +1.3 +1.3 +1.4 +1.3 +1.4 +1.4 +1.3 +1.3 +1.3 +1.3
+1.3 +1.3 +1.3 +1.3 +1.3 +1.3 +1.3 +1.3 +1.3 +1.3 +1.3
+1.28 +1.3 +1.2 +1.3 +1.2 +1.3 +1.3 +1.2 +1.2 +1.3 +1.2
+1.25 +1.3 +1.2 +1.3 +1.2 +1.3 +1.3 +1.2 +1.2 +1.3 +1.2
+1.23 +1.2 +1.2 +1.3 +1.2 +1.3 +1.3 +1.2 +1.2 +1.2 +1.2
+1.2 +1.2 +1.2 +1.2 +1.2 +1.2 +1.2 +1.2 +1.2 +1.2 +1.2
0 0000000000
-1.2 -1.2 -1.2 -1.2 -1.2 -1.2 -1.2 -1.2 -1.2 #NUM!-1.2
-1.23 -1.2 -1.2 -1.3 -1.2 -1.2 -1.2 -1.3 -1.3 #NUM!-1.3
-1.25 -1.3 -1.2 -1.3 -1.2 -1.2 -1.2 -1.3 -1.3 #NUM!-1.3
-1.28 -1.3 -1.2 -1.3 -1.2 -1.2 -1.2 -1.3 -1.3 #NUM!-1.3
-1.3 -1.3 -1.3 -1.3 -1.3 -1.3 -1.3 -1.3 -1.3 #NUM!-1.3
-1.33 -1.3 -1.3 -1.4 -1.3 -1.3 -1.3 -1.4 -1.4 #NUM!-1.4
-1.35 -1.4 -1.3 -1.4 -1.3 -1.3 -1.3 -1.4 -1.4 #NUM!-1.4
-1.38 -1.4 -1.3 -1.4 -1.3 -1.3 -1.3 -1.4 -1.4 #NUM!-1.4
-1.4 -1.4 -1.4 -1.4 -1.4 -1.4 -1.4 -1.4 -1.4 #NUM!-1.4

↓CEILINGとCEILING.PRECISEの違いがよくわからなかったので比較。どうやら基準値に負の数を指定した時の挙動が変わるらしい。PRECISE系の方が安定した挙動をしているから、たぶんより洗練されているのだろう。

formula CEILING(x,y) CEILING.PRECISE(x,y) FLOOR(x,y) FLOOR.PRECISE(x,y) MROUND(x,y) CEILING(x,y) CEILING.PRECISE(x,y) FLOOR(x,y) FLOOR.PRECISE(x,y) MROUND(x,y)
y 0.10.10.10.10.1-0.1-0.1-0.1-0.1-0.1
x +1.4 +1.4 +1.4 +1.4 +1.4 +1.4 #NUM!+1.4 #NUM!+1.4 #NUM!
+1.38 +1.4 +1.4 +1.3 +1.3 +1.4 #NUM!+1.4 #NUM!+1.3 #NUM!
+1.35 +1.4 +1.4 +1.3 +1.3 +1.4 #NUM!+1.4 #NUM!+1.3 #NUM!
+1.33 +1.4 +1.4 +1.3 +1.3 +1.3 #NUM!+1.4 #NUM!+1.3 #NUM!
+1.3 +1.3 +1.3 +1.3 +1.3 +1.3 #NUM!+1.3 #NUM!+1.3 #NUM!
+1.28 +1.3 +1.3 +1.2 +1.2 +1.3 #NUM!+1.3 #NUM!+1.2 #NUM!
+1.25 +1.3 +1.3 +1.2 +1.2 +1.3 #NUM!+1.3 #NUM!+1.2 #NUM!
+1.23 +1.3 +1.3 +1.2 +1.2 +1.2 #NUM!+1.3 #NUM!+1.2 #NUM!
+1.2 +1.2 +1.2 +1.2 +1.2 +1.2 #NUM!+1.2 #NUM!+1.2 #NUM!
0 0000000000
-1.2 -1.2 -1.2 -1.2 -1.2 #NUM!-1.2 -1.2 -1.2 -1.2 -1.2
-1.23 -1.2 -1.2 -1.3 -1.3 #NUM!-1.3 -1.2 -1.2 -1.3 -1.2
-1.25 -1.2 -1.2 -1.3 -1.3 #NUM!-1.3 -1.2 -1.2 -1.3 -1.3
-1.28 -1.2 -1.2 -1.3 -1.3 #NUM!-1.3 -1.2 -1.2 -1.3 -1.3
-1.3 -1.3 -1.3 -1.3 -1.3 #NUM!-1.3 -1.3 -1.3 -1.3 -1.3
-1.33 -1.3 -1.3 -1.4 -1.4 #NUM!-1.4 -1.3 -1.3 -1.4 -1.3
-1.35 -1.3 -1.3 -1.4 -1.4 #NUM!-1.4 -1.3 -1.3 -1.4 -1.4
-1.38 -1.3 -1.3 -1.4 -1.4 #NUM!-1.4 -1.3 -1.3 -1.4 -1.4
-1.4 -1.4 -1.4 -1.4 -1.4 #NUM!-1.4 -1.4 -1.4 -1.4 -1.4

↓ROUNDDOWNとTRUNCの比較。ROUNDDOWNとTRUNCは全く同じ。ただし、TRUNCは第2引数を省略することができる。

formula ROUNDDOWN(x,y) TRUNC(x,y) ROUNDDOWN(x,y) TRUNC(x,y)
x 3141.5926543141.592654-3141.592654-3141.592654
y +3 3141.5923141.592-3141.592-3141.592
+2 3141.593141.59-3141.59-3141.59
+1 3141.53141.5-3141.5-3141.5
0 31413141-3141-3141
-1 31403140-3140-3140
-2 31003100-3100-3100
-3 30003000-3000-3000

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

続く...

Excelの端数処理関数を整理したところで、次は実際に各種の端数処理をExcelで実装していくところなんだけど、記事が長くなってしまったので一度ストップ。続きは今度書く。

続き「MS Excelで端数処理 2: ワークシート関数で切り上げ・切り捨て・四捨五入・JIS丸め」を書きました！
スポンサーサイト

関連記事

トラックバック URL

http://liosk.blog103.fc2.com/tb.php/211-3e6bc7a9

トラックバック

MS Excelで端数処理 2: ワークシート関数で切り上げ・切り捨て・四捨五入・JIS丸め
前回の記事「MS Excelで端数処理 1: Excelの端数処理関数」から既に1ヶ月以上経ってしまった。標準的な端数処理 (切り捨て・切り上げ・四捨五入の類) は実装が終わっているので、とりあえずまとめ。 今回まと...
  • 2011-12-12
  • 発信元: LiosK-free Blog
JavaScriptでJIS丸め
前回、ExcelでJIS丸め等々を実装したので、余勢を駆ってJavaScriptでも実装したのでメモ。 ECMAScriptの仕様書によると、JavaScriptのMath.floor(), Math.ceil(), Math.round()は負の無限大(-∞)や正の無限大(...
  • 2012-01-03
  • 発信元: LiosK-free Blog

コメント

コメントの投稿

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