日経PC21 / いろいろな誤差対策 - 微小値とROUND関数を使いこなす

全460ページの「エクセル(Excel)大事典」を読めば“演算誤差の謎”がよくわかる！

◆“達人”芳坂和行氏に学ぶ、エクセル(Excel)「演算誤差」対策講座

第３回　いろいろな誤差対策

―― 微小値とROUND関数を使いこなす

誤差のある計算式を見つけたら、次は、正しい計算ができるように計算方法を工夫します。一番わかりやすいのは整数で計算することですが、小数点以下の数のままで、誤差を考慮に入れながら計算する方法もあります。ポイントを押さえれば、誤差を予測し、それに影響されないように計算することも簡単にできるのです。今回は、誤差を考慮した計算方法を中心に、誤差対策について勉強して行きます。

◆「微小値」を使って誤差を解消する

エリカ: 「ハルちゃん、またメール書いてるんだ」
ハル: 「あ、先輩」
エリカ: 「この絵は… 怪獣？」
ハル: 「はい。『宇宙怪獣ゴーサ』です。頭のアンテナから電波を出して、テレビやパソコンを狂わせてしまうんです」
エリカ: 「ゴーサ？」
ハル: 「地球防衛隊のラウンドビームを浴びて小さくなってしまって、今はパソコンの中で暮らしています。それで、ときどきパソコンの計算を狂わせてしまう… というお話なんです」
エリカ: 「ラウンドビームね… 子供って、そういうのが好きなの？」
ハル: 「えっと… 私が好きなのかも。誤差はパソコンの中で小さな怪獣がいたずらしているんだって考えたら、なんだか楽しくて」
エリカ: 「ふーん。でも、誤差ってよくわからないから、確かに怪獣みたいなものかもね」
ハル: 「そうだ、誤差と言えば、ちょっとわからないことがあるんです」
エリカ: 「どうしたの？」
ハル: 「この前と同じ、顧客満足度の表なんですが、他の人が作った表の中に『=IF(E3+0.01>=0.1,"○","")』という式があるんです。この『+0.01』の意味がわからなくて…」

エリカ: 「これはね、『+0.01』が演算誤差の対策になっているの」
ハル: 「え？そうなんですか？」
エリカ: 「誤差対策には整数化の他にも、微小値を使う方法や ROUND 関数で数をそろえる方法があるんだ」

ハル: 「整数化というのは、10倍や100倍をして整数にしてから比較する方法でしたよね」
エリカ: 「うん。整数にしてから比較したり、丸めたりすれば誤差の心配は要らない。また元の桁に戻したければ、10で割ったり100で割ったりすればいいよ」

◆微小値を使う方法

ハル: 「微小値を使うというのは、どんな方法ですか？」
エリカ: 「小さな数を足したり引いたりして、誤差があっても正しい答えになるようにするの」
ハル: 「え？そんなことができるんですか？」
エリカ: 「とっても簡単だよ。例えば『E3>=0.1』という比較を『E3+0.01>=0.1』に変えるだけ。つまり、計算結果に小さな数を足して、少しだけ大きな数にするの。『0.01』を足して『0.11』にすれば、誤差があっても必ず『0.1以上』の範囲に入るよね？」

ハル: 「なるほど。確実に 0.1 より大きい数にするわけですね」
エリカ: 「そう。商売で言えば、値切られる分も見越して、上を見て値段を付けておこう、って話だね」
ハル: 「先輩は商人だったんですか…」
エリカ: 「常識だよ。他の比較方法についても同じように考えればいいよ」

ハル: 「一致の比較では ABS（アブソルート）関数を使うんですね」
エリカ: 「ABS 関数は絶対値を返す関数。つまり符号を取り除いてプラスの数にするの。差がプラスでもマイナスでも、充分に小さければ一致と見なすってこと」

◆微小値の“大きさ”を決める方法

ハル: 「足したり引いたりする『0.01』という数は、どんな計算でも同じというわけではないですよね？」
エリカ: 「うん。計算によって違うよ」
ハル: 「この数は、どうやって決めたらいいですか？」
エリカ: 「誤差よりも大きくて、数の変化の単位より小さい数にするの。誤差より小さい数では効果がないし、数の変化の単位を足したり引いたりしたら、比較した結果が違ってしまうでしょう？」

ハル: 「数の変化の単位というと… 0.1 ずつ増えたり減ったりするときは 0.1 が単位になるってことですか？」
エリカ: 「そう。この計算では、数の変化の単位は 0.1 で、誤差の大きさは最大で約 0.00000000001。だから 0.00000000001 から 0.1 の間の数なら何でもいいの。普通は数の単位の10分の1とか、100分の1とかでいいと思う」
ハル: 「数の単位が 0.1 だから、その10分の1ということで、0.01 を使っているわけですね」
エリカ: 「うん。その通り」
ハル: 「数の変化の単位はわかりますが、誤差の大きさは、どうやって調べるんですか？」
エリカ: 「エクセルでは 15 桁まで正確に計算できるから、計算で使う数の 16 桁目を誤差の最大値と考えればいいの。例えば『4.3-4.2』という計算では『4.3』と『4.2』と計算結果の『0.1』を使うよね。この中で一番大きい『4.3』の 16 桁目の大きさ、つまり 0.000000000000001 が一回の計算で発生する誤差のおよその最大値になる」

ハル: 「桁が一番大きい数の 16 桁目ですね」
エリカ: 「計算で出すなら、1 つの数に含まれる誤差の最大値は、数に『2^-53』を掛けて計算するの。例えば『4.3-4.2』は 2 つの数の計算だから、桁の大きい方の『4.3』を使って、『4.3*(2^-53)*2』で誤差の最大値を見積もりできる」
ハル: 「『2^-53』を掛けるんですね」
エリカ: 「そう。でも、計算で使う数が10桁くらいまでなら誤差は充分に小さいから、厳密に計算しなくても、数の変化の単位の10分の1か 100分の1にすれば問題ないよ」
ハル: 「そうですか。それなら簡単に大きさを決められますね」

◆微小値を使って検索する

エリカ: 「数値の範囲を検索するときも微小値が使えるよ。この前の伸び率のランクの表では、計算結果が少し小さいために一つ下のランクになってしまったけれど、計算結果に微小値を足して、少し大きくすれば正しく検索できる」

ハル: 「伸び率の単位は 0.001 だから、その10分の1の『0.0001』を足して、ランク表にある伸び率より確実に大きくするわけですね」
エリカ: 「そういうこと」
ハル: 「一致しているものを検索したいときはどうすればいいですか？」
エリカ: 「一致の検索はちょっと難しいの。数値の範囲で検索してから、見つかったものが一致しているかどうか調べるくらいかな」
ハル: 「そうですか… 工夫しないといけないんですね」

◆微小値を使って丸める

エリカ: 「丸めの処理でも微小値が使えるよ。例えば、A1 に『=4.3-4.2』、A2 に『=ROUNDDOWN(A1,1)』と入れると答えは『0』になってしまうけれど、数の変化の単位が 0.1 だとしたら、その10分の1の『0.01』を足して『=ROUNDDOWN(A1+0.01,0)』にすれば答えは『0.1』になる」

ハル: 「『4.3-4.2』の計算結果が必ず 0.1 より大きくなるように小さな数を足すってことですね」
エリカ: 「ROUND 関数で四捨五入するときも同じように微小値を足せばいい。ROUNDUP 関数で切り上げる場合は、反対に微小値を引くの。ただし、計算結果が 0 の場合は、微小値を引いてもマイナスの方向へ切り上がって 0 にはならないから注意してね。0 はちゃんと 0 になるように、IF 関数で分けて処理するといいよ。0 かどうかは、誤差があるから『ABS(A1)<0.01』のように絶対値が充分に小さいかどうかで判定できる」

ハル: 「切り上げるときは微小値を引くんですね… 元々の数がマイナスのときはどうすればいいですか？」
エリカ: 「マイナスの場合は、切り上げと四捨五入では微小値を引いて、切り捨てでは微小値を足す。つまりプラスのときの反対にするの。ただし、マイナス値の扱いは関数によっても違うから、使う関数に応じて考えること」
ハル: 「プラスとマイナスのどちらでも計算できるようにするには IF 関数で場合分けをすればいいですか？」
エリカ: 「それでもいいけど、プラスの数で計算して、最後にマイナスに戻すのが簡単だと思う。マイナスに戻すには SIGN（サイン）関数を使うといいよ。例えば『SIGN(A1)』は A1 がマイナスなら -1、ゼロなら 0、プラスなら 1 を返す。『=ROUND(ABS(A1)+0.01,1)*SIGN(A1)』のように『ABS(A1)』で絶対値にして、最後に『SIGN(A1)』を掛けてマイナスに戻すわけ」
ハル: 「わかりました。絶対値で計算して、最後にプラス・マイナスの符号を付けるんですね」

◆ROUND関数で数をそろえる方法

ハル: 「ROUND 関数で数をそろえるというのは、どんな方法ですか？」
エリカ: 「例えば『ROUND(E3,1)>=ROUND(0.1,1)』のように、両方の数を小数点以下1桁に四捨五入してから比較するの。『ROUND(0.1,1)』は『0.1』と同じ数になるから『ROUND(E3,1)>=0.1』でもいいよ」

ハル: 「なるほど。ROUND 関数で丸めればぴったり同じ数になるんですね」
エリカ: 「うーん… それは少し違うの。特別な保証がない限り、小数点以下の数は微妙に違うことがあると考えるのが基本。だから、この方法も絶対確実な方法かどうかはわからないの。マイクロソフトのサポート技術情報に載っている方法だから、たぶん大丈夫だとは思うけど」
ハル: 「たぶん？」
エリカ: 「全部の数をチェックしたわけじゃないから、『だぶん』としか言えないの。同じ丸めの関数でも、ROUNDDOWN 関数や ROUNDUP 関数では結果が違うことがあるよ」
ハル: 「え？ ROUNDDOWN 関数や ROUNDUP 関数で丸めたら、同じ数にならないんですか？」
エリカ: 「うん。例えば『0.8700601』と『0.87006』を小数点以下6桁に切り捨てたら、どちらも『0.87006』になるけど、ぴったり同じ数ではないの。『0.29』と『0.3』を小数点以下1桁に切り上げても、微妙に違う数になる」

ハル: 「丸めの関数でもぴったり同じ数になるとは限らないんですね…」
エリカ: 「ほら、学校で友達といつも一緒に勉強していたはずなのに、テストの結果が自分だけ悪かったりしたでしょう？同じことをしているように見えても、どこか違ったんだよね」
ハル: 「先輩… そんなことがあったんですか…」
エリカ: 「とにかく、ROUND 関数ではこういうケースは見つかってないから、たぶん大丈夫だと思う。それからもう一つ注意点があるんだけど、比較する数はすべて ROUND 関数で丸めておくこと。計算結果だけではなく、セルの中の定数も ROUND 関数で丸めておいた方がいいよ。見た目はまったく同じでも、入力方法によって微妙に違う数になっていることがあるからね」
ハル: 「セルの中の定数を丸めるというのは、どうすればいいですか？」
エリカ: 「例えば、A1 に小数点以下1桁の数があったら B1 に『=ROUND(A1,1)』という式を入れて、コピー、A1 に値だけを貼り付けるの。その後、B1 の式はクリアすればいい」
ハル: 「なるほど。わかりました」
エリカ: 「特に、オートフィルや連続データの作成で入力したデータは見落としやすいから注意してね。例えば、A1 に『7.1』、A2 に『7.2』と入れて、マウスのドラッグで連続データを作成すると A10 は『8.00000000000001』になってしまう」
ハル: 「オートフィルでも誤差が出るんですね。気を付けます」

◆「表示桁数で計算する」オプションで数をそろえる

エリカ: 「小数点以下の数をそろえるには、ROUND 関数の他にも『表示桁数で計算する』というオプションを使う方法もあるよ」
ハル: 「どんなオプションなんですか？」
エリカ: 「表示形式の桁数を使って自動的に四捨五入してくれるの。計算結果だけではなく、セルの中の定数も自動的に四捨五入してくれる」
ハル: 「セルの中の数が自動的に変わるんですか？」
エリカ: 「うん。普通は表示形式を変えても、セルの中のデータそのものは変わらないよね。このオプションを使うと、表示形式を変えるとそれに合わせてデータも変わるの」
ハル: 「すごいですね」
エリカ: 「だから、あらかじめセルの表示形式をしっかり設定しておいて、安易に変更しないことが大切。それから、これはブックのオプションだから、設定するとそのブックの中のシートはすべて自動的に丸められるの。そのつもりでいてね」
ハル: 「わかりました」
エリカ: 「もう一つ注意点なんだけど、時間の計算には使わない方がいいよ。時間の表示形式を設定すると、『標準』の表示形式と同じように、15 桁の数値に四捨五入されるみたいなんだ。計算ができないわけじゃないけど、通常の時間のシリアル値とは違う数になってしまって計算が難しくなる」
ハル: 「そうなんですか…」
エリカ: 「それじゃ、顧客満足度の表で試してみようか。セルに表示形式を設定しておいて、[ツール]-[オプション] [計算方法] の『表示桁数で計算する』をチェックする」

ハル: 「『データの正確さが失われます。元に戻すことはできません』って表示されました。これはどういう意味ですか？」
エリカ: 「このオプションを設定するとセルのデータが変わってしまうよっていう警告ね。ブック全体に影響があるから、もし心配なら、ここでキャンセルして、表示形式を確認してからもう一度やり直せばいい」
ハル: 「わかりました。では [OK] をクリックします… あ、ちゃんと E3 が 0.1以上に判定されました」

エリカ: 「このオプションは数式の中の計算には影響がないから、数式の中で丸めたいときは ROUND 関数を使ってね」
ハル: 「どういうことですか？」
エリカ: 「例えば、『=IF(D3-C3>=0.1,"○","")』は 0.1 以上とは判定されない。『D3-C3』の結果は四捨五入されないの。式の中で丸めたいなら ROUND 関数を使って『=IF(ROUND(D3-C3,1)>=0.1,"○","")』としないとだめ」
ハル: 「わかりました。自動的に丸めてくれるのはセルの中の数と計算結果だけなんですね」

◆ROUND 関数や ROUNDDOWN 関数は計算を間違えることがある

ハル: 「ROUND 関数や『表示形式で計算する』オプションを使って数をそろえれば、あとは整数と同じように比較や検索や丸めができるわけですね」
エリカ: 「それは違うの。比較と検索は問題ないと思うけど、小数点以下の丸めは、正しくできるとは限らないよ」
ハル: 「え？小数点以下の桁で丸めるのは、だめなんですか？」
エリカ: 「うん。とにかく、A1 セルに『9000.05085』と入れてみて」
ハル: 「『9000.05085』ですか… あれ？『9000.05084999999』になりました… どういうことですか？」

エリカ: 「これはね、セルには『9000.05085』が確かに入っているんだけど、それを正確に数式バーに表示できないっていうエクセルの不具合なんだ。小数点以下にはこういう数がたくさんあるの」
ハル: 「エクセルにはこんな不具合があったんですか…」
エリカ: 「実は、ROUND 関数にもこれと同じ不具合があって、正しく丸められないことがある。A2 に『=ROUND(A1,4)』と入れると、本当なら繰り上がって『9000.0509』になるはずなのに、『9000.0508』に切り捨てられてしまう」

ハル: 「あ！本当に切り捨てられてしまいました…」
エリカ: 「数式バーの表示と同じように『9000.05085』を『9000.05084999999』として計算してしまうみたいなんだ。当然、答えが違ってしまうの。TEXT 関数や FIXED 関数で丸めても同じ。ROUNDDOWN 関数も間違った切り捨てをすることがある。A1 に『40000.848』、A2に『=ROUNDDOWN(A1,3)』と入れると『40000.847』になってしまう」

ハル: 「『40000.848』をセルに入れると数式バーの表示は『40000.8479999999』になってしまいますね…」
エリカ: 「ROUND 関数や『表示桁数で計算する』オプションを使うだけでは、この問題には対処できないの」
ハル: 「正しく丸めるにはどうすればいいんですか？」
エリカ: 「整数化して計算するか、微小値を使えばいいよ。例えば、A1 に『9000.05085』と入れて、数の変化の単位が『0.00001』だとしたら、その10分の1の『0.000001』を足して『=ROUND(A1+0.000001,4)』とすれば『9000.0509』になる」

ハル: 「微小値を使う方法なら大丈夫なんですね」
エリカ: 「他には [編集]-[置換] や [データ]-[区切り位置] でも同じ理由で思うように処理できないことがあるから注意してね」
ハル: 「そうなんですか… 注意します。でも、小数点以下の数って本当に不思議です…」
エリカ: 「結局、誤差対策としては、整数化と微小値を使う方法が確実だと思う。計算結果を ROUND 関数で丸めるのは簡単だけど、それで安心できるわけじゃないから」

◆「シリアル値」を使った時刻計算での誤差対策

ハル: 「時間のシリアル値にも誤差があるんですよね？シリアル値も同じように誤差対策をすればいいですか？」
エリカ: 「うん。基本的には同じ。ただ、いくつか違う点もあるよ。まず、整数化についてだけど、普通の数は 10 倍とか 100 倍して整数にするけど、シリアル値の場合は違うの」
ハル: 「どういうことですか？」
エリカ: 「シリアル値では1時間は 1/24 で、2時間は 2/24。つまり 24倍すれば時間数になるの。1分は 1/(24*60) だから、分数（ふんすう）にするには 24*60 倍する。1秒は 1/(24*60*60) だから、秒数にするには 24*60*60 倍する。時間や分や秒だけの数にするなら HOUR 関数、MINUTE 関数、SECOND 関数も使えるよ」

ハル: 「どの単位の整数にするかで、使い分ければいいですね」
エリカ: 「微小値を使う方法はまったく同じ。例えば、数の変化の単位が1分だったら、その10分の1の 6 秒か、それに近い数を足したり引いたりすればいい。1秒でもいいし、1秒は約 0.000011574 だから『0.00001』を使ってもいいよ」
ハル: 「そうか… シリアル値の変化の単位は 1分とか 1秒なんですね」
エリカ: 「時間の丸めには、基準値の倍数に丸める FLOOR（フロア）関数や、 CEILING（シーリング）関数、分析ツールアドインの MROUND（エム・ラウンド）関数を使うことが多いの。例えば、分単位の時間の計算結果を 15分単位に切り捨てたいときは、1秒を足して『=FLOOR(計算結果+"0:0:1","15:00")』と計算すれば、誤差のために間違って余分に切り捨てられることはないよ」
ハル: 「『+"0:0:1"』で 1 秒を足しているんですね」

エリカ: 「切り上げたいときは CEILING 関数を使えばいい。誤差のために余分に切り上げられないように 1 秒を引いておくの。ただし、計算結果が『0:00』のときは 1 秒を引いても『0:00』にはならないから、IF 関数を使って『0:00』だけを分けて処理するといいよ」
ハル: 「ROUNDUP 関数のときと同じ要領でいいですか？」
エリカ: 「うん。『0:00』かどうかは、例えば『ABS(時間)

ハル: 「比較のときだけ VALUE 関数を使うのはどうしてですか？」
エリカ: 「VALUE 関数を使うのは文字列をシリアル値に変換するため。『"0:0:1"*1』のように 1 を掛けてもいいよ。計算の中では自動的にシリアル値に変換してくれるんだけど、比較のときは自動的にはしてくれないから自分で変換しないといけないの。気を付けてね」
ハル: 「そうなんですか。気を付けます」
エリカ: 「四捨五入には分析ツールアドインの MROUND 関数が使える。誤差のために余分に切り捨てないように微小値を足しておくの。例えば、秒単位のシリアル値を 1 分単位に四捨五入するときは微小値として 0.1 秒を足して、『MROUND(時間+"0:0:0.1","0:01")』とすればいい」
ハル: 「0.1 秒は『"0:0:0.1"』で指定できるんですね。知りませんでした」

エリカ: 「それから、『0:00』が誤差のためにマイナスになって HOUR 関数とかがエラーになってしまうことがあるけど、そういうときにも微小値が使えるよ。微小値を足して少し大きくするの。誤差ではなく本当に計算結果がマイナスになるなら、もっとちゃんと対処しないといけないけどね」
ハル: 「そうですね。せめて『0:00』までは正常に計算したいです」

ハル: 「時間のシリアル値を ROUND 関数でそろえるときは、どうやって丸めればいいですか？」
エリカ: 「時間のシリアル値をそろえるのには ROUND 関数を使うよりも『TEXT(A1,"[h]:m:s")』のように TEXT 関数で文字列にすることの方が多いかな。文字列からシリアル値に戻すなら『=VALUE(TEXT(A1,"[h]:m:s"))』とすればいい。ただし、文字列からシリアル値を作っても、セルの中のシリアル値とぴったり一致するとは限らないから注意してね」

ハル: 「数式で作ったシリアル値とセルに入れたシリアル値が違うこともあるんですね。シリアル値を全部そろえることはできないんですか？」
エリカ: 「自分でシリアル値の計算方法を統一したり、セルの定数をすべて数式に置き換えたりすれば、できなくはないけど… 面倒だから、あまりやらないと思う」
ハル: 「そうですか…」
エリカ: 「最後にもう一つだけ、とっておきの誤差対策を教えようか？」
ハル: 「え？どんな対策ですか？」
エリカ: 「誤差が出ても気にしないこと！」
ハル: 「はあ？」
エリカ: 「私達の仕事では、ちょっと端数が狂ったくらいで深刻な問題になることはないよ。もちろん、絶対に正確に計算しないといけないこともあるけど、それ以外のときは、多少の誤差はあるものだと思って気楽にやればいいと思う」
ハル: 「でも… 私はいつも、きちんとした仕事がしたいです。自分の作った資料が間違っているなんて嫌です」
エリカ: 「ハルちゃんは真面目だー！」
ハル: 「普通ですよぉ…」