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テーブルのデータを更新するときに、データテーブルのデータを簡単に更新できます。