【Excel】エクセルの散布図に近似曲線を追加する方法（回帰直線・数式・R2）

Excelの散布図は、売上と広告費、気温と来客数、身長と体重のように、2つの数値の関係を確認したいときに便利なグラフです。

ただし、点が並んでいるだけでは傾向をつかみにくく、どの程度の関係があるのかを説明しにくい場面もあります。

そこで役立つ機能が近似曲線です。

散布図に回帰直線や曲線を追加し、数式とR2値を表示すれば、データの傾向を視覚と数値の両面から確認できます。

この記事では、Excelで散布図へ近似曲線を追加する基本操作から、回帰直線の数式、R2値の読み方、予測に活用するときの注意点まで解説していきます。

散布図への近似曲線の追加方法【グラフ要素から回帰直線を表示】

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

散布図の作成手順

近似曲線を追加する前に、横軸にする数値と縦軸にする数値を正しく並べた散布図を作成します。

サンプルデータでは、A1セルに広告費、B1セルに売上と入力し、A2からA5とB2からB5に数値が入っている状態を想定します。

1行目はヘッダーとして扱い、2行目以降を実際の分析対象にすることが基本です。

A1からB5を選択したら、Excel上部の挿入タブを開き、グラフグループにある散布図またはバブルチャートの挿入を選択します。

最初は、マーカーのみで表示される散布図を選ぶと、データ点と近似曲線の関係を見やすくできます。

折れ線グラフではなく散布図を選ぶ理由は、散布図が横軸と縦軸の両方を連続した数値として扱うためです。

たとえば広告費が10、20、30と増える間隔と、10、30、40のように不規則な間隔では、散布図のほうが実際の数値差を正確に反映できます。

作成後に横軸へ売上、縦軸へ広告費が入ってしまった場合は、グラフを右クリックしてデータの選択を開き、系列のXの値とYの値を確認しましょう。

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

散布図を作成できたら、グラフ内のデータ点を一度クリックして系列全体を選択します。

続いて、グラフ右上に表示されるプラス記号のグラフ要素をクリックします。

一覧にある近似曲線へチェックを入れると、標準設定の線形近似曲線が散布図に追加されます。

点の中心を通る線を無理に引く機能ではなく、全体のばらつきに最も合うよう計算された傾向線が近似曲線です。

グラフ要素の近似曲線の右側にある矢印を選ぶと、線形、指数、線形予測、移動平均などの候補を確認できます。

まずは線形を表示し、点の並びがほぼ右上がりまたは右下がりの直線状に見えるかを観察するとよいでしょう。

近似曲線が追加されない場合は、グラフの種類が散布図ではない、または複数の系列のうち別の系列を選んでいる可能性があります。

複数系列の散布図では、系列ごとに近似曲線を設定できるため、対象となる点を選んでから操作することが大切です。

右クリックメニューによる書式設定

データ点を右クリックして近似曲線の追加を選ぶ方法でも、同じように回帰直線を表示できます。

この方法では、追加と同時に画面右側へ近似曲線の書式設定ペインが開くため、種類や表示内容を続けて調整したいときに向いています。

ペインでは、近似または回帰の種類から線形を選び、必要に応じて指数、対数、多項式、累乗、移動平均へ変更します。

線の色や太さ、破線の種類は塗りつぶしと線の項目から変更できます。

近似曲線はデータを説明する補助線なので、元のマーカーより目立ちすぎない色と太さに整えると読みやすいグラフになります。

会議資料では系列と同じ色の実線、予測値を含む補助線ではグレーの破線とするなど、目的に合わせた表現も有効です。

一度追加した近似曲線を削除したい場合は、線をクリックしてDeleteキーを押すか、グラフ要素のチェックを外します。

回帰直線の数式表示と係数の読み方【y=ax+bの確認】

続いては、近似曲線に数式を表示し、回帰直線の意味を確認していきます。

グラフ上への数式表示

近似曲線を選択し、近似曲線の書式設定ペインを開きます。

ペインの下部にあるグラフに数式を表示するへチェックを入れると、yとxを使った式がグラフ内に表示されます。

線形近似では、一般にy=ax+bという形の数式になります。

たとえばy=3.4x+86.2と表示された場合、広告費が1単位増えると、売上は平均して約3.4単位増える傾向と読み取れます。

数式の表示位置は、式をクリックしてドラッグすると移動できます。

データ点や凡例と重なると読みにくくなるため、グラフ内の空白部分へ配置しましょう。

桁数が少なすぎて係数が0と表示される場合は、数式を右クリックして近似曲線ラベルの書式設定を開き、表示形式の小数点以下の桁数を増やします。

Excel関数による傾きと切片の算出

グラフに表示された式だけでなく、セル内の関数でも回帰直線の傾きと切片を求められます。

サンプルデータで広告費がA2からA5、売上がB2からB5に入力されているとします。

=SLOPE(B2:B5,A2:A5)

=INTERCEPT(B2:B5,A2:A5)

SLOPE関数では、最初の引数に既知のyの範囲であるB2:B5を指定し、2番目の引数に既知のxの範囲であるA2:A5を指定します。

SLOPE関数の引数の順序は、縦軸の範囲を先、横軸の範囲を後にする点が重要です。

INTERCEPT関数も同じ範囲指定で使えます。

たとえばD2セルに傾き、D3セルに切片を算出し、E2セルに任意の広告費を入力すれば、F2セルで予測売上を計算できます。

=$D$2*E2+$D$3

数式を予測へ使う場合の範囲

回帰直線の式は、入力済みデータの範囲内で傾向を確認する用途に特に向いています。

広告費10から40の実績だけを使って作成した式に対し、広告費500の売上を求めても、信頼できる予測になるとは限りません。

このように観測した範囲を大きく超えて予測することを外挿といいます。

外挿は便利ですが、実際には広告費を増やしても売上の伸びが鈍るなど、元の直線関係が続かない可能性があります。

一方、実績範囲の間を補う予測は内挿と呼ばれ、比較的扱いやすい考え方です。

近似曲線を使うときは、数式が正しく計算されていることと、業務上の前提が妥当であることを分けて確認しましょう。

季節、店舗数、価格改定、キャンペーンなど、売上へ影響する別要因がある場合には、単純な2変数の回帰直線だけでは説明しきれないこともあります。

R2値の表示と決定係数の判断【当てはまりの確認】

続いては、近似曲線がデータへどの程度当てはまっているかを示すR2値について確認していきます。

R2値をグラフに表示する操作

R2値は決定係数とも呼ばれ、近似曲線が実際のデータのばらつきをどれほど説明できているかを表す指標です。

近似曲線の書式設定ペインで、グラフにR-2乗値を表示するへチェックを入れると、数式の近くにR2の値が表示されます。

R2値は0から1の範囲で表示され、1に近いほど近似曲線がデータに当てはまっていると考えられます。

R2が0.99であれば、売上の変動の多くを広告費との線形関係で説明できる可能性があります。

ただし、R2が高いことだけで因果関係が証明されるわけではありません。

R2値の目安と散布図の見比べ方

R2値に絶対的な合格ラインはなく、業種、データ数、分析目的によって適切な判断は変わります。

管理された実験データでは高いR2値を求めることがありますが、人の行動や市場を扱うデータでは、0.5程度でも意味のある傾向が見つかる場合があります。

数値だけを見るのではなく、実際の散布図で点がどのように散らばっているかを必ず確認しましょう。

たとえば一部だけ極端に離れた点がある場合、R2値が低くなっている原因は外れ値かもしれません。

逆に、少ないデータ点がきれいに一直線へ並んでいても、件数が少なすぎれば将来の再現性を判断しにくいことがあります。

データ数、外れ値、業務上の背景を合わせて確認する姿勢が重要です。

CORREL関数とRSQ関数による確認

グラフ上の表示に加えて、ワークシートの関数で関連する数値を確認することもできます。

RSQ関数は、指定したyの範囲とxの範囲から決定係数を直接求める関数です。

=RSQ(B2:B5,A2:A5)

CORREL関数では相関係数を求められます。

線形の単回帰では、相関係数を2乗した値がR2値に対応します。

=CORREL(A2:A5,B2:B5)

CORREL関数は関係の向きも示し、正の値なら一方が増えると他方も増える傾向、負の値なら反対に動く傾向を表します。

一方でRSQ関数は0以上の値となるため、増加関係か減少関係かは傾きや相関係数と併せて確認します。

近似曲線の種類とデータ形状【線形・多項式・指数の選択】

続いては、散布図の形に合わせた近似曲線の種類について確認していきます。

線形近似曲線の特徴

線形近似曲線は、データの増減を一本の直線として表す最も基本的な種類です。

広告費と売上、作業時間と処理件数、距離と燃料費のように、一定の割合で増減する傾向を確認したいときに使われます。

散布図の点が全体として右上がりまたは右下がりに並ぶなら、最初に線形近似を試すのが自然です。

線形近似は式が読みやすく、SLOPE関数やINTERCEPT関数ともつながるため、Excelでの予測表にも利用しやすい特徴があります。

ただし、実際の点が明確な弧を描いている場合、直線は平均的な傾向しか示せません。

グラフを見て両端でずれが大きいと感じたら、別の種類も比較しましょう。

多項式近似曲線の次数

多項式近似曲線は、直線では表せない曲がりを持つデータに適しています。

近似曲線の書式設定で多項式を選び、次数を2、3、4などに指定します。

次数2は放物線に近い形で、最初は増加し途中から減少する、または増加の勢いが変わるデータを表現しやすい選択です。

次数を高くすると曲線はデータ点へ合わせやすくなりますが、過度に複雑な式は将来予測に弱くなることがあります。

少ないデータに高い次数を設定すると、偶然のばらつきまで曲線が拾ってしまう過学習のような状態になりがちです。

R2値だけを上げる目的で次数を増やすのではなく、業務上の変化の理由を説明できるかを考える必要があります。

指数・対数・移動平均の使い分け

指数近似曲線は、増加や減少の割合が一定で、値が急激に変化するデータへ適しています。

ただし、指数近似ではyの値に0や負の数が含まれると使用できません。

対数近似曲線は、最初は大きく変化するものの、次第に変化がゆるやかになる形を表したいときに役立ちます。

累乗近似も使えますが、xとyの値に0や負の数が含まれる場合は制約があります。

移動平均は、時系列の短期的な変動をならして傾向を見るためのもので、回帰式やR2値を表示する近似曲線とは目的が少し異なります。

散布図と近似曲線の見やすい設定【軸・ラベル・予測線の調整】

続いては、散布図と近似曲線を資料として読みやすく整える設定について確認していきます。

軸の範囲と単位の調整

散布図は軸の範囲によって印象が大きく変わります。

縦軸の最小値を0にするか、データの最小値付近にするかは、何を見せたいかで決めます。

売上の絶対量を比較する資料では0始まりが分かりやすい一方、わずかな差や変動を詳しく見たい分析では範囲を絞るほうが有効な場合もあります。

軸の範囲を狭くしたときは、差が実際より大きく見える可能性があるため、目盛りと単位を明確に示します。

軸を右クリックして軸の書式設定を開くと、最小値、最大値、目盛間隔を手動で設定できます。

金額なら円、千円、万円などの単位をタイトルや軸ラベルに含めると、数値の読み違いを防げます。

数式とR2ラベルの配置

数式とR2値は、グラフの情報量を高める一方で、データ点と重なると読みにくくなります。

近似曲線ラベルを選択して、空白のある位置へドラッグしましょう。

ラベルのフォントサイズは、グラフタイトルより控えめで、軸の目盛りより少し大きい程度にするとバランスが整います。

複数の系列があるグラフでは、すべての数式を表示すると煩雑になることがあります。

その場合は、主な系列だけを表示する、別表に係数とR2値を整理するなど、読み手に合わせた情報量に調整します。

グラフは数式を見せるための画面ではなく、データの関係を短時間で伝えるための図です。

予測期間の延長設定

近似曲線の書式設定では、予測の前方補間と後方補間を設定できます。

前方補間へ数値を入力すると、現在の最大x値より先まで近似曲線を延長できます。

後方補間では、最小x値より前へ曲線を伸ばせます。

たとえば広告費が40までのデータしかない場合に、前方補間を10とすれば、横軸50付近まで傾向線を表示するイメージです。

延長した部分は実績データではないため、実線ではなく破線にして予測範囲だと分かるようにする工夫も有効です。

予測線を延長してもR2値そのものが改善するわけではありません。

あくまで既存データから計算したモデルを先へ描画しているだけなので、外挿のリスクは残ります。

まとめ エクセルの散布図に近似曲線を追加する方法（R2・数式・回帰直線）

Excelの散布図へ近似曲線を追加すると、2つの数値の関係を視覚的に把握し、回帰直線の数式として活用できます。

散布図を作成したら、データ系列を選択し、グラフ要素または右クリックメニューから近似曲線を追加します。

直線的な傾向であれば線形近似を選び、グラフに数式を表示するへチェックを入れると、y=ax+bの形で回帰式を確認できます。

R2値は近似曲線の当てはまりを確認する大切な指標ですが、数値だけでなく散布図の形、外れ値、データ件数も合わせて判断することが重要です。

SLOPE関数、INTERCEPT関数、RSQ関数を使えば、傾き、切片、決定係数をセル内でも計算できます。

予測値を作るときは、先頭セルへ回帰式を入力してからオートフィルを使うと、複数の条件を効率よく比較できます。

線形、多項式、指数、対数などの近似曲線は、R2値の高さだけで選ばず、データの形と業務上の意味に合うものを選びましょう。

散布図の軸、単位、数式ラベル、予測範囲まで整えることで、Excelの近似曲線は分析結果を伝える実用的な資料になります。