MS Excelで端数処理 1: Excelの端数処理関数 / LiosK-free Blog

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(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		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(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.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		ROUNDDOWN(x,y)	TRUNC(x,y)	ROUNDDOWN(x,y)	TRUNC(x,y)
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

LiosK-free Blog