MS Excelで端数処理 1: Excelの端数処理関数
- 2011-11-06
- カテゴリ: Client Side
- タグ: Tips Excel Windows
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 |
ROUND |
ROUND |
TRUNC |
CEILING |
CEILING. |
FLOOR |
FLOOR. |
MROUND |
INT |
|
---|---|---|---|---|---|---|---|---|---|---|---|
y | 1 | 1 | 1 | 1 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | 0.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 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |
-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 |
CEILING. |
FLOOR |
FLOOR. |
MROUND |
CEILING |
CEILING. |
FLOOR |
FLOOR. |
MROUND |
|
---|---|---|---|---|---|---|---|---|---|---|---|
y | 0.1 | 0.1 | 0.1 | 0.1 | 0.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 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |
-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 | ROUND |
TRUNC |
ROUND |
TRUNC |
|
---|---|---|---|---|---|
x | 3141.592654 | 3141.592654 | -3141.592654 | -3141.592654 | |
y | +3 | 3141.592 | 3141.592 | -3141.592 | -3141.592 |
+2 | 3141.59 | 3141.59 | -3141.59 | -3141.59 | |
+1 | 3141.5 | 3141.5 | -3141.5 | -3141.5 | |
0 | 3141 | 3141 | -3141 | -3141 | |
-1 | 3140 | 3140 | -3140 | -3140 | |
-2 | 3100 | 3100 | -3100 | -3100 | |
-3 | 3000 | 3000 | -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