エクセルで売上、気温、広告費、実験結果などをグラフ化したとき、点や棒の増減だけでは全体の傾向をつかみにくいことがあります。
そのような場面で役立つのが近似曲線です。
近似曲線をグラフへ追加すると、データの流れを線として可視化でき、将来値の見通しや相関の確認にも活用できます。
近似曲線は、散らばったデータの傾向を数式と線で表す機能です。
グラフの種類とデータの性質に合わせて、線形、指数、対数、多項式などを選ぶことが重要になります。
この記事では、エクセルのグラフに近似曲線を追加し、数式と決定係数を表示して相関を読み取る流れを詳しく解説します。
サンプルデータは1行目に見出しがある表を前提に進めます。
エクセルのグラフに近似曲線を追加する方法
| 月 | 広告費 | 売上 |
|---|---|---|
| 1月 | 10 | 120 |
| 2月 | 15 | 145 |
| 3月 | 20 | 165 |
| 4月 | 25 | 192 |
それではまず、グラフへ近似曲線を追加する基本操作について解説していきます。
近似曲線は元データそのものを変更せず、選択した系列に対して傾向線を重ねる機能です。
最初にグラフ内のデータ系列を正しく選ぶことが、操作を成功させる大切なポイントです。
散布図とデータ系列の選択
近似曲線を相関分析に使うなら、横軸と縦軸の数値の関係を正確に扱える散布図が適しています。
たとえば広告費と売上の関係を確認する場合、A列の月ではなく、B列の広告費を横軸、C列の売上を縦軸に設定します。
範囲B1からC5を選択し、挿入タブの散布図からマーカーのみの散布図を選びましょう。
月別の推移を見せたい折れ線グラフにも近似曲線は追加できますが、横軸が文字列として扱われる場合は、数値間隔に基づく分析には向きません。
グラフができたら、点や棒を一度クリックしてデータ系列全体を選択します。
選択に成功すると、各データ点の周囲に選択ハンドルが表示されます。
売上だけに近似曲線を付けたい場合は、売上系列を選択します。
複数系列があるグラフでは、意図しない系列を選ぶと別の線に近似曲線が追加されるため、凡例の色も確認しましょう。
グラフ要素からの近似曲線追加
データ系列を選んだ状態でグラフ右上に表示されるプラス記号のグラフ要素をクリックします。
一覧にある近似曲線へマウスポインターを合わせ、右側の矢印からその他のオプションを選択してください。
または、系列を右クリックして近似曲線の追加を選んでも同じ設定画面を開けます。
近似曲線が追加されると、既定では系列と似た色の直線がグラフ内に表示されます。
表示されないときは、グラフ全体ではなく、データ系列を選択しているかを確認します。
棒グラフの場合は、棒のどれか一つをクリックしてから右クリックすると、系列全体に対するメニューを開きやすくなります。
書式設定ウィンドウの確認
近似曲線の追加後には、画面右側に近似曲線の書式設定ペインが表示されます。
この画面では種類、予測、切片、数式、決定係数、線の見た目をまとめて設定できます。
最初は線形近似が選ばれることが多いものの、データに常に最適とは限りません。
近似曲線をクリックして選択し、塗りつぶしと線の項目から色、太さ、破線を調整すると、元の系列との区別がつきやすくなります。
報告書では、元データは控えめな色、近似曲線は目立つ色にすると、傾向を読み取りやすいグラフになります。
【操作のポイント】近似曲線はグラフ全体ではなくデータ系列単位で追加します。散布図を使う場合は、横軸と縦軸にする数値列を先に確認しましょう。
近似曲線の種類と使い分け
| 種類 | 向くデータ | 代表的な傾向 |
|---|---|---|
| 線形 | 一定の割合で増減 | 直線的な増加や減少 |
| 指数 | 増加率が大きく変化 | 複利的な伸び |
| 多項式 | 山や谷がある | 曲線的な変動 |
続いては、目的に合う近似曲線の種類を確認していきます。
線がデータ点の近くに通っていても、種類の選び方が不適切なら、予測や解釈を誤るかもしれません。
線形近似と一次関数
線形近似は、横軸の値が増えるにつれて縦軸の値もおおむね一定の割合で増減するデータに使います。
広告費が増えるほど売上がほぼ一定の割合で増えるような関係では、最初に線形を試すとよいでしょう。
線形近似曲線の式は、y ＝ ax ＋ b の形です。
aは傾きで、xが1増えたときにyがどの程度変化するかを示します。
bは切片で、xが0のときの推定値を表します。
傾きが正なら正の相関傾向、負なら負の相関傾向を示します。
ただし、近似曲線は因果関係を証明するものではありません。
広告費と売上が同時に増えていても、季節要因やキャンペーンなど、ほかの要因が影響している可能性があります。
指数近似と対数近似
指数近似は、値が増えるほど増加量そのものも大きくなるデータに向いています。
利用者数の急増、複利計算、初期段階で急速に広がる現象などが代表例です。
一方、対数近似は、最初は大きく増えるものの、次第に伸びが緩やかになるデータで使われます。
指数近似では、y ＝ b e のx乗に相当する形で傾向を表します。
対数近似では、y ＝ a ln x ＋ b に相当する形となり、横軸の値は0より大きい必要があります。
指数近似では、縦軸に0または負の値が含まれると利用できません。
対数近似では、横軸に0または負の値が含まれると設定できないため、元データを確認しましょう。
エラーではなく、数式として定義できない値が含まれていることが原因の場合もあります。
多項式近似と移動平均
上がった後に下がる、または周期的に山と谷を作るデータでは、多項式近似を検討します。
次数は曲線の複雑さを示し、2次なら大きく一度曲がる曲線、3次以上なら複数の変化を表現できます。
次数を高くしすぎると、少数のデータ点に無理に合わせた不自然な曲線になることがあります。
見栄えだけで次数を上げず、データ数と業務上の意味を踏まえて選ぶことが重要です。
日別売上や株価のように短期的な上下動をなだらかに見せたい場合は、移動平均も選択肢になります。
移動平均は将来を予測する式ではなく、一定期間の平均で変動を平滑化する方法です。
【操作のポイント】直線的な関係なら線形、急増なら指数、伸びが鈍化するなら対数、山や谷があるなら多項式を候補にします。
近似式と決定係数の表示
| 広告費 x | 売上 y | 読み取り例 |
|---|---|---|
| 10 | 120 | 低い広告費 |
| 20 | 165 | 売上が上昇 |
| 30 | 220 | 傾向線で予測可能 |
続いては、近似曲線の数式と決定係数をグラフに表示する方法を確認していきます。
数式を表示すると具体的な予測値を計算でき、決定係数を表示すると近似の当てはまりを判断できます。
グラフ上の数式表示
近似曲線の書式設定で、グラフに数式を表示するへチェックを入れます。
グラフ内にy ＝ 5.1x ＋ 68.3のような式が表示され、近似曲線の位置を計算で再現できるようになります。
たとえばxが30の広告費なら、式のxへ30を入れて売上の推定値を求めます。
y ＝ 5.1x ＋ 68.3 の場合、xが30なら y ＝ 5.1 × 30 ＋ 68.3 です。
計算結果は221.3となり、広告費30に対する売上の推定値として読み取れます。
数式ラベルはドラッグして見やすい空白部分へ移動できます。
ラベルがデータ点や凡例に重なると、グラフの可読性が大きく下がります。
決定係数 R二乗値の確認
同じ設定画面で、グラフにR二乗値を表示するへチェックを入れると、決定係数が表示されます。
R二乗値は0から1までの数値で、1に近いほど近似曲線がデータのばらつきをよく説明している目安です。
たとえばR二乗が0.92なら、売上の変動のうち約92パーセントを、その近似モデルで説明できるという見方ができます。
R二乗値が高くても、将来の結果を保証するわけではありません。
データ数が少ない場合や、偶然の変動を含む場合は、値だけで判断しないことが必要です。
決定係数はモデルの当てはまりを測る指標であり、因果関係の強さそのものではありません。
相関係数との違い
相関係数は、二つの数値データがどの程度一緒に増減するかをマイナス1から1の範囲で表す数値です。
エクセルではCORREL関数を使って求められます。
広告費がB2からB5、売上がC2からC5にある場合の数式です。
＝CORREL(B2:B5,C2:C5)
正の値なら一方が増えるともう一方も増える傾向、負の値なら一方が増えるともう一方が減る傾向を示します。
線形近似では、相関係数を二乗した値が決定係数と一致する関係があります。
ただし相関係数には正負の向きがあり、R二乗値には向きがありません。
【操作のポイント】数式は予測計算、R二乗値は当てはまり、CORREL関数は増減の向きを含む相関確認に使い分けます。
近似曲線による予測値の算出
| A列 | B列 | C列 |
|---|---|---|
| 月 | 広告費 | 売上 |
| 5月予測 | 30 | 予測式を入力 |
続いては、近似曲線を利用して予測値を算出する方法を確認していきます。
グラフの数式を目で読み取る方法のほか、ワークシート関数を使うと予測結果をセルで管理できます。
FORECAST.LINEAR関数の入力
線形近似による予測にはFORECAST.LINEAR関数が便利です。
1行目がヘッダーで、B2からB5に広告費、C2からC5に売上が入っている場合、C6へ次の数式を入力します。
＝FORECAST.LINEAR(B6,C2:C5,B2:B5)
B6の広告費を新しいx、C2からC5を既知のy、B2からB5を既知のxとして指定する構成です。
この関数は過去データから直線的な関係を求め、B6に入力した広告費に対応する売上を返します。
既知のyと既知のxの範囲は、同じ行数にそろえる必要があります。
片方だけ1行多い、あるいは見出しセルを含めると、正しい計算ができません。
傾きと切片を使う計算
近似式をセル内で組み立てたい場合は、SLOPE関数とINTERCEPT関数を使います。
傾きは ＝SLOPE(C2:C5,B2:B5) で求めます。
切片は ＝INTERCEPT(C2:C5,B2:B5) で求めます。
予測値は 傾き × 新しい広告費 ＋ 切片 で計算できます。
たとえばD2に傾き、D3に切片を表示し、C6へ ＝$D$2*B6＋$D$3 と入力する方法です。
先頭セルで数式を作った後、オートフィルで下方向へ引っ張れば、複数の広告費に対する予測値を一括で算出できます。
絶対参照の$D$2と$D$3を使うことで、オートフィル後も傾きと切片の参照先が固定されます。
予測区間と実績値の比較
近似曲線による予測値は、予算作成や目標設定の出発点として有用です。
一方で、実績値が必ず近似線の上に並ぶわけではありません。
季節性、値引き、在庫、競合の動きなど、モデルへ入っていない要因によって差が生まれます。
予測値は確定値ではなく、過去の傾向から得られた見込み値として扱いましょう。
月ごとに予測値と実績値を並べ、差額や誤差率を確認すると、モデルを見直す判断材料になります。
【操作のポイント】線形予測はFORECAST.LINEAR関数で管理すると便利です。数式をコピーする場合は、データ範囲の絶対参照を確認します。
近似曲線が追加できない場合の確認事項
| 状況 | 主な原因 | 確認内容 |
|---|---|---|
| 項目が選べない | グラフ種類 | 散布図へ変更 |
| 線が不自然 | 種類が不適切 | 線形以外も確認 |
| 数式が出ない | 表示設定 | チェック項目を確認 |
続いては、近似曲線を追加できないときや、結果が期待と違うときの確認事項を解説していきます。
操作ミスだけではなく、グラフの種類やデータ形式が原因となるケースもあります。
グラフ種類と対象系列
円グラフ、レーダーチャート、積み上げグラフなどでは、近似曲線を追加できない場合があります。
数値同士の関係を分析する目的なら、散布図へ変更するのが確実です。
グラフを選択し、グラフのデザインタブからグラフの種類の変更を選び、散布図を指定します。
横軸を数値として扱いたいときは、折れ線グラフより散布図が適しています。
また、系列ではなくグラフエリアや凡例を選んでいると、近似曲線の追加メニューが見つからないことがあります。
系列の点、線、棒のいずれかをクリックして選択状態を確認してください。
空白セルと文字列データ
数値列に空白、エラー値、文字列が混ざると、意図した近似結果にならないことがあります。
たとえば数値に見える「10個」や「1,000円」は、入力方法によっては文字列として扱われます。
セルを選択して数式バーを確認し、数値として認識されているかを確かめましょう。
欠損値を0で埋めると、本来はデータがないだけの箇所までゼロとして近似計算へ加わります。
空白を0へ置き換える前に、その0が業務上の実測値なのかを判断することが必要です。
過剰適合と外れ値
一部の極端に大きい値や小さい値があると、近似曲線が全体の実態を表さないことがあります。
外れ値を見つけたら、入力ミス、単位の違い、特別なキャンペーンなどの理由を確認します。
理由のある実績値を安易に削除するのではなく、注記を付けたうえで、外れ値を含む場合と除く場合を比較する考え方もあります。
多項式の次数を上げればR二乗値が高く見えることがありますが、未知のデータへの予測精度が上がるとは限りません。
【操作のポイント】追加できない場合は散布図と系列選択を確認します。近似結果が不自然なら、空白、文字列、外れ値、近似の種類を見直しましょう。
まとめ エクセルのグラフに近似曲線を追加する方法（相関・数式）
| 確認項目 | 活用場面 |
|---|---|
| 散布図と系列選択 | 数値同士の関係確認 |
| 数式とR二乗値 | 予測と当てはまり確認 |
| FORECAST.LINEAR関数 | セルでの予測値管理 |
エクセルの近似曲線は、グラフのデータ系列を選択し、グラフ要素または右クリックメニューから追加できます。
広告費と売上のような二つの数値の関係を確認する場合は、散布図に線形近似曲線を追加する方法が基本です。
グラフに数式とR二乗値を表示すると、傾向を感覚だけでなく数値でも判断できます。
急速な増加には指数近似、伸びの鈍化には対数近似、山や谷がある変動には多項式近似や移動平均を検討します。
将来値をセルで計算したいときは、FORECAST.LINEAR関数、SLOPE関数、INTERCEPT関数を活用しましょう。
ただし、近似曲線や相関係数は因果関係を示すものではありません。
データの件数、外れ値、季節要因、業務上の背景も確認しながら、根拠のある分析へつなげることが大切です。
