VLOOKUP関数を使って大量のデータ連携をすると、重くて動かなくなる経験ってありますよね… 。Excel機能であるPowerPivotのリレーションシップというデータ連携を使えば、簡単かつ重くならないのでVLOOKUPは不要です!
【本記事で得られること】
・ExcelでVLOOKUP関数に頼らないデータ連携
・PowerPivotのリレーションシップの設定方法
・複数データテーブルの連携
・連携の設定が分かる(多対1など)
パワーピポットのリレーション設定は、簡単に複数のデータの連携ができるだけでなく、データ動作も軽いので、非常によく使います!
パワーピポットでリレーションシップの設定
VLOOKUP関数を使わない、簡単なデータ連携方法であるリレーションシップの設定方法を紹介します。
◆リレーションシップ機能でデータ連携
今回、下記のように、キー項目でデータを連携していきます。商品コードをキーに売上データと商品マスタテーブルを繋げ、地域コードをキーに売上データと地域マスタテーブルを繋げています。
![PowerPivot データ連携 パワーピポット リレーション](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-13-1.jpg)
リレーションでデータ連携させると、VLOOKUP関数を使いまくって連携させる方法と比べ、圧倒的に軽いのでデータ量が多くてもサクサク動きます!
【関連記事】
▶パワークエリ、パワーピポットまとめ
▶PowerBIのリレーション設定
▶パワークエリの処理速度を速くする方法
【結論】
VLOOKUP関数を使ったデータ連携は重い!
パワーピポットのリレーション設定は簡単かつ軽い!
それでは、PowerPivotでキー同士で複数テーブルの連携をしていきましょう!
①データのテーブル化
リレーション設定をするためには、データを全てテーブル化しておく必要があります。
今回使用するデータは
・売上データ
・商品マスタ
・地域マスタ
の3つです。
![PowerPivot 売上データ](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-15-777x1024.png)
![PowerPivot 商品マスタ、地域マスタ](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-14.png)
こちらのデータをテーブル化していきます。
テーブル化したいデータのどこかのセルをクリックして、
「挿入」タブ→「テーブル」
![パワーピポット テーブル化](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-33.png)
テーブル化する範囲と先頭行をテーブルの見出しとして使用するかを聞かれますので、チェックを入れてOKボタンをクリック
![PowerPivot パワーピポット テーブル化](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-16.png)
テーブル化できました!
![PowerPivot パワーピポット テーブル化 パワークエリ](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-18.png)
混乱しないようにテーブル名を変更しておきましょう!
![PowerPivot テーブル名変更](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-19.png)
残りのマスタテーブルも同様にテーブル化しておけば、リレーション設定の下準備はOKです。
【結論】
リレーションシップ(データ連携)の下準備として、全てのデータをテーブル化する!
②リレーションシップで複数テーブルの連携
データをテーブル化した後は、データ連携の肝である、リレーションシップを設定していきます。
データタブ→PowerPivotウィンドウに移動というアイコンをクリックしてPowerPivotウィンドウを開きます。※PowerPivotのアドインを有効化していない方は有効化しておきましょう。
![PowerPivotウィンドウ](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-20.png)
PowerPivotウィンドウが開いたら、
ホームタブの表示にある「ダイアグラムビュー」をクリックします。
![PowerPivotウィンドウ ダイアグラムビュー](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-22.png)
![ダイアグラムビュー リレーション パワークエリ パワーピポット PowerPivot](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-23.png)
テーブル化したデータが表示されるので、ここでリレーション設定をしていきます。
![リレーション データ](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-24.png)
リレーション設定は、連携したいキー項目をドラック&ドロップすればOKです。下記の場合ですと、商品コードをキーとして、売上データと商品マスタを連携し、地域コードをキーとして、売上データと地域マスタを連携しています。
![リレーション PowerPivot パワーピポット](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-25.png)
これでリレーションシップ機能を使ったデータの連携が完了です!簡単ですね!
リレーションシップの連携設定には、多対1、多対多などがありますが、今回は多対1でつながっていることを意味します。例えば
・売上データの商品コード:多(*)
・商品マスタの商品コード:1
で連携されており、多は商品コードに重複した複数データがあり、1は重複データのない一意の値のみであることを意味しています。
リレーションシップの設定は多対1が基本であり、マスタテーブルはキー項目に重複がないように、事前に重複削除などの対応をしておくことがエラーを避けるポイントです。
【関連記事】
▶パワークエリで重複削除したデータ加工
▶PowerBIでリレーションを使ったデータ連携
【結論】
パワーピポットウィンドウのダイアグラムビューで連携させたいキーをドラック&ドロップしてデータ連携!
③ピポットテーブルの作成
次に連携したデータを使ってピポットテーブルを作成していきます。
PowerPivotウィンドウのホームタブから、ピポットテーブルをクリックします。
![ピポットテーブル リレーション PowerPivot Excel](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-26.png)
データ連携しているデータ項目がピポットテーブルに使えます!それぞれ、欲しい形にピポットテーブルに行や列、値を設定していきましょう!
![PowerPivot ピポットテーブル データ連携 Excel リレーション](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-27-641x1024.png)
リレーションしたデータでのピポットテーブルの作成ができました!
![ピポットテーブル リレーション PowerPivot パワークエリ Excel](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-28-1024x481.png)
【結論】
リレーション設定をしておけば、複数テーブルのデータを使ってピポットテーブルを作成できる!
Excelのデータタブからのリレーション設定
最後に、別のリレーション設定方法も紹介します。こちらはパワーピポットの管理画面(PowerPivotウィンドウ)までいかなくても、リレーション設定ができます。しかし、複数テーブルのデータ連携やデータ同士の関係性が分かりづらいので、ダイアグラムビューを使う方法のほうがおすすめです。
Excelのデータタブのデータツールの場所にある、リレーションシップのアイコンをクリックします。
![Excel リレーション設定 データ連携](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-29.png)
リレーションシップの管理画面が開くので、新規作成から連携の設定をしていきます。下記のように、テーブル名と列にキー項目を指定すればOKです。商品コードをキーに売上データと商品マスタテーブルを連携させています。※マスタテーブルを関連テーブルに設定するようにしましょう。
![リレーションシップ Excel データ連携](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-30-1024x307.png)
同様に、地域コードをキーに売上データと地域マスタテーブルを連携させます。
![リレーションシップ Excel データ連携](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-32-1024x303.png)
リレーションシップの管理の場所にテーブルと関連ルックアップテーブルが表示されます。どのテーブルがどのキーで連携しているかを確認できます。
![リレーションシップの管理 データ連携](https://power-bi-seminar.site/wp-content/uploads/2023/06/image-31-1024x336.png)
【結論】データタブ→リレーションシップからもデータ連携の設定ができる
Excelでリレーション、データ連携まとめ
今回はVLOOKUP関数を使わなくても簡単にデータ連携ができる、リレーションシップの設定方法を紹介しました。
ポイントは
・VLOOKUP関数の連携は重い!
パワーピポットのリレーション連携は簡単&軽い!
・リレーション前の下準備としてテーブル化
・PowerPivotウィンドウのダイアグラムビューから
キー項目をドラック&ドロップしてデータ連携
・リレーション設定をしていれば、ピポットテーブルに
複数テーブルのデータを使える!
リレーションシップ機能を使ったデータ連携は、現代の新Excel時代において必須です。VLOOKUP関数ばかりに頼っていては、膨大なデータ分析などには対処しきれませんので、ぜひPowerPivotのリレーション設定で、重いVLOOKUP関数からの卒業を目指しましょう!
パワーピポットやPowerBIの関数であるDAX関数についてのまとめ記事は下記をご参考ください。
▶DAX関数まとめ(構文、事例紹介)
パワークエリでデータ加工ができれば、パワーピポットやPowerBIでデータ分析、見える化が可能です。PowerBIの使い方、学習方法は下記をご参考ください。
▶PowerBI、PowerQueryの便利機能、学習方法まとめ
良ければ、☟クリック応援よろしくお願いします!
コメント