Excel。事務職のデータ分析30。回帰直線による複数の予測値を算出するにはどうしたらいいの？
＜回帰分析：TREND関数＞
Excelのいいところは、統計や分析のことを詳しく勉強していなくても、ちょっとした数式を使うだけで、資料作りもパワーアップすることができます。
例えば、散布図の近似曲線に表示できる、回帰式。y=ax+b。
この数式を使うことで、回帰直線に予測値を算出することができます。
また、この散布図を作るのも、
次のような簡単な表から作ることができるわけで、
日ごろの資料をパワーアップできる可能性を秘めているわけですね。
改めて確認すると、
回帰式。y=ax+bの、aは「傾き」といって、
SLOPE関数を使うことで算出することができました。
bの「切片（せっぺん）」は、
INTERCEPT関数を使うことで簡単に算出することができました。
この「傾き」や「切片（せっぺん」を算出しないで、
一発で、予測値を算出することができるのが、FORECAST関数でした。
ただし、売上高と来店客数の関係性が高いのか低いのかを知るために、
「相関係数」というのを算出しておく必要がありました。
その相関係数を算出するためには、
CORREL関数を使うことで算出することができました。
この算出された結果が、”1”に近ければ、関係性が高いことを表しています。
さて、F8には、FORECAST関数を使って予測値を算出しています。
売上高を1500にするには、
来店客数は648人必要だろうということがわかったのですが、
複数の予測値を算出したい場合は、どうしたらいいのでしょうか？
いちいち、E8の数値を入力し直したり、
あるいは、F8のFORECAST関数をコピーするという方法もありますが、
複数の予測値を算出するには、
TREND関数を配列関数として使う方法があります。
【複数の予測値は、TREND関数】
次のような複数の売上高の場合、来店客数の予測値を算出してみましょう。
TREND関数を使うのですが、配列関数で処理しますので、
F11:F16を範囲選択して、TREND関数を手入力します。
TREND関数ダイアログボックスを表示する方法でもいいのですが、
配列関数にするのに、ctrl + shift + Enterキーを押すので、
手入力で関数を作るほうが、たぶん楽です。
F11に、
=TREND(C2:C13,B2:B13,E11:E16)
と入力したら、ctrl + shift + Enterキーを押すと、数式は、
{=TREND(C2:C13,B2:B13,E11:E16)}
となります。
そして、予測値が算出されます。
FORECAST関数で算出した結果の売上高1500の値と、
TREND関数で算出した結果が合致しています。
なので、複数の予測値を算出したい場合は、
FORECAST関数よりも、
TREND関数を配列関数で算出させる方法もありますので、
日ごろ作っている資料をパワーアップしたい時には、
使ってみると面白い結果が見えてくるかもしれませんね。
