Google検索

Office2019 Excel2019

Power Queryでフォルダー内のすべてのファイルのデータを結合

投稿日:2019年9月3日 更新日:

ExcelのPower Queryを使って、指定したフォルダー内の複数ファイルのデータを1つのテーブルに結合することができます。後でそのフォルダー内にファイルを追加しても、結合したテーブルで[更新]ボタンをクリックするだけで、新しいデータを結合できます。

指定フォルダー内のデータを結合

ただし、各ブックのテーブルの[テーブル名]が同じであることが条件になります。テーブルではなく通常の範囲でも出来ますが、その際は[シート名]が統一されている必要があります。

ここでは、複数のブックで解説しますが、テキストファイルも同じように結合することができます。

参考Power Queryの概要については、Microsoftのページを参照してください。

Power Query の概要 - Excel

ブックとデータの確認

3個のブック[4月.xlsx」、[5月.xlsx]、[6月.xlsx]があります。

同じシート名[Sheet1]に同じレイアウトの表です。

参考テーブルの場合は、同じテーブル名にしておく必要があります。テーブルの名前の確認と変更方法を参考にしてください。

3つのブック

上記3個のブックが1つのフォルダーに保存されています。

同一フォルダー内の3個のブック

この3個のブックのデータをPower Queryで結合します。

ここではExcel2019(永続ライセンス)で解説します。

新規ブックでインポート

新規ブックを起動します。

[データ]タブの[データの取得と変換]グループにある[データの取得]から[ファイルから]-[フォルダーから]をクリックします。

フォルダーから

フォルダー内のファイルに関するメタデータとリンクをインポートします。

[データ]タブの[データの取得]から[ファイルから]-[フォルダーから]

以下のフォルダーのパスを入力するウィンドウが表示されますので、[参照]ボタンをクリックして目的のフォルダーを指定します。

フォルダーのパスを入力するウィンドウ

ここでは、以下のように指定しました。

フォルダーパスが正しく入力されていることを確認して[OK]ボタンをクリックします。

フォルダーのパスを入力したウィンドウ

以下のウィンドウが表示されます。

[結合]ボタンをクリックして、[データの結合と変換]をクリックします。

[結合]ボタンをクリックして、[データの結合と変換]をクリック

Excel2016でPower Queryを使用する場合

Excel2016(永続ライセンス)の場合は、[データ]タブの[取得と変換]グループにある[新しいクエリ]から[ファイルから]-[フォルダーから]を選択します。

Excel2016[データ]タブの[新しいクエリ]から[ファイルから]-[フォルダーから]

Excel2010/2013でPower Queryを使用する場合

Excel2010とExcel2013では、Power Queryアドインをインストールして使用します。

Excel2013にPower Queryをインストールして[POWER QUERY]タブを表示

Excel2013にPower QueryアドインをMicrosoftのダウンロードセンターのページからインストールしてみました。OSは、W ...

続きを見る

以下は、Excel2013にPower Queryアドインをインストールしています。

[POWER QUERY]タブの[外部データの取り込み]グループにある[ファイルから]-[フォルダーから]を選択します。

Excel2013[POWER QUERY]タブの[ファィルから]-[フォルダーから]

参考ご使用中のOfficeのバージョンを確認する方法は、以下の記事で解説しています。

Office(Word、Excel、PowerPointなど)のバージョンを確認する方法

Microsoft Officeも長い時間を経て、大きく様変わりしてきました。 トラブルを解決しようとするとき、あるいは、何かの操作を探すと ...

続きを見る

Power Queryエディターで編集

以下のような[Fileの結合]ウィンドウが表示されます。

[Sheet1]を選択すると、右側にプレビューが表示されます。[OK]ボタンをクリックします。

参考もし、ブック内にテーブルがあって、それを結合する場合は、左側でテーブル名を選択します。

[Fileの結合]ウィンドウ

以下のようなPower Queryエディターが表示されます。

Power Queryエディター

列の名前の変更

左端にある[Source.Name]の名前を変更します。

ダブルクリックして[月]と入力します。名前の変更は、[変換]タブの[任意の列]グループにある[名前の変更]をクリックして操作してもOKです。

[変換]タブの[任意の列]グループにある[名前の変更]

参考不要な列などがあれば、削除してください。

不要な列の削除

値の置換

[月]列の拡張子は不要なので、削除します。

右クリックしてショートカットメニューから[値の置換...]をクリックします。

あるいは、[変換]タブの[任意の列]グループにある[値の置換]ボタンをクリックします。

[変換]タブの[任意の列]グループにある[値の置換]

以下の[値の置換]ダイアログボックスが表示されます。

[値の置換]ダイアログボックス

[検索する値]に[.xlsx]、[置換後]のテキストボックスは空白のままにして[OK]ボタンをクリックします。

[値の置換]ダイアログボックスの[検索する値]に入力

以下のようになります。

値が置換された列

閉じて読み込む

ワークシートに読み込みます。

[ホーム]タブの[閉じて読み込む]の下方(文字部分)をクリックして、[閉じて次に読み込む...]をクリックします。

[ホーム]タブの[閉じて次に読み込む...]

[データのインポート]ダイアログボックスが表示されます。

以下のように[テーブル]と[既存のワークシート]を選択して[OK]ボタンをクリックします。

参考既定では[新規のワークシート]が選択されていますが、[既存のワークシート]を選択すると任意のシートの任意のセルを指定できます。

[閉じて読み込む]をクリックすると、新規ワークシートへテーブルとして読み込まれます。すぐにピボットテーブルを作成した場合なども[閉じて読み込む]を選択すると効率的です。

[データのインポート]ダイアログボックス

Sheet1に以下のようにテーブルとして読み込まれます。

必要に応じてテーブルの書式設定をします。

金額に桁区切りが必要であれば[桁区切りスタイル]を設定するなどして、テーブルを調整してください。

更新しても、テーブルのスタイルが解除されることはありません。

ワークシートに読み込まれたテーブル

ここでは、テーブルの[売上]列に桁区切りを設定しました。

D列を選択して、[ホーム]タブの[数値]グループにある[桁区切りスタイル]をクリックしています。

[ホーム]タブの[数値]グループにある[桁区切りスタイル]

参考テーブルに変換されると、テーブルとしての便利な機能も使用できるようになります。

テーブルの便利機能(テーブルに変換して効率アップ)

Excelのテーブルはとても便利なものです。 通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分 ...

続きを見る

Power Queryエディターの再表示

再度、Power Queryエディターを表示して編集する場合は、[クエリと接続]ウィンドウにあるクエリをダブルクリックします。

[クエリと接続]ウィンドウにあるクエリ

もし、[クエリと接続]ウィンドウが非表示の場合は、[データ]タブの[クエリと接続]グループにある[クエリと接続]をクリックします。

[データ]タブの[クエリと接続]グループにある[クエリと接続]

または、[クエリ]タブの[編集]グループにある[編集]ボタンをクリックします。

[クエリ]タブの[編集]グループにある[編集]

データの更新をテーブルへ反映させる

データ元のワークシートでデータが変更された場合は、データを更新して最新のデータを反映させることができます。

[デザイン]タブの[外部のテーブルデータ]グループにある[更新]から[更新]をクリックします。

参考ブック内で複数の外部データとの接続がある場合、[すべて更新]をクリックすると、ブック内のすべての外部データの接続が更新されます。特定のデータのみ更新したい場合は、[更新]をクリックしてください。

ポップヒントで分かりますが、ショートカットキーは以下のとおりです。

  • すべて更新[Ctrl]+[Alt]+[F5]
  • 更新[Alt]+[F5]

[デザイン]タブの[外部のテーブルデータ]グループにある[更新]

または、[データ]タブの[クエリと接続]グループにある[すべて更新]から[更新]をクリックします。

[データ]タブの[クエリと接続]グループにある[すべて更新]

フォルダーにブックを追加した場合

Power Queryエディターでシートを結合した場合、同一フォルダー内にさらに同じレイアウトのブックが追加されも、更新のみで反映できます。

例えば、フォルダーに[7月]のブックを追加しました。

フォルダーにブックを追加

[デザイン]タブの[外部のテーブルデータ]グループにある[更新]をクリックします。

以下のように追加したブックの情報が追加されます。本当に便利です。

[デザイン]タブの[更新]

参考Power Queryエディターから読み込んだテーブルからピボットテーブルを作成することもできますが、更新する際はテーブルから更新して、ピボットテーブルの更新をしてください。以下の記事で解説しています。

ピボットテーブルを作成した場合の更新

テーブルの名前の確認と変更方法

各ブックのテーブルを結合することもできますが、テーブルの[テーブル名]が同じであることが条件になります。

テーブルに変換するには、[挿入]タブの[テーブル]グループにある[テーブル]をクリックします。

[挿入]タブの[テーブル]グループにある[テーブル]

[テーブルの作成]ダイアログボックスが表示されますので、データ範囲を確認して[OK]ボタンをクリックします。

[テーブルの作成]ダイアログボックス

以下のようにテーブルに変換されます。

テーブル内のセルをアクティブにすると、[テーブルツール]が表示されますので、[デザイン]タブの[プロパティ]グループにある[テーブル名]で確認できます。

また、このテキストボックスで名前を編集できます。

[デザイン]タブの[プロパティ]グループにある[テーブル名]

Power Queryエディターで指定フォルダー内の複数ブックのテーブルを結合する場合は、このテーブル名が統一されている必要があります。

テーブル名が異なると以下のメッセージが表示されますので、注意してください。

[Expression.Error]キーがテーブルのどの行とも一致しませんでした。

メッセージウィンドウ

関連Power Queryでシートの結合もできます。

Power Queryで複数シートの結合

Power Queryを使って、同一ブック内の複数シートを結合する方法を紹介します。同じレイアウトの表の場合は、簡単に結合できます。 結合だ ...

続きを見る

Power Queryエディターを使うとデータベースの整形を簡単に行うことができます。

先頭行に複数の同じ項目名がある表をPower Queryで分析可能なテーブルに変換

Excel2016から標準で装備されているPower Query(パワークエリ)を使用すると、効率よくデータを加工することができます。 Ex ...

続きを見る

1列の複数行で1レコードになっている表をPower Queryで複数列のテーブルに変換

1列の複数行に1レコードのデータが並んでいる表も、Power Query(パワークエリ)エディターを使って、複数列のテーブルに整形することが ...

続きを見る

Power Queryでデータベースに整形(マトリックス表をテーブルに)

Power Query(パワークエリ)は、Excel2016とExcel2019には、標準でインストールされています。Excel2010とE ...

続きを見る

Profile

執筆者/はま

おすすめ記事と広告

-Office2019 Excel2019

Copyright © 2001-2019 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2019 AllRights Reserved Powered by AFFINGER4.