Power Pivot for Excel 日付テーブルを作成する方法

Power Pivot の日付テーブルは、時系列でデータを参照して計算する場合に欠かせません。 この記事では、Excel の Power Pivot で日付テーブルを作成する方法について勉強します。

前回記事は Power Pivot 計算領域にメジャーを作成して利用する方法でした。
下のカードをクリックすれば開きます。よければご覧ください(^^ゞ

日付テーブルとは

日付テーブルとは、データモデル内の必要な期間にわたる連続した日付の列があるテーブルです。

暦年でレポートする必要がある場合は、指定された期間を含む暦年の範囲すべての日が含まれている必要があります。たとえば、データの期間が「2019年4月1日から2021年9月30日」までの場合は、「2019年1月1日から2021年12月31日」までのすべての日付を順番に含んでいる必要があります。

日付テーブルには、日付、月、四半期、年度、週、曜日などの列を含めることができます。
次の画像は、今回設定するサンプルの日付テーブルです。

Power Pivot for Excel の日付テーブル作成方法

Power BI なら DAX で手軽に日付テーブルを作れるようです。CALENDER関数やCALENDARAUTO関数を使って作成しますが、残念ながら Power Pivot for Excel でやろうとするとエラーになります。いろいろ試しましたが、どうやらサポートされていないようです。

Excel の Power Pivot で日付テーブルをデータ モデルに追加する方法は、Power Query 側で日付テーブルを作る方法のほかに、次のような方法でも作成できるようです。

• 標準機能で付いている「日付テーブル」を利用して作成する
• Excelで日付テーブルを作り、Power Pivot に貼り付けてテーブルを作成する

今回は、この二つの方法について実際に試してみましょう。

標準機能[日付テーブル]で日付テーブルを作成する

では、さっそくやってみましょう。

・Power Pivot for Excel の [デザイン]タブ > [予定表]グループ > [日付テーブル]リボン > [新規作成] を選択・実行します。

・日付テーブルを自動作成している動画です。

・動作は、始めに[Date]列が作成されます。作成される日付はどうやらデータモデル内のテーブルで使われている日付を基準にして作成されるようです。

・[Date]列をベースに次々と計算列が追加されていく動作を確認することができます。

・「予定表」という日付テーブルが作成され、次の７つの列が出来上がっています。
　[Date] [年] [月の番号] [月] [MMM-YYYY] [曜日の番号] [曜日]
　※ これが規定のようですが、データモデルによって作成される列の種類は変わるようです。

・列の計算式は左から順番に、[Dateは計算式なし] [年=YEAR([Date])] [月の番号=MONTH([Date])] [月=FORMAT([Date],”MMMM”)] [MMM-YYYY=FORMAT([Date],”MMM-YYYY”)] [曜日の番号=WEEKDAY([Date])] [曜日=FORMAT([Date],”DDDD”)]

・DAX の CALENDAR関数で生成される計算テーブルと同じような感じで作成されますが、モデルのデータ列の間にリレーションシップは自動作成されません。別途手動で設定する必要があります。

・[Date]列に格納されている日付値は完全なカレンダー年となっています。モデル内の日付列の最初の値が2021年5月1日でしたので、2021年1月１日から2021年12月31日までのカレンダー年の各日付が 連続した単一の日付範囲列となっています。

・[日付テーブル]リボン > [範囲の変更] で [Date]列のデータ範囲を変更することができます。

・自動作成された計算列の計算式を変更すれば、欲しいデータに変更することができます。
　例えば、[=FORMAT([Date],”DDDD”)] では曜日が英語表記になっていますが、
　　　　 [=FORMAT([Date],”AAAA”)] と変更すれば日本語表記に変更できます。

日付表示の書式設定文字

FORMAT関数で使用する日付の書式設定に使用する文字の一覧がこちらです。

書式文字	表示内容
d	先頭に 0 を付けずに数値で日を表示します (1 – 31)
dd	先頭に 0 を付けて数値で日を表示します (01 – 31)
ddd	曜日を省略形で英語表示します (Sun – Sat)
aaa	曜日を省略形の日本語表示します (日 – 土)
dddd	完全な名前で曜日を英語表示します (Sunday – Saturday)
aaaa	完全な名前で曜日を日本語表示します (日曜日 – 土曜日)
w	曜日を数値で表示します (日曜日＝1、土曜日＝7)
ww	1 年の何週目であるかを数値 (1 – 54) で表示します
m	先頭に 0 を付けずに数値で月を表示します (1 – 12)
mm	先頭に 0 を付けて数値で月を表示します (01 – 12)
mmm	省略形で月を表示します (Jan – Dec)
mmmm	完全な月の名前を表示します (January – December)
q	年の何番目の四半期かを数値 (1 – 4) で表示します
y	年の通算日を数値で表示します (1 – 366)
yy	年を 2 桁の数字で表示します (00 – 99)
yyyy	年を 4 桁の数字で表示します (100 – 9999)

計算列は追加設定できます（削除も可能）

必要に応じて「年度」や「四半期」などの計算列を追加設定することができます。

・例として[年度] と [四半期] 列を追加してみました。設定した計算式は次のとおりです。

列名	計算式
年度	=IF([月の番号]<=3,[年]-1,[年]) ※「年度」と表記したい場合は =IF([月の番号]<=3,[年]-1,[年]) &”年度” ※ 年度の開始が9月(8月期決算)の場合は「３」 を「8」に変更します

【計算式解説】
　＝IF([月の番号]<=3　←　月の番号が3以下かどうかの条件設定
　,[年]-1 　 ←　Trueの場合、[年] を1マイナスして表示します 　　　　　　　
　,[年]) ←　Falseの場合、[年] をそのまま表示します

列名	計算式
四半期	=IF([月の番号]<=3,“Q4”,IF([月の番号]<=6,“Q1”,IF([月の番号]<=9,”Q2″,“Q3”))) ※ 日本語で表記したい場合は “Q4” → “第4四半期” のように変更します ※ 年度の開始が9月(8月期決算)の場合は “Q4” を “Q2” のように+2づつ調整します

【計算式解説】 年度の開始が４月の場合（3月期決算）
　※ [月の番号] を小さい順に判定していくようにしています
　=IF([月の番号]<=3,“Q4”　←　条件「月番号が3以下」の場合、True なら “Q4”
　,IF([月の番号]<=6,“Q1”　←　Flase の場合条件2「月番号6以下」がTrue なら “Q1”
　,IF([月の番号]<=9,”Q2″　←　Flase の場合条件3「月番号9以下」がTrue なら “Q2”
　,“Q3”)))　←　Flase の場合 “Q3”

もちろん不要な列は削除することもできます。

Excelで作った日付データを貼り付けて日付テーブルを作成する

Excel で連続した単一の日付範囲列を作成してデータモデルにコピーする方法は、データモデルに日付テーブルを作成する一番簡単な方法です。早速やってみましょう。

Excel での作業手順は次のとおりです。

1. Excelで新しいワークシートを用意します
2. 列の最初の行A1セルに、列見出し「Date」等を入力します
3. 同じ列の2行目A2セルに、作成する日付範囲の最初の日付を入力します
4. 日付を入力したA2セルを選択し、フィルハンドルをクリックして下にドラッグします
5. 必要な日付範囲になるまでドラッグすれば、連続した日付範囲列の作成完了です
6. この範囲をテーブルにしておいても良いでしょう

ここからは Power Pivot のデータモデルにコピーする作業です。

1. Excel で作成した連続した日付範囲列（テーブル）を選択し、クリップボードにコピーします
2. 貼り付け先の「Power Pivot for Excel」ウィンドウを表示します
3. [ホーム]タブ > [クリップボード]リボン > [貼り付け]をクリックします
4. すると「貼り付けプレビュー」ダイアログが表示されます

設定後 [OK] を選択します。

作成されたテーブルがこちらです。

単一列のテーブルです。

これで、クリップボードの内容がデータモデルに新しく「日付テーブル」として追加されました。

ただし、追加されたテーブルは [Date] のみの単一列ですので、[列の追加] で必要な計算列を追加して成形していく必要があります。

確かにデータモデルに日付テーブルを作成する方法としては一番簡単ですが、作成後の成形には少し時間がかかりそうですね(^^ゞ

DAX タイムインテリジェンス関数を使えるようにする

「タイムインテリジェンス関数」とは、「日、月、四半期、年など」の期間(タイム) を使用してデータを操作(インテリジェンス)する関数です。各関数の詳細についてはここでは触れませんが、期間に対する累計や比率などを計算して比較する際に利用できる非常に便利な関数です。

タイム インテリジェンス関数 (DAX) – DAX | Microsoft Docs のリファレンスへのリンクです。

「タイムインテリジェンス関数」の詳細については、別途勉強して解説できるようにしたいと思いますが、日付テーブルをきちんと設定しておく必要があるようですので設定していきましょう。

日付テーブルとしてマークする

DAX のタイムインテリジェンス関数を計算で使用するときは、日付テーブルのプロパティを指定する必要があります。

日付テーブルのプロパティでは、タイムインテリジェンス関数が正確に機能するうえで必要なメタデータを設定します。そうしておくことで、計算を実行するときに Power Pivot の数式エンジンは既定のプロパティに従って、必要な日付を取得します。

日付テーブルのプロパティを設定する方法

1. Power Pivot ウィンドウで、日付テーブルを選択します
2. [デザイン] タブで、[日付テーブルとしてマーク] をクリックします
3. [日付テーブルとしてマーク] ダイアログ ボックスの [日付] ボックスの一覧で、一意の識別子とする列(既定で [Date] 列が選択されます)を選択し [OK] をクリックします
4. [日付テーブルの設定] で設定を変更することができます

「一意の値」ではない列を選択すると、このメッセージが出て設定できないようになっています。

リレーションシップを設定します

データモデル内で「日付テーブル」と「日付列を持つテーブル」との日付列間で「一対多」の「リレーションシップ」設定も忘れずに行いましょう。

「ダイアグラムビュー」で設定した画像がこちらです。

これで DAX 「タイムインテリジェンス関数」が利用できるようになりました。

「リレーションシップ」を設定することでピボットテーブルなどで使用できるようになります。

まとめ（おわりに）

以上、Power Pivot for Excel で日付テーブルを作成・設定する方法について、勉強しながら解説してきました。

まとめと感想など

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/

各記事のサンプルファイルをダウンロードできます

今回記事はサンプルファイルの登録はありません！
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください