【Excel】Power Queryでグループ化後の合計が合わない時の確認ポイント

Power Queryでデータをグループ化して集計する操作は、Excel業務で頻繁に使われる便利な機能です。しかし、グループ化後に表示される合計値が、手計算や元データの合計と一致しないというトラブルに遭遇することがあります。この問題は、データ型の設定ミスやグループ化の条件忘れなど、いくつかの原因に絞られます。本記事では、グループ化後の合計が合わない原因を系統的に切り分け、具体的な確認手順と解決方法を詳しく解説します。

1. グループ化設定の基本を再確認する

最初に、Power Queryエディターで設定したグループ化の内容を確認します。グループ化の設定画面では、「グループ化する列」「新しい列名」「集計方法」を指定します。ここで設定を誤ると、期待する合計が得られません。

1-1. 集計方法の選択を確認する

グループ化の集計方法には「合計」「平均」「最大」「最小」「個別のカウント」などがあります。よくあるのは「合計」を選んだつもりが「カウント」になっているケースです。次の手順で確認してください。

1. Power Queryエディターで、該当のクエリを開きます。
2. 「ホーム」タブの「グループ化」をクリックし、設定ダイアログを開きます。
3. 「新しい列名」に表示されている集計方法が「合計」であることを確認します。
4. もし「合計」以外になっていれば、ドロップダウンから「合計」に変更します。
5. OKを押してクエリを更新し、結果が正しいか確認します。

1-2. グループ化の列が正しいか確認する

グループ化の対象となる列を間違えている場合、合計値が変になります。例えば、顧客名でグループ化するつもりが注文番号でグループ化してしまい、同じ顧客でも別グループとして扱われることがあります。グループ化列に指定した列名が意図通りか、元データと突き合わせて確認しましょう。

2. データ型の不一致をチェックする

Power Queryは各列にデータ型(数値、テキスト、日付など)を割り当てています。集計対象の列が数値型になっていないと、合計が正しく計算されません。例えば、見た目が数字でも「テキスト」型のセルがあると、合計は0または無視されます。

2-1. 列のデータ型を確認する方法

1. Power Queryエディターで対象の列のヘッダー左側にあるアイコンを確認します。「123」なら数値、「ABC」ならテキスト、「日付」アイコンなら日付です。
2. もしテキストアイコンが表示されていれば、その列を右クリックし「型の変更」→「整数」または「10進数」を選択します。
3. 型を変更した後、グループ化の結果が変わるか確認します。

2-2. データ型変換の失敗パターン

文字列として数値が入力されている場合、Power Queryは自動的に型を推測しますが、一部のセルに数値以外の文字(カンマや単位)が混ざっているとテキスト扱いになります。また、空のセルやエラー値があると合計に影響します。このような場合は、先に「置換」や「値の置き換え」でクリーニングしてから型を変更してください。

3. グループ化前のデータに重複や空白がないか確認する

グループ化は指定した列の値でデータをまとめるため、元データに重複や空白があると想定外のグループが作られます。特に空白セルは別のグループとして扱われるため、合計が分散してしまいます。

3-1. 空白を特定して対処する

1. Power Queryエディターで、グループ化に使う列を選択し、「ホーム」タブの「列の統計」→「値の分布」をクリックします。
2. 表示された分布で「(空)」のカウントが0でなければ、空白レコードが存在します。
3. 空白を埋めるか、除外する必要があります。例えば、空白を「未分類」などの文字に置き換えるか、フィルターで空白行を削除します。
4. 空白行を削除する場合は、「ホーム」タブの「行の削除」→「空の行の削除」を実行します。

3-2. 重複データの影響

グループ化は同一キーを持つ行をまとめるため、元データに重複行があっても合計は正しくなります。しかし、グループ化する列自体に重複があるのは当然です。問題は、意図しない細かいグループができていないかです。例えば「顧客名」列に「佐藤」と「佐藤　」(スペース有)が混在すると別グループになります。先頭や末尾のスペースは削除しておきましょう。

4. 複数列でグループ化する際の落とし穴

Power Queryでは複数の列をキーとしてグループ化できます。キーの組み合わせによって集計結果が変わります。例えば「地域」と「商品カテゴリ」でグループ化すると、それぞれの組み合わせごとに合計が計算されます。もし単一列でのグループ化結果と比較して合計が合わないと感じた場合、キーの選択が適切か見直しましょう。

4-1. キーの順序は関係ない

複数列でグループ化する場合、キーの指定順序は結果に影響しません。ただし、出力されるテーブルの列順は指定順になります。

4-2. 集計結果の検算方法

1. Excelシート上で、元データをグループ化列で並べ替え、手動で小計を計算します。
2. Power Queryの結果と各グループの合計が一致するか比較します。
3. 不一致があれば、そのグループの元データを詳しく調べ、データ型や空白、スペースの有無を確認します。

5. カスタム集計(集計方法の選択)による影響

Power Queryのグループ化では「合計」の他に「平均」「中央値」「標準偏差」など多様な集計方法が選べます。集計方法を間違えると意図しない値になります。また、「すべての行」を選択するとグループ化せずに集計列を追加する動作になります。誤って「すべての行」を選んでいないか確認しましょう。

5-1. よくある誤選択パターン

• 「値の集計」で「合計」を選ぶべきところを「カウント」にしてしまい、件数が表示される。
• 「すべての行」を選んでしまい、テーブルが入れ子になって画面が崩れる。
• 「個別のカウント」を選び、重複を除いたカウントが表示される。

6. 失敗パターンとその対処法(比較表)

失敗パターン	原因	確認ポイント	対処法
合計が0になる	集計列がテキスト型	列ヘッダーのアイコン	型を数値に変更
合計が少ない	グループキーに空白やスペース	値の分布で空白確認	空白を埋めるか削除
グループ数が多すぎる	細かい違い(スペース、大文字小文字)	テキスト整形の有無	書式を統一(TRIM、UPPER)
合計が大きい	重複行が含まれている	行の重複チェック	事前に重複行を削除

7. よくある質問(FAQ)

Q1. グループ化後に表示される「カウント」が合計より多いのはなぜ?

集計方法が「合計」ではなく「カウント」に設定されている可能性があります。設定画面で「合計」に変更してください。

Q2. グループ化した列に「(空)」というグループが現れる

グループ化に使った列に空白セルがあると、その空白が一つのグループとして扱われます。空白セルを特定の値(「不明」など)に置き換えるか、フィルターで除外してください。

Q3. グループ化の合計とピボットテーブルの合計が合わない

Power Queryのグループ化結果とExcelのピボットテーブルで集計した結果が異なる場合、元データの読み込み範囲やフィルターの有無を確認してください。また、ピボットテーブルでは集計方法が「合計」以外になっていないか確認しましょう。

8. まとめ

Power Queryでグループ化後の合計が合わない原因は、集計方法の誤り、データ型の不一致、空白やスペースの混入、グループキーの認識違いに集約されます。最初にグループ化設定の基本を確認し、次にデータ型をチェック、その後空白や重複を排除することで、ほとんどの問題は解決します。本記事で紹介した確認手順を順に実行すれば、トラブルの原因を効率的に特定できるでしょう。

📊

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

🏆

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