Excelで散布図に近似曲線を追加したものの、表示された式と手計算の結果が一致しない、グラフの線がデータの傾向に見えないと感じることがあります。
線形近似の式が合わないときは、計算そのものの誤りだけでなく、グラフの種類、選択範囲、切片の設定、表示桁数などを順に確認することが大切です。
近似曲線の式が合わない原因は、散布図ではないグラフ、誤った系列範囲、切片固定、丸め表示に集約されることが多いです。
元データを使う計算式とグラフの設定を同じ条件にそろえることで、線形近似の式を正しく検証できます。
この記事では、エクセルの近似曲線の式が合わない原因と確認方法について、線形近似を中心に解説していきます。
エクセルで近似曲線の式を正しく表示する確認手順
それではまず、近似曲線の式を正しく表示するための基本的な確認手順について解説していきます。
| A列 月 | B列 広告費 | C列 売上 |
|---|---|---|
| 4月 | 10 | 120 |
| 5月 | 20 | 145 |
| 6月 | 30 | 166 |
| 7月 | 40 | 191 |
この例では、B列の広告費を説明変数のX、C列の売上を目的変数のYとして扱います。
近似曲線の式は、横軸に置いた値をX、縦軸に置いた値をYとして計算されます。
散布図への変更
折れ線グラフで作成した場合は、横軸が数値ではなく項目として扱われることがあるため、最初に散布図へ変更します。
散布図はX軸とY軸の両方を数値として認識するグラフであり、広告費と売上のような二つの数値の関係を調べる用途に適しています。
データ範囲B1からC5を選択し、挿入タブから散布図を選びます。
マーカーのみの散布図を選択してから近似曲線を追加すると、各データ点と直線の関係を視覚的に確認しやすくなります。
折れ線グラフは月別の推移を見るには便利ですが、横軸の間隔が数値差に連動しないため、回帰式を検証する目的では散布図を使うことが基本です。
X値とY値の系列範囲
散布図を作成した後は、グラフを右クリックしてデータの選択を開き、系列の編集画面で範囲を確認します。
系列Xの値にはB2からB5、系列Yの値にはC2からC5が入っている状態が正しい設定です。
1行目は見出しであるため、数値範囲に含めません。
見出しを含めたり、途中の空白セルを含めたりすると、グラフと関数で参照するデータがずれることがあります。
確認の目安は、X値の個数とY値の個数が同じであり、同じ行どうしが一組の観測値になっていることです。
広告費がB2なら、その売上は必ずC2という対応を保ちます。
式とR二乗値の表示
散布図内のデータ点を右クリックし、近似曲線の追加から線形を選択します。
近似曲線の書式設定では、グラフに数式を表示する項目と、グラフにR二乗値を表示する項目にチェックを入れましょう。
表示される式は一般にY＝aX＋bの形で、aは傾き、bは切片です。
R二乗値は直線がデータのばらつきをどの程度説明できるかを見る指標で、1に近いほど直線との適合度が高いと判断できます。
ただし、R二乗値が高いことと、元データの参照範囲が正しいことは別の確認項目です。
【操作のポイント】散布図にしてから、X値とY値の参照範囲、数式、R二乗値の順で確認すると原因を切り分けやすくなります。
散布図と折れ線グラフの横軸の違い
続いては、散布図と折れ線グラフで近似曲線の式が変わる理由を確認していきます。
| 比較項目 | 散布図 | 折れ線グラフ |
|---|---|---|
| 横軸の扱い | 数値 | 項目 |
| 間隔 | 数値差に応じて変化 | 等間隔 |
| 線形近似の確認 | 適する | 注意が必要 |
横軸が連番になるケース
折れ線グラフでは、横軸に10、20、30、40と入力していても、値そのものではなく一番目、二番目、三番目という順番で扱われる場合があります。
この状態で近似曲線を表示すると、手計算で広告費をXとして求めた傾きと一致しないことがあります。
データ点の間隔が異なる数値をXとして分析したいなら、横軸を連番ではなく実数として扱う必要があります。
特に日付、金額、距離、温度、時間のように値の間隔に意味があるデータでは、グラフ種類の選択が重要です。
数値軸としての確認
散布図に変更したら、横軸を右クリックして軸の書式設定を開きます。
軸の最小値や最大値、目盛間隔が数値として設定できることを確認しましょう。
広告費が10から40までなら、横軸が10、20、30、40付近に配置され、各点の水平距離も数値差に応じて変わります。
すべての点が等間隔に並ぶときは、グラフ種類または系列設定をもう一度確認する場面かもしれません。
日付データを使う場合
日付をX値に使う場合も、Excel内部では日付が連続した数値として保存されているため、散布図なら日数差を反映した近似が可能です。
ただし、月名を文字列で入力しただけのデータは、数値軸として利用できません。
月別の数値を分析したいときは、実際の日付を入力するか、1から12までの月番号を別列に用意します。
月名を表示したい場合は、分析用のX列には日付または連番を置き、表示用の月名列は別に管理すると安全です。
【操作のポイント】数値の関係を調べる線形近似では、折れ線グラフではなく散布図を選び、横軸が数値軸になっているか確認します。
近似曲線の切片設定と計算条件
続いては、切片の設定によって近似曲線の式が合わなくなるケースを確認していきます。
| 設定 | 式の形 | 用途 |
|---|---|---|
| 切片を自動計算 | Y＝aX＋b | 通常の回帰分析 |
| 切片を0に設定 | Y＝aX | 原点通過に根拠がある場合 |
切片を固定する設定
近似曲線の書式設定には、切片を設定する項目があります。
ここに0などの値が入力されていると、Excelは直線をその値で縦軸と交差させる条件で計算します。
通常のSLOPE関数やINTERCEPT関数、LINEST関数で求めた結果は切片を自動計算するため、固定された近似曲線とは一致しません。
切片を固定した回帰式と、切片を自由に求める回帰式は、同じデータでも別の計算結果になります。
原点通過にする判断
広告費が0なら売上が必ず0になるとは限らないため、広告費と売上の分析では切片を0に固定しないのが一般的です。
一方で、単価が一定の商品の数量と金額のように、理論上Xが0ならYも0となる関係では、原点通過を検討する余地があります。
ただし、現実の計測データには初期費用、基本料金、測定誤差などが含まれることがあります。
数学的に原点を通すべきか、業務上の意味から判断しましょう。
切片を固定する前に、通常のY＝aX＋bでグラフを作成し、切片が0からどの程度離れているかを確認する方法が有効です。
近似の種類の選択
近似曲線には線形のほか、指数、対数、多項式、移動平均などがあります。
線形近似を検証したいときに多項式や指数近似が選ばれていると、当然ながらSLOPE関数での結果とは合いません。
グラフ上の曲線がまっすぐではない場合は、近似曲線の種類を開いて線形が選択されているか確認します。
関数で求める計算方法とグラフで選ぶ近似の種類は、必ず同じ線形条件にそろえることが必要です。
【操作のポイント】近似曲線の書式設定で切片の値が空欄であり、種類が線形になっていることを確認します。
SLOPE関数とINTERCEPT関数による式の検証
続いては、関数を使ってグラフの近似曲線の式を確認する方法を解説していきます。
| E列 項目 | F列 数式 | 結果の意味 |
|---|---|---|
| 傾き | =SLOPE(C2:C5,B2:B5) | a |
| 切片 | =INTERCEPT(C2:C5,B2:B5) | b |
| 決定係数 | =RSQ(C2:C5,B2:B5) | R二乗 |
SLOPE関数で傾きを求める方法
SLOPE関数は、既知のYの範囲と既知のXの範囲から、線形回帰式の傾きを返す関数です。
サンプルデータでは、F2セルに次の数式を入力します。
=SLOPE(C2:C5,B2:B5)
第1引数には売上であるC2からC5を指定し、第2引数には広告費であるB2からB5を指定します。
引数の順序を逆にすると、広告費をY、売上をXとして計算する別の傾きになるため注意が必要です。
SLOPE関数では、最初にYの範囲、次にXの範囲を指定します。
INTERCEPT関数で切片を求める方法
切片を求めるには、F3セルにINTERCEPT関数を入力します。
=INTERCEPT(C2:C5,B2:B5)
この結果が、近似曲線に表示されたY＝aX＋bのbにあたる値です。
グラフ上の式がY＝2.4X＋96.5のように表示され、SLOPE関数とINTERCEPT関数の値も2.4付近と96.5付近なら、同じ範囲を使って計算できています。
わずかな違いは、グラフ上の表示桁数による丸めである可能性があります。
RSQ関数によるR二乗値の照合
近似曲線に表示されたR二乗値は、RSQ関数でも確認できます。
=RSQ(C2:C5,B2:B5)
R二乗値まで関数とグラフで一致するなら、系列範囲、近似の種類、切片条件が同一である可能性が高いといえます。
式だけでなくR二乗値も照合すると、偶然似た数値になっているだけではないかを確認できます。
【操作のポイント】SLOPE、INTERCEPT、RSQの三つを同じデータ範囲で計算し、グラフの式とR二乗値をまとめて照合します。
表示桁数とデータ品質による誤差
続いては、式が合わないように見える表示上の要因と、元データの確認方法を解説していきます。
| 確認箇所 | 起こりやすい問題 | 対応 |
|---|---|---|
| 式ラベル | 小数点以下の丸め | 表示桁数を増やす |
| セルの値 | 文字列の数値 | 数値形式へ変換 |
| データ範囲 | 空白や外れ値 | 対象を再確認 |
グラフ上の式の小数点以下
近似曲線のラベルは、既定では小数点以下の桁数が少なく表示されることがあります。
たとえば実際の傾きが2.45678でも、グラフでは2.46と表示される場合があります。
表示された2.46を使って予測値を計算すると、元の精密な係数を使ったSLOPE関数の結果とは少しずれます。
数式ラベルをクリックしてラベルの書式設定を開き、表示形式で小数点以下の桁数を増やしましょう。
グラフに見える式は計算値の一部を丸めた表示であり、内部の係数そのものではない場合があります。
数値と文字列の混在
セルに見た目は10と表示されていても、先頭にアポストロフィが付いた文字列、全角数字、空白を含む文字列になっていることがあります。
文字列が混在すると、関数やグラフが意図しないデータを除外したり、別の扱いをしたりすることがあります。
セルを選んで数式バーを確認し、表示形式を標準または数値に設定します。
警告アイコンが出ている場合は、数値に変換する操作も有効です。
COUNT関数とCOUNTA関数の結果を比べると、見た目は入力されているのに数値として数えられていないセルを見つける手掛かりになります。
空白セルと外れ値の影響
空白セルや極端に大きい値、小さい値が含まれると、回帰直線の傾きと切片は大きく変化します。
外れ値が入力ミスなら修正が必要ですが、実際に発生した値なら安易に削除せず、発生理由を確認することが重要です。
近似曲線はデータ全体に最も合う直線を求めるため、一部の大きな値にも影響を受けます。
式が合わないと感じたときは、計算結果だけでなく、グラフ上で各点がどこにあるかも確認しましょう。
【操作のポイント】表示桁数を増やし、数値形式、空白セル、外れ値を確認してから式の差を判断します。
まとめ エクセルの近似曲線の式が合わない確認方法（線形近似）
エクセルで近似曲線の式が合わないときは、まずグラフを散布図にし、横軸がX値、縦軸がY値として正しい範囲を参照しているか確認します。
線形近似では、SLOPE関数の傾き、INTERCEPT関数の切片、RSQ関数のR二乗値をグラフと照合する方法が確実です。
切片を固定している場合や、線形以外の近似曲線を選択している場合は、通常の回帰関数とは一致しません。
また、グラフに表示された小数点以下の桁数が少ないと、式が違うように見えることがあります。
確認は、散布図への変更、系列範囲の確認、切片と近似種類の確認、関数による照合、表示桁数とデータ品質の確認という順番で進めるとスムーズです。
近似曲線を単に表示するだけでなく、元データと関数の結果を合わせて確認すれば、分析結果をより安心して活用できるでしょう。
