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によって結果が異なってくる という問題の解決法をご教授願います。
お礼
早速ありがとうございます。 質問内容も、参考URLも仰せのとおりです。 相対参照形式指定のことが5ページにありました。おかげさまで思った通りにものができそうです。 ありがとうございました。