他のExcelファイルのデータを参照して資料を作成することはありますか？
ファイルが1つだけなら、シートを1つ追加してそこにピボットテーブルやグラフなどを作成すれば良いでしょうか。ただ、他人が作ったファイルだと触りにくいですよね。
複数ファイルがあれば、1つのファイルにコピーするのも大変ですし、元のファイルが更新された場合にコピーし直しになります。
そこでPower Queryを使えば、他のExcelファイルを取得することが簡単にできます。同じレイアウトであれば、同じフォルダに置いておけば一括で取得することもできます。元のファイルが更新された場合でも、ボタン1つで再取得可能です。
Power QueryでExcelファイルを取得する
以下の売上データがあったとします。
このExcelファイルをPower Queryを使ってデータ取得を行う方法を解説します。
データを取得する
Excelを立ち上げます。「データ」タブを開き、「データの取得」ー「ファイルから」ー「Excel ブックから」をクリックします。
データ取得したいExcelファイルを選択し、「インポート」をクリックします。
データを取得したいシートを選択し、OKをクリックします。
データ取得が完了しました。シートにテーブルが作成されます。
データを更新する
データ取得元のExcelファイルが更新された場合、Power Queryを使った取得先のExcelファイルはどうやって更新すればいいでしょうか。
データ取得元のExcelファイルにデータを1行追加します。
Power Queryを使った取得先のExcelファイルを開き、「クエリ」タブの「更新」ボタンをクリックします。
データが追加されることが確認できます。
データ取得元のファイルが更新されても、「更新」ボタン一つでデータの取得ができるのは非常に便利ですね。
Power Queryで「複数」のExcelファイルを取得する
売上を月ごとにExcelでまとめており、それぞれ同じフォーマットだったとします。以下のようなExcelファイルがあるとします。
2025年1月度
2025年2月度
2025年3月度
各月の売上明細を集計したい場合、皆さんはどうしますか？Excelファイルを全部開いて、１つのExcelファイルにコピー＆ペースしますか？結構面倒ですよね。
Power Queryではデータの取得に「フォルダ」が使えます。「フォルダ」を使うと、同じフォルダにあるファイルをすべて取得してくれます。
データを取得する
同じフォルダにExcelファイルを置きます。
「データ」タブを開き、「データの取得」ー「ファイルから」ー「フォルダーから」をクリックします。
Excelファイルを置いたフォルダを開き、OKをクリックします。
「結合」ボタンの▼をクリックし、「データの結合と変換」をクリックして同じフォルダにあるExcelファイルを1つに結合します。
データを取得したいシートを選択し、OKをクリックします。
データを出力する
必要に応じてデータの加工をしたあとに、「閉じて読み込む」をクリックします。
2025年1月～2025年3月のExcelファイルが1つのシートに表示されました。
あとはピボットテーブルなどで明細データを集計して分析に活用します。
ファイルの保存先フォルダが変わった場合どうすれば良い
サンプルでは、「C:\PowerQuery4」というフォルダを参照していましたが、フォルダ名の変更や場所が変わった場合どうなるでしょうか？
「C:\PowerQuery4」を「C:\PowerQuery5」に変更してみましょう。
クエリを更新しても、フォルダが見つからないため以下のようにエラーになります。
Power Queryエディタでフォルダ名を変更する必要があります。まずは以下の手順でPower Queryエディタを開きます。
サンプルファイルのソースを変更します。以下の手順でソースのステップを開きます。
フォルダ名を変更します。
その他のクエリでフォルダを取得したクエリをのソースを変更します。以下の手順でソースのステップを開きます。
フォルダ名を変更し、「閉じて読み込む」をクリックします。
エラーは解消され、変更後のフォルダを取得するようになります。
Power Queryで「複数」のExcelファイルから「複数」シートを取得する
同じフォルダにあるExcelファイルを一括で取得する方法について解説しました。ただこのやり方だと、特定のシート名を取得するため、複数シートがあるファイルについては全ての情報が取得できません。
例えば以下のようなファイルでは、売上年月がシートごとに分かれています。
支社ごとに１ファイルあり、売上年月がシートごとに分かれている場合にデータ取得する方法について解説してきます。
データを取得する
「データ」タブを開き、「データの取得」ー「ファイルから」ー「フォルダーから」をクリックします。
Excelファイルを置いたフォルダを開き、OKをクリックします。
結合の▼ボタンをクリックし、「データの結合と変換」をクリックします。
パラメーターと書かれたフォルダを選択し、OKをクリックします。シートを選択してしまうと、選択したシートしか取得できないため要注意です。
データを加工する
データの取得ができました。Item列にシート名が入っていますが、自動的に日付型に変換されているためテキスト型に変換します。
Item列を選択し、「変換」タブの「データ型」ー「テキスト」をクリックします。
「現在のものを置換」をクリックします。
yyyy年m月形式のシート名になりました。
Name、Kind、Hidden列は使わないため削除します。列を選択して右クリックし、「列の削除」をクリックします。
列が削除されました。
次にData列に各シートごとのデータがテーブル型で保持されており、これを展開します。Data列の←→をクリックし、OKをクリックします。
データが展開されました。各シートごとにヘッダー行が含まれており、それも展開されています。
ヘッダー行は不要のため削除します。Data.Column1の▼をクリックします。
売上日のチェックを外します。
各シートのヘッダー行が削除されました。列名はわかりやすいように変更します。列名はダブルクリックすると編集可能になります。
Source.Nameにはファイル名が入っています。今回のサンプルではファイル名に含まれている支社名だけを抽出し、支社という列にしたいと思います。
Source.Name列をクリックし、「抽出」ー「区切り記号の後のテキスト」をクリックします。
「区切り記号」に_（アンダーバー）を入力し、OKをクリックします。
_（アンダーバー）より後ろの文字が残りました。.xlsxも削除して支社名だけ残します。
Source.Name列をクリックし、「抽出」ー「区切り記号の前のテキスト」をクリックします。
「区切り記号」に.（ピリオド）を入力し、OKをクリックします。
.（ピリオド）より前の文字が残り、支社名だけ抽出できました。
列名はわかりやすいように支社に変更します。
後は必要に応じてデータ型を変更していきます。売上日のデータ型が「すべて」になっています。日付型の方が扱いやすいため、売上日を選択し、「データ型」ー「日付」をクリックして変換します。
データを出力する
必要なデータ加工が終わりました。「閉じて読み込む」をクリックすると新しいシートにデータ加工した結果を保存してくれます。
以下のように、支社ごとに分かれているファイルを取得することが出来ました。
