《手順を紹介》Power Query（パワークエリ）で接続先をセル上の指定ファイルにする方法とは？

Power QueryでファイルからデータをExcelに読み込んだ場合、元のファイルの場所（ファイルパス）が記録されます。そのため、フォルダー名やファイル名の変更、移動などを行うと、ファイルパスが一致せずエラーとなります。

しかし、フォルダー名やファイル名の変更や移動のたびにソース（接続ファイルの場所）を変更するのは面倒ですよね。

この問題の解決方法を端的にお伝えすると、ワークシート上にファイルが存在している場所を表示しておき、Power Queryでその位置を読み取らせることでフォルダー名やファイル名の変更や移動が行われた場合でもエラーにならずに設定することができます。

そこで今回は、データソースの取得部分をセル上の指定ファイル・ファイルパスに変更し、セルに入力するだけで簡単に変更できる方法をステップバイステップでご紹介します。

監修：みずきさん

監修：みずきさん

EXCEL女子の研修講師をしています。Excel研修では「覚える」だけではなく「理解する」ことで、自身の力としてExcelスキルを身につけられる研修を実施しています。研修をするたびにExcelが大好きになり、Excelってすごい！と実感しています。Excelの他には写真が好きで食べ物や風景を撮影しています！

Power Query（パワークエリ）とは？

Power Query（パワークエリ）は、Microsoftが開発したデータ処理ツールで、データを取り込んだり変換したりするためのマイクロソフトのアドインです。

Power Queryを使用すると、複数のデータソースからデータを統合したり、データの変換や整理を行ったりすることができます。データの取り込みや変換作業を効率化するための機能が豊富に備わっており、データ分析やレポート作成の時間を大幅に短縮することができます。

データソース

データソースとは、分析するデータのある場所を指します。Power Queryでファイルを取得すると、直接データソースの情報が記述されます。

例：C:\PowerQuery\6月.xlsx

クエリが動作する際に、データソースを参照しているため、ファイル名やファイルパスの変更があった場合、以下のようにエラーが発生します。

接続先をセル上の指定ファイル・ファイルパスにする

ここからは、CドライブのPower Queryフォルダにある「6月.xlsx」を取り込み、読込後にフォルダー名とファイル名を表示して、Power Queryの接続先としてシート上の値を設定する方法をご紹介します。

操作方法

（1）Excelを起動し、新規ブックを作成後、任意の場所に保存
（2）[データ]タブ-[データの取得と変換]グループ-「ファイルから」をクリック⇒「Excelブックから」を選択

（3）ファイル「6月.xlsx」を選択し、「インポート」をクリック
（4）「データ」シートを選択し、「読み込み」の▼をクリック⇒「読み込み先...」をクリック

（5）「データのインポート」のダイアログボックスが表示されたら「既存のワークシート」を選択
⇒「OK」をクリック

（6）データがテーブルとして読み込まれ、クエリと接続の作業ウィンドウが表示される

（7）新規シートを追加する
（8）【A1】セルに「フォルダー名」と入力する
（9）【A2】セルにフォルダー名を表示する関数「=CELL("filename")」と入力する

（10）【A2】セルに表示させたいのはフォルダー名だけなので、FIND関数とLEFT関数を使用して「C:\PowerQuery\」の表示に変更するため、【A2】セルを「=LEFT(CELL("filename"),FIND("[",CELL("filename"))-1) 」に変更する

※この関数の詳細ついては、以下の解説をご覧ください。

POINT

関数について

1つ目のCELL関数で「C:PowerQuery[パワークエリ.xlsx]Sheet2」という文字列を取り出す。
2つ目のCELL関数の文字列から、FIND関数を使用して「[」が何文字目にあるか探す。
何文字目かを見つけたら、表示したいのはその文字の1文字前なので「-1」をする。
LEFT関数で、①の左から該当の文字数分文字列を取り出し「C:PowerQuery」と表示される。

（11）【A4】セルに「ファイル名」と入力し、【A5】セルに「6月」と入力する
（12） セル範囲【A1:A2】を選択し、[挿入]タブ-[テーブル]グループ-「テーブル」をクリックしテーブルにする
※テーブル作成時は「先頭行をテーブルの見出しとして使用する」にチェックを入れる。

（13） 同様にセル範囲【A4:A5】もテーブルにする

（14）テーブル名を変更する
テーブルを選択し、[テーブルデザイン]タブ-[プロパティ]グループのテーブル名を、それぞれ「フォルダー」と「ファイル」に変更する。
（15）クエリと接続の「データ」を右クリックした 後に「編集」をクリック⇒Power Queryエディターを開く
（16）続いて「詳細エディター」をクリックする
※「詳細エディター」については、以下をご覧ください。

STEP UP

詳細エディター

owerQueryでは「クエリの設定」の作業ウィンドウに、「適用したステップ」が表示されます。「詳細エディター」はその適用したステップのソースコードで「M言語」を使用して記載されています。

PowerQueryではほとんどの操作をリボンから実施できるので、コードを書く必要はありませんが、より詳細な設定をするには詳細エディターを編集する必要があります。

詳細エディターのM言語の概要

• 「let」で始まり「in」で終わる
• 「ステップの名前」=「コード」の形式になっている
• 大文字と小文字が区別される
• 各コードは前後のステップ名が連動されている

（17）「詳細エディター」に変数を入力する
「let」の後ろに「filepath=Excel.CurrentWorkbook(){[Name="フォルダー"]}[Content]{0}[フォルダー名],」と入力し、続けて「filename=Excel.CurrentWorkbook(){[Name="ファイル"]}[Content]{0}[ファイル名],」と入力する。
※大文字・小文字に注意しながら入力します。

POINT

変数

変数とは値を入れておく箱のことで、名前を付けて使用します。名前を付けた箱に、値を代入するイメージです。

（18）「ソース = Excel.Workbook(File.Contents("C:\PowerQuery\6月.xlsx"), null, true),」を「ソース = Excel.Workbook(File.Contents(filepath&filename&".xlsx"), null, true),」に変更する。
※大文字・小文字に注意しながら入力します。

（19）「適用したステップ」の1行目と2行目が変数に変化したこと、エラーがなくプレビューが表示されていることを確認⇒「閉じて読み込む」をクリックする

これで、フォルダやファイル名が変更されてもクエリが動作するようになりました。

（20）フォルダー内に「7月.xlsx」のファイルを移動し、「sheet2」のファイル名を「7月」に変更後、「更新」をクリックしデータが読み込まれることを確認する

POINT

シート名について

詳細エディターではソースとして「シート名」も指定しています。ここではフォルダー名とファイル名は変数指定していますが、シート名については、「データ」というシート名になっていますので、各ファイルシート名が同じである必要があります。　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

まとめ

今回はデータソースの取得部分をセル上の指定ファイル・ファイルパスに変更し、セルに入力するだけで簡単に変更できる方法をご紹介しました。

ぜひ、ご活用いただき、業務効率化をしていきましょう。　

Excel研修サービス案内

実際に現場で活躍するEXCEL女子が考えたカリキュラムをもとに、学習するExcel研修のご紹介です。

資料ダウンロード