【Excel】Power QueryのグループByで集計テーブルを作成する方法

日々の業務で大量のデータを手作業で集計することに時間を取られていませんか。特定の商品カテゴリごとの売上合計や、顧客ごとの購入回数などを素早く知りたい場面は多いものです。この記事では、ExcelのPower Queryに搭載されている「グループBy」機能を使い、複雑な集計作業を効率的に行い、要約されたテーブルを簡単に作成する方法を詳しく解説します。

Power QueryのグループBy機能を使えば、手動でのデータ整理や複雑な数式作成に費やす時間を大幅に削減できます。この記事を読み終えることで、ビジネスデータの集計と分析をより迅速に進めることが可能になります。

Power QueryのグループBy機能とは何か

Power QueryのグループBy機能は、指定した一つまたは複数の列の共通値に基づいて、データを集約し新しい集計テーブルを生成する強力な機能です。例えば、売上データの中から「商品名」を基準にグループ化し、「売上個数」の合計や「単価」の平均値を算出できます。これにより、膨大な生データから必要な情報を効率的に抽出できます。

この機能は、データの前処理と分析の効率化に不可欠です。手作業でフィルターや並べ替えを繰り返したり、SUMIFS関数やCOUNTIFS関数を多数組み合わせたりする手間を省けます。Power Queryで作成した集計クエリは再利用でき、元データが更新されてもワンクリックで集計結果を最新の状態に保てます。

グループBy機能の活用でできること

Power QueryのグループBy機能では、多様な集計が可能です。具体的な活用例をいくつか紹介します。

• 顧客ごとの購入履歴から、購入回数や購入金額の合計を算出する。
• 商品カテゴリごとの販売個数の合計や平均単価を求める。
• 日付ごとに、その日の最大売上、最小売上、販売された商品の種類をカウントする。
• 地域ごとに、達成された目標数や未達成の目標数を集計する。

これらの集計をPower Queryで行うことで、データの整合性を保ちながら、正確な分析基盤を構築できます。Excel for Microsoft 365, Excel 2019, Excel 2021のいずれのバージョンでも利用可能です。

Power QueryでグループBy集計テーブルを作成する手順

ここでは、Excelに用意されたサンプルデータを使って、Power QueryのグループBy機能で集計テーブルを作成する具体的な手順を解説します。今回は、商品の売上データから「カテゴリ」ごとの「売上合計」と「商品種類数」を算出する例を見ていきましょう。

1. 元データの準備とテーブル化
   集計したいデータがExcelワークシートに存在することを確認します。データが単純な範囲の場合は、あらかじめテーブルとして書式設定しておくことを推奨します。テーブル化するには、データ範囲内の任意のセルを選択し、「挿入」タブの「テーブル」をクリックします。
2. Power Queryエディターを起動する
   テーブル内の任意のセルを選択した状態で、「データ」タブの「データの取得と変換」グループにある「テーブルまたは範囲から」をクリックします。Power Queryエディターが新しいウィンドウで開きます。
3. グループBy機能を選択する
   Power Queryエディターが開いたら、「変換」タブをクリックします。「テーブル」グループの中に「グループ化」というボタンがあるので、それをクリックします。「グループ化」ダイアログボックスが表示されます。
4. 基本モードでグループ化の基準と集計を設定する
   今回は「カテゴリ」でグループ化し、「売上合計」と「商品種類数」を算出します。
   • グループ化の基準: 「グループ化の基準」ドロップダウンリストから「カテゴリ」を選択します。
   • 新しい列の追加1: 「新しい列名」に「売上合計」と入力します。「操作」ドロップダウンリストから「合計」を選択します。「列」ドロップダウンリストから「売上」を選択します。
   • 新しい列の追加2: 別の集計を追加するには、「集計の追加」ボタンをクリックします。「新しい列名」に「商品種類数」と入力します。「操作」ドロップダウンリストから「行のカウント(重複なし)」を選択します。「列」ドロップダウンリストから「商品名」を選択します。

   設定が完了したら「OK」をクリックします。

5. 詳細モードで複雑な集計を行う(参考)
   もし複数の列でグループ化したり、さらに複雑な集計が必要な場合は、「グループ化」ダイアログボックスの下部にある「詳細」オプションを選択します。「グループ化の基準」を複数追加したり、異なる集計方法を組み合わせて適用できます。
6. 集計結果をExcelワークシートに読み込む
   Power Queryエディターの左上にある「ファイル」タブをクリックし、「閉じて読み込む」の「閉じて読み込む」を選択します。Power Queryによって、集計された新しいテーブルがExcelの新しいワークシートに挿入されます。

Power QueryグループBy使用時の注意点と対処

Power QueryのグループBy機能は非常に便利ですが、使用中に予期しない結果やエラーに遭遇することもあります。ここでは、よくある注意点と、その場合の対処法を解説します。

意図しないグループ化結果になる場合

グループ化を行ったにもかかわらず、期待した数よりも多くのグループが作成されたり、同じはずの項目が別々に扱われたりすることがあります。

原因は、グループ化の基準に指定した列に「表記ゆれ」や「データ型の不一致」があるためです。例えば、「大阪」と「大阪府」のようにわずかな表記の違いがある場合、Power Queryはこれらを異なる値として認識します。また、数値がテキスト形式で保存されている場合も正しくグループ化できない原因になります。

1. 表記ゆれの修正: グループ化を行う前に、Power Queryエディターで表記ゆれを修正します。該当する列を選択し、「変換」タブの「値の置換」機能を使って統一したり、「重複の削除」や「空白の削除」も活用します。
2. データ型の統一: 該当する列のヘッダーにあるアイコンをクリックし、適切なデータ型(例: テキスト、整数など)に統一します。

集計方法が思った通りに適用されない場合

「合計」を選択したのにエラーが出たり、意図しない結果になったりする場合があります。

原因は、集計対象の列のデータ型が、選択した集計操作に適していないためです。例えば、テキスト形式の列に対して「合計」操作を適用しようとするとエラーが発生します。「平均」や「合計」などの算術操作は数値データにのみ適用できます。

1. データ型の確認と修正: 集計対象の列のデータ型を確認します。数値として扱いたい場合は、必ず「数値」または「10進数」などの数値型に設定し直します。
2. 適切な集計操作の選択: テキスト列に対しては「行のカウント」や「すべての行」など、データ型に合った集計操作を選びます。「行のカウント(重複なし)」は、特定の項目が何種類存在するかを知るのに便利です。

Power Queryが更新できない場合

一度作成した集計テーブルを更新しようとしたときに、「データソースエラー」などのメッセージが表示され、更新できないことがあります。

原因は、元データの場所が移動したり、ファイル名が変更されたりした場合に、Power Queryがデータソースを見つけられなくなるためです。また、元データの構造が大幅に変更され、Power Queryの適用ステップと合わなくなった場合もエラーの原因になります。

1. データソース設定の確認: Excelの「データ」タブから「クエリと接続」ウィンドウを開きます。該当するクエリを右クリックし、「データソース設定」を選択します。ここで、元データのパスが正しいか確認し、必要に応じて「パスの変更」で修正します。
2. Power Queryエディターでステップを修正: エラーが発生しているクエリを右クリックして「編集」を選択し、Power Queryエディターを開きます。「適用したステップ」ペインでエラーが表示されているステップを確認します。元データの変更に合わせて、列の名前変更や削除などのステップを修正または削除することで解決できます。

Power QueryグループByとピボットテーブルの機能比較

ExcelにはPower QueryのグループBy機能の他に、ピボットテーブルという強力な集計ツールも存在します。それぞれの特徴を理解し、用途に応じて使い分けることが重要です。

項目	Power Query グループBy	ピボットテーブル
主な用途	データ加工を伴う複雑な集計、定型的なデータクレンジング後の集計	手軽なデータ集計・分析、多角的な視点からのデータ探索
データの加工能力	M言語による柔軟なデータ変換・整形、欠損値処理、データ型変更、列の追加など	集計のみに特化、元データ加工は限定的
再利用性・自動化	作成したクエリは保存され、元データ更新時にワンクリックで再集計・更新が可能	元データ更新後にピボットテーブルの更新操作が必要
学習コスト	Power QueryエディターとM言語の基礎知識が必要で、やや学習コストが高い	直感的なドラッグアンドドロップ操作で、比較的簡単に習得できる
結果の出力形式	整形された集計テーブルが新しいワークシートに書き出される	ピボットテーブル形式で出力され、レイアウトや表示形式を柔軟に変更できる

まとめ

この記事では、ExcelのPower QueryのグループBy機能を使って、大量のビジネスデータを効率的に集計し、要約されたテーブルを作成する方法を解説しました。グループBy機能を使うことで、手動での集計作業から解放され、正確なデータ分析の基盤を構築できます。データの前処理における表記ゆれやデータ型の不一致といった問題への対処法も理解できたことでしょう。

Power QueryのグループByは、単純な集計だけでなく、詳細モードを使い複数のキーでグループ化したり、様々な集計関数を組み合わせたりすることも可能です。今後は、複数のデータソースを結合してグループByでクロス集計を行うなど、さらに応用的な活用にも挑戦してみてください。業務におけるデータ集計の時間を大幅に短縮し、より高度なデータ分析へとつなげることが可能になります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】