photo credit: Studying for an Exam via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
以前、このブログが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-2乗値は、近似曲線がもっともらしいかどうかを示す指標で、0から1の値をとるということです。1に近いほどもっともらしい値をとるということなので、今回の近似曲線のR-2乗値が0.93ということは、まずまずもっともらしい、ということが言えそうです。
実際のR-2乗値の評価については、0.9は欲しいという場合もありますし、0.7～0.8程度以上が妥当という場合もあるので、ケースバイケースのようです。
あまり低いのであれば、信頼しづらいということで良いかと思います。
未来の予測値をシミュレーション
今回の場合、xの値は年月、yの値をページビューとしています。
B列のページビュー数を求める場合は
で算出できるということです。
例えば、B21セルであれば
と数式を入れればいいということになります。
A列に2020年末までの年月を入力し、同じくB列の2020年末までの対象セルに上記数式をコピーします。その上でグラフの対象データ範囲を2020年末まで引っ張ると
このように表示されます。
2020年末までに見事100万PVを突破するということになります…！
FORCAST関数で回帰直線による予測をする
線形近似曲線の数式ですが、実はグラフでわざわざ出さなくても関数で算出することができます。
FORCAST関数で回帰直線による予測を行うことができます。
書き方としては
と書きます。前述の例では、xは年月、yの値はページビューですからB21セルを求めるのであれば
とすればOKです。
この数式を2020年12月まで引っ張れば先ほどの方法と近い予測値が得られます。
RSQ関数でR-2乗値を求める
FORCAST関数で予測値は出ますが、その予測値がもっともらしいかはわかりません。
その場合は、RSQ関数でR-2乗値を算出しましょう。
書き方は
です。
今回の場合は
とすればOKですね。実際に求めてみますと
という結果になります。
まとめ
エクセルで過去のデータから将来の予測値をシミュレーションする方法についてお伝えしました。
グラフに近似曲線を描く方法とFORCAST関数を使う方法と二つの方法についてお伝えしました。
いずれも回帰分析と呼ばれる方法です。
予測値を出すだけならFORCAST関数が簡単ですが、ビジュアルで見たいときはグラフが良いですよね。目的に応じて使い分けて頂ければと思います。
さて、ブログのページビューのシミュレーションの予測を立てる上で「年月」だけでシミュレーションするというのも、若干無理やりな感じがありますよね。
次回はそのへんも解決しつつ、別の回帰分析の方法としてデータ分析アドインを使う方法についてお届けできればと思います。
どうぞお楽しみに！
コメント
すっごくわかりやすくて助かりました！
ありがとうございます！！
中学生にもわかるような回帰分析や統計の本を是非だしてくださいー！
みゅこさん
ありがとうございます！
お役に立てて嬉しいです～
統計の本…勉強します！！