Power BI Desktop や Excel の ピボットテーブルで日付を軸にした集計をするときに、求めている期間に合致するようにするために「日付テーブル」を用意することがあります。
Power BI Desktop で使用するにしても Excel で作ってインポートするほうがやりやすいな、という方も少なくないようなので Excel で日付テーブルを作成するときによく使われる列とその列を作成するための数式例を書いてみました。
必ずしもこの数式でなければならないということではないし、自分は 7 月 ～ 9 月を第 1 四半期にしたいのに・・・なんていう場合にはもちろん手を加えていただかないといけませんが、参考にはしていただけるのではないでしょうか。
なるべく一般的な Excel ユーザーが使いやすそうな関数を使ってみました。
今回は、4 月 ～ 6 月を第 1 四半期とすることを例にし、表はテーブルに変換して [日付テーブル] という名前を設定しています。
日付テーブルは集計したい期間内の日付が 1 日の欠損もないように作成しなければいけないので、下図はそれに該当しませんが、四半期や年度が思い通りに変わっていることを見ていただくために各月の 1 日 (ついたち) をテーブルに含めています。
■ [月] 列
MONTH 関数を使って [日付] 列のシリアル値の「月」を返す数式を作成しています。
数式例
=MONTH([@日付])
■ [年月] 列
TEXT 関数を使って [日付] 列のシリアル値の「年」と「月」に文字列を組み合わせて「〇〇〇〇年●●月」という文字列を返す数式を作成しています。
数式例
=TEXT([@日付],"yyyy年mm月")
■ [会計年度] 列
会計年度の先頭に「FY」を追加した文字列を返す数式を作成しています。
1月から3月が直前の12月と同じ年度になるため、[日付] 列のシリアル値の「年」から 1 (年) マイナスするように IF 関数で分岐しています。
数式例
="FY"&IF([@月]<4,YEAR([@日付])-1,YEAR([@日付]))
■ [四半期] 列
IFS 関数を使用して、[月] 列の数値が 4 より小さければ「第4四半期」、7 より小さければ「第1四半期」、10 より小さければ「第2四半期」、その他 (10 ～ 12) は「第3四半期」を返す数式を作成しています。
レポートで「第1四半期」ではなく「Q1」などの表記を使用したい場合は、使用したい文字列に置き換えてください。
数式例
=IFS([@月]<4,"第4四半期",[@月]<7,"第1四半期",[@月]<10,"第2四半期",TRUE,"第3四半期")
■ [会計年度-四半期] 列
[会計年度] 列の文字列と「-Q」という文字列、[四半期] 列の左から ２ 文字目、これらを結合して、会計年度と四半期を「-」 (ハイフン) でつないだ結果を返す数式を作成しています。
(ハイフンはあってもなくてもお好みで)
複数年度分のデータを 1 つにまとめてレポートするときに、会計年度と四半期がわかる軸を作成する場合などに用意します。
数式例
=[@会計年度]&"-Q"&MID([@四半期],2,1)
■ [週番号] 列
WEEKNUM 関数を使って [日付] 列のシリアル値が、その年の何週目に含まれるのかを対応する週番号で返す数式を作成しています。月曜日を週の始まりとするために第2引数で「2」を指定しています。
週単位での集計を行うときに作成して使用します。
数式例
=WEEKNUM([@日付],2)
■ [曜日番号] 列
WEEKDAY 関数を使って [日付] 列のシリアル値の曜日番号を返す数式を作成しています。
月曜日を週の始まりとするために、第2引数で「2」を指定しています。
この数式は、[曜日] 列に曜日を表示するときに使用したり、並べ替えのキーとして使用したりします。
週単位での集計を行うときに作成して使用します。
数式例
=WEEKDAY([@日付],2)
■ [曜日] 列
SWITCH 関数を使って [曜日番号] 列の数値に該当する曜日を返す数式を作成しています。
たとえば、曜日番号が「1」だったら「月」が返ります。
[曜日] 列は、「=TEXT([@日付],"aaa")」のように TEXT 関数を使用して曜日を表示してもよいです。
週単位での集計を行うときに作成して使用します。
数式例
=SWITCH([@曜日番号],1,"月",2,"火",3,"水",4,"木",5,"金",6,"土",7,"日")
[年月] のデータ型
Power Query エディターで見ていただくとわかるのですが、今回作成している [年月] 列は、データを読み込んだときに「日付」型に変換されます。
セルに作成した「〇〇〇〇年●●月」という文字列で使用するには、データ型を「テキスト」に変更してください。
列タイプの変更のメッセージが表示されたら [現在のものを置換] を実行してよいです。
Excel ではなく Power BI のデータ モデルにカレンダー テーブル作るよ、という方もほぼ使用できる関数に違いはないでしょう。今回の例のなかだと、TEXT 関数ではなくて FORMAT 関数で対応するくらいでしょうか。
集計したいレポートに合わせて日付テーブルを作成しなければならない、というときのためにこのあたりの数式は覚えておくとよさそうです。
石田 かのこ
