仕事でエクセルを使っている際に、データ群の線形近似式を求めることはよくあると思います。
①散布図を作成し
②近似曲線の追加し
③グラフに数式を追加
という手順でグラフの傾き、切片を算出している人は多いのではないでしょうか。
さらにその出てきた数値をわざわざ手打ちでセルに入力する、なんてゆうこともあると思います。
この記事ではそんな面倒な作業を一瞬で解消できる方法をご紹介します。
特別なプログラミングスキルは必要なく、もともとエクセルに組み込まれているものですので、是非このスキルをマスターし、日々の業務を効率化していきましょう。
それでは早速やっていきます！
線形近似式予測スキル【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上で、このSLOPEとINTERCEPTを使用する方法をご紹介します。
※VBAの使い方については以下の記事をご確認ください。
www.higashisalary.com
難しそうですが、それぞれたったの一行で終わります。
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()
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
こういった使い方もよくしますので、ぜひマスターしておきましょう。
終わりに
というわけで今回は普段のエクセル業務に活用できるSLOPEとINTERCEPTをご紹介しました。また、VBAへの組み込みも非常に簡単に行うことができます。
少しづつエクセルスキルを向上させ、日々の業務を効率化していきましょう。
私のブログではエクセルスキルはもちろん、様々なプログラミングスキルを紹介しています。
・日々の残業時間を減らしたい。
・業務をもっと効率的にこなしたい。
・もっと勉強する時間を作りたい。
こんな思いを持っている人は、ぜひ他の記事も見てみてくださいね。
それではまた！
関連記事
www.higashisalary.com
www.higashisalary.com
オススメ記事
www.higashisalary.com
www.higashisalary.com
