【Excel】エクセルで近似曲線を追加する方法（数式・R2乗値・傾き）

Excelで散布図を作成したものの、データの傾向をどのように読み取ればよいのか迷う場面は少なくありません。

近似曲線を追加すると、売上と広告費、温度と消費電力、時間と生産量のような二つの数値の関係を視覚的に把握しやすくなります。

さらに数式とR2乗値をグラフ上に表示すれば、傾きの意味や予測値の考え方まで確認できます。

この記事では、Excelで近似曲線を追加する操作から、数式、R2乗値、傾きの読み方まで順番に解説していきます。

 

エクセルで近似曲線を追加する方法【散布図からの設定】

それではまず、散布図に近似曲線を追加する基本操作について解説していきます。

A	B
1	広告費	売上
2	10	120
3	20	155
4	30	188
5	40	226

 

散布図の作成手順

近似曲線を正しく使うには、横軸と縦軸の数値を対応させた散布図を作成します。

サンプルではA列の広告費を説明変数、B列の売上を目的変数として扱います。

A1からB5までを選択し、挿入タブの散布図から、マーカーのみが表示される散布図を選びましょう。

折れ線グラフでも点の変化は見られますが、横軸が数値として均等ではない場合に解釈を誤りやすくなります。

数値同士の関係を分析する近似曲線では、原則として散布図を選ぶことが重要です。

グラフが表示されたら、横軸が広告費、縦軸が売上になっているかを確認します。

意図した軸と逆になったときは、グラフを右クリックしてデータの選択を開き、系列のXの値とYの値を修正できます。

 

グラフ要素からの近似曲線追加

続いては、グラフ要素から近似曲線を追加する方法を確認していきます。

散布図をクリックすると、グラフ右上にプラス記号のグラフ要素ボタンが表示されます。

このプラス記号をクリックし、近似曲線にチェックを入れると、既定では線形の近似曲線が追加されます。

近似曲線の右側にある矢印を選ぶと、線形以外の候補も確認できます。

より細かく設定したい場合は、グラフ内のデータ系列を右クリックして、近似曲線の追加を選択する方法が便利です。

表示された近似曲線の書式設定では、種類、予測、切片、数式表示などを一画面で調整できます。

最初は線形近似を表示し、点の並び方を見てから別の種類を比較すると判断しやすくなります。

 

近似曲線の種類と選び方

続いては、データに合う近似曲線の種類を確認していきます。

点がおおむね一直線に並ぶ場合は線形近似が向いています。

売上が広告費にほぼ比例して増えるようなデータでは、線形近似の数式が説明しやすいでしょう。

増え方が途中から加速する場合は指数近似、ゆるやかに変化する曲線なら対数近似や多項式近似が候補になります。

周期的な動きを表す移動平均は、日別売上やアクセス数の短期的な変動をならす用途に適しています。

【操作のポイント】

 

近似曲線の数式表示と予測値の確認

続いては、グラフに近似式を表示して予測へ利用する方法を確認していきます。

広告費	売上	予測売上
10	120	約120
20	155	約155
30	188	約190

 

グラフ上への数式表示

続いては、近似曲線の数式をグラフ上に表示する操作を確認していきます。

近似曲線をクリックして右クリックし、近似曲線の書式設定を選択します。

画面右側の設定項目を下へスクロールすると、グラフに数式を表示するというチェック項目があります。

ここへチェックを入れると、y = ax + b の形に近い数式がグラフ内に表示されます。

数式ボックスはドラッグで移動できるため、点や線と重ならない位置へ整えましょう。

表示桁数が少なく、係数が丸められているように見える場合は、数式ボックスを右クリックして表示形式を調整します。

グラフ上の式は見やすさを優先した表示であり、厳密な計算にはセル関数を併用すると安心です。

 

線形近似式の読み方

続いては、線形近似式に含まれる数値の意味を確認していきます。

線形近似式は、一般に y = ax + b と表されます。

たとえば y = 3.5x + 85 と表示された場合、広告費が1単位増えると売上は平均で約3.5単位増える傾向を表します。

広告費が30なら、3.5に30を掛けて85を加えるため、予測値は190になります。

実際の値が予測値と異なることは自然であり、その差には季節要因、担当者の違い、商品の構成などが影響するかもしれません。

 

予測と予測区間の使い分け

続いては、近似曲線の予測機能の使い方を確認していきます。

近似曲線の書式設定にある予測では、前方補外と後方補外の値を指定できます。

前方補外に10を入力すると、現在の横軸の最大値から先へ10単位分だけ近似曲線を延長できます。

将来の傾向を視覚的に検討する場面では役立ちますが、元データの範囲外では誤差が大きくなる点に注意が必要です。

予測値は確定値ではなく、過去データに基づく推定値として扱いましょう。

【操作のポイント】

 

R2乗値の表示と相関の判断

続いては、R2乗値をグラフに表示して近似の当てはまりを確認していきます。

R2乗値の例	読み取り方
0.95	近似曲線への当てはまりが比較的高い状態
0.60	傾向はあるものの、ばらつきも確認が必要な状態
0.10	直線的な関係は弱い可能性がある状態

 

R2乗値の表示手順

続いては、R2乗値をグラフ上へ表示する操作を確認していきます。

近似曲線を右クリックし、近似曲線の書式設定を開きます。

グラフにR-2乗値を表示するという項目にチェックを入れると、数式の近くにR2乗値が表示されます。

Excelの画面ではR²と見えることもあり、これは決定係数と呼ばれる指標です。

数式とR2乗値を同時に表示すると、傾きと当てはまりを一つのグラフで確認できます。

R2乗値は近似曲線の説明力を判断する目安であり、数値が1に近いほどデータ点とのずれが小さい傾向を示します。

 

R2乗値の目安と注意点

続いては、R2乗値を評価するときの目安を確認していきます。

R2乗値が0なら、選んだ近似曲線では縦軸の変動をほとんど説明できていない状態です。

R2乗値が1に近づくほど、近似曲線はデータ点の配置をよく表していると考えられます。

ただし、0.8以上なら必ず良い、0.5未満なら必ず使えないという一律の基準はありません。

品質管理、営業分析、自然科学の測定など、分野ごとに許容される誤差や必要な精度が違うためです。

R2乗値だけでは因果関係を証明できない点も重要です。

広告費と売上に高い関係が見られても、キャンペーン時期や需要の増加という別の要因が両方へ影響している可能性があります。

 

外れ値を含むデータの確認

続いては、外れ値がR2乗値へ与える影響を確認していきます。

散布図にほかの点から大きく離れた点がある場合、その値が近似曲線の傾きやR2乗値を大きく変えることがあります。

入力ミスではないか、特別なイベントがあった日ではないか、測定条件が同じかを確認しましょう。

外れ値を消す前には、元データをコピーして比較することが大切です。

【操作のポイント】

 

傾きの計算とSLOPE関数の活用

続いては、近似曲線の傾きをセルで計算し、実務で使いやすくする方法を確認していきます。

A	B	C
1	広告費	売上	計算結果
2	10	120	=SLOPE(B2:B5,A2:A5)
3	20	155	傾き

 

SLOPE関数による傾きの算出

続いては、SLOPE関数で傾きを求める方法を確認していきます。

SLOPE関数は、既知のyの範囲と既知のxの範囲から、線形回帰式の傾きを返す関数です。

結果として表示された数値は、グラフの線形近似式にあるxの係数とおおむね一致します。

データを追加したときに範囲を広げる必要があるため、Excelテーブル化して構造化参照を使う方法も有効です。

SLOPE関数では、yの範囲を先、xの範囲を後に指定する順序が重要です。

 

INTERCEPT関数とLINEST関数の違い

続いては、切片や回帰分析に使う関数を確認していきます。

INTERCEPT関数は、近似式 y = ax + b におけるb、つまり切片を求める関数です。

LINEST関数は、傾きと切片に加え、統計情報をまとめて求めたい場合に役立ちます。

複数の説明変数を使う分析や、回帰分析の詳細を確認したい場面ではLINEST関数が候補になります。

グラフで傾向を伝えるなら近似曲線、セルで再計算や報告書を作るならSLOPE関数やINTERCEPT関数が便利です。

 

数式の入力とオートフィル

続いては、予測値を数式で一覧化する方法を確認していきます。

傾きをF2、切片をF3へ求めた場合、C2には =A2*$F$2+$F$3 と入力します。

A2は各行の広告費を参照し、F2とF3は固定の係数を参照するため、絶対参照を使います。

C2の右下にあるフィルハンドルを下へドラッグすると、C3以降にも予測式をコピーできます。

サンプルデータは1行目をヘッダーにし、数式は2行目から入力すると管理しやすくなります。

実測値のB列と予測値のC列を並べれば、予測とのずれも確認できます。

【操作のポイント】

 

近似曲線が表示されない場合の確認項目

続いては、近似曲線を追加できない、または期待通りに表示されない場合の確認項目を解説していきます。

確認項目	対処の方向性
グラフの種類	散布図へ変更して確認します。
空白セルや文字列	数値形式とデータ範囲を確認します。
系列の選択	対象のデータ系列をクリックしてから操作します。

 

グラフ種類とデータ系列の確認

続いては、グラフ種類とデータ系列を確認していきます。

近似曲線は多くのグラフへ追加できますが、数値の関係を扱う場合は散布図が最も分かりやすい形式です。

円グラフや積み上げグラフでは、近似曲線を追加できない、または意図した分析にならないことがあります。

グラフ内に複数系列がある場合は、近似曲線を付けたい系列の点を一度クリックして選択します。

グラフ全体ではなく、分析対象の系列を選択してから右クリックすることが操作のコツです。

系列ごとに異なる近似曲線を設定できるため、商品別や地域別の傾向を比較することもできます。

 

空白セルと文字列データの確認

続いては、元データの入力状態を確認していきます。

数値に見えても、先頭にアポストロフィが付いた文字列や、単位を含んだ文字列になっていると計算対象として扱われない場合があります。

セルを選択して表示形式を確認し、数値として認識されているかを確かめましょう。

空白セルが途中にあると、グラフの系列や関数の参照範囲が想定どおりにならない場合があります。

 

近似曲線の精度を上げる工夫

続いては、分析の信頼性を高める工夫を確認していきます。

データ件数が少なすぎると、一見きれいな直線に見えても偶然の可能性があります。

異なる時期や条件のデータを増やし、同じ基準で測定された値を使うことが大切です。

桁が大きく異なるデータでは、軸の表示単位や対数目盛を検討する場面もあります。

近似曲線の精度はExcelの操作だけで決まらず、元データの品質によって大きく左右されます。

【操作のポイント】

 

まとめ エクセルの近似曲線追加方法（傾き・R2乗値・数式）

Excelで近似曲線を追加するには、まず横軸と縦軸の関係が分かる散布図を作成し、グラフ要素またはデータ系列の右クリックから近似曲線を設定します。

線形近似では、表示された y = ax + b のaが傾き、bが切片です。

傾きはxが1増えたときのyの平均的な増減を示すため、売上予測や変化量の説明に活用できます。

R2乗値は近似曲線の当てはまりを見る目安であり、1に近いほどデータとのずれが小さい傾向を示します。

ただし、R2乗値が高いだけでは因果関係を判断できないため、データの背景や外れ値も確認しましょう。

SLOPE関数を使えば傾きをセルで計算でき、INTERCEPT関数と組み合わせることで予測値の表も作成できます。

散布図、近似式、R2乗値、元データの品質を一緒に確認し、Excelでの分析を実務に役立てていきましょう。