Excel PowerPivot-データモデルの管理

Power Pivotの主な用途は、データテーブルとそれらの間の関係を管理して、複数のテーブルからのデータの分析を容易にする機能です。ピボットテーブルの作成中に、またはPowerPivotリボンから直接、Excelテーブルをデータモデルに追加できます。

複数のテーブル間に関係が存在する場合にのみ、複数のテーブルのデータを分析できます。Power Pivotを使用すると、データビューまたはダイアグラムビューから関係を作成できます。さらに、Power Pivotにテーブルを追加することを選択した場合は、関係も追加する必要があります。

ピボットテーブルを使用したデータモデルへのExcelテーブルの追加

Excelでピボットテーブルを作成する場合、それは単一のテーブル/範囲のみに基づいています。ピボットテーブルにさらにテーブルを追加する場合は、データモデルを使用して追加できます。

ワークブックに2つのワークシートがあるとします-

  • 営業担当者のデータとそれらが表す地域をテーブルに含むもの-営業担当者。

  • もう1つは、売上、地域、月ごとのデータをテーブルに含む–売上です。

以下に示すように、営業担当者ごとに売上高を要約できます。

  • 表をクリックします–売上高。

  • リボンの[挿入]タブをクリックします。

  • [テーブル]グループで[ピボットテーブル]を選択します。

Salesテーブルのフィールド(Region、Month、Order Amount)を含む空のピボットテーブルが作成されます。あなたが観察できるように、MORE TABLES ピボットテーブルフィールドリストの下にあるコマンド。

  • その他の表をクリックします。

ザ・ Create a New PivotTableメッセージボックスが表示されます。表示されるメッセージは次のとおりです。分析で複数のテーブルを使用するには、データモデルを使用して新しいピボットテーブルを作成する必要があります。[はい]をクリックします

以下に示すように、新しいピボットテーブルが作成されます-

[ピボットテーブルフィールド]の下に、2つのタブがあることがわかります– ACTIVE そして ALL

  • [すべて]タブをクリックします。

  • 2つのテーブル-SalesとSalesperson、および対応するフィールドがPivotTableFieldsリストに表示されます。

  • 営業担当者テーブルの営業担当者フィールドをクリックして、ROWS領域にドラッグします。

  • SalesテーブルのMonthフィールドをクリックして、ROWS領域にドラッグします。

  • SalesテーブルのOrderAmountフィールドをクリックして、∑VALUES領域にドラッグします。

ピボットテーブルが作成されます。ピボットテーブルフィールドにメッセージが表示されます–Relationships between tables may be needed

メッセージの横にある[作成]ボタンをクリックします。ザ・Create Relationship ダイアログボックスが表示されます。

  • Table、[販売]を選択します。

  • Column (Foreign) ボックスで、[地域]を選択します。

  • Related Table、営業担当者を選択します。

  • Related Column (Primary) ボックスで、[地域]を選択します。

  • [OK]をクリックします。

2つのワークシートの2つのテーブルからのピボットテーブルの準備が整いました。

さらに、Excelが2番目のテーブルをピボットテーブルに追加するときに述べたように、ピボットテーブルはデータモデルで作成されました。確認するには、次のようにします-

  • リボンの[POWERPIVOT]タブをクリックします。

  • クリック Manageデータモデルグループ内。PowerPivotのデータビューが表示されます。

ピボットテーブルの作成に使用した2つのExcelテーブルが、データモデルのデータテーブルに変換されていることがわかります。

別のブックからデータモデルへのExcelテーブルの追加

2つのテーブル–営業担当者と営業が2つの異なるワークブックにあるとします。

次のように、Excelテーブルを別のブックからデータモデルに追加できます。

  • Salesテーブルをクリックします。

  • [挿入]タブをクリックします。

  • [テーブル]グループの[ピボットテーブル]をクリックします。ザ・Create PivotTable ダイアログボックスが表示されます。

  • [テーブル/範囲]ボックスに「Sales」と入力します。

  • [新しいワークシート]をクリックします。

  • [このデータをデータモデルに追加する]チェックボックスをオンにします。

  • [OK]をクリックします。

新しいワークシートに空のピボットテーブルが表示され、Salesテーブルに対応するフィールドのみが表示されます。

Salesテーブルデータをデータモデルに追加しました。次に、次のように、営業担当者テーブルのデータもデータモデルに取り込む必要があります。

  • Salesテーブルを含むワークシートをクリックします。

  • リボンの[データ]タブをクリックします。

  • [外部データの取得]グループの[既存の接続]をクリックします。[既存の接続]ダイアログボックスが表示されます。

  • [テーブル]タブをクリックします。

This Workbook Data Model, 1 tableが表示されます(これは前に追加したSalesテーブルです)。また、テーブルが表示されている2つのワークブックもあります。

  • Salesperson.xlsxの下の[営業担当者]をクリックします。

  • [開く]をクリックします。ザ・Import Data ダイアログボックスが表示されます。

  • ピボットテーブルレポートをクリックします。

  • 新しいワークシートをクリックします。

あなたはその箱を見ることができます– Add this data to the Data Modelチェックされ、非アクティブです。[OK]をクリックします。

ピボットテーブルが作成されます。

ご覧のとおり、2つのテーブルはデータモデルにあります。前のセクションのように、2つのテーブル間に関係を作成する必要がある場合があります。

PowerPivotリボンからデータモデルにExcelテーブルを追加する

Excelテーブルをデータモデルに追加する別の方法は、 so from the PowerPivot Ribbon

ワークブックに2つのワークシートがあるとします-

  • 営業担当者とその地域のデータを表に含むもの–営業担当者。

  • もう1つは、売上、地域、月ごとのデータをテーブルに含む–売上です。

分析を行う前に、まずこれらのExcelテーブルをデータモデルに追加できます。

  • Excelテーブル-Salesをクリックします。

  • リボンの[POWERPIVOT]タブをクリックします。

  • [テーブル]グループの[データモデルに追加]をクリックします。

Power Pivotウィンドウが表示され、データテーブルSalespersonが追加されます。さらにタブ–リンクされたテーブルが[パワーピボット]ウィンドウのリボンに表示されます。

  • リボンの[リンクされたテーブル]タブをクリックします。

  • Excelテーブル:営業担当者をクリックします。

ブックにある2つのテーブルの名前が表示され、営業担当者の名前にチェックマークが付いていることがわかります。これは、データテーブルの営業担当者がExcelテーブルの営業担当者にリンクされていることを意味します。

クリック Go to Excel Table

営業担当者テーブルを含むワークシートを含むExcelウィンドウが表示されます。

  • [販売ワークシート]タブをクリックします。

  • Salesテーブルをクリックします。

  • リボンの[テーブル]グループで[データモデルに追加]をクリックします。

ExcelテーブルSalesもデータモデルに追加されます。

ご存知のように、これら2つのテーブルに基づいて分析を行う場合は、2つのデータテーブル間に関係を作成する必要があります。Power Pivotでは、これを2つの方法で行うことができます-

  • データビューから

  • ダイアグラムビューから

データビューからの関係の作成

ご存知のとおり、データビューでは、レコードを行、フィールドを列としてデータテーブルを表示できます。

  • PowerPivotウィンドウの[デザイン]タブをクリックします。

  • [関係]グループで[関係の作成]をクリックします。ザ・Create Relationship ダイアログボックスが表示されます。

  • [テーブル]ボックスの[売上]をクリックします。これは、関係が始まるテーブルです。ご存知のとおり、列は、一意の値を含む関連テーブルの営業担当者に存在するフィールドである必要があります。

  • [列]ボックスの[地域]をクリックします。

  • [関連リンクテーブル]ボックスで[営業担当者]をクリックします。

関連リンク列には、地域が自動的に入力されます。

[作成]ボタンをクリックします。関係が作成されます。

ダイアグラムビューからの関係の作成

ダイアグラムビューからのリレーションシップの作成は比較的簡単です。指定された手順に従います。

  • PowerPivotウィンドウの[ホーム]タブをクリックします。

  • [表示]グループの[ダイアグラムビュー]をクリックします。

データモデルのダイアグラムビューがPowerPivotウィンドウに表示されます。

  • SalesテーブルのRegionをクリックします。SalesテーブルのRegionが強調表示されます。

  • 営業担当者テーブルの地域にドラッグします。営業担当者テーブルの地域も強調表示されます。ドラッグした方向に線が表示されます。

  • テーブルSalesからテーブルSalespersonへの関係を示す線が表示されます。

ご覧のとおり、SalesテーブルからSalespersonテーブルに、関係と方向を示す線が表示されます。

関係の一部であるフィールドを知りたい場合は、関係の行をクリックしてください。両方のテーブルの行とフィールドが強調表示されます。

関係の管理

データモデルの既存の関係を編集または削除できます。

  • PowerPivotウィンドウの[デザイン]タブをクリックします。

  • 「関係」グループの「関係の管理」をクリックします。[関係の管理]ダイアログボックスが表示されます。

データモデルに存在するすべての関係が表示されます。

関係を編集するには

  • 関係をクリックします。

  • クリック Editボタン。ザ・Edit Relationship ダイアログボックスが表示されます。

  • 関係に必要な変更を加えます。

  • [OK]をクリックします。変更は関係に反映されます。

関係を削除するには

  • 関係をクリックします。

  • [削除]ボタンをクリックします。リレーションシップの削除によって影響を受けるテーブルがレポートにどのように影響するかを示す警告メッセージが表示されます。

  • 削除してもよろしい場合は、[OK]をクリックしてください。選択した関係が削除されます。

パワーピボットデータの更新

Excelテーブルのデータを変更するとします。Excelテーブルのデータを追加/変更/削除できます。

PowerPivotデータを更新するには、次の手順を実行します。

  • PowerPivotウィンドウのLinkedTableタブをクリックします。

  • [すべて更新]をクリックします。

データテーブルは、Excelテーブルで行われた変更で更新されます。

ご覧のとおり、データテーブルのデータを直接変更することはできません。したがって、データをデータモデルに追加するときに、データテーブルにリンクされているExcelテーブルでデータを維持することをお勧めします。これにより、Excelテーブルのデータを更新するときに、データテーブルのデータを簡単に更新できます。