【Excel】エクセルの近似曲線の式が合わない原因と対処法（多項式近似・指数近似・誤差範囲）

Excelで散布図に近似曲線を追加したのに、表示された数式で計算するとグラフ上の線と一致しない場合があります。

多項式近似や指数近似を選んでも予測値がずれると、入力ミスなのか、近似曲線の種類が不適切なのか判断しにくいところです。

近似曲線は元データを完全に再現する線ではなく、データ全体の傾向を数式として近づけるための機能です。

この記事では、エクセルの近似曲線の式が合わない主な原因と、散布図で正しく検証する方法を詳しく解説していきます。

タイトルにある多項式近似、指数近似、誤差範囲についても、数式とサンプルデータを使って確認しましょう。

 

エクセルの近似曲線の式を一致させる確認方法

それではまず、近似曲線の式とグラフが合わないと感じたときに最初に行う確認方法について解説していきます。

月	広告費	売上	予測値
1	10	42	確認前
2	20	63	確認前
3	30	91	確認前
4	40	126	確認前

近似曲線は、個々の点を必ず通過する直線や曲線ではありません。

まずは表示された式を正しく読み取り、同じX値を代入した計算結果を比較することが基本です。

 

表示桁数を増やす設定

近似曲線の式が合わない原因として特に多いのが、グラフに表示される係数の桁数が少ないことです。

たとえば本来は y = 2.34678x + 18.91234 であっても、グラフ上では y = 2.3x + 18.9 のように丸めて表示されることがあります。

この丸められた式をセルに入力して計算すると、元の近似曲線から少しずつ離れていきます。

係数が大きい場合やX値の範囲が広い場合は、8桁から10桁程度まで表示すると誤差を抑えやすくなります。

グラフで見やすい式と、セルで再計算するための高精度な式は分けて考えることが大切です。

 

散布図とX値の参照範囲

近似曲線の式を利用するなら、グラフの種類は原則として散布図を選びます。

折れ線グラフでは、横軸が数値ではなく項目として扱われることがあります。

たとえば広告費が10、20、30、40であっても、折れ線グラフでは1番目、2番目、3番目、4番目の位置として近似される可能性があります。

その状態で広告費の数値をX値として式に代入すると、グラフ内部で使われたX値とセルで計算したX値が異なり、式が合わないように見えます。

グラフを選択して「グラフの種類の変更」を開き、「散布図」の中からマーカー付き散布図を選択しましょう。

さらに「データの選択」から系列を編集し、Xの値が広告費列、Yの値が売上列になっているか確認します。

 

式の再計算による検証

数式の正しさは、グラフだけでなくワークシート上に予測値列を作って検証できます。

たとえば近似式が y = 2.34678x + 18.91234 で、広告費がB列、売上がC列に入力されている場合、D2に次の式を入力します。

1行目を見出し行とする場合、D2に式を入力した後、セル右下のフィルハンドルを下へドラッグしてオートフィルします。

予測値と実測値の差は、E2に次の式を入力すると確認できます。

差がゼロにならなくても異常とは限りません。

近似曲線は残差を含む予測モデルであり、実測値との差が小さく、データ全体の傾向を説明できているかを確認するものです。

 

近似曲線の種類とデータ形状の選び方

続いては、直線近似、多項式近似、指数近似を使い分ける考え方を確認していきます。

X値	Y値	傾向の例	候補
1	12	ほぼ一定に増加	線形
2	18	増加量が拡大	多項式
3	31	倍率的に増加	指数
4	55	急激な増加	指数

近似曲線の種類は、見栄えではなくデータが示す変化の仕方で選択します。

 

線形近似と一定の増減

線形近似は y = ax + b の形で表され、Xが1増えたときにYがほぼ一定量だけ増減するデータに適しています。

単価と数量から計算される金額、時間に対する一定速度の移動距離などは、線形近似で傾向を捉えやすい例です。

ただし実際の売上、温度変化、成長率のように増え方そのものが変化するデータでは、直線近似だけでは曲がり方を表せません。

直線で無理に近似すると、中央では合っていても両端で大きく外れることがあります。

R2乗値が低い場合は、係数の小数点以下を増やす前に、近似の種類を見直すほうが合理的です。

 

多項式近似と次数の設定

多項式近似は、曲線的に増減するデータを y = ax² + bx + c や y = ax³ + bx² + cx + d のような式で近似する方法です。

二次式は山型や谷型の傾向、三次式以上は変曲点を含む複雑な傾向を表現できます。

たとえばグラフに y = 0.1256x² + 1.8421x + 9.7634 と表示された場合は、D2に次のように入力します。

多項式の次数を上げるほど、元データに近い曲線を作りやすくなります。

しかし次数を必要以上に高くすると、たまたま生じたばらつきまで拾ってしまい、将来の予測に弱い式になることがあります。

まずは2次、必要に応じて3次を試し、データの意味を説明できる範囲で次数を抑えることが実務的です。

 

指数近似とゼロ以下の値

指数近似は y = ab^x の形で表され、一定量ではなく一定の割合で増加または減少するデータに向いています。

複利で増える残高、初期段階の利用者数、減衰する濃度などでは、指数近似が候補になります。

Excelの指数近似では、Y値に0または負の値が含まれると計算できない、あるいは期待した線にならないことがあります。

これは指数関数の計算過程で対数を利用するためです。

変換したデータで求めた式は、そのまま元の単位の予測式にはなりません。

変換前後の関係を記録し、分析目的に対して指数近似が本当に適切かを優先して判断する必要があります。

 

多項式近似の数式と予測値の作り方

続いては、多項式近似の数式をセルへ入力し、オートフィルで予測値を作る方法を確認していきます。

月	来客数	売上	二次式の予測値	残差
1	20	35	数式を入力	数式を入力
2	40	72	オートフィル	オートフィル
3	60	121	オートフィル	オートフィル
4	80	181	オートフィル	オートフィル

多項式近似では、掛け算、べき乗、符号の位置を正確に入力する必要があります。

 

二次式をセルに入力する手順

散布図を作成し、「グラフ要素」から「近似曲線」を選択した後、「その他のオプション」で「多項式近似」を指定します。

次数を2にし、「グラフに数式を表示する」にチェックを入れると二次式が表示されます。

仮に表示式が y = 0.0184x² + 1.2567x + 4.8921 なら、予測値列の先頭セルに次の式を入力します。

B2はX値が入ったセルです。

Excelの数式では x² のような上付き文字は使えないため、べき乗演算子の ^ を使って B2^2 と入力します。

係数の前後にあるプラスとマイナスを取り違えると、曲線の向きまで変わるため注意が必要です。

 

オートフィルと絶対参照の使い分け

係数を数式内へ直接入力すると、D2の式を下方向へコピーするだけで各行の予測値を計算できます。

一方、係数をG2からI2などの別セルへ管理したい場合は、コピー時に係数セルがずれないよう絶対参照を使用します。

G2にa、H2にb、I2にcを入力し、B列をX値とするなら、D2には次の式を入力します。

$G$2のように列と行の前へ$を付けると、下へオートフィルしても係数の参照先を固定できます。

B2には$を付けないため、D3ではB3、D4ではB4を参照します。

数式を入力後、D2右下のフィルハンドルを最終行までドラッグすると予測値列が完成します。

 

残差と誤差率の計算

近似式の良し悪しは、予測値だけでなく残差を確認して判断します。

残差とは実測値から予測値を引いた差であり、売上がC列、予測値がD列ならE2には次の式を入力します。

さらに実測値に対する誤差率を確認するなら、F2に =IFERROR(ABS(E2/C2),0) を入力し、パーセント表示に設定します。

残差が常にプラス、または常にマイナスに偏っている場合、曲線の種類や対象範囲が適切でない可能性があります。

残差が上下にランダムに散らばる状態は、少なくとも特定方向への偏りが少ないという判断材料になります。

 

指数近似の設定と誤差範囲の確認

続いては、指数近似を設定する方法と、R2乗値や誤差範囲を確認する手順を解説していきます。

日数	登録数	指数近似の予測値	差
1	15	数式を入力	数式を入力
2	23	オートフィル	オートフィル
3	36	オートフィル	オートフィル
4	57	オートフィル	オートフィル

指数近似は、値が急増または急減するように見えるデータで効果を確認しやすい方法です。

 

指数近似曲線の追加手順

散布図のデータ系列をクリックし、グラフ右上の「＋」を選択して「近似曲線」の矢印を押します。

「その他のオプション」を開き、近似曲線の種類から「指数近似」を選択します。

同じ設定画面で「グラフに数式を表示する」と「グラフにR-2乗値を表示する」にチェックを入れましょう。

この時点で曲線が表示されない場合は、Y値に0以下の値や空白、文字列が混ざっていないかを確認します。

たとえばX値がB2なら、予測値の式は次の形です。

e^0.2743x を 0.2743^x と入力しないことが重要です。

 

R2乗値の読み方

R2乗値は、近似曲線がデータのばらつきをどの程度説明できているかを見るための指標です。

一般に1へ近いほど近似度は高くなりますが、R2乗値だけで最適なモデルと断定することはできません。

高次の多項式はR2乗値を上げやすい一方、未入力の将来データに対する予測精度が高いとは限りません。

R2乗値は近似の手掛かりであり、業務上の因果関係やデータの取得条件と組み合わせて判断します。

複数の近似曲線を比較する場合は、同じデータ範囲、同じ外れ値の扱いでR2乗値と残差を見比べることが必要です。

 

誤差範囲と外れ値の扱い

誤差範囲を確認するには、実測値、予測値、残差、誤差率を並べて表にします。

たとえば誤差率が数パーセントに収まっていても、売上規模が大きければ金額として無視できない差になるかもしれません。

逆に少数の大きな誤差があっても、キャンペーン実施日や集計漏れなど明確な理由があるケースもあります。

外れ値を削除する前には、入力ミスなのか、実際に起きた重要な事象なのかを必ず確認しましょう。

都合の悪い値を除外して式をきれいにするのではなく、除外理由を説明できる状態にすることが分析の信頼性につながります。

 

近似曲線の式が合わない場合の見直し項目

続いては、設定を見直しても式が合わない場合に確認したい項目を整理していきます。

確認項目	起こりやすい問題	確認方法
表示桁数	係数の丸め誤差	数値書式を増やす
データ範囲	見出しや空白を含む	系列を編集する
X値	項目軸として処理される	散布図にする
切片	ゼロ固定の影響	設定を解除して比較する

一つの原因だけとは限らないため、設定とデータを順に見直すことが近道です。

 

切片設定とゼロ固定

近似曲線のオプションには、「切片を設定する」という項目があります。

ここへ0を入力すると、曲線は必ずY軸の0を通るように調整されます。

売上ゼロなら広告費もゼロ、距離ゼロなら時間もゼロのように、理論上の根拠がある場合には有効な設定です。

ただし、固定費や初期値があるデータでは、切片を0に固定すると本来の傾向から不自然にずれることがあります。

切片のゼロ固定は式を単純にする設定ではなく、データの前提を強く指定する設定です。

式が合わなくなったと感じた場合は、切片設定を解除した近似曲線と並べて比較しましょう。

 

空白セルと文字列データ

数値列に空白、ハイフン、文字列、エラー値が混ざると、グラフの系列範囲と想定したデータ範囲に差が出ることがあります。

特に「－」を未入力の意味で使っている表では、見た目は整っていても数値として計算されません。

数値の有無を確認するには、対象範囲を選択してステータスバーの件数や平均を見たり、ISNUMBER関数を使ったりする方法があります。

結果がFALSEなら、そのセルは数値として扱われていません。

また、CSVから貼り付けた数値に余分な空白が付いている場合もあります。

VALUE関数、TRIM関数、区切り位置などを状況に応じて利用し、近似に使う列を数値へ整えましょう。

 

予測範囲を広げすぎるリスク

近似曲線の数式は、元データの範囲内で傾向を見るためには便利です。

しかし、元データが1から10までしかないのに、式へ100を代入して将来予測するような使い方には注意が必要です。

多項式近似は範囲外で急上昇または急降下しやすく、指数近似も小さな係数差が大きな予測差につながります。

近似曲線による範囲外の予測は外挿と呼ばれ、グラフ上で合って見えても不確実性が高くなります。

予測する場合は、過去データを学習用と検証用に分ける、業務上の上限値を確認するなど、式以外の条件も加味しましょう。

 

まとめ エクセルの誤差範囲・指数近似・多項式近似の式が合わない原因と対処法

エクセルの近似曲線の式が合わないときは、まず近似曲線ラベルの表示桁数を増やし、丸められていない係数でセル計算を行いましょう。

散布図を使い、グラフで利用しているX値と、セルの数式に代入するX値をそろえることも重要です。

直線近似は増減量がほぼ一定のデータ、多項式近似は曲線的な変化、指数近似は倍率的な増減を示すデータで検討します。

多項式近似では次数を上げすぎず、指数近似ではY値が正の数値になっているかを確認することがポイントです。

予測値、残差、誤差率をワークシートに作成し、R2乗値だけでなく誤差の偏りや外れ値の理由まで確認しましょう。

近似曲線は完全一致のための式ではなく、データの傾向を読み取り、説明や予測に役立てるための道具です。

数式の表示精度とデータ条件を整えたうえで、目的に合う近似方法を選んで活用していきましょう。