エクセルの散布図では、売上と広告費、気温と販売数、勉強時間と得点のような二つの数値の関係を視覚的に確認できます。
ただし、点が並んでいるだけでは、全体として増加傾向なのか、減少傾向なのか、曲線的な変化なのかを判断しにくい場面もあるでしょう。
そのようなときに役立つ機能が近似曲線です。
散布図に近似曲線を追加すると、データの傾向を線で示し、回帰式や決定係数もグラフ上に表示できます。
この記事では、散布図への回帰直線の追加、数式表示、指数近似や多項式近似の使い分け、予測値の計算方法まで順番に解説します。
散布図の近似曲線で押さえたいポイント
・グラフを選択して近似曲線を追加する
・線形近似では回帰直線と数式を表示できる
・決定係数 R² を確認して当てはまりを判断する
・数式は A列を説明変数、B列を目的変数としたサンプルで確認する
散布図の種類や近似方法を適切に選べば、数値の関係を説明しやすい資料へ整えられます。
エクセルの散布図に近似曲線を追加する方法
それではまず、散布図に近似曲線を追加し、回帰直線を表示する基本操作について解説していきます。
| 広告費 A列 | 売上 B列 |
|---|---|
| 10 | 82 |
| 20 | 106 |
| 30 | 128 |
| 40 | 155 |
| 50 | 177 |
ここでは、1行目を見出しとして、A2からA6に広告費、B2からB6に売上を入力した表を使用します。
横軸に広告費、縦軸に売上を配置すると、広告費が増えるほど売上も増える関係を確認できます。
散布図の作成操作
まず、A1からB6までの範囲をドラッグして選択します。
このとき、1行目の見出しも含めて選択すると、グラフの軸タイトルや系列名を後から確認しやすくなります。
続いて、リボンの挿入タブを開き、グラフグループにある散布図のアイコンを選びましょう。
最初は、マーカーのみで表示される散布図を選ぶと、各データの位置を正確に把握できます。
折れ線グラフではなく散布図を選ぶことが重要です。
折れ線グラフは横軸を項目として扱うため、数値同士の距離や関係を分析する回帰分析には散布図のほうが適しています。
作成後のグラフでは、横軸の目盛りが広告費、縦軸の目盛りが売上の値として表示されているか確認してください。
散布図を作る前に、空白セルや文字列が途中に混ざっていないかを確認しましょう。
数値ではないデータが含まれると、意図しない系列や空白のあるグラフになる場合があります。
操作対象の範囲を最初に整えておくことが、見やすい近似曲線につながります。
グラフ要素からの近似曲線追加
散布図をクリックすると、グラフの右上付近にプラス記号のグラフ要素ボタンが表示されます。
プラス記号をクリックし、近似曲線にチェックを入れると、データ点に沿う直線が自動的に追加されます。
初期設定では、多くの場合に線形近似が選択されます。
線形近似は、データの変化を直線で表す方法であり、広告費と売上のようにおおむね一定の割合で変化するデータに向いています。
近似曲線の右側にある矢印を選ぶと、線形以外の候補も表示されます。
近似曲線の色や太さを変えたい場合は、追加された線をクリックしてから右クリックし、近似曲線の書式設定を選択します。
資料で目立たせたい場合は、データ点より少し濃い色の線にすると、傾向を読み取りやすくなるでしょう。
【操作のポイント】グラフ要素ボタンで近似曲線へチェックを入れた後、線を一度クリックすると、近似曲線だけを選択できます。
系列の右クリックによる近似曲線追加
グラフ要素ボタンが見つけにくい場合は、散布図の点のどれかをクリックして、データ系列全体を選択します。
次に右クリックし、表示されたメニューから近似曲線の追加を選択してください。
右側に近似曲線の書式設定作業ウィンドウが開き、近似の種類を細かく指定できるようになります。
データ点ではなくグラフの余白を右クリックすると、近似曲線の追加が表示されないことがあります。
選択中の対象が系列であることを確認してから操作するのがコツです。
複数の系列がある散布図では、近似曲線を追加したい系列を先に選択してください。
たとえば、前年と今年の売上を別系列で配置した場合、各系列に別々の回帰直線を引けます。
比較する系列が多い場合は、近似曲線と系列の色を合わせると、どの線がどのデータに対応するか分かりやすくなります。
【操作のポイント】複数系列の散布図では、近似曲線を追加する前に系列をクリックし、対象のマーカーすべてが選択状態になったことを確認します。
近似曲線の数式と決定係数の表示設定
続いては、回帰直線の数式と決定係数を散布図に表示する設定を確認していきます。
| 広告費 A列 | 売上 B列 | 散布図での役割 |
|---|---|---|
| A2からA6 | B2からB6 | 横軸と縦軸の対応 |
| 説明変数 x | 目的変数 y | 回帰式の計算対象 |
数式を表示すると、グラフの印象だけでなく、数値を使って傾向を説明できるようになります。
広告費を x、売上を y とした場合、線形近似では y＝ax＋b の形で数式が表示されます。
回帰式における記号の意味
y は予測したい値です。
x は基準となる入力値です。
a は傾きで、x が1増えたときの y の変化量を表します。
b は切片で、x が0のときの y の推定値を表します。
近似曲線の書式設定画面
近似曲線をクリックし、右クリックから近似曲線の書式設定を選択します。
右側の作業ウィンドウにある近似曲線のオプションでは、線形、指数、対数、多項式、累乗、移動平均などを選択できます。
広告費と売上がほぼ直線的に増える今回の例では、線形を選択するのが自然です。
最初から複雑な近似曲線を選ぶより、データの意味に合う単純なモデルから確認することが大切です。
データが少ない状態で高次の多項式を使うと、点には近づいて見えても、将来の予測には使いにくい曲線になる場合があります。
書式設定画面では、近似曲線の名前を系列名に基づく自動表示から、任意の名前へ変更することも可能です。
報告書に掲載するなら、広告費と売上の回帰直線のように、読み手が意味を理解しやすい名称に整えるとよいでしょう。
【操作のポイント】近似曲線の種類は見た目だけで決めず、横軸と縦軸に置いた数値の関係や業務上の意味と照らし合わせます。
グラフ上への数式表示
近似曲線の書式設定画面を下へスクロールすると、グラフに数式を表示するというチェック項目があります。
この項目にチェックを入れると、たとえば y＝2.4x＋58.1 のような回帰式がグラフ内に表示されます。
表示された数式は、ドラッグしてグラフの見やすい位置へ移動できます。
データ点と重なって読みにくいときは、グラフ右上や空白の多い位置へ配置しましょう。
数式の y は縦軸の値、x は横軸の値に対応します。
つまり、横軸に広告費を入力すれば、式から予測売上を計算できるという意味です。
数式内の係数は表示桁数によって丸められることがあります。
精密な計算に利用する場合は、表示された式だけを転記せず、後ほど紹介する SLOPE 関数や INTERCEPT 関数も使って確認してください。
グラフ内の数式は説明用として便利ですが、セルで再利用する計算式としては表示桁数に注意が必要です。
分析結果を資料へ掲載する場合は、元データの単位と数式の単位も併記すると誤解を防げます。
決定係数 R² の確認
近似曲線の書式設定には、グラフに R-2乗値を表示するという項目もあります。
チェックを入れると、数式の近くに R²＝0.98 のような値が表示されます。
R²は決定係数と呼ばれ、近似曲線がデータのばらつきをどの程度説明できているかを見る目安です。
一般には1に近いほど、データ点が近似曲線に沿っていると判断できます。
ただし、R²が高いことだけで、二つの数値に因果関係があるとは断定できません。
広告費と売上の関係であれば、季節要因、商品の価格、キャンペーン時期など、ほかの要因も売上へ影響する可能性があります。
決定係数は、あくまで今回のデータに対する当てはまりを確認する数値として扱いましょう。
【操作のポイント】R²は高いほど直線への当てはまりを確認しやすくなりますが、将来の売上を保証する数値ではありません。
回帰直線の数式をセルで求める方法
続いては、散布図に表示した回帰直線と同じ考え方で、傾き、切片、予測値をセルに求める方法を確認していきます。
| A列 | B列 | D列 |
|---|---|---|
| 広告費 | 売上 | 計算内容 |
| 10から50 | 82から177 | 傾き、切片、予測値 |
ここでも、1行目に見出しがあり、実際のデータは2行目から6行目まで入力されているものとします。
グラフで確認した傾向をセルでも扱えるようにすると、予測表や見積もり表へ応用できます。
SLOPE関数による傾きの計算
D1に傾き、D2に数式を入力する場合は、D2へ次の数式を入力します。
=SLOPE($B$2:$B$6,$A$2:$A$6)
SLOPE関数は、回帰直線 y＝ax＋b における a、つまり傾きを返す関数です。
最初の引数には縦軸の値である売上の範囲 B2からB6を指定し、二番目の引数には横軸の値である広告費の範囲 A2からA6を指定します。
SLOPE関数では、目的変数を先、説明変数を後に指定します。
引数の順番を逆にすると、意図した回帰式の傾きにはなりません。
数式内のドル記号は絶対参照です。
後から別のセルへ数式をコピーする可能性がある場合でも、参照範囲を固定できます。
今回のデータでは、おおよそ2.38という値が返されます。
これは、広告費が1増えると、売上が平均的に約2.38増える傾向を表す数値です。
【操作のポイント】SLOPE関数では、yに当たる売上列を先頭の引数へ、xに当たる広告費列を二番目の引数へ入力します。
INTERCEPT関数による切片の計算
次に、D3などのセルへ切片を計算する数式を入力します。
=INTERCEPT($B$2:$B$6,$A$2:$A$6)
INTERCEPT関数は、回帰直線 y＝ax＋b における b、つまり切片を返します。
切片は、横軸の値が0の場合に、縦軸の値がどの程度になると推定されるかを示す数値です。
広告費が0という状態が実務上あり得るかは、データの範囲や事業内容によって異なります。
そのため、切片は必ずしも実際の0円広告費における売上を意味するとは限りません。
あくまで、入力したデータ範囲から求められた直線の位置を決める数値として読み取ることが必要です。
グラフに表示した数式の定数項と、INTERCEPT関数の結果は、表示桁数の違いを除けば同じ方向の値になります。
数式表示が y＝2.38x＋58.4 なら、傾きは約2.38、切片は約58.4です。
【操作のポイント】切片はグラフの数式にあるプラスまたはマイナスの後ろの数値に対応し、INTERCEPT関数でより細かい桁数を確認できます。
予測値の計算式とオートフィル
たとえば、F列に予測広告費、G列に予測売上を作成するとします。
F1に予測広告費、G1に予測売上と入力し、F2からF6に10、20、30、40、50を入力してください。
G2には、D2の傾きとD3の切片を利用して、次の数式を入力します。
=$D$2*F2+$D$3
この式では、$D$2が傾き、F2が広告費、$D$3が切片です。
G2を選択し、セル右下のフィルハンドルを下へドラッグすれば、G3からG6にも数式をコピーできます。
先頭セルに数式を入れ、オートフィルで下へ展開すると、広告費ごとの予測売上を効率よく作成できます。
実測売上と予測売上を並べれば、どの月やどの条件で予測との差が大きかったかも確認しやすくなります。
予測値を四捨五入して整数表示にしたい場合は、ROUND関数で数式全体を囲む方法もあります。
=ROUND($D$2*F2+$D$3,0)
最後の0は、小数点以下を表示しない指定です。
【操作のポイント】オートフィルでコピーする数式では、傾きと切片のセルを絶対参照にし、広告費のセルだけを相対参照にします。
線形近似以外の近似曲線の種類
続いては、データの形に応じて選べる近似曲線の種類と、散布図での見分け方を確認していきます。
| データの見え方 | 候補となる近似曲線 | 利用場面 |
|---|---|---|
| ほぼ一直線 | 線形 | 増減量が比較的一定 |
| 曲がりながら増減 | 多項式 | 山や谷のある変化 |
| 急激に増加 | 指数 | 複利的な変化 |
近似曲線は、見栄えのよい線を作るためだけの機能ではありません。
データの意味と傾向に合わせて選ぶことで、分析の方向性を整理できます。
線形近似の特徴
線形近似は、すべての近似曲線のなかでも基本となる種類です。
データが右上がりまたは右下がりに、おおむね直線状に並ぶ場合に使用します。
広告費と売上、作業時間と処理件数、商品の個数と合計金額などは、条件によって線形近似を検討しやすい例です。
線形近似では、傾きが変化量を表すため、数式の意味を説明しやすい利点があります。
一方で、実際には途中から効果が鈍る、上限に近づく、季節ごとに動きが変わるといったデータを一本の直線で説明しようとすると、ズレが大きくなることもあります。
まず散布図の点の配置を見て、直線が全体の中心を通るイメージになるか確認してください。
R²だけに頼らず、外れ値が極端に影響していないかを見ることも必要です。
【操作のポイント】直線の傾きが業務上の感覚と大きく異なる場合は、単位、入力範囲、外れ値を見直します。
多項式近似の次数設定
多項式近似は、データが曲線的に増えたり減ったりする場合に使える方法です。
近似曲線の書式設定で多項式を選ぶと、次数を2から6の範囲で指定できます。
2次では放物線のような曲線になり、3次以上では変化の方向が複数回変わる曲線を表現できます。
たとえば、気温が低すぎても高すぎても売上が下がり、中間の気温で売上が高くなるようなデータでは、2次近似を検討できるかもしれません。
次数を上げるほどデータ点に近づくとは限らず、未来の値が不自然に大きく振れる場合があります。
特にデータ件数が少ない場合、複雑な曲線は偶然のばらつきまで表してしまうことがあります。
予測目的なら、対象範囲の外側へ安易に延長しないことが大切です。
多項式近似を選ぶときは、低い次数から試し、データの意味を説明できる曲線かどうかを確認しましょう。
グラフ上の曲線が細かく上下する場合は、次数が高すぎる可能性があります。
【操作のポイント】多項式の次数は、R²が高くなることだけを目的に増やさず、現象の変化を無理なく説明できる最小の次数を選びます。
指数近似と対数近似の使い分け
指数近似は、値が増えるほど増加量も大きくなるようなデータに向いています。
たとえば、一定の割合で増える残高、初期に急速に増える利用者数などで検討されます。
一方、対数近似は、初期には大きく増えるものの、次第に増加が緩やかになるデータに使いやすい方法です。
学習時間と習熟度、最初の広告出稿量と認知の伸びなどでは、効果が逓減する形になることがあります。
指数近似や対数近似では、使用できない値の条件があります。
対数近似では横軸に0以下の値を含められず、累乗近似でも値の条件に注意が必要です。
エラーになるときは、横軸や縦軸に0、負の数、空白が含まれていないかを確認しましょう。
【操作のポイント】急激な増加か、次第に伸びが鈍る変化かを散布図で確認してから、指数近似または対数近似を試します。
散布図の近似曲線で発生しやすい問題
続いては、近似曲線が追加できない、数式が表示されない、期待した形にならない場合の確認ポイントを解説していきます。
| 状況 | 主な確認項目 |
|---|---|
| 近似曲線が追加できない | グラフ種類と選択対象 |
| 数式が表示されない | 書式設定のチェック項目 |
| 線が不自然 | データ範囲と近似種類 |
操作の問題に見えても、元データやグラフの種類が原因になっていることがあります。
原因を一つずつ切り分けると、設定を戻しやすくなります。
近似曲線メニューが表示されない原因
近似曲線の追加メニューが表示されない場合は、グラフの種類を確認してください。
散布図では近似曲線を追加できますが、円グラフ、ドーナツグラフ、レーダーチャートなどでは対応していません。
また、グラフ全体ではなく、データ系列を選択した状態で右クリックする必要があります。
系列が選択できていると、すべてのデータ点に選択枠が付いたように見えます。
ピボットグラフでは、近似曲線を利用できないケースがあります。
ピボットテーブルの集計結果を使いたい場合は、通常のセル範囲へ値をコピーし、その範囲から通常の散布図を作成する方法を検討してください。
グラフの種類を変更するには、グラフデザインタブからグラフの種類の変更を選択します。
【操作のポイント】近似曲線の項目が見当たらないときは、まず通常の散布図かどうか、次にデータ系列を選択しているかを確認します。
数式表示が見つからない場合
数式を表示するチェック項目は、近似曲線を追加しただけでは自動でオンになりません。
近似曲線の書式設定を開き、画面下部にあるグラフに数式を表示する項目を探してください。
作業ウィンドウの幅が狭い場合や、表示倍率が高い場合は、下へスクロールしないと項目が見えないことがあります。
数式にチェックを入れても見つからない場合は、グラフの枠外へ移動していないか確認しましょう。
グラフをクリックして選択し、表示される数式ラベルを探すと見つかる場合があります。
数式ラベルはテキストとしてドラッグ移動できるため、データ点との重なりを避けられます。
小数点以下の桁数を調整したい場合は、数式ラベルを右クリックして近似曲線ラベルの書式設定を開き、表示形式を変更します。
【操作のポイント】数式ラベルを移動した後は、グラフの外へはみ出していないかを確認し、印刷範囲でも読める位置に整えます。
近似結果が実感と合わない場合
近似曲線が実感と合わない場合は、データ範囲が正しいかを最初に確認します。
見出し行以外に、合計行、途中の空白行、別の単位のデータが混ざると、意図しない回帰結果になることがあります。
たとえば、広告費を円単位で入力した行と千円単位で入力した行が混在すれば、散布図は大きく歪みます。
外れ値がある場合も、回帰直線の傾きや切片は影響を受けます。
外れ値を削除する前には、入力ミスではないか、特殊なキャンペーンや欠品などの事情があったのかを確認しましょう。
都合の悪い値を単に消すのではなく、外れ値になった理由を記録して分析する姿勢が重要です。
近似曲線の種類を変える前に、データの単位、範囲、入力誤り、外れ値を確認すると、問題の原因を見つけやすくなります。
【操作のポイント】回帰式を使った予測は、元データと大きく離れた範囲へ適用するほど誤差が大きくなる可能性があります。
近似曲線を見やすく活用する資料作成
続いては、散布図、回帰直線、数式を読みやすく配置し、資料で活用するための整え方を確認していきます。
| 要素 | 整え方 |
|---|---|
| グラフタイトル | 何と何の関係かを明記 |
| 軸タイトル | 単位を含めて表示 |
| 回帰式とR² | データ点と重ならない位置へ配置 |
分析結果を伝える際は、近似曲線を追加しただけで終わらせず、読み手が判断できる形へ整えることが大切です。
とくに社内資料や報告書では、数式の意味を補うラベルがあると伝達力が上がります。
グラフタイトルと軸タイトルの設定
グラフタイトルには、広告費と売上の関係のように、何を比較している散布図なのかを具体的に入力します。
横軸と縦軸には、広告費 千円、売上 千円のように単位も併記してください。
数値だけが並ぶグラフでは、単位の違いが読み取りの誤りにつながることがあります。
軸タイトルに単位を入れると、回帰式の係数を解釈しやすくなります。
グラフ要素ボタンから軸ラベルへチェックを入れると、横軸ラベルと縦軸ラベルを追加できます。
タイトルが長すぎる場合は、分析対象、期間、単位を本文側で補足し、グラフ内では簡潔な表現にする方法もあります。
複数のグラフを並べる資料では、タイトルの表記ルールをそろえると比較しやすくなります。
【操作のポイント】グラフタイトルには比較対象、軸タイトルには項目名と単位を入れ、数値の意味をグラフ単体で理解できる状態にします。
回帰式とR²の配置調整
数式とR²の表示は、グラフの右上、左上、または点が少ない空白部分に置くと読みやすくなります。
近似曲線ラベルを選択してドラッグすれば、位置を自由に変更できます。
散布図の点が密集している中央部分に数式を置くと、データの確認を妨げることがあります。
近似曲線の線が細すぎる場合は、書式設定から幅を少し太くし、破線ではなく実線にすると視線を追いやすくなります。
ただし、線を強調しすぎると、実測値であるデータ点が見えにくくなるかもしれません。
散布図では実測値のマーカーと傾向を示す近似曲線の両方が読み取れるバランスが重要です。
複数系列の回帰直線を使う場合は、凡例を表示し、線の色と系列名を対応させましょう。
【操作のポイント】数式とR²はデータの上に重ねず、グラフ内の余白へ移動して、印刷時の文字サイズも確認します。
予測と実測の比較方法
回帰式から求めた予測値は、実測値と並べて比較すると活用しやすくなります。
たとえば、C列に予測売上、D列に実測値との差を作成する方法があります。
D2へ次の数式を入力すると、実測売上と予測売上の差を確認できます。
=B2-C2
差がプラスなら実測売上が予測より高く、マイナスなら実測売上が予測より低い状態です。
差の大きい行を確認すると、広告費以外の要因が強く影響した期間を見つける手がかりになります。
予測値は意思決定を補助する材料であり、未来を確定する数値ではありません。
過去のデータ範囲で得られた回帰式は、条件が変わった後の結果まで保証するものではありません。
データを定期的に更新し、散布図と近似曲線を作り直すことで、変化した傾向を確認できます。
【操作のポイント】予測値だけを見るのではなく、実測値との差を確認し、誤差が生じた背景を分析へ活用します。
まとめ エクセルの散布図に近似曲線を追加する方法
エクセルの散布図に近似曲線を追加するには、まずA列とB列の数値を選択して散布図を作成し、グラフ要素または右クリックメニューから近似曲線を追加します。
広告費と売上のように直線的な関係が想定されるデータでは、線形近似を選ぶと回帰直線を表示できます。
近似曲線の書式設定でグラフに数式を表示する項目とR²を表示する項目へチェックを入れると、傾向を数値でも説明できます。
回帰式の傾きはSLOPE関数、切片はINTERCEPT関数で求められます。
予測値をセルで計算する場合は、傾きと切片を絶対参照にし、横軸の値だけを相対参照にしてオートフィルを利用しましょう。
線形近似だけで説明しにくい場合は、多項式、指数、対数なども候補になります。
ただし、R²の高さや曲線の見た目だけで判断せず、元データの意味、単位、外れ値、利用する範囲を確認することが欠かせません。
散布図、近似曲線、数式、決定係数を組み合わせ、数値の関係を分かりやすく伝える資料作成へ役立てていきましょう。
