【エクセル】線形近似式の傾き、切片を算出！VBAへの組み込みも！

エクセルを使って、データから線形近似式を求めたい。

いつも、①散布図を作成、②近似曲線を追加、③グラフに数式を追加

という手順で傾きと切片を算出しているんだけど、もっと良い方法はないだろうか？

それなら関数を使えば一発で算出できますよ。

というわけで、この記事でデータから線形近似式の傾きと切片を一発算出する方法をご紹介していきます。

それでは早速やっていきましょう。

線形近似式予測：SLOPE&INTERCEPT関数
SLOPE&INTERCEPT関数の使い方
実際の使用例
VBAへの組み込み方法
おわりに

線形近似式予測：SLOPE&INTERCEPT関数

エクセルには線形近似式の傾きと切片を求める関数が以下のように存在します。

傾きを求める関数：SLOPE

切片を求める関数：INTERCEPT

SLOPEは【傾斜する】という意味のある単語。

INTERCEPTは【さえぎる】という意味がある単語です。

SLOPEはそのままなので、覚えやすいでしょう。

INTERCEPTはY軸をさえぎっている点とイメージすればわかりやすいですね。

以降はこれらの使い方を詳細解説していきます。

SLOPE&INTERCEPT関数の使い方

傾きを求めるSLOPE関数の使い方

=SLOPE(【y軸のデータ】,【x軸のデータ】)

切片を求めるINTERCEPT関数の使い方

=INTERCEPT(【y軸のデータ】,【x軸のデータ】)

たったこれだけです。非常に簡単ですね。

次は実際の使用例を見てみましょう。

実際の使用例

今回は、以下の画像のようなデータ群の切片と傾きを求めてみましょう。

参考までに散布図を作成し、線形近似式＆数式を追加しています。

今回はグラフ上の数式のように

傾き＝2.8

切片=-1.5

をそれぞれ先ほど紹介したSLOPE関数、INTERCEPT関数を使って算出していくことになります。

それでは先ほど紹介したように関数を使ってみましょう。

まずは傾き（SLOPE関数）です。

領域を選択する際はB2セルを左クリックし、そのままクリックを離さずに、B5セルまでマウスを移動させると便利ですね。

以下が実際の算出結果です。

散布図と同じ2.8が算出されています。

次は切片（INTERCEPT関数）です。

非常に簡単ですね。

というわけでここまでが、線形近似式を求める方法でした。

※この記事を読んでいる人の中には、傾き、切片を求めて線形補間をしようとしている人も多いのではないでしょうか？

わざわざ傾き、切片を算出しなくても、線形補間を行う方法もありますので、興味があれば、以下の記事も読んでみてください。

【エクセル】線形補間を一発算出！傾き、切片必要なし！VBA利用まで！

VBAへの組み込み方法

VBAで複雑な処理を行っていると、途中で算出したデータに対して、傾きと切片を求めたい、という状況も出てくるかと思います。

ここではVBA上で、このSLOPEとINTERCEPTを使用する方法をご紹介します。

難しそうですが、それぞれたったの一行で終わります。

Sub Macro1()
    Range("B6") = WorksheetFunction.Slope(Range("B2:B5"), Range("A2:A5"))
    Range("B7") = WorksheetFunction.Intercept(Range("B2:B5"), Range("A2:A5"))
End Sub

これで先ほどと同じように

B6セルに傾きが

B7セルに切片が入力されます。

もちろん、これらの数値はセルに入力せず変数として用いることも可能です。

実際に変数として使用すると以下のようになります。

ここでは今回の近似式でx=20のときのyの値を計算しています。

Sub Macro1()
    Dim katamuki As Single
    Dim seppen As Single
    Dim kai As Single
    katamuki = WorksheetFunction.Slope(Range("B2:B5"), Range("A2:A5"))
    seppen = WorksheetFunction.Intercept(Range("B2:B5"), Range("A2:A5"))
    x = 20
    kai = katamuki * x + seppen
    Range("B8") = kai
End Sub