【Excel】パワークエリで店舗ごとの集計の下に全店の合計を追加する｜参照とクエリの追加

前の記事では、パワークエリのグループ化で、ローデータを店舗・月・カテゴリごとにまとめました。

この記事ではその続きとして、店舗ごとの集計の下に、全店の合計の行を追加します。私の資料では、店舗を選ぶプルダウンに「合計」も入れていて、「合計」を選ぶと全店の数字が出るようにしています。店舗も全店も、同じXLOOKUPの式で出せます。

なお、この記事の表や手順は説明のために作ったもので、実際に使っているExcelとは作りが違います。例の数字も架空です。

全体の流れ

クエリを3つ使います。

シートに書き出すのは最後の「集計」だけです。「店舗別」と「全店合計」は、「接続の作成のみ」にして、シートには書き出しません。

前の記事で作ったクエリを「店舗別」という名前にして使います。中身はこのようになっています。

前の記事では、このクエリでA列に検索キーを作りました。今回は、検索キーは最後の「集計」のクエリで作ります。

店舗別で検索キーを作っていると、それをもとにする全店合計にも、店舗名が入ったままの検索キーが残ってしまうためです。前の記事どおりに作っている場合は、Power Queryエディターの右側の「適用したステップ」から、検索キーを追加したステップと列を移動したステップを、×で削除しておいてください。

［データ］タブ→［クエリと接続］で、右側にクエリの一覧を出します。「店舗別」を右クリック→［読み込み先］を選び、「接続の作成のみ」にして［OK］を押します。シートに書き出していたテーブルは消えますが、クエリは残ります。

前の記事で書き出したテーブルをXLOOKUPで見ている場合は、テーブルが消えると式が #REF! になります。このあと「集計」をテーブルで書き出したら、式の参照先を集計のテーブルに変えてください。

クエリの一覧で「店舗別」を右クリック→［参照］を選びます。店舗別の結果をもとにした新しいクエリができるので、名前を「全店合計」に変えます。

参照しておけば、ローデータを更新したときも、店舗別と全店合計が一緒に更新されます。

店舗の列を選んで右クリック→［削除］で削除します。

次に、［ホーム］タブ→［グループ化］→「詳細設定」で、「月」と「カテゴリ」でグループ化し、金額を合計します。店舗の区別がなくなるので、全店を合わせた金額になります。

［列の追加］タブ→［カスタム列］を押して、新しい列名を「店舗」、式を次のようにします。

"合計"

すべての行の店舗が「合計」になります。

列名は、必ず店舗別と同じ「店舗」にしてください。このあとの「クエリの追加」は列名で列を合わせるので、名前が違うと別の列として扱われます。列の並びが店舗別と違っていても、名前が同じなら正しくつながります。

できたら、［ホーム］タブ→［閉じて読み込む］の▼→［閉じて次に読み込む］で、「接続の作成のみ」を選びます。

Excelの画面に戻ったら、［データ］タブ→［データの取得］→［クエリの結合］→［追加］を選びます。「2つのテーブル」のまま、最初のテーブルを「店舗別」、2番目のテーブルを「全店合計」にして［OK］を押します。

新しいクエリができるので、名前を「集計」に変えます。店舗別の行の下に、全店合計の行が縦につながります。

「クエリのマージ」という似た名前の機能もありますが、こちらは列を横に足す機能です。行を縦に足すときは「クエリの追加」を使います。

前の記事と同じように、［列の追加］タブ→［カスタム列］で「検索キー」の列を作り、先頭に移動します。

[店舗] & "_" & Text.From([月]) & "_" & [カテゴリ]

［ホーム］タブ→［閉じて読み込む］を押すと、「集計」だけがテーブルでシートに書き出されます。