ピボットテーブルを作った。翌月、元のデータに行を足した。更新ボタンを押した。
増えた分が反映されない。
原因はだいたいひとつで、ピボットが見ている範囲が、作ったときの行数で固定されているからだ。
これを直すと、月次の集計がかなり楽になる。そしてついでに、ピボットで詰まるところをまとめて片づけておきたい。
この記事は育ちます。公開後も、会話パート・トーク・補足を足していきます。
公開 2026-10-16 ／ 最終更新 2026-10-16（公開）
水深 — ピボットは「範囲」を記憶している
ピボットテーブルを作るとき、元データの範囲を指定する。たとえば Sheet1!$A$1:$E$500。
この範囲が、ピボットの中にそのまま保存される。
だから501行目にデータを足しても、ピボットは見に行かない。500行目までしか知らないからだ。
「更新」ボタンは、記憶している範囲を読み直すボタンであって、範囲を広げるボタンではない。ここが誤解されやすい。
直し方は3つある
① 範囲を手で変える
ピボットを選んで「ピボットテーブル分析」タブ →「データソースの変更」。範囲を広げる。
毎月やるなら、毎月やることになる。その場しのぎ。
② 列全体を指定する
範囲を Sheet1!$A:$E にする。行数を書かない。
これで行を足しても反映される。ただし、空白行が1件としてカウントされることがある。ピボットの項目に「(空白)」が出てくるのはこれが原因だ。
③ 元データをテーブルにする（おすすめ）
元データを選んで Ctrl＋T。テーブルにする。
そのテーブルを元にピボットを作ると、行を足したぶんだけ範囲が自動で広がる。空白も出ない。
テーブルには名前が付く（既定では「テーブル1」）。ピボットのデータソースがその名前になっていれば、正しく設定できている。
ピボットを作る前に、まずCtrl＋T。 これを習慣にすると、この問題は二度と起きない。
更新を忘れる問題
範囲を直しても、更新ボタンを押し忘れるという事故は残る。
ピボットは自動で更新されない。元データを変えても、ピボットは古いままだ。そしてそれに気づかず、古い数字で報告してしまう。
対策が2つある。
① ファイルを開いたときに自動更新する設定にする
ピボットを右クリック →「ピボットテーブル オプション」→「データ」タブ →「ファイルを開くときにデータを更新する」にチェック。
これで、開いた時点で最新になる。
② 更新日時をシートに表示しておく
これは手動になるが、「いつ更新したか」が見えているだけで、押し忘れに気づきやすくなる。
月次で使うブックなら、①を入れておくのが安全だと思う。
数字が「個数」で出てしまう
もうひとつ、よくある詰まり方。
値のエリアに金額を入れたのに、合計ではなく「データの個数」になっている。
原因は、その列に文字列が混ざっていること。1行でも文字が入っていると、Excelは「この列は数値ではない」と判断して、個数を数える。
混ざる典型は、
- 空欄のつもりで半角スペースが入っている
- 「−」や「該当なし」と書いてある
- 数値が文字列として入っている（左寄せ）
- 「1,000」のようにカンマ付きの文字列
直し方は、元データの列を数値に揃える。値のエリアの設定を「合計」に変えても、根本は直らない。
見つけ方は、=COUNT(範囲) と =COUNTA(範囲) を比べる。COUNTは数値だけ、COUNTAは空白以外を全部数える。この2つの差が、文字列の件数になる。
日付が勝手にグループ化される
日付を行エリアに入れると、年・四半期・月にまとめられる。便利なときと、邪魔なときがある。
日別で見たいのにグループ化されたら、日付を右クリック →「グループ解除」。
逆に、グループ化されないときは、日付が日付になっていない（文字列になっている）。この場合もグループ化はできない。
日付の列を選んで、右寄せになっているか確認する。左寄せなら文字列だ。
「(空白)」が出る
行や列に「(空白)」という項目が出てくる。原因は2つある。
① 範囲に空白行が含まれている
列全体を指定していると起きる。テーブルにすれば解決する。
② 元データに空白セルがある
部署名が入っていない行がある、など。これはデータ側の問題なので、埋めるか、「未設定」などの文字を入れる。
空白のままにしないのが基本だと思う。空白は「まだ入力していない」のか「該当しない」のか区別がつかない。
同じ項目が2つに分かれる
「営業部」と「営業部 」（末尾にスペース）が、別の項目として並ぶ。
原因は前にも書いたとおり、見えない空白や全角と半角の混在。
- TRIM関数で前後の空白を落とす
- ASC関数で半角に揃える
- CLEAN関数で印刷できない文字を落とす
元データの段階で揃えておく。ピボット側では直せない。
そして、元データの入力を選択肢にするのがいちばん効く。入力規則（データの入力規則 →「リスト」）で、選ぶ形にしてしまう。手で打たせるから揺れる。
集計の重複を、ピボットで見つける
ピボットは集計の道具だが、チェックの道具としても使える。
行エリアに「伝票番号」、値エリアに「データの個数」を置く。2以上になっているものが重複だ。
同じ考え方で、
- 取引先コードと取引先名を両方行エリアに置く → 1つのコードに2つの名前があれば、マスタが汚れている
- 商品コードと単価を置く → 同じ商品に複数の単価があれば、価格の入力ミスか、期中の改定
「集計して見る」より、「ばらつきを見つける」のに使うと強い。これはピボットの使い方として、あまり語られていないと思う。
元データと集計を、混ぜない
ピボットを使うときの構えとして、いちばん大事なのはここだと思う。
- Sheet1 元データ（テーブル。1行1件、列ごとに1項目、空白なし）
- Sheet2 ピボット
- Sheet3 報告用の表（ピボットを参照、または値貼り付け）
この3層に分ける。
元データのシートに小計行を入れたり、色を付けたり、メモを書いたりしない。元データは、機械が読むためのものと割り切る。
見た目を整えるのは、Sheet3でやる。セル結合を使うならSheet3だけ。前に書いた話とつながる。
GETPIVOTDATAが邪魔なとき
ピボットのセルを別のシートから参照しようとすると、=GETPIVOTDATA(…) という長い式が自動で入る。
これは「項目名で参照する」式で、ピボットのレイアウトが変わっても正しい値を拾えるという利点がある。
ただ、下にコピーしても連続してくれない。ふつうのセル参照がほしいときは邪魔になる。
止め方は、「ピボットテーブル分析」タブ →「オプション」の横の矢印 →「GETPIVOTDATAの生成」のチェックを外す。
これで、普通に =Sheet2!B5 のような参照になる。
どちらがいいかは用途による。レイアウトを固定して使うならオフ、項目が増減するならオン。
ピボットを使わないほうがいい場面
最後に、逆の話も。
ピボットが向かないのは、こういう場合だ。
- 毎回まったく同じ形の表を作る → SUMIFSのほうが速いことがある
- 帳票として印刷する → レイアウトの自由度が低い
- 数式で他のセルから参照する → 参照が壊れやすい
- 複数の表を横に並べたい → ピボットは範囲が動くので、隣を押しつぶす
とくに最後。ピボットの右隣にセルを置くと、項目が増えたときに「ピボットテーブルが重なるため変更できません」と怒られる。ピボットの周りは空けておく。
SUMIFSとピボットの使い分けは、こう考えている。
- 形が決まっていて、毎月同じ → SUMIFS（式が固定されるので事故が少ない）
- 切り口を変えながら見たい → ピボット（ドラッグで組み替えられる）
探すときはピボット、出すときはSUMIFS。 これが実感に近い。
航路 — 範囲が固定されているのが原因
整理する。
- ピボットは作ったときの範囲を記憶している。更新は「範囲を読み直す」だけ
- 解決は元データをテーブル（Ctrl＋T）にしてから作ること
- 「ファイルを開くときに更新する」設定で、押し忘れを防ぐ
- 合計でなく個数になるのは、列に文字列が混ざっているから
- 「(空白)」が出るのは、空白行か空白セルがある
- 同じ項目が分かれるのは、空白・全角半角の揺れ
- ピボットは重複やばらつきを見つける道具としても使える
- 元データ・ピボット・報告用を、3枚のシートに分ける
ピボットが更新されないのは、故障でも、使い方が下手なのでもなかった。範囲を覚えているという仕様どおりに動いていただけだった。
仕様が分かると、対処が1行で済む。「作る前にCtrl＋T」。それだけで、毎月の数分が消える。
スライサーとタイムライン
ピボットを人に使ってもらうなら、この2つを付けておくと親切だと思う。
スライサー
ボタンで絞り込める。フィルタのドロップダウンより、何で絞っているかが一目で分かる。
ピボットを選んで「ピボットテーブル分析」→「スライサーの挿入」→ 絞りたい項目を選ぶ。
タイムライン
日付専用のスライサー。年・四半期・月・日の単位で、バーを動かして期間を選べる。
どちらも、複数のピボットに連動させられる。「レポートの接続」で、同じ元データを使うピボットを指定すると、1つのスライサーで全部が動く。
ダッシュボードのような画面が、これだけで作れる。 関数を1本も書かずに。
値の集計方法を変える
値エリアに置いた数字は、既定では「合計」になる。ここを変えると、見え方が広がる。
値の上で右クリック →「値の集計方法」から選ぶ。
- 合計、個数、平均、最大、最小
- データの個数（空白以外を数える）
- 数値の個数（数値だけ数える）
そして、もうひとつ強いのが「計算の種類」だ。
- 総計に対する比率 → 構成比が出る
- 親行集計に対する比率 → 部署内での比率が出る
- 累計 → 月ごとの累計が出る
- 基準値との差分 → 前月比、前年比が出る
前月比を出すのに、式を書く必要がない。 「計算の種類」→「基準値との差分の比率」→ 基準を「前の値」にするだけ。
ここを知らずに、ピボットの横に自分で計算列を作っている人は多いと思う。自分もそうだった。
集計フィールドと、集計アイテム
もう一段。ピボットの中で計算を作れる。
集計フィールド
「ピボットテーブル分析」→「フィールド/アイテム/セット」→「集計フィールド」。
「売上 ÷ 数量」で単価を出す、「粗利 ÷ 売上」で粗利率を出す、といったことができる。
注意点が1つある。集計フィールドは、合計してから計算する。行ごとに計算して合計するのとは、結果が違う。
粗利率のような比率では、これが正しい挙動になる（各行の率を平均しても意味がない）。ただし、意図と違うこともあるので、数字が合わないときはここを疑う。
複数の表を、リレーションでつなぐ
売上データと商品マスタが別のシートにある。商品分類ごとに集計したい。
従来は、売上データにVLOOKUPで分類を持ってきてから、ピボットを作っていた。
データモデルを使うと、その作業がいらなくなる。
- 両方の表をテーブルにする
- ピボットを作るときに「このデータをデータモデルに追加する」にチェック
- 「リレーションシップ」で、商品コードどうしをつなぐ
これで、売上データ側に分類の列がなくても、分類で集計できる。
VLOOKUPの列を作らなくていいので、元データが軽くなる。そしてマスタを直せば、集計にも反映される。
Power Queryのマージと似ているが、こちらはつないだまま持つ。どちらがいいかは場合による。
ピボットの「更新されない」を、もう一段
本文で書いた以外にも、更新されないパターンがある。
① 元データが別ファイルで、そのファイルが閉じている
更新できないか、古いキャッシュを返す。元ファイルを開いてから更新する。
② 数式で作った列が、再計算されていない
計算方法が「手動」になっていると、元データの数式が古いまま。F9で再計算してから、ピボットを更新する。
③ フィルタで消えた項目が残っている
元データから消えた項目が、ピボットの選択肢に残り続ける。
「ピボットテーブル オプション」→「データ」→「ファイルに保存するアイテム数」を「なし」にすると、消える。
③はよく見る。「もう存在しない部署名」がいつまでも選択肢に出てくるのは、これが原因になる。
この記事は、まだ書きかけです
この記事は、公開してから足していく形にしています。調べたことを書いた本文がまず先にあって、あとから「聞き手と話し手の会話」「あとで思い出したこと」「読んだ人から教わった補足」が積もっていきます。
だから、同じURLでも半年後には別のものになっているはずです。もし一度読んだことがある記事なら、下の更新ログだけ見てください。増えたところが分かります。
更新ログ
- 2026-10-16 公開（本文のみ。会話パートはこれから足します）
