エクセルで散布図や折れ線グラフを作成したあと、データの傾向を数式として確認したい場面は少なくありません。
近似曲線を追加すると、売上の推移、実験結果、需要予測などをグラフ上で視覚的に読み取りやすくなります。
ただし、式を表示したのに小数点以下が丸められる、R2乗値が表示されない、表示された式をそのまま計算に使えないといった悩みも起こりがちです。
近似曲線の式は、グラフを選択して近似曲線を追加し、グラフに数式を表示する設定を有効にすると表示できます。
桁数は近似曲線ラベルの表示形式から調整できます。
R2乗値は、近似の当てはまり具合を確認するための指標です。
この記事では、エクセルの近似曲線の式を表示する方法から、有効数字や小数点以下の桁数を整える手順、R2乗値の見方までを解説します。
サンプルデータは、1行目に見出しがある表を前提として進めます。
エクセルで近似曲線の式を表示する方法
| 月 | 広告費 | 売上 |
|---|---|---|
| 4月 | 10 | 120 |
| 5月 | 20 | 155 |
| 6月 | 30 | 189 |
| 7月 | 40 | 228 |
それではまず、散布図に近似曲線と数式を表示する基本操作について解説していきます。
横軸に説明変数、縦軸に結果として確認したい数値を置くと、回帰式の意味を読み取りやすくなります。
散布図の作成手順
近似曲線の式を正しく使うには、データの種類に合ったグラフを選ぶことが重要です。
広告費と売上、気温と売上個数、時間と測定値のように、二つの数値の関係を扱う場合は散布図が基本になります。
上の表では、広告費を横軸、売上を縦軸に設定します。
まず、見出しを含めてB1からC5までの範囲を選択しましょう。
次に、挿入タブのグラフグループから散布図を選び、マーカーのみが表示される散布図をクリックします。
散布図は横軸を数値として扱うため、数値どうしの関係を示す近似曲線に向いています。
折れ線グラフでも近似曲線を追加できますが、横軸が月名やカテゴリとして認識されることがあり、数式の解釈に注意が必要です。
月ごとの時系列を見せる目的なら折れ線グラフ、変数間の関係を分析する目的なら散布図という使い分けが実務的です。
散布図が作成できたら、横軸と縦軸の値が意図どおりかを確認してください。
軸の指定が逆になっていると、表示される式のxとyの意味も逆になります。
近似曲線の追加操作
続いて、グラフに近似曲線を追加する操作を確認していきます。
散布図内の青い点のいずれかをクリックし、データ系列全体を選択します。
選択した状態で右クリックし、表示されたメニューから近似曲線の追加を選びます。
画面右側に近似曲線の書式設定ペインが開きます。
近似または回帰の種類では、まず線形を選択するとよいでしょう。
線形近似は、データがおおむね直線的に増減する場合に使う代表的な方法です。
指数、対数、多項式、べき乗、移動平均なども選べますが、見た目だけで種類を決めるのは避けましょう。
最初は線形近似を試し、データの性質とR2乗値を確認してから別の近似方法を検討する流れが分かりやすいです。
たとえば、一定の割合で増加するデータには指数近似、曲線的な山や谷があるデータには多項式近似が候補になります。
ただし、複雑な近似式ほど将来予測が正確になるとは限りません。
データ数が少ない場合は、曲線が偶然のばらつきまで拾ってしまうことがあります。
数式とR2乗値の表示設定
近似曲線を追加しただけでは、グラフ内に数式が表示されないことがあります。
近似曲線の書式設定ペインを下へスクロールし、グラフに数式を表示するにチェックを入れます。
同じ場所にあるグラフにR2乗値を表示するにもチェックを入れましょう。
すると、グラフ上にy = 3.6x + 83.2のような式と、R² = 0.98のような値が表示されます。
線形近似式は、一般に y = ax + b の形で表示されます。
a は傾きで、x が1増えたときに y がどの程度変わるかを示します。
b は切片で、x が0のときの推定値です。
数式のxは横軸、yは縦軸に対応するため、グラフ作成前に列の役割を決めておくことが大切です。
表示された数式はグラフ内のラベルなので、セルの数式とは別の要素です。
そのため、グラフ上の式をコピーしてセル計算に使う場合は、表示桁数が十分かを次の見出しで確認する必要があります。
【操作のポイント】散布図のデータ系列を右クリックして近似曲線を追加し、書式設定ペインで数式とR2乗値の表示を有効にします。
近似曲線ラベルの桁数と有効数字
| 広告費 x | 売上 y | 表示式の例 |
|---|---|---|
| 10 | 120 | y = 3.6x + 83.2 |
| 20 | 155 | y = 3.6x + 83.2 |
| 30 | 189 | y = 3.6x + 83.2 |
続いては、近似曲線の式に表示される小数点以下の桁数と有効数字を整える方法を確認していきます。
見やすい式と、計算に利用できる精度の式は必ずしも同じではありません。
数式ラベルの表示形式
グラフ上に表示された近似曲線の式をクリックすると、数式ラベルだけを選択できます。
数式ラベルを右クリックし、近似曲線ラベルの書式設定を選択しましょう。
書式設定ペインの表示形式を開くと、数値の種類や小数点以下の桁数を変更できます。
標準のままでは、係数や切片が少ない桁数で丸められて表示される場合があります。
グラフ上の近似式を別のセルで計算に使うなら、小数点以下の表示桁数を増やしてから転記することが必要です。
カテゴリで数値を選択し、小数点以下の桁数を3桁、4桁、または必要な精度に合わせて指定します。
分析用の資料では、係数を小数第3位程度まで表示すると読みやすいことがあります。
一方で、製造データや研究データでは、測定精度に合わせてさらに多くの桁が必要になるかもしれません。
表示形式だけを変えても、元データや内部計算の精度が上がるわけではありません。
あくまで、保持されている計算結果をどこまで画面に見せるかを変える設定です。
有効数字の考え方
有効数字とは、数値の信頼性を持つ桁をどこまで扱うかという考え方です。
近似式の係数を必要以上に細かく表示すると、正確そうに見えても、元データの測定誤差を超えた意味を持たないことがあります。
たとえば広告費を千円単位、売上を万円単位で入力しているなら、係数を小数第10位まで示す必要は通常ありません。
反対に、表示が y = 4x + 80 のように粗すぎると、元の近似結果と計算結果の差が大きくなる場合があります。
資料で傾向を説明する場合は、読みやすさを優先して小数第2位から第3位程度に整える方法があります。
近似式を後続の計算に使う場合は、グラフの表示値ではなく、LINEST関数などで係数をセルに求める方法も有効です。
有効数字は多ければよいのではなく、データの単位、測定精度、利用目的に合っていることが重要です。
社内報告用のグラフでは、式を簡潔にして読み手の負担を減らす判断もあります。
予測モデルとして利用するなら、グラフ表示用と計算用の係数を分けて管理すると安心です。
指数表示と単位の調整
係数が非常に小さい、または非常に大きい場合、近似曲線の式がE表記になることがあります。
たとえば 1.23E-05 は、1.23 × 10のマイナス5乗を表します。
この表示は計算上の異常ではなく、指数形式による通常の表示です。
読み手に伝わりにくい場合は、元データの単位を変えると式を見やすくできます。
売上を円で入力しているなら千円や万円に、時間を秒で入力しているなら分や時間に換算する方法があります。
xとyの単位を変更すると、近似式の係数と切片も変わるため、グラフの軸ラベルに単位を明記しましょう。
たとえば売上を円から万円へ変えると、縦軸の数値が10000分の1になり、式の係数も対応して変化します。
式だけを見比べるのではなく、軸の単位とデータ範囲も同時に確認することが欠かせません。
数式ラベルを移動してデータ点と重ならない場所に置けば、視認性も改善できます。
【操作のポイント】近似曲線ラベルを右クリックし、表示形式から小数点以下の桁数を調整します。
R2乗値の意味と判断基準
| R2乗値の例 | 近似の状態 | 確認の方向性 |
|---|---|---|
| 0.98 | 直線に近い傾向 | 外れ値と用途を確認 |
| 0.65 | ばらつきがある傾向 | 別要因や別モデルを検討 |
| 0.20 | 関係が弱い可能性 | グラフとデータを再確認 |
続いては、R2乗値が何を表すのか、近似曲線の評価でどう使うのかを確認していきます。
R2乗値だけで結論を出さず、散布図の形やデータの背景と合わせて読むことが大切です。
R2乗値の基本的な意味
R2乗値は決定係数とも呼ばれ、近似曲線が元データのばらつきをどの程度説明できているかを示す指標です。
一般に、R2乗値は0から1の範囲で表示されます。
1に近いほど、選んだ近似式がデータに近い形で当てはまっていると読めます。
0に近いほど、その式だけではデータの変動を十分に説明できていない可能性があります。
R2乗値が高いことは、二つの数値の因果関係を自動的に証明するものではありません。
広告費と売上のR2乗値が高くても、季節要因、価格改定、キャンペーン内容など別の要因が影響している場合があります。
R2乗値は、あくまで指定したデータと近似モデルの適合度を確認するための数値です。
グラフ上の点が近似曲線の周辺にどのように分布しているかも見てください。
一部の点だけが大きく離れている場合は、入力ミスや特殊な要因が隠れていることがあります。
R2乗値が高い場合の注意点
R2乗値が0.95や0.99のように高い場合、近似式はデータの傾向をよく表しているように見えます。
しかし、データ数が少ないときや、多項式の次数を高くしたときは注意が必要です。
多項式近似では、次数を増やすほど既存データに曲線を合わせやすくなり、R2乗値も高くなりやすい傾向があります。
その一方で、範囲外の値を予測すると不自然に大きな数値になることがあります。
既存データへの当てはまりを重視する評価と、将来の値を予測する評価は別に考えます。
グラフの範囲を大きく超えた予測は、R2乗値が高くても慎重に扱いましょう。
特に多項式近似での外挿は、グラフの見た目以上に誤差が大きくなることがあります。
高いR2乗値を見たら、まずデータ件数、外れ値、近似の種類、利用範囲を確認する習慣を持つとよいでしょう。
分析の目的が説明なのか予測なのかによっても、適した近似方法は変わります。
R2乗値が低い場合の確認事項
R2乗値が低いからといって、データが誤っているとは限りません。
現実の業務データには、複数の要因、偶発的な変動、季節性などが含まれるためです。
まずは、横軸と縦軸の列を正しく指定しているか確認しましょう。
数値として見えるセルに文字列が混ざっていると、グラフや集計の結果に影響することがあります。
次に、単純な線形近似ではなく、指数近似や対数近似がデータの性質に合うかを検討します。
R2乗値を改善するためだけに近似の種類を選ぶのではなく、業務上の理由を説明できるモデルを選ぶことが重要です。
たとえば累積値を扱うデータなら増加の形が直線にならないことがあります。
また、季節ごとの変動が強い売上データは、一つの近似曲線だけで説明しにくい場合があります。
必要なら期間を分ける、別の説明変数を用意する、外れ値の理由を調べるといった対応を進めましょう。
【操作のポイント】R2乗値は1に近いほど適合度が高い指標ですが、因果関係や将来予測の確実性を保証する数値ではありません。
近似曲線の種類と選択方法
| 近似曲線 | データの特徴 | 式の例 |
|---|---|---|
| 線形 | 一定量ずつ増減 | y = ax + b |
| 指数 | 増加率が一定に近い | y = beのax乗 |
| 多項式 | 曲がりや山谷がある | y = axの二乗 + bx + c |
続いては、近似曲線の種類と、データに合わせた選択方法を確認していきます。
ここでは近似曲線の追加画面を、Excelの操作画面に近いHTMLイメージで示します。
線形近似の利用場面
線形近似は、xが増えるとyもおおむね一定の量で増える、または減るデータに適しています。
広告費と売上がほぼ直線的に伸びる場合や、作業時間と処理件数の関係を確認する場合に使いやすい方法です。
式は y = ax + b の形で表示されます。
aが正なら右上がり、負なら右下がりの関係です。
まず線形近似を表示して散布図を確認すると、データの全体像をつかみやすくなります。
線形近似は説明しやすく、係数の意味も比較的理解しやすい点が利点です。
ただし、明らかな曲線傾向があるデータに無理に当てはめると、R2乗値が低くなったり、残差に偏りが出たりします。
グラフの左右で近似線から外れる方向がそろっている場合は、別の近似曲線を試す余地があります。
指数近似と対数近似の特徴
指数近似は、値が増えるほど増加量も大きくなるようなデータに向いています。
複利的な増加、初期段階の利用者数の伸び、菌の増殖のような現象が代表例です。
一方で対数近似は、初めは大きく変化し、その後は変化が緩やかになるデータで候補になります。
学習回数と作業時間の短縮、広告接触回数と反応の伸びなどで見られることがあります。
指数近似と対数近似では、0や負の値を含むデータに制約があります。
近似曲線が追加できない場合は、横軸または縦軸に0以下の値が含まれていないか確認してください。
近似種類の選択では、数式の形だけでなく、業務上その増減パターンに説明が付くかを確認します。
たとえば売上が急増しているから指数近似が正しいとは限りません。
期間限定の施策や在庫切れなど、別の事情が数値を動かしている可能性もあります。
多項式近似の次数設定
多項式近似は、データに曲がりや山、谷がある場合に利用できます。
近似曲線の書式設定ペインで多項式を選ぶと、次数を指定できます。
2次なら放物線に近い形、3次以上なら曲がり方がさらに複雑になります。
次数を上げるほどグラフ上の点に近づく場合がありますが、式は長くなり、解釈も難しくなります。
多項式近似の次数は、R2乗値を上げるためだけに高くせず、必要最小限にする考え方が基本です。
少ないデータに高次の多項式を使うと、データ点の間で不自然に波打つ曲線になることがあります。
特に将来値を出す目的では、元データの範囲内でのみ参考値として扱う慎重さが必要です。
資料に載せる場合は、近似の種類と次数を注記すると、読み手が判断しやすくなります。
【操作のポイント】近似曲線の種類は見た目だけで決めず、データの増減理由、R2乗値、予測に使う範囲を合わせて判断します。
セル関数による近似式と予測値
| A列 | B列 | C列 |
|---|---|---|
| 広告費 | 実績売上 | 予測売上 |
| 10 | 120 | =FORECAST.LINEAR(A2,$B$2:$B$5,$A$2:$A$5) |
| 20 | 155 | オートフィル |
| 30 | 189 | オートフィル |
続いては、グラフに表示した近似式をセル上の計算や予測値の作成に活用する方法を確認していきます。
グラフラベルの丸め誤差を避けたい場合は、関数で計算する方法が便利です。
FORECAST.LINEAR関数による予測
FORECAST.LINEAR関数は、既知のxとyのデータから線形予測値を返す関数です。
今回の例で、C2に予測売上を表示するなら次の数式を入力します。
=FORECAST.LINEAR(A2,$B$2:$B$5,$A$2:$A$5)
第1引数のA2は、予測したい広告費です。
第2引数のB2からB5は既知の売上、第3引数のA2からA5は既知の広告費を指定します。
範囲指定には絶対参照を付けるため、ドル記号を入力します。
1行目がヘッダーの場合、実際の数値範囲は2行目から指定する点に注意してください。
C2に数式を入力したら、セル右下のフィルハンドルを下へドラッグしてオートフィルします。
各行の広告費に対応した線形予測値が表示されます。
実績売上と予測売上を並べると、近似式がどの程度実績に近いかを表でも確認できます。
SLOPE関数とINTERCEPT関数の利用
線形近似式の傾きと切片を別々のセルで求めたい場合は、SLOPE関数とINTERCEPT関数を使います。
傾きを求める数式は次のとおりです。
=SLOPE($B$2:$B$5,$A$2:$A$5)
切片を求める数式は次のとおりです。
=INTERCEPT($B$2:$B$5,$A$2:$A$5)
SLOPE関数の結果をE2、INTERCEPT関数の結果をE3に置いたとします。
広告費がA2にあるとき、近似式による予測値は = $E$2*A2+$E$3 のように計算できます。
この方法なら、グラフに表示される丸め後の係数ではなく、セルが保持する計算精度で予測値を計算できます。
係数セルには分かりやすく傾き、切片といった見出しを付けておくと、後で数式を確認しやすくなります。
グラフで傾向を見せ、セルで正確な計算を行う役割分担が実用的です。
LINEST関数と決定係数の確認
より詳細に回帰分析の結果を確認したい場合は、LINEST関数を利用できます。
LINEST関数は、線形回帰の係数を配列として返す関数です。
Microsoft 365などの動的配列に対応したエクセルでは、結果が複数セルに自動展開されることがあります。
基本的な式は次の形です。
=LINEST($B$2:$B$5,$A$2:$A$5,TRUE,TRUE)
最後のTRUEを指定すると、決定係数や標準誤差などの統計情報も取得できます。
ただし、展開される数値の位置には意味があるため、利用前に関数の結果構成を確認してください。
単純にR2乗値だけをセルで求める場合は、RSQ関数も使えます。
=RSQ($B$2:$B$5,$A$2:$A$5)
グラフのR2乗値とRSQ関数の結果を比べると、同じ線形関係を評価しているか確認できます。
近似曲線の種類が線形以外の場合は、関数での再現方法が異なるため、グラフ上の式と計算方法の対応を慎重に確認しましょう。
【操作のポイント】予測計算にはFORECAST.LINEAR関数、係数の取得にはSLOPE関数とINTERCEPT関数、決定係数の確認にはRSQ関数を使えます。
まとめ エクセルの近似曲線の式を表示する方法（R2乗値・有効数字・桁数）
エクセルで近似曲線の式を表示するには、散布図などのグラフを作成し、データ系列から近似曲線を追加します。
近似曲線の書式設定ペインでグラフに数式を表示する、グラフにR2乗値を表示するのチェックを有効にすると、グラフ内に式と決定係数を表示できます。
近似曲線のラベルを右クリックして表示形式を変更すれば、係数やR2乗値の小数点以下の桁数を整えられます。
グラフに表示される数式は読みやすさを優先した表示なので、実際の計算にはセル関数で取得した係数や予測値を使うと安心です。
R2乗値は近似式の当てはまりを確認する目安ですが、高い値だけで因果関係や予測精度を断定することはできません。
線形、指数、対数、多項式などの近似曲線は、データの形と業務上の意味を踏まえて選びましょう。
FORECAST.LINEAR関数、SLOPE関数、INTERCEPT関数、RSQ関数を使えば、グラフで見つけた傾向をセル上の計算にも生かせます。
桁数、有効数字、R2乗値を適切に扱い、エクセルの近似曲線を分かりやすい分析と実務的な予測に役立ててください。
