エクセルピボットをBIツールのようにつかう – 2.5 – 日付のグループ化
エクセルをBIツールのように活用する方法を解説しています。使用するのはエクセルの標準機能 (マクロは使用ナシ) だけ！
今回は、日付をグループ化する方法について解説しています。
目次
日付の自動グループ化
日付のグループ化とは
「年/月/日」の日付を「年」や「月」にグループ化します。日付をグループ化、例えば、シンプルな操作で日別データを月ごとに集計できる、タイムラインやスライサーで目的の月を抽出できるなど多くのメリットがあります。
ピボットには、画像のように分析するデータへグループ列を追加しなくても自動で日付をグループ化できる機能があります。
ピボットのこの機能を活用すれば、データへグループ列を追加するような手順を省略することができます。
ただし、必要とするすべてのグループ化を自動でできるわけではありません。分析するデータにあわせて、自動・手動・マスタとのリレーションなど組み合わせながらグループ化します。
グループ化しなければ日付軸のチャートは日付の日だけ。
グループ化することで、月を軸へ、年を凡例へ。
自動グループ化しないとき
ある程度の期間を越える日付列をピボットで自動グループ化することができます。便利な機能なので使わない手はないのですが、自動グループ化したくないときはピボットを挿入する前に自動グループ化を無効にすることができます。
・「ホーム」
・「その他」から「オプション」を選択します。
・「詳細設定」
・「ピボットテーブルで日付/時刻列の自動グループ化を無効にする」チェックしてOKです。
自動グループ化とデータ
今回のデータ期間は２年間、日付別です。
日付は「年」「四半期」「月」「日」に自動でグループ化されます。ただし、データ期間の長短で自動グループ化される内容が異なります。
・データ期間が「ある程度以内」のときは「年」「四半期」が自動でグループ化されない。
・データ期間が「ある程度以上」のときは「日付」が自動でグループ化されない。
このようなことがあります。
「ある程度」というのは、何かのアルゴリズムがあるのだろうと想像していますが、何年・何か月・何日というのがはっきりしません。１年間のデータで日別データで試してみると、「年」「四半期」が自動でグループ化されませんでした。
自動グループ化手順
ピボットテーブルを挿入します。ピボットグラフのみでも自動グループ化はできますが、グループ化の手動調整ができません。
・「日付」を「軸」フィールドへドラッグ＆ドロップします。
四角へチェックを入れると「凡例」フィールドへ入ってしまうことがあります。「凡例」フィールドへは最大255項目しか入りません。255日以上のときは「軸」フィールドへドラッグ＆ドロップしてください。
「年」「四半期」「月」「日付」このように粒度が粗いグループから粒度が細かいグループが自動作成されます。
自動グループ化の手順はこれだけです。
自動グループ化を手動で調整
データ期間の長短によりグループ化の具合がことなります。手動で調整します。
・ピボットテーブルの「年」のセルを右クリック
・「グループ化」を選択します。
開いた窓のなかで、グループ化する単位をクリックして青色に、グループ化しない単位をクリックして白色にしてOKです。
タイムライン・スライサー
タイムライン
・ピボットテーブルのどこかにカーソルを合わせます。
・「分析」タブ
・「タイムラインの挿入」
・開いた窓の「日付」をチェック
・「月」の右側の▼三角をクリック
・グループ化した単位で表示を変更できます。
スライサー
・ピボットテーブルのどこかにカーソルを合わせます。
・「分析」タブ
・「スライサーの挿入」
・開いた窓の項目をチェックします。
スライサーが挿入できれば成功です。
自動グループ化できない単位
日付の日、曜日、週数
画像のように、日付の月を取り出すことができます。１月の売上売価は2019年1月と2020年1月の合計です。
日付の日を軸に設定すると「月＆日」になることがわかります。「日」を単独で取り出せません。
例えば、毎月20日と30日はポイント5倍デーになっているとすると、2年間の毎月20日と30日をフィルターかなにかで、ちまちまと選んで分析する・・・
日付列を複製して表示形式を変更する。この方法で日付の日だけを取り出すことはできません。
エクセルピボットでグループ化ができるのは「年」「四半期」「月」「日付(月＆日)」です。
「日」「週」「曜日」のグループ化はできません。
関数で列を複製する
セルの表示形式変更では「日」や「曜日」でグループ化することはできません。関数を使用してグループ列を作成します。
#日を作成 =DAY(列名) #曜日を作成 =TEXT(列名,"aaa") #週数を作成 =WEEKNUM(列名,1) #1は日曜日スタート、2は月曜日スタート
作成した日付の日をスライサーへ設定します。20日と30日だけを選択すると、ポイント5倍デーの実績だけを取り出すことができます。
計算列でグループ化できないとき
例えば、祝祭日をグループ化したいとき、計算列を作成する方法では不可能です。日付を日本の祝祭日へ変換する関数がありません。
祝祭日に該当する日をポチポチ選んで手動でグループ化する方法はあるものの、もちろんオススメしません。
オススメするのはカレンダーマスタとのリレーションです。この方法であれば、日付を「日」「曜日」「週数」へ変化した計算列さえも作成する必要がなくなります。
カレンダーマスタはこちらの記事を参考にしてください。
