【Excel】ピボットテーブルで集計値が合わない時の重複データ確認

ピボットテーブルは売上や在庫の集計に便利ですが、「なぜか合計が正しくない」「元データと数字が合わない」と感じたことはありませんか。多くの場合、その原因は元データに含まれる重複レコードにあります。重複データが存在すると、ピボットテーブルは単純に全ての行を集計するため、本来1件としてカウントすべきデータが複数回加算されます。本記事では、ピボットテーブルの集計値が合わない原因として重複データに焦点を当て、検出方法から対処手順までを具体的に解説します。

【要点】この記事で確認すること

• 最初に見る場所: ピボットテーブルの元データ(テーブルまたは範囲)を開き、条件付き書式や関数を使って重複行を可視化します。
• 切り分けの軸: 重複の種類(完全一致か一部重複か)、データソースの管理方法(ローカルファイルか共有サーバーか)、ピボットテーブルの更新タイミングなどを確認します。
• 注意点: 会社PCで共有ファイルを扱う場合、重複削除は元データに影響を与えるため、必ずバックアップを取ってから作業してください。また、管理者が設定したデータ入力規則で重複が許可されている可能性もあるため、勝手に削除しないでください。

ピボットテーブルの集計値が合わない原因は重複だけではありません。まずは可能性を整理し、適切な切り分けを行いましょう。以下に主な原因を挙げます。

• 元データに重複行が存在する: 同じレコードが複数回記録されていると、集計値が実際より大きくなります。特に売上伝票や顧客マスタで発生しやすい問題です。
• 集計フィールドの設定ミス: 「合計」ではなく「カウント」や「平均」が選択されている場合、意図した集計値になりません。フィールド設定を確認してください。
• ピボットテーブルの更新漏れ: 元データを変更した後にピボットテーブルを更新していないと、古いデータで集計が行われます。「すべて更新」を実行しましょう。
• 空白セルやエラーの混入: 元データに空白や#N/Aが含まれると、集計が正しく行われないことがあります。事前にクリーニングが必要です。

これらの原因の中でも、特に重複データは発見が難しく、影響範囲が広いため注意が必要です。次の章では、重複が集計値に与える影響を具体例で示します。

例えば、以下のような売上データを考えます。1行目と2行目が同じ受注番号「A001」で、金額も同じです。これが重複です。

このデータをピボットテーブルで「受注番号」を行ラベル、「金額」の合計を値にすると、A001の合計は200と表示されます。しかし正しくは100です(重複がなければ)。このように集計値が実際より大きくなってしまうのです。

重複が発生する原因としては、以下のようなシナリオが考えられます。

• データ入力時に同じ伝票を2回登録してしまった。
• システム連携の不具合で同一データが重複出力された。
• 複数人で手入力した際に、重複をチェックせずに追加した。

重複を放置すると、報告先に誤った数値を伝えてしまうリスクがあります。早急に確認し、適切に対処する必要があります。

重複データを発見するには、いくつかの方法があります。ここでは3つの代表的な手法を紹介します。それぞれの特徴を比較表にまとめました。

3-1. 条件付き書式を使った視覚的な確認

3-2. COUNTIF関数で重複数をカウント

3-3. データタブの「重複の削除」機能

重複を確認したら、次はどう処理するかを決めます。状況に応じて以下の方法を選んでください。

1. 元データから重複行を削除する: 最も直接的な方法です。上記の「重複の削除」機能を使うか、フィルターで重複行を抽出して手動で削除します。ただし、共有ファイルの場合は他のユーザーに影響が出るため、管理者やチームと相談してください。
2. ピボットテーブルで集計方法を変更する: 重複を許容したまま、集計値を調整する方法です。例えば、「合計」の代わりに「平均」を使うか、特定のフィールドを「個別カウント」に変更します。ピボットテーブルの値フィールド設定で「計算の種類」を変更できます。ただし「個別カウント」はデータモデルを使用する必要があり、通常のピボットテーブルでは利用できません。
3. 元データにID列を追加して一意にする: 重複している行に連番を振るなどして、見かけ上ユニークにします。これによりピボットテーブルが各行を別々に集計しなくなり、重複の影響を回避できます。
4. パワークエリを使って重複を除外する: データタブの「テーブル範囲から取得」でパワークエリを開き、「行の削除」→「重複の削除」を適用します。これにより元データを変更せずに、読み込み時に重複を排除できます。共有ファイルに影響を与えずに済むため、安全です。
5. データソースを変更する: もし定期的に重複が発生するのであれば、元のデータソース(システム出力など)の設定を見直す必要があります。管理者に報告し、重複が発生しない仕組みを依頼してください。

重複以外の原因を見逃さないために、ピボットテーブル自体の設定も確認しましょう。以下はよくあるミスです。

• 集計フィールドの種類を確認: 値フィールドの設定で「合計」ではなく「カウント」になっていないか確認します。数値項目なのにカウントになっていると、件数が表示されます。
• フィルターやスライサーの影響: フィルターがかかっていると範囲が限定されます。すべてのデータを表示する設定にしてみてください。
• ピボットテーブルオプション: 「合計と集計」の設定で「行の集計を表示する」がオフになっていないか確認します。また、「データ」タブの「セルを編集可能にする」などの設定も影響する場合があります。
• データソースの範囲変更: 元データに行を追加した場合、ピボットテーブルのデータソース範囲が自動拡張されていないと、新しい行が集計に含まれません。「ピボットテーブル分析」→「データソースの変更」で範囲を確認し、テーブルとして定義することをおすすめします。

会社の共有ドライブやTeams上でピボットテーブルを使っている場合、重複データをむやみに削除できないケースがあります。以下の点を管理者に確認してください。

• 重複が意図的に許可されていないか: システムの仕様上、同じ伝票番号でも明細行が分かれている場合、見かけ上同じデータに見えることがあります。データの意味を確認しましょう。
• アクセス権限: 自分が編集権限を持っているか確認します。編集権限がなければ、重複削除は管理者に依頼する必要があります。
• バックアップの有無: 重複削除は元データを変更する操作です。事前にバックアップが取られているか、または自分でバックアップを取ってから作業するよう伝えてください。
• データ入力ルールの整備: 重複が頻発するなら、入力時に重複チェックを行うマクロや、データの一意制約を設定することを提案しましょう。

Q1: 重複を削除したのにピボットテーブルが更新されません。
A1: ピボットテーブルを右クリックして「更新」を実行するか、リボンの「データ」→「すべて更新」をクリックしてください。それでも反映されない場合、データソースの範囲が変化していないか確認します。テーブル形式にしておくと自動反映されます。

Q2: 重複が多くてどれを残すべきか判断できません。
A2: 重複行を調べる際に、COUNTIF関数で出現回数を表示し、さらに各行の作成日時やIDなどで比較します。通常は最も古い行や最新の行を残すことが多いです。業務ルールに従ってください。

Q3: パワークエリを使うと元データは変更されないとのことですが、具体的な手順を教えてください。
A3: 元データの範囲を選択し、[データ]タブ→[テーブル範囲から取得]をクリックします。パワークエリエディターが開いたら、[ホーム]タブ→[行の削除]→[重複の削除]を選択します。適用したら[閉じて読み込む]でシートに結果を出力します。元データは影響を受けません。

Q4: ピボットテーブル内で直接「重複を除外」する機能はありますか?
A4: 標準機能にはありません。ただし、データモデルを使用してピボットテーブルを作成すると、「個別カウント」が利用できます。ただし少し複雑になるため、初めは重複を元データ側で除去することをおすすめします。

ピボットテーブルの集計値が合わない原因として、重複データは頻繁に発生する問題です。まずは条件付き書式やCOUNTIF関数で重複を特定し、業務フローに合わせて適切に処理しましょう。会社の共有ファイルを扱う場合は、管理者と相談しながら安全に対応することが大切です。ピボットテーブルの設定自体も定期的に見直し、正確な集計を維持してください。