パワークエリの［グループ化］を使うと、売上明細を地域別・商品別の合計へまとめるような処理を、更新可能な手順として保存できます。ExcelのSUMIFやピボットテーブルと違うのは、元データへ接続し、列の型や整形を行い、集計してからワークシートまたはデータモデルへ読み込む一連の変換を再実行できる点です。ただし、表記揺れ、型、空白、重複を確認せずにグループ化すると、同じ地域が別グループになったり、売上が二重計上されたりします。操作より先に集計粒度と検算方法を決めます。
グループ化で決める三つの要素
- グループキー：地域、商品、年月など、同じまとまりと判断する列。
- 集計対象：数量、売上金額、処理時間など、計算する列。
- 集計方法：合計、平均、最小、最大、行数、すべての行など。
たとえば「地域別売上」と「地域・商品別売上」は粒度が違います。後者は地域と商品を両方キーにします。「同じ注文番号が複数行ある」データも、明細として正しければ重複ではありません。注文単位、商品明細単位、顧客単位のどこへまとめるかを一文で書ける状態にします。
練習用データを用意する
例として、注文日、地域、商品、数量、売上金額の五列を持つ明細表を使います。一行目を一意な見出しにし、途中の空白行、結合セル、小計行、総計行を含めません。元表を選び、Ctrl+TでExcelテーブルへ変換してから、［データ］、［テーブルまたは範囲から］でPower Queryエディターを開きます。元表には直接手を加えず、クエリの変換として記録します。
集計前に列の型を確定する
列見出し左のアイコンで、注文日は日付、地域と商品はテキスト、数量は整数、売上金額は固定小数または適切な数値型になっているか確認します。Microsoftの公式説明では、Power Queryのデータ型は列内の値を分類し、型に応じた変換と計算を提供します。CSVやExcelなどの非構造化ソースでは自動推測が入るため、結果をそのまま信頼せず明示的に設定します。
売上金額がテキストのままだと合計できない、日付が米国式として誤解釈される、商品コードの先頭0が消えるといった問題があります。エラーへ変わった値をフィルターして確認し、ロケールを使った型変換が必要なら元データの仕様に合わせます。エラーを0へ置換して隠す前に原因を直します。
グループキーを整える
「東京」「東京 」「とうきょう」は別の値です。必要に応じて作業用列で前後空白のトリミング、制御文字のクリーニング、大文字・小文字の統一、正式名称への置換を行います。変換規則を明文化し、似ているという理由だけで別の顧客名や商品名を統合しません。Microsoftの重複に関する公式説明は、Power Queryがテキストの大文字・小文字を区別することを注意点として示しています。
空白とnullも確認します。地域がnullの明細を削除するのか、「未設定」として一つのグループへ残すのかは業務ルールです。削除すると合計金額が減るため、除外件数と除外金額を記録します。
一列でグループ化する基本手順
- Power Queryエディターで［ホーム］または［変換］の［グループ化］を選びます。
- ダイアログで［基本］を選び、グループ化する列を［地域］にします。
- 新しい列名を［売上合計］にします。
- 操作を［合計］、対象列を［売上金額］にします。
- ［OK］を選び、地域ごとに一行になったことを確認します。
適用したステップには通常［グループ化された行］が追加されます。ステップ名を「地域別売上」のように変更すると、後から意図を追いやすくなります。出力の列名には単位や意味を含め、「合計」だけの曖昧な名前にしません。
複数列でグループ化する
- ［グループ化］を開き、［詳細設定］へ切り替えます。
- 最初のキーに［地域］を選びます。
- ［グループ化の追加］で［商品］を加えます。
- 集計として［売上合計］、［合計］、［売上金額］を設定します。
- 必要なら別の集計を追加し、［明細行数］、［行数］を設定します。
- ［OK］を選び、地域・商品ごとの組み合わせが一行になったことを確認します。
Microsoft Learnの公式手順も、複数キーは［詳細設定］で［グループ化の追加］を使う方法を示しています。集計列は一つに限らず、合計と行数を同時に作れます。ただしExcel、Power BI Desktop、Power Query Onlineで表示される操作が一部異なり、公式資料では「個別値のカウント」と「パーセンタイル」がPower Query Online限定とされています。利用中の画面にある操作を確認します。
集計方法を正しく選ぶ
- 合計：売上金額や数量など、足し合わせる意味がある値。
- 平均：一行当たり金額など。重み付き平均が必要なら単純平均にしない。
- 最小・最大：最初・最後の日付や値の範囲確認。
- 行数：そのグループに入った明細行の件数。
- すべての行：各グループの明細を入れ子のテーブルとして保持する。
顧客別単価の平均を求めるとき、数量の違う行を単純平均すると期待と異なる場合があります。「売上合計÷数量合計」が必要なら、それぞれを合計してからカスタム列で割ります。分母が0またはnullのケースを先に決めます。
すべての行を使う
［すべての行］は、グループごとの元明細を［Table］値として保持します。各地域の最大売上行を取り出す、最新注文を選ぶなど、単純な合計以上の処理に使えます。公式例では、［すべての行］で保持したテーブルから最大値の行を抽出しています。便利な一方、入れ子テーブルを多数保持すると処理が重くなり、展開操作も複雑になります。基本集計で足りるなら合計や最大を使います。
重複行を先に削除しない
同じ注文番号、商品、数量、金額の行が二つあっても、正しい二回の取引か、取り込み重複かはデータだけでは判断できません。［重複の削除］を集計前に入れると、正しい明細まで消す危険があります。取引ID、明細番号、更新日時、ソースファイル名など、業務上の一意キーで判定し、重複候補の件数と金額を別に確認します。
Microsoftの公式説明は、重複削除でどの行が残るかに保証がないことも注意しています。「並べ替えて先頭を残す」だけで最新版を選べると決めつけず、最新版の選択規則を明示します。Power Queryでは集計や重複削除を通じた並び順も保証されないため、最終的な順序が必要なら変換の最後で並べ替えます。
集計前後を検算する
- 集計前の総行数と、グループ別［明細行数］の合計が一致する。
- 集計前の売上金額合計と、グループ別［売上合計］の合計が一致する。
- null、エラー、負数、0、異常に大きい値の件数が説明できる。
- 既知の地域・商品について、元明細を手計算した結果と一致する。
- グループ数が想定範囲で、末尾空白や表記揺れの別グループがない。
Power Queryエディターのプレビューだけでなく、［閉じて読み込む］後の出力でも合計を確認します。プレビューは全件を常に表示しているわけではありません。重要な集計では、元システムの既知総額や承認済み帳票とも照合します。
更新に強い手順へ整える
元データが増えたときに更新できるよう、固定セル範囲ではなくExcelテーブルまたは適切なコネクターを使います。Microsoftのベストプラクティスでは、用途に合うコネクターを選び、不要な行を早い段階で絞り、コストの高い処理を適切な順序へ置くことが推奨されています。ただしフィルターを早く入れる場合も、除外条件が翌月のデータに適用してよいか確認します。
列名変更や列削除で更新エラーが起きるため、元データの列契約を管理します。クエリ名、各ステップ名、グループキー、集計対象、除外条件、型、ロケールを記録します。パスや資格情報を他人へ渡すときは機密情報を含めません。
読み込みと再利用
処理が正しければ［ホーム］、［閉じて読み込む］または［閉じて次に読み込む］を選び、ワークシートまたはデータモデルへ読み込みます。Microsoft Supportの現行手順では、既存クエリは［データ］、［クエリと接続］から編集や読み込み先変更ができます。読み込み結果のセルを直接直しても次回更新で戻るため、修正は元データまたはクエリのステップへ反映します。
完了条件
グループ化の完了は、地域ごとの行が表示された時点ではありません。集計粒度が一文で説明でき、キーの表記とnull方針が決まり、列型が正しく、重複処理に根拠があり、集計前後の件数と合計が一致し、代表ケースの手計算とも合い、元データ追加後の更新でも同じ結果を再現できる状態です。ここまで確認すれば、毎月の手作業集計を安全に更新可能なクエリへ変えられます。
公式情報・参考資料
- Microsoft Learn：Power Queryで行をグループ化・集計する
- Microsoft Learn：Power Queryのデータ型
- Microsoft Learn：Power Queryで重複値を扱う
- Microsoft Learn：Power Queryのベストプラクティス
- Microsoft Support：Excelでクエリを作成・読み込み・編集する
- Microsoft Learn：Power Queryの一般的な作成上の問題
コメント