Power Queryを使って、複数のマトリックス表を結合して1つのピボットテーブルに変換することができます。一度、ピボットテーブルを作成すると、シートが増えたり、減ったりしても更新ボタンをクリックするだけです。
ここでは、以下の[複数シートのデータ集計]で使用したデータをもとに解説します。
-
複数シートのデータ集計(シートが増減する場合の方法も解説)
Excelでは、異なる複数のシートの合計を簡単に求めることができます。 3つの方法を紹介します。 また、集計するシートが増減する場合の方法も ...
Excel2019(永続ライセンス)バージョン1908(ビルド11920.20300)での解説です。
参考Power Query(パワークエリ)については、以下の記事が参考になると思います。Power Queryを使用できるのはExcel2013以降のバージョンです。
Excel の Power Query について - Microsoft サポート(Microsoft)
Power Query (Excel で変換 & 取得) を使用すると、外部データをインポートまたは接続し、そのデータを整形できます (たとえば、列の削除、データ型の変更、テーブルのマージなど)。 次に、Excel にクエリを読み込んで、グラフとレポートを作成できます。 データを定期的に更新して、最新の状態にすることができます。(上記ページより)
Power Queryが使用できない環境でも、ピボットテーブルウィザードで統合することができます。
-
複数のマトリックス表(クロス集計)をピボットテーブルウィザードで統合
複数のマトリックス表(クロス表)をピボットテーブルウィザードで統合する方法です。 [データの統合]という機能を使って、合計を求めたりすること ...
この記事の目次
シートの確認
サンプルとして使用するブック[店舗別集計.xlsx]の結合する前のワークシートを確認しておきます。
[東京]「大阪」「名古屋」[福岡]の4つのシートがあります。同じレイアウトで行も列も同じ項目です。
この4つのシートをPower Queryで結合してピボットテーブルを作成するまでを解説します。
新規ブックでインポート
新規ブックを起動します。
[データ]タブの[データの取得と変換]グループにある[データの取得]から[ファイルから]-[ブックから]をクリックします。
[データの取り込み]ダイアログボックスが表示されますので、目的のファイルを選択して[インポート]ボタンをクリックします。
[ナビゲーター]ウィンドウが開きます。
ブック名を選択して[データの変換]をクリックします。
ここでシート名を選択しないように気を付けてください。
参考シートを結合する方法は、1つではありません。他の方法もありますが、ここではブックを選択してまとめてインポートします。
Excel2016でPower Queryを使用する場合
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から]を選択します。
参考ご使用中のOfficeのバージョンを確認する方法は、以下の記事で解説しています。
-
Office(Word、Excel、PowerPointなど)のバージョンを確認する方法
Microsoft Officeも長い時間を経て、大きく様変わりしてきました。 トラブルを解決しようとするとき、あるいは、何かの操作を探すと ...
Power Queryエディターで編集
以下のようなPower Queryエディターが起動します。
不要な列の削除
不要な列を削除します。
[Item][Kind][Hidden]の列を選択して、右クリックします。[Shift]キーを使用すると、連続した列を選択できます。
そして、ショートカットメニューから[列の削除]をクリックします。
[ホーム]タブの[列の管理]グループにある[列の削除]をクリックしても同じです。
Data列の展開
[Data]列の右上のボタンをクリックします。
そして[さらに読み...]をクリックします。
以下のようになりますので、[展開]が選択されていることを確認して[OK]ボタンをクリックします。
展開されて、以下のようになります。
参考右側の[クエリの設定]ウィンドウでは[適用したステップ]には、クエリに記録された作業が順に表示されます。Power Queryエディターには元に戻るボタンなどはありませんが、[適用したステップ]の[×]をクリックすると、処理を取り消して1つ前の状態に戻ることができます。
ただし、途中のステップを削除するとエラーになることがありますので注意してください。
テーブルの先頭行を列見出しに設定
1行目を列見出しにするため[ホーム]タブの[変換]グループにある[1行目をヘッダーとして使用]をクリックします。
1行目をヘッダーとして使用
このテーブルの先頭行を列見出しに昇格させます。
結果、以下のようになります。
タイトル行を非表示
タイトル行は不要なので、[商品名]列のフィルターボタン▼をクリックして、[商品名]のチェックボックスをオフします。[OK]ボタンをクリックします。
以下のようになります。
列のピボット解除
マトリックス表をテーブル形式にするための変換を行います。Power Queryエディターでは、[列のピボット解除]という機能になります。
[1月]から[4月]の列までを選択します。
そして、[変換]タブの[任意の列]グループにある[列のピボット解除]の▼ボタンをクリックして、メニューの中から[選択した列のみをピボット解除]をクリックします。
ポップヒントには以下のように記されています。
現在選択されている列のみ、属性/値のペアに変換します。
以下のようにピボットが解除されて、[属性]と[値]の列になります。
列の名前の変更
[東京]と[属性]と[値]の列の名前を変更します。
[東京]の列を選択して、[変換]タブの[任意の列]グループにある[名前の変更]をクリックします。
列の名前が反転しますので、そのまま名前を入力します。ここでは、[店名]とします。
参考列の名前の変更は、列名の箇所でダブルクリックしても入力できる状態になります。
同様に[属性]と[値]の列も名前を変更します。ここでは[月]と[売上個数]とします。
データ型の変更
[売上]列のデータ型を整数に変更します。
左上のボタンをクリックすると、データ型の一覧が表示されますので[整数]を選択します。
データ型の変更は、[変更]タブの[任意の列]グループにある[データ型]からも変更することができます。整数を選択すると、以下のようになります。
閉じて次に読み込む
Power Queryエディターからワークシートに読み込みます。
[ホーム]タブの[閉じて読み込む]の下方(文字部分)をクリックして、[閉じて次に読み込む...]をクリックします。
[データのインポート]ダイアログボックスが表示されます。
以下のように[テーブル]と[既存のワークシート]を選択して[OK]ボタンをクリックします。
参考既定では[新規のワークシート]が選択されていますが、[既存のワークシート]を選択すると任意のシートの任意のセルを指定できます。
[閉じて読み込む]をクリックすると、新規ワークシートへテーブルとして読み込まれます。すぐにピボットテーブルを作成した場合なども[閉じて読み込む]を選択すると効率的です。
ここでは、一度テーブルとして読み込み、その後でピボットテーブルを作成します。
Sheet1に以下のようにテーブルとして読み込まれます。行は49行まであります。
参考テーブルに変換すると、テーブルとしての便利な機能も使用できるようになります。
-
表をテーブルに変換してデータベース管理などの作業効率をアップ
Excelのテーブルはとても便利なものです。 通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分 ...
Power Queryについて学習するなら、以下のテキストがお勧めです。
ピボットテーブルを作成
テーブルからピボットテーブルを作成するには、テーブル内のセルをアクティブにして、[デザイン]タブの[ツール]グループにある[ピボットテーブルで集計]をクリックします。
ピボットテーブルで集計
ピボットテーブルを使用してこのテーブルのデータを集計します。
ピボットテーブルを使用すると、複雑なデータの配列や集計、詳細の確認を簡単に行うことができます。
[ピボットテーブルの作成]ダイアログボックスが表示されます。
ここでは、既定のまま新規ワークシートに作成します。
Sheet2に以下のようにピボットテーブルのフィールドを各エリアへドラッグして作成しました。
- [商品名]フィールドを[行]エリアへ
- [月]フィールドを[列]エリアへ
- [販売個数]フィールドを[値]エリアへ
- [店名]フィールドを[フィルター]エリアへ
串刺し計算と同じように各店舗の合計を求めることができます。
参考ピボットテーブルを作成しておくと、さまざまな切り口で集計と分析ができます。
-
ピボットテーブルとピボットグラフを同時に作成
ピボットテーブルの作成に慣れたなら、グラフも同時に作成してみませんか? ここでは、Excel2016で解説します。 サンプルのデータは以下の ...
ピボットテーブルにデータの更新を反映
Power Queryで作成していると、同じレイアウトのシートが増えたり、減ったりしても更新ボタンを押すだけです。もちろん、データが変更されても更新するだけです。
ここでは、ピボットテーブルの元データのなるブック[店舗別集計表.xlsx]の[福岡]シートを削除してみます。
テーブルで更新
読み込んだテーブルを基にピボットテーブルを作成しているので、まず、テーブルで更新します。
テーブル内でクリックして、[テーブルルール]-[デザイン]タブの[外部のテーブルデータ]グループにある[更新]ボタンをクリックして[更新]をクリックします。
参考ブック内で複数の外部データとの接続がある場合、[すべて更新]をクリックすると、ブック内のすべての外部データの接続が更新されます。特定のデータのみ更新したい場合は、[更新]をクリックしてください。
ポップヒントで分かりますが、ショートカットキーは以下のとおりです。
- すべて更新[Ctrl]+[Alt]+[F5]
- 更新[Alt]+[F5]
以下のように更新されます。
ピボットテーブルで更新
そして、ピボットテーブルで更新します。
Sheetを切り替えて、ピボットテーブル内をクリックします。
[ピボットテーブルツール]タブの[分析]タブにある[更新]をクリックします。
フィルターの[店名]を開いてみると、[福岡]が削除されていることが分かります。
以下の記事でも解説しています。
Power Queryエディターの編集
Power Queryエディターは再表示して編集することができます。
ただし、途中のステップを削除するとエラーになることがありますので注意してください。
もし、[クエリと接続]ウィンドウを閉じてしまった場合は、[データ]タブの[クエリと接続]グループにある[クエリと接続]をクリックします。
[クエリと接続]ウィンドウでクエリをダブルクリックします。
他の方法もあります。以下の記事で解説しています。
データの再取り込み
もし、別のシートにクエリを再読み込みしたい場合は、[データ]タブの[データの取得と変換]グループにある[既存の接続]をクリックします。
クエリからテーブルを読み込んだシートを削除してしまった場合も再読み込みができます。
既存の接続を使用してデータを取り込む
よく使用するソースから、データをインポートします。
[既存の接続]ウィンドウが表示されます。
[接続]タブの目的の接続を選択して、[開く]ボタンをクリックします。
[データのインポート]ダイアログボックスが表示されます。必要な項目を選択して[OK]ボタンをクリックします。
参考マトリックス表をテーブルに変換する方法は、以下の記事でも解説しています。
-
Power Queryでデータベースに整形(マトリックス表をテーブルに)
Power Query(パワークエリ)は、Excel2016とExcel2019には、標準でインストールされています。Excel2010とE ...
また、複数シートの結合については、以下の記事も参考になると思います。
-
Power Queryで同一ブック内の複数シートを結合するには
Power Queryを使って、同一ブック内の複数シートを結合する方法を紹介します。 同じレイアウトの表の場合は、簡単に結合できます。 結合 ...