【Excel】パワークエリで大量データを軽くする｜グループ化で行数を減らして集計する

以前使っていた集計のExcelは、ローデータをそのまま貼り付けて、SUMIFSで集計していました。データは1年分で約20万行あり、店舗を切り替えるたびに、ステータスバーに「計算中」と出て止まっていました。

そこで、パワークエリでローデータを店舗・月・カテゴリごとにまとめてから集計するようにしたところ、「計算中」で止まらなくなりました。

この記事では、パワークエリの「グループ化」で行数を減らして、集計を軽くする方法を紹介します。なお、この記事の表や手順は説明のために作ったもので、実際に使っているExcelとは作りが違います。例の数字も架空です。

例として使うデータ

「売上データ」というテーブルに、このようなローデータが入っているとします。

店舗・月・カテゴリごとの金額の合計を出したい場合、商品や数量、担当者の列は使いません。同じ店舗・月・カテゴリの行が何行もあるので、まとめれば行数を大きく減らせます。

ローデータは、あらかじめテーブルにしておきます。テーブルにする方法は、こちらの記事で紹介しています。

売上データのテーブルの中のセルを選んで、［データ］タブ→［テーブルまたは範囲から］を押します。パワークエリの画面（Power Queryエディター）が開きます。

日付のままでは月ごとにまとめられないので、先に月の列を作ります。

日付の列を選んで、［列の追加］タブ→［日付］→［月］→［月］を選びます。右端に「月」の列が追加され、10、11のように月が入ります。

次に、集計に使わない列を削除します。この例では、日付・商品・数量・担当者の列です。残すのは、店舗・カテゴリ・金額・月の4列です。

削除するときは、残す列をCtrlキーを押しながら選んで、右クリック→［他の列の削除］を選ぶのがおすすめです。消す列を選んで削除すると、ローデータに後から列が増えたときに、その列は残ってしまいます。［他の列の削除］なら、選んだ列だけを残すので、万が一列が増えても、パワークエリを直す必要がありません。

集計に使わない行がある場合は、列の見出しの▼から、空白の行や、集計に入れない値のチェックを外します。

［ホーム］タブ→［グループ化］を押して、「詳細設定」を選びます。「基本」のままだと、1つの列でしかグループ化できません。

［OK］を押すと、店舗・月・カテゴリごとに1行にまとまります。

7行が4行になりました。実際のデータでは、同じ店舗・月・カテゴリの行がもっとたくさんあるので、行数は大きく減ります。

後でXLOOKUPで検索しやすいように、店舗・月・カテゴリをつなげた検索キーの列を作ります。

［列の追加］タブ→［カスタム列］を押して、新しい列名を「検索キー」にし、次の式を入れます。

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

月は数値なので、Text.Fromで文字に変えてからつなげています。そのままつなぐとエラーになります。

追加した列は右端に入るので、列の見出しを右クリック→［移動］→［先頭へ］で、A列に移します。