第15回 カレンダーテーブルとタイムインテリジェンス関数

経営分析などをする際、前月増減や対前年同期比などを算出・表示しますが、実はこの裏で日付や時間の階層を持つ「カレンダー」が重要な意味を持ち合わせています。

時系列データを最大限活用するためのカレンダーテーブル

年度や四半期、月次や週次など、任意の時系列単位でデータを集計したい、月別累計や移動平均を算出したい、対前月比や対前年増減額を表示したい、などと思うことがあると思います。

このような時系列データを簡単に扱えるようにするのが、「カレンダーテーブル」です。

カレンダーテーブルとは、連続した日付列を持ち、年月などの時系列の階層を持つ、ディメンションテーブルです。このカレンダーテーブルと販売データのようなファクトテーブルをリレーションシップすることにより、任意の期間で集計したり、後述するタイムインテリジェンス関数を利用できたりするようになります。

カレンダーテーブルを作成することは、期間の操作が含まれる経営分析に必須です。

カレンダーテーブルの作成とタイムインテリジェンス関数

カレンダーテーブルを作成する一番簡単と思われる方法は、データテーブルをPower Pivotに読込後、販売データなどのテーブルの日付列をクリックして選択し、[デザイン]タブの[予定表]グループの[日付テーブル]アイコンをクリックするだけという方法です。

これで[予定表]という名称の“カレンダーテーブル”を作成することができます(図表)。

図表 [予定表]という名称の“カレンダーテーブル”

既定では、Date、年、月の番号、月、MMM-YYYY、曜日の番号、曜日、という7項目のカレンダーテーブルを作成できます。

しかし、これらの情報だけでは、財務会計のデータとして不十分だよねー。

年度や四半期など、各社独自のカレンダーの要素も欲しいですね。

営業の観点からすれば、営業日数を計算したいというニーズもあるでしょう。

一方、営業日と祝祭日を見比べれば、営業日でもないのに取引が計上されている仕訳データに出くわすなど、不正会計と向き合うヒントも得られます。

年度と四半期をカレンダーテーブルに表示する

ここでは、分析に必須の「年度」と「四半期」を「カレンダーテーブル」に表示してみましょう。

3月決算であれば[列の追加]から次にように設定します。なお、図表を見ればわかるように算式中、[月]を用いません。[月]では文字列となってしまうので、数字で表現される[月の番号]を使う点に留意してください。

年度=IF([月の番号]<=3,[年]-1,[年])
四半期=IF([月の番号]<=3,”Q4″,IF([月の番号]<=6,”Q1″,IF([月の番号]<=9,”Q2″,”Q3″)))

上記の計算式の内容は、Excel関数でおなじみのIF関数と同じです。

例えば、年度の“[月の番号]<=3”という部分は、“3月以下である場合”ということを意味します。図表1186行目と1187行目で年度と四半期がそれぞれ区分されているのが分かります。

なお、このカレンダーテーブルは連続した日付列を持つ「一意のデータ」です。

このカレンダーテーブルと、SalesDataのような「ファクトテーブル」の販売日などの日付列の間で「リレーションシップ」をとることで、集計に便利な「タイムインテリジェンス関数」を利用できるようになります。

タイムインテリジェンス関数とは(次回予告!)

タイムインテリジェンス関数とは、次回解説予定のDAX(Data Analysis Expressions) の特徴的な関数で、年、四半期、月、日などの期間=タイムを使用してデータを操作=インテリジェンスし、前年比や累計などを計算する際に便利な関数です。

例えば、「SAMEPERIODLASTYEAR関数」を用いることで前年同月比などを簡単に計算できます。

年度累計を表示したければ「TOTALYTD関数」を用い、四半期累計を計算したければ「TOTALQTD関数」を用います。

すべて簡単な英語表記になっているので、比較的イメージしやすいと思います。

・時系列データを上手に分析するには、カレンダーテーブルが必要だよ。
・カレンダーテーブルを用いることで、年度累計や前年比などを簡単に計算できるタイムインテリジェンス関数を利用できるようになるよ。

モダンExcel研究所

フォローお待ちしてます!

error: Content is protected !!