オーロラさんの勉強帳

33歳、未経験からIT業界へ。SESのロースキル客先常駐勤務。データベース、Excel、Excel VBA、ネットワーク、LinuxなどIT関連のことを主に書いていきます。

【Power Query】【Excel】フォルダ内の全てのファイルのデータを1つのブックにまとめる(結合する)方法

 

やりたい事

Power Queryを使って、同じフォルダー内の全てのファイルの情報を1つのファイルにまとめる。

f:id:auroralights:20201219024142p:plain

フォルダ内のファイルをまとめる方法には、手作業でコピーして1つのファイルにまとめたり、マクロを作ったり、色々な方法があると思いますが、Power Queryを使うと、とても簡単に1つのファイルにデータをまとめる(結合する)ことができます。
Power Query、すごい便利です。

 

事前の準備

テーブルにする

まとめるファイルのデータを同じ名前のテーブルにしておきます。「先頭行をテーブルの見出しとして使用する」にチェックを付けておきます。(表のデータを選択している状態で、Ctrl+Tを押すと表をテーブルにできます)

テーブルにすることで各ファイルのデータの先頭行をヘッダー情報としてPower Queryで扱ってくれます。

f:id:auroralights:20201219112101p:plain

※テーブル名を同じ名前にしておきます。

f:id:auroralights:20201219111714p:plain

 

補足情報:テーブル・先頭行をテーブルの見出しとして使用する

「先頭行をテーブルの見出しとして使用する」にチェックを入れない場合は、以下のように列1、列2という不要な情報が入ってしまいます。

f:id:auroralights:20201219112713p:plain

 

「先頭行をテーブルの見出しとして使用する」テーブルになっていないデータの場合、1行目のデータもヘッダーではなく、データとして扱われます。

f:id:auroralights:20201219113434p:plain

「先頭行をテーブルの見出しとして使用する」テーブルの場合は、以下のように商品コード、商品名がヘッダーとして扱われます。

f:id:auroralights:20201219113642p:plain

補足情報:テーブルでないデータについて

データをテーブルにせずにまとめる(結合する)場合は、各ファイルのシート名を同じ名前にしておく必要があります。

Power Queryでフォルダ内の全てのファウルを1つのファイルにまとめる(結合する)方法

1.新規でExcelブックを起動します。

f:id:auroralights:20201219024900p:plain

 

2.画面上部の「データ」タブから「データの取得」、「ファイルから」「フォルダーから」を順次選択します。

f:id:auroralights:20201219025035p:plain

 

3.フォルダーパス欄にまとめたい(結合したい)ファイルが入っているフォルダーパスを入力し、「OK」を選択します。

※フォルダーパスは「参照」から指定することもできます。

f:id:auroralights:20201219025222p:plain

4.表示されたウィンドウの「結合」、「データの結合と変換」を順次選択します。

・データの結合と変換:結合した状態でPower Queryエディタが開く
・結合および読み込み:結合した表を新しいワークシートに読み込む
・結合および読み込み先:結合した表を

f:id:auroralights:20201219025845p:plain

5. 「テーブル1」を選択するとサンプルファイル「最初のファイル」のSheet1のプレビューが右側に表示されるので、「OK」を選択します。

※まとめる(結合する)ファイルのデータがテーブルの場合は、テーブル名を選択します。テーブルでない場合は、シート名を選択します。
※テーブルをまとめる場合は、すべてのファイルのテーブルが同じ名前である必要があります。
※テーブルでないデータをまとめる場合は、すべてのファイルのシート名を同じシート名にする必要があります。

 

・テーブルの場合:先頭行がヘッダーとして扱われます。

f:id:auroralights:20201219111327p:plain

・テーブルでない場合

f:id:auroralights:20201219030742p:plain

※テーブルでないデータをまとめた場合「1行目をヘッダーとして使用」を選択をすると、1行目がヘッダーとなります。

ただし、他のファイルの1行目はデータとなっているので、あらかじめデータをテーブルにしておく方が便利です。

f:id:auroralights:20201219032731p:plain

6.「閉じて読み込む」を選択するとExcelファイルに結合した情報が読み込まれます。

f:id:auroralights:20201219115314p:plain

フォルダー内にファイルを追加した場合の更新方法

以下のようにフォルダーにファイルを追加した場合、「更新」を選択するだけで、データを反映することができます。

f:id:auroralights:20201219115723p:plain

「クエリ」タブの更新を選択すると、追加したファイルの情報が追加されます。 
※4.xlsxの情報が追加されました。

f:id:auroralights:20201219115938p:plain