1. Excel の近似式は間違っている (合わない) 場合がある
2. 解決方法
3. 原因

Excel の近似式は間違っている (合わない) 場合がある

最初のこの問題に出くわしたときは、全く何が起こっているのかわからなかった。

まず、こんな感じの x と y があったとする。両者の関係性を知りたいときには、まず散布図を書くのが基本である (参考: R のプロット関数、ggplot で散布図を作成する)。

散布図は以下のようになり、上に凸の分布になる。そこで、二次関数の近似曲線 trendline を追加し、式と R² 値を表示させる。

ちなみに、これは回帰分析である。このあたりを詳しく知りたい人は、以下のページなどを参照のこと。

• 相関分析と回帰分析の違い
• 回帰分析の概要
• 重回帰分析とは

回帰式は y = -2E-05x² + 0.0038x - 0.0721 であり、R² も 0.9383 とかなり良い値が出ている。-2E-05 は 0.00002 のこと。

したがって、この式の x に 40, 60, 70... などの値を代入すれば、y の値に近い 0.04, 0.07, 0.082... のような値が出るはずである。

ところが、実際に計算してみると、y は全く違う値になってしまう。

最大値もおかしい。プロットと近似曲線からみると、x = 80 前後で y は最大の値をとるはずである。しかし、計算してみると x = 95 付近で y が最大になっており、これも非常におかしい。

これは多くの人が経験する問題のようで、「Excel 近似曲線 合わない」などで検索するとヒントが得られる。しかし、トップヒットした Microsoft のページは、以下のように支離滅裂なもの。機械翻訳か？

解決方法

y を 1,000 倍にしてやってみたらヒントがつかめた。x に対して 1000y をプロットする。もちろんグラフの形は全く同じで、y 軸の値が変わるだけであるが、数式には変化がみられ、y = -0.024x² + 3.8172x - 72.074 となる。

先ほどと同じように、この式に x の値を当てはめてみる。たとえば x = 40 のときには 1000y = 42.214 となり、これはもともとの y の値にかなり近い。最大値も x = 80 付近であり、グラフの見た目と一致している。

二次関数の最大値を求めるサイトはいろいろあるが、この WolframAlpha がお気に入りである。最大値を求める場合には maximum calculator というページを使い、関数を入力する。

Compute をクリックすると、x = 9543/112、つまり約 85.2 のときに y は 47464079/605000 ~ 78.5 ぐらいで最大となる。これはグラフと非常に一致した結果である。

つまり、解決方法は x と y の値を近くすること であった。なぜこれで解決するかは下に。

原因

Excel の近似式が合わなかった原因は、本来の x² の係数である -0.000024 が -0.00002 に四捨五入されてしまった ことにある。非常に小さい値の違いではあるが、x が 2 乗で効いてくること、y の値が x に対して非常に小さいことから、影響が大きく出てしまっている。

これを避けるためには、上で示したように回帰の際に x と y の値を近くすればいいのだが、もっと本質的には 有効数字に対する感覚を磨いておくこと だろう。

0.00002 などという数字を見たら「有効数字一桁じゃん、これはなんとかしないと不正確だな」と思えるようなセンスを身につけておきたい。

コメント欄

サーバー移転のため、コメント欄は一時閉鎖中です。サイドバーから「管理人への質問」へどうぞ。