高度なデータ分析-データモデル

データモデルは、Excel2013以降のバージョンで使用できます。Excelでは、データモデルを使用して、現在のブック内の複数のテーブルから、および/またはインポートされたデータから、および/またはデータ接続を介してブックに接続されたデータソースからのデータを統合できます。

データモデルを使用すると、テーブル間の関係を作成できます。データモデルは、ピボットテーブル、ピボットグラフ、PowerPivot、およびPowerViewレポートで透過的に使用されます。

データのインポート中にデータモデルを作成する

複数の関連テーブルを含むMicrosoftAccessデータベースなどのリレーショナルデータベースからデータをインポートする場合、一度に複数のテーブルをインポートすると、データモデルが自動的に作成されます。

次のデータソースからデータをインポートする場合、オプションでデータモデルにテーブルを追加できます-

リレーショナルデータベース、一度に1つのテーブル
テキストファイル
Excelワークブック

たとえば、Excelブックからデータをインポートしているときに、オプションを確認できます。 Add this data to the Data Model、チェックボックスが有効になっています。

インポートするデータをデータモデルに追加する場合は、チェックボックスをオンにします。

Excelテーブルからのデータモデルの作成

PowerPivotコマンドを使用して、Excelテーブルからデータモデルを作成できます。PowerPivotについては、後の章で詳しく学習します。

すべてのデータモデルコマンドは、リボンの[PowerPivot]タブで使用できます。これらのコマンドを使用して、Excelテーブルをデータモデルに追加できます。

次の販売データワークブックについて考えてみます。ここには、製品、製品ID、および価格を含む製品カタログワークシートがあります。東、北、南、西の4つの地域での販売用に4つのワークシートがあります。

これらの4つのワークシートのそれぞれには、各月の各製品の販売台数と合計金額が含まれています。各地域の各製品の合計金額と各地域の合計売上高を計算する必要があります。

次の手順により、目的の結果に到達できます-

データモデルの作成から始めます。
製品カタログワークシートをクリックします。
リボンの[POWERPIVOT]タブをクリックします。
[データモデルに追加]をクリックします。[テーブルの作成]ダイアログボックスが表示されます。
テーブル範囲を選択します。
[マイテーブルにヘッダーがある]チェックボックスをオンにします。[OK]をクリックします。

新しいウィンドウ– PowerPivot for Excel – <Excelファイル名>が表示されます。

空白のウィンドウの中央に次のメッセージが表示されます-

データモデルに追加したテーブルProductBacklogは、PowerPivotウィンドウにシートとして表示されます。テーブルの各行はレコードであり、ウィンドウの下部にある左右の矢印ボタンを使用してレコードを前後に移動できます。

PowerPivotウィンドウの[リンクテーブル]タブをクリックします。
[Excelテーブルに移動]をクリックします。

Excelデータウィンドウが表示されます。

ワークシートタブ–東をクリックします。
リボンの[POWERPIVOT]タブをクリックします。
[データモデルに追加]をクリックします。

別のシートがPowerPivotウィンドウに表示され、Eastテーブルが表示されます。

ワークシートについて繰り返します–北、南、西。全部で、5つのテーブルをデータモデルに追加しました。PowerPivotウィンドウは次のようになります-

テーブル間の関係の作成

テーブル全体で計算を行う場合は、最初にテーブル間の関係を定義する必要があります。

PowerPivotウィンドウのリボンの[ホーム]タブをクリックします。ご覧のとおり、テーブルはデータビューに表示されます。
ダイアグラムビューをクリックします。

テーブルがダイアグラムビューに表示されます。ご覧のとおり、一部のテーブルが表示領域から外れていて、テーブル内のすべてのフィールドが表示されていない可能性があります。

各テーブルのサイズを変更して、そのテーブルのすべてのフィールドを表示します。
すべてが表示されるように、テーブルをドラッグして配置します。

Eastテーブルで、ProductIDをクリックします。
リボンの[デザイン]タブをクリックします。
[関係の作成]をクリックします。[関係の作成]ダイアログボックスが表示されます。

表の下のボックスに、東が表示されます。[列]の下のボックスに、製品IDが表示されます。

[関連ルックアップテーブル]の下のボックスで、[製品カタログ]を選択します。
製品IDは、関連ルックアップ列の下のボックスに表示されます。
[作成]ボタンをクリックします。

テーブルEastとProductBacklogの関係を表す線が表示されます。

北、南、西のテーブルに対して同じ手順を繰り返します。関係線が表示されます。

データモデルのテーブル内のデータの要約

これで、各地域の各製品の販売データをわずか数ステップで要約する準備が整いました。

[ホーム]タブをクリックします。
[ピボットテーブル]をクリックします。
ドロップダウンリストから[ピボットテーブル]を選択します。

[ピボットテーブルの作成]ダイアログボックスがExcelテーブルウィンドウに表示されます。[新しいワークシート]を選択します。

新しいワークシートに、空のピボットテーブルが表示されます。ご覧のとおり、フィールドリストには、すべてのフィールドが表示されたデータモデルのすべてのテーブルが含まれています。

表1（製品カタログ）から製品IDを選択します。
他の4つの表から[合計金額]を選択します。
∑値の各フィールドについて、[値フィールド設定]の[カスタム名]を変更して、地域名を列ラベルとして表示します。

合計金額は、指定したラベルに置き換えられます。すべてのデータテーブルの値を要約したピボットテーブルに、必要な結果が表示されます。

データモデルへのデータの追加

新しいデータテーブルをデータモデルに追加したり、新しいデータ行をデータモデルの既存のテーブルに追加したりできます。

次の手順で、新しいデータテーブルをデータモデルに追加します。

リボンの[データ]タブをクリックします。
[外部データの取得]グループの[既存の接続]をクリックします。[既存の接続]ダイアログボックスが表示されます。
[テーブル]タブをクリックします。ブック内のすべてのテーブルの名前が表示されます。
データモデルに追加するテーブルの名前をクリックします。

[開く]ボタンをクリックします。[データのインポート]ダイアログボックスが表示されます。

ご存知のように、データテーブルをインポートすると、データモデルに自動的に追加されます。新しく追加されたテーブルがPowerPivotウィンドウに表示されます。

データモデルの既存のテーブルにデータの新しい行を追加します。

データ接続を更新します。データソースからのデータの新しい行がデータモデルに追加されます。