PowerPivot in Excel の日付テーブルとその作成方法について

Excel で日付テーブルを作成する

Excel で日付テーブルを作成して、データ モデルの新しいテーブルにコピーできます。 この作業はとても簡単であり、さまざまな事例に柔軟に対応できます。

Excel でデータ テーブルを作成するには、連続した日付の範囲を含む単一の列から始めます。 次に、Excel の数式を使って、年、四半期、月、会計年度、期間といった追加の列を Excel ワークシートに作成するか、テーブルをデータ モデルにコピーしてから、追加の列を計算列として作成します。 PowerPivot で追加の日付列を作成する方法については、この記事で後述の「新しい日付列を日付テーブルに追加する」セクションをご覧ください。

新しい日付列を日付テーブルに追加する

各年の日を 1 日 1 行の形式で網羅した行を含む、単一の日付列を含む日付テーブルは、日付範囲のすべての日を定義するうえで重要です。 同時に、ファクト テーブルと日付テーブルの間にリレーションシップを作成する必要もあります。 ただし、各年の日を 1 日 1 行の形式で網羅した行を含む、単一の日付列は、ピボットテーブルや Power View レポートで日付に基づいて分析する場合には使いやすくありません。 日付テーブルには、日付の範囲やグループでデータを集計するときに役に立つ列を含める必要があります。 たとえば、月または四半期別の売上高を集計する場合や、売上高の前年比伸び率を求めるメジャーを作成する場合などが考えられます。 このような場合には、対象となる期間のデータを集計できるように、年、月、四半期などの列が日付テーブルに必要です。

リレーショナル データ ソースから日付テーブルをインポートした場合、必要な日付列の種類が既に含まれる場合があります。 場合によっては、このような列のいくつかを変更したり、追加の日付列を作成したりする必要があります。 これに該当するのは、Excel で独自の日付テーブルを作成して、データ モデルにコピーするような場合です。 幸いなことに、DAX の日付/時刻関数を使用すると、Power Pivot で新しい日付列 を簡単に 作成できます。

日付と時刻の関数 (DAX)

Excel 数式で日付と時刻の関数を操作した場合は、日付と時刻の関数に慣れ親しんだと思います。 これらの関数は Excel の対応する関数とよく似ていますが、次のようにいくつかの重要な違いがあります。

• DAX の日付と時刻の関数は、datetime データ型を返します。

• 値を列から引数として取得できます。

• 日付値を返したり、操作したりする目的で使用できます。

これらの関数は多くの場合、日付テーブルにカスタム日付列を作成するときに使うので、理解しておくことが重要です。 ここでは、これらの関数を使って、Year、Quarter、FiscalMonth といった列を作成します。

DAX には、次のような日付と時刻の関数があります。

• DATE

• DATEVALUE

• NEXTDAY

• EDATE

• EOMONTH

• HOUR

• MINUTE

• MONTH

• 今

• SECOND

• TIME

• TIMEVALUE

• 本日は

• 平日

• WEEKNUM

• YEAR

• YEARFRAC

数式には他にも多くの DAX 関数を使用できます。 たとえば、ここで説明する数式の多くは、MODやTRUNCなどの数学/ 三角関数 、IFなどの論理関数 、FORMATなどのテキスト関数を使用します。その他の DAX 関数の詳細については、この記事の後半の「その他のリソース」セクションを参照してください。

数式の例 (カレンダー年度)

次の例では、Calendar という名前の日付テーブルに追加の列を作成する場合に使う数式について説明します。 2010/1/1 から 2016/12/31 までの連続した範囲の日付を含む、Date という名前の列が既に存在しています。

MonthName

=FORMAT([date],"mmmm")

この数式では、月の名前を取得するために 、FORMAT 関数を使用して数値を Date 列からテキストに変換します。 引数として Date 列を最初に指定し、さらに書式の引数として、月の名前を省略せずに表示するため、"mmmm" を使っています。 結果は次のように表示されます。

月の名前を 3 文字の省略形で返すには、書式の引数に "mmm" を使います。

DayofWeek

=FORMAT([date],"ddd")

この数式では、FORMAT 関数を使って、曜日を取得しています。 曜日の省略形が必要なので、書式の引数に "ddd" を指定しています。

ピボットテーブルのサンプル

年、四半期、月といった日付のフィールドを用意したら、ピボットテーブルやレポートで使うことができます。 たとえば、次の画像は、Sales ファクト テーブルの SalesAmount フィールドを VALUES に、Calendar ディメンション テーブルの Year と Quarter を ROWS にそれぞれ表示しています。 SalesAmount は年と四半期のコンテキストで集計されています。

数式の例 (会計年度)

祝日や特別な日付

場合によっては、特定の日付が祝日またはその他の特別な日付を示す日付列を含める必要があります。 たとえば、ピボットテーブルに祝日フィールドをスライサーまたはフィルターとして追加して、年始の売上総額を集計するような場合です。 これ以外にも、該当する日付を他の日付列やメジャーで除外するような場合も考えられます。

祝日や特別な日付を含める手順は、とても簡単です。 対象の日付を含むテーブルを Excel で作成できます。 このテーブルをコピーするか、[データ モデルに追加] コマンドでデータ モデルにリンク テーブルとして追加できます。 ほとんどの場合、このテーブルとカレンダー テーブルの間にリレーションシップを作成する必要はありません。 それを参照する数式は 、LOOKUPVALUE 関数を使用して 値を返します。

次の例に示すのは、Excel で作成したテーブルであり、日付テーブルに追加する祝日を含んでいます。

日付テーブルに、Holiday という名前の列を作成して、次のような数式を使います。

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

この数式をより詳しく見ていきましょう。

LOOKUPVALUE 関数を使って、Holidays テーブルの Holiday 列から値を取得します。 最初の引数には、結果の値になる列を指定します。 具体的には、必要な値が返されるHolidays テーブルの Holiday 列を指定します。

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

次に、2 番目の引数には、検索する対象の日付を含んでいる検索列を指定します。 次のように、Holidays テーブルの Date 列を指定します。

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

最後に、Holiday テーブルで検索する日付を含んでいる Calendar テーブルの列を指定します。 当然、Calendar テーブルの Date 列です。

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

Holiday 列は、Holidays テーブルの日付と一致した日付値を含んでいる各行の祝日名を返します。

カスタム カレンダー - 13 × 4 週間

小売業や食品サービスなど、一部の組織では、多くの場合、4 週間の期間を 13 回など、さまざまな期間について報告します。 13 の 4 週間のカレンダーでは、各期間は 28 日間です。したがって、各期間には、4 つの月曜日、4 つの火曜日、4 つの水曜日が含まれるなどです。 各期間には同じ日数が含まれるので、通常、祝日は毎年同じ期間に入ります。 任意の日に期間を開始することができます。 予定表や会計年度の日付と同様に、DAX を使用して、ユーザー設定の日付を含む追加の列を作成できます。

次の例では、最初の期間が会計年度の第 1 日曜日で始まります。 この場合、会計年度は 7/1 で始まります。

Period

このカスタム カレンダーの期間は 28 日で構成され、常に日曜日で始まります。 この列は、最初の会計年度の第 1 日曜日で始まる期間の番号を返します。

=INT(([Week]+3)/4)

この数式をより詳しく見ていきましょう。

まず、次のように、Week 列からの値を整数として返す数式を作成します。

=INT([Week])

次のように、値に 3 を加算します。

=INT([Week]+3)

次のように、結果を 4 で除算します。

=INT(([Week]+3)/4)

結果は次のように表示されます。

PeriodFiscalYear

この値は、期間の会計年度を返します。

=INT(([Period]+12)/13)+2008

この数式をより詳しく見ていきましょう。

まず、Period から値を取得して 12 を加算する数式を作成します。

= ([Period]+12)

会計年度には 28 日の期間が 13 個存在するので、結果を 13 で除算します。

=(([Period]+12)/13)

テーブルの最初の年である 2010 を加算します。

=(([Period]+12)/13)+2010

最後に、次のように、INT 関数を使って、結果の小数部を削除し、整数を取得してから 13 で除算します。

=INT(([Period]+12)/13)+2010

結果は次のように表示されます。

PeriodInFiscalYear

この値は、各会計年度の最初の期間 (日曜日で開始) で始まる、1 ～ 13 の値を返します。

=IF(MOD([Period],13), MOD([Period],13),13)

この数式は少し複雑なので、わかりやすいように説明から始めます。 この数式では、[Period] の値を 13 で除算して、年に含まれる期間の番号 (1 ～ 13) を取得します。 取得した値が 0 である場合は、13 を返します。

まず、Period の値を 13 で除算したときの余りを返す数式を作成します。 MOD ( 数学 関数と三角関数) は次のように使用できます。

=MOD([Period],13)

ほとんどの場合、この例のカレンダー日付テーブルの最初の 5 日間のように、期間の値が 0 である場合を除き、この結果は最初の会計年度内に含まれるためです。 これは IF 関数で行います。 結果が 0 の場合は、次のように 13 を返します。

=IF(MOD([Period],13),MOD([Period],13),13)

結果は次のように表示されます。

複数のリレーションシップ

場合によっては、複数のリレーションシップを作成するか、複数の日付テーブルを作成する必要があります。 たとえば、Sales ファクト テーブルに DateKey、ShipDate、ReturnDate などの複数の日付フィールドがある場合、それらはすべてカレンダーの日付テーブルの Date フィールドとのリレーションシップを持つ可能性がありますが、アクティブなリレーションシップは 1 つのみです。 この場合、DateKey はトランザクションの日付、つまり最も重要な日付を表すので、アクティブなリレーションシップとして最適 です 。 他のユーザーには非アクティブなリレーションシップがあります。

次のピボットテーブルでは、会計年度および会計四半期別に売上合計を計算します。 数式 Total Sales:=SUM([SalesAmount]) を含む Total Sales という名前のメジャーは VALUES に配置され、Calendar 日付テーブルの FiscalYear および FiscalQuarter フィールドは ROWS に配置されます。

DateKey の取引日付で売上合計を集計するので、この単純なピボットテーブルは正常に機能します。 Total Sales メジャーは DateKey の日付を使っており、Sales テーブルの DateKey と Calendar 日付テーブルの Date 列の間にリレーションシップがあるので、会計年度と会計四半期で集計されます。

非アクティブなリレーションシップ

ただし、取引日ではなく出荷日で売上合計を合計する場合 は、どうでしょうか。 Sales テーブルの ShipDate 列とカレンダー テーブルの [日付] 列の間にリレーションシップが必要です。 このリレーションシップを作成しない場合、集計は常にトランザクションの日付に基づいて行います。 ただし、アクティブにできるリレーションシップは 1 つしかなくても複数のリレーションシップを持つ可能性があります。トランザクションの日付が最も重要なので、予定表テーブルとのアクティブなリレーションシップが取得されます。

この場合、ShipDate には非アクティブなリレーションシップが含まれるので、出荷日に基づいてデータを集計するために作成されたメジャー数式では 、USERELATIONSHIP 関数を使用して非アクティブなリレーションシップを指定する必要があります。

たとえば、Sales テーブルの ShipDate 列と Calendar テーブルの Date 列の間に非アクティブなリレーションシップを設定すれば、出荷日に基づいて売上合計を集計するメジャーを作成できます。 このリレーションシップを指定するには、次のような数式を使います。

Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

この数式では単純に、SalesAmount の集計を計算していますが、Sales テーブルの ShipDate 列と Calendar テーブルの Date 列の間のリレーションシップを使ってフィルター処理しています。

これで、ピボットテーブルを作成して、Total Sales by Ship Date メジャーを VALUES に、FiscalYear と FiscalQuarter を ROWS に配置すると、同じ Grand Total が得られます。ただし、会計年度と会計四半期の他の集計値はすべて、取引日ではなく、出荷日に基づいているので、異なる値になります。

非アクティブなリレーションシップを使うと、日付テーブルを 1 つだけ使うことができますが、(Total Sales by Ship Date の場合のように) メジャーの数式で非アクティブなリレーションシップを参照する必要があります。 これとは別に、複数の日付テーブルを使う方法があります。

複数の日付テーブル

ファクト テーブルの複数の日付列を操作する方法として、複数の日付テーブルを作成して、それぞれの間にアクティブなリレーションシップを個別に作成します。 もう一度、Sales テーブルの例を見てみましょう。 データを集計する条件として、日付を含む、次の 3 つの列があります。

• DateKey - 各取引の販売日を含む。

• ShipDate – 販売された商品が顧客に出荷された日付と時刻を含む。

• ReturnDate – 返された 1 つ以上のアイテムが受信された日付と時刻。

トランザクションの日付を含む DateKey フィールドが最も重要な点に注意してください。 これらの日付に基づいて集計のほとんどを行うので、最も確実にカレンダー テーブルの Date 列との間にリレーションシップが必要になります。 ShipDate と ReturnDate と Calendar テーブルの Date フィールドの間に非アクティブなリレーションシップを作成したくない場合、特別なメジャー数式が必要な場合は、出荷日と返される日付の追加の日付テーブルを作成できます。 その後、その間にアクティブなリレーションシップを作成できます。

この例では、ShipCalendar という名前の別の日付テーブルを作成しました。 これはもちろん、追加の日付列を作成する意味で、これらの日付列は別の日付テーブルに含まれるため、予定表テーブル内の同じ列と区別する方法で名前を付けしたいと考えます。 たとえば、ShipYear、ShipMonth、ShipQuarter という名前の列を作成しました。

ピボットテーブルを作成して、Total Sales メジャーを VALUES に、ShipFiscalYear および ShipFiscalQuarter を ROWS にそれぞれ配置した場合には、非アクティブなリレーションシップと特別な Total Sales by Ship Date 計算フィールドを作成した場合と同じ結果が得られます。

これらの各アプローチでは、慎重に検討する必要があります。 1 つの日付テーブルで複数のリレーションシップを使用する場合は、USERELATIONSHIP 関数を使用して非アクティブなリレーションシップを転送する特別なメジャーを作成する必要がある場合があります。 一方、フィールド リストでは複数の日付テーブルを作成すると混乱を招く可能性があります。また、データ モデルのテーブルが多くなるので、より多くのメモリが必要になります。 最適な機能を試してください。

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

1. PowerPivot ウィンドウで、Calendar テーブルを選びます。

2. [デザイン] タブで、[日付テーブルとしてマーク] をクリックします。

3. [日付テーブルとしてマーク] ダイアログ ボックスで、一意の値を含む列と Date データ型を選びます。

テキスト データ型の日付を日付データ型に変換する

場合によっては、取引データを含むファクト テーブルに、テキスト データ型の日付が格納されていることがあります。 つまり、2012-12-04T11:47:09 のように表示される日付が日付ではないか、少なくとも PowerPivot で認識できる日付型ではないということです。 実際には日付として読み取ることができるテキストです。 ファクト テーブルの日付列と日付テーブルの日付列の間にリレーションシップを作成するには、この両方の列が Date データ型である必要があります。

通常、日付列のデータ型をテキスト データ型から日付データ型に変更する場合、PowerPivot は日付を解釈して本来の日付データ型に自動的に変換することができます。 PowerPivot がデータ型を変換できない場合、型の不一致エラーが返されます。

ただしその場合でも、日付を本来の日付データ型に変換できます。 新しい計算列を作成し、DAX の数式を使って、テキスト文字列から年、月、日、時刻などを解釈した上で、PowerPivot で本来の日付として読み取ることができるように連結し直すことができます。

この例では、Sales という名前のファクト テーブルを PowerPivot にインポートしました。 このテーブルには、DateTime という名前の列が含まれています。 値は次のようになります。

PowerPivot の [ホーム] タブの [書式] グループで [データ型] を確認した場合、テキスト データ型であることがわかります。

データ型が一致しないので、日付テーブルの DateTime 列と Date 列の間にリレーションシップを作成することはできません。 データ型を Date に変更しようとすると、型の不一致エラーが返されます。

この場合、PowerPivot はデータ型をテキストから日付に変換できません。 この列をそのまま使うことはできますが、本来の日付データ型に変換するには、新しい列を作成し、テキストを解釈して、PowerPivot で Date データ型に変換できる値に作成し直す必要があります。

この記事で前述の「時刻を操作する」セクションで説明したように、時間レベルの精度で分析する必要がなければ、ファクト テーブルの日付を日レベルの精度に変換してください。 その点を考慮して、新しい列の値を日レベルの精度に設定します (時刻を除外)。 次の数式を使えば、DateTime 列の値を日付データ型に変換すると同時に、時間レベルの精度データを削除できます。

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))

これで、新しい列が作成されました (名前は Date)。 PowerPivot は値が日付であると認識して、データ型を Date に自動的に設定します。

時間レベルの精度を保つ必要がある場合は、時、分、秒を範囲に含めるように数式を拡張するだけです。

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +

TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))

これで、Date 列を Date データ型に設定したので、この列と日付テーブルの日付列の間にリレーションシップを作成できます。

その他のリソース

PowerPivot で使用する日付

PowerPivot における計算

クイック スタート: 30 分で学ぶ DAX の基礎

データ分析式リファレンス

DAX リソース センター