【Power Query】フォルダ内の複数のExcelファイルを1つにまとめる — 配ったテンプレートのシート名・見出しがずれていても止まらない方法

Excel
この記事は約11分で読めます。

配ったテンプレートで集めたExcelファイルを、Power Query で1つにまとめようとしたら、エラーで止まった。あるいは、まとまったのに合計が合わない。よくある原因は、届いたファイルの一部が作り替えられていることです。

直すのは「サンプル ファイルの変換」というクエリ1つです。シートを名前ではなく位置で取り、見出しの行を探し、列名をそろえる。この3か所を書き換えると、作り替えられたファイルも同じ表にまとまります。

筆者も、配ったテンプレートが人の手で作り替えられ、シート名や見出しがファイルごとにずれたことがあります。この記事は、その「ずれ」がある前提での作り方です。

この記事で分かること

  • フォルダー内の複数のExcelファイルを、Power Query で1つの表にまとめる手順
  • CSVをまとめるときとの違い
  • シート名・見出しの位置・列名がずれたファイルで起きること
  • ずれを吸収する「サンプル ファイルの変換」の書き換え方
  • まとめたあとに、取りこぼしが無いかを確かめる方法

4つのファイルを1つにまとめた結果（Power Query エディター）

どんな場面で使うか

  • 拠点や担当者ごとにテンプレートを配り、毎月の報告をExcelで集めている
  • 届いたファイルを1つずつ開いて、1枚のシートに貼り付けている
  • CSVをまとめる手順をまねたら、Excelファイルでは途中で止まった

テンプレートを配って集めると、全部が同じ形で返ってくるとは限りません。シート名を拠点名に変える人、上にタイトルを足す人、見出しを打ち直す人がいます。どれも見た目にはほとんど違いが分かりません。

Power Query の標準の手順は、どのファイルも1つ目と同じ形だという前提で動きます。そのため、1か所ずれただけでエラーになったり、値が黙って抜けたりします。

基本説明

CSVとの違いは「シートを選ぶ段」

フォルダー内のファイルをまとめる流れは、CSVのときと同じです。大部分はフォルダ内のCSVを一括取り込みする記事と共通です。

違うのは、Excelファイルの中にシートがあることです。結合するときに、どのシートを読むかを選ぶ段が1つ増えます。

ここで選んだシートは、名前で記録されます。ほかのファイルも、同じ名前のシートを探して読みます。

使うデータ

サンプルファイル（記事末尾）は、拠点別の10月の報告4つです。千葉が配ったままの形で、残りの3つは1か所ずつ作り替えられています。

ファイル 作り替えられたところ 件数 数量の合計
千葉 なし（配ったまま） 5 640
神戸 1行目にタイトル、2行目が空白。見出しは3行目 4 275
大阪 シート名が「報告」から「大阪」に変わった 4 350
仙台 C列の見出しが「数量」ではなく「個数」 3 310

4つを合わせると16件、数量の合計は1,575です。これが、まとめた結果を確かめるときの答えになります。

千葉のファイルです。シート名は「報告」で、1行目が見出しです。

千葉のファイル（配ったままの形）

神戸のファイルです。見出しの上に、タイトルと空白の行があります。

神戸のファイル（見出しの上にタイトル行がある）

大阪のファイルです。中身は同じ形ですが、シート名が「大阪」です。

大阪のファイル（シート名が「大阪」）

仙台のファイルです。C列の見出しが「個数」です。

仙台のファイル（C列の見出しが「個数」）

ずれがあると何が起きるか

シート名が違うファイルでは、選んだ名前のシートが見つからず、エラーで止まります。

見出しの位置や列名が違うファイルでは、エラーで止まるか、その列が null のまま結合されます。null になる場合はエラーが出ないので、合計が少ないことに気づきにくくなります。

手順

ステップ1: 4つのファイルを1つのフォルダーに入れる

  1. 「月次報告」のような名前で、新しいフォルダーを作る
  2. サンプルの4ファイルをダウンロードし、そのフォルダーに入れる
  3. フォルダーには、まとめるExcelファイル以外を入れない

ファイル名は、末尾が chiba・kobe・osaka・sendai の4つです。名前の順で、千葉が先頭に来ます。

ステップ2: 「フォルダーから」で結合する

  1. 新しいブックを開き、「データ」タブ →「データの取得」→「ファイルから」→「フォルダーから」を選ぶ
  2. ステップ1のフォルダーを選んで「開く」を押す
  3. ファイルの一覧が出たら、「結合」の▼から「データの結合と変換」を選ぶ
  4. 「ファイルの結合」画面で、「サンプル ファイル」は「最初のファイル」のままにする
  5. 左の一覧から「報告」シートを選び、「OK」を押す

CSVのときとの違いは手順5です。ここで「報告」という名前が記録されます。

ステップ3: どのファイルで止まったかを確かめる

Power Query エディターが開くと、プレビューの最後に、すべての列が「Error」の行が出ます。その行の「Error」の文字ではなく、セルの空いているところをクリックすると、下に Expression.Error のメッセージが出ます。キーがテーブルのどの行とも一致しない、という内容です。

その上の神戸の行が null になっているのも、ずれの影響です。ステップ4でまとめて直ります。

大阪のファイルで止まったところ

メッセージの Item=報告 は、探したシートの名前です。Error の行は神戸の次なので、原因はファイル名の順で次に読む大阪のファイルです。ステップ2で選んだ「報告」シートが、大阪のファイルにはありません。

左の「クエリ」の一覧には、自動でいくつかのクエリができています。このうち「サンプル ファイルの変換」が、1ファイルぶんの読み方を決めています。ここを直すと、全部のファイルに同じ直しが効きます。

ステップ4: 「サンプル ファイルの変換」を書き換える

  1. 左の一覧で「サンプル ファイルの変換」（版によって、後ろにフォルダー名が付く）を選ぶ
  2. 「ホーム」タブ →「詳細エディター」を押す
  3. 先頭の let と ソース = … の2行は残し、その下をすべて消して、下のコードを貼り付ける
  4. 「完了」を押す
    シートだけ = Table.SelectRows(ソース, each [Kind] = "Sheet"),
    ナビゲーション = シートだけ{0}[Data],
    見出しまで削除 = Table.Skip(ナビゲーション, each [Column1] <> "日付"),
    昇格されたヘッダー数 = Table.PromoteHeaders(見出しまで削除, [PromoteAllScalars = true]),
    列名をそろえる = Table.RenameColumns(昇格されたヘッダー数, {{"個数", "数量"}}, MissingField.Ignore)
in
    列名をそろえる

書き換えたあとの「サンプル ファイルの変換」

「適用したステップ」に、コードの行が1つずつ並びます。プレビューは千葉のファイルの5行です。

書き換えたのは次の3か所です。

ステップ していること 受け持つずれ
シートだけ・ナビゲーション シートだけに絞り、名前ではなく1枚目を取る 大阪（シート名）
見出しまで削除 A列が「日付」の行より上を捨てる 神戸（タイトル行）
列名をそろえる 「個数」の列があれば「数量」に直す 仙台（列名）

MissingField.Ignore は、「個数」の列が無いファイルでもエラーにしない指定です。千葉・神戸・大阪には「個数」が無いので、これが無いとそこで止まります。

ステップ5: 結果を確かめて読み込む

  1. 左の一覧で、フォルダー名と同じ名前のクエリ（例では「月次報告」）を選ぶ
  2. 「Source.Name」列に、4つのファイル名が出ていることを確かめる
  3. 「日付」列の見出しの左端のアイコン（ABC と 123 の表示）を押し、「日付」を選ぶ
  4. 「ホーム」タブ →「閉じて読み込む」を押す
  5. 読み込まれた表の「数量」列を選び、Excelの画面右下の合計を見る

手順3で「列タイプの変更」の確認が出たときは、「現在のものを置換」を押します。手順3を飛ばすと、シートの日付が 46296 のような数字になります。自動でできた「変更された型」のステップでは、日付の列の型が決まっていないためです。

表は16行、合計は1,575になります。「使うデータ」の表と同じなら、取りこぼしはありません。

Source.Name 列は、その行がどのファイルから来たかを示します。合わないときは、この列でファイルごとに絞り込むと、どのファイルが原因かが分かります。

翌月からは、フォルダーにファイルを足して「データ」タブ →「すべて更新」を押すだけです。

サンプルコードまたは例

例：書き換えた行を1つ外すと何が起きるか

3か所は、それぞれ別のずれを受け持っています。1つ外すと、受け持っていたファイルで次のことが起きます。

外したステップ 起きること 数量の合計
シートだけ・ナビゲーション（名前で取る形に戻す） 大阪のファイルでエラーになり、読み込めない —
見出しまで削除 神戸の行が6行に増え、日付・品目・数量・担当者がすべて null になる 1,300
列名をそろえる 仙台の3行で、数量が null になる 1,265

試すときは、外したステップの次の行が参照している名前も、1つ前のステップに直します。

下の2つは、エラーが出ません。行は増えたり残ったりするので、表だけ見ると正しくまとまったように見えます。件数と合計を元のファイルと突き合わせるのは、このためです。

動作確認

ステップ4のコードが手元で動くかは、次の順に確かめます。

  1. サンプルの4ファイルだけを入れたフォルダーを用意する（ステップ1）
  2. 新しいブックで、ステップ2〜4を行う
  3. 「月次報告」クエリを選び、プレビューを見る
  4. 「日付」列の型を日付にして、「閉じて読み込む」で新しいシートに表を出す

成功すると、次のようになります。

  • 表は16行で、Source.Name 列に4つのファイル名が出る
  • 日付・品目・数量・担当者の列に、null の行が無い
  • 日付の列が 2026/10/01 のような日付で出る
  • 数量の合計が1,575になる
  • 神戸の4行の数量は60・100・75・40、仙台の3行の数量は130・110・70になる

よくあるエラー

「キーがテーブルのどの行とも一致しない」というエラーが出る

原因：ステップ2で選んだ名前のシートが、どれかのファイルに無い。

直し方：ステップ4のコードで、シートを1枚目で取る形にする。

シートを足されたファイルで、別の表が混ざる

原因：ステップ4のコードはシートの1枚目を取るので、前に別のシートを足されると、そちらを読むことがある。

見分け方：Source.Name 列でそのファイルに絞ると、見覚えの無い値や null が並ぶ。

直し方：足されたシートを後ろへ移してもらうか、テンプレートのシートだけを残してもらう。

見出しを別の名前にされた列が null になる

原因：列名がそろっていない。エラーは出ない。

見分け方：「数量」列の▼を開くと、一覧に (null) が出る。Source.Name 列で、どのファイルの行かを確かめる。

直し方：「列名をそろえる」のステップに組を足す。たとえば {{"個数", "数量"}, {"数", "数量"}} のように並べる。

ファイルの行が全列 null の1行だけになる

原因：そのファイルで「日付」の見出しが打ち直された、または左に列が足された。「見出しまで削除」は A列が「日付」とぴったり同じ行を探すので、見つからないと全行を読み飛ばす。エラーは出ず、そのファイルは全列が null の1行だけになる。

見分け方：Source.Name 列でそのファイルに絞ると、日付・品目・数量・担当者がすべて null の行が1行だけ出る。前の節との違いは、一部の列だけ null か、全列が null か。

直し方：見出しをテンプレートどおりに戻してもらう。左に列を足された場合も同じ。「列名をそろえる」に組を足しても、この場合は効かない。

フォルダーを移したら、フォルダーが見つからないというエラーが出る

原因：フォルダーの場所が、フォルダー名のクエリの「ソース」ステップにそのまま書かれている。

直し方：フォルダー名のクエリを選び、「適用したステップ」の「ソース」の歯車を押して、新しい場所を選び直す。

まとめ

  • Excelファイルをまとめる手順はCSVとほぼ同じで、違いはシートを選ぶ段
  • 選んだシートは名前で記録されるので、作り替えられたファイルでずれる
  • 直すのは「サンプル ファイルの変換」1つ。シートを位置で取る・見出しの行を探す・列名をそろえる
  • 列名や見出しの位置のずれは、エラーにならずに値が抜けることがある。件数と合計を元と突き合わせる
  • 翌月からは、ファイルをフォルダーに足して「すべて更新」を押すだけ

関連記事

対応バージョン: Microsoft 365／Excel 2019 以降

サンプルファイル

記事中で使用しているサンプルデータはこちらからダウンロードできます。

タイトルとURLをコピーしました