はじめに
最小二乗法による直線フィッティングはよく使われる技なので、実はExcelは答えが一発で出るような方法を用意しています。
ただし、途中経過を全て飛ばして答えが出てきますので、最小二乗法についてまったく知らない人がいきなりこれを使うのは危険です。それどころか、出てきた数値が何を意味しているのかわからない可能性もあります。
最小二乗法についてまったく知らない人は、まず
こちらをご覧ください。
最小二乗法について少し知っていても、具体的な計算プロセスについては知らないという人は
こちらをご覧ください。
最小二乗法による直線フィッティングについて、よく知っていると言う人は、以下の解説に進んでください。
表の加工をせずに、グラフさえ出来ればいいという場合は
こういう方法もあります。(おそらく、これが一番簡単です。)
Excelには、最小二乗法による直線フィッティング用に
LINESTという関数が用意されています。
一般的な使い方は
=LINEST(計算に使うYの範囲、計算に使うXの範囲、Y切片を0にするかしないか)
というような形式です。
X,Yという順番ではなく、Y,Xという順番であることに注意してください。
範囲の指定の方法ですが
という表があり、C5からC10までのデータを使って計算しようとするとき、Yの範囲指定は
C5:C10
というような形になります。
同様にB5からB10までのデータを使って計算しようとするとき、Xの範囲指定は
B5:B10
というような形になります。
Y切片の与え方ですが、
TRUEを指定すると、Y切片は0以外の値をとります。すなわち直線の式が Y=aX+b となるようにa,bを求めます。
FALSEを指定すると、Y切片が0になるように、すなわち直線の式が Y=aX となるようにaを求めます。
出力について
Y切片の値が0と指定、すなわち
=LINEST(C5:C10,B5:B10,FALSE)
とすれば、傾きaが計算されて、値が表示されます。
ところで、Y切片が0以外の値になるように指定した場合はどうでしょう?
=LINEST(C5:C10,B5:B10,TRUE)
とすると、やはり傾きaしか表示してくれません。では、Y切片bを表示させるにはどうしたらいいのでしょう。
これは
=INDEX(LINEST(C5:C10,B5:B10,TRUE),2)
とすることで表示させることが出来ます。
実は、LINEST関数は配列型の出力をします。第一要素が傾き、第二要素がY切片です。
INDEX関数と、最後の ,2 で第二要素を書き出すように指定してます。
(よって、第一要素である傾きの出力は =INDEX(LINEST(C5:C10,B5:B10,TRUE),1) と書いてもよいです。)
これを二つまとめて出力する方法もあります。
いきなり二つとも計算する方法
以下の具体例を参考にしてください。
傾きとY切片を別々にも求める
適当なセルを選択して
=LINEST(C5:C10,B5:B10,TRUE)
と打ちます。
Enterを押すと、傾きが計算されます。
同様に、適当なセルに
=INDEX(LINEST(C5:C10,B5:B10,TRUE),2)
と打ちます。
Enterを押すと、Y切片がが計算されます。
このようにして、傾きとY切片がそれぞれ計算されます。
傾きとY切片を同時に求める
適当なところに、セルを二つ選択します。(マウスボタンを押しながら横にひっぱる。)
ここで、
=LINEST(C5:C10,B5:B10,TRUE)
と打ちます。
ここで、Ctrl+Shift+Enter を同時に押します。
そうすると、傾きとY切片が同時に計算されて表示されます。
(同時に、上の表示が {=LINEST(C5:C10,B5:B10,TRUE)} というように、{ }カッコがつきます)
完成!