サービス
サービスを探す
プロ人材を探す
仕事を探す
ブログを探す
ピボットテーブルが集計してくれないときに疑う4か所
記事IT・テクノロジー
データを更新して「更新」ボタンを押したのに、合計欄の数字が変わらない。
あるいは、明細を数えると300行あるはずなのに、ピボットの右下に出ている件数が280しかない。
エラーは出ません。数字は出ているので、間違っていることに気づきにくい。
ピボットテーブルが正しく集計しない原因は、だいたい次の4か所のどれかです。
順番に、原因から見ていきます。
■ 疑うところ1：参照範囲がデータの増加に追いついていない
ピボットテーブルは、作成した時点の範囲（例えば A1:F300）を記憶しています。
その後で元データに行を追加しても、範囲は自動では広がりません。
301行目以降を追加して更新ボタンを押しても、ピボットは300行目までしか見ていないので、
新しい行は集計に反映されません。これがいちばん多い原因です。
厄介なのは、「更新」ボタン自体は正常に動いているように見えることです。
ボタンを押すとぐるっと処理が走り、既存の300行分の集計は正しく再計算されます。
だから「更新はできている、でも数字が変わらない」という一見矛盾した状態に見えて、
原因が範囲の古さだと気づくまで時間がかかります。
対処は2つあります。
その場しのぎは、ピボットテーブルの分析タブから「データソースの変更」で範囲を広げ直すことです。
ただし次に行が増えたら、また同じことが起きます。
根本対処は、元データを「テーブル」に変換しておくことです。
Excelなら元データを選択して Ctrl+T、Googleスプレッドシートなら範囲を選んで
「表示形式 > 交互の背景色」ではなく名前付き範囲やテーブル機能を使います。
テーブル化すると、行を追加したときに範囲が自動で追従するようになります。
■ 疑うところ2：数値のはずが文字列になっている
合計欄が0にはならないのに、数えると明細より少ない合計しか出ない場合、
一部の数値が文字列として入力されている可能性があります。
見分け方は、セル内の数字が右揃えか左揃えかです。
Excelは数値を右揃え、文字列を左揃えで表示します。
金額の列なのに一部だけ左揃えになっていたら、それは文字列です。
文字列の数字はピボットの合計に含まれません。エラーも出ないまま、静かに抜け落ちます。
他システムから書き出したCSVを取り込んだときや、先頭にアポストロフィが付いた状態で
コピーしたときによく起こります。
直すには、空いている列に次の数式を入れて数値に変換し、その列を元データとして使います。
=VALUE(A2)
あるいは、対象範囲を選んで「区切り位置」ダイアログを開き、そのまま完了を押すだけでも
文字列が数値に変換されることがあります。
どのセルが文字列化しているか目視で追うのが大変な場合は、次の数式で件数を数えられます。
=SUMPRODUCT(--ISNUMBER(A2:A300))
明細の行数と、この数式で返ってくる件数を比べてください。差があるだけ、
文字列化した数値が紛れ込んでいます。差がゼロになるまで直せば、集計から漏れは無くなります。
■ 疑うところ3：空白の行や列が表の途中に紛れ込んでいる
元データの範囲選択を自動認識に任せている場合、表の途中に空白行が1本あると、
そこで表が終わったと判断され、それより下がすべて集計から外れます。
見出し行のすぐ下や、月ごとの区切りとして空白行を入れている表でよく起こります。
空白列も同様で、列の途中が空だと右側が別の表として扱われることがあります。
たとえだ12月分の下に1行空けて1月分を続けているような表では、
1月分がまるごと集計から消えていても、シートの見た目上は違和感がありません。
「先月までは合っていたのに今月から合わない」というときは、まずこれを疑ってください。
対処は、集計前に元データの空白行・空白列を詰めることです。
どうしても空白を残したい場合は、範囲を手動で指定してピボットを作り直してください。
■ 疑うところ4：フィルターや集計方法の設定が残っている
前回の作業でかけたフィルター条件が残ったままになっていて、
特定の商品や期間だけが除外されていることがあります。
ピボットテーブルのフィールドリストで、フィルターの絞り込み条件（▼マーク）を確認してください。
もう1つ、「合計」のつもりが「個数」になっているケースです。
値フィールドの設定で集計方法が自動的にCOUNTになっていることがあり、
これは元データに1つでも空白セルや文字列が混ざっていると起こりやすくなります。
値フィールドを右クリックして「値フィールドの設定」から集計方法を確認してください。
これは、値フィールドとして最初にドラッグしたセルが空白や文字列だった場合に起こりやすい設定です。
以降にどれだけ数値の行が続いていても、Excel側の初期判定はそのままCOUNTのことがあります。
金額の列を集計しているつもりで、実は件数を見ていた、という取り違えは珍しくありません。
■ やってはいけないこと
数字が合わないからと、ピボットの結果を直接上書きすること。
ピボットテーブルの集計セルを手で書き換えても、次に更新ボタンを押した瞬間に
元の集計値へ戻ります。原因を直さないまま数字だけ合わせても、翌日にはまた崩れます。
まず疑うべきは元データの範囲と型であって、ピボット側の設定ではないことがほとんどです。
もう1つ、原因を特定しないままピボットテーブルを一から作り直すことも避けてください。
作り直せば一時的に直ったように見えますが、元データ側の空白行や文字列化は残ったままなので、
次に行が増えたときにまた同じ症状が出ます。作り直しは、原因を潰した後の仕上げにしてください。
■ それでも解決しないとき
・4か所とも確認したのに、まだ数字が合わない
・複数のシートを集計元にしていて、どこがずれているか追えない
・ピボットテーブルが重くて、更新のたびに固まる
このあたりまで来たら、元データの構造から見直した方が早いです。
私はExcel・スプレッドシートの集計まわりの調査と修正を承っています。
どこが原因でずれていたかを一覧にしてお返ししています。
――
この記事の執筆には生成AIを活用しています。内容は実際の挙動を確認して書いています。
このブログを見た人にオススメ
