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

仕事でエクセルを使っている際に、データ群の線形近似式を求めることはよくあると思います。

①散布図を作成し

②近似曲線の追加し

③グラフに数式を追加

という手順でグラフの傾き、切片を算出している人は多いのではないでしょうか。

さらにその出てきた数値をわざわざ手打ちでセルに入力する、なんてゆうこともあると思います。

この記事ではそんな面倒な作業を一瞬で解消できる方法をご紹介します。

特別なプログラミングスキルは必要なく、もともとエクセルに組み込まれているものですので、是非このスキルをマスターし、日々の業務を効率化していきましょう。

それでは早速やっていきます！

線形近似式予測スキル【SLOPE&INTERCEPT】
SLOPE&INTERCEPTの使い方
実際の使用例
VBAへの組み込み方法
おわりに

線形近似式予測スキル【SLOPE&INTERCEPT】

エクセルには様々な関数が組み込まれていることはご存じかと思いますが、その中に線形近似式の傾きと切片を求めるものが以下のように存在します。

傾きを求める関数：SLOPE

切片を求める関数：INTERCEPT

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

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

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

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

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

SLOPE&INTERCEPTの使い方

傾きを算出したいセル上で

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

切片を算出したいセル上で

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

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

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

実際の使用例

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

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

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

傾き＝2.8

切片=-1.5

をそれぞれエクセルの関数のみで算出していくことになります。

それでは先ほど紹介したように数式を入力していきましょう。

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

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

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

切片も同様にすれば簡単に算出できます。

非常に簡単ですね。

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

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

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

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

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