【Excel】散布図に近似曲線とR²（決定係数）を表示する方法 — 数式の読み方とセルで求める関数

散布図を作ってみたものの、「なんとなく右上がり」以上のことが言えない。そんなときは近似曲線を1本引くと、関係の強さと傾きが数字で出ます。

この記事では、気温と販売数のサンプルで近似曲線とR²を表示し、その数字の読み方と、同じ値をセルに出す関数までを順に説明します。

この記事で分かること

• 散布図に近似曲線を引き、数式とR²（決定係数）を表示する手順
• 近似曲線の数式（傾き・切片）とR²の読み方
• SLOPE・INTERCEPT・RSQ で同じ値をセルに出す方法
• FORECAST.LINEAR で、気温から販売数を予測する方法

完成形はこうなります。

どんな場面で使うか

• 気温と販売数、広告費と問い合わせ数など、2つの数字の関係を確かめたい
• 「関係がありそう」を、資料に書ける数字にしたい
• 片方の値から、もう片方のおおよその値を見積もりたい

基本説明

サンプルファイル（記事末尾）の「販売データ」シートを使います。

A列＝日付、B列＝最高気温(℃)、C列＝販売数(本) で、データは2行目から21行目までの20日分です。E列には、あとで関数の結果を入れる項目名を用意してあります。

近似曲線とR²とは

近似曲線は、散布図の点の真ん中を通るように引いた線です。線を式で表すと、片方の値からもう片方を見積もれます。

R²（決定係数）は、点がその線のまわりにどれだけ集まっているかを0〜1で表した数です。1に近いほど、点が線の近くに並んでいます。

近似曲線の種類

Excelでは6種類から選べます。まずは線形近似（直線）から試すのが基本です。

種類	形	向いている関係
線形近似	直線	片方が増えると、もう片方も一定の割合で増減する
指数近似	曲がりながら急に増える	増え方がだんだん速くなる。0以下の値があると選べない
対数近似	最初は急で、だんだん緩やか	増えるほど伸びが小さくなる
多項式近似	山や谷のある曲線	途中で増減が入れ替わる。次数（2〜6）を選ぶ
累乗近似	曲線	一方が何倍かになると、もう一方も決まった倍率で変わる
移動平均	ギザギザをならした線	傾向を見るだけ。数式は出ない

多項式近似は、次数を上げるほどR²が大きくなりやすい種類です。R²が上がっても、予測に向く線になったとは限りません。

手順

ここからは「販売データ」シートで操作します。

ステップ1 散布図を作る

1. B1:C21 を選ぶ（A列の日付は含めない）
2. 「挿入」タブ →「散布図」→「散布図」（マーカーだけの形）を選ぶ
3. 横軸の数字の上で右クリック →「軸の書式設定」を選ぶ
4. 「最小値」に 20、「最大値」に 36 を入力する

右上がりに点が並んでいます。軸の範囲の決め方は散布図を見やすくする記事で解説しています。

ステップ2 近似曲線と数式・R²を表示する

1. グラフの点のどれか1つを右クリックする
2. 「近似曲線の追加」を選ぶ
3. 右に開いた「近似曲線の書式設定」で「線形近似」が選ばれていることを確かめる
4. 下のほうの「グラフに数式を表示する」にチェックを入れる
5. 「グラフに R-2 乗値を表示する」にチェックを入れる

点の間を通る直線と、数式・R²のラベルが表示されます。ラベルが点と重なるときは、ドラッグで動かせます。

ステップ3 数式とR²を読む

表示された数式は「y = 傾き × x + 切片」の形です。このサンプルでは x が気温、y が販売数です。

• 傾き（約11.02）: 気温が1℃上がると、販売数が約11本増える傾向がある
• 切片（約−117.42）: 気温が0℃のときの値。データに無い範囲なので、意味のある数ではない
• R²（約0.874）: 販売数のばらつきの約87%を、気温との直線の関係で説明できる

R²が何以上なら良いという決まった基準はありません。また、R²が高くても、一方が原因でもう一方が変わったとまでは言えません。

ステップ4 傾き・切片・R²をセルに出す

グラフ上の数字は、桁を省いて表示されます。計算に使うときは、セルに関数で出します。

1. F2 を選ぶ
2. 次の4つの数式を F2〜F5 に1つずつ入力する

F2: =SLOPE(C2:C21,B2:B21)
F3: =INTERCEPT(C2:C21,B2:B21)
F4: =RSQ(C2:C21,B2:B21)
F5: =CORREL(C2:C21,B2:B21)

F2〜F5 に 11.02・-117.42・0.874・0.935 が出ます。F2〜F4 はグラフのラベルと同じ値です（ラベルは表示する桁数が違うだけです）。F5 の相関係数はラベルには出ません。

どの関数も、1つ目に y（販売数）、2つ目に x（気温） を渡します。CORREL は相関係数で、2乗すると RSQ と同じ値になります。

ステップ5 気温から販売数を予測する

1. F7 に 33 を入力する
2. F8 に次の数式を入力する

=FORECAST.LINEAR(F7,C2:C21,B2:B21)

F8 に 246.2 と出ます。最高気温33℃の日は、約246本売れる見込みです。

サンプルコードまたは例

ステップ4・5の数式と、結果をまとめます。表示桁数はサンプルのF列の設定です。

セル	数式	結果
F2	=SLOPE(C2:C21,B2:B21)	11.02
F3	=INTERCEPT(C2:C21,B2:B21)	-117.42
F4	=RSQ(C2:C21,B2:B21)	0.874
F5	=CORREL(C2:C21,B2:B21)	0.935
F8	=FORECAST.LINEAR(F7,C2:C21,B2:B21)	246.2（F7 が 33 のとき）

例：傾きと切片から予測する

FORECAST.LINEAR は、「切片＋傾き×x」を計算しているのと同じです。F2・F3 を使って、F8 の数式を次のように置き換えても、同じ 246.2 になります。

=F3+F2*F7

例：データに無い範囲を予測しない

F7 を 40 にすると、F8 は 323.3 になります。ただし、サンプルの気温は22.4〜34.8℃です。

その外側でも同じ直線が続くとは限りません。予測に使うのは、元データの範囲の中にとどめます。

よくあるエラー

グラフの数式が SLOPE の結果と合わない

原因: 散布図ではなく、折れ線グラフに近似曲線を引いています。折れ線グラフの近似曲線は、横軸の値ではなく 1, 2, 3… という並び順を x にして計算します。

このサンプルでは、R²が 0.103 まで下がります。日付の順に並べた番号と販売数の関係を見ているためです。

直し方: グラフを選び、「グラフのデザイン」（または「デザイン」）タブ →「グラフの種類の変更」で「散布図」に変えます。

SLOPE の結果が 0.08 のような小さな値になる

原因: 引数の順番が逆です。=SLOPE(B2:B21,C2:C21) は「販売数が1本増えると気温が何℃上がるか」を計算するので、0.08 になります。

直し方: 1つ目に y（販売数）、2つ目に x（気温）を渡します。RSQ と CORREL は順番を逆にしても同じ値になるので、間違いに気づきにくい点に注意します。

「近似曲線の追加」がメニューに無い

原因: 点ではなく、軸やグラフの余白を右クリックしています。

直し方: 点の1つにマウスを合わせてから右クリックします。グラフ右上の「＋」→「近似曲線」からも追加できます。

「指数近似」が選べない

原因: y の値に0以下が含まれています。指数近似は、y が0より大きいデータでしか計算できません。

直し方: 0以下の値が入力ミスなら直します。正しい値なら、ほかの種類を使います。

まとめ

• 近似曲線は、点を右クリック →「近似曲線の追加」で引ける。数式とR²はチェック2つで表示できる
• 数式の傾きは「x が1増えたときの y の増え方」、R²は「点が線のまわりにどれだけ集まっているか」
• 計算に使う値は SLOPE・INTERCEPT・RSQ でセルに出す。1つ目が y、2つ目が x
• 予測は FORECAST.LINEAR で出せる。元データの範囲の外は予測しない
• 近似曲線は散布図に引く。折れ線グラフだと x が並び順になる

まずはサンプルで近似曲線を引き、F2〜F4 の値とグラフのラベルを見比べてみてください。

関連記事

• 【Excel】散布図を見やすくする — 軸の範囲・目盛・データラベルの設定
• 【Excel】 散布図に円を表示させる
• 【学習ガイド】Excel関数はこの順番で覚える — 実務で使う順ロードマップ

対応バージョン: Excel 2016 以降（FORECAST.LINEAR を使うため）

サンプルファイル

記事中で使用しているサンプルデータはこちらからダウンロードできます。

• excel-scatter-trendline-sample.xlsx