【Excel】TREND関数の使い方と危険性【検索関数と組み合わせる】

2018/9/2 2018/12/9 関数

AI時代といわれる今日この頃ですが、もはや数値予測は『勘』ではなく『観』である必要があるというのが今回のTREND関数です。

簡単に言ってしまえば、先のデータを過去のデータから予測してくれるものです。ものすごい便利な時代ですね！
しかしこのTREND関数も使う私たちが正しく使えなければ意味がないのです。

今回は実践に則したTREND関数の使い方を見ていきましょう。

TREND関数の基本的な考え方

危険性って？

実践的にTREND関数を使ってみるには？

今回も検索関数やその他の関数が各種出てきます。しっかりと理解した上で読み進めていきましょう。

TREND関数の基本的な考え方

TREND関数はすこし極端な言い方をすれば、過去の数値を基に『このままいくとどれぐらいになるだろう』という値を算出する関数です。
もちろんそれ通りになるわけではないですが、目標の目安を設定する意味では単なる『勘』ではなく論理的な方法です。

＝TREND(yの範囲,xの範囲,予測に使うxの範囲,切片)

はい、「x」や「y」って聞くと難しく感じますよね。でも簡単です。
私は文系で数学はかなり苦手でしたし今も苦手ですが、その私でも理解できた流れに沿ってお話しします。
知識のある方からすれば「これ違うよ」という内容もあるかもしれませんが、あくまで初心者がもつイメージのお話しなので細かくはスルーしてください。

まずはイメージで理解してみましょう。先程の表をグラフにしてみます。

青い点が月の売上を指しています。そして知りたいのは赤枠の範囲である6月がどこに来るのかという事です。
あてずっぽうではなく、ある程度根拠が欲しいわけですね。

今回の目的である6月の売上予測の値を出すには、『回帰直線』という考え方をします。私も社会人になってTREND関数を調べてから初めてこの言葉を知りました笑

回帰直線とは

2組のデータ(今回でいう月と売上)の中心的な分布傾向を表す直線。分布の中心を通る直線ぐらいのイメージです。

これを考えてみます。

赤い点線がこれまでの傾向を表している回帰直線になっています。各月の青い点の中心を通る線といいながら、離れている点もあればほぼ重なっている点もあります。

回帰直線とは以下のイメージです。
それぞれの青い点から回帰曲線までの距離があります(グラフの各点から出ている黒い矢印)が、その矢印がどの点からも一番短くなる線が回帰直線です。

一般的には、「傾向がわかる」みたいな表現が近いと思います。

先程の売上表と数式で考えてみます。

知りたいのは6月の値です。

5月までの売上が分かっています。
ｘ軸は売上で、ｙ軸は月です。

これを先程の式に当てはめます。

＝TREND(既知のy範囲,既知のx範囲,予測に使うxの範囲,切片)

=TREND(B3:B7,A3:A7,A8,TRUE)

切片はTRUEとしておいて大丈夫です。
これで算出された答えが、回帰直線上にあれば「OKです。

6月の売上予測の値は16,556千円となりました

見事に回帰直線上です。回帰直線で一定の結果が出ました。

危険性って？

『一定の結果』とすごい含みのある言い方をしましたが、このままではあまり有効な値とは言えません。なにせ、これは月合計の売上の話をしています。『勘』のいい方はここで気付くかもしれません。

月の日数が無視されているんです。
2月の売上が低いのはそれは31日と比べて28日なので日数が少なければそれだけ売上合計も低いですよね。

月の売上＝1日あたりの売上×日数になるわけですから。

これを踏まえて考えてみます。

1日の売上で考えると、月によってばらつきがあるのがわかります。6月の1日の売上を算出して、6月の30日分を掛けてみます。

=TREND(B11:B15,A11:A15,A16,TRUE)

6月の1日あたりの売上が523千円となり、30日を掛けると15,676千円となりました

先程出した6月の売上予測と比較してみます。

大きく差が出ているのがわかります。
『じゃあどちらを信用したらいいの？』と言われれば、私なら情報が多い右の15,676千円という値のほうが信憑性が高いと答えます。

使うデータや同じデータでも使い方によって答えが異なってしまうのがTREND関数の危険性なんです。

実践的にTREND関数を使ってみるには？

TREND関数自体を非難しているわけではなく、使い方が重要という事です。TREND関数自体は非常に優れた関数なのでそこは上手に活用する方法(考え方)を身に付けていきましょう。

実際に使いこなすには、いかに同じ条件で考えるかが重要です。
先程の日数のように条件が違うならば、正しいデータにならないのです。また例題を使って考えてみましょう。

例題：屋台の売上予測

サッカーの試合会場の外で屋台を出すことになりました。天候によって観客数が大きく左右されてしまいます。9月の売上データを基に10月1日の売上予測をします。
なお、10月1日の天候は雨の予報です。

普通にTREND関数を使うと10/1の売上予測は『148,608』と出ます。

=TREND(D3:D32,A3:A32,A33)
※9/1～30までの売上データから回帰直線を算出。

しかし10/1は雨予報であるので、雨が降った日のデータに絞り込んだほうが予測の精度は上がりそうです。絞り込んでみました。

条件一致する日を出すセルをG列に用意します。そしてG3に
=IF($I$1=C4,ROW(A4)-2,””)

H3セルには
=IFERROR(OFFSET(G3,SMALL($G$3:$G$33,ROW(A1))-ROW(A1),-6,1,1),””)
と入っています。

これで雨に該当する日を特定したのであとは該当日の売上データを検索してしまえば一覧になりますね。検索関数などの使い方に関しては以下をご覧ください。

あとはM1セルに条件に合致した日付の売上をベースにTREND関数を当てはまれば完了しますね。

普通に9月全日でTREND関数を使用したら『148,608』でしたが、雨という条件に絞ることで『143,346』と数値が変化しました。

どちらのほうが信憑性が高いでしょうか？聞くまでもありませんね。
このようにTREND関数は正しく使うには『どのようなデータ』が必要なのかを考えるようにすれば心強い味方になります。