【Excel】ExcelでPower Queryの接続先フォルダーを相対パスで管理したい時の注意点

Power Queryを利用して複数のExcelファイルを結合する際、接続先のフォルダーパスを絶対パスで指定していると、ファイルサーバーの構成変更や別ユーザーでの共有時にパスが無効になりがちです。相対パスで管理できれば便利ですが、Power Queryには標準で相対パスを解決する機能が用意されていません。この記事では、Power Queryで接続先フォルダーを相対パスで運用するための具体的な方法と注意すべきポイントを解説します。

Power Queryにおける絶対パスと相対パスの違い

Power Queryで外部フォルダーを参照するクエリを作成する際、既定では絶対パス(例:C:\Users\User01\Documents\Data\)を使用します。絶対パスは特定のコンピューターの特定の場所を直接指定するため、そのPC以外ではパスが無効になります。一方、相対パス(例:.\Data\)は現在のブックの位置を基準としたパス指定であり、ブックごとフォルダーを移動してもパスが維持される利点があります。しかしPower Queryの内部エンジンは、クエリを保存するときに絶対パスへの変換を強制するため、相対パスのままでは使用できません。

なぜPower Queryでは相対パスが直接使えないのか

Power QueryのM言語はクエリの保存時にパスを静的に解決します。そのため、ダイアログからフォルダーを選択する際に自動的に絶対パスとして記録されます。また、Power Queryの関数である Folder.Contents や Excel.Workbook は引数に文字列の絶対パスを期待しています。相対パス(“..\Data\”など)を直接渡すと、現在の作業フォルダー(Power Queryからは不明)からの相対解釈が行われず、エラーになります。

この制約を回避するには、何らかの方法で相対パスから絶対パスを動的に生成し、クエリに渡す必要があります。主なアプローチとして、Excelのセルや名前付き範囲をパラメーターとして使用する方法、M言語内で現在のブックのパスを取得する方法、VBAを使ってクエリのソースを書き換える方法があります。

相対パスを実現する代表的な方法

パラメーターとセル参照を利用する方法

最も簡便な方法は、Excelワークシートのセルに基準となる絶対パス(例えば =CELL(“filename”) で現在のブックのパスを取得)を表示し、そのセルをPower Queryのパラメーターにバインドする方法です。この方法では、ブックの保存場所を変更すると自動的にパラメーターが更新され、クエリのソースも連動して変わります。ただし、パラメーターの作成にはPower Queryエディター上での操作が必要です。

M言語で現在のブックパスを利用する方法

M言語の Excel.CurrentWorkbook() 関数を使って、現在のブックのパスを取得するテクニックもあります。たとえば、次のようなM式でパスを取得できます。

let
    WorkbookPath = Excel.CurrentWorkbook(){[Name="PathCell"]}[Content]{0}[Column1],
    …

この方法では、ワークシート上のセルにパスを記述しておき、その値を読み取ります。セルには =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1) などの式で現在のブックのフォルダーパスを動的に表示させます。

VBAでクエリのソースを動的に書き換える方法

テクニカルな方法として、VBAマクロを使ってPower Queryのクエリ定義を直接書き換える方法があります。この方法では、ブックを開くたびにソースパスを更新できますが、マクロの有効化や信頼設定が必要であり、社内セキュリティポリシーとの兼ね合いを確認する必要があります。また、マクロを使わない環境では利用できないため、汎用性は低くなります。

操作手順:パラメーターとセル参照を用いた相対パスの設定方法

ここでは、最も実用的な「パラメーターとセル参照」の手順を紹介します。

1. 基準パス用のセルを作成する:ワークシートの任意のセル(例:A1)に、相対パスの基準となるフォルダーパスを入力します。たとえば、ブックが保存されているフォルダーからの相対位置を指定する場合、セルに =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)&"Data\" と入力します。これにより、ブックと同じフォルダー内の「Data」サブフォルダーへの絶対パスが動的に生成されます。
2. 名前付き範囲を定義する:上記のセルを選択し、数式タブの「名前の定義」から「名前付き範囲」を作成します。名前は「FolderPath」などわかりやすいものにします。これによりPower Queryから参照しやすくなります。
3. Power Queryにパラメーターを作成する:Excelの「データ」タブから「クエリと接続」を開き、「新しいクエリ」→「その他のソースから」→「空のクエリ」を選択します。Power Queryエディターが開いたら、「ホーム」タブの「パラメーターの管理」→「新しいパラメーター」をクリックします。名前を「FolderParam」、種類を「テキスト」、現在の値を手入力ではなく「ワークシートのセルから取得」にします。
4. セル参照を設定する:パラメーターの「値」に名前付き範囲「FolderPath」を設定するか、直接ワークシートのセル範囲を指定します(例:Sheet1!$A$1)。これでパラメーターがセルの値とリンクします。
5. クエリのソースをパラメーターに変更する:既存のクエリ(例:フォルダーからファイルを結合するクエリ)を開き、ソースステップを編集します。ソースのフォルダーパスをハードコードではなく、作成したパラメーター名「FolderParam」に置き換えます。たとえば、Folder.Contents("C:\Users\…") を Folder.Contents(FolderParam) に書き換えます。
6. クエリを閉じて読み込む:設定が完了したら、「閉じて読み込む」を実行します。これで、ブックの保存場所を変更しても、セル内のパスが自動更新され、クエリも連動して新しいフォルダーを参照するようになります。

方法	メリット	デメリット
絶対パス直書き	設定が簡単、誰でもすぐに作れる	ブック移動・共有時に毎回修正が必要
パラメーター+セル参照	動的にパスが変わる、マクロ不要	セルの式が壊れると誤動作、パラメーター作成に少し手間
M言語で現在のブックパス取得	クエリ内で完結、外部参照が少ない	M言語の知識が必要、複雑になりがち
VBAによる書き換え	完全自動化できる	マクロの有効化が必要、セキュリティ制限に引っかかる

よくある失敗パターンとトラブルシューティング

相対パス管理を導入する際、以下のような失敗がよく発生します。

• パラメーターが更新されない:セルの値を変更しても、既存のクエリは再実行されるまで新しいパスを認識しません。「データ」タブの「すべて更新」を忘れずに実行してください。
• 相対パスの基準がずれる:CELL("filename") は保存されていないブックでは空文字を返すため、ブックを保存せずにテストするとエラーになります。最初にブックを目的のフォルダーに保存してから設定を行ってください。
• 名前付き範囲のスコープ:名前付き範囲をワークブックレベルではなくワークシートレベルで作成してしまい、Power Queryから参照できなくなることがあります。名前付き範囲は「ブック」スコープで定義してください。
• 特殊文字や日本語を含むパス:フォルダー名に全角文字やスペースが含まれる場合、Power Queryがパスを正しく解釈できないことがあります。できれば英数字のみのフォルダー構成を推奨します。

管理者に確認すべき設定と注意事項

社内で共有するExcelブックにPower Queryの相対パスを導入する場合、以下の点を管理者に確認しておくと安心です。

• 共有ドライブのパス変更:ファイルサーバーの移行やドライブレターの変更が予定されている場合、その影響範囲と対応時期を把握しておきます。
• クエリの信頼設定:Power Queryで外部データソースに接続するには、Excelの「信頼できる場所」または「外部データ接続の許可」設定が必要な場合があります。管理者に問い合わせて環境を整えましょう。
• マクロの使用可否:VBA方式を採用する場合は、マクロの実行ポリシー(デジタル署名の有無、信頼された場所)を事前に確認してください。
• バージョン互換性:古いバージョンのExcelではパラメーター機能が使えない場合があります。社内で統一バージョンを使用しているか確認します。

よくある質問(FAQ)

Q1. 相対パスを使うとファイルの移動時に自動的に更新されますか?
A. セル参照方式の場合、ブックを開くときや更新時にセル内の式が再計算されるため、自動的に新しい絶対パスが生成されます。ただし、クエリを手動または自動で更新(データの更新)しないとソースは変わりません。

Q2. ほかのユーザーと共有する場合、注意することは?
A. 各ユーザーのローカルフォルダー構成が異なる場合は、相対パスの基準となるセルに各自の環境に合ったパスを設定する必要があります。ネットワーク共有ドライブを使用するのが現実的です。

Q3. パラメーターがうまく読み込まれません。どうすればいいですか?
A. パラメーターの設定で「現在の値」が空になっていないか確認してください。また、名前付き範囲の参照が正しいか、数式バーで確認します。一度パラメーターを削除して再作成すると直ることがあります。

Q4. Power Queryで相対パスを直接入力する方法は本当にないのですか?
A. 残念ながら、Power Queryのダイアログ上では相対パスを直接入力できません。しかし、上記のパラメーター方式やM言語で相対パスを変換すれば同じ効果が得られます。

まとめ

Power Queryで接続先フォルダーを相対パス管理するには、標準機能だけでは実現できないため、パラメーターとセル参照を組み合わせる方法が最も実用的です。設定のポイントは、基準パスを動的に生成するセル数式を用意し、Power Queryパラメーターにバインドすることです。失敗を防ぐために、ブックの保存場所や名前付き範囲のスコープに注意してください。これらのテクニックを活用すれば、ファイル構成の変更に強いExcel業務プロセスを構築できます。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】