2-5.Excel・スプレッドシート・ピボットテーブルの使い方 PR

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

【エクセル:エラー事例】ピボットテーブルで参照元データの範囲指定の失敗をなくす3つの方法
記事内に商品プロモーションを含む場合があります

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

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

こんな人にオススメ

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

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

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

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

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

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

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

の3つです。

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

アイコン名を入力

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

【エクセル:エラー事例】ピボットテーブルで参照元データの範囲指定の失敗をなくす方法:その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つだけ注意する点があります。それはピボットテーブルの参照元データの範囲の下に総計などの数値が出てくる場合は、それをピボットテーブルが拾って集計(二重計上)してしまうので、フィルター機能で除く必要がありと面倒になります。(※そのことが分かっていれば問題ありませんが)ケースバイケースで選択すればいいと思います。

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

 今回の記事で下のような人の悩み解決ができれば幸いです。 

こんな人にオススメ

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

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

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

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

の3つですから、参考にしてください。

 まだ他にもやり方はあると思いますが、この3つが私の経験から比較的簡単にできる方法ではないかと思います。

アイコン名を入力

 最後にもう一度。「データの更新」は絶対条件です。上の3つの方法の以前の問題ですから忘れないように!

【関連記事】

【要注意!】エクセル・スプレッドシート及びピボットテーブルの操作方法で注意する点

要注意!【エクセル:エラー事例】株管理ポートフォリオ(円グラフ)作成用のエクセル・スプレッドシート及びピボットテーブルの使い方で注意する点
要注意!【エクセル:エラー事例】株管理ポートフォリオ(円グラフ)作成用のエクセル・スプレッドシート及びピボットテーブルの使い方で注意する点 今回の記事は、ExcelのピボットテーブルをGoogleスプレッドシートにコンバートすることに関する悩みや、ピボットテーブルの参照元データの範囲指定の失敗等に対し回答するものです。  当ブログの株式投資ExcelテンプレートをGoogleスプレッドシートにコンバートする際、ピボットテーブルの集計で同じようなミスが何度も出てくると思われます。  私がそのようなときに解決した方法、その原因と対策を記事にしました。参考になれば幸いです。...
【エクセル:エラー事例】ピボットテーブルの集計(合計・総計)が参照元データの集計と合わない、集計できない場合の原因と修正方法(事例②:「範囲の更新忘れ」or「範囲指定の間違い」)
【エクセル:エラー事例】ピボットテーブルの集計(合計・総計)が参照元データの集計と合わない、集計できない場合の原因と修正方法(事例②:「範囲の更新忘れ」or「範囲指定の間違い」) 今回の記事は、Excelのピボットテーブルの集計(合計)に関するエラーの悩みに対し図解付きで回答するものです。  当ブログの株式投資Excelテンプレートのような不揃いなデータを使った場合、ピボットテーブルの集計(合計)で同じようなミスが何度も出てくると思われます。  ただ、その原因はおおよそ2つに絞られると思われるのでその点を詳細に記事にしました。参考になれば幸いです。...

 株管理ポートフォリオ作成の基本記事

【いまさら聞けない基本の「き」】エクセルで株管理ポートフォリオ(円グラフ)の作り方とExcelテンプレートの使い方の基本の記事5本<全て無料>(SBI・楽天証券・2024年の新NISA移行対応予定)※Googleスプレッドシート版あり
【2024年の新NISA移行対応】【いまさら聞けない基本の「き」】エクセルで株管理ポートフォリオ(円グラフ)の作り方とExcelテンプレートの使い方の基本の記事4本<全て無料>(SBI・楽天証券)※Googleスプレッドシート版あり 今回の記事は、いまさら聞けない基本の「き」として、エクセルによるSBI証券と楽天証券とSBIネオモバイル証券の株管理ポートフォリオの作り方とテンプレートの使い方の基本記事を集めました。途中からブログ記事を読んだ方で、基本に戻って作り方や使い方を知りたいと悩んでいる方へ向けたものです。  一度作成してしまえば次回からは意外と簡単にポートフォリオができてしまいます。  全て無料配信です。  私が現に使っているExcelテンプレートを多数公開していますので、ぜひお使いください。ピボットテーブルで集計をしており、使いやすさ、分かりやすさを追求して作成しております。  参考になれば幸いです。...

親子2世代運用のポートフォリオ作成テンプレート使い方の記事一覧

【おすすめ】老後資金は親子2世代で資産運用・Excelテンプレートで株管理ポートフォリオ(円グラフ)作成」記事一覧【SBI・楽天証券・NISA対応】Googleスプレッドシート版も有<無料>
【おすすめ】老後資金は親子2世代で資産運用・Excelテンプレートで株管理ポートフォリオ(円グラフ)作成」記事一覧【SBI・楽天証券・NISA対応】Googleスプレッドシート版も有<無料> 今回の記事は、老後資金の親子2世代運用を目指している定年後のシニアの方やシニアを親に持つ方へむけての記事の一覧表です。  2世代運用での複数口座や複数名義など集計に悩んでいる方は、私が現に使っているExcelテンプレートでポートフォリオ(円グラフ)を作る方法をぜひお使いください。  ピボットテーブルでの集計で、使いやすさ、分かりやすさを追求して作成しております。参考になれば幸いです。  全て無料です。...
【NISA枠・拡大拡充に備えて】株式投資信託・資産運用管理ポートフォリオ作成用Excelテンプレートの使い方(老後資金の親子2世代運用版)【SBI証券・楽天証券】<無料>※スプレッドシート対応
【NISA枠・拡大拡充に備えて】株式投資信託・資産運用管理ポートフォリオ作成用Excelテンプレートの使い方(老後資金の親子2世代運用版)【SBI証券・楽天証券】<無料>※スプレッドシート対応今回の記事は、NISAに特化していますが、基本は老後資金の親子2世代運用を目指している定年後のシニアの方やシニアを親に持つ方へむけての記事です。複数口座や複数名義など集計し、その中にNISAが混在しており、NISAの集計や分析で悩んでいる方向けです。私がNISA確認用に使っているExcelテンプレートを無料で公開していますので、ぜひお使いください。ピボットテーブルで集計をしており、使いやすさ、分かりやすさを追求して作成しております。参考になれば幸いです。...

 これまでに公開したテンプレートに関する記事一覧はコチラです。(リスク別テンプレート一覧はコチラです。)

※【楽天市場】楽天市場で「エクセル・ピボットテーブル」関連の商品購入はこちらから↗

※【メルカリ】メルカリで「エクセル・ピボットテーブル」関連の商品購入はこちらから↗

ABOUT ME
CAN(かん)
●60歳でサラリーマン定年退職後、10年目。「今日が一番若い日」と言いながらブログを始めたブログ初心者のオヤジです。●3つの証券会社分&家族全員分&100銘柄分をまとめて見える化できるExcelテンプレート無料配信中!
こちらの記事もおすすめ!
【図解スプレッドシート】同時リスク(暴落×円高)発生時の資産の下落幅が分かる株管理ポートフォリオの作り方とテンプレートの使い方(SBI証券・老後資金の親子2世代運用・積立NISA等・投資信託含む)<無料配信> 2-2-3.ダブルリスク

【図解スプレッドシート】同時リスク(暴落×円高)発生時の資産の下落幅が分かる株管理ポートフォリオの作り方とテンプレートの使い方(SBI証券・老後資金の親子2世代運用・積立NISA等・投資信託含む)<無料配信>

2022年8月12日
らくぶる
かん  今回はシニア高齢者の代表者のじゃなさんのMy資産の一部にバランス型の投資信託を保有した場合、全体の株価暴落リスクと円高為替リスクは …
【おすすめ】マネーフォワードMEの便利な使い方:最速1分で株式投資の同時リスク(株価暴落×円高)発生時の資産下落幅がわかるExcelテンプレート配信<無料>(SBI証券・楽天証券等・親子2世代運用版) 2-2-3.ダブルリスク

【おすすめ】マネーフォワードMEの便利な使い方:最速1分で株式投資の同時リスク(株価暴落×円高)発生時の資産下落幅がわかるExcelテンプレート配信<無料>(SBI証券・楽天証券等・親子2世代運用版)

2022年7月27日
らくぶる
更新(2023/02/19):配信テンプレートの修正更新(【C】データベースの列ADの「銘柄」を【B】コード表からVLOOKUP関数で引く …
【Excelテンプレート】株価暴落想定したポートフォリオの作り方とテンプレートの使い方(リバランスのやり方)の配信<無料>(SBI証券・楽天証券・1人用・NISA対応) 2-2-1.暴落下落リスク

【Excelテンプレート】株価暴落想定した株管理ポートフォリオ(円グラフ)の作り方とテンプレートの使い方(リバランスのやり方)の配信<無料>(SBI証券・楽天証券・1人用・NISA対応)

2022年5月12日
らくぶる
じゃなさん 今度は「株価暴落想定とリバランス用」のポートフォリオの作成とテンプレートの使い方じゃな。それの「SBI証券」+「楽天証券」版じ …