photo credit: Statistics 1 via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
当ブログが2020年末までにどれほどのページビューになるのか、過去のデータから予測をしていくということをしています。
前回、以下の記事で回帰分析という方法なのですが、エクセルのグラフを使う方法と、FORCAST関数を使う方法と２通りお伝えしました。
今回は別の方法として、エクセルのデータ分析アドインを使って回帰分析をする方法についてお伝えします。
こちらの方法のほうが、色々な周辺の分析もパッと出てきてくれるんです。
また、それとともに前回係数として使っていたのが「年月」だったので（一応、予測のもっともらしさを示すR-2乗値は0.93程度だったのですが）、係数としてよりそれっぽい「公開記事数」を係数に再度シミュレーションをし直してみようと思います。
リボンに「データ分析」を追加する
まずリボンの「データ」の中の「分析」というエリアに「データ分析」というメニューがありますか？
これです。
存在しない場合は、アドインを追加する必要がありますので、まずはその方法からお伝えします。
既にあるよーという方は、この部分は読み飛ばして下さい。
データ分析アドインを追加する
まず、メニューの「ファイル」から「オプション」を選択します。
「Excelのオプション」ウィンドウが開きますので、左側から「アドイン」を選択をします。
「データ分析」アドインが追加されていれば、右側の「アクティブなアプリケーションアドイン」に「データ分析ツール」が含まれているはずです。
追加をする場合はウィンドウ下部の「設定」から追加していきましょう。
「アドイン」ウィンドウが開きますので、「分析ツール」にチェックをして「OK」します。
これでアドインが追加されます。リボンの「データ」に「データ分析」のメニューが追加されているか確認してみてください。
データ分析アドインで回帰分析をする
これにて準備は完了ということで、早速回帰分析を進めていきましょう。
サンプルとして使うでーたは前回と同じく、当ブログの2016年8月までのブログのページビュー、そして公開記事数です。
このデータをもとに、将来のページビューを予測していきます。
そもそも回帰分析とは
回帰分析は過去のデータから
という数式を見出す方法です。それぞれの要素は
- y：目標とする数値（目標変数）で今回は「ページビュー」
- x：yの前提となる数値（説明変数）で今回は「公開記事数」（前回の記事では「年月」でした）
- a：切片
- b：傾き（回帰係数）
のことで、回帰分析の目的はその切片aと傾きbを求めるということに他なりません。
過去のデータさえそろっていれば、エクセルのデータ分析ツールで瞬時で出せるようになります。
データ分析アドインの回帰分析の方法
では、そのやり方を解説していきます。
リボンの「データ」から先ほど追加した「データ分析」を選択します。
「データ分析」ウィンドウが開きます。この中には統計分析をする様々な機能が含まれていてウハウハです。今回はこの中から「回帰分析」を選択して「OK」をします。
「回帰分析」ウィンドウが開きます。以下のように各設定をしていきましょう。
- 入力Y範囲：目標変数yの元データ（ページビュー）のラベル＋データ範囲
- 入力X範囲：説明変数xの元データ（公開記事数）のラベル＋データ範囲
- ラベル：チェックを入れると1行目をラベルとして取り扱う
- 出力オプション：出力先をどこにするか指定
- OK
今回は出力先をE1セルに設定しました。
では、それでOKをしてみましょう。
回帰分析表を読み解く
このように結果として回帰分析表が出力されます。
なんか、すごそうですね…！
ただ、全ての項目を見なくても回帰式は求めることができます。
R-2乗値
まず、結果のもっともらしさを表現するR-2乗値ですが、この分析結果でいうと「重決定R2」という箇所が該当をしています。
今回は「0.96」ですね。
前回「年月」を説明変数xとして使っていたときは、0.93でしたから、それよりももっともらしい結果になったわけです。
切片と傾き
次に、切片aと傾きbは「係数」という箇所を見ます。
- 切片a：-26054.9
- 傾きb：681.4542
でしたから、回帰分析で求めた数式（回帰式）は
となります。
パッと出ると気持ちいいですね～
95％信頼区間
回帰分析表は他にもたくさん情報があるのですが、95％信頼区間についてお伝えしておきます。
これは
です。つまり今回の場合は
- 切片は-41700.9から-10408.9の区間に95％の確率で含まれている
- 傾きは613.626から749.2823の区間に95％の確率で含まれている
ということです。ですから、この区間があまりにも広い範囲だった場合は、回帰分析表で算出された係数をそのまま使ってシミュレーションをするのは適切ではなくなってしまいます。
回帰分析をもとに将来の予測をする
では、この結果をもとに将来の予測をしてみましょう。今回は、R-2乗値も高いですし、95%信頼区間もそんなに広くないので、そのまま係数として出力された切片と傾きを使います。
説明変数xである公開記事数は9月以降、毎月30ずつ増えていくとします。ですからC21セルは
です。それに対する目的変数yであるページビュー（B21セル）は、F17セルの切片、F18セルの傾きを使って
となります。
これを2020年12月までひっぱって、グラフを作成してみますと
となりました。
このペースでいくと、2020年12月には1,325,950ページビューに到達するようです。
まとめ
過去のデータから将来の予測をする回帰分析、エクセルのデータ分析アドインを使うととても簡単に行うことができます。
グラフを使う方法、FORCAST関数を使う方法も含めて、複数の方法がありますが、その都度目的にマッチした方法を選択頂ければと思います。
さて、今回は「毎月30記事を更新する」という前提でシミュレーションしましたが、逆のアプローチとして、200万ページビューに到達するためには、毎月何記事更新すればいいのでしょうか？
そんなときはエクセルではゴールシークという機能を使えますので、次回はその使い方についてお伝えしたいと思います。
どうぞお楽しみに！
連載目次：エクセルで回帰分析！過去データから将来の予測や行動計画を立てる
過去のデータが揃っていれば回帰分析を使うことで将来の予測を立てたり、目標値を達成するための行動計画を立てたりということが可能です。初心者向けにエクセルを使って簡単に回帰分析をする方法についてブログのページビューを事例としてお伝えしていきます。- エクセルで過去のデータから将来の予測値をシミュレーションする【回帰分析】
- エクセルのデータ分析アドインを使って回帰分析をする方法と回帰分析表の見方
- エクセルのゴールシークを使って目標から逆算して毎月の必要なアクションを知る
