エクセルピボットをBIツールのようにつかう – 2.5 – 日付のグループ化

エクセルピボットをBIツールのようにつかう – 2.5 – 日付のグループ化

エクセルをBIツールのように活用する方法を解説しています。使用するのはエクセルの標準機能 (マクロは使用ナシ) だけ! 
今回は、日付をグループ化する方法について解説しています。

日付の自動グループ化

日付のグループ化とは
日付のグループ化

 「年/月/日」の日付を「年」や「月」にグループ化します。日付をグループ化、例えば、シンプルな操作で日別データを月ごとに集計できる、タイムラインやスライサーで目的の月を抽出できるなど多くのメリットがあります。

 ピボットには、画像のように分析するデータへグループ列を追加しなくても自動で日付をグループ化できる機能があります。
 ピボットのこの機能を活用すれば、データへグループ列を追加するような手順を省略することができます。

 ただし、必要とするすべてのグループ化を自動でできるわけではありません。分析するデータにあわせて、自動・手動・マスタとのリレーションなど組み合わせながらグループ化します。

日付軸

 グループ化しなければ日付軸のチャートは日付の日だけ。

グループ化でできること

 グループ化することで、月を軸へ、年を凡例へ。

自動グループ化しないとき
「ホーム」メニュー

 ある程度の期間を越える日付列をピボットで自動グループ化することができます。便利な機能なので使わない手はないのですが、自動グループ化したくないときはピボットを挿入する前に自動グループ化を無効にすることができます。
・「ホーム」
・「その他」から「オプション」を選択します。

詳細設定

・「詳細設定」
・「ピボットテーブルで日付/時刻列の自動グループ化を無効にする」チェックしてOKです。

自動グループ化とデータ
データ

 今回のデータ期間は2年間、日付別です。

 日付は「年」「四半期」「月」「日」に自動でグループ化されます。ただし、データ期間の長短で自動グループ化される内容が異なります。

・データ期間が「ある程度以内」のときは「年」「四半期」が自動でグループ化されない。
・データ期間が「ある程度以上」のときは「日付」が自動でグループ化されない。
 このようなことがあります。

 「ある程度」というのは、何かのアルゴリズムがあるのだろうと想像していますが、何年・何か月・何日というのがはっきりしません。1年間のデータで日別データで試してみると、「年」「四半期」が自動でグループ化されませんでした。

自動グループ化手順
ピボットテーブル

 ピボットテーブルを挿入します。ピボットグラフのみでも自動グループ化はできますが、グループ化の手動調整ができません。

・「日付」を「軸」フィールドへドラッグ&ドロップします。
 四角へチェックを入れると「凡例」フィールドへ入ってしまうことがあります。「凡例」フィールドへは最大255項目しか入りません。255日以上のときは「軸」フィールドへドラッグ&ドロップしてください。

自動グループ化

 「年」「四半期」「月」「日付」このように粒度が粗いグループから粒度が細かいグループが自動作成されます。

 自動グループ化の手順はこれだけです。

自動グループ化を手動で調整
手動調整

 データ期間の長短によりグループ化の具合がことなります。手動で調整します。
・ピボットテーブルの「年」のセルを右クリック
・「グループ化」を選択します。

グループ化の選択

 開いた窓のなかで、グループ化する単位をクリックして青色に、グループ化しない単位をクリックして白色にしてOKです。

タイムライン・スライサー

タイムライン
タイムライン

・ピボットテーブルのどこかにカーソルを合わせます。
・「分析」タブ
・「タイムラインの挿入」
・開いた窓の「日付」をチェック

タイムラインの表示変更

・「月」の右側の▼三角をクリック
・グループ化した単位で表示を変更できます。

スライサー
スライサー

・ピボットテーブルのどこかにカーソルを合わせます。
・「分析」タブ
・「スライサーの挿入」
・開いた窓の項目をチェックします。

スライサー

 スライサーが挿入できれば成功です。

自動グループ化できない単位

日付の日、曜日、週数
日付の月

 画像のように、日付の月を取り出すことができます。1月の売上売価は2019年1月と2020年1月の合計です。

日付の日

 日付の日を軸に設定すると「月&日」になることがわかります。「日」を単独で取り出せません。
 例えば、毎月20日と30日はポイント5倍デーになっているとすると、2年間の毎月20日と30日をフィルターかなにかで、ちまちまと選んで分析する・・・

表示形式変更

 日付列を複製して表示形式を変更する。この方法で日付の日だけを取り出すことはできません。

グループ化

 エクセルピボットでグループ化ができるのは「年」「四半期」「月」「日付(月&日)」です。
 「日」「週」「曜日」のグループ化はできません。

関数で列を複製する
計算列を追加

 セルの表示形式変更では「日」や「曜日」でグループ化することはできません。関数を使用してグループ列を作成します。

#日を作成
=DAY(列名)

#曜日を作成
=TEXT(列名,"aaa")

#週数を作成
=WEEKNUM(列名,1)
#1は日曜日スタート、2は月曜日スタート
日付の日を取り出す

 作成した日付の日をスライサーへ設定します。20日と30日だけを選択すると、ポイント5倍デーの実績だけを取り出すことができます。

計算列でグループ化できないとき
完全手動グループ化

 例えば、祝祭日をグループ化したいとき、計算列を作成する方法では不可能です。日付を日本の祝祭日へ変換する関数がありません。

 祝祭日に該当する日をポチポチ選んで手動でグループ化する方法はあるものの、もちろんオススメしません。

 オススメするのはカレンダーマスタとのリレーションです。この方法であれば、日付を「日」「曜日」「週数」へ変化した計算列さえも作成する必要がなくなります。

 カレンダーマスタはこちらの記事を参考にしてください。