PowerPivot での集計
Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 その他…表示数を減らす
Microsoft 365 を無料でお試しください
集計は、データの折りたたみ、集計、またはグループ化の方法です。 テーブルやその他のデータ ソースからの生データから始めると、データはフラットになることが多く、詳細は多くありますが、整理またはグループ化されていません。 この概要や構造が不足すると、データ内のパターンを検出することが困難になる可能性があります。 データ モデリングの重要な部分は、特定のビジネスの質問に対する回答でパターンを簡略化、抽象化、または集計する集計を定義することです。
AVERAGE、COUNT、DISTINCTCOUNT、MAX、MIN、SUM を使用する集計など、最も一般的な集計は、AutoSum を使用して target=”_blank” title=”メジャー” rel=”noopener”>メジャーで自動的に作成できます。 AVERAGEX、COUNTX、COUNTROWS、SUMXなどの他の種類の集計ではテーブルが返され、 target=”_blank” title=”データ分析式 (DAX)” rel=”noopener”>データ分析式 (DAX) を使用して作成された数式が必要です。
PowerPivot での集計について
集計のグループの選択
データを集計する際には、製品、価格、地域、日付などの属性によってデータをグループ化してから、グループ内のすべてのデータに有効な数式を定義します。 たとえば、1 年間の合計を作成することは、集計の作成です。 一方、去年に対する今年の比率を作成し、それをパーセントで表すことも、別の種類の集計です。
データをグループ化する方法は、業務上の質問に基づいて決定します。 たとえば、集計を使用すると、以下の質問に答えることができます。
Counts 1 か月にいくつのトランザクションがありましたか?
平均 今月の平均売上は営業担当者によって何でしたか?
最小値と最大値 販売数の上位 5 位に入った販売地区はどれですか?
これらの質問に答えるための計算を作成するには、カウントまたは合計の対象となる数値を含む詳しいデータが必要であり、その数値データには、結果の分類に使用するグループと何らかの方法で関連づけられていることが必要です。
製品カテゴリや店舗が所在する地域の名称など、グループ化に使用できる値がまだデータに含まれていない場合は、カテゴリを追加することでデータにグループを追加できます。 Excel でグループを作成するときは、使用するグループを手動で入力するか、ワークシートの列から選ぶ必要があります。 しかし、リレーショナル システムでは、製品のカテゴリなどの階層はファクト テーブルや値テーブルとは別のテーブルに保存されていることが少なくありません。 通常、カテゴリ テーブルは何らかのキーでファクト データに連結しています。 たとえば、データに製品 ID が含まれていても製品名やそのカテゴリが含まれていなかったとします。 フラットな Excel ワークシートにカテゴリを追加するには、カテゴリ名を含む列をコピーする必要があります。 PowerPivot を使えば、製品カテゴリ テーブルをデータ モデルにインポートしたり、数字データを含むテーブルと製品カテゴリ一覧との間のリレーションシップを作成したり、それらのカテゴリを使用してデータをグループ化できます。 詳細については、「 target=”_blank” title=”テーブル間のリレーションシップをCreateする」を” rel=”noopener”>テーブル間のリレーションシップをCreateする」を参照してください。
集計用の関数の選択
使用するグループを決定し、追加したら、集計に使う数学関数を決める必要があります。 集計という用語は、合計、平均、最小、件数など、集計に使用される数学的または統計的演算と同じ意味で使われることがあります。 しかし PowerPivot では、PowerPivot と Excel の両方が備えている標準的な集計のほかに、集計のための数式を独自に作成できます。
たとえば、前の例で使用したのと同じ値とグループ化のセットで、以下の質問に答えるカスタム集計を作成できます。
フィルター処理されたカウント 月末のメンテナンス期間を除き、1 か月にいくつのトランザクションがあったか。
時間の経過に伴う平均を使用した比率 前年同期と比較して、売上の伸び率や減少率は何でしたか?
グループ化された最小値と最大値 どの販売地区が各製品カテゴリの上位にランク付けされたか、または販売促進ごとに上位にランク付けされましたか?
数式とピボットテーブルへの集計の追加
データが意味を意味を持つようなグループ化方法と処理する値についておおよその方針が決まったら、ピボットテーブルを構築するのかテーブル内で計算を作成するのかを決めます。 PowerPivot は、Excel の従来の機能を拡張、改善することで、総計、件数、平均などの集計を作成できるようになりました。 カスタム集計は、[PowerPivot] ウィンドウ内、または Excel ピボットテーブル領域内の PowerPivot で作成できます。
target=”_blank” title=”計算列” rel=”noopener”>計算列で、現在の行のコンテキストを考慮する集計を作成して、別のテーブルから関連する行を取得してから、関連する行の値の合計、件数、平均などを計算できます。
target=”_blank” title=”メジャー” rel=”noopener”>メジャーでは、数式内で定義されたフィルターと、ピボットテーブルのデザインとスライサー、列見出し、行見出しの選択によって課されるフィルターの両方を使用する動的集計を作成できます。 標準集計を使用するメジャーは、AutoSum を使用するか、数式を作成することで、 PowerPivot で作成できます。 Excel のピボットテーブルで標準集計を使用して暗黙的なメジャーを作成することもできます。
ピボットテーブルへのグループ化の追加
ピボットテーブルをデザインするときは、グループ化、カテゴリ、または階層を示すフィールドをピボットテーブルの列および行のセクションにドラッグして、データを分類します。 次に、数値が含まれているフィールドを値の領域にドラッグし、件数、平均、合計などを計算できるようにします。
ピボットテーブルにカテゴリを追加しても、カテゴリ データがファクト データに関連していない場合は、エラーまたは異常な結果が生じる可能性があります。 PowerPivot は通常、自動的にリレーションシップを検出および提案することで問題を修正しようとします。 詳細については、「 target=”_blank” title=”ピボットテーブルでのリレーションシップの操作” rel=”noopener”>ピボットテーブルでのリレーションシップの操作」を参照してください。
また、フィールドをスライサーにドラッグして、表示する特定のデータ グループを選ぶこともできます。 スライサーを使用すると、結果をピボットテーブルで対話的にグループ化、並べ替え、およびフィルターできます。
数式のグループ化の操作
グループ化とカテゴリを使用すると、テーブル間のリレーションシップを作成してから、これらのリレーションシップを活用して関連する値を参照する数式を作成することによって、テーブルに保存されているデータを集計することもできます。
つまり、カテゴリ別に値をグループ化する数式を作成する場合は、最初にリレーションシップを使用して詳細データを含むテーブルとカテゴリを含むテーブルを関連付けてから、数式を作成します。
参照を使用する数式を作成する方法の詳細については、「 target=”_blank” title=”PowerPivot の数式での参照” rel=”noopener”>PowerPivot の数式での参照」を参照してください。
集計でのフィルターの使用
PowerPivot の新機能の 1 つは、ユーザー インターフェイス上およびピボットテーブル内またはチャート内だけでなく、集計の計算に使用する数式内であっても、データの列とテーブルにフィルターを適用できる機能です。 フィルターは、計算列と s の両方の数式で使用できます。
たとえば、新しい DAX 集計関数では、合計や件数を計算する値を指定する代わりに、テーブル全体を引数として指定できます。 そのテーブルにフィルターを適用しないと、集計関数の対象は、テーブル内の指定した列のすべての値になります。 しかし、DAX でテーブルに動的フィルターまたは静的フィルターを作成し、フィルターの条件と現在のコンテキストに応じて集計の対象をデータの異なるサブセットにすることができます。
数式で条件とフィルターを組み合わせることで、数式に入力した値によって変わる集計や、ピボットテーブルの行見出しや列見出しの選択によって変わる集計を作成できます。
詳細については、「 target=”_blank” title=”数式内でのデータのフィルター処理” rel=”noopener”>数式内でのデータのフィルター処理」を参照してください。
Excel の集計関数と DAX の集計関数との比較
次の一覧は、Excel の標準的な集計関数の一部で、PowerPivot でのこれらの関数の使用方法にリンクしています。 これらの関数の DAX バージョンは Excel バージョンとほぼ同じですが、構文と特定のデータ型の扱いに若干の違いがあります。
標準の集計関数
| 関数 | 用途 |
|---|---|
| 平均 | 列内のすべての数値の平均 (算術平均) を返します。 |
| AVERAGEA | 列内のすべての数値の平均 (算術平均) を返します。 テキスト値および数値以外の値も処理されます。 |
| カウント | 列内の数値の数をカウントします。 |
| Counta | 列内の空ではない値の数をカウントします。 |
| 最大 | 列の最大の数値を返します。 |
| マックス | テーブルに対して評価される一連の式から最大値を返します。 |
| 分 | 列の最小の数値を返します。 |
| ミンクス | テーブルに対して評価される一連の式から最小値を返します。 |
| 合計 | 列内のすべての数値を加算します。 |
DAX の集計関数
DAX には、集計を実行するテーブルを指定できる集計関数が含まれています。 したがって、これらの関数を使用すると、単に列内の値を加算したり、それらの値の平均を求めたりする代わりに、集計するデータを動的に定義する式を作成することができます。
次の表は、DAX で使用できる集計関数の一覧を示しています。
| 関数 | 用途 |
|---|---|
| AVERAGEX | テーブルに対して評価される一連の式の平均を計算します。 |
| COUNTAX | テーブルに対して評価される一連の式をカウントします。 |
| COUNTBLANK | 列内の空白値の数をカウントします。 |
| COUNTX | テーブル内の行の総数をカウントします。 |
| COUNTROWS | 入れ子になったテーブル関数 (フィルター関数など) から返される行の数をカウントします。 |
| SUMX | テーブルに対して評価される一連の式の合計を返します。 |
DAX の集計関数と Excel の集計関数の相違点
これらの関数は Excel に対応する関数と同じ名前ですが、 PowerPivotのメモリ内分析エンジンを利用し、テーブルと列を操作するように書き換えられます。 Excel ブックで DAX 数式を使用することはできません。また、その逆も同様です。 これらは、 PowerPivot ウィンドウと、 PowerPivot データに基づくピボットテーブルでのみ使用できます。 また、関数の名前は同じですが、動作が若干異なる場合があります。 詳細については、個々の関数リファレンス トピックを参照してください。
また、集計内で列が評価される方法は、Excel が集計を処理する方法とは異なります。 次の例を使用して説明します。
Sales テーブル内の Amount 列の値の合計が必要で、次の式を作成したとします。
`
=SUM('Sales'[Amount])`
最も簡単なケースでは、関数は単一のフィルター処理されていない列から値を取得し、結果は Excel と同じです。これは常に列 Amount の値を加算するだけです。 ただし、 PowerPivotでは、数式は "Sales テーブルの各行の Amount の値を取得し、それらの個々の値を加算します。 PowerPivot は、集計が実行される各行を評価し、各行の 1 つのスカラー値を計算してから、それらの値に対して集計を実行します。 そのため、フィルターがテーブルに適用されている場合や、フィルター処理される可能性がある他の集計に基づいて値が計算される場合は、数式の結果が異なる場合があります。 詳細については、「 target=”_blank” title=”DAX の数式のコンテキスト” rel=”noopener”>DAX の数式のコンテキスト」を参照してください。
DAX タイム インテリジェンス関数
前のセクションで説明したテーブル集計関数以外に、DAX には指定した日時を操作して組み込みのタイム インテリジェンスを提供する集計関数があります。 これらの関数は、日付の範囲を使用して関連する値を取得し、値を集計します。 日付範囲で値を比較することもできます。
次の表に、集計に使用できるタイム インテリジェンス関数を一覧にします。
| 関数 | 用途 |
|---|---|
| CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR | 指定された期間の末日の値を計算します。 |
| OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR | 指定された期間の前の期間の末日の値を計算します。 |
| TOTALMTD TOTALYTD TOTALQTD | 期間の最初の日付から、指定された日付列に含まれる最も新しい日付までを対象に、値を計算します。 |
タイム インテリジェンス関数セクション (タイム インテリジェンス関数) の他の関数は、集計で使用する日付またはカスタムの日付範囲を取得するために使用できる関数です。 たとえば、DATESINPERIOD 関数を使用して日付範囲を返し、その一連の日付を別の関数の引数として使用して、それらの日付だけを対象とするカスタム集計を計算できます。