Power Query 第20回目です。
ここまで主にフォルダーからソースデータを取得する方法を中心にして進めてきています。
すごく便利なんですが、その設定方法や管理方法をここで整理しておきたいと思います。
ソースファイルをフォルダーから取得する方法は、ファイル名を指定しなくてよいのですごく便利ですよね。でも、ファイル数が増えてくると必要のないデータでもすべて読み込んでしまうため読み込むのに時間もかかるようになります。
そこで、今日はフォルダーの設定や管理方法について整理しましょう(^^)/
データを月別にサブフォルダーに分けて使っていますけど、全部のファイルを読み込んでいるんですよね。だんだん時間がかかるようになってきました。
そんな問題が解決できるならうれしいです。よろしくお願いしますm(__)m
【この記事でわかることは】
・基本的なフォルダーの設定や管理方法がわかります
前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ
・前回の内容は カスタム関数の作成（その2）で「複数のパラメーターを持つカスタム関数」の作成について解説しています。
記事作成にあたっては次の Microsoft office のサポートの情報などを参照しています。
・Power Query for Excel のヘルプ
・Excel の Power Query について
なお、この記事の画像は Microsoft 365 で取得した画像です。Excel2016 のリボンやメニューなどバージョンによって違うようなので、適宜読み替えをお願いします(^^)/
フォルダーからのデータ取得について整理
フォルダーからのデータ取得・結合とは
フォルダーに格納されている同じスキーマを持つ複数のファイルを1つのテーブルに結合します。
同じスキーマとは
「スキーマ」とはファイルの構成のことです。同じスキーマの条件は次のとおりです。
- ファイルは同じ列名（見出し）で構成されている必要があります。
- ファイル名は同じでなくても構いません。
- ファイルが、Excelファイルの場合はさらに以下の条件も満たしている必要があります。
- シート名が同じ
- ファイルの種類（拡張子）が同じ（xlsx・xls・xlsm・xlsb等の混在は不可）
- 読み取りパスワードがついていない
※ 読み込み時にエラーが発生した場合、これらを修正して再度更新すれば読み込みできます。
フォルダーから取得する際の留意事項
- 同じフォルダー内（含むサブフォルダー）のファイルは全て読み込まれます。
- つまり、読み込みたくないファイルは、当該フォルダー以外の場所に移動する必要があります。
- フォルダ内に違うスキーマのファイルが混入している場合はエラーが発生します。
フォルダーの設定見本
例えばこんな感じでフォルダーを設定します。
基本的な手順
- ダウンロードしたソースファイルはいつも（決められた）同じフォルダに保存します。
- 結合したテーブルで［更新］ボタンをクリックします。
- 新しいデータが追加結合されます。
- データを追加するのではなく入れ替えるなら、前ファイルを別のフォルダに移動しておきます。
- フォルダ内のファイルを一つにしておけば、ファイル一つだけの読み込みになります。
フォルダーからのデータ取得を制御する
非常に便利なフォルダからのデータ取得をもっと使いやすくするにはどうすればよいか考えます。
パラメーターを利用する
- 自ブックのパスを自動取得にしておきましょう。自ブックの移動に対応できるようになります。
- ローカルで使用する場合、サブフォルダは自ブックのカレントフォルダ内にしましょう。
- 共有フォルダ等の場合は、共有フォルダのパスをパラメーターに設定しておきましょう。
- パラメーターの取得方法は次のような手法があります。
パラメーターの追加もテーブルに追加するだけで済む、パラメーターテーブルからカスタム関数で取得する方法がおすすめです。
※詳しくはリンク先を参照してください。
パラメータテーブルの変更例
変更方法の一例を示しておきます。
変更前のパラメーターテーブル
年別にソースデータを取得するように変更したパラメーターテーブル
・この場合 [Name] が変更・追加されているのでコードの修正が伴います。
パラメーターでソースデータを８月だけに絞る例
・ソースのサブフォルダーを「\08」を追加することで８月のデータだけにアクセスします。
・この場合は、ソースコードの変更は必要ありません。
フィルターを利用する
- フィルターを利用してソースデータの絞り込みを行う方法
- パラメーターを利用してフィルターを変更する方法
- インデックス列を利用して最新のファイルだけ取り込む方法
- 日付型関連のフィルター設定方法（日付列から曜日列を作成する方法）
※詳しくはリンク先を参照してください。（リンクのないものは記事作成次第更新します）
Power Pivot で必要なデータを抽出する
- 取得データはある程度幅を持たせておき、ピボットテーブルで抽出する方法
※詳しくはリンク先を参照してください。（リンクのないものは記事作成次第更新します）
まとめ（おわりに）
以上で、基本的なフォルダーの設定・管理方法について整理してみました。
まだまだ勉強途中の状況ですが、現状で考えてみた内容です。間違えていたり不十分なところが多いかもしれませんので、その点をご考慮いただけるようお願いいたします。
まとめと感想など
いかがでしたか？
いままで勉強してきた部分を整理しただけの内容でしたが、整理することによって足りない部分の確認や気づきがあると思います。フィルターの利用や Power Pivot については今後勉強していく必要があることが確認できました。
そうですね。こうやって整理してみると復習にもなるし、使い方の幅も広がったように思います。次の勉強が楽しみです(^^♪
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
【今後の記事について】
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/
過去記事のサンプルファイルをダウンロードできます
今回記事はサンプルファイルを使用していないので登録していません！
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください
※各記事内容へのリンクの設置しています。
【今回わかったことは】
・基本的なフォルダーの設定・管理方法について理解を深めることができました