元データに行を追加して「更新」を押しても、ピボットテーブルに新しい行が入らないことがあります。よくある原因は、元データの参照先が Sheet1!$A$1:$E$100 のような固定範囲になっていることです。参照先が固定範囲だと、101行目以降に追加した行は、何度更新しても集計に入りません。
対処は次の3つです。
- 今の参照先が固定範囲になっていないか確認する
- 元データをExcelの「テーブル」に変える
- ピボットテーブルの参照先をテーブル名に切り替える
ここまで済ませれば、あとは行を追加して「更新」を押すだけで反映されます。ファイルを開いたときに自動で更新する設定も、あわせて説明します。
まず、今の参照先を確認する
- ピボットテーブル内のセルをどれか1つ選択する
- リボンの「ピボットテーブル分析」タブ（バージョンによっては「分析」や「オプション」）を開き、「データソースの変更」をクリックする
- 表示された画面の「テーブル/範囲」欄を見る
| 欄の表示例 | 状態 | 次にやること |
|---|---|---|
Sheet1!$A$1:$E$100 |
固定範囲。範囲より下に追加した行は集計されない | テーブル化して参照先を変更する |
売上明細、テーブル1 など |
テーブルを参照している。テーブルが広がれば集計範囲も広がる | 更新漏れか、テーブル自体の範囲を確認する |
Sheet1!$A:$E |
列全体を参照している | 空白行が「(空白)」という項目として集計に出やすいため、テーブル参照に変えるほうが扱いやすい |
| 別のブック名を含む参照 | 別ファイルのデータを参照している | どのファイルを更新すればよいかを先に確認する |
参照先を確認したら、いったん「キャンセル」で閉じてください。
なぜテーブルにすると範囲が広がるのか
Excelのテーブル（「挿入」→「テーブル」で作る表）は、最終行のすぐ下に入力したり貼り付けたりすると、範囲が自動で広がります。ピボットテーブルの参照先を $A$1:$E$100 のようなセル番地ではなくテーブル名にしておけば、その時点のテーブル全体が集計対象になります。
なお、行を追加してもテーブル自体の範囲が広がらないときは、ピボットテーブルではなくテーブル側の問題です。その確認は「Excelテーブルの範囲が自動で広がらない」ケースとして切り分けてください。
手順1: 元データをテーブルに変換する
変換する前に、元データが次の状態になっているか確認します。
- 見出しが1行だけで、見出しに空欄や同じ名前がない
- データの途中に、完全に空の行や列がない
- 結合セルがない
- 表のすぐ下に合計行や備考がない（テーブルに含めると、合計行もデータとして集計されてしまいます）
確認できたら変換します。
- 元データの中のセルを1つ選択する
Ctrl+Tを押す（または「挿入」→「テーブル」）- 表示された範囲が表全体になっているか確認する
- 「先頭行をテーブルの見出しとして使用する」にチェックを入れて「OK」を押す
続けて、テーブルに分かりやすい名前を付けます。
- テーブル内のセルを選択する
- 「テーブルデザイン」タブ（バージョンによっては「デザイン」）の左端にある「テーブル名」欄に、
売上明細のような名前を入力してEnterを押す
テーブル名にスペースは使えません。テーブル1 のままでも動きますが、テーブルが複数あるブックでは、名前を付けておくほうが参照先を間違えにくくなります。
テーブルにすると縞模様の書式が付きます。見た目を変えたくない場合は、「テーブルデザイン」タブのスタイル一覧で「なし」を選んでください。
手順2: ピボットテーブルの参照先をテーブル名に変える
- ピボットテーブル内のセルを選択する
- 「ピボットテーブル分析」→「データソースの変更」を開く
- 「テーブル/範囲」欄の内容を消し、
売上明細のようにテーブル名を入力する - 「OK」を押す
- もう一度「データソースの変更」を開き、欄がテーブル名になっていることを確認する
押さえておきたい点が3つあります。
- ピボットテーブルが複数あるときは、1つずつ参照先を確認します。1つだけテーブル参照に変えても、ほかのピボットテーブルは固定範囲のまま残っていることがあります。
- 見出しの名前を変えた列は、参照先を切り替えた後、ピボットテーブルのレイアウトから外れることがあります。切り替えた後は、行・列・値の配置が元どおりか確認してください。
- これから新しくピボットテーブルを作る場合は、テーブル内のセルを選択した状態で「挿入」→「ピボットテーブル」を使えば、最初からテーブル名が参照先になります。
手順3: 行を追加して「更新」する
ピボットテーブルは、数式のように自動では再計算されません。元データを変えたら、必ず更新します。
| 操作 | 方法 | 対象 |
|---|---|---|
| 更新 | ピボットテーブル内で右クリック→「更新」、または Alt + F5 |
選択しているピボットテーブル（同じデータを共有するものを含む） |
| すべて更新 | 「データ」タブ→「すべて更新」、または Ctrl + Alt + F5 |
ブック内のピボットテーブルやクエリすべて |
ピボットテーブルが複数あるブックや、Power Queryと組み合わせているブックでは、「すべて更新」を使えば更新漏れを防げます。
ファイルを開いたときに自動で更新する
更新のし忘れが心配なら、ファイルを開いたときに自動で更新されるように設定できます。
- ピボットテーブル内で右クリック→「ピボットテーブル オプション」を開く
- 「データ」タブを開く
- 「ファイルを開くときにデータを更新する」にチェックを入れて「OK」を押す
この設定は、ピボットテーブルごと（正確には、ピボットテーブルが使う集計用データごと）に持つ設定です。ピボットテーブルが複数ある場合は、それぞれ確認してください。
この設定で更新されるのは、ファイルを開いたときだけです。開いている間に元データへ行を追加した場合は、手作業で「更新」を押す必要があります。編集のたびに自動で更新したい場合はVBAで実現できますが、ブックがマクロ有効形式（.xlsm）になり、保守の手間も増えます。多くの業務では「開いたときに自動更新」と「編集後に Ctrl + Alt + F5」の組み合わせで足ります。
Web版のExcelでは、オプション画面で設定できる項目がデスクトップ版と異なることがあります。設定はデスクトップ版のExcelで行い、Web版では更新ボタンで反映されるか確認してください。
新しい行が反映されたかを確かめる
更新した後は、件数と合計を元データと突き合わせます。
- 合計: ピボットテーブルの総計と、
=SUM(売上明細[金額])の結果が一致するか - 件数: ピボットテーブルでIDなどの個数を集計した値と、
=ROWS(売上明細)の結果が一致するか - 新しい項目: 追加した行の日付・取引先などが、行ラベルやフィルターの一覧に出ているか
削除したはずの項目がフィルターの一覧に残り続ける場合は、「ピボットテーブル オプション」→「データ」タブの「1つのフィールドに保持するアイテム数」を「なし」にしてから更新します。
テーブル化しても反映されないときの確認順
- 参照先: 「データソースの変更」の欄がテーブル名になっているか
- 追加した行の位置: 追加した行がテーブルの中に入っているか。そのセルを選んだときに「テーブルデザイン」タブが表示されなければ、テーブルの外にあります
- 更新の対象: 別のピボットテーブルだけを更新していないか。迷ったら「すべて更新」を使う
- フィルター: レポートフィルターやスライサーで、新しい項目が除外されていないか。手作業で項目を選んでフィルターしている場合は、「フィールドの設定」→「小計とフィルター」の「手動フィルターに新しいアイテムを含める」の状態を確認する
- 値の型: 追加した金額が文字列として入力されていないか。文字列の数字は合計に入らず、集計方法が「個数」に変わる原因になります
この順番で確認すれば、参照先の問題なのか、テーブルの問題なのか、表示や型の問題なのかを切り分けられます。
