【Excel】散布図に近似曲線を追加する方法!Excelの線形・指数・多項式回帰の選び方と精度確認

Excelで作成した散布図のデータ傾向を視覚的に把握したい場合、近似曲線(トレンドライン)の追加が有効です。

しかし、近似曲線の種類や精度確認の方法が分からず、どのように活用すれば良いか迷う方もいるでしょう。

この記事では、Excelの散布図に近似曲線を追加する手順と、線形・指数・多項式回帰の選び方、そして近似曲線の精度を確認する方法を解説します。

この解説を読むことで、データ分析の精度を高め、より的確な意思決定に繋げられるようになります。

散布図における近似曲線の役割

散布図に近似曲線を追加することは、データ間の関係性を視覚的に捉える上で非常に重要です。

近似曲線は、データポイントのばらつきを考慮しながら、データ全体の傾向を表す直線を引く機能です。

これにより、変数間の相関関係や、将来の値を予測するための参考情報が得られます。

近似曲線の種類と選び方

Excelで散布図に追加できる近似曲線には、主に線形、指数、対数、多項式、移動平均などがあります。

どの種類を選ぶかは、データの分布や関係性の性質によって異なります。

線形近似(Linear Trendline)

線形近似は、データが直線的な関係にある場合に最も適しています。

例えば、広告費と売上の関係が、広告費の増加に伴って売上も一定の割合で増加する場合などに有効です。

数式は y = mx + c の形で表されます。

指数近似(Exponential Trendline)

指数近似は、データが指数関数的な増加または減少を示す場合に適しています。

例えば、複利計算による資産の増加や、感染症の初期段階における感染者数の増加などが該当します。

数式は y = ab^x の形で表されます。

対数近似(Logarithmic Trendline)

対数近似は、増加または減少のペースが徐々に鈍化していくデータに適しています。

例えば、学習曲線や、ある製品の市場浸透率の推移などが考えられます。

数式は y = a ln(x) + b の形で表されます。

多項式近似(Polynomial Trendline)

多項式近似は、データが曲線的な関係を示す場合に柔軟に対応できます。

次数を指定することで、より複雑なカーブを描くことが可能です。例えば、製品のライフサイクルにおける売上推移などが該当します。

次数が2であれば y = ax^2 + bx + c、次数が3であれば y = ax^3 + bx^2 + cx + d のようになります。

移動平均(Moving Average)

移動平均は、データの短期的な変動を平滑化し、長期的なトレンドを把握するのに役立ちます。

時系列データや、ノイズの多いデータ分析でよく用いられます。

期間を指定することで、平滑化の度合いを調整できます。

近似曲線の追加手順

Excelで散布図に近似曲線を追加する手順は以下の通りです。

1. 散布図の選択
   近似曲線を追加したい散布図をクリックして選択します。
2. グラフ要素の追加
   グラフの右上にある「+」ボタン(グラフ要素)をクリックします。
3. 近似曲線の選択
   表示されたメニューから「近似曲線」にチェックを入れます。
4. 近似曲線の詳細設定
   「近似曲線」の右側にある矢印をクリックし、「その他のオプション」を選択します。
5. 近似曲線の種類選択
   「近似曲線の書式設定」ウィンドウが表示されます。ここで、「線形」「指数」「対数」「多項式」「移動平均」など、目的に合った近似曲線の種類を選択します。
6. 多項式の次数設定(多項式の場合)
   多項式近似を選択した場合は、「次数」を2以上で指定します。データの傾向に合わせて調整してください。
7. 決定係数(R^2値)の表示
   「グラフに数式を表示する」と「グラフに決定係数を表示する」にチェックを入れると、近似曲線の数式と決定係数(R^2値)がグラフ上に表示されます。
8. 書式設定の完了
   「近似曲線の書式設定」ウィンドウを閉じます。これで散布図に近似曲線が追加されました。

近似曲線の精度確認方法(決定係数R^2値)

近似曲線の精度を評価するために最も一般的に用いられるのが「決定係数(R^2値)」です。

決定係数は、0から1の間の値を取り、1に近いほど近似曲線がデータに良く当てはまっていることを示します。

Excelで近似曲線を追加する際に、「グラフに決定係数を表示する」にチェックを入れることで、この値を確認できます。

決定係数(R^2値)の解釈

決定係数(R^2値)は、従属変数(y軸の値)の変動のうち、独立変数(x軸の値)によって説明できる割合を示します。

例えば、R^2値が0.9であれば、yの変動の90%がxによって説明できると解釈できます。

一般的に、0.8以上の値があれば良好な当てはまりと見なされますが、分野やデータの性質によって許容範囲は異なります。

決定係数が低い場合の対処法

決定係数が低い(0.8未満など)場合、選択した近似曲線の種類がデータに適合していない可能性があります。

その場合は、以下の対処法を試してください。

別の近似曲線の種類を試す

現在選択している近似曲線の種類がデータに合っていない可能性があります。

線形、指数、対数、多項式(次数を上げる・下げる)など、他の種類を試して、決定係数が最も高くなるものを選びます。

散布図のデータポイントを視覚的に確認し、どのような曲線が最もデータに沿っているかを判断することも重要です。

多項式の次数を調整する

多項式近似の場合、次数が高すぎると過学習(データに過剰に適合しすぎ、一般性が失われること)を起こし、低すぎるとデータの傾向を捉えきれないことがあります。

次数を1ずつ増やしたり減らしたりして、決定係数とグラフの見た目の適合度を確認しながら最適な次数を見つけます。

一般的に、次数は低いに越したことはありません。過学習を防ぐため、必要最低限の次数を選択することが推奨されます。

外れ値を確認・処理する

データの中に極端に離れた値(外れ値)があると、近似曲線がその外れ値に引っ張られてしまい、全体の傾向を正しく表せなくなることがあります。

散布図上で外れ値がないか確認し、もし存在するようであれば、その原因を調査し、必要に応じてデータから除外するか、別の分析手法を検討します。

外れ値を除外した後に再度近似曲線を引くと、決定係数が改善されることがあります。

データの前処理を検討する

データの性質によっては、対数変換などの前処理を行うことで、より直線的な関係性が現れ、線形近似が有効になる場合があります。

例えば、値の範囲が非常に広いデータや、指数関数的な増加が見られるデータは、対数変換を検討する価値があります。

ただし、データ変換を行った場合は、その解釈に注意が必要です。

近似曲線に数式を表示する

近似曲線の数式を表示することで、データ間の関係性を数式で理解し、将来の値を計算する際に活用できます。

近似曲線の書式設定ウィンドウで「グラフに数式を表示する」にチェックを入れると、グラフ上に数式が表示されます。

この数式は、選択した近似曲線の種類によって異なります。

線形近似の数式

線形近似の数式は、一般的に「y = mx + c」の形式で表示されます。

ここで、m は傾き(xが1単位増加したときのyの変化量)、c は切片(xが0のときのyの値)を表します。

この数式を使えば、任意のxの値に対するyの予測値を計算できます。

指数近似の数式

指数近似の数式は、「y = ab^x」の形式で表示されます。

ここで、a は初期値(xが0のときのyの値)、b は成長率(xが1単位増加したときのyの増加率)を表します。

この数式は、急激な成長や減少を示すデータに適しています。

多項式近似の数式

多項式近似の数式は、次数に応じて「y = ax^2 + bx + c」や「y = ax^3 + bx^2 + cx + d」のように表示されます。

次数が高いほど複雑な曲線を描くことができ、より多くのデータポイントに適合させることが可能ですが、過学習のリスクも高まります。

数式の係数(a, b, c, dなど)は、データの傾向によって決まります。

近似曲線と予測

近似曲線の数式を用いることで、散布図のデータ範囲外の値を予測することが可能です。

例えば、過去の売上データから近似曲線を引いて、将来の売上を予測するといった応用が考えられます。

ただし、予測はあくまで過去のデータ傾向に基づいたものであり、将来の不確実な要因は考慮されていません。

予測の注意点

近似曲線による予測は、あくまで参考値として捉えるべきです。

特に、データから大きく外れた値を予測しようとする場合や、データの変動要因が複雑な場合は、予測精度が低下する可能性があります。

決定係数(R^2値)が低い場合や、データの傾向が大きく変化する可能性がある場合は、予測結果の解釈には慎重さが求められます。

ExcelのFORECAST関数との連携

Excelには、線形近似に基づいた予測を行うための「FORECAST.LINEAR」関数(旧バージョンではFORECAST関数)があります。

この関数を使うことで、近似曲線の数式を直接入力することなく、特定のx値に対するyの予測値を求めることができます。

より複雑な近似(指数、多項式など)で予測を行いたい場合は、近似曲線の書式設定で表示される数式を参考に、手動で計算するか、Excelの他の統計関数を組み合わせる必要があります。

近似曲線の書式設定

追加した近似曲線の見た目を調整することで、グラフの視認性を高めることができます。

近似曲線を右クリックし、「近似曲線の書式設定」を選択すると、線の色、太さ、スタイルなどを変更できます。

また、近似曲線の終端を延長することで、将来の傾向をより長く表示することも可能です。

近似曲線の色や太さを変更する

グラフのデザインに合わせて、近似曲線の色や太さを変更できます。

例えば、他のグラフ要素と区別するために、目立つ色を選択したり、線の太さを調整したりすることが可能です。

「近似曲線の書式設定」ウィンドウの「線と塗りつぶし」オプションで設定できます。

近似曲線の終端を延長する

「近似曲線の書式設定」ウィンドウの「近似曲線のオプション」で、「前方」または「後方」に数値を入力することで、近似曲線を指定した期間だけ延長できます。

これにより、将来のデータ傾向を視覚的に示唆することができます。

ただし、延長した期間の予測精度は、元のデータ範囲の精度よりも低くなる可能性があることに留意してください。

Power Queryとの連携によるデータ準備

複雑なデータソースから散布図を作成する場合、Power Queryを活用してデータを整形・加工することが有効です。

Power Queryを使えば、複数のファイルからのデータ結合、不要な列の削除、データ型の変換などを自動化できます。

これにより、散布図作成に必要なクリーンなデータセットを効率的に準備できます。

Power Queryの活用例

例えば、月ごとの売上データを異なるExcelファイルに持っている場合、Power Queryを使ってそれらを一つにまとめ、月ごとの合計売上を計算できます。

また、Webサイトから取得したデータを、近似曲線の分析に適した形式に変換することも可能です。

データ準備の自動化は、分析の迅速化とミスの削減に繋がります。

まとめ

Excelで散布図に近似曲線を追加することで、データ全体の傾向を視覚的に把握し、数式を用いて将来の予測を行うことが可能になります。

線形、指数、多項式などの近似曲線の種類をデータに合わせて選択し、決定係数(R^2値)で精度を確認することが重要です。

必要に応じて、近似曲線の種類や次数を調整したり、外れ値の処理を行ったりすることで、より精度の高い分析結果を得られます。

さらに、Power Queryを活用してデータ準備を効率化することで、分析プロセス全体をスムーズに進めることができるでしょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】