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

f:id:yshgs_elec:20210212195426j:plain

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

 

そんなときに、

①散布図を作成

②近似曲線の追加

③グラフに数式を追加

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

 

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

 

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

 

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

 

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

スポンサーリンク

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

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

傾きを求める関数:SLOPE

切片を求める関数:INTERCEPT

 

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

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

 

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

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

 

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

スポンサーリンク

SLOPE&INTERCEPTの使い方

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

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

 

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

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

 

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

 

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

スポンサーリンク

実際の使用例

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

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

f:id:yshgs_elec:20200724210927j:plain

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

傾き=2.8

切片=-1.5

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

 

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

f:id:yshgs_elec:20200724211946j:plain

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

 

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

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

f:id:yshgs_elec:20200724211836j:plain

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

f:id:yshgs_elec:20200724212434j:plain

非常に簡単ですね。

 

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

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

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

スポンサーリンク

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

f:id:yshgs_elec:20200724233817j:plain

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

スポンサーリンク

おわりに

というわけで今回は普段のエクセル業務に活用できるSLOPEとINTERCEPTをご紹介しました。また、VBAへの組み込みも非常に簡単に行うことができます。

 

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

 

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

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

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

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

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

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

↓ 応援ボタン

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

それではまた!

コメント

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