なんでもエクセル(Nandemo Excel)

何でもエクセルでやってしまう そこのあなた。ようこそ

Power Query:セル値をパラメータにしてファイルを参照する

クエリで自ブックを参照したい時や、自ブックと同階層のファイルを可変で参照したい時、セルに入力された値が使えると便利だ。というのも「当該ブック(保存済)のファイルパス」なら数式で求められるからだ。

A1:=CELL("filename",A1)

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

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


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

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

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

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

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


フォルダパス
=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),

こうするだけ。