アップダウンが激しくてまともに分析できないよ…。
今回はこの悩みを解決する方法を、誰にでもわかるように教えます。
この記事では、Excelでできる主な方法3つを紹介します。
その方法を、最初に簡単にまとめておきます。
データを準備しよう
あなたが普段よく見ている、ブログアクセスのデータはWeb上にあります。
なのでExcelで処理するには、データを「エクスポート」する必要があります。
エクスポートの方法は簡単で、2ステップで終了です。
Google Search Console の場合
Google Analytics の場合
方法A：週ごと集計
週ごと集計
「週ごと集計」では、その名の通り１週間単位でデータの推移を見ていくことになります。
ところで、なぜ「週ごと」にするのでしょうか？
それは、曜日によってアクセス数に格差があるからです。
例えば「休日にググる人が多い」とすると、休日と平日のアクセス数にはかなり差があるため、グラフのアップダウンが激しくなりますよね。
このように「週ごと集計」をするメリットは、曜日による格差が排除されて分析しやすくなることなのです。
では、その方法を以下で解説していきます。
ステップ①：「ピボットテーブル」で集計する
まず、エクスポートしたExcelファイルを開きます。
そして、下のタブから「日付」を選択します。
(下図参照↓)
次に、上の「挿入」タブをクリックします。
そして「ピボットテーブル」を選択します。
(下図参照↓)
すると、下図のようなウィンドウが開きます。
ここはそのまま「OK」ボタンを押してください。
「OK」を押すと、右側に下図のようなものが出てきます。
まず最初に「日付」にチェックを入れましょう。
次に、分析したいデータにチェックを入れます。
今回は「表示回数」にチェックを入れましたが、もしあなたが「クリック数」を分析したいなら「クリック数」にチェックを入れればいいです。
チェックを入れるとこんな感じ。(下図参照↓)
次に、Excelシート上にできた表の、どれか１つのセルを右クリックします。
すると、下図のようにメニューが出てくるので「グループ化」を選択します。
そうすると下図のような画面が出てきます。
「単位」の項目のうち「日」だけを選択し、下にある「日数」を「７」に変更します。
「OK」ボタンを押すと、下図のように７日ごとに集計できたことがわかります！
ステップ②：折れ線グラフに図示する
さて最後に、この週ごとに集計されたデータをグラフに表すことにしましょう。
まず、グラフにしたい数値データを選択します。
次に、上の「挿入」タブを選択します。
下図の手順に沿って、「折れ線グラフ」を選択。
はい！下図のように、ガタガタが軽減されて分析しやすいグラフになりました！
グラフを読むにあたって、最後のデータ（2021/8/20-8/25）は７日間の合計になっていないこともあるのは注意しておきたいですね。
【おまけ】曜日別分析
おまけとして、曜日別に集計する方法も紹介しておきます。
「曜日別分析」では、その名の通り「日・月・火・…・土」に分けて集計して分析します。
こうすることで「休日にアクセスが多い」などの傾向をつかむことができます。
ステップ①：WEEKDAY関数で曜日を割り当てる
まず、セルF2に「=WEEKDAY(A2)」と入力し、Enter。
この作業をすべての日付についてやればいいのですが、さすがに大変なので「オートフィル」機能でやります。
オートフィル機能をご存じでない方のために、簡単に説明しておきました。
(下図参照↓)
ちなみにここで現れた「３,２,１,７,…」という数字は何かというと、曜日を表しています。
今回の場合は
1＝日曜日，２＝月曜日，３＝火曜日，…，７＝土曜日
のように対応しています。
ステップ②：SUMIF関数で集計する
次に、ステップ①で割り当てた曜日を頼りに集計していきます。
まず、Excelシート上の少し離れたところで、下図のように入力してください。
入力出来たらEnterを押し、ステップ①と同様にオートフィルで７個分同じようにデータをそろえましょう。
そうすると、曜日ごとの合計が計算されます！
(下図参照↓)
【方法B】移動平均法
移動平均法
「移動平均法」とは、次のような意味です。
移動平均を求めたい中心となるデータから前後のいくつかのデータ（項）を足して、平均をとるという方法です。
引用：https://bellcurve.jp/statistics/course/12933.html
いまいちピンとこないという人は、下の図を見てください。
このように、周りのデータを巻き込んだ平均値を使うことで、変化を滑らかにすることができます。
ちなみに「７項移動平均」とした理由は、「アクセス数は１週間周期で移り変わるだろうな」と考えたからです。
この方法はシンプルかつ有用だと思うので、ぜひマスターしておいてください。
これより下で説明している、「移動平均」をExcelでやる方法はちょっと面倒でした…。
「追記」としてもっと楽な方法を紹介します。
まず、移動平均法で滑らかにしたいグラフを下のように表示させます。
そうすると、グラフの右上に「+」マークがあるのでクリック。
出てきたメニューのうち「近似曲線」にカーソルを当てる(クリックしない！)。
すると、すぐ右に「▶」マークが出てくるのでクリック。
メニューが出てくるので「その他のオプション」をクリック。
すると画面右側に下図のようなオプションが出てきます。
下図の①～③に従って設定すると、先ほどのグラフに移動平均線が出てくるようになります！
「移動平均線がちょっと見にくい…」と思った人は、
「書式」タブ ▶ 「図形の枠線」
から色・太さ・線の種類などを変えてみてください。
ステップ①：AVERAGE関数で移動平均を求める
まず、下図のように入力します。
ここで注意なのが、７項移動平均の場合最初と最後の各3セルは空けておかなければならないことです。
最初と最後の各3日分の移動平均は求められないからです。
最後の3セルもあけるのを忘れずに↓
ステップ②：グラフに図示する
さあ、この移動平均をグラフに図示してみましょう。
「日付」の列と「移動平均」の列を同時に選択したうえで、グラフ作成します。
(下図参照↓)
すると、下図のようなグラフができました！
だいぶ滑らかになって、分析がしやすくなりました。
【方法C】対数変換
対数変換
次は「対数変換」をやってみましょう。
対数変換をすることで、激しい値の変化を小さくすることができます。
対数変換では、高校数学で習う「対数(log)」を使います。
例えば「表示回数」が「100」とします。
このとき「100」の常用対数(底＝10)をとると「log10100」つまり「2」となります。
このように「100」という値を「2」に変換するような作業を「対数変換」といいます。
（あ、安心してください。logの計算方法が分からなくてもExcelが計算してくれるので…。)
ステップ①：LOG関数で対数変換
下図のように、新しい列「対数変換」を作って「＝LOG(C2)」と入力します。
入力したらEnterを押し、これまでと同様「オートフィル」で1番下まで入力します。
そしてグラフを作成します。(移動平均法のステップ②と同様)
すると結果は…↓
なるほど…
ガタガタはあまり軽減されていないものの、「増加傾向にある」ということがはっきりわかるようになりました！
まとめ
というわけで、以上になります。
この記事では、やり方メインで説明しましたが
「なぜこの方法を使うのか」
「この方法をどう分析に生かすか」
ということも、しっかりと押さえておいてください。
グラフをただ眺めて終わり、ではもったいないので。
最後に、記事の冒頭で示した図を再掲しておくので、復習用にどうぞ。
＊記事作成にあたり、こちらの書籍を参考にさせていただきました↓
記事への意見・感想はコチラ