【Excel】Power Queryで複数シートのデータを縦に結合する方法

Excelで複数のシートに分かれたデータを一つにまとめたい場面は多いでしょう。

特に、月次レポートや支店ごとの売上データなど、同じ形式の表がシートごとに存在する場合、手作業でのコピー&ペーストは非常に手間がかかります。

この記事では、ExcelのPower Query機能を使って、これらの複数シートのデータを効率的に縦方向に結合する方法を解説します。

Power Queryを使えば、一度設定しておけばデータが更新されてもすぐに反映できるため、業務効率が劇的に向上します。

Power Queryによる複数シート結合の仕組み

Power Queryは、Excelに搭載された強力なデータ整形・加工ツールです。様々なデータソースからデータを取得し、複雑な変換処理をGUI操作で行えます。

複数シートのデータを縦に結合する際、Power Queryはまず、Excelブック内のすべてのシートをリストとして取得します。次に、そのリストから結合したいシートをフィルタリングします。最後に、各シートからテーブルデータを抽出し、それらを指定された順序で連結(Append)します。この一連の処理を「クエリ」として保存し、再利用可能にします。

この機能を利用するには、結合したい各シートにテーブルが設定されていることが前提となります。テーブルになっていない場合、Power Queryはシート全体を単一のデータとして認識するため、期待通りの結合ができません。

Excelブック内の全シートを縦に結合する手順

ここでは、Excelブック内に存在するすべてのシートのデータを、Power Queryを使って縦に結合する基本的な手順を解説します。

1. Power Queryエディターを開く
   Excelのリボンメニューから「データ」タブを選択します。「データの取得と変換」グループにある「データの取得」をクリックし、「ファイルから」>「Excelブックから」を選びます。
2. Excelブックを選択する
   結合したいデータが含まれるExcelブックファイルを選択し、「インポート」をクリックします。
3. ナビゲーターウィンドウで「シート」を選択する
   「ナビゲーター」ウィンドウが表示されたら、左側のペインで「シート」を選択します。これにより、ブック内のすべてのシートが一覧表示されます。
4. 「データの変換」をクリックする
   「シート」を選択した状態で、ウィンドウ下部にある「データの変換」ボタンをクリックします。これにより、Power Queryエディターが起動します。
5. 「すべての列を結合」機能を利用する
   Power Queryエディターの「ホーム」タブにある「結合」グループから「すべての列を結合」をクリックします。
6. 結合設定を行う
   「テーブルの結合」ダイアログが表示されます。
   「結合するテーブル」で、結合したいデータが含まれるシートのテーブルを選択します。通常、ブック内のすべてのシートを対象にする場合は、ここで「シート」を選択します。
   「プレビュー」で、結合結果のイメージを確認できます。
7. 「OK」をクリックする
   設定内容を確認し、「OK」をクリックします。
8. 結合されたデータを確認・整形する
   Power Queryエディターに、すべてのシートのデータが縦に結合された結果が表示されます。
   必要に応じて、不要な列の削除、データ型の変更、列名の修正などの整形を行います。
9. データをExcelに読み込む
   整形が完了したら、「ホーム」タブの「閉じて読み込む」をクリックします。「閉じて読み込む」の▼をクリックし、「閉じて次に読み込む」を選択すると、読み込み先(テーブル、ピボットテーブルなど)を指定するダイアログが表示されます。

特定のシートのみを縦に結合する方法

ブック内のすべてのシートではなく、特定のシートだけを結合したい場合もあります。その場合は、以下の手順でシートをフィルタリングします。

1. Power Queryエディターを開く
   上記の手順1〜4と同様に、Power Queryエディターを起動します。
2. 「シート」クエリを選択する
   左側の「クエリ」ペインで、「シート」という名前のクエリを選択します。これは、ブック内のすべてのシートのリストを表します。
3. シート名をフィルタリングする
   「シート」クエリの「Name」列(シート名が表示されている列)のヘッダーにあるフィルターボタンをクリックします。
4. 結合したいシートを選択する
   表示されるリストから、結合したいシート名のチェックボックスのみをオンにします。不要なシートはオフにします。
5. 「OK」をクリックする
   選択したシートのみが残った状態で、「OK」をクリックします。
6. 「すべての列を結合」機能で結合する
   次に、「ホーム」タブの「結合」グループから「すべての列を結合」をクリックします。
7. 結合設定を行い「OK」をクリックする
   ダイアログが表示されたら、「結合するテーブル」で「シート」を選択し、「OK」をクリックします。
8. データをExcelに読み込む
   結合結果を確認・整形後、「閉じて読み込む」でExcelシートにデータを読み込みます。

結合時の注意点とよくある失敗例

Power Queryでのデータ結合は非常に便利ですが、いくつかの注意点や失敗しやすいポイントがあります。

各シートがテーブル形式になっていない

Power Queryでシートを結合する際、各シートの内容は「テーブル」として認識されている必要があります。もし、テーブルになっていない場合、シート全体が1つのデータとして扱われたり、予期しない形でデータが結合されたりすることがあります。

対処法:

1. 各シートをテーブルに変換する
   結合したいデータが含まれる各シートを開きます。データ範囲を選択し、「挿入」タブの「テーブル」をクリックします。ダイアログで範囲を確認し、「先頭行をテーブルの見出しとして使用する」にチェックを入れて「OK」をクリックします。
2. Power Queryを更新する
   テーブル変換後、Power Queryエディターでクエリを更新(「ホーム」タブの「すべて更新」)するか、再度データ取得からやり直します。

結合するシート間で列名やデータ型が一致しない

結合するシート間で、列名が異なったり、同じ列でもデータ型(数値、文字列、日付など)が異なったりすると、結合時にエラーが発生したり、データが正しく結合されなかったりします。特に、列名の大文字・小文字の違いも区別されるため注意が必要です。

対処法:

1. 結合前の整形を徹底する
   Power Queryエディターで、結合する前に各シートのクエリ(または、シートをフィルタリングした後のクエリ)を開き、列名を統一し、データ型を揃えます。
2. 「列の追加」機能で列を揃える
   もし、一部のシートにしか存在しない列がある場合は、「ホーム」タブの「列の追加」>「カスタム列」などで、不足している列を作成し、デフォルト値(例:0や空欄)を設定して列を揃えることも可能です。

結合後に不要な列が含まれる

「すべての列を結合」機能は、すべての列をそのまま結合するため、シートによっては不要な列が含まれることがあります。例えば、シートごとに作成日や担当者名が異なる場合、それらがすべて結合結果に含まれてしまうことがあります。

対処法:

1. 結合後のクエリで列を削除する
   結合が完了したクエリを開き、「ホーム」タブの「列の管理」>「列の選択」または、不要な列ヘッダーのフィルターボタンから「列の削除」を選択して、不要な列を取り除きます。
2. 結合前のクエリで列を削除する
   もし、すべてのシートで共通して不要な列がある場合は、結合前の各シートのクエリで列を削除しておくと、より効率的です。

結合順序が意図しないものになる

「すべての列を結合」機能では、結合するシートの順序をある程度指定できますが、シートの追加や削除を繰り返すと、意図しない順序で結合されてしまうことがあります。特に、シート名でフィルタリングした場合、シート名のアルファベット順などで結合されることがあります。

対処法:

1. 「カスタム列の追加」で結合順序を制御する
   「シート」クエリを選択した状態で、「列の追加」タブから「カスタム列」を選択します。新しい列を作成し、結合したい順序を示す数値を入力します(例:1月シートなら1、2月シートなら2)。
2. カスタム列で並べ替える
   作成したカスタム列(例:「順序」列)を基準に、シートのリストを並べ替えます。
3. 結合処理に進む
   並べ替え後、このシートリストを基に「すべての列を結合」処理を実行します。

Power QueryとVBAの使い分け

複数シートのデータを縦に結合する方法として、VBA(Visual Basic for Applications)マクロを使用することも考えられます。しかし、Power QueryとVBAにはそれぞれ得意な場面があります。

項目	Power Query	VBA
操作の容易さ	GUI操作が中心で、プログラミング知識は不要	VBAの知識が必要
データ更新の容易さ	クエリを更新するだけで自動反映	マクロの実行が必要
複雑なデータ変換	GUIで直感的に、またはM言語で高度な処理が可能	コード記述で柔軟に対応可能
処理速度	大量データの場合、VBAより高速な場合がある	処理内容によっては非常に高速
学習コスト	比較的低い	高い
セキュリティ	マクロ無効化の影響を受けにくい	マクロ有効化のセキュリティ設定が必要

一般的に、定型的なデータ結合や、データソースがExcelファイルやデータベースなど、Power Queryが直接接続できるものであれば、Power Queryの利用が推奨されます。一方、Excelのオブジェクト(グラフ、図形など)を操作したり、より複雑な条件分岐やエラーハンドリングが必要な場合は、VBAが適していることがあります。

今回の「複数シートの縦方向結合」というタスクにおいては、Power Queryの「すべての列を結合」機能が非常に効率的で、特別なプログラミング知識も不要なため、最も推奨される方法です。

ExcelのPower Query機能を使えば、複数シートに分散したデータを、手作業でコピー&ペーストすることなく、効率的に縦方向に結合できます。

今回解説した手順で、シートをテーブルに変換し、Power Queryエディターで「すべての列を結合」機能を使えば、データ更新時の手間を大幅に削減できます。

ぜひ、この機能を活用して、日々のデータ集計作業を効率化してください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】