ピボットテーブルの総計が、元データの合計と合わない。金額を入れたのに、件数が出る。合計が0になる。
総計が元データと合わないときに最初に確認したいのは、元データの範囲と、集計方法が「合計」になっているかです。ずれの原因は、設定・元データ・行と列のフィールドの3か所に分けて探せます。
この記事でわかること
- ピボットテーブルの合計が合わない7つの原因
- 起きている問題から原因をしぼる確認の順番
- 確認の順番を決めると業務の何が良くなるか
結論：合わない原因は「設定」「元データ」「フィールド」の3か所に分けて探す
ピボットテーブルの合計が合わないときは、設定・元データ・行と列のフィールドの3か所を順に見ると、原因をしぼれます。
ピボットテーブルは、表の行を項目ごとに自動で集計するExcelの機能です。行・列・値のエリアに、フィールド(元データの列)を入れて使います。
合計が合わないときに見る場所は、次の3つです。
- 設定：元データの範囲・更新・フィルター・集計方法
- 元データ：文字列の数値・重複行
- フィールド：行・列に入れた列
どれも、エラーは出ません。それらしい合計が出るので、見ただけでは気づきにくいずれです。
ピボットテーブルの作り方や、基本の操作から確認したい場合は、次の記事が入口になります。
まずここから確認：起きている問題から、7つの原因のどれかをしぼる
起きている問題を見れば、7つの原因のうち最初に疑うものが決まります。
データをピボットして集計が合わないとき、自分がまず確かめるのは、どの列を軸にピボットしたかです。軸にする列は1列だけのこともあれば、2列、3列と入れることもあります。その列の選び方が間違っていないかを見ます。
もう1つ気をつけているのは、ピボットすることによって重複される件数です。
Excelのピボットテーブルでは、前者が下の表の7「行・列のフィールド」、後者が6「重複行」に当たります。
| 起きている問題 | 疑う原因 | 最初に見る場所 |
|---|---|---|
| 追加した行が入らない | 1. 元データの範囲 | データ ソースの変更 |
| 元データを直しても変わらない | 2. 更新 | 右クリックの「更新」 |
| 合計が元データより小さい | 3. フィルター | 矢印のアイコン・スライサー |
| 金額なのに件数が出る | 4. 集計方法 | 値フィールドの設定 |
| 合計が0・小さい | 5. 文字列の数値 | セル左上の緑の三角 |
| 合計・件数が大きい | 6. 重複行 | 注文IDごとの行数 |
| 比べたい資料と行が合わない | 7. 行・列のフィールド | 行・列のエリア |
比べたい資料(前月の報告書や、ほかの人が作った集計表など)と、行ごとの数字が合わないときは、7から見ます。
どの問題か分からないときは、表の上から順に見ます。1〜4は設定の確認で、元データに手を入れずに確かめられます。
原因と直し方：設定の4つと、元データ・フィールドの3つ
7つの原因は、どれも「どこで直すか」が決まっています。設定で直すものと、元データで直すものを分けて考えます。
原因1：元データの範囲に、追加した行が入っていない
問題と原因：ピボットテーブルは、作ったときに指定したセル範囲を集計します。範囲の下に行を足しても、範囲を広げないと集計に入りません。
確認方法：ピボットテーブル内を選び、「ピボットテーブル分析」タブの「データ ソースの変更」を開きます。範囲の最後の行が、元データの最後の行と同じかを見ます。
どう直すか：範囲を広げます。元データを「テーブル」に変換しておくと、更新したときに追加した行が自動で含まれます。毎月行が増える表なら、テーブルにしておくと範囲の手直しがいりません。
原因2：元データを直したのに、集計が変わらない
問題と原因：元データの数字を直しても、ピボットテーブルが更新されるまで、集計は前のままです。
Excelのバージョンによっては、元データの変更を自動で反映する「自動更新」の設定があります。自動更新がオフのときや、この設定がないバージョンでは、手で更新が必要です。
確認方法：ピボットテーブル内を右クリックし、「更新」を選んで数字が変わるかを見ます。ピボットテーブルが複数あるときは「すべて更新」を使います。
どう直すか：「ピボットテーブル分析」タブの「オプション」から「データ」タブを開き、「ファイルを開くときにデータを更新する」をオンにします。ファイルを開くたびに更新されます。
原因3：フィルター・スライサーで、一部の項目が外れている
問題と原因：フィルターやスライサー(ボタンで絞り込む部品)で絞ると、選んだ項目だけが表示されます。外れた項目の分、総計も小さくなります。
前に使った人が付けたフィルターが、そのまま残っていることもあります。
確認方法：行ラベル・列ラベルの矢印を見ます。フィルターが効いていると、矢印のアイコンが変わります。フィルターのエリアと、スライサーの選択も見ます。
どう直すか：「フィルターのクリア」で解除し、総計が元データの合計と合うかを比べます。絞り込みが必要なら、何で絞ったかを表の近くに書いておきます。
原因4：集計方法が「個数」になっている(合計にならない)
問題と原因：値のエリアに入れたフィールドは、数値だけなら「合計」で集計されます。文字列や空白が混じると、既定で「個数」になります。金額の列に1つでも文字や空白があると、件数が出ます。
確認方法：値のエリアのフィールド名が「個数 / 金額」のようになっていないかを見ます。
どう直すか：フィールドの矢印から「値フィールドの設定」を開き、「集計方法」で「合計」を選びます。
ただし、個数になった原因の文字や空白は、元データに残っています。原因5で中身を確かめます。
原因5：数値が文字列で入っている(合計が0・小さい)
問題と原因：CSVの貼り付けなどで、数字が文字列として入ることがあります。見た目は数字でも、計算では数値として扱われません。集計方法を合計にしても、文字列の分は足されず、合計が0や小さい値になることがあります。
確認方法：セルの左上の緑の三角(エラー インジケーター)を探します。
列全体を確かめるなら、COUNT関数とCOUNTA関数の結果を比べます。COUNTは数値のセルだけ、COUNTAは空白以外のセルを数えます。2つの差が、文字列などで入っているセルの数です。
どう直すか：セルを選び、エラー インジケーターから「数値に変換」を選びます。VALUE関数で数値にした列を作る方法もあります。直したら、ピボットテーブルを更新します。
原因6：元データに重複行がある(合計・件数が大きい)
問題と原因：同じ行が二重に入っていると、合計も件数も二重に数えます。
1件の注文が、明細で複数行に分かれている場合も注意します。「個数」は注文の数ではなく、行の数を数えます。以下は説明のための架空の例です。
| 注文ID | 商品 | 金額 |
|---|---|---|
| A001 | ペン | 300 |
| A001 | ノート | 500 |
| A002 | 消しゴム | 100 |
A001は1件の注文ですが、ペンとノートの2行に分かれています。そのため注文IDの個数は3になりますが、注文は2件です。
確認方法：注文IDなど、1件を表す列を行のエリアに入れます。同じ列を値のエリアにも入れて「個数」にします。2以上のIDが、複数行あるIDです。
どう直すか：二重に取り込んだ行なら、元データから外します。明細で分かれた行なら正しいデータなので、消さずに「何を1件と数えるか」を決め直します。
原因7：行・列に入れたフィールドが、比べたい資料と違う
問題と原因：比べたい資料は部署別なのに、担当者のフィールドを入れている。2つ目・3つ目のフィールドを入れて、行が細かく分かれている。こうした場合、総計が合っていても、行ごとの数字が資料と合いません。
表記ゆれも同じです。「営業1部」と、末尾に空白がある「営業1部 」は、別の項目として別の行になります。
確認方法：行・列のエリアに入れたフィールドを、比べたい資料の切り口と1つずつ照らし合わせます。行ラベルの一覧に、似た名前が並んでいないかも見ます。
どう直すか：フィールドを、比べたい単位に入れ替えます。表記ゆれは元データの側で名前をそろえ、ピボットテーブルを更新します。
現場目線の注意点：ずれをピボットテーブルの外で合わせない
ずれた数字を手で調整して合わせると、原因が元データに残り、翌月も同じ調整が要ります。
ピボットテーブルの横に、手で差額を足して合わせない。原因が元データに残るので、翌月も同じずれが出ます。調整した人にしか、数字の中身が分からなくなります。
重複した行を、理由を確かめずに消さない。二重の取り込みなら、消すのが正しい直し方です。明細の行なら、消すと金額が足りなくなります。
どちらか分かったら、元データを作っている人にも伝えます。次の月に、同じ重複が入るのを防げます。
それでも合わないとき：比べたい資料の数字の条件と、集計フィールドを確かめる
7つを確かめても合わないなら、ピボットテーブルではなく、比べたい資料の数字の条件がずれている可能性があります。
比べたい資料の数字と、次の3点をそろえます。
- 期間：どの日付で月を区切っているか
- 対象：キャンセル・返品を含むか
- 単位：税込か税抜か、円か千円か
集計フィールド(フィールドどうしの計算式を足す機能)を使っている場合は、計算のされ方を確かめます。集計フィールドの式は、1行ずつではなく、フィールドの合計に対して計算されます。
式は、「ピボットテーブル分析」タブの「計算」グループにある「フィールド/アイテム/セット」から、数式の一覧を表示して確かめられます。
架空の例で、「単価×数量」を集計フィールドにしたとします。単価100円・数量2個と、単価200円・数量1個の2行なら、売上は400円です。
集計フィールドでは、単価の合計300円×数量の合計3個で、900円になります。この場合は、元データに「金額」の列を作ってから合計します。
列が増えたり減ったりして、元データの形が大きく変わった場合は、ピボットテーブルを作り直す方が確実です。
数字の前提や定義をそろえる考え方は、次の記事にまとめています。
業務への着地：確認の順番を決めると、「どっちが正しいか」の見比べが減る
確認の順番を決めておくと、合わないたびに一から探す時間と、資料どうしを見比べる時間が減ります。
合わないときに思いつく所から見ると、同じ場所を何度も見たり、見落としたりします。7つを表の順に見ると、確認が毎回同じ手順で終わります。
架空の例で、Before/Afterを比べると次のとおりです。
| 場面 | Before | After |
|---|---|---|
| 合わないとき | 思いつく所から見る | 表の順に7か所を見る |
| 行が増えたとき | 毎月範囲を広げ直す | テーブルで自動で入る |
| 元データの修正 | 古い集計のまま出す | 開くときに更新する |
| ずれの調整 | 横で差額を足す | 元データの側で直す |
良くなるのは、確認の速さだけではありません。元データの側で直すと、翌月から同じずれが出なくなります。ピボットテーブルの数字を、調整なしで報告に使えるようになります。
元データに日付が列で並んでいたら、すぐピボットしたくなります。
毎月の集計そのものを、貼り付けと更新だけで済む形にする手順は、次の記事で整理しています。
まとめ
ピボットテーブルの合計が合わないときは、次の7つを確認します。
- 元データの範囲に、追加した行が入っているか
- 元データを直した後に、更新したか
- フィルター・スライサーで、項目が外れていないか
- 集計方法が「個数」になっていないか
- 数値が文字列で入っていないか
- 元データに重複行や、明細の複数行がないか
- 行・列のフィールドが、比べたい資料と同じ切り口か
それでも合わないときは、比べたい資料の数字の期間・対象・単位と、集計フィールドの計算のされ方を確かめます。ずれは元データの側で直すと、翌月の調整がいらなくなります。
