Office 2019 Excel 2019

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について学習するなら、以下のテキストがお勧めです。

created by Rinker
★★★★★ マクロもVBAも必要なく標準機能だけでOK♪
Amazonのレビューを見る

Power Queryエディターの再表示

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

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

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

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

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

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

Power Queryエディターを起動するショートカットキーは、[Alt]+[F12]です。

キーボード[alt]+[f12]

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

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

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

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

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

  • すべて更新[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で2つのデータの共通項目を関連付け(マージ)して取り込む

Power Queryでは、2つのデータの共通項目を関連付けして、1つのテーブルを作成することができます。 Excel2013以降であれば、 ...

続きを見る

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

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

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

続きを見る

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

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

続きを見る

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

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

続きを見る

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

created by Rinker
★★★★★ 分かりやすい!Excelの基本はこれでOK♪
Amazonのレビューを見る

Profile

執筆者/はま

-Office 2019 Excel 2019

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

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