数式での集計
ここでは集計について説明し、PowerPivot for Excel で使用できる集計の種類の概要を示します。PowerPivot for Excel には、集計を作成するための次のようなツールがあります。
PowerPivot データに基づいたピボットテーブルおよびピボットグラフを作成できます。 Excel ピボットテーブルは、ワークシートでデータをグループ化して集約するための一般的なツールです。 PowerPivot は Excel のピボットテーブル機能に統合されて、多くの機能拡張を提供します。
DAX 数式言語を使用してカスタム集計を作成できます。 DAX を使用すると、PowerPivot テーブルに計算列を作成したり、ピボットテーブルおよびピボットグラフにメジャーを作成したりすることができます。
このトピックの最後のセクションに、集計の作成方法に関する詳細情報のリンクが記載されています。
集計の概要
集計とは、データを集約、要約、またはグループ化する方法です。 テーブルや他のデータ ソースからの生のデータは通常フラットです。つまり、詳細なデータが多数あっても、データは分類またはグループ化されていません。 このように概要や構造が不十分なために、データのパターンを見つけにくい場合があります。 したがって、特定の業務上の疑問に応じてパターンを簡素化、抽象化、または集約する集計を定義することが、アナリストの仕事の重要な要素になります。
集計のグループの選択
データを集計する際には、製品、価格、地域、日付などの属性によってデータをグループ化してから、グループ内のすべてのデータに有効な数式を定義します。 たとえば、1 年間の合計を作成することは、集計の作成です。 一方、去年に対する今年の比率を作成し、それをパーセントで表すことも、別の種類の集計です。
データをグループ化する方法は、業務上の質問に基づいて決定します。 たとえば、集計を使用すると、以下の質問に答えることができます。
件数 1 か月に何件取引があったか?
平均 販売員ごとの今月の平均売上高はいくらだったか?
最小値と最大値 どの販売地域が販売個数で上位 5 位だったか?
これらの質問に答えるための計算を作成するには、カウントまたは合計の対象となる数値を含む詳細なデータが必要であり、その数値データには、結果の分類に使用するグループと何らかの方法で関連づけられていることが必要です。
製品カテゴリや店舗が所在する地理的領域の名称など、グループ化に使用できる値がまだデータに含まれていない場合は、カテゴリを追加することでデータにグループを適用できます。 Excel でグループを作成するときは、使用するグループを手動で入力するか、ワークシートの列から選択する必要があります。
しかし、リレーショナル システムでは、多くの場合、製品のカテゴリなどの階層はファクト テーブルや値テーブルとは別のテーブルに保存されています。 通常、カテゴリ テーブルは何らかのキーでファクト データに連結しています。 たとえば、データに製品 ID が含まれていても製品名やそのカテゴリが含まれていないことがわかったとします。 フラットな Excel ワークシートにカテゴリを追加するには、カテゴリ名を含む列でコピーすることが必要になります。 しかし、PowerPivot ブックでは、製品カテゴリ テーブルをブックにインポートすること、および数字データを含むテーブルと製品カテゴリ一覧との間のリレーションシップを作成することが可能で、それらのカテゴリを使用してデータをグループ化できます。 詳細については、「テーブル間のリレーションシップ」を参照してください。
集計用の関数の選択
使用するグループ化を指定して追加した後、集計にどの数学関数を使用するかを決める必要があります。 集計という用語は、合計、平均、最小、件数など、集計に使用される数学的演算または統計的演算の同義語として使われることがあります。 ただし、PowerPivot for Excel では、Excel が備えている標準的な集計に加えて集計用のカスタムの数式を作成できます。
たとえば、前の例で使用したのと同じ値とグループ化のセットで、以下の質問に答えるカスタム集計を作成できます。
フィルター後の件数 月末の保守期間を除いて、1 か月に何件取引があったか?
長期間の平均を使用した比率 去年の同じ時期と比較して売上の増加率または低下率はどのくらいか?
グループ化した最小値と最大値 どの販売地域が各製品カテゴリまたは各販売キャンペーンの 1 位であったか?
数式とピボットテーブルへの集計の追加
データが意味を持つようにグループ化する方法と処理する値について考えがまとまれば、ピボットテーブルを構築するかテーブル内で計算を作成するかを決めることができます。 PowerPivot for Excel は Excel の従来の機能を拡張し、改善して、総計、件数、平均などの集計を作成できるようになりました。 PowerPivot ウィンドウ内、または Excel ピボットテーブル領域内のいずれかで、カスタム集計を PowerPivot で作成できます。
計算列で、現在の行のコンテキストを考慮する集計を作成して、別のテーブルから関連する行を取得してから、関連する行の値の合計、件数、平均などを計算できます。
メジャーでは、数式内で定義されるフィルターと、ピボットテーブルのデザイン、およびスライサー、列見出し、行見出しの選択によって制限されるフィルターの両方を使用する動的な集計を作成できます。
詳細については、「数式の作成」を参照してください。
ピボットテーブルへのグループ化の追加
ピボットテーブルをデザインするときは、グループ化、カテゴリ、または階層を示すフィールドをピボットテーブルの列および行のセクションにドラッグして、データを分類します。 次に、数値が含まれているフィールドを値の領域にドラッグし、件数、平均、合計などを計算できるようにします。
ピボットテーブルにカテゴリを追加しても、カテゴリ データがファクト データに関連していない場合は、エラーまたは異常な結果が生じる可能性があります。 PowerPivot for Excel は通常、自動的にリレーションシップを検出および推奨することで問題を修正しようとします。 詳細については、「ピボットテーブルでのリレーションシップの操作」を参照してください。
また、フィールドをスライサーにドラッグして、表示する特定のデータ グループを選択することもできます。 スライサーは Excel と PowerPivot for Excel の新機能であり、これを使用すると、結果をピボットテーブルで対話的にグループ化、並べ替え、およびフィルターできます。
数式のグループ化の操作
グループ化とカテゴリを使用すると、テーブル間のリレーションシップを作成してから、これらのリレーションシップを活用して関連する値を参照する数式を作成することによって、テーブルに保存されているデータを集計することもできます。
つまり、カテゴリ別に値をグループ化する数式を作成する場合は、最初にリレーションシップを使用して詳細データを含むテーブルとカテゴリを含むテーブルを関連付けてから、数式を作成します。
参照を使用する数式を作成する方法の詳細については、「数式内でのリレーションシップと参照」を参照してください。
集計でのフィルターの使用
PowerPivot の新機能の 1 つは、ユーザー インターフェイス上およびピボットテーブル内またはチャート内だけでなく、集計の計算に使用する数式内であっても、データの列とテーブルにフィルターを適用できる機能です。 フィルターは、計算列とメジャーの両方の数式に使用できます。
たとえば、新しい DAX 集計関数では、合計や件数を計算する値を指定する代わりに、テーブル全体を引数として指定できます。 そのテーブルにフィルターを適用しないと、集計関数の対象は、テーブル内の指定した列のすべての値になります。 しかし、DAX でテーブルに動的フィルターまたは静的フィルターを作成し、フィルターの条件と現在のコンテキストに応じて集計の対象をデータの異なるサブセットにすることができます。
数式で条件とフィルターを組み合わせることで、数式に入力した値によって変わる集計や、ピボットテーブルの行見出しや列見出しの選択によって変わる集計を作成できます。
詳細については、「数式内でのデータのフィルター処理」を参照してください。
Excel の集計関数と DAX の集計関数との比較
次の表に、Excel が提供する標準的な集計関数の一部を一覧にし、PowerPivot for Excel でこれらの関数の実装へのリンクを提供します。 これらの関数の DAX バージョンは Excel バージョンとほぼ同じですが、構文と特定のデータ型の扱いに若干の違いがあります。
標準の集計関数
関数 |
機能 |
列内のすべての数値の平均 (算術平均) を返します。 |
|
列内のすべての数値の平均 (算術平均) を返します。 テキスト値および数値以外の値も処理されます。 |
|
列内の数値の数をカウントします。 |
|
列内の空ではない値の数をカウントします。 |
|
特定の列の中で最も大きい数値を返します。 |
|
テーブルに対して評価される一連の式から最大値を返します。 |
|
1 つの列の中で最も小さい数値を返します。 |
|
テーブルに対して評価される一連の式から最小値を返します。 |
|
列内のすべての数値を加算します。 |
DAX 集計関数
DAX には、集計を実行するテーブルを指定できる集計関数が含まれています。 したがって、これらの関数を使用すると、単に列内の値を加算したり、それらの値の平均を求めたりする代わりに、集計するデータを動的に定義する式を作成することができます。
次の表は、DAX で使用できる集計関数の一覧を示しています。
関数 |
機能 |
テーブルに対して評価される一連の式の平均を計算します。 |
|
テーブルに対して評価される一連の式をカウントします。 |
|
列内の空白値の数をカウントします。 |
|
テーブル内の行の総数をカウントします。 |
|
入れ子になったテーブル関数 (フィルター関数など) から返される行の数をカウントします。 |
|
テーブルに対して評価される一連の式の合計を返します。 |
DAX の集計関数と Excel の集計関数の相違点
これらの関数は Excel バージョンと同じ名前ですが、PowerPivot VertiPaq エンジンを利用しており、テーブルおよび列を対象とするように書き直されています。DAX 数式を Excel ブックで使用することはできません。その逆もできません。 PowerPivot データに基づく PowerPivot ウィンドウとピボットテーブルでのみ使用できます。 また、関数の名前が同じでも、動作が若干異なる場合があります。 詳細については、個々の関数のリファレンス トピックを参照してください。
また、集計内で列が評価される方法は、Excel が集計を処理する方法とは異なります。 次の例を使用して説明します。
Sales テーブル内の Amount 列の値の合計が必要で、次の式を作成したとします。
=SUM('Sales'[Amount])
最も単純な例では、関数はフィルター処理されていない 1 つの列から値を取得します。結果は、Amount 列の各値を常に加算するだけの Excel の結果と同じになります。 しかし PowerPivot の場合、この数式は、"Sales テーブルの各行について Amount の値を求め、それらの値を加算する" と解釈されます。 PowerPivot では、集計の実行対象となる各行を評価し、行ごとに単一のスカラー値を求め、それらの値に対して集計を実行します。 したがって、フィルターが 1 つのテーブルに適用されている場合、またはフィルター処理される可能性のあるその他の集計に基づいて値が計算される場合、式の結果は異なることがあります。 詳細については、「DAX の数式のコンテキスト」を参照してください。
DAX タイム インテリジェンス関数
前のセクションで説明した新しいテーブル集計関数以外に、DAX には指定した日時を操作して組み込みのタイム インテリジェンスを提供する集計関数があります。 これらの関数は、日付の範囲を使用して関連する値を取得し、値を集計します。 日付範囲で値を比較することもできます。
次の表に、集計に使用できるタイム インテリジェンス関数を一覧にします。
関数 |
機能 |
指定された期間の末日の値を計算します。 |
|
指定された期間の前の期間の末日の値を計算します。 |
|
期間の最初の日付から、指定された日付列に含まれる最も新しい日付までを対象に、値を計算します。 |
タイム インテリジェンス関数のセクション (タイム インテリジェンス関数 (DAX)) に記載されているその他の関数は、集計に使用する日付またはカスタムの日付範囲を取得するために使用できる関数です。 たとえば、DATESINPERIOD 関数を使用して日付範囲を返し、その一連の日付を別の関数の引数として使用して、それらの日付だけを対象とするカスタム集計を計算できます。