PowerQuery Excelシートの値からファイル名を指定できるようにする簡単な方法

Excel テーブル

スポンサーリンク

はじめに

ExcelのPowerQueryは便利だが、元データとなる外部のExcelファイル等を「データソース」として指定・変更するのが、一般ユーザには難しい。

特に、そのデータソースの保存場所が後から変化した場合に、それをPowerQueryに反映させるのは、手順を知ってても操作が面倒だ。

Excel PowerQuery データソースの変更

そこで今回は、その「データソース」となるExcelファイルを、Excelシートのセル上で指定できるようにして、一般ユーザでも柔軟にデータソースを変更できるようにする方法を取り上げる。

この方法については色々なサイトでちらほら紹介されているが、今回の記事ではもう面倒な理屈の説明は抜きで、書いてある手順を単に丸写しで進めるだけで良いように、答えだけ書いていく。

以前の記事で紹介した、セルの値を絞り込み条件に指定できる方法を、ファイル名として指定するようにアレンジしたものだ。

Excel PowerQuery シートの値をデータ絞り込みに利用できるようにする

ちなみに、フォルダの場所をExcelシート上で指定させ、その中のExcelファイルをまとめて開くようにする方法について、説明も難しいので↓にて紹介している。

(有料Note)PowerQueryでフォルダを指定してファイル結合する処理を簡易化するツールを作成しました

フルパスの意味~コピー方法

そもそもファイルのフルパスというのは、

D:\Excel\個人情報\個人情報.xlsx

みたいに、ファイルの保存場所~ファイル名までが全て一体となった文字列の情報だ。

それに対し「ファイル名」といったら最後の

個人情報.xlsx

みたいな部分で、「保存場所」「フォルダ」とかいったら最初の

D:\Excel\個人情報\

といった部分だ。

フルパスのコピー方法

そして「フルパス」は、エクスプローラ(Winキー + E で開けば良い)でファイルを選択してから

リボン「ホーム」タブ
→パスのコピー

とすれば手早くコピーできる。

そしてこの「パスのコピー」メニューは、右クリック→「クイックアクセスツールバーに追加」としてクイックアクセスツールバーに追加しておけば良い。

そうすれば、クイックアクセスツールバーの9番目までにあったなら、Altキー+1~9キーで素速く実行できるようになる。

フルパスを書いたテーブルの作成

では実際の作業に入る。

まずExcelのシートに、ファイルのフルパスを書き込む「テーブル」を作成する。

シートのA1セルに「パス」と入力し、このA1セルは横方向中央揃えにしておこう。その方がテーブル作成がスムーズにできる。

そしてA2セルは、Excelファイルのフルパスを書き込むセルとする。
実際のファイルのフルパスを書いておいても良いし、空欄のままでも良い。

A1~A2セルを選択した状態で、ショートカットキー Ctrl + T で、このセル範囲を「テーブル」に変換する。

Excel2010以降 「テーブル」機能をフル活用しよう。まずは作ってみる。

リボンに「テーブル デザイン」タブが出現するはずなので、テーブル名の欄に「パス」と入れておく。

いま作ったテーブルを選択した状態で

リボン「データ」タブ
→「テーブルまたは範囲から」

とする。

PowerQueryエディタで関数の作成

PowerQueryのエディタが開く。

リボン「ホーム」タブ
→「新しいソース」
→「その他のソース」
→「空のクエリ」

とする。

クエリ1という新しいクエリが作られる。ここで

リボン「ホーム」タブ
→「詳細エディター」

とする。

Excel PowerQuery ソースコードの詳細エディターについて

そして表示される詳細エディターに、次のソースコードをコピペする。

ちなみにここでは「myPath」というのを変数として使っている。

関数の呼び出し

今やっていたのは「クエリ1」という方のクエリについて詳細エディターを操作していたが、ここで最初に作った「パス」クエリの方を選択する。そして

リボン「列の追加」タブ
→「カスタム関数の呼び出し」

とする。

次のダイアログで

関数クエリ→「クエリ1」
myPath→「パス」

と選択する。選択肢は1つしかないはず。

そして「OK」を押す。

「クエリ1」の列が追加されているので、その右端の□ボタンを押す。

「元の列名をプレフィックスとして使用します」のチェックは外して「OK」を押す。

次のような画面が出てくる。

色々と列があるけど、必要ならシート名など絞り込む。

そして「Data」列の右端の□ボタンを押す。

「元の列名をプレフィックスとして使用します」のチェックは外して「OK」を押す。

これで、元データExcelファイルのデータが展開される。

あとは、他の記事で取り上げているように、データを加工していけば良い。

そして、元データの保存場所などが後から変更になった場合は、「パス」テーブルのA2セルに書かれたフルパスを書き換えてから、リボン「データ」タブ→「すべて更新」とすれば良い。

あるいはショートカットキー Ctrl + Alt + F5キー でデータを更新できる。

これで、PowerQueryの知識がない一般ユーザでも、データソースの変更を手軽にできるようになる。

スポンサーリンク