【Excel】エクセルで近似式を求める方法・出し方（関数・e）

Excelで散布図を作成したとき、データの傾向を数式として表したい場面があります。

近似式を求める方法には、グラフの近似曲線を使う方法、LINEST関数などで係数を計算する方法、指数関数のeを使って予測値を出す方法があります。

最も手軽なのは散布図に近似曲線を追加して数式を表示する方法ですが、セルに計算式を残したい場合は関数を使う方法が便利です。

この記事では、サンプルデータの1行目に見出しがある前提で、近似式の出し方と使い分けを詳しく解説します。

 

グラフの近似曲線から近似式を表示する方法

A列	B列
広告費	売上
10	125
20	168
30	211
40	246

それではまず、グラフ上に近似式を表示する方法について解説していきます。

データを視覚的に確認しながら式を出せるため、初めて近似式を扱う場合にも分かりやすい手順です。

 

散布図の作成手順

まずA1からB5までの範囲を選択し、Excel上部の挿入タブから散布図を選択しましょう。

データが横方向と縦方向の数値の組み合わせである場合、散布図はXとYの関係を確認するために適したグラフです。

直線に近い並びなら線形近似、増え方が急になるなら指数近似や多項式近似を検討します。

見出しである広告費と売上は、通常は系列名として扱われます。

もし横軸と縦軸が意図した列と逆になったときは、グラフを右クリックしてデータの選択を開き、系列のXの値とYの値を確認してください。

この段階で元の表に空白行、文字列、エラー値が混じっていると近似の精度に影響することがあります。

数値データだけを連続した範囲に整えることが、正しい近似式への第一歩です。

 

近似曲線と数式の追加操作

作成した散布図の点をクリックし、右クリックメニューから近似曲線の追加を選択します。

右側に近似曲線の書式設定が表示されたら、線形を選ぶと一次式が追加されます。

続けて、グラフに数式を表示する、グラフにR2乗値を表示するにチェックを入れます。

表示されるyは予測した値、xは入力する値を示しています。

グラフに表示された係数は小数点以下が丸められる場合があります。

予測計算に使う場合は、グラフの式だけで判断せず、後述するLINEST関数で詳細な係数を取得する方法も確認しましょう。

 

R2乗値と近似の信頼性

R2乗値は決定係数とも呼ばれ、近似式が元データのばらつきをどの程度説明できるかを見る目安です。

1に近いほどデータが式に沿っており、0に近いほど式だけでは傾向を説明しにくい状態です。

ただし、R2乗値が高いことと、将来予測が必ず当たることは同じではありません。

特に少数のデータだけで作った近似式は、範囲外の値へ当てはめると大きく外れることがあります。

異常値が含まれると傾きや切片が変化するため、入力ミスや特別な事情による値がないかも確認してください。

 

LINEST関数による直線近似の係数計算

A列	B列	C列
広告費	売上	計算結果
10	125	=LINEST(B2:B5,A2:A5)
20

続いては、LINEST関数で直線近似の係数を求める方法を確認していきます。

グラフを作らずにセルへ傾きと切片を出したい場合や、計算表として再利用したい場合に向く方法です。

 

LINEST関数の引数と入力例

売上がB2からB5、広告費がA2からA5に入力されている場合、空いているC2セルに=LINEST(B2:B5,A2:A5)と入力します。

Microsoft 365やExcel 2021以降では、結果が横方向へ自動的に展開されます。

左側のC2に傾き、右側のD2に切片が表示される仕組みです。

古いExcelでは、結果を出したい横2セルを選択してから数式を入力し、配列数式として確定する必要がある場合があります。

既知のyには結果の列、既知のxには原因や条件となる列を指定する点が重要です。

この順番を逆にすると、求めたい近似式の向きも逆になります。

 

傾きと切片から近似式を作る方法

LINEST関数の結果がC2に4.1、D2に84.5と表示されたなら、近似式はy = 4.1x + 84.5です。

係数を使って予測値を計算する場合、E列に新しい広告費を入力し、F2に=$C$2*E2+$D$2と入力します。

その後、F2の右下にあるフィルハンドルを下へドラッグすれば、複数行の予測値を作成できます。

係数セルの参照に$を付けないと、オートフィル時に参照位置がずれてしまいます。

式をコピーする前に、C2とD2が近似係数として正しく表示されているか確認すると安心です。

 

SLOPE関数とINTERCEPT関数の使い分け

傾きだけを求めたい場合はSLOPE関数、切片だけを求めたい場合はINTERCEPT関数も使えます。

傾きは=SLOPE(B2:B5,A2:A5)、切片は=INTERCEPT(B2:B5,A2:A5)です。

LINEST関数は複数の統計情報も取得できるため、分析目的では便利ですが、単純な計算表ではSLOPE関数とINTERCEPT関数のほうが式の意味を読み取りやすいことがあります。

たとえば傾きが4.1なら、広告費が1増えるごとに売上が平均で約4.1増える関係を表します。

一方で切片84.5は、広告費を0としたときの推定値です。

実務上あり得ないx=0を表している場合もあるため、切片だけを現実の値として解釈しすぎないようにしましょう。

 

FORECAST関数とTREND関数による予測値の算出

A列	B列	C列
広告費	売上	予測売上
10	125	=FORECAST.LINEAR(A2,$B$2:$B$5,$A$2:$A$5)
20	168

続いては、関数で近似式に基づく予測値を直接出す方法を確認していきます。

係数そのものを別セルへ出さなくても、入力値に応じた推定値を計算できます。

 

FORECAST.LINEAR関数の基本

FORECAST.LINEAR関数は、既存データの線形近似を使って、指定したxに対応するyを予測する関数です。

C2に=FORECAST.LINEAR(A2,$B$2:$B$5,$A$2:$A$5)と入力すると、A2の広告費に対応する予測売上が表示されます。

実績値のB列と予測値のC列を並べることで、どの程度の差があるか比較できます。

FORECAST.LINEAR関数は内部で直線近似を行うため、傾きと切片を手作業で組み立てる必要がありません。

ただし、関数名にLINEARとあるとおり、対象は直線関係です。

増え方が曲線状のデータには、そのまま使わないほうがよい場合があります。

 

TREND関数で複数の予測値を並べる方法

TREND関数は、複数の新しいxに対する予測値をまとめて求めたいときに便利です。

たとえばE2からE6に将来の広告費を入力し、F2に=TREND($B$2:$B$5,$A$2:$A$5,E2:E6)と入力します。

Microsoft 365では、F2から下方向へ予測値が自動展開されます。

旧バージョンでは予測値を表示する範囲を先に選ぶ必要があることもあります。

予測の対象となるE列には数値だけを入れ、見出しであるE1は範囲に含めないようにしてください。

元のデータ範囲と新しいxの単位は、必ず同じに揃えます。

広告費を千円単位で入力したのか円単位で入力したのかが混在すると、結果が大きく変わるため注意が必要です。

 

予測値と実績値の差の確認

近似式で求めた予測値と実際の値の差は、残差と呼ばれます。

実績がB2、予測がC2なら、差は=D2として=B2-C2で求められます。

差の絶対値を見たい場合は=ABS(B2-C2)を使います。

残差が特定の範囲で大きく偏っている場合、直線近似の選択が適切でない可能性があります。

予測式は答えを保証するものではなく、データの傾向を数値化するための道具です。

季節要因、キャンペーン、価格変更のように過去データだけでは表せない条件も併せて確認しましょう。

 

指数近似とEXP関数によるeの計算

A列	B列	C列
経過日数	利用者数	指数予測
1	120	=係数A*EXP(係数B*A2)
2	146
3	179

続いては、増加率や減少率が一定に近いデータで使う指数近似と、eを使ったEXP関数を確認していきます。

時間の経過とともに急激に増える利用者数、複利、減衰、細菌数の変化などでは、直線より指数関数が適することがあります。

 

指数近似曲線の選択方法

散布図に近似曲線を追加する画面では、線形の代わりに指数を選択できます。

グラフに数式を表示すると、y = a e^(bx)に近い形式、またはy = a e^xのような形式で式が示されます。

指数近似は、xが増えるほどyの増え方そのものも大きくなるデータで検討します。

反対に、毎回ほぼ同じ量だけ増えるデータなら、線形近似のほうが分かりやすく適切です。

指数近似では、yの値に0以下が含まれていると扱えません。

負の値やゼロが含まれるデータでは、別の近似方法を選ぶか、データの意味を見直す必要があります。

グラフの見た目だけで決めず、R2乗値と業務上の変化の仕組みを合わせて判断しましょう。

 

LOGEST関数で指数式の係数を求める方法

指数関数の係数をセルに取り出したいときは、LOGEST関数を使います。

基本形は=LOGEST(既知のy,既知のx)です。

たとえばB2からB4に利用者数、A2からA4に経過日数があるなら、=LOGEST(B2:B4,A2:A4)を入力します。

LOGEST関数は、y = b × m^xという形の係数mとbを返します。

これをeを使う形のy = a × e^(bx)へ読み替える場合、mとeの関係を使います。

Excelで自然対数を求める関数はLN関数です。

mが1より大きければ増加型、0より大きく1より小さければ減少型の指数関数になります。

 

EXP関数で予測式をセルに入力する方法

EXP関数は、自然対数の底eを底とする指数を計算する関数です。

=EXP(1)と入力すると、eの値である約2.71828が返されます。

係数aをF2、係数bをG2、経過日数をA2に入力した場合、予測式は=$F$2*EXP($G$2*A2)です。

この式をC2に入力し、C2右下のフィルハンドルを下へドラッグすると、各日数の予測値を表示できます。

EXP関数の引数全体が指数になるため、括弧内の計算順序を意識することが大切です。

=F2*EXP(G2*A2)のように書くと、F2以外の部分だけがeの指数として計算されます。

=EXP(F2*G2*A2)と入力すると計算の意味が変わるため、係数aを外側へ置く式を守りましょう。

 

近似式の種類とデータに合う選び方

近似の種類	式の例	向く傾向
線形	y = ax + b	一定量ずつ増減
指数	y = ae^(bx)	一定割合で増減
多項式	y = ax² + bx + c	山や谷を含む曲線

続いては、近似式の種類を選ぶときの考え方を確認していきます。

データに合わない式を選ぶと、計算自体はできても予測に使いにくい結果になることがあります。

 

線形近似が向くデータの特徴

線形近似は、xが1増えるごとにyがほぼ一定量ずつ増減する関係で使います。

広告費と売上、作業時間と生産数、距離と燃料費などでは、一定の範囲で直線的な関係が見られることがあります。

式がy = ax + bと単純で、傾きの意味を説明しやすいことが線形近似の強みです。

一方で、値が増えるにつれて増加幅も大きくなるデータを無理に直線で表すと、後半で誤差が大きくなります。

散布図の点が弓なりに並ぶ場合は、指数近似や多項式近似も比較してください。

最初に線形近似を試し、残差の偏りを観察する流れが実務では分かりやすい方法です。

 

多項式近似と移動平均の扱い

多項式近似は、上昇後に下降するような山型、または下降後に上昇する谷型の傾向を表すときに使えます。

二次式ならy = ax² + bx + cのように、xの二乗を含む式になります。

Excelの近似曲線の追加画面で多項式を選び、次数を設定すると作成できます。

ただし次数を高くしすぎると、元データにはよく合う一方で新しい値への予測が不安定になることがあります。

見かけのR2乗値だけを追って複雑な式にすることは、過剰適合につながる可能性があります。

時系列の細かな揺れをならして傾向を見たいだけなら、近似式ではなく移動平均を選ぶ方法もあります。

何を説明したいのか、何を予測したいのかを先に決めることが大切です。

 

近似式を業務で使う前の確認項目

近似式を作成したら、元データの単位、対象期間、入力漏れを確認します。

売上を税込で集計した月と税抜で集計した月が混在している場合、計算結果の精度は下がります。

また、データ数が少ないと偶然の値に式が引っ張られやすくなります。

可能であれば、近似式の作成に使わなかったデータでも予測値を確かめましょう。

近似式はデータの範囲内で使うほど信頼しやすく、範囲外へ大きく延長するほど不確実性が高まります。

予測値は意思決定の補助として扱い、重要な判断では現場情報や条件の変化も加える姿勢が必要です。

 

近似式で発生しやすいエラーと修正方法

表示や症状	主な原因	確認方法
#VALUE!	数値範囲に文字列	入力形式の統一
#NUM!	指数計算の範囲外	EXPの引数確認
予測値のずれ	参照範囲のずれ	$記号の確認

続いては、近似式や関数を入力したときに起こりやすい問題を確認していきます。

エラー表示だけでなく、数式は動くものの値が不自然な場合にも、参照範囲とデータ形式を見直すことが重要です。

 

LINEST関数で結果が表示されない場合

LINEST関数で期待した結果が出ない場合は、既知のyと既知のxの行数が一致しているかを確認します。

たとえばB2:B10をyに指定したなら、xもA2:A10のように同じ件数で指定します。

片方だけ見出し行を含めたり、片方だけ最終行を含めなかったりすると正しく計算できません。

数値に見えるセルでも、先頭にアポストロフィがある文字列や、全角数字が混じっていることがあります。

セルが数値として認識されているかは、表示形式だけでなく数式バーでも確認しましょう。

空白セルが多い場合は、近似に使う連続した実データの範囲だけを指定するほうが安全です。

 

EXP関数で値が極端になる原因

EXP関数では、引数が少し大きくなるだけで結果が急激に大きくなります。

たとえば=EXP(100)のような式は非常に大きな値となり、条件によっては#NUM!エラーになることがあります。

これは関数の不具合ではなく、指数関数の性質です。

係数bに意図しない大きな数値が入っていないか、xの単位が日、月、年のどれなのかを確認してください。

日数を月数として扱うなど単位がずれると、EXP関数の結果は大幅に変わります。

必要ならxを30で割って月単位に換算するなど、元データと係数の基準を統一しましょう。

 

オートフィル後に計算結果がずれる対策

最初の行では正しいのに、下へコピーすると予測値がずれる場合は、相対参照が原因になりやすいです。

たとえば係数aがF2、係数bがG2にあるとき、予測式は=$F$2*EXP($G$2*A2)とします。

F2とG2はどの行でも同じ係数を使うため、列と行の前に$を付けて固定します。

A2は経過日数に合わせてA3、A4へ変化してほしいため、通常は固定しません。

数式をコピーする前に、固定する参照と動かす参照を区別することがオートフィルの基本です。

 

まとめ エクセルで近似式を求める方法（関数・e）

Excelで近似式を求めるとき、まず散布図を作成して近似曲線を追加すれば、式とR2乗値を簡単に表示できます。

直線的な傾向にはLINEST関数、SLOPE関数、INTERCEPT関数、FORECAST.LINEAR関数が便利です。

セル内で予測計算まで行いたい場合は、係数を求めてから絶対参照を使った数式を作成します。

増加率や減少率が一定に近いデータでは、指数近似、LOGEST関数、EXP関数を使う方法が候補になります。

eを底とする式は、y = a × e^(bx)の形で考え、Excelでは=$F$2*EXP($G$2*A2)のように入力できます。

近似式は便利ですが、データ数、異常値、単位、対象範囲によって精度が変化します。

散布図の形とR2乗値を確認し、実績値との差も見ながら、目的に合った近似式を選んでいきましょう。