フォルダーからデータを取得する方法

本連載の第1回～第3回で紹介したように、複数のExcelファイルを結合することを目的にパワークエリを使用するケースもよくある。この場合は「フォルダーから」を指定してデータを取得するのが一般的だ。今回は、フォルダーからデータを取得するときの操作手順と注意点について紹介していこう。

フォルダー内のExcelファイルを結合して取得

前回の連載では、「単体のExcelファイル」からデータを取得するときの操作手順を紹介した。今回は、同じフォルダー内にある「複数のExcelファイル」を結合してデータを取得する方法を紹介していこう。

• 

  フォルダーを指定してデータを取得

Excelを起動し、前回の連載と同様に「データ」タブにある「データの取得」をクリックする。続いて、データの取得元を指定する。特定のフォルダーを指定してデータを取得するときは、「ファイルから」→「フォルダーから」を選択すればよい。

• 

  「データの取得」コマンドの操作

フォルダーを指定する画面が表示されるので、データの取得元にするフォルダーを指定し、「開く」ボタンをクリックする。

• 

  データの取得元になるフォルダーの指定

指定したフォルダー内に保存されているファイルの情報が一覧表示される。ここでは「結合」ボタンをクリックし、「データの結合と変換」を選択するのが基本だ。

• 

  「データの結合と変換」を選択

ファイルの結合方法を指定する画面が表示されるので、「どのデータ表を基準にデータを結合するか？」を指定していく。最初に、基準とするファイルを選択する。すべてのExcelファイルが“同じ形式”で作成されている場合、この選択は「最初のファイル」のままでよい。

• 

  結合時に基準とするExcelファイルの選択

続いて、データ表が記録されているワークシートを選択する。すると、そのプレビューが右側に表示される。これを確認し、「OK」ボタンをクリックする。

• 

  ワークシートの指定

フォルダー内にあるExcelファイルを結合した形で「Power Query エディター」に、データが取得される。

• 

  「Power Query エディター」に取得されたデータ

以上が「フォルダー」を対象にデータを結合して取得するときの操作手順となる。取得元のExcelファイルが“同じ形式”で作成されていれば、特に問題なく作業を終えられるはずだ。なお、思い通りにデータを取得できなかった場合は、後ほど紹介する注意点を確認してみる必要がある。

他のボタンをクリックした場合は？

先ほど示した操作手順では、「データの結合と変換」を選択するのが基本、と解説した。とはいえ、この画面には「結合」ボタンのほかに「読み込み」や「データの変換」といったボタンも用意されている。続いては、各ボタンをクリックしたときの動作について補足しておこう。

• 

  データの取得時に表示されるボタン

まずは、「結合」ボタンに用意されている選択肢について。ここで「結合および読み込み」を選択し、その後、基準とするExcelファイルとワークシートを指定すると、「Power Query エディター」を経由することなく、結合したデータ表（テーブル）がそのままExcelに出力される。

「結合および読み込み先...」を選択した場合も同様だ。この場合は、データ表の出力先を指定できるようになる。

• 

  データ表の出力先を指定するダイアログ

どちらも「結合したデータ表を一切加工しないで、そのままExcelに出力する」という動作になる。結合したデータ表を「Power Query エディター」で加工したい場合は、冒頭で示したように「データの結合と変換」を選択しなければならない。

なお、「結合」ボタンの選択肢には“読み込み”という文字が記載されているが、本連載ではあえて“出力”と表現している。これは「データの取得」と混同しないようにするためだ。

“読み込み”という表現はExcelの立場から見たもので、「Excelにデータを読み込む」ということを指している。これをパワークエリの立場から見ると、「Excelにデータを出力する」となる。要するに、「どちらの立場から見て表現しているか？」の違いであり、“読み込み”と“出力”は同じ動作を意味している。

本連載ではパワークエリの立場から見て説明したほうが理解しやすいと考え、「データの出力」という表現を使っている。

話を元に戻して、各ボタンをクリックしたときの動作を紹介していこう。次は「読み込み」ボタンをクリックした例だ。この場合、「Power Query エディター」は表示されず、“フォルダー内にあるファイルの情報”がテーブルとしてExcelに出力される。

• 

  Excelに出力されたファイル情報

最後に、「データの変換」ボタンをクリックした例を紹介しておこう。この場合は、先ほどと同じ“ファイルの情報”が「Power Query エディター」に取得される。

• 

  「Power Query エディター」に取得されたデータ

いずれも少し上級者向けのデータ取得方法になるので、当面の間は「結合」→「データの結合と変換」の使い方だけを覚えておけばよいだろう。少し紛らわしい部分もあるので、もういちど“おさらい”しておこう。

◆「結合」ボタン
・データの結合と変換 ……… データを結合して「Power Query エディター」に取得
・結合および読み込み ……… データを結合して、そのままExcelに出力

◆「読み込み」ボタン
ファイル情報をExcelに出力

◆「データの変換」ボタン
ファイル情報を「Power Query エディター」に取得

“結合”が「データの結合」を意味しており、“変換”は「Power Query エディター」を開く、“読み込み”は「Excelにそのまま出力」、と考えると状況を整理しやすくなるだろう。

フォルダー内のデータ結合するときの注意点

続いては、データの結合に関する注意点をいくつか紹介していこう。思い通りにデータを結合するには、以下に示す点について注意しておく必要がある。

最初の例は、一部のExcelファイルだけ“見出し”の文字が異なっていた場合だ。他のExcelファイルでは“見出し”を「分類」と記載しているのに、4月2日のExcelファイルだけ「区分」と記載されている……、という状況を例に紹介していこう。

• 

  「4月2日の売上.xlsx」に記録されているデータ表

ファイルの結合方法を指定する画面で、「最初のファイル」（4月1日の売上.xlsx）を選択し、「Sheet1」を指定すると、以下の図のようなプレビューが表示される。このプレビューに表示されている“見出し”の文字は「分類」だ。

• 

  基準とするファイル、ワークシートの指定

「OK」ボタンをクリックしてデータを結合すると、以下の図のようにデータが取得される。4月2日だけ「分類」のデータがnullになっているのを確認できるだろう。

• 

  「Power Query エディター」に取得されたデータ

nullの表記は「データなし」を意味している。4月2日のExcelファイルに「区分」という列はあるものの、「分類」という列は存在していない。よって、「分類」の列はnull（データなし）として取得されてしまう。

今度は「4月2日のExcelファイル」を基準にしてデータを結合した例を見ていこう。この場合、プレビューに表示されている“見出し”の文字は「区分」となる。

• 

  「4月2日の売上.xlsx」を基準にした場合

結果は以下の図の通り。今度は、4月2日だけデータが取得され、他の日付はデータがnullになってしまう。

• 

  「Power Query エディター」に取得されたデータ

このように、それぞれのExcelファイルで“見出し”の文字が異なっていると、正しくデータを結合（取得）できなくなる。よって、各列の「見出し」は統一しておくのが基本といえる。

次の例は、それぞれのExcelファイルで「シート名」が異なる場合だ。他のExcelファイルはシート名が「Sheet1」となっているのに、4月2日のExcelファイルだけ「20240402」というシート名になっている……、という状況を例に紹介していこう。

• 

  「4月2日の売上.xlsx」に記録されているデータ表

ファイルの結合方法を指定する画面では、基準とするファイルの選択だけでなく、シート名の指定も行わなければならない。基準とするファイルに「最初のファイル」（4月1日の売上.xlsx）を選択した場合、シート名には「Sheet1」を指定することになる。

• 

  基準とするファイル、ワークシートの指定

「OK」ボタンをクリックしてデータを結合すると、4月1日のデータを取得した後にエラー（Error）が発生していることを確認できる。

• 

  「Power Query エディター」に取得されたデータ

これは、4月2日のExcelファイルに「sheet1」というシート名が存在していないことが原因だ。さらに、4月3日以降のデータ取得も実行されなくなってしまう。

ファイルの結合方法を指定する画面で「エラーのあるファイルをスキップする」をチェックしておけば、4月3日以降のデータを取得できるようになるが、4月2日のデータを取得できないことに変わりはない。

このように、それぞれのExcelファイルで「シート名」が異なる場合も正しくデータを取得できなくなってしまう。

このようなトラブルを回避するには、それぞれのExcelファイルで

・データ表の「見出し」を統一しておく
・「シート名」を統一しておく

という点に配慮しなければならない。これらの表記が異なる場合は、「表記を統一する作業」を行った後にパワークエリを使用する必要がある。

シート名を指定せずにデータ結合する方法

最後に、シート名を指定せずにデータを結合する方法を紹介しておこう。それぞれのExcelファイルで「見出し」や「シート名」を統一されていないときの対処法として覚えておくとよい。

シート名を指定せずにデータを結合するときは、「パラメーター」の部分を指定した状態で「OK」ボタンをクリックする。

• 

  基準とするファイルの指定

すると、フォルダー内にある“ファイルの情報”が「Power Query エディター」に取得される。

• 

  「Power Query エディター」に取得されたデータ

以降の手順は、前回の連載で紹介した「各ワークシートにあるデータ表を結合したい場合は？」と同じ。以下の図に示した手順で「Data」の列を展開する。

• 

  展開する列の指定

展開されたデータを見ると、「シート名」や「見出し」が異なっていても、正しくデータが取得されていることを確認できるだろう。

• 

  展開されたデータ

続いて、不要な列を削除し、ヘッダーを指定する処理などを行うと、データを結合した表に加工できる。取得元フォルダーにあるExcelファイルの数が多く、表記を統一するのが大変な場合は、この方法でデータの結合＆取得を行ってもよい。

ただし、それぞれのExcelファイルで「列の並び順が異なる」とか、「列の数が異なる」といった場合は上手くいかないケースもある。シート名を指定しなかった場合は、「見出し」ではなく、「列の並び順」を基準にデータが結合される仕組みになっている。これまでの話をまとめると、

◆シート名を指定した場合
・各列の「見出し」を基準にデータが結合される
・「見出し」の文字が異なる場合、その列のデータは取得されない

◆シート名を指定しなかった場合
・各列の「並び順」を基準にデータ結合される

念のため、こういった“仕組みの違い”についても覚えておく必要があるだろう。