PowerPivot データモデルをリレーションシップで結合する

Power Query 第26回目です。Power Pivot 2回目です。
Power Query でデータモデルに変換されたテーブルを Power Pivot でリレーションシップを設定して結合する方法を勉強します。

くるみこ
くるみこ

今回は Power Pivot でリレーションシップの設定方法を勉強しましょう。
Excelで頻繁に使っているVLOOKUP関数や通常のピボットテーブルと比較しながら見ていきましょう。

リレーションシップという言葉はよく聞きます。でも、実際にどんなものかよくわからないのでよろしくお願いしますm(__)m

【この記事でわかることは】
・Power Pivot でデータモデルのリレーションシップを設定する方法がわかります
Power Pivot でデータモデルで Pivit Table を作成する方法がわかります

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

・Power Pivot を有効にする方法とPower Pivot から Pivit Table を作成する手順がわかります。

記事作成にあたっては次の Microsoft office のサポートの情報などを参照しています。
Power Query for Excel のヘルプ
Excel の Power Query について
Power Pivot in Microsoft Excel の使用を開始する

なお、この記事の画像は Microsoft 365 で取得した画像です。Excel2016 のリボンやメニューなどバージョンによって違うようなので、適宜読み替えをお願いします(^^)/

過去のピボットテーブルについての記事はこちらからアクセスできます。

今までの内容は、Power Query を使っていない Excel での内容ですが少しは参考になると思います。

リレーションシップを設定する

「テーブルのリレーションシップ」は Power Pivot でなくても設定できます。ただし、その場合は「テーブル」がワークシート上にある必要があります。前回の記事の中で「PowerPivot と Excel での作業を比較」を行っていますが、不要なメモリが消費されたり、データ量が制限されます。

それに対して、Power Pivot では「データ モデル」を使うことでメモリが節約され、データ量も制限を超えて利用することができるようになります。 以下、実際の手順を記載していきたいと思います。

Power Query でデータモデルを作成する

Power Pivot で扱うデータは、Power Query でソースデータを取り込む際に、データのインポート設定で次のように設定します。

読み込み時の場合は[閉じて読み込む…]選択します。後から設定する場合は「クエリと接続」から設定するクエリ上で右クリック > [読み込み先…]を選択すると左のダイアログが表示されます。

・「接続の作成のみ」を選択し、「□このデータをデータモデルに追加する」に を入れて [OK] を選択します。

・こうすることで、サイズが大幅に削減できます。ワークシートにはテーブルは表示されず、メモリ上のデータモデルにテーブルが格納されます。(Power Pivot ウィンドウでテーブルを確認できます)

・データモデルに追加した3つのクエリがこちらです。

Power Pivotでリレーションシップを設定

PowerPivot ウィンドウを開きます

[データ] > [データツール]グループ内に > [データモデルの管理] がある場合(下図参照)

または [Power Pivot] タブ > [管理]リボンをクリックします。

すると 「Power Pivot for Excel」ウィンドウが表示されます。

・下の画像は「Power Pivot for Excel」でテーブルの [データビュー] が表示されています。

・ [ダイアグラム ビュー] が表示されました。

ダイアグラムビューで設定してみます

・ここで、Drag&Dropだけでテーブル間のリレーションシップの作成を行うことができます。

・「フィルターの方向」と「基数」が 多対一(*:1)であることなどが確認できます。

リレーションシップを確認してみます

・[デザイン]タブ > [リレーションシップ] > [リレーションシップの管理]をクリックします。

・「リレーションシップの管理」ダイアログが表示されました。

・先ほど設定したリレーションシップの設定状況が表示されています。

・[編集]クリックで表示されるダイアログ

・[作成]クリックで表示されるダイアログ

・ リレーションシップの作成は、[デザイン]タブ > [リレーションシップ] > [リレーションシップの管理] > [リレーションシップ作成] からも作成できることがわかりました。

Power Pivot でピボットテーブルを作成

リレーションシップが設定できたデータモデルでピボットテーブルを作成してみましょう。

ピボットテーブルを作成します

「Power Pivot for Excel」の [ホーム]タブ > [ピボットテーブル] をクリックします。

・「ピボットテーブルの作成」ダイアログで作成場所を指定します。

・[OK] 選択で空のピボットテーブルが作成・表示されます。

「データモデル」と「テーブル」の見分け方

「データモデル」とワークシート上の「テーブル」を見分ける方法は、次の画像をご覧ください。

・画像は、クエリ作成時に [接続の作成のみ] ではなく [テーブル] を設定していた場合のものです。

・両方が表示されている状態なので、選択を間違えないようにしましょう!

・Excel から [挿入] > [ピボットテーブル] で作成する場合

・左図の「ピボットテーブルの作成」ダイアログが表示されます。

・[このデータモデルを使用する] を選択します。

・設定後 [OK] 選択で先ほどと同じ空のピボットテーブルが作成されます。

ピボットテーブルのフィールドを設定します

各データモデルの項目をピボットテーブルのフィールドに配置します。

項目を選ぶ時は(1対多)の1を選びます

・データ部分は「多」ですよね。

・項目の部分は「1対多」の「1」を選択して配置します。
 「多」を配置してしまうと、状況によりデータが分散してしまいますので注意しましょう!

・ちょっと変な画像ですが、何となくイメージしてもらえればよいのかなぁ

スポンサーリンク

まとめ(おわりに)

以上、Power Pivot で デーらモデルをリレーションシップで結合する方法について勉強しました。
すこし、少しあっさりした内容でしたが概略や手順を紹介しました。

まとめと感想など

くるみこ
くるみこ

Power Pivot で簡単にリレーションシップを設定出来ることが確認できましたね。ピボットテーブルの設定方法と注意点も確認できました。いかがでしたか?

そうですね。いろいろなケースをもっと経験していかないと、まだまだ実感がわきませんね。今日勉強したことは、忘れないうちに復習しながらいろいろ試してみたいです(^^)/

【今回わかったことは】
・Power Pivot でデータモデルのリレーションシップを設定する方法がわかりました
・Power Pivot でデータモデルで Pivit Table を作成する方法がわかりました

★★★ ランキング参加中! クリックしてね(^^)/ ★★★

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/

各記事のサンプルファイルをダウンロードできます

今回記事のサンプルファイルを一応登録しておきます
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら(このリンク先)からご利用ください