【エクセル：エラー事例】ピボットテーブルで参照元データの範囲指定の失敗をなくす3つの方法

　Excelのピボットテーブルを使うと集計や分析作業が格段に楽になり、時短にも非常に役立っています。そんな中で意外と時間を要してしまうことがあります。

　それがピボットテーブルの参照元データ（データソース）の範囲指定に関わる失敗が度々発生することです。

こんな人にオススメ

参照元データ（データソース）をピボットテーブルで集計する際、データの更新のたびに、範囲指定で失敗してしまう、これを防ぐ簡単な方法はないか、と悩んでいる人

※テーブル機能が使えない場合を想定しています。（※下記の事例参照）

　そこで今回は、ピボットテーブルの参照元データの範囲に関わる失敗を防ぐための方法を記事にします。

　ここで一つ断っておきますが、今回の記事は、テーブル機能が使えない不規則なデータを扱うことを条件として説明します。規則性のある参照元データの場合は、エクセルのテーブル機能を使えば範囲指定のミスはなくなるからです。

（※別記事参照【Excel教本にない事例】不揃いデータをピボットテーブルとVLOOKUP関数で可視化する方法（株式投資のポートフォリオ作成））

　私がとっているピボットテーブルの参照元データの範囲指定の失敗をなくす方法は

参照元データの範囲を「あらかじめ大きく」とって参照する範囲を固定する方法
参照元データの最終行に「ダミー行」をとって範囲指定する方法
参照元データの範囲の「列だけ」を範囲指定する方法

の3つです。

　当ブログで公開している株式投資Excelテンプレートでピボットテーブルを使っているのでそれを使って簡単な方法を説明します。

アイコン名を入力

　どの方法をとってもピボットテーブルの「データの更新」は絶対条件ですから忘れないように。「データの更新」をクリックしないとどんな方法をとっても失敗しますから。

1 【エクセル：エラー事例】ピボットテーブルで参照元データの範囲指定の失敗をなくす方法：その1～参照元データの範囲あらかじめ大きくとって範囲を固定する方法
2 【エクセル：エラー事例】ピボットテーブルで参照元データの範囲指定の失敗をなくす方法：その2～参照元データの最終行にダミーのデータ枠をとってダミーデータまでを範囲指定する方法
3 【エクセル：エラー事例】ピボットテーブルで参照元データの範囲指定の失敗をなくす方法：その3～参照元データの列だけを範囲指定する方法
4 まとめ：【エクセル：エラー事例】ピボットテーブルで参照元データの範囲指定の失敗をなくす3つの方法
5 【関連記事】

【エクセル：エラー事例】ピボットテーブルで参照元データの範囲指定の失敗をなくす方法：その1～参照元データの範囲あらかじめ大きくとって範囲を固定する方法

【Excel教本にない失敗事例】ピボットテーブルで参照元データの範囲指定の失敗をなくす3つの方法 — ※**範囲指定→　’【C】データベース（自作）’!$A$1:$AZ$58**　　最後の行は**「ダミー行」**

　この方法は、（※事例では参照元データを【C】データベースと呼称している）

参照元データをあらかじめ範囲を広くとって範囲を固定する（上記図面：範囲参照）
- その固定された範囲の中で各金融機関のWEBサイトから保有商品をコピペしても充分に余るだけの空白部分を行方向と列方向にとっておくことで、新たな商品の追加に耐えるようにしておく
- 当然、保有商品が減れば、その部分は空白のままにしておき、範囲も固定のまま変更しない

こととします。

　当ブログで公開している株式投資Excelテンプレートは、基本的にこの方法をとっていますので、参照してください。

【参照記事】

【図解エクセル】株式資産の見える化テンプレの使い方（楽天証券用）・1人分

【図解エクセル】株式資産の見える化テンプレの使い方（SBI証券用）・1人分

【図解エクセル】株式資産の見える化テンプレの使い方（SBIネオモバイル証券用）・1人分

【追加】【図解Excel】ネオモバイル証券のテンプレートにヤフーファイナンスのポートフォリオを利用する方法（2022年9月のヤフーファイナンスの様式変更後に対応）

【エクセル：エラー事例】ピボットテーブルで参照元データの範囲指定の失敗をなくす方法：その2～参照元データの最終行にダミーのデータ枠をとってダミーデータまでを範囲指定する方法

※　ダミー行の前に「行挿入」すれば範囲は自動的に広がる。
　　　　　　　　　例：’【C】データベース（自作）’!$A$1:$AZ$66　　**＜※$58→$66へ＞**

　この方法は、前項の「その1」と同時に行うことで、双方のいいとこ取りをする形で

参照元データの最終行にダミー行をどり、そのダミーの行まであらかじめ範囲をとって範囲を固定しておく。

ことです。

　これは、どうしても参照元データの行を増やす必要があるときを想定しており

それまで1人の名義だったが、家族の名義を付け加える必要があった場合
他の証券口座を開いた場合
保有商品が予想より増えてしまった場合
時系列の表を作るのでデータを追加する必要がある場合

などです。

　参照元データの行を増やす必要がある場合、既に指定された範囲内で行挿入することで、その範囲も自動的に変更されるので、範囲指定のし忘れ防止策となります。

【注意！】新たなデータを行挿入せずに、最終行（ダミー行）の下に追加した場合は、範囲は自動で変更されず、手動で範囲指定する必要があります。ここは注意してください。

【注意！】参照元データは、不揃いデータであるためテーブル機能が使えないので、通し番号を打つことや、フィルターを使用することで一塊のデータベースであることを認識させておいてください。

【参照記事】～上記と同様です。

【エクセル：エラー事例】ピボットテーブルで参照元データの範囲指定の失敗をなくす方法：その3～参照元データの列だけを範囲指定する方法

※**範囲指定→　’【C】データベース（自作）’!$A:$AZ**　　**※範囲は「列A～列Z」のみ**

　この方法は、

参照元データの範囲の「列だけ」を範囲指定する方法

で

行を追加していく場合、列の範囲だけを範囲指定しておけば、まったく行方向の範囲指定の心配することがない

ので大変便利がいいと思います。

　範囲の選択も、マウスで表全体に列の範囲をとればいいので簡単です。私のブログの株式投資Excelテンプレートの場合、時系列でデータを次々に参照元データの下に追加していく際は便利がいいので使うときもあります。

　ただ、その2の方法も簡単ですからその2・その3の選択は自由です。

注意！！

その3の方法で1つだけ注意する点があります。それはピボットテーブルの参照元データの範囲の下に総計などの数値が出てくる場合は、それをピボットテーブルが拾って集計（二重計上）してしまうので、フィルター機能で除く必要がありと面倒になります。（※そのことが分かっていれば問題ありませんが）ケースバイケースで選択すればいいと思います。