はじめに
Excelを使ってデータ分析をしていると、複数のファイルに分かれたデータを統合する必要があることがあります。各ファイルのテーブル構造が複雑な場合、手作業で統合するのは大変ですし、ミスにつながります。
そこで今回は、Power Query を使って 共通のIDを基準に複数のファイルのデータを統合する方法 を解説します。
今回のゴール・やりたいこと
以下のように異なる3つのExcelファイル上に存在するテーブルデータを、共通ID基準で統合し、１つのテーブルにまとめます。
■前提条件
✔ 各ファイルの１列目に共通IDが入っている。
✔ 各ファイルの1行目は列名が入っている。 (ID、商品名、カテゴリ、、など)
✔ 各ファイルから、必要な列のみ指定して結合する。(不要な列は読み込まない。)
サンプルデータ
1. 商品リスト（product_list.xlsx）
| ID | 商品名 | カテゴリ |
|---|---|---|
| P001 | りんご | フルーツ |
| P002 | バナナ | フルーツ |
| P003 | にんじん | 野菜 |
| P004 | じゃがいも | 野菜 |
| P005 | 牛乳 | 飲料 |
2. 価格情報（product_price.xlsx）
| ID | 価格 (円) |
| P001 | 150 |
| P002 | 120 |
| P003 | 90 |
| P004 | 200 |
| P006 | 250 |
3. 在庫情報（product_stock.xlsx）
| ID | 在庫数 |
| P001 | 50 |
| P002 | 30 |
| P003 | 40 |
| P004 | 20 |
| P005 | 10 |
| P007 | 15 |
統合後のデータ（product_merged.xlsx）
| ID | 商品名 | カテゴリ | 価格 (円) | 在庫数 |
| P001 | りんご | フルーツ | 150 | 50 |
| P002 | バナナ | フルーツ | 120 | 30 |
| P003 | にんじん | 野菜 | 90 | 40 |
| P004 | じゃがいも | 野菜 | 200 | 20 |
| P005 | 牛乳 | 飲料 | – | 10 |
| P006 | – | – | 250 | – |
| P007 | – | – | – | 15 |
Power Query を使ったデータ統合手順
1. Power Query でデータを取得する
- 「データ」タブ → 「データの取得」 → 「ファイル」 → 「Excelブック」 を選択
- 「product_list.xlsx」を開く
- 「product_list」シートのデータを選択し、「データの変換」をクリック
- 「閉じて次に読み込む」を選択し、Power Query エディタを開いたままにする
同様に、「product_price.xlsx」「product_stock.xlsx」 のデータも取得しておきます。
※今回はExcelファイル全体をデータ範囲として取得する例を紹介していますが、あらかじめテーブル化したデータ範囲を読み込むことも可能です。
2. ID を基準にテーブルを結合する
1つ目の結合（商品リスト × 価格情報）
- 「product_list」テーブルを選択し、「ホーム」タブ → 「クエリのマージ」をクリック
- 「product_price」を選択し、「ID」列をキーにして結合（左外部結合）
- 展開アイコン（🔽）をクリックし、「価格 (円)」のみ選択して展開 (この作業で必要な列のみ読み込める)
2つ目の結合（商品リスト＋価格情報 × 在庫情報）
- 「ホーム」タブ → 「クエリのマージ」 をもう一度クリック
- 「product_stock」を選択し、「ID」列をキーにして結合（左外部結合）
- 展開アイコン（🔽）をクリックし、「在庫数」のみ選択して展開
3. データの整理と仕上げ
- 必要に応じて、列毎のデータ型を確認（価格や在庫は数値型にする等。よく分からなければスキップ可）
- 空欄（null）を「-」に置き換える
- 「変換」タブ → 「置換」 → null を「-」に置き換える
- 「閉じて読み込む」で 表示中のExcel に読み込み、テーブルとして保存
IDを基準に、必要な列のみ結合されたデータがテーブルとして得られます。
4. データの更新
各ファイルが更新されたり、IDが追加されたら、「データ」タブ→クエリと接続の「すべて更新」や「更新」を押せば、各ファイルから最新の内容が反映されます。
Power Query を使うメリット
✅ 手作業なしでデータ統合可能 → IDが増えても「更新」ボタンで即反映できます。
✅ データ構造が異なっていてもマージ可能 → 各ファイルの列や項目が増えても、必要な列だけ取り出せる。
✅ 毎回データをコピー＆ペーストする手間がゼロに。単純ミスも減る。
まとめ
Power Queryを活用すると、複雑なデータの管理が楽になります。「毎回手作業でコピペするのが面倒…」 という方は、ぜひ挑戦してみてください！
コメント