【Excel】エクセルの近似曲線・近似直線をグラフや散布図に追加する方法（最小二乗法・原点を通る・複数系列・表示されない）

Excelの散布図に近似直線や近似曲線を追加すると、数値のばらつきから傾向を読み取りやすくなります。

売上と広告費、温度と消費電力、測定値と時間など、二つの数値の関係を確認する場面では、グラフに線を引くだけではなく、どの近似方法を選ぶかが重要です。

近似曲線は最小二乗法を基本として計算されるため、データ全体との誤差が小さくなるように直線や曲線が自動作成されます。

この記事では、Excelのグラフや散布図に近似直線を追加する基本操作から、原点を通る設定、複数系列への追加、近似曲線が表示されない場合の確認方法まで解説します。

 

エクセルで近似曲線を追加する方法

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

A列	B列
1	広告費	売上
2	10	120
3	20	185
4	30	245

 

散布図の作成手順

近似曲線を使う場合は、最初に散布図を作成することが基本です。

横軸と縦軸の関係を扱う近似では、項目が等間隔に並ぶ折れ線グラフよりも、数値を座標として扱う散布図が適しています。

サンプルデータでは、1行目を見出しとしてA1からB4を選択します。

続いて、挿入タブから散布図を選び、マーカーのみが表示される散布図をクリックしましょう。

横軸には広告費、縦軸には売上が配置され、各行の数値が一つずつ点として表示されます。

 

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

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

このボタンをクリックし、近似曲線にチェックを入れると、既定では線形の近似直線がグラフ上に追加されます。

線形は、データの増減がほぼ一定の割合で進む場合に向く方式です。

近似線を選択して右クリックし、近似曲線の書式設定を開く方法でも同じ操作ができます。

グラフに追加された線はデータ点をすべて通る線ではなく、データ全体とのずれが小さくなる位置に引かれた線です。

そのため、一部の点が線から離れていても、直ちに計算ミスとは限りません。

 

系列を選択して追加する操作

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

選択した点だけにハンドルが表示された状態で、グラフ要素から近似曲線を追加してください。

近似曲線はグラフ全体ではなく系列ごとに設定する要素です。

意図しないデータの近似線が追加されたときは、近似曲線をクリックしてDeleteキーを押し、正しい系列を選び直します。

 

最小二乗法と近似式の表示

続いては、近似直線がどのように計算されるか、数式と決定係数の確認方法を解説していきます。

広告費 x	売上 y	予測値
10	120	123
20	185	183
30	245	243

 

最小二乗法による直線の考え方

Excelの線形近似は、一般に最小二乗法という考え方で求められます。

各データ点の実際のyの値と、式で求めた予測値との差を残差と呼びます。

最小二乗法では、残差を二乗した値の合計が最も小さくなるように、傾きaと切片bを決定します。

誤差をそのまま足すと正負が相殺されるため、誤差を二乗して評価することが特徴です。

この処理により、全データを通して最も説明しやすい一本の直線が得られます。

 

グラフに数式を表示する設定

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

表示された作業ウィンドウの下部にある、グラフに数式を表示する項目へチェックを入れましょう。

グラフ上にy = 6x + 63のような式が表示されれば、横軸の数値から縦軸の予測値を計算できます。

たとえばy = 6x + 63で、広告費xが25なら、予測売上は6×25+63で213と求められます。

ただし、グラフに表示される係数は丸められていることがあります。

精密な予測計算を行う場合は、SLOPE関数やINTERCEPT関数を利用する方法も検討しましょう。

 

決定係数 R二乗値の確認

同じ書式設定画面で、グラフにR二乗値を表示する項目にもチェックを入れられます。

R二乗値は、近似式がデータのばらつきをどの程度説明できているかを見る指標です。

R二乗値が1に近いほど、データ点は近似式に沿っていると判断しやすくなります。

ただし、R二乗値だけで因果関係まで断定することはできません。

データ数が少ない場合や、偶然似た動きをしている場合もあるため、業務上の背景や外れ値の有無も確認してください。

 

原点を通る近似直線の設定

続いては、近似直線を原点で固定したい場合の設定と注意点を確認していきます。

使用量 x	料金 y
0	0
10	150
20	300

 

切片をゼロに固定する手順

原点を通る近似直線とは、xが0のときにyも0になる直線です。

近似曲線を右クリックして書式設定を開き、切片を設定する項目に0を入力します。

これにより、通常はy = ax + bで表される式が、y = axの形に固定されます。

設定後は近似線の開始位置が原点に移動し、グラフに表示される数式から定数項がなくなります。

 

原点固定が適するデータの条件

原点を通す設定は、数値上の見た目だけで決めないことが重要です。

たとえば単価が一定で、使用量が0なら料金も0となるモデルでは、原点通過の考え方が適しています。

一方で、基本料金、初期費用、固定費のように、xが0でもyが0にならない要素がある場合は注意が必要です。

実態に合わない原点固定は、予測値を大きくゆがめる可能性があります。

通常の近似式と原点を通る近似式を両方表示し、差がどの程度あるかを比較するのも有効です。

 

関数で原点通過の傾きを求める方法

グラフ以外で原点通過の傾きを計算するなら、SUMPRODUCT関数とSUMSQ関数を組み合わせられます。

SUMPRODUCTは各行のxとyを掛けて合計し、SUMSQはxの二乗を合計します。

得られた値が、y = axにおける傾きaです。

なお、ExcelのSLOPE関数は切片を自由に持つ通常の回帰直線を求める関数なので、原点固定の計算とは条件が異なります。

 

複数系列への近似曲線の追加

続いては、複数のデータ系列がある散布図へ、それぞれの近似曲線を追加する操作を確認していきます。

月	商品A 売上	商品B 売上
1	120	90
2	150	130
3	190	155

 

データ系列ごとの選択

複数系列の散布図では、最初にどのデータ系列へ近似曲線を付けるかを明確にします。

グラフ上の商品Aの点をクリックすると、商品Aだけが選択されます。

その状態でグラフ要素の近似曲線を追加すれば、商品A専用の線が表示されます。

続けて商品Bの点をクリックし、同じ手順を実行すると商品Bにも別の近似曲線を追加できます。

複数系列では、線の色とデータ点の色をそろえると、読者が系列を見分けやすくなります。

 

Excelの散布図を模した操作画面

この画面では、緑色の点で示された商品Aの系列が選択されています。

赤枠で示したグラフ要素ボタンから近似曲線を選ぶと、選択中の系列だけに直線を追加できます。

 

近似曲線の種類と使い分け

近似曲線の書式設定では、線形以外にも指数、対数、多項式、べき乗、移動平均を選べます。

データが一定量ずつ増減するなら線形、増加率が大きく変化するなら指数、山や谷があるなら多項式を検討します。

多項式の次数を高くしすぎると、個別のばらつきに過剰反応する線になりやすいため注意が必要です。

移動平均は将来予測の式を作るものではなく、短期的な変動をならして推移を見るための方法です。

 

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

続いては、近似曲線の項目が選べない、またはグラフに表示されない場合の原因を確認していきます。

確認項目	確認内容
グラフ種類	散布図または対応するグラフか
データ数	複数の数値が含まれているか
系列選択	対象の系列を選んでいるか

 

グラフの種類による制限

近似曲線はすべてのグラフ種類で使えるわけではありません。

円グラフ、ドーナツグラフ、レーダーチャート、積み上げグラフなどでは追加できない場合があります。

数値の関係を分析したいなら、散布図へ変更してから近似曲線を設定する方法が確実です。

グラフを選択し、グラフのデザインタブからグラフの種類の変更を選ぶと、散布図へ切り替えられます。

 

数値データと空白セルの確認

横軸または縦軸の値が文字列として保存されていると、期待どおりに近似計算できないことがあります。

セル左上の緑色の三角や、左寄せで表示された数値がないかを確認してください。

VALUE関数を使う、区切り位置で変換する、エラー表示から数値に変換するなどの方法で数値化できます。

また、空白セルやエラー値が多い場合も、近似曲線の作成結果が不安定になるかもしれません。

 

近似式が読めない場合の調整

近似曲線は表示されても、数式やR二乗値がデータ点や凡例に重なって読めない場合があります。

数式のテキストボックスをクリックして、グラフ内の空いている位置へドラッグしてください。

近似曲線の色、太さ、破線の種類も書式設定で変更できます。

元データの線と近似線の見分けやすさを優先すると、資料としての読みやすさが向上します。

 

近似直線による予測値と分析

続いては、作成した近似直線を予測や業務分析へ活用する考え方を確認していきます。

広告費	近似式による予測売上
15	153
25	213
35	273

 

FORECAST.LINEAR関数による予測

グラフの近似式を目視で使う代わりに、FORECAST.LINEAR関数で予測値を求めることもできます。

この関数は、既知のxと既知のyから線形回帰を行い、D2のxに対応する予測yを返します。

1行目はヘッダーであるため、参照範囲に見出しを含めないことが重要です。

グラフ上の近似式とセルの予測計算を併用すると、説明用の資料と実務用の計算表をつなげられます。

 

外れ値を含むデータの見方

一部のデータ点が近似線から大きく離れている場合は、外れ値の可能性があります。

入力ミス、特別なキャンペーン、機器不良、季節要因など、通常と異なる事情がないかを確認しましょう。

ただし、外れ値を見つけたからといって、根拠なく削除するのは適切ではありません。

外れ値を含む結果と除外した結果を比較し、その違いを説明できるようにする姿勢が大切です。

 

近似結果を資料へ伝える工夫

近似曲線を会議資料へ使う場合は、式だけを載せるより、何を示すグラフかを明記すると伝わりやすくなります。

横軸と縦軸の単位、対象期間、データ数、分析目的をグラフタイトルや注記で補足してください。

R二乗値が低い場合は、単純な直線だけでは説明しにくいデータであることを示す材料になります。

近似線を結論そのものとして扱うのではなく、追加調査や意思決定のための判断材料として使いましょう。

 

まとめ エクセルの近似曲線・近似直線をグラフや散布図に追加する方法

Excelで近似曲線や近似直線を追加するには、数値の関係を表せる散布図を作成し、対象のデータ系列を選択してグラフ要素から近似曲線を設定します。

基本となる線形近似は最小二乗法により求められ、グラフに数式とR二乗値を表示すれば、傾向を視覚面と数値面の両方から確認できます。

原点を通る設定は、xが0ならyも0になるという前提が成り立つ場合に有効です。

複数系列では、各系列を選択して個別に近似曲線を追加し、色や線種を調整すると見やすいグラフになります。

近似曲線が表示されない場合は、グラフの種類、セルが数値になっているか、対象系列が選択されているかを確認しましょう。