みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

ExcelでPC(パソコン)によって計算結果(演算結果)が違う

Excel2003 SP2 を使用しています。
類似の質問は見つけられませんでした。

以下のような表の中で、※☆の部分は配列数式を用いているのですが、上司のPC(甲と呼びます)と私のPC(乙と呼びます)とで
  セルの演算結果
が異なります。
私自身、最小二乗法の理論的なことは漠然と知っていても、PCが演算する際にどう計算しているか、まではわからないので、演算バグかどうか・どうしたら解決するかを知りたいです。

Excelに限らずコンピューターは浮動小数点誤差が生じるものと聞いていますし、特に二乗の計算を繰り返して近似式を求めさせる場合には必ずしもパソコンは信用できないかも知れませんが、演算バグならば、甲と乙とで同じバグ(数値)になると期待するのですが…。PCによって見える計算結果が違うとなると、そのファイルは仕事で信用して使えない、とも言えるので困っています。

前置きが長くなりましたが、具体的には
↓こんな表があります。↓
行列 A   B   C
1  
2       
3          ※
4          ☆
5       
6    X  K  Y 
7    1  30  2
8    2  15  #N/A
9    3  30  6
10   4  15  #N/A 
11   5  30  10
12   6  15  #N/A
13   7  30  14
14   8  30  16
15   9  15  #N/A
16  10  15  #N/A
17  11  15  #N/A
18  12  30  24
19  13  30  26
20  14  15  #N/A
21  15  15  #N/A
22
23
↑ここまで↑ (22・23行目は、範囲指定のためのダミー行)

A列・B列は数値です。
セルC7 には
=IF($B7<30,#N/A,$A7*2)
と入れて、後は21行目までオートフィルです。
つまり、K(B列)が30以上の時だけ同じ行のY(C列)に数値が現れるようにしています。
このX(A列)とY(C列)の近似式をとると、
  Y=2X
となるはずです。つまり
  傾き   2
  Y切片  0
です。

そこで、この
傾き を SLOPE 関数で
Y切片 を INTERCEPT 関数で
求めることにしました。

※(セルC3)に =SLOPE(IF(ISNUMBER(C7:C23),C7:C23,""),$A7:$A23)
☆(セルC4)に =INTERCEPT(IF(ISNUMBER(C7:C23),C7:C23,""),$A7:$A23)
と入れて下さい。配列数式なので 数式入力の際、
  Ctrl+Shift+Enter
で決定する必要があります。
正しく配列数式として入れ終わると、数式が{ }で囲まれるはずです(やり直しが利きます)。
Excelのシートは「新規作成」でデフォルトのままなので、各セルの書式は「標準」のままです。セルの場所は上の通りでなくてもかまいません。

[第1段階]
乙で上の2式を入力すると、
※ は 2
☆ は 0
という正しい結果が得られたので、甲にメール添付で送信しました。

[第2段階]
甲でも
※ は 2
☆ は 0
と表示されましたが、上の2式のセルそれぞれを覗いてただ Enter だけで決定し直すと、
※ は #VALUE!
☆ は #VALUE!
となります(配列数式でなくなるからです)。そこでもう一度上の2式のセルそれぞれを覗いて、Ctrl+Shift+Enter で決定し直すと、
※ は  1.888
☆ は -1.104
という謎の演算結果が現れました。

[第3段階]
甲の「謎の演算結果」を含むファイルを別名で保存してメール添付で送り返してもらうと、乙でも
※ は  1.888
☆ は -1.104
と表示されることが確認できました。
しかし、乙で上の2式のセルそれぞれをCtrl+Shift+Enter で決定し直すと、
※ は 2
☆ は 0
という正しい結果が得られました。

[第4段階]
乙で戻るボタンを押して「謎の演算結果」をもう一度見ようとしましたが、戻ると
※ は 2
☆ は 0
という正しい結果になっていました。



つまり、甲から乙に送り返してきたファイルでは
1.888
など(謎の演算結果)が表示されていたのは、甲の演算能力に依存した演算結果で、
第3段階後半や第4段階で
2
など(正しい演算結果)は乙の演算能力に依存した演算結果ということでしょうか。両者のマシンスペックの差はありますが、Excelは同じ修正モジュールを適用済みのはずです。

SLOPE や INTERCEPT には触れなくてかまいませんから、
別の関数を例に挙げていただいてけっこうですから(結局、小数の足し算とかと同じですか?)、
どのPCでも同じバグ(演算結果)が出る のではなく、PCによって結果が異なってくる という問題の解決法をご教授願います。

投稿日時 - 2009-06-04 12:48:53

QNo.5015978

すぐに回答ほしいです

質問者が選んだベストアンサー

こんにちは。
しばらく調べてみました。

まず、私は、IEEE754倍精度型浮動小数点自体の誤差と、Excelの丸め誤差とは、日経のどなたかが書いている問題を100%信じてよいのか、少し疑問に思っています。これは、別のところにも書いたのですが、Excelの補正処理はある程度されていますから、ワークシート上で最近接偶数丸めを、そのままむき出しでユーザーが体験しているわけではありません。

本来、Microsoft が、不完全な補正処理に出会うのだと思っています。浮動小数点丸め誤差自体は、VBA側の問題であって、ワークシートの問題は、その処理の仕方などは違います。

それと、#5のMicrosoft サポートの内容は、英文から読み直してみましたが、今回の件とは違うような気がします。

しかし、私も、どう考えても、PCの違いで、誤差が発生するということはありえません。

Microsoft が示すように、Excel 2003 とExcel 2007 に対する、それ以下のバージョンでは、その動作が変わるということは、Microsoft サポートの#5のリンク先の通りです。

しかし、今回の表の数値自体では、Microsoft の説明にあるように、大きな数値と小さな変化に対して、下位バージョンで、エラーが発生するわけで、単純な一次関数で問題は発生しません。下位バージョンで試してみました。ただし、エラー値が入ることで、x値とy値の対比が完全ではないように思うのです。予測値をエラーの代わりに生めて上げるか、図のようなデータ自体に補正させてあげる必要があるのではないか、と考えます。

="" で間を埋めているので、完全な空ではありません。
つまり、INTERCEPT やSLOPE関数ではなく、元の数式から作る方法なら、誤差は出ないように思います。

B22  平均    =AVERAGE(B7:B2
B23  標準偏差  =STDEVP(B7:B21)

F23の式  相関係数  =F22/(B23*C23)
------ =平均値/(xの標準偏差*xの標準偏差)

投稿日時 - 2009-06-05 15:40:55

お礼

再度のご投稿、ありがとうございます!

Microsoft サポートでいう、
(Excel 2003 および)以降のバージョンの Excel
とは、Excel 2003「ではない」ものを指すのか、
Excel 2003 に修正モジュールを適用したものを指すのか、解釈を困っておりました。

なるほど、SLOPE関数を使うと傾きが一瞬で出る、という便利さに慣れ過ぎていましたが、道具がおかしいと思われる時には原点に戻って、式を立てると目が覚めるものですね!こうしたアプローチがある、というヒントをくださったことに感謝しております。難しく考えていましたが、なるほど、最小二乗法で近似式を出すということは、標準偏差の比を取るということと同じですね。このアプローチに従って、数学を勉強してみます。

投稿日時 - 2009-06-07 00:56:56

ANo.6

このQ&Aは役に立ちましたか?

3人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.5

こんばんは。

最初に関係ないけれども、以下では、そのままに入らないはずですから、NA()にしてあげないといけないはずです。

=IF($B7<30,#N/A,$A7*2)
  ↓
=IF($B7<30,NA(),A7*2)

それで、その誤差は、同じバージョンで試されたものではないはずです。
Microsoft サポートに出ています。

Excel の統計関数:INTERCEPT
http://support.microsoft.com/kb/828234/ja
(和文は、機械翻訳です)Article ID: 828234

ここに、

Excel 2003より前のバージョンでは、INTERCEPTは丸め誤差を表示する。
Excel 2003とExcelのその後のバージョンでは、INTERCEPTの動作を改善している。

Excel 2003やその後のバージョンでは、INTERCEPTのコードは、直接変更はしていないが、SLOPEが改善されたために、INTERCEPT側ものその動作は改善された。
(英文の拙訳)

と書かれています。

投稿日時 - 2009-06-04 21:16:49

補足

ご回答ありがとうございます!返事が遅れて申し訳ございません。

きちんと確かめてお礼を正式に返した方が良さそうなので、8日(月曜)以降までお待ちください。


>その誤差は、同じバージョンで試されたものではないはずです

なるほど、その可能性はありそうです。同じに見えるけれど違うEXCELを使っている、というのが一番自然な解釈ですものね。

ところで、
>そのままに入らないはずですから、
とおっしゃっていますが、NA()にしなくても、#N/A のままで、文字ではなくエラー値の #N/A として認識してくれているようですが?

投稿日時 - 2009-06-07 00:38:05

お礼

長らくアクセスできず、
お返事が遅くなってすみませんでした。

結局、マシンスペックによってはエクセルの計算結果が信用できないこともありうる、ということで納得することにしました。

ありがとうございました。

投稿日時 - 2009-07-09 19:27:49

ANo.4

こんにちは
下記の条件で、同じことを試みてみました。
変な現象は出ませんでした。

Intel Pentium(R)4、Memory 1GB
Windows XP SP3
Excel 2003 SP3(Microsoft office Pro 2003)

 同一バージョンのExcelで、しかも同じ式で結果が異なるとは解せないですね。
 Excelは、浮動小数点演算をするときは、CPUに実装されているIEEE形式をサポートしたハードウェアを使うのでしょう?
 とすれば、OS(この場合Win)がCPU内蔵の浮動小数点演算をサポートするハードウェアが返した値を正確にExcelに伝えられない状況が発生しているか、浮動小数点演算をサポートするハードウェアが壊れていて、Excelが自前のエミュレーションルーチンで計算しているためかという推測をしたくなりますが、結果の違いが「誤差」の範囲を超えているような気がして..(--;
 OSかCPUが壊れてるっぽい..とかExcelの再計算を見張っていて計算結果を狂わすユニークなウィルスを飼っているとかm(__;mすいません

投稿日時 - 2009-06-04 14:30:56

補足

ご回答ありがとうございます!返事が遅れて申し訳ございません。

きちんと確かめてお礼を正式に返した方が良さそうなので、8日(月曜)以降までお待ちください。


>結果の違いが「誤差」の範囲を超えているような気がして..

まさに同感です。上司に向かって「私の作ったファイルは正しいが、そちらのPCが壊れています」「ウィルスを飼っている可能性があります」とも言いにくいし、他人のPCを徹底的に調べるのも難しいので、
皆さんには申し訳ないのですが、今回の問題の落とし所は結局、
「他人のPCで正しく計算結果が表示されなかったファイルは、使わないでおく」
「何が原因だったのかの調査は、うやむやのまま打ち切る」
ということになりそうです。

皆様が同じ問題に興味を持ち、「では私のPCでは演算結果が正しく表示できるのだろうか」と実演までして原因をさぐってくださるご厚意には、たいへん感謝しております。

投稿日時 - 2009-06-07 00:23:29

お礼

長らくアクセスできず、
お返事が遅くなってすみませんでした。

結局、マシンスペックによってはエクセルの計算結果が信用できないこともありうる、ということで納得することにしました。

ありがとうございました。

投稿日時 - 2009-07-09 19:27:07

ANo.3

nrb

いや書くの忘れてました・・・・
演算誤差がある、ということは回答していますが、演算誤差に違いがあることに対して、回答してませんね。

確かに・・・

有り得なくは無いんですが、現状
OSがXP以後でCPUがMMX(だったかな)ならばWINでは計算事態は同じ成ります

ただし式が同じって条件です
A*B*C

C*B*A
では最終桁が変わることは有ります


判り易いように書くと 仮に少数以下は無いと判り易くします

1÷10×10=0
1÷10では少数以下は表示できません
0.1は0とされる
したがって0と計算される

1×10÷10=1
成ります

これが計算の誤差の実例です

また
EXCEL数値の有効桁数は15桁です。数値表現他、演算全て標準のIEEEですのでこれ以上の精度を求めることはできません



こんなソフトなど使うことになります
Vector:CalmCalc (Windows95/98/Me / パーソナル) - ソフトの詳細
http://www.vector.co.jp/soft/win95/personal/se147355.html


同じと思っても計算の順序で差異ができます

これは計算方法で多少は回避できます


他には
表示方法です
エクセルではセルごとに制限を掛けることができます

少数以下切捨て
少数○桁まで
とか

たぶんこれじゃないかね

投稿日時 - 2009-06-04 13:59:02

お礼

再度のご投稿は、気にかけてくださっているということで、たいへんありがたいです。

>WINでは計算事態は同じ成ります

という部分は、失礼ですが、
WINDOWSでは、計算自体は同じになります
と読み替えればよろしいですか?

乙(私のPC)と甲(上司のPC)で全く同じEXCELファイルを開いただけなので、計算自体は同じはずです。(上司も、初め配列数式ではない入力をしましたが、その後配列数式として入力し直しました。)

同じ理由によりセルの書式設定による違い
(少数以下切捨て、少数○桁まで、とか)
も今回はありませんし、お題に挙げた通り、全セルの書式はデフォルトの「標準」です。

ご意見をくださったのに否定して申し訳ないのですが、今回PCに計算させようとしている近似直線の式そのものは単純なので($A7*2 と $A7 を比較するのだから、Y=2X )、
傾きは 2 か 2.0 か、… 2.0000000 であると期待できるので、
1.999996633 ぐらいだったら「誤差」でも納得できるのですが、
1.888 という結果は不可解です。

整数のみとはっきりしていれば、INT や ROUND などを組み合わせることによって、倍精度の誤差を最小限にすることができるかも知れませんね。残念ながら今回のファイルは実験なので、本番では、小さな整数ではなく、有効数字5桁程度の小数の精度が欲しいです。

ご指摘の通り、「計算の順序」ということに大義では含まれそうですが、配列数式の演算がPCに想像以上の負荷をかけているのではないかな、と私は感じています。

ありがとうございました。

投稿日時 - 2009-06-07 00:21:28

ANo.2

機種(もしくはOS?)の違いによって、演算精度が異なる、というのは初耳で、へーっと思ってしまいました。
そういう現象に出会ったことはありませんでしたが、ありえなくないですね。(演算ユニットにバグがあるとか)

でもその前に、
Excelのオプションに、「計算方法」というタブがあるのですが、その設定に違いがあったりしませんか?

あと、実際に操作している人が異なるので、本当に同じ操作をしているのか、疑問が残ります。
言われたとおりにやってる!といっても、実は違うということはよくあります。

No.1さんは、演算誤差がある、ということは回答していますが、演算誤差に違いがあることに対して、回答してませんね。
同じ規格で計算しているのに、結果が異なるのはおかしな話です。
この誤差が事実なら、どちらかの演算ユニットに規格に沿ってないバグがあるはずです。

投稿日時 - 2009-06-04 13:18:42

補足

ご回答ありがとうございます!返事が遅れて申し訳ございません。

きちんと確かめてお礼を正式に返した方が良さそうなので、8日(月曜)以降までお待ちください。


>同じ規格で計算しているのに、結果が異なるのはおかしな話です。

私が困ったのも、正にこの部分なのです。こちらのPCで「よしできた」と思った演算式が、他人のPCで再現できないなんて、「どうすりゃいいんだ!」って感じですよね。

投稿日時 - 2009-06-06 23:57:47

お礼

長らくアクセスできず、
お返事が遅くなってすみませんでした。

結局、マシンスペックによってはエクセルの計算結果が信用できないこともありうる、ということで納得することにしました。

ありがとうございました。

投稿日時 - 2009-07-09 19:26:37

ANo.1

nrb

http://pc.nikkeibp.co.jp/pc21/special/gosa/eg4.shtml
あとはCPUとOS問題
http://support.microsoft.com/kb/402554/ja


Windows上でのごくごく一般的な環境では、floatはIEEE754単精度浮動小数点数形式(以下単精度)、 doubleはIEEE754倍精度浮動小数点数形式(以下倍精度)として実装してます
解決方法はありません
必ず起きます


少なくするには計算方法の見直しや
誤差を修正するプログラムなどご自身でプログラム組んでください

投稿日時 - 2009-06-04 13:03:05

お礼

素早いご回答、たいへん感謝しております!

やはりCPUとOSの差による影響は、Excelでも避けられないのですね…。

近似式をPCが正確に求めてくれないのは非常に困るのですが、誤差を予測・修正する方法がわからないので、あきらめます。
(マクロ上で、FOR~NEXTループ で 点と直線の距離 を求めさせ続けるのは私でもできそうですが、一関数の代替としてそこまでやるのはこちらの都合ではあまり現実的ではありません。)

アドバイスに従って、「誤差は必ずあるもの」誤差を「少なくする」方向で、見直していきたいと思います。ありがとうございました。

投稿日時 - 2009-06-04 13:33:13

あなたにオススメの質問