タグ別アーカイブ: 複数ファイル

【Power Query裏技】フォルダにある複数ファイルから特定のシートのみを可変で取得する方法

【Power Queryのパラメーターの仕組みを使い、複数のファイルから特定のシートのデータを可変で取得しよう】

Power Queryのフォルダにある複数ファイルから一括でデータを取得する仕組みは驚くほど便利です

この仕組みで大量の転記処理から解放された人も多いのではないでしょうか?

ただこの仕組みには一つだけ問題があります

ファイル内に複数シートがある場合や、シート名が変更になる場合です

今回の記事では、必要に応じてファイル内で取得するシート名を変更できる方法を解説します

今回使用するデータ

今回使用するファイルには次の画像のように複数のシートが入力されています

このような複数シートがある複数ファイルを一つのフォルダに格納しておきます

こちらのフォルダを指定して複数ファイルのデータを一括で取得します

ポイント

パラメーター

今回は過去の記事で解説したパラメーターを使用してシート名を指定します

今回は上の画像にある「sheetName」というパラメーターを作成し、こちらでシート名を可変で指定できるようにします

ダミーの値によるフィルター

こちらも過去記事で紹介した方法です

簡単に可変の値でフィルターをかける方法です

一度、ダミーの値でフィルターをします

その後、前述のようにフィルターをかけた値に可変にするパラメーターを組み入れます

不必要な列の削除、および行のフィルター

今回の処理では、不要な列の削除と行のフィルターを行う箇所が随所にでてきます

フォルダからデータ取得

従来の「フォルダからデータを取得してエディタを開くフロー」と一部違う点があります

今回は上の画像の箇所ではなく、次の箇所から直接エディタを開き、シート選択のステップは省略します

データ成型

エディタを開いて「適用したステップ」を確認すると、次の画像のようにステップが1つしかありません

エディタの中味はというと、次の画像のようにまだファイル内のデータを取得していない状態になっています

こここから、カスタム列作成画面でM関数を使用してデータを成型していきます

その前に、不要なファイルや列を削除していきます

~で始まるファイルはフィルター条件を指定して除いておきます

その後、「Content」「Name」以外の列を削除します

不要なファイルや列を削除したら、カスタム列作成画面でM関数を使用します

M関数:Excel.Workbookでファイル内のデータを取得できるようになります

次の画像が上記の「M関数を含むカスタム列」を挿入した後の画面です

ファイル内のデータを取得した内容を展開できるようになっています

こちらを展開処理します

ただ1回展開処理しても、まだ次の画像のようにデータ成型したと言える状態になっていません

ここから再び不要な列を削除した後、データ/Data列を展開処理します

ここから、データの1行目をヘッダーにします

その後は不必要な列は削除し、不必要な行はフィルターしておきましょう

*シート名の列は残しておきましょう

パラメーターの作成及びシート名でのフィルター処理

ここからはポイントの項で前述したパラメーターを作成します

上の画像の「新しいパラメーター」をクリックして次の画像の画面を開きます

現在の値には取得したいシート名を設定します

これでシート名を可変にするパラメーターは作成できました

その後、ダミーの値でシートをフィルターします

ここで数式バーに注目してください

ダミーでフィルターした値が数式の中に組み込まれています

こちらを設定したパラメーターで置き換えます

フィルターされたシートがData_202202➡Data_202201に変わりました

これで処理は終了です

試しにパラメーターの設定値を他の値に変えてみます

パラメーターの値に合わせてフィルターされるシート名が変更になっています

<まとめ>

今回は複数ファイルの中にある複数シートの中から、特定のシートのデータのみを一括で取得する方法を解説しました

パラメーターとダミーでフィルターを行う仕組みを組み合わせると、意外と簡単にシート名を可変で取得する仕組みが構築できます

ただ、無駄な列の削除や不要な行のフィルター処理が多い点が難点です

この点さえクリアーすれば、一度作成したパラメーターを何度でも使い回せますので、シートの管理がとても楽になります

ぜひ試してみてください

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

フォルダから複数ファイルデータを一括取得~初級講座4回目

こんにちは、Excellent仕事術ガッツ鶴岡です

前回はエクセルファイルからデータを取得しました

今回はフォルダにある複数ファイルから一括でデータを取得します。

フォルダ内データ一括取得

この機能はPower Queryの機能の中でも一押しの機能のひとつです

何故なら、複数ファイルに分散しているデータを一括で処理しようとしたら、通常はガッツのある人の手が必要となるからです。

その一括処理の内容はとても面倒くさく、ミスも起こり易いです

ところが、今回紹介するPower Queryのある機能を使うと、フォルダにファイルを入れておきさえすれば、全て一括で取得してくれるのです!

記事を最後まで読む時間が無い、という方はぜひこちら↓の動画をご覧ください

ちなみに、動画内で使用しているサンプルファイルは下からダウンロードできます

本題に入る前に、今回の解説に使用するデータを紹介します

今回の解説に使用するフォルダ(Data)の中には、3つファイルが入っています

アイコン

フォルダ用データ 22.26 KB 238 downloads

...

営業の部署 にて、営業担当者毎に販売内容をフォルダ内に入れていく想定です

ファイルに書き込まれている内容(シート:Data)は次のような項目です

ちなみにDataシート以外にもメモというシートがあります

では、本格的な内容の解説に入りたいと思います

解説は 「1.取得元のフォルダを指定する」と「2.ファイルの中で取得するシートを指定する」の2つに分けて行います

1.取得元のフォルダを指定する

今回もデータタブの操作から処理を開始します

➀フォルダから取得を指定します(下の図参照)

➁実際に使用するフォルダを指定します

今回の解説で使用するのは「Data」フォルダーです

③指定するフォルダ名を確認 ⇒「OK」を押すとフォルダの指定は終了です

フォルダの指定が終了した後は、1~3回目の解説では出てこなかった画面が登場します

2.ファイルの中で取得するシートを指定する

➀フォルダに入っているファイル名が表示されます

下の図の中に「結合」のボタンが見えますでしょうか?

一番下に4つのボタンが見えますが、一番左のボタンです

このボタンについては次の➁の解説で操作します

➁ボタン右横の▼マークを押し、「データの結合と変換」を押します

この時、フォルダ内のファイルが1つでも開いていると、データの結合と変換がうまくいかないので注意が必要です

「データの結合と変換」をクリックした後は、また更に新たな画面が開きます

③取得するシートを指定します

新たに開いた画面(下の図参照)の左側に、ファイルに含まれるシート名(Data、メモ)が表示されるので、Dataシートを選択します

Dataシートを選択したら、OKボタンを押します

そうすると、Power Queryエディターが開きます

④Power Queryエディター画面で読込処理を行う

Power Queryエディター画面には複数ファイルの情報が一括表示されます

まず、設定されるクエリの名前を「一括データ」に変更しましょう!

次にPower Queryエディター画面左上で「閉じて読み込む」ボタンをクリックします

これでフォルダ内のデータが一括で読み込まれます

ファイル別に分かれていた注文NOも1~6まで一括で表示されています

さて、ここからがこのフォルダ一括読込機能の最大の醍醐味です

フォルダ内にもう一つ、ファイルを追加します

アイコン

追加用1 9.66 KB 109 downloads

...

通常は、これまで行ってきた1、2の作業を行う必要がありそうですよね

ところが、ワンクリックで追加ファイルを一括で読み込むことができます

エクセルシートの右に”一括処理”という前回作成したクエリがあります

こちらのクエリの上にカーソルを置き、右クリックします

そうすると最新の情報に更新できるようになります

更新処理を行うと、フォルダ内に新規追加したファイル内の注文NO:78が下の図のようにシート上に反映されています

<まとめ>

この機能はこれまでのエクセル処理の常識を覆す内容です

今までは私もそうでしたが、フォルダ内にある複数ファイルのデータを一括で取得しようとしたらVBAのコードを書いていました

しかも、そのVBAコードはVBA初心者が書ける内容ではないです

2-②「データの結合と変換のクリック時」でも説明しましたが、フォルダ内のファイルが一つでも開いているとうまく行かない点だけは注意が必要です

後はPower Queryの画面操作に慣れてくれば、とても簡単な処理だと思います

ぜひ、職場で有効活用してみてください!

次回は読込先の変更について解説します!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村