Power Query：セル値を使ってファイルを相対参照する - なんでもエクセル（Nandemo Excel）

プライバシーレベルの問題を無視するなら（下げないと使えないので）、クエリで自ブックを参照したい時や、自ブックと同階層のファイルを可変で参照したい時、セルに入力された値がパラメータとして使えると便利ではある。

ファイルパスを表示する

というのも「当該ブック（保存済）のファイルパス」なら数式で簡単に求められるからだ。

A1：=CELL("filename",A1)

これで上記の画像のような文字列が返る。この時、第二引数を省略するのはＮＧ。省略するとアクティブなブック（及びシート）のファイルパスが返ってしまうので注意。

このままでは使えないので、クエリで使える形に加工する。


ファイルパス
=LET(_fn,CELL("filename",A1),SUBSTITUTE(LEFT(_fn,FIND("]",_fn)-1),"[",""))

このセルに名前を付けておけば準備完了。因みに LET関数は Excel2021以上でないと使えないので、古いバージョンの場合は同じフレーズを数式内で繰り返すことになる。

 =SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")

とりあえず「参照元」と名前定義しておく。上記の数式をそのまま名前定義しても使えないのでこれも注意。結果の文字列は必ずセルに表示させること。

セルを名前定義

フォルダパスが欲しい場合は、下記のようにする。


フォルダパス
=LET(_fn,CELL("filename",A1),LEFT(_fn,FIND("[",_fn)-1))

後は自ブックを参照するクエリを作成して、ソースのところを変数に置き換えてやればおしまい。


［データ］→［データの取得］→［ファイルから］→［Excel ブックから］
シートを指定して［データの変換］


Power Queryエディタの［ホーム］→［詳細エディタ］を開く

最後に詳細エディタの「ソース」のところをいじる。

    ソース = Excel.Workbook(File.Contents("C:\xxx\xxx\sumple.xlsx"), null, true),

この部分を

    参照元 = Excel.CurrentWorkbook(){[Name="参照元"]}[Content]{0}[Column1],
    ソース = Excel.Workbook(File.Contents(参照元), null, true),

これでおしまい。もちろん「便利＝適切」とは限らないので、ご利用は計画的に。

フォルダコネクタから絞り込む形でもできるはずなので、機密情報の漏洩リスクをかかえたくないなら、そっちでやることをお勧めしたい。