スポンサーリンク
はじめに
ExcelのPowerQueryは便利だが、元データとなる外部のExcelファイル等を「データソース」として指定・変更するのが、一般ユーザには難しい。
特に、そのデータソースの保存場所が後から変化した場合に、それをPowerQueryに反映させるのは、手順を知ってても操作が面倒だ。
そこで今回は、その「データソース」となるExcelファイルを、Excelシートのセル上で指定できるようにして、一般ユーザでも柔軟にデータソースを変更できるようにする方法を取り上げる。
この方法については色々なサイトでちらほら紹介されているが、今回の記事ではもう面倒な理屈の説明は抜きで、書いてある手順を単に丸写しで進めるだけで良いように、答えだけ書いていく。
以前の記事で紹介した、セルの値を絞り込み条件に指定できる方法を、ファイル名として指定するようにアレンジしたものだ。
ちなみに、フォルダの場所をExcelシート上で指定させ、その中のExcelファイルをまとめて開くようにする方法について、説明も難しいので↓にて紹介している。
フルパスの意味～コピー方法
そもそもファイルのフルパスというのは、
D:\Excel\個人情報\個人情報.xlsx
みたいに、ファイルの保存場所～ファイル名までが全て一体となった文字列の情報だ。
それに対し「ファイル名」といったら最後の
個人情報.xlsx
みたいな部分で、「保存場所」「フォルダ」とかいったら最初の
D:\Excel\個人情報\
といった部分だ。
フルパスのコピー方法
そして「フルパス」は、エクスプローラ(Winキー + E で開けば良い)でファイルを選択してから
リボン「ホーム」タブ
→パスのコピー
とすれば手早くコピーできる。
そしてこの「パスのコピー」メニューは、右クリック→「クイックアクセスツールバーに追加」としてクイックアクセスツールバーに追加しておけば良い。
そうすれば、クイックアクセスツールバーの9番目までにあったなら、Altキー+1～9キーで素速く実行できるようになる。
フルパスを書いたテーブルの作成
では実際の作業に入る。
まずExcelのシートに、ファイルのフルパスを書き込む「テーブル」を作成する。
シートのA1セルに「パス」と入力し、このA1セルは横方向中央揃えにしておこう。その方がテーブル作成がスムーズにできる。
そしてA2セルは、Excelファイルのフルパスを書き込むセルとする。
実際のファイルのフルパスを書いておいても良いし、空欄のままでも良い。
A1～A2セルを選択した状態で、ショートカットキー Ctrl + T で、このセル範囲を「テーブル」に変換する。
リボンに「テーブル デザイン」タブが出現するはずなので、テーブル名の欄に「パス」と入れておく。
いま作ったテーブルを選択した状態で
リボン「データ」タブ
→「テーブルまたは範囲から」
とする。
PowerQueryエディタで関数の作成
PowerQueryのエディタが開く。
リボン「ホーム」タブ
→「新しいソース」
→「その他のソース」
→「空のクエリ」
とする。
クエリ1という新しいクエリが作られる。ここで
リボン「ホーム」タブ
→「詳細エディター」
とする。
そして表示される詳細エディターに、次のソースコードをコピペする。
ちなみにここでは「myPath」というのを変数として使っている。
|
1 2 3 4 5 6 7 |
let ソース = (myPath as text) ＝＞ let ソース = Excel.Workbook(File.Contents(myPath), null, true) in ソース in ソース |
関数の呼び出し
今やっていたのは「クエリ1」という方のクエリについて詳細エディターを操作していたが、ここで最初に作った「パス」クエリの方を選択する。そして
リボン「列の追加」タブ
→「カスタム関数の呼び出し」
とする。
次のダイアログで
関数クエリ→「クエリ1」
myPath→「パス」
と選択する。選択肢は1つしかないはず。
そして「OK」を押す。
「クエリ1」の列が追加されているので、その右端の□ボタンを押す。
「元の列名をプレフィックスとして使用します」のチェックは外して「OK」を押す。
次のような画面が出てくる。
色々と列があるけど、必要ならシート名など絞り込む。
そして「Data」列の右端の□ボタンを押す。
「元の列名をプレフィックスとして使用します」のチェックは外して「OK」を押す。
これで、元データExcelファイルのデータが展開される。
あとは、他の記事で取り上げているように、データを加工していけば良い。
そして、元データの保存場所などが後から変更になった場合は、「パス」テーブルのA2セルに書かれたフルパスを書き換えてから、リボン「データ」タブ→「すべて更新」とすれば良い。
あるいはショートカットキー Ctrl + Alt + F5キー でデータを更新できる。
これで、PowerQueryの知識がない一般ユーザでも、データソースの変更を手軽にできるようになる。
スポンサーリンク
