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

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

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

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

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

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

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

日付と時刻の関数 (DAX)

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

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

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

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

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

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

• 日付

• DATEVALUE

• NEXTDAY

• EDATE

• EOMONTH

• 時

• 分

• 月

• 今

• 秒

• 時間

• TIMEVALUE

• 今日

• ウィークデー

• WEEKNUM

• 年

• 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 週間

小売やフード サービスなどの一部の組織では、多くの場合、13 の 4 週間の期間など、さまざまな期間について報告します。 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)

これは、ほとんどの場合、目的の結果を提供します。ただし、[期間] の値が 0 の場合は、それらの日付が最初の会計年度内に含まれていないためです (例の Calendar 日付テーブルの最初の 5 日間など)。 これは IF 関数を使用して行うことができます。 結果が 0 の場合、次のように 13 が返されます。

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

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

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

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

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

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

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

しかし、合計売上を取引日ではなく出荷日別に合計する場合はどう でしょうか。 Sales テーブルの ShipDate 列と Calendar テーブルの Date 列の間にリレーションシップが必要です。 そのリレーションシップを作成しない場合、集計は常にトランザクションの日付に基づいています。 ただし、アクティブにできるリレーションシップは 1 つだけですが、複数のリレーションシップを持つことができます。また、トランザクションの日付が最も重要であるため、Calendar テーブルとのアクティブなリレーションシップを取得します。

この場合、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 リソース センター