月次の報告書を支店ごとに受け取って一つにまとめる、部署別の申込一覧を集約する——こうした作業をシートのコピー貼り付けで乗り越えてきた方は多いと思います。しかしファイルが増えてくると、貼り忘れ・行ずれ・書式の崩れが起きやすく、毎月同じ手間が繰り返されます。
コピー以外の方法には大きく三つあります。「外部参照(リンク貼り付け)」「Power Query による取り込み」「マクロ(VBA)による自動結合」です。どれが向いているかはファイル数・更新頻度・担当者のExcelスキルによって変わります。この記事では、それぞれの仕組みと向き不向きを整理したうえで、今日の締切に間に合わせるための判断基準と具体的な手順を示します。
作業を始める前に、集約先のファイルと元ファイルを必ずバックアップしてください。外部参照や Power Query はリンク先のパスに依存するため、ファイルを移動すると参照が切れます。操作前にフォルダ構成を固めておくことが先決です。
三つの方法の特徴と向き不向き
外部参照(リンク貼り付け)
別ファイルのセルを =[ファイル名.xlsx]シート名!セル番地 の形式で参照する方法です。元ファイルを開かなくても値を引っ張れますが、元ファイルの行が増減すると参照先がずれます。
向いている場面: 集約するセルの位置が決まっていて変わらない場合。たとえば「各拠点の月次合計を一か所に並べる」程度の単純な集計。
向いていない場面: 元ファイルの行数が毎回変わる一覧表の結合。行が増えても参照式は追いかけてくれません。
Power Query による取り込み
ExcelのPower Query(「データ」タブから利用できる機能)を使い、複数ファイルを読み込んで縦に結合します。一度クエリを設定すれば、次回以降は「更新」ボタン一つで最新データに反映できます。Excel 2016以降では標準搭載されています(画面の表示は版や設定により異なります)。
向いている場面: 同じ列構成のファイルが複数あり、毎月追加される。フォルダ単位でまとめて取り込みたい。
向いていない場面: ファイルごとに列の順序や名前がばらばらな場合。取り込み後に列の整合を合わせる手間が発生します。
マクロ(VBA)による自動結合
VBAでファイルを順番に開き、シートの内容をコピーして閉じる処理を繰り返す方法です。柔軟性は最も高いですが、作成・保守にVBAの知識が必要です。
向いている場面: 列構成がファイルによって異なる、特定の条件行だけを抽出したい、など Power Query では難しい加工が必要なとき。
向いていない場面: VBAを書ける担当者がおらず、引継ぎのリスクを減らしたいとき。
判断フローチャート
次の問いに順番に答えると、方法を選びやすくなります。
- 集約するのはセル単位の値(合計・件数など)か、一覧行の積み上げか?
- セル単位の値 → 外部参照で足りることが多い
- 一覧行の積み上げ → 2へ
- 全ファイルの列名・列順は統一されているか?
- 統一されている → Power Query が最初の選択肢
- バラバラ → VBAか、先に列を統一してから Power Query
- 更新を今後も繰り返すか、一度きりか?
- 繰り返す → Power Query またはVBAで仕組みを作る
- 一度きり → コピー貼り付けで済ませてよい場面もある
Power Query でフォルダ内の複数ファイルを結合する手順
ここでは同じ列構成のExcelファイルが一つのフォルダに入っている場合を扱います。架空の例として、「sales_A支店.xlsx」「sales_B支店.xlsx」「sales_C支店.xlsx」という三つのファイルがあり、それぞれ「日付・担当者・金額」の三列からなる一覧を持つとします(架空の例です)。
準備
- 元ファイルを一つのフォルダにまとめます。フォルダのパスは後で変えないことを前提にします。
- 集約先となる新しいExcelファイルを別の場所に作成します。
操作手順
- 集約先ファイルを開き、「データ」タブをクリックします。
- 「データの取得」→「ファイルから」→「フォルダーから」を選びます。
- 元ファイルが入ったフォルダを指定し、「開く」をクリックします。
- ファイルの一覧が表示されたら「データの変換」を選んでPower Queryエディターを開きます。
- エディター内で「結合」→「ファイルの結合と変換」を選ぶと、各ファイルから最初のシートを読み込むサンプルが表示されます。どのシートを対象にするかここで指定します。
- 「OK」を押すと、フォルダ内の全ファイルが縦に結合された状態でエディターに表示されます。
- 不要な列(ファイルパスの列など)を削除し、列名を整えます。
- 「閉じて読み込む」を押すと、集約先ファイルのシートに結果が書き出されます。
次回以降は、フォルダ内のファイルを更新してから集約先ファイルで「クエリと接続」→「更新」を実行するだけです。
迷いやすい例外:ファイルによってシート名が違う場合
Power Query のフォルダ結合は、指定したシート名(またはシートの順番)をもとに読み込みます。あるファイルだけシート名が「Sheet2」ではなく「データ入力」となっていると、そのファイルのデータが取り込まれないか、エラーになることがあります。取り込み前に元ファイルのシート名を統一しておくか、エディター内でエラー行を確認する手順を挟んでください。
外部参照を使うときの注意点
外部参照の式は =[売上A支店.xlsx]Sheet1!B2 のように書きます。元ファイルを閉じた状態でも値は表示されますが、元ファイルを移動・改名すると参照が切れます。参照が切れると「#REF!」や「ファイルが見つかりません」というエラーが出るため、リンクの状態は「データ」→「リンクの編集」(または「クエリと接続」)で定期的に確認してください。
外部参照は、集約先と元ファイルを同じチームが管理しており、ファイルのパスが変わらないことが前提です。クラウドストレージ上に置いたファイルを参照する場合は、同期の状態によって値が更新されないことがあります。この点は使用しているサービスの仕様を公式ヘルプで確認してください。
VBAで結合する場合の最低限の構造
VBAに慣れていない方への説明として、処理の流れだけ示します。
- 結合先のシートを指定する
- 対象ファイルを一つずつ
Workbooks.Openで開く - 開いたファイルの対象シートからデータ範囲をコピーし、結合先の最終行の次に貼り付ける
- 開いたファイルを保存せずに閉じる
- 全ファイルが終わったら完了
この構造はインターネット上に多くのサンプルがあります。ただし、他者が書いたコードをそのまま実行する前に内容を確認し、個人情報を含むファイルは社内のセキュリティポリシーに従って扱ってください。VBAのコードは社内で引き継げる体制がある場合に採用を検討してください。
次の行動
まずフォルダ内のファイルの列構成が統一されているかを確認してください。統一されていれば Power Query のフォルダ結合が最も再利用しやすい選択肢です。今日だけ間に合わせたいなら手作業のコピーでもかまいませんが、来月以降も同じ作業が続くなら Power Query の設定に30分を使う価値があります。外部参照を使う場合は、ファイルを移動しないルールをチームで共有してから運用してください。
この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。
