Excelで売上や測定値の推移をグラフ化したとき、データがどのような傾向を持つのかを視覚的に把握したい場面があります。
そのようなときに役立つ機能が、散布図や棒グラフなどへ追加できる近似曲線です。
近似曲線を使うと、個々の値のばらつきだけでは見えにくい増加傾向、減少傾向、周期性を確認しやすくなります。
さらにグラフ上に数式と決定係数を表示すれば、見た目だけでなく数値を根拠にしてデータの関係を判断できます。
近似曲線の基本ポイント
・散布図では横軸と縦軸の数値的な関係を確認しやすいです。
・線形近似では y＝ax＋b の形で変化の大きさを確認できます。
・決定係数 R² は、近似式がデータをどの程度説明できるかの目安です。
この記事では、Excelのグラフへ近似曲線を追加し、式や相関に関する指標を表示する手順を、サンプルデータとともに詳しく解説します。
エクセルのグラフに近似曲線を追加する方法
それではまず、グラフに近似曲線を追加する基本操作について解説していきます。
| 月 | 広告費 | 売上 |
|---|---|---|
| 1 | 10 | 105 |
| 2 | 20 | 128 |
| 3 | 30 | 151 |
| 4 | 40 | 171 |
近似曲線は、グラフ内の系列を選択してから追加するのが基本です。
横軸と縦軸の数値の関係を分析したい場合は、折れ線グラフより散布図を使うことが重要です。
散布図の作成
最初に、B列の広告費とC列の売上を含む範囲を選択します。
サンプルデータでは、1行目を見出しとして、B1からC5を選択する想定です。
続いて、Excelの挿入タブから散布図を選び、マーカーのみが表示される散布図を作成しましょう。
散布図では、横軸に広告費、縦軸に売上が配置されるため、2つの数値の関係を自然に表せます。
折れ線グラフはカテゴリの順番を表す用途に向いていますが、横軸の間隔まで数値として分析する目的には散布図のほうが適しています。
たとえば広告費が10万円から20万円へ増えたときと、30万円から40万円へ増えたときを比較する場合、散布図なら横軸の数値間隔を正しく反映できます。
グラフ要素からの近似曲線追加
作成した散布図をクリックすると、グラフの右上付近にグラフ要素のプラス記号が表示されます。
プラス記号をクリックし、近似曲線のチェックボックスをオンにすると、既定では線形の近似曲線が追加されます。
近似曲線の右側にある矢印を選択すると、線形、指数、対数、多項式、累乗、移動平均といった種類を選べます。
データが概ね一直線に増減しているなら、まずは線形近似曲線を選ぶと判断しやすいでしょう。
グラフ上に線が引かれたら、元のデータ点から著しく離れていないかも確認します。
線がデータの中央を通るように見える場合でも、後述する決定係数を確認することで、より客観的な評価が可能です。
系列の右クリックによる追加
グラフ要素のプラス記号が見つからない場合は、データ系列の点を右クリックする方法も便利です。
売上を表すマーカーのいずれかをクリックして系列全体を選択し、右クリックメニューから近似曲線の追加を選択します。
右側に近似曲線の書式設定ペインが開くため、種類や予測期間、表示設定をまとめて調整できます。
データ点ではなくグラフの余白を選択していると近似曲線を追加できないため、系列が選択状態かを確認しましょう。
複数系列があるグラフでは、近似曲線を付けたい系列を先に選ぶことも大切です。
【操作のポイント】近似曲線はグラフ全体ではなく、選択したデータ系列ごとに追加されます。
近似曲線の式と決定係数の表示設定
続いては、近似曲線の式と決定係数をグラフに表示する設定を確認していきます。
| 広告費 x | 売上 y | 考え方 |
|---|---|---|
| 10 | 105 | 横軸と縦軸の1組の値 |
| 20 | 128 | 近似線からの距離を確認 |
| 30 | 151 | 式による予測値の対象 |
式を表示すると、近似曲線がどの計算式で描かれているのかを確認できます。
決定係数を表示すると、その式が元データの動きをどの程度うまく表しているかを判断できます。
近似曲線の書式設定ペイン
近似曲線をクリックし、右クリックメニューから近似曲線の書式設定を選択します。
右側に表示されるペインには、近似曲線のオプション、塗りつぶしと線、効果などの項目があります。
近似曲線のオプションを開くと、近似または回帰の種類を切り替えられます。
ここで線形が選択されていることを確認したうえで、ペインの下側までスクロールしましょう。
設定内容はグラフへすぐ反映されるため、表示位置や文字の重なりもその場で確認できます。
操作をやり直す必要がある場合でも、元の表の数値は変更されません。
グラフに数式を表示する設定
近似曲線のオプションにあるグラフに数式を表示するチェックボックスをオンにします。
すると、グラフ上に y＝2.2x＋83.5 のような式が表示されます。
この式では、xが横軸の広告費、yが縦軸の売上を表します。
傾きにあたる2.2は、広告費が1単位増えた場合に売上が平均してどの程度変化するかを示す値です。
切片にあたる83.5は、xが0であると仮定したときの推定値です。
切片は現実の条件では意味を持たない場合もあるため、数式を読むときはデータの範囲を意識する必要があります。
線形近似の基本式
y＝ax＋b
aは傾き、bは切片です。
xへ任意の値を入れることで、近似式上の予測値を計算できます。
決定係数R二乗の表示
同じ近似曲線のオプションで、グラフにR二乗値を表示するチェックボックスもオンにします。
グラフには R²＝0.98 のような表示が追加されます。
R²は0から1の範囲で示され、1に近いほど近似式がデータの変動をよく説明している状態です。
ただし、R²が高いからといって、必ずしも原因と結果の関係が証明されるわけではありません。
広告費と売上には季節、商品構成、価格変更など別の要因も影響する可能性があります。
R²は近似の当てはまりを示す指標であり、因果関係を直接示す数値ではない点が重要です。
【操作のポイント】式とR²の表示は、近似曲線の書式設定ペインで個別にオンにできます。
線形近似曲線と相関係数の確認
続いては、線形近似曲線の読み方と相関係数をワークシートで求める方法を確認していきます。
| セル | 入力内容 | 用途 |
|---|---|---|
| E1 | 相関係数 | 見出し |
| E2 | =CORREL(B2:B5,C2:C5) | 相関係数の算出 |
Excelのグラフに表示されるR²と、関数で求める相関係数は関連していますが、同じ値ではありません。
線形回帰においては、相関係数を二乗すると決定係数R²になります。
CORREL関数による相関係数
相関係数をセルで確認するには、CORREL関数を使います。
広告費がB列、売上がC列にあり、1行目が見出しの場合、E2セルへ次の数式を入力します。
=CORREL(B2:B5,C2:C5)
この数式では、B2からB5までを1つ目のデータ範囲、C2からC5までを2つ目のデータ範囲として指定します。
両方の範囲は同じ行数で対応している必要があります。
相関係数はマイナス1から1までの範囲で表示され、1に近いほど強い正の相関、マイナス1に近いほど強い負の相関を表します。
0付近なら、直線的な関係は弱いと考えられます。
Excel画面での数式入力
数式バーへ入力する場合も、最初に結果を表示したいE2セルを選択します。
数式を確定すると、E2には計算結果が表示されます。
複数の組み合わせを比較する場合は、E1に項目名を入力し、隣の列へ別のCORREL関数を配置すると整理しやすくなります。
データ範囲に空白セルや文字列が多いと、意図した比較にならないことがあります。
数式を入力する前に、横軸用と縦軸用のデータが同じ行で対応しているか確認しましょう。
相関係数と決定係数の使い分け
相関係数は関係の向きも含めて確認できる指標です。
たとえば広告費が増えるほど売上が増える場合、相関係数は正の値になります。
一方で、決定係数R²は0から1の値となるため、正の関係か負の関係かはR²だけでは判断できません。
売上が広告費の増加に伴って下がるデータでも、直線としてよく当てはまればR²は高くなります。
相関係数は関係の方向と強さを確認するための値です。
決定係数R²は近似曲線がデータへ当てはまる程度を確認するための値です。
【操作のポイント】正の関係か負の関係かを知りたい場合は、R²だけでなくCORREL関数の結果も確認します。
近似曲線の種類と選び方
続いては、データの形に応じた近似曲線の種類を確認していきます。
| 種類 | 向いている傾向 | 注意点 |
|---|---|---|
| 線形 | 一定の割合で増減 | 最初に検討しやすい種類 |
| 多項式 | 曲線的に上下する変化 | 次数を上げすぎない |
| 指数 | 増加率が大きくなる変化 | ゼロ以下の値に注意 |
線形近似曲線
線形近似曲線は、データの変化を直線として表す最も基本的な種類です。
売上、費用、数量、時間などの関係が、おおむね一定の割合で増減する場合に向いています。
迷ったときは、まず線形近似曲線を追加してR²とデータ点の位置を確認する方法が実務的です。
線形は数式の読み方が比較的わかりやすく、傾きから変化の大きさを説明しやすい特徴があります。
ただし、データの途中で増加率が大きく変わる場合や、山型の動きをする場合には、直線だけで説明するのは難しいでしょう。
多項式近似曲線
多項式近似曲線は、曲線的な変化を表したい場合に使います。
たとえば気温と電力使用量、回転数と燃費のように、ある地点を境に増減の様子が変わるデータで検討できます。
次数を2にすると放物線に近い形となり、次数を上げるほど細かな曲がりにも追従します。
しかし、次数を必要以上に高くすると、偶然のばらつきまで曲線が拾ってしまうことがあります。
R²を高くすることだけを目的に次数を増やすと、将来予測には使いにくい式になる可能性があります。
多項式近似では、データの特徴を過度に再現しすぎないことが大切です。
グラフの見た目、R²、データが生まれた背景を合わせて判断しましょう。
指数・対数・移動平均
指数近似曲線は、時間が進むにつれて増加量そのものが大きくなるようなデータに向いています。
複利的な増加、初期の急成長、減衰現象などが代表例です。
対数近似曲線は、最初は大きく変化するものの、次第に変化が緩やかになる傾向で利用できます。
移動平均は数式を使って将来値を推定する目的よりも、日々の売上やアクセス数の細かな上下動をならして傾向を見る目的に適しています。
移動平均では期間を3、5、7などに設定できますが、期間を長くするほど滑らかになる一方で変化への反応は遅くなります。
【操作のポイント】近似曲線の種類は、グラフの形だけでなく、データを何のために分析するかで選びます。
数式による予測値とグラフの調整
続いては、表示した近似式を使って予測値を求める方法と、グラフを見やすく整える方法を確認していきます。
| セル | 内容 | 例 |
|---|---|---|
| G1 | 予測用広告費 | 50 |
| H1 | 予測売上 | =2.2*G1+83.5 |
近似式をセルで使う方法
グラフに y＝2.2x＋83.5 と表示された場合、xに広告費を入れることで推定売上を求められます。
たとえばG1に50を入力し、H1へ次の数式を入力します。
=2.2*G1+83.5
Excelの数式では、掛け算にアスタリスク記号を使います。
この例では、広告費50に対する推定売上が計算されます。
ただし、グラフ上の式は表示桁数が丸められていることがあります。
精度が必要な予測では、グラフに見えている丸め後の式だけでなく、LINEST関数やFORECAST.LINEAR関数の利用も検討しましょう。
FORECAST.LINEAR関数の利用
線形回帰による予測値を直接求めたい場合は、FORECAST.LINEAR関数が便利です。
G1に予測したい広告費を入力し、H1に次の数式を入力します。
=FORECAST.LINEAR(G1,C2:C5,B2:B5)
最初の引数G1は予測したいxの値です。
2つ目のC2:C5は既知のyの範囲、3つ目のB2:B5は既知のxの範囲です。
この関数を使うと、グラフ上へ表示される式の小数点以下が省略されていても、元データを基にした計算結果を得られます。
入力後にH1の右下にあるフィルハンドルを下へドラッグすれば、G列に複数の予測用広告費を並べたときも計算式をコピーできます。
表示位置と予測期間の調整
グラフ内の数式やR²がデータ点と重なる場合は、表示されたテキストボックスをドラッグして空白部分へ移動できます。
近似曲線の書式設定では、予測の前方補外や後方補外の期間を指定することも可能です。
前方補外を設定すると、最後のデータ点より先まで近似曲線を伸ばせます。
将来の見込みを視覚化する際に便利ですが、データ範囲から離れるほど予測の不確実性は大きくなります。
近似曲線をデータの外側へ延長した部分は、実績ではなく統計的な推定として扱いましょう。
【操作のポイント】予測値の計算には、グラフの表示式よりもFORECAST.LINEAR関数を使うと精度を保ちやすくなります。
まとめ エクセルの近似曲線の追加方法
Excelのグラフに近似曲線を追加する方法では、まず横軸と縦軸の数値関係を表しやすい散布図を作成することが基本です。
グラフ要素のプラス記号、またはデータ系列の右クリックから近似曲線を追加できます。
線形近似曲線は、一定の割合で増減するデータの傾向を確認する際に最初に選びやすい種類です。
近似曲線の書式設定でグラフに数式を表示するチェックをオンにすれば、y＝ax＋bの形で推定式を確認できます。
グラフにR二乗値を表示する設定もオンにすると、近似式がデータへどの程度当てはまっているかを確認できます。
相関の向きまで確認したい場合は、CORREL関数で相関係数を求めるとよいでしょう。
R²は近似の当てはまり、相関係数は関係の方向と強さを確認するための指標です。
多項式、指数、対数、移動平均なども利用できますが、数値をきれいに合わせるだけではなく、データの背景に合う種類を選ぶことが大切です。
近似式から予測値を計算する場合は、表示桁数による誤差を避けるため、FORECAST.LINEAR関数の活用もおすすめです。
近似曲線は、データの傾向を見つけるための有効な補助線です。
グラフ、数式、R²、相関係数を組み合わせ、根拠のあるデータ分析へつなげましょう。
