ピボットテーブルは、元データを直しても自動では変わらない。更新するまで前の数字が残る。しかも更新ボタンを押しても、足した行が入らないことがある。
300行で作ったピボットに50行足して、更新ボタンを押した。総計は13,608,200円のままだった。元データの合計は15,678,700円で、2,070,500円が落ちている。
以下の操作は、2026年9月21日にMicrosoft 365のExcel（バージョン16）で実際に動かして確かめた。データは式を確かめるために作った架空の売上だ。
更新ボタンを押しても、足した50行は入らなかった
原因はデータソースの範囲にある。ピボットを作ったとき、元データの範囲が売上!$A$1:$C$301のように固定で記録される。あとから302行目以降に足しても、その範囲の外なので見ない。
更新ボタンは「いま記録されている範囲を読み直す」だけだ。範囲そのものは広げない。だから何度押しても同じ数字が出る。ここが分かりにくい。動いていないのではなく、正しく動いた結果として古い数字が出ている。
範囲を広げるには、ピボットを選んで分析タブのデータソースの変更から、新しい範囲を指定し直す。実際に売上!$A$1:$C$351に変えて更新したら、総計は15,678,700円になった。
毎月これをやるのは忘れる。行が増えるたびに範囲を指定し直す作業が、更新のたびに1つ増えるからだ。
テーブルにすると、行が増えても追いかける
元データを選んでCtrl+Tでテーブルにしてから、そのテーブル名をデータソースに指定する。テーブルは行を足すと自動で伸びるので、範囲を指定し直さなくていい。
実際に試した。350行をテーブルにしてピボットのソースを差し替え、さらに20行足して更新ボタンを押すと、総計は16,835,200円になった。足した20行がそのまま入っている。
テーブルにするときの注意は2つある。見出し行が1行であること、見出しが空のままの列がないこと。見出しが空だと「列1」のような名前が勝手に付いて、ピボットの項目名がそれになる。
配っているブックは、元データをテーブル（売上表）にしてある。行を足して更新するだけで済む。
ファイルを開くときに更新する設定は、既定で外れている
ピボットを右クリックしてピボットテーブルオプションを開き、データのタブを見ると「ファイルを開くときにデータを更新する」がある。ここが入っていれば、受け取った人が開いた時点で最新になる。
このブックで既定値を読んだら、オフだった。何も設定していないピボットは、開いても更新されない。作った人が最後に更新した時点の数字が、そのまま画面に出る。
配る側はここを入れておく。ただし入れてあっても、元データが同じブックの中にあることが前提だ。外部ファイルを参照しているなら、そのファイルが開ける場所にないと更新できない。
配られる側は、この設定が入っているかどうかを開いて確かめるしかない。入っていない前提で検算したほうが早い。
総計が合っているのに、金額が合わないことがある
更新の話とは別に、もっと見つけにくい形がある。金額の列に、数字が文字列として入っている行だ。
200行のうち10行だけ、金額を文字列で入れて試した。本来の合計は8,447,400円で、文字列の10行は330,800円。本来の3.9%にあたる。
結果はこうなった。ピボットの総計は8,116,600円。元データのSUMも8,116,600円。差はゼロで一致する。SUMもピボットの合計も、文字列は飛ばして足すからだ。総計を突き合わせる検算だけでは見つからない。
見つかるのは件数のほうだ。COUNTは190、COUNTAは200。数値として数えられるのが190行しかない。ピボットに「データの個数」を置くと200と出る。文字列も1件として数えるからだ。
数値の件数 =COUNT(売上!$D:$D)
空でない件数 =COUNTA(売上!$D:$D)-1
文字列の行数 =空でない件数-数値の件数この差が0でなければ、金額の列に文字列が混ざっている。他のブックから貼ったときや、CSVを読み込んだときに出る。名簿の突き合わせが一致しない話と同じで、見た目は同じでも中身の型が違う。
空白行を1つ入れたら、範囲が50行目までに切れた
200行のデータの51行目に空白行を1つ入れて、自動で取れる範囲を読んだ。$A$1:$C$50だった。見出しを除くと49行しか入っていない。下にある151行は範囲の外になる。
ピボットを作るときに範囲を選ばずに「挿入」から作ると、Excelは連続した範囲を自分で判断する。その判断が空白行で止まる。小計の行を入れたり、月の区切りに1行空けたりすると、そこで切れる。
これは式で見つけられる。データが入っている最後の行の行番号と、件数から出る最後の行を比べればいい。
最終行の行番号 =SUMPRODUCT(MAX((売上!$A$1:$A$5000<>"")*ROW(売上!$A$1:$A$5000)))
件数から出る行 =COUNTA(売上!$A:$A)途中に空白行がなければ、この2つは一致する。空白行が3つあれば3つずれる。SUMPRODUCTとMAXの組み合わせはCtrl+Shift+Enterが要らないので、配ったブックでも壊れない。
件数が少ないなら、ピボットをやめて式にする
ピボットには「更新する」という手順が要る。手順があるかぎり、いつか飛ぶ。SUMIFSで組めば、元データを直した瞬間に集計も変わる。更新という言葉が出てこない。
店舗ごとの合計なら、こう書ける。
=SUMIFS(売上!$D:$D,売上!$B:$B,$A3)列全体を指定しておけば、行が増えても範囲を直さなくていい。ピボットの範囲を毎月広げるより手数が少ない。
ピボットが効くのは、見る軸をその場で変えたいときだ。店舗別で見て、次に品名別で見て、月でも切る。この動きを式でやるのは面倒だし、式が増えるほど読めなくなる。
うちは、毎月同じ形で出すものはSUMIFS、その場で掘るものはピボット、という分け方にしている。在庫のSUMIFSが22個ずれた話のように式のほうにも落とし穴はあるが、更新を忘れて古い数字が出るという事故は起きない。
いつ更新したかを記録したいとも思ったが、ワークシート関数でピボットの更新日時を取る方法は見つけられなかった。TODAYは開くたびに今日になるので、更新した証拠にはならない。更新した人が手で日付を書く欄を作るのが、いまのところの落としどころだ。
受け取った側が3分で確かめる式
配られたブックのピボットを信じていいかは、3つ見れば分かる。
1つ目は総計と元データの合計。GETPIVOTDATAでピボットの総計を取って、元データのSUMと引き算する。
ピボットの総計 =GETPIVOTDATA("金額の合計",集計!$A$3)
元データの合計 =SUM(売上!$D:$D)
差 =元データの合計-ピボットの総計GETPIVOTDATAは、フィールドの指定を省くと総計を返す。ピボットの左上のセルを2つ目の引数に渡す。ピボットの形を変えても式が壊れないので、総計のセルを直接参照するより安全だ。
2つ目がCOUNTとCOUNTAの差、3つ目が最終行の差になる。この3つが全部0なら、いま見ている数字は元データと一致している。
配るブックには、この検算を入れたシートを足しておく。自分が更新を忘れたときにも気づける。数字が合っているかを人の記憶に任せない、という点ではAIが書いた文書を照合する話と同じ考え方だ。
作っていて気づいたのは、範囲が古いピボットほど数字が「それらしく」見えることだった。300行分の集計は形が整っていて、どこもおかしくない。50行足りないことは、元データの合計と並べるまで誰にも分からない。合っているように見える数字ほど、一度は引き算しておく。
