【Excel】近似曲線を使って計算式を元に数値を予想する方法!

【Excel】近似曲線を使って計算式を元に数値を予想する方法!

Excelでデータから近似曲線を表示させることで、今後のデータを予想します。さらに、近似曲線を使った計算式を元に数値を予想することにより、次月の売上予測などに役立てることができます。この記事では、Excelの近似曲線の数値を予想する方法をご紹介します。


目次[非表示]

  1. 近似曲線とは?
  2. 【Excel】近似曲線を使って計算式を元に数値を予想する

近似曲線とは?

近似曲線とは、Excelで月々の売上高などのデータから、グラフの散布図に曲線をつけたものです。


同時に、近似線から数式も表示させることができ、その数字を元に計算式の数値を出して次月の売上高を予想することが可能になります。2つの回帰分析がありますが、統計ではデータの関係性を分析し、機械学習ではデータを元に次の数値を予測するという特徴があります。

この記事では、後者のR-2といわれる決定係数の予測方法をご説明していきますが、こちらは実際の数値に予測がより正確に数字で表せるものです。
数字は一定の比率で増えているかどうか、さらにプラスされるのはどの位かなどを分析・予想できます。


売上予想だけではなく、月々の来客数、気温と売り上げ、製品の質量と値段など、様々なデータの関係に応用できます。データ分析・統計学で使われる近似曲線は、身近に予測のため使われます。

この記事では、Excelのデータから近似曲線を表示させ、その数式を元に数値を予想する方法をご紹介します。

【Excel】近似曲線を使って計算式を元に数値を予想する

では、先にExcelのデータを使って近似曲線を表示させてみましょう。
その後、R-2の値を出します。

近似曲線を表示させる

 データの範囲を選択
データの範囲を選択します。
「挿入」タブを押してグラフの「散布図」を選ぶ
「挿入」タブを押してグラフの「散布図」を選びます。
散布図のグラフ
散布図のグラフが表示されます。

このグラフに曲線をつけましょう。
散布図の点の上で右クリックをします。

「近似曲線の追加」を選ぶ
「近似曲線の追加」を選びます。
「グラフに数式を表示する」と「グラフにR-2乗値を表示する」にチュックを入れる
データが2つあるので、「多項式近似」が「2」となっているところにチェックを入れます。
下の、「グラフに数式を表示する」と「グラフにR-2乗値を表示する」にもチェックを入れましょう。
 近似曲線
近似曲線が表示されました。
数式も出すことができています。

R-2乗値とは

ここで、R-2乗値について少しご説明します。
その後、実際にR-2値を求めてみましょう。

 

R-2乗値(R²)は予測がどの位実際のデータと合っているか確認できる値です。
数値は0~1の範囲で、大きいほど正確度が高いと言えます。

R-2は決定係数のことです。
「決定係数=説明変数xが目的変数yをどれくらい説明できるか」という説明になります。
「目的変数y=求めたいもの」
「説明変数x=目的変数に作用するもの」
式にすると「y=ax+b」となります。

xの値でyの値が決まる(説明できる)ということです。
そのため、yが実際値に近いことをxで説明できます。

数式からR-2値を求める

セルをアクティブにし、数式を入力
値を入力したいセルをアクティブにし、数式を入力します。
xは任意の数字にしましょう。
値が変動できるようセルを決めておきます。

yの値はxの値を参照にできます。

値の予想
値を予想できました。

近似曲線のオプションから

「グラフツール」の「レイアウト」タブにある「近似曲線」を押して「その他の近似曲線オプション」をクリック
「グラフツール」の「レイアウト」タブにある、「近似曲線」を押して「その他の近似曲線オプション」をクリックします。
近似曲線の書式設定
「予測」の「前方補外」に予想したい先の数字を入力し、「グラフにR-2乗値を表示する」など必要な個所にチェックも入れます。
そして、閉じましょう。
 予測の曲線と数式
予測の曲線と数式が表示されました。

数式を入力します。
この場合、「y=342.68x²+2859.4x」に30か月後の予測で「x=30」にして計算します。
値は、「394,194」になります。

追加するとこのようなグラフになります。

値が1に近いほど実際値と正確であることを示しています。
この場合、R-2の値が「0.9985」という数字なので、かなり正確に予測できていることになります。


関連記事