【Excel】パワークエリで読み込むファイルをセルで指定する｜ローデータと集計のExcelを分けて軽くする

前の記事では、パワークエリで大量のローデータを店舗・月・カテゴリごとにまとめました。

私の資料では、ローデータは別のExcelに置いて、集計のExcelからパワークエリで読み込んでいます。ローデータと集計を1つのExcelに入れると、ファイルが大きくなり、開くのにも時間がかかるためです。分けておくと、集計のExcelは軽くなり、開くのも速くなります。

この記事では、読み込むファイルの名前をセルに書いておき、パワークエリがそのセルを見てファイルを読み込む方法を紹介します。

なお、この記事の表や手順は説明のために作ったもので、実際に使っているExcelとは作りが違います。

全体の流れ

パワークエリで別のExcelを読み込むと、ファイルの場所は「C:\Users\…\売上データ.xlsx」のようなフルパス（絶対パス）で記録されます。集計のExcelから見た場所では指定できないため、フォルダごと別の場所に移すと、パスを直さないと読み込めなくなります。

そこで、集計のExcelがあるフォルダの場所を関数で取得し、セルに書いたファイル名とつなげてフルパスを作ります。パワークエリはそのセルを見て読み込むので、フォルダごと移しても、パスを直す必要がありません。読み込むファイルを変えるときも、セルのファイル名を書き換えるだけですみます。

ローデータのExcelは、集計のExcelと同じフォルダか、その下のフォルダに置きます。

集計のExcelに、次のような表を作ってテーブルにします。テーブル名は、パワークエリから分かりやすいように「設定」に変えておきます。

ファイル名	フルパス
売上データ.xlsx	C:\Users\…\集計\売上データ.xlsx

フルパスの列には、次の式を入れます。

=LET(path,CELL("filename",A1),dir,LEFT(path,FIND("[",path)-1),dir&[@ファイル名])

CELL関数に「filename」を指定すると、「C:\Users\…\集計\[集計.xlsx]シート名」のように、フォルダ・ファイル名・シート名がまとめて返ってきます。そこから「[」の前までをLEFT関数で取り出すと、フォルダの場所だけが残ります。最後に、ファイル名の列とつなげています。

保存していないExcelでは、CELL関数が空になり、式がエラーになります。先に一度保存してから式を入れてください。

ローデータのExcelを「データ」などの下のフォルダに置くときは、ファイル名の前にフォルダ名も書きます。

ファイル名	フルパス
データ\売上データ.xlsx	C:\Users\…\集計\データ\売上データ.xlsx

こうしておけば、式はそのままで使えます。

「設定」テーブルの中を選んで、［データ］タブ→［テーブルまたは範囲から］を押します。Power Queryエディターが開いたら、フルパスの値を右クリック→［ドリルダウン］を選びます。表ではなく、フルパスの文字だけが表示されます。

クエリの名前を「フルパス」に変えて、［ホーム］タブ→［閉じて読み込む］の▼→［閉じて次に読み込む］で、「接続の作成のみ」を選びます。

まずは、いつもどおりローデータのExcelを読み込みます。［データ］タブ→［データの取得］→［ファイルから］→［Excelブックから］でローデータのExcelを選び、読み込むテーブルを選んで［データの変換］を押します。

Power Queryエディターの右側の「適用したステップ」で、一番上の「ソース」を選びます。上の数式バーに、次のような式が表示されます。

= Excel.Workbook(File.Contents("C:\Users\…\集計\売上データ.xlsx"), null, true)

「”C:\Users\…\売上データ.xlsx”」の部分を、前後の「”」ごと消して「フルパス」に書き換え、Enterを押します。

= Excel.Workbook(File.Contents(フルパス), null, true)

これで、「フルパス」のクエリの値、つまりセルで作ったフルパスのファイルを読み込むようになります。数式バーが表示されていないときは、［表示］タブの「数式バー」にチェックを入れます。

あとは、前の記事と同じように、不要な列の削除やグループ化をしていきます。

フルパスは式で作っているので、集計のExcelとローデータのExcelをフォルダごと別の場所に移しても、そのまま［すべて更新］で読み込めます。私の資料は共有フォルダに置いていますが、場所を変えてもパスを直さずに使えています。

セルの値を使って別のファイルを読み込むと、更新したときにプライバシーレベルに関するエラーが出ることがあります。私の資料も、共有フォルダに置いたファイルを他の人が［すべて更新］したときに、エラーになりました。

このときは、［データ］タブ→［データの取得］→［クエリオプション］を開き、「グローバル」の「プライバシー」で「常にプライバシーレベル設定を無視します」を選んで［OK］を押すと、エラーが出なくなります。

ただし、これは社内のファイルだけを扱う場合の設定です。また、この設定はPCごとに必要です。私も、更新する他の人にPCの設定を変えてもらいました。