PowerPivot の数式での参照
Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 その他…表示数を減らす
PowerPivot の最も強力な機能の 1 つは、テーブル間のリレーションシップを作成し、関連するテーブルを使用して関連データを検索またはフィルター処理する機能です。 テーブルから関連する値を取得するには、PowerPivot、データ分析式 (DAX) で提供される数式言語を使用します。 DAX はリレーショナル モデルを使用するため、別のテーブルまたは列の関連値または対応する値を簡単かつ正確に取得できます。 Excel の VLOOKUP に精通している場合、 PowerPivot のこの機能は似ていますが、実装がはるかに簡単です。
計算列の一部として、またはピボットテーブルまたはピボットグラフで使用するメジャーの一部として参照を行う数式を作成できます。 詳細については、次のトピックを参照してください。
target=”_blank” title=”PowerPivot の集計フィールド” rel=”noopener”>PowerPivot の集計フィールド
target=”_blank” title=”PowerPivot の計算列” rel=”noopener”>PowerPivot の計算列
このセクションでは、検索のために提供される DAX 関数と、関数の使用方法のいくつかの例について説明します。
注: 使用する参照操作または参照式の種類によっては、最初にテーブル間にリレーションシップを作成することが必要になる場合があります。
ルックアップ関数について
現在のテーブルに何らかの識別子のみが含まれているが、必要なデータ (製品価格、名前、その他の詳細な値など) が関連テーブルに格納されている場合に、一致するデータや関連するデータを別のテーブルから検索する機能は特に便利です。 現在の行または現在の値に関連する別のテーブルに複数の行がある場合にも便利です。 たとえば、特定のリージョン、ストア、営業担当者に関連付けられているすべての売上を簡単に取得できます。
配列に基づく VLOOKUP や、複数の一致する値の最初の値を取得する LOOKUP などの Excel 参照関数とは対照的に、DAX はキーで結合されたテーブル間の既存のリレーションシップに従って、正確に一致する 1 つの関連値を取得します。 DAX は、現在のレコードに関連するレコードのテーブルを取得することもできます。
注: リレーショナル データベースに精通している場合、 PowerPivot の参照は、Transact-SQL の入れ子になったサブselect ステートメントと同様に考えることができます。
1 つの関連値の取得
RELATED 関数は、現在のテーブルの現在の値に関連する別のテーブルから 1 つの値を返します。 目的のデータを含む列を指定し、関数はテーブル間の既存のリレーションシップに従って、関連テーブル内の指定された列から値をフェッチします。 場合によっては、関数はリレーションシップのチェーンに従ってデータを取得する必要があります。
たとえば、Excel に今日の出荷の一覧があるとします。 ただし、一覧には従業員 ID 番号、注文 ID 番号、荷送人 ID 番号のみが含まれており、レポートの読み取りが困難になります。 必要な追加情報を取得するには、そのリストを PowerPivot リンク テーブルに変換し、Employee テーブルとリセラー テーブルとのリレーションシップを作成し、EmployeeID を EmployeeKey フィールドに一致させ、ResellerID を ResellerKey フィールドに作成します。
リンク テーブルに参照情報を表示するには、次の数式を使用して、2 つの新しい計算列を追加します。
= RELATED(‘Employees'[EmployeeName])
= RELATED(‘Resellers'[CompanyName])
検索前の今日の出荷数
| OrderID | EmployeeID | ResellerID |
|---|---|---|
| 100314 | 230 | 445 |
| 100315 | 15 | 445 |
| 100316 | 76 | 108 |
Employees テーブル
| EmployeeID | Employee | リセラー |
|---|---|---|
| 230 | Kuppa Vamsi | モジュラーサイクルシステム |
| 15 | Pilar Ackeman | モジュラーサイクルシステム |
| 76 | Kim Ralls | 関連付けられているバイク |
ルックアップを使用した今日の出荷
| OrderID | EmployeeID | ResellerID | Employee | リセラー |
|---|---|---|---|---|
| 100314 | 230 | 445 | Kuppa Vamsi | モジュラーサイクルシステム |
| 100315 | 15 | 445 | Pilar Ackeman | モジュラーサイクルシステム |
| 100316 | 76 | 108 | Kim Ralls | 関連付けられているバイク |
関数は、リンクされたテーブルと Employees テーブルと Resellers テーブルの間のリレーションシップを使用して、レポート内の各行の正しい名前を取得します。 計算に関連する値を使用することもできます。 詳細と例については、「 RELATED 関数」を参照してください。
関連する値の一覧を取得する
RELATEDTABLE 関数は、既存のリレーションシップに従い、指定されたテーブルから一致するすべての行を含むテーブルを返します。 たとえば、各リセラーが今年発注した注文の数を確認するとします。 次の数式を含む Resellers テーブルに新しい計算列を作成できます。この列は、ResellerSales_USD テーブル内の各リセラーのレコードを検索し、各リセラーによって行われた個々の注文の数をカウントします。
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
この数式では、RELATEDTABLE 関数は、現在のテーブル内の各リセラーの ResellerKey の値を最初に取得します。 (テーブル間の既存のリレーションシップ PowerPivot 使用するため、数式内の任意の場所で ID 列を指定する必要はありません)。 次に、RELATEDTABLE 関数は、各リセラーに関連するResellerSales_USD テーブルからすべての行を取得し、行をカウントします。 2 つのテーブル間にリレーションシップ (直接または間接) がない場合は、ResellerSales_USD テーブルからすべての行を取得します。
サンプル データベースのリセラーのモジュラー サイクル システムの場合、売上テーブルには 4 つの注文があるため、関数は 4 を返します。 関連付けられたバイクの場合、リセラーには売上がないため、関数は空白を返します。
リセラー | このリセラーの売上テーブル内のレコード
—|—
モジュラーサイクルシステム | Reseller ID | SalesOrderNumber
| 445 | SO53494
| 445 | SO71872
| 445 | SO65233
| 445 | SO59000
| Reseller ID | SalesOrderNumber
関連付けられているバイク | |
注: RELATEDTABLE 関数は 1 つの値ではなくテーブルを返すので、テーブルに対する操作を実行する関数の引数として使用する必要があります。 詳細については、「 RELATEDTABLE 関数」を参照してください。
ページの先頭へ