スポンサーリンク
今回は、ExcelのPowerQueryで、ある1つのフォルダ中のExcelファイル等を、全部まとめて開く方法を取り上げる。
これまでPowerQueryの記事で取り上げてきたのは、1個のExcelファイルを指定して開く方法ばかりだった。
実際のところ私なんかは、今回取り上げる方法で、フォルダの方を指定してExcelファイルを開くことが多い。
ファイル名を指定して開くには、1回1回変わるファイル名を律儀に指定し直さないといけないが、それこそがPowerQueryの最も面倒な要素の一つだ。
だがフォルダのパスなら、それを一度指定してしまえば、その中のファイルを開くのにファイル名とか関係ないので、幾分やりやすくなる。
フォルダ内の複数ファイルを開く
今回題材として使用するファイルは、こちらのフォルダにまとめて保存してあるので、これを自PCのフォルダに保存してから進めると良い。
では開き方。
リボン「データ」タブ
→「データの取得」
→「ファイルから」
→「フォルダーから」
とする。
この「フォルダーから」は、ぜひオリジナルのリボンに組み込んでおこう。
フォルダーのパスを入力
フォルダーのパスを入力するダイアログが出てくる。
ここで「参照」を押しても良いが、それだとフォルダを選ぶための凄く面倒なダイアログが再び出てきて、それは避けたい。
要はこのテキストボックスに、フォルダーの場所を文字列として入れれば良いわけだ。
こういうのはなるべく、そのパスの文字列を、予めコピーしておいてから貼り付けるだけで済むようにしておこう。
ちなみにWindowsのエクスプローラ(Windows+Eで起動できるよ)で、Alt+Dを押せば、フォルダのパスが書いてあるアドレス欄にジャンプできるので、そこでパスをコピーすれば良い。
そして、フォルダ内のファイルを結合したりするか問う画面が出てくる。
ここでは、ひとまず「データの変換」を押そう。
これ以外のボタンを押すと、なんかファイルの更新日時とかの情報ばかり1行ずつ表示されたもの(下のイメージ画像がそんな感じだけど)がExcel出力されるような面倒なものが多く、それは扱わない。
ファイル名でフィルタリング
表示されるデータの中には、様々な項目の列がある。
今回はこの中で、ファイル名を示す「Name」列に注目する。
「Name」列の右端にある四角のボタンを押して、フィルタリング画面を開く。
複数あるExcelファイルの中で、一部のファイルのみを選択し結合したい場合に、それらファイルを選択する。
今回の例では「個人情報9999.xlsx」のファイルはちょっと他と違うファイルなので、これのチェックを外し「OK」を押そう。
データを結合
選択された複数Excelファイルのデータを結合する。
「Content」列の右端にあるボタンを押そう。
フォルダ内の複数ファイル結合に関する画面が出てくる。
まず一番上に「サンプルファイル」を選ぶ項目がある。
要は、同様のレイアウトになっている複数ファイルを開くに当たって、テンプレートとなる最初のファイルを選択するものだ。
ここはイジらず「最初のファイル」のままにしておけば良い場合が多いかと思うが、変更したければ一覧から改めて選択すれば良い。
「シート1」など同名のシート1個ずつだけを取り込み結合したいという場合
→こちら
1ファイル内の複数シートを結合とかまで含めて、1通りに定まらない様々な名称のシートを結合したいという場合
→こちら
(1)各Excelファイルから1つずつ同名シートを結合する場合
まず、取り込むExcelファイルが幾つあっても、その中にある「シート1」など同名のシート1個ずつだけを取り込み結合したいという場合。
私が会計システムから出力したExcelファイルを結合する場合なんかは、これで簡単に済ませられる場合が多い。
先ほどの説明で、複数ファイル結合に関する画面から再開。
左側の画面には、シートやテーブルの名前の一覧が表示される。
今回は、「Sheet1」しか表示されないはずなので、それを選択して「OK」を押せば良い。
展開したデータには、左端に「Source.Name」という列があり、これがExcelファイルの名前になる。
ファイル名の情報が必要で敢えてこの「Source.Name」列を残すのが有効な場合もあるだろうし、この列を削除するかは場合に応じて判断すれば良い。
ひとまず、今回は「Source.Name」列を削除して進める。
不要な行の削除
今回の例題のファイルでは、見出しが「Column1」などとなって正しく設定されておらず、先頭の2行が邪魔だ。
リボン「ホーム」タブ
→行の削除
→「上位の行の削除」
とし、次の画面で邪魔な行数の「2」を指定して「OK」とする。
そして
リボン「ホーム」タブ
→「1行目をヘッダーとして使用」
として、見出し行を正しく認識させる。
この辺は、これまでの記事でも何度か扱ってきた通り。
しかしこれだけでは、複数取り込んだExcelファイルのうち、最初のファイルについて先頭の2行を削除しただけだ。
下図の51～53行目のように、2個目以降のファイルについては不要な行が削除されていないので、これはフィルタリングで削除していくことになる。
フィルタリングのやり方は色々あって、その場その場の状況に応じることになるが、今回は「性別」列について「null」「性別」のチェックを外すという方法で良いだろう。
要は
- データがnullの行
- データが見出しの文字と同じもの
を削除すれば良いということが多い。
今回の例では「名前」列なんかはそれだけでは上手く行かないが。
これで、不要な行が正しく削除された状態になる。
(2)シート名が複数混在する取込みの場合
次は、1ファイル内の複数シートを結合とかまで含めて、1通りに定まらない様々な名称のシートを結合したいという場合。
先ほどの説明で、複数ファイル結合に関する画面から再開。
左側の画面で、パラメーターとか書いてある、フォルダのマークをクリックして「OK」を押せば良い。
それでひとまず、全てのシートやテーブルがまとめて結合候補として読み込まれる。
属性でフィルタリング
次のような画面になるけど、なんかFilterDatabaseとかPrint_Titlesとか、余計なものも混じっている。
シート以外に、名前定義とかテーブルとかいったものも、一覧の中に入ってくるということだ。
この中から、不要なものは削除する。
「Kind」列に、データの種類についてまとめられている。
「Sheet」→シート
「DefinedName」→名前定義
「Table」→テーブル
という感じで、通常は「Sheet」にフィルタリングすれば良いだろう。
「Table」の方が、データ範囲が明確に指定されるので良い場合も多いが。
種類を「Sheet」のみに絞ると次のようになる。
「Name」列がシート名にあたるので、必要ならそれもフィルタリングする。
そして「Data」列見出しの右端にある四角ボタンを押して、データを展開する。
展開する項目を選び、「元の列名をプレフィックスとして使用します」のチェックは通常は外せば良い。
そして「OK」を押す。
下図のようにデータ展開される。
後は、同名シート1個ずつだけを取り込む場合と同じ要領で進めれば良い。
スポンサーリンク
