Google検索

Office2019 Excel2019

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

投稿日:

Power Queryを使って、複数のマトリックス表を結合して1つのピボットテーブルに変換することができます。一度、ピボットテーブルを作成すると、シートが増えたり、減ったりしても更新ボタンをクリックするだけです。

ここでは、以下の[複数シートのデータ集計]で使用したデータをもとに解説します。

複数シートのデータ集計

Excelでは、異なる複数のシートの合計を簡単に求めることができます。 3つの方法を紹介します。 集計シートの合計値を求めたい範囲を選択して ...

続きを見る

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

Excel2019(永続ライセンス)バージョン1908(ビルド11920.20300)での解説です。

参考Power Query(パワークエリ)については、以下の記事が参考になると思います。Power Queryを使用できるのはExcel2013以降のバージョンです。

Power Query の概要 - Excel(Microsoft)

Power Query を使用すると、データソースを検索し、接続を作成し、必要に応じてデータを図形(列を削除したり、データ型を変更したり、テーブルを結合したりするなど) を行うことができます。データを整形したら、結果を共有したり、クエリを使用してレポートを作成したりすることができます。(上記ページより)

Power Queryが使用できない環境でも、ピボットテーブルウィザードで統合することができます。

複数のマトリックス表(クロス集計)をピボットテーブルウィザードで統合

複数のマトリックス表(クロス表)をピボットテーブルウィザードで統合する方法です。[データの統合]という機能を使って、合計を求めたりすることも ...

続きを見る

シートの確認


サンプルとして使用するブック[店舗別集計.xlsx]の結合する前のワークシートを確認しておきます。

[東京]「大阪」「名古屋」[福岡]の4つのシートがあります。同じレイアウトで行も列も同じ項目です。

4つのシートの確認

この4つのシートをPower Queryで結合してピボットテーブルを作成するまでを解説します。

新規ブックでインポート

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

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

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

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

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

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

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

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

参考シートを結合する方法は、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]キーを使用すると、連続した列を選択できます。

そして、ショートカットメニューから[列の削除]をクリックします。

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

[ホーム]タブの[列の管理]グループにある[列の削除]をクリックしても同じです。

[ホーム]タブの[列の管理]グループにある[列の削除]

Data列の展開

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

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

[Data]列の右上のボタンをクリックして[さらに読み...]をクリック

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

Data列の展開

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

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

ただし、途中のステップを削除するとエラーになることがありますので注意してください。

Power Queryエディーターの[適用したステップ]

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

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

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

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

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

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

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

タイトル行を非表示

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

[商品名]列のフィルターボタン▼をクリック

以下のようになります。

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

列のピボット解除

マトリックス表をテーブル形式にするための変換を行います。Power Queryエディターでは、[列のピボット解除]という機能になります。

[1月]から[4月]の列までを選択します。

そして、[変換]タブの[任意の列]グループにある[列のピボット解除]の▼ボタンをクリックして、メニューの中から[選択した列のみをピボット解除]をクリックします。

ポップヒントには以下のように記されています。

現在選択されている列のみ、属性/値のペアに変換します。

列を選択して[選択した列のみをピボット解除]

以下のようにピボットが解除されて、[属性]と[値]の列になります。

ピボットを解除した結果

列の名前の変更

[東京]と[属性]と[値]の列の名前を変更します。

列の名前の変更

[東京]の列を選択して、[変換]タブの[任意の列]グループにある[名前の変更]をクリックします。

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

列の名前が反転しますので、そのまま名前を入力します。ここでは、[店名]とします。

参考列の名前の変更は、列名の箇所でダブルクリックしても入力できる状態になります。

同様に[属性]と[値]の列も名前を変更します。ここでは[月]と[売上個数]とします。

列の名前を変更したPower Queryエディター

データ型の変更

[売上]列のデータ型を整数に変更します。

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

データ型の変換

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

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

閉じて次に読み込む

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

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

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

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

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

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

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

ここでは、一度テーブルとして読み込み、その後でピボットテーブルを作成します。

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

Sheet1に以下のようにテーブルとして読み込まれます。行は49行まであります。

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

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

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

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

続きを見る

ピボットテーブルを作成

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

ピボットテーブルで集計

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

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

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

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

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

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

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

  • [商品名]フィールドを[行]エリアへ
  • [月]フィールドを[列]エリアへ
  • [販売個数]フィールドを[値]エリアへ
  • [店名]フィールドを[フィルター]エリアへ

ピボットテーブル

串刺し計算と同じように各店舗の合計を求めることができます。

串刺し計算

参考ピボットテーブルを作成しておくと、さまざまな切り口で集計と分析ができます。

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

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

続きを見る

ピボットテーブルにデータの更新を反映

Power Queryで作成していると、同じレイアウトのシートが増えたり、減ったりしても更新ボタンを押すだけです。もちろん、データが変更されても更新するだけです。

ここでは、ピボットテーブルの元データのなるブック[店舗別集計表.xlsx]の[福岡]シートを削除してみます。

[福岡]シートを削除したブック

テーブルで更新

読み込んだテーブルを元にピボットテーブルを作成しているので、まず、テーブルで更新します。

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

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

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

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

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

以下のように更新されます。

テーブルの更新

ピボットテーブルで更新

そして、ピボットテーブルで更新します。

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

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

ピボットテーブルの更新

フィルターの[店名]を開いてみると、[福岡]が削除されていることが分かります。

フィルターの[店名]

以下の記事でも解説しています。

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

Power Queryエディターの編集

Power Queryエディターは再表示して編集することができます。

ただし、途中のステップを削除するとエラーになることがありますので注意してください。

もし、[クエリと接続]ウィンドウを閉じてしまった場合は、[データ]タブの[クエリと接続]グループにある[クエリと接続]をクリックします。

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

[クエリと接続]ウィンドウでクエリをダブルクリックします。

[クエリと接続]ウィンドウでクエリをダブルクリック

他の方法もあります。以下の記事で解説しています。

Power Queryエディターの再表示

データの再取り込み

もし、別のシートにクエリを再読み込みしたい場合は、[データ]タブの[データの取得と変換]グループにある[既存の接続]をクリックします。

クエリからテーブルを読み込んだシートを削除してしまった場合も再読み込みができます。

既存の接続を使用してデータを取り込む

よく使用するソースから、データをインポートします。

[データ]タブの[データの取得と変換]グループにある[既存の接続]

[既存の接続]ウィンドウが表示されます。

[接続]タブの目的の接続を選択して、[開く]ボタンをクリックします。

[既存の接続]ウィンドウ

[データのインポート]ダイアログボックスが表示されます。必要な項目を選択して[OK]ボタンをクリックします。

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

参考マトリックス表をテーブルに変換する方法は、以下の記事でも解説しています。

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

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

続きを見る

また、複数シートの結合については、以下の記事も参考になると思います。

Power Queryで複数シートの結合

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

続きを見る

Profile

執筆者/はま

おすすめ記事と広告

-Office2019 Excel2019

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

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