Google検索

Office2019 Excel2019

Power Queryで複数シートの結合

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

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

結合だけなら、コピー&ペーストでも出来ますが、Power Queryのメリットは、後から元のシートの数値が変更になったり、シートが追加されてもテーブルの更新ボタンをクリックするだけで済みます。

Power Queryでシートを結合

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

Power Query の概要 - Excel

シートの確認

シート名に[2011年度][2012年度][2013年度]と年度がついたワークシートがあります。項目名は、各シートとも[商品名][店名][売上]と同じです。

3つのワークシート

この3つのシートをPower Queryエディターで結合します。

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

参考複数シートの書式などを揃えるには、編集したいワークシートを選択して[グループ]として設定すると便利です。

複数のワークシートに同時に書式設定(グループ設定の活用)

Excelでは、同じ形式のワークシートを複数作成することはよくあると思います。月別や県別、支店別などに分けて管理されることは多いですね。 そ ...

続きを見る

新規ブックでインポート

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

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

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

[データの取り込み]ダイアログボックスが表示されますので、ファイルを選択して[インポート]ボタンをクリックします。

[データの取り込み]ダイアログボックス

[ナビゲーター]ウィンドウが開きます。

ブック名を選択して[データの変換]をクリックします。

ここでシート名を選択しないように気をつけてください。

参考シートを結合する方法は、1つではありません。他の方法もありますが、ここではブックを選択してまとめてインポートします。

ナビゲーターウィンドウ

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]タブの[外部データの取り込み]グループにある[ファイルから]-[Excelから]を選択します。

Excel2013[POWER QUERY]タブの[ファイルから]-[Excelから]

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

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

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

続きを見る

Power Queryエディターで編集

以下のようなPower Queryエディターが起動します。

Power Queryエディター

不要な列の削除

不要な列を削除します。

[Item][Kind][Hidden]の列を選択して、[ホーム]タブの[列の管理]グループにある[列の削除]をクリックします。

[Shift]キーを使用すると、連続した列を選択できます。

Power Queryエディター[ホーム]タブの[列の削除]

参考列の削除は、右クリックのショートカットメニューからも行うことができます。

右クリックのショートカットメニューから[列の削除]

Data列の展開

[Data]列の右上のボタンをクリックします。

そして[さらに読み...]をクリックします。

Data列の展開

以下のようになりますので、[展開]が選択されていることを確認して[OK]ボタンをクリックします。

Data列の展開

展開されて、以下のようになります。

参考右側の[クエリの設定]ウィンドウでは[適用したステップ]には、クエリに記録された作業が順に表示されます。Power Queryエディターには元に戻るボタンなどはありませんが、[適用したステップ]の[×]をクリックすると、処理を取り消して1つ前の状態に戻ることができます。

PowerQueryエディターの[展開したステップ]

テーブルの先頭行を列見出しに設定

1行目を列見出しにするため[ホーム]タブの[変換]グループにある[1行目をヘッダーとして使用]をクリックします。

1行目をヘッダーとして使用

このテーブルの先頭行を列見出しに昇格させます。

[ホーム]タブの[1行目をヘッダーとして使用]

結果、以下のようになります。

[ホーム]タブの[1行目をヘッダーとして使用]をクリックした結果

[2011年度]の列の名前を変更します。ダブルクリックするか、[変換]タブの[任意の列]グループにある[名前の変更]をクリックすると、反転するので変更できます。

ここでは、[年度]にしておきます。

Power Queryエディター[変換]タブの[列の名前の変更]

以下のようになります。

Power Queryエディターで列の名前を変更後

タイトル行を非表示

タイトル行は不要なので、[商品名]列のフィルターボタン▼をクリックして、[商品名]のチェックボックスをオフします。[OK]ボタンをクリックします。

Power Queryエディターのフィルターボタン

以下のようになります。

タイトル行を非表示にした結果

データ型の変更

[売上]列のデータ型を整数に変更します。左上のボタンをクリックすると、データ型の一覧が表示されますので、[整数]を選択します。

Power Queryエディターの[データ型]の変更

データ型の変更は、[変更]タブの[任意の列]グループにある[データ型]からも変更することができます。整数を選択すると、以下のようになります。

Power Queryエディター[変更]タブの[任意の列]グループにある[データ型]

閉じて読み込む

Power Queryエディターからワークシートに読み込みます。

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

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

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

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

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

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

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

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

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

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

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

シートに読み込んだテーブル

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

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

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

続きを見る

Power Queryエディターの再表示

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

[クエリと接続]作業ウィンドウ

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

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

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

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

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

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

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

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

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

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

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

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

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

ワークシートが増えた場合

Power Queryエディターでシートを結合した場合、同一ブック内にさらに同じレイアウトのワークシートが増えても、更新のみで反映できます。

例えば、以下のように[2014年度]のシートが追加しました。

ワークシートの追加

[デザイン]タブの[外部のテーブルデータ]グループにある[更新]から[更新]をクリックします。一瞬で2014年度のデータが追加されます。

テーブルで更新した結果

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

読み込んだテーブルでピボットテーブルを作成した場合は、テーブルから先に更新して、ピボットテーブルを更新します。

テーブルからピボットテーブルを作成するには、テーブル内のセルをアクティブにして、[デザイン]タブの[ツール]グループにある[ピボットテーブルで集計]をクリックします。

ピボットテーブルで集計

ピボットテーブルを使用してこのテーブルのデータを集計します。

ピボットテーブルを使用すると、複雑なデータの配列や集計、詳細の確認を簡単に行うことができます。

[デザイン]タブの[ツール]グループにある[ピボットテーブルで集計]

[ピボットテーブルの作成]ダイアログボックスが表示されます。

ここでは、既定のまま新規ワークシートに作成します。

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

Sheet2に以下のようにピボットテーブルのフィールドを各エリアへドラッグして作成しました。

  • [年度]フィールドを[行]エリアへ
  • [商品名]フィールドを[列]エリアへ
  • [売上]フィールドを[値]エリアへ

ピボットテーブルでレイアウト

元データであるワークシートで変更があった場合は、まず、テーブルで更新します。

ここでは、元データで2014年度のシートが追加されています。

Sheet1に切り替えて、テーブル内のセルをアクティブにします。

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

データが更新されたことを確認します。

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

Sheet2に切り替えて、ピボットテーブル内をクリックします。

[ピボットテーブルツール]タブの[分析]タブにある[更新]をクリックします。

[ピボットテーブルツール]タブの[分析]タブにある[更新]

以下のように2017年度のデータが追加されます。

データが更新されたピボットテーブル

参考ピボットテーブルについては、以下の記事が参考になると思います。

ピボットテーブルとピボットグラフを同時に作成

ピボットテーブルの作成に慣れたなら、グラフも同時に作成してみませんか? ここでは、Excel2016で解説します。 サンプルのデータは以下の ...

続きを見る

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

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

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

続きを見る

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

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

続きを見る

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

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

続きを見る

複数のマトリックス表をPower Queryでピボットテーブルに変換

Power Queryを使って、複数のマトリックス表を結合して1つのピボットテーブルに変換することができます。一度、ピボットテーブルを作成す ...

続きを見る

また、指定したフォルダー内の複数ファイルのデータを1つのテーブルに結合することもできます。

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

ExcelのPower Queryを使って、指定したフォルダー内の複数ファイルのデータを1つのテーブルに結合することができます。後でそのフォ ...

続きを見る

ピボットテーブルを学習するなら、以下のテキストがお勧めです。Power Queryについても学習することができます。

Profile

執筆者/はま

おすすめ記事と広告

-Office2019 Excel2019

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

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