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

 

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

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

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

 

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

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

 

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

 

スポンサーリンク

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

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

傾きを求める関数:SLOPE

切片を求める関数:INTERCEPT

 

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

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

 

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

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

 

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

 

スポンサーリンク

SLOPE&INTERCEPT関数の使い方

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

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

 

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

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

 

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

 

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

 

スポンサーリンク

実際の使用例

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

 

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

SLOPE関数とINTERCEPT関数を適用するサンプルデータ

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

傾き=2.8

切片=-1.5

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

 

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

まずは傾き(SLOPE関数)です。

サンプルデータにSLOPE関数を適用している様子

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

 

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

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

サンプルデータにSLOPE関数を適用した結果

 

次は切片(INTERCEPT関数)です。

サンプルデータにINTERCEPT関数を適用している様子

非常に簡単ですね。

 

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

 

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

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

 

スポンサーリンク

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

 

VBA上で切片、傾きを算出し、線形補間を行った例

こういった使い方も覚えておくと便利ですので、ぜひマスターしておきましょう。

 

※今回は線形近似式の導出でしたが、2次、3次でも近似式の係数をVBAで導出することは可能です。

以下でやり方を解説していますので、興味があればご覧ください。

【エクセルVBA】データから2次の多項近似式を取得する方法!

【エクセルVBA】データから3次の多項近似式を導出する方法!

スポンサーリンク

おわりに

というわけで今回は普段のエクセル業務に活用できるSLOPE関数とINTERCEPT関数をご紹介しました。

 

少しづつエクセルスキルを向上させ、日々の業務を効率化していきましょう。

 

このように私のブログではエクセルスキルはもちろん、様々なプログラミングスキルを紹介しています。

・日々の残業時間を減らしたい。

・業務をもっと効率的にこなしたい。

・もっと勉強する時間を作りたい。

こんな思いを持っている人は、ぜひ他の記事も見てみてください。

この記事が役に立ったという方は、ぜひ応援よろしくお願いします。

↓ 応援ボタン

にほんブログ村 IT技術ブログ VBAへ
にほんブログ村

それではまた!

コメント

タイトルとURLをコピーしました