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

Power Queryを使って、複数のクロス集計表を結合して1つのピボットテーブルに変換することができます。

一度、ピボットテーブルを作成すると、シートが増えたり、減ったりしても更新ボタンをクリックするだけです。

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

Excel2019 バージョン1908（ビルド11920.20300）での解説です。

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

Excel の Power Query について - Microsoft サポート（Microsoft）

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

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

Power Queryエディターで編集

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

不要な列の削除

不要な列を削除します。

［Item］［Kind］［Hidden］の列を選択して、右クリックします。［Shift］キーを使用すると、連続した列を選択できます。

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

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

Data列の展開

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

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

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

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

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

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

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

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

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

タイトル行を非表示

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

以下のようになります。

列のピボット解除

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

［1月］から［4月］の列までを選択します。

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

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

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

列の名前の変更

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

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

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

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

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

データ型の変更

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

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

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

閉じて次に読み込む

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

［ホーム］タブの［閉じて読み込む］の下方（文字部分）をクリックして、［閉じて次に読み込む...］をクリックします。

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

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

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

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

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

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

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

Power Queryについて学習するなら、以下のテキストがお勧めです。

ピボットテーブルを作成

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

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

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

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

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

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

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

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

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

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

テーブルで更新

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

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

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

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

• すべて更新［Ctrl］+［Alt］+［F5］
• 更新［Alt］+［F5］

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

ピボットテーブルで更新

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

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

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

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

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

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

Power Queryエディターの編集

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

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

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

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

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

Power Queryエディターの再表示

データの再取り込み

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

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

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

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

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

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

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