見習い奮闘記

ExcelでGoogleアナリティクスでエクスポートしたデータでヒストグラムを作る

おほほほほ。Excelでヒストグラムが作れてうれしい見習です。
勢いのって、Googleアナリティクスのデータでできるかな???と試してみました(Excel2016使用)。

こんなの作ってみました。
Googleアナリティクスのデータでヒストグラムを作るとこんな風です。

Googleアナリティクスでデータをエクスポートする

まず、どんなデータでヒストグラムを作りたいのか・・・。
たとえば、こんなのがありそうです。
先月の日別セッション数は、どんな感じになっているのか。それに、新規ユーザーとリピーターで比較して見たい。
まずは、Googleアナリティクスにログインし、エクスポートするデータを整えます。

  • 期間を設定
  • 比較したい指標を設定
  • 比較したいセグメントを設定
  • 区切りたい期間を設定

今回は

  • 期間を設定(2018/6/1-2018/6/30)
  • 比較したい指標を設定(セッション)
  • 比較したいセグメントを設定(新規ユーザー、リピーター)
  • 区切りたい期間を設定(日)

を設定します。そしたら、

[エクスポート] → [Excel(XLSX)]と進み、エクスポートされたExcelを「名前をつけて保存」し、開きます。

Excelで作業する

複数のシートから構成されているので、「データセット2」シートを開きます。

最初に、合計行(A62:D63)を削除しておきます。

 

★ピボットテーブルを作る

データを選択した状態で、[挿入]タブ→[ピボットテーブル]へと進みます。


[データ範囲]を確認し[OK]します。


ピボットテーブルの設定は、以下のとおりにします。

  • 列「セグメント」
  • 行「日の指標」
  • 値「セッション」(合計)

★ヒストグラムの作成

データ区間を検討する

※上記で作成した「ピボットテーブル」のシートで作業します。

Min関数とMax関数を使い、最小値と最大値を確認します(関数中に、B列C列の合計行が入らないように注意)。

[ホーム]→[フィル]→[連続データの作成]で
範囲「列」、種類「加算」、増分値「20」(ヒストグラムでの1メモリの幅)、停止値「220」(Max関数の209から)を入力し、OKします。

 

ヒストグラム作成

ピボットテーブルをクリックした状態で[データ]→[データ分析]→[ヒストグラム]→[OK]とし、ヒストグラムのダイアログボックスを出します。

[ヒストグラム]のダイアログボックスでは、
[入力元]として、[入力範囲]に「リピーター」のデータ[$B$5:$B$34]、
データ区間として[$B$5:$B$34]、
[新規ワークシート][グラフ作成]にチェックをいれて、[OK]します。

※リピーターのヒストグラムが出力されるので、B1のセルを「リピーター」と入力しておきます。

ピボットテーブルが出力されたシートに戻り

ピボットテーブルをクリックした状態で[データ]→[データ分析]→[ヒストグラム]→[OK]とし、ヒストグラムのダイアログボックスを出します。

[ヒストグラム]のダイアログボックスで、
[入力元]として、
[入力範囲]に「新規ユーザー」のデータ[$B$5:$B$34]、
データ区間として[$B$5:$B$34]、
出力先に、さきほど「リピーター」のヒストグラムが出力されたシートのC1セルを入力し、

[グラフ作成]にチェックをいれて、[OK]します。

※新規のヒストグラムが出力されるので、D1のセルを「新規」と入力しておく。

「リピーター」と「新規」の2つのヒストグラムが出来上がるので、「リピーター」のヒストグラムをクリックしアクティブにします。

[デザイン]タブの[データの選択]→[データソースの選択]で[編集]をクリックします。


[系列名]に見出し行の[B1]、
[系列値]にデータの範囲[B2:B13]を入力(「次の級」のセルを範囲から外す)して、[OK]をクリックします。

次に、[追加]をクリックし、「新規」のデータを足します。

 

[系列名]に見出し行の[D1]、
[系列値]にデータの範囲[D2:D13]を入力(「次の級」のセルを範囲から外す)して、[OK]をクリックします。

「リピーター」と「新規」がひとつのヒストグラムになりました。

ヒストグラムの見た目を整えます

ヒストグラムを選択し[書式]タブ[系列”リピーター”]→[選択範囲の書式設定]で[データ系列の書式設定]から[系列の重なり]を100%[要素の間隔]を0%にします。
また、[塗りつぶし]を[塗りつぶ(単色)]にし、任意の色を設定、[透明度]を60%程度にします。

続けて、ヒストグラムを選択し[書式]タブ[系列”リピーター”]→[選択範囲の書式設定]で[データ系列の書式設定]から[系列の重なり]を100%[要素の間隔]を0%にします。
また、[塗りつぶし]を[塗りつぶ(単色)]にし、任意の色を設定、[透明度]を60%程度にします。

データ系列の設定

データの書式設定

※系列の重なりと要素の間隔の設定で、2つの系列が重なってしまいます。
透明度を下げることにより、2つの系列に同じデータ系列にデータがあったときも透けて見えます。

というわけで、できました!
たぶん、これでいいはず・・・。とどきどきする見習です。みなさま、ごきげんよー。

Excelでヒストグラムを作る

ご無沙汰でございます。いまだに見習いの見習です。みなさま、いかがお過ごしでしょうか。

ところで最近、ウェブ解析に関わったりしているのですが、統計とか使うことになるので、Excelもがんばってみました。きっと、Excelできるッ。

いきなり、「時代はヒストグラムだ!」ということで、Excelでヒストグラムを作ることになりました(Excel2016使用)。

ヒストグラムってこんなの

ヒストグラムってこんなの

↑ヒストグラムって、こんなのです!
言われてみれば、見たことあるかも・・・。テストの点数とか、体重とか、身長とか、どのくらいの値の人がどんな感じにいるのかなー、って調べるときに使ったりするみたいです。(あと、アクセス解析では、日別の平均セッション数とか、モゴモゴモゴ……。)

この「ヒストグラム」、Excelで作ることができるんですって。あれまぁ、すごい。
ということで、やってみました。今回はExcel2016で作りましたが、それより前のバージョンでもいけまーす。

★Excelアドインの「分析ツール」を有効にする

ただ、このヒストグラム、Excelのアドオンの「分析ツール」を使うので、はじめて「分析ツール」を利用するときは、このアドインを有効にしておく必要があります。

Excel[ファイル]→[オプション]→[アドイン]→[分析ツール]を選んで、[OK]をクリックします。

これをすると、Excelの[データ]タブに[データ分析]が追加されます。

★ヒストグラムを作る

ではでは・・・。あとは動画でご説明です~。

0.A列にそれぞれのデータを入れておく

ここでは、A1:A100までデータが入っています。

1.B列に「データ区間」を入れる

データ区間とは、横軸の数字のことです。
とりあえず、最小値から最大値までを1刻みで刻んでいくヒストグラムを作ることにします。ここでは「0から始まって25までで1刻み」ことです。

  1. A列の最小値と最大値を求め、横軸の最小の値と最大の値になる数を決めます
  2. C列にMin関数とMax関数を使って、A列の最小値と最大値を求めます
  3. 上記で求めた最小値の値(0)をB1セルに入力します
  4. B1セルをアクティブにした状態で、[ホーム]→[連続データの作成]と進み、[連続データ]で「範囲」を「列」、「種類」を「加算」、「増分値」を「1」(1刻みにしたいから)、「停止値」を「25」(Max関数で出力されたのが25なので)として、「OK」をクリック

これで、B列に0から25まで、1ずつ増えていく連番が入力されました!

2.ヒストグラム作成画面で作業する

  1. A1セルをアクティブにした状態で、[データ]タブの[データ分析]をクリック。[ヒストグラム]へと進みます
  2. 「入力元」で[入力範囲]としてA1:A100、[データ区間]としてB1:B26を入力します
    それぞれ、[入力範囲]ではA1をクリックしてから、[データ区間]ではB1をクリックしてから、[Shift]+[Ctrl]+[↓]のキーを押下すると、最後の行まで選択できます。
  3. [新規ワークシート][グラフ作成]にチェックをつけて、[OK]します。

新しいシートにヒストグラムができました!!!

3.ヒストグラムの見た目を整える

あとは、見た目を整えていきます。

  1. 不要な「グラフ要素」を削除します
    凡例(はんれい:ここでは「頻度」)、「軸ラベル」(ここでは「頻度」と「データ区間」)を削除します

    • グラフ上でグラフ要素をクリックしてアクティブにした状態で[del]キーを押すだけで削除できます
  2. 系列「頻度」の棒の幅を隣の棒とくっつくようにします
    1. ヒストグラムを選択した状態で[書式]タブの[現在の選択範囲][系列”頻度”]を選び、[選択範囲の書式設定]をクリック
    2. [データ系列の書式設定][系列のオプション]から[要素の間隔]を[0%]にします
  3. 余分なデータ範囲を削除
    「次の級」というセル(A28:B28)が「グラフデータの範囲」に入っているので、範囲から除外します

    1. グラフをクリックし、アクティブにした状態にすると、データ範囲に青や紫などのカラーの囲み線が出ます。
    2. マウスカーソルが「黒い2方向の矢印」が出た状態で、この囲み線をドラッグすると、範囲を広げたり、狭めたりできます(「黒い4方向の矢印」ではないので注意。これだと範囲が移動してしまいます……)。

で、で、できた!(と思う・・・。)
やった~♥
でも、本当は、いい感じの「データ区間」をどんな感じに設定するか、とか、Excel以前の設定が大切なんですって・・・。

こりゃ、前途多難ですわ、がんばらなくっちゃ。

Excelで予定表を作る

ご無沙汰してます。相変わらず、「明日できること、今日すな」の見習いです。

なんだか、やることいっぱいだから、とりあえず予定表を作ってみよう!とトライしました。

こんなんできました(Excel2016使用)。

excel0909-1

毎月、きちっと予定表を作って管理すれば、できる見習い?てな感じです。
ささっと仕事ができるように、カレンダーを毎月作り直す必要がないように、ですです。時短です。
てなことで、2017年9月の予定表を作るときには、G1セルに「2017」、I1セルに「9」と入力するだけで、B3:C33まで、自動で日付と曜日が入ります。
そして、土曜日の行は青っぽく、日曜日の行は赤っぽく塗りつぶされることで、カレンダー感が出ます。そして、本日については、真っ赤に塗りつぶされることで、緊迫感が出てます。

これは、おもに「関数」と「条件付き書式」でできてます。
次にささっと作れるように、ビボー録です。

 

まずは、関数・・・・・・

★年と月を入れるだけで、自動で表示される

excel0909-2

入っている関数は、これだけ。まず、B3に「=DATE(G1,I1,1)」と入力。
これは、G1やI1を参照して、月初の1日目を取得します。
そして、B4に「=B3+1」が入ります。これは、B3の次の日ということ。Excelでは、1が1日となるので、これでOK。
あとは、オートフィルで式を下へコピーすれば大丈夫!

 

★一応、月末処理も・・・

でもでも、30日とか31日とか、月によって違うし、2月に至っては、どうなるのよ!って問題があります。

で、思いついたのが、B31からB33の式です。
B31には「=IF(B30=””,””,IF(MONTH(B30+1)=$I$1,B30+1,””))」としました。
これは、
「まず、直前の日のセルが空白だったらこのセルも空白よ!」ということからスタート。これは、本当はこのセルには入力する必要がないのだけど、その下2つのセルと同じにしておいたほうが管理上楽かなということと、オートフィルを使いたかったから。本来は、B32以降のセルに入れておけばよいものです。

で、上のセルが空白ではなかったら、次は「上のセルの日の次の日の月がI1で入力した月と同じであれば、上のセルの日に一日足した日を返しなさい。そうでなければ、空白よ!」という意味です。つまり、9月の予定表を作っているとき、単純に前の日を1日足していくと、10月1日の予定表が出てきてしまうことがあります。それを防ぐために、「もし、そのセルに表示される日が、次月ならば、空白にしておいてね♪」ということをしてます。

この3つのセルだけに、この式を入れたのは、31日分作った表の中で、一番短くなる可能性のある2月28日の次の日以降を処理するため。29日以降のセルで処理すればよいからです。

なお、C列については、B列をそのまま返しています。こんなことしないで「表示形式」でひとつのセルでやればいいじゃん!っていう突っ込みがあるかもですが、まあ、そこはご愛嬌ということで・・・・・・。許してください。なんとなくです。なんとなく。

 

あとは、「条件付き書式」で曜日と本日

条件付き書式で一気にいけます。

前にも、条件付き書式で、悩んだことがあるので、そのリンクはこちら。

まず、条件付書式を設定したいセル「B3:E33」を選択します。

excel0909-2-2

その状態で、「ホーム」→「条件付き書式」→「新しいルール」と進んで、「数式を使用して、書式設定するセルを決定」をクリックし、「次の数式を満たす場合に値を書式設定」に式を入れ、「書式」から「セルの書式設定」→「塗りつぶし」で塗りつぶしの色を設定していく流れです。

excel0909-2-3excel0909-2-4

「B3:E33」に設定している書式はこんな風です。

excel0909-3
上から、「本日なら、赤く塗りつぶしてね」です。なお、本日の塗りつぶしが行われた場合、曜日での色分けは無効にしたいので、「条件を満たす場合は停止」にチェックが入っています

次が、土曜日の場合に青っぽく塗りつぶす条件付き書式

その次が、日曜日の場合に赤っぽく塗りつぶす条件付き書式です。

 

★「本日なら、赤く塗りつぶしてね」

の式は、「=$B3=TODAY()」です。「$B3」と複合参照になっているのは、この条件に当てはまる場合には、その行のC、D、E列にも書式を適用してほしいから。Bの前に$マークをつけて、B列を固定させるけど、条件付き書式を下にコピーしたときには、4行目5行目と条件を移動させたいので、3の前には$をつけていません。

excel0909-4

★土曜日なら青っぽく塗りつぶす

の式は、「=WEEKDAY($B3)=7」です。B3をWeekday関数を使って、曜日を取得します。Weekday関数は、このように書くと、その曜日を日曜日を1として月曜日は2、火曜日は3というように、数字で返してくるので、土曜日の場合は、7と返します。だから「、7という数字が返ってきたときには、青っぽくしてね」となります。ここでも、「$B3」と複合参照で書いておきます。理由は、「本日なら、赤く塗りつぶしてね」と同じ。

excel0909-5

★日曜日なら赤っぽく塗りつぶす

の式は、「=WEEKDAY($B3)=1」です。これは、「土曜日なら青っぽく塗りつぶす」を日曜日に変えただけなので、理由もまったく同じ。おほほほほ。

excel0909-6

と、こんな感じで、予定表を作ってみました。どうでしょ。
おおお、気づいたら、もう、こんな遅い時間ではありませんか。予定表を作ったところで、本日も終了! あれ??????

 

前途多難な見習いです。皆様、ごきげんよう・・・・・・。(きゃー、どうしよう)