エクセルで過去のデータから将来の予測値をシミュレーションする【回帰分析】

★気に入ったらシェアをお願いします!

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

以前、このブログが2020年末までに200万PVに達成するためにはどうしたらよいのか?ということを検証しました。

こちらの記事です。

2020年にブログのページビューを200万に到達させるには何記事必要か分析してみた
5年後、2020年までにブログのページビューを200万PVまで伸ばしたいとしたら、どうすれば達成できるでしょうか?エクセルを使ってシミュレーションしてみましたので、8月の運営報告とともにお伝えしたいと思います。

これまでの実績をもとにエクセルの機能をあれこれ使ってシミュレーションしますと

2016年9月から毎月49記事を更新していけば、2020年には200万PVに達する

という結果が出ました。

当然、色々な要因でその通りにいかない場合も多いのですが、目標やその目標を達成するためのアクションを予測して分解して行動計画に落とし込むというのは、ビジネスにおいて非常に重要なことだと思います。

何回かのシリーズ記事で、エクセルの色々な機能を使って将来の予測値をシミュレーションする方法についてお伝えします。

今回はグラフの近似曲線を使う方法と、FORCAST関数を使う方法で将来のページビューを予測値をシミュレーションしていきます。

では、早速行ってみましょう。

過去データの準備

まず現在までのブログのページビュー数に関する実績表を作りました。

エクセル上の過去のデータ

指標としては、年月とページビュー、それに加えて関連性が高いであろう公開記事数を入れています。

この過去データの実績をもとに2016年9月以降の予測値をシミュレーションしていきたいと思います。

グラフの線形近似を使って予測をする

まずグラフの線形近似を使う方法についてお伝えします。

通常のグラフを作るのと同様、A列とB列を選択した状態でグラフの挿入をします。

リボンから「挿入」→「縦棒グラフの挿入」→「集合縦棒」と選択していきましょう。

するとこのようなグラフがあっという間に出来上がります。

エクセルで過去のデータからグラフを作成

次にこのグラフに線形近似曲線を追加していきます。ちなみに、ここで追加する線形近似曲線は回帰直線とも言います。

グラフを選択した状態でグラフエリアの右上に出る「+」ボタンから「近似曲線」→「その他のオプション」とたどります。

エクセルのグラフの近似曲線のオプションで

「近似曲線の書式設定」ウィンドウが開きますので、「線形近似」のラジオボタンにチェック、「グラフに数式を表示する」と「グラフにR-2乗値を表示する」のチェックボックスの両方にチェックします。

エクセルの近似曲線の書式設定

すると、グラフに点線の直線と「y=525.9x-2E+07」「R²=0.929」という数式が表示されます。

エクセルのグラフで近似曲線と数式を表示

この直線が線形近似曲線、「y=~」の数式が線形近似曲線の数式となります。

中学校で習ったのを覚えていらっしゃると思いますが、xにかかる変数を「傾き」、定数で加算(減算)される値を「切片」と言いましたね。

さて、数式ですが、傾きが指数表記ですとちょっとわかりづらいので、表示形式を変更しましょう。

数式のあたりをクリックすると右側のウィンドウが「近似曲線ラベルの書式設定」ウィンドウに切り替わりますので、「ラベルオプション」の表示形式を「数値」に変更、小数点以下の桁数を「2」に設定します。

エクセルのグラフでラベルの表示形式を変更

すると数式の表示が「y=525.90x-22,146,950.69」に変更されました。

エクセルの近似曲線の数式のラベルの書式設定を変更した

R-2乗値とは

ちなみに、「R²=0.93」とありますが、これはR-2乗値といいます。またの名を決定係数とも言います。

R2とは、1つ以上の予測変数との関係によって説明される応答変数の変動のパーセントのことです。通常、R2が大きいほど、データへのモデルの適合度は高くなります。R2は、必ず0~100%の間の値になります。R二乗は、決定係数または重決定係数(線形重回帰の場合)とも呼ばれます。

参考R二乗 – Minitab

んー、ちょっとわかりづらいですね。

簡単に説明するとR-2乗値は、近似曲線がもっともらしいかどうかを示す指標で、0から1の値をとるということです。1に近いほどもっともらしい値をとるということなので、今回の近似曲線のR-2乗値が0.93ということは、まずまずもっともらしい、ということが言えそうです。

実際のR-2乗値の評価については、0.9は欲しいという場合もありますし、0.7~0.8程度以上が妥当という場合もあるので、ケースバイケースのようです。

あまり低いのであれば、信頼しづらいということで良いかと思います。

未来の予測値をシミュレーション

今回の場合、xの値は年月、yの値をページビューとしています。

B列のページビュー数を求める場合は

ページビュー数 = 525.90 * 年月 – 22,146,950.69

で算出できるということです。

例えば、B21セルであれば

=525.9*A21-22146950.69

と数式を入れればいいということになります。

A列に2020年末までの年月を入力し、同じくB列の2020年末までの対象セルに上記数式をコピーします。その上でグラフの対象データ範囲を2020年末まで引っ張ると

エクセルで将来の予測値のグラフを作成

このように表示されます。

2020年末までに見事100万PVを突破するということになります…!

FORCAST関数で回帰直線による予測をする

線形近似曲線の数式ですが、実はグラフでわざわざ出さなくても関数で算出することができます。

FORCAST関数で回帰直線による予測を行うことができます。

書き方としては

FORCAST(予測に使うx,yの範囲,xの範囲)

と書きます。前述の例では、xは年月、yの値はページビューですからB21セルを求めるのであれば

=FORECAST(A21,$B$2:$B$20,$A$2:$A$20)

とすればOKです。

この数式を2020年12月まで引っ張れば先ほどの方法と近い予測値が得られます。

RSQ関数でR-2乗値を求める

FORCAST関数で予測値は出ますが、その予測値がもっともらしいかはわかりません。

その場合は、RSQ関数でR-2乗値を算出しましょう。

書き方は

RSQ(yの範囲,xの範囲)

です。

今回の場合は

=RSQ(B2:B20,A2:A20)

とすればOKですね。実際に求めてみますと

エクセルのRSQ関数でR-2乗値を算出

という結果になります。

まとめ

エクセルで過去のデータから将来の予測値をシミュレーションする方法についてお伝えしました。

グラフに近似曲線を描く方法とFORCAST関数を使う方法と二つの方法についてお伝えしました。

いずれも回帰分析と呼ばれる方法です。

予測値を出すだけならFORCAST関数が簡単ですが、ビジュアルで見たいときはグラフが良いですよね。目的に応じて使い分けて頂ければと思います。

さて、ブログのページビューのシミュレーションの予測を立てる上で「年月」だけでシミュレーションするというのも、若干無理やりな感じがありますよね。

次回はそのへんも解決しつつ、別の回帰分析の方法としてデータ分析アドインを使う方法についてお届けできればと思います。

エクセルのデータ分析アドインを使って回帰分析をする方法と回帰分析表の見方
過去のデータから予測値を求めるために、エクセルのデータ分析アドインを使って回帰分析をする方法についてお伝えします。アドインの追加の方法、回帰分析とは何か、回帰分析表の見方などについて解説しています。

どうぞお楽しみに!

連載目次:エクセルで回帰分析!過去データから将来の予測や行動計画を立てる

過去のデータが揃っていれば回帰分析を使うことで将来の予測を立てたり、目標値を達成するための行動計画を立てたりということが可能です。初心者向けにエクセルを使って簡単に回帰分析をする方法についてブログのページビューを事例としてお伝えしていきます。
  1. エクセルで過去のデータから将来の予測値をシミュレーションする【回帰分析】
  2. エクセルのデータ分析アドインを使って回帰分析をする方法と回帰分析表の見方
  3. エクセルのゴールシークを使って目標から逆算して毎月の必要なアクションを知る