商品の販売履歴が記録されていく [販売履歴] テーブルがあり、商品名や商品の価格がまとめられた [商品マスター] テーブルと関連付けてピボットテーブルで集計を行いたいため、データ モデルに [販売履歴] テーブルと [商品マスター] テーブルを追加してリレーションシップを設定し、このデータ モデルをもとにピボットテーブルを作成しています。
このピボットテーブルで、年度や四半期、月ごとの集計が、自身の求めている期間に合致するように日付テーブルを作成することがありますが、今回は Power Pivot for Excel の機能を使って新規作成する手順についてご紹介します。
なお、途中でも書いてありますが、この作成方法の場合は [販売履歴] テーブルに現在の日付テーブルの範囲外のデータが追加されても、[予定表] テーブルの日付の範囲が自動的に変わらないため、手動で範囲を更新する必要があります。(範囲変更についてはまた別途)
日付テーブル (予定表) を作成する
PowerPivot for Excel を起動してデータ モデルに [予定表] という名前の日付テーブルを新しく作成します。
1.リボンの [データ] タブの [データ ツール] グループの [データ モデルの管理] をクリックします。
2.PowerPivot for Excel が起動します。
[販売履歴] テーブルの最初の日付 (ここでは 2024/4/7) と、最後の日付 (ここでは 2025/3/23)を確認します。
3.リボンの [デザイン] タブの [予定表] グループの [日付テーブル] をクリックし、[新規作成] をクリックします。
4.[予定表] という名前の日付テーブルが作成されます。
5.[予定表] テーブルの [Date] 列を確認します。
「2024/1/1」から「2025/3/31」までの日付が、1 日の欠損もなく用意されていることがわかります。
[予定表] テーブルの [Date] 列には、[販売履歴] テーブルに存在している日付の開始日の年の 1 月 1 日から、終了日の年の 12 月 31日までが用意されています。
活動履歴を記録しているテーブル (ファクト テーブル) にある日付の範囲をもとにして自動的に必要な期間の日付を含む列が作成されている、ということです。
ただし、この作成方法の場合は、[販売履歴] テーブルに現在の日付テーブルの範囲外のデータが追加されても、[予定表] テーブルの日付の範囲は自動的には変わりません。手動で範囲を更新する必要があります。
列を編集する
[予定表] には [Date] 列の日付を活かした数式を使って、[年] や [月の番号]、[MMM-YYYY] などの列が作成されています。
現在は月の表記が「Jan-2024」などの英語表記であるため、「2024年1月」のような表記にすることを例に列の数式を編集します。
1.[MMM-YYYY] 列を選択すると、数式バーに数式が表示されます。
2.「Jan-2024」が「2024年1月」と表示されるように、数式バーで下記の数式に編集して [Enter] を実行するか、[コミット] をクリックします。
DAX の FORMAT 関数は、Excel のTEXT 関数に似た、値に表示形式を適用して文字列を返す関数です。
=FORMAT([Date] , "YYYY年M月")
3.数式が編集され、「YYYY年M月」の形式で表示されます。
4.列名を編集する場合は、列名の部分 (ここでは [MMM-YYYY]) をダブル クリックして変更する列名 (ここでは「年-月」)を入力して [Enter] を実行します。
5.不要な列を削除する場合は、列 (ここでは [月]) を右クリックして [列の削除] をクリックします。
6.図のメッセージで [はい] をクリックします。
7.列が削除されます。
ここまでの操作で [予定表] テーブルは図のような状態になっています。
リレーションシップを設定する
作成した [予定表] テーブルと集計したいデータを持っている [販売履歴] テーブルの間にリレーションシップを設定します。
1.リボンの [ホーム] タブの [表示] グループの [ダイアグラム ビュー] をクリックします。
2.[販売履歴] テーブルの [日付] フィールドを [予定表] テーブルの [Date] フィールドへドラッグ アンド ドロップします。
3.リレーションシップが設定されます。
4.[ブックに切り替え] をクリックして、ピボットテーブルの作成されているブックのシートをアクティブにします。
(上書き保存して PowerPivot for Excel を閉じてもよい)
5.[ピボットテーブルのフィールド リスト] を [すべて] にして、[予定表] テーブルが追加されていることを確認します。 
6.[予定表] の [Date] フィールドを [列] エリアへバインドすると、日付ごとの集計値が表示されます。
7.[予定表] の [年-月] フィールドを [列] エリアの [Date] フィールドの上へバインドすると、月ごとの集計値が表示されます。
このとき「2024年10月」が左端 (先頭) に配置されているのは、[年-月] フィールドの値が文字列 (テキスト) で、なおかつ左から昇順で配置されているためです。移動をして並び順を変更することもできますが、次の手順でご紹介している [年度] や [四半期] といったフィールドを作成して解決することもできます。
[年度] と [四半期] を作成する
データ モデルに [四半期] 列と [年度] を追加して、上位の単位での集計ができるようにします。
1.PowerPivot for Excel を起動します。
リボンの [データ] タブの [データ ツール] をグループの [データ モデルの管理] をクリックします。
2.追加する列の名前を決定するには、[列の追加] と表示されている見出しをダブル クリックして編集できる状態にし、列名 (ここでは [年度]) を入力して [Enter] を実行します。
3.列名が変更されます。
4.ここでは 4月から新しい年度とすることを例にします。
IF 関数を使って [月の番号] フィールドの値が 「3 以下 (つまり 1 月から 3 月)」の場合は、[年] から 1 マイナスした値を返し、それ以外 (つまり 4 月から 12月) の場合は [年] をそのまま返して、後ろに「年度」という文字列を結合します。次の数式を数式バーに入力して作成します。
=IF([月の番号]<=3 , [年]-1 , [年]) & "年度"
5.[年度] 列が作成されます。
「2024/4/1」から「2024年度」、すなわち 4 月から年度が変わっていることが確認できます。
6.[四半期] 列を作成します。
IF 関数を使って [月の番号] フィールドの値が 「3 以下 (1 月から 3 月)」の場合は「Q4」、「6 以下 (4 月から 6 月)」の場合は「Q1」、「9 以下 (7 月から 9 月)」の場合は「Q2」、それ以外は「Q3」という文字列が返るように、次の数式を数式バーに入力して作成します。
=IF([月の番号]<=3, "Q4", IF([月の番号]<=6, "Q1", IF([月の番号]<=9, "Q2", "Q3")))
7.[四半期] 列が作成されます。
「2024/4/1」が「Q1」と表示されていることなどが確認できます。
8.Excel ブックに切り替えて、ピボットテーブルの作成されているブックのシートをアクティブにします。
[ピボットテーブルのフィールド リスト] を [すべて] にして、[予定表] テーブルに [年度] フィールドと [四半期] フィールドが追加されていることを確認します。
9.[年度] フィールドと [四半期] フィールドを [行] エリアの [年-月] フィールドの上へバインドします。
10.ここでは 2024年度のデータのみが含まれていますが、[年度] － [四半期] － [年-月] － [Date] (日付) という階層で集計ができていることが確認できます。
もちろん今回ご紹介した列だけでは自分の求めている集計の切り口が足りない、ということもあるでしょう。PowerPivot for Excel には日付テーブルをすばやく作成できる機能があるということや、列は編集するだけでなく新しく追加できること、リレーションシップが必要である、といった基本的な内容をご紹介しました。
いままで Excel のピボットテーブルしか作ったことがないよ、という方で日付を自身の求めている期間 (年度など) で集計したいという方は試してみてください。
石田 かのこ
