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

【超レア】【エクセル:エラー事例】ピボットテーブルの集計(合計・総計)が参照元データの集計と合わない場合の原因と対策(事例①:コード表に未登載あり)

【エクセル:エラー事例】ピボットテーブルの集計(合計・総計)が参照元データの集計と合わない場合の原因と対策
記事内に商品プロモーションを含む場合があります

今回の記事は、Excelのデータベースをピボットテーブルで集計したとき、参照元データ(データソース)の集計値とピボットテーブルの集計値が合わない(一致しない)と悩んでいる方に向けた記事です。

かん

このブログは、株式投資Excelテンプレート(楽天SBI証券・NISA対応)でポートフォリオの作り方の記事を配信しています。このテンプレートを使うときによくあるミスを取り上げて記事にしました。

私の経験から結論は、その原因のほとんどが、

  1. エラー表示(空白も含む)の中に答えがある(今回の記事)事例:コード表に未登載
  2. 「範囲の更新忘れ」か「範囲指定の間違い」(別記事を参照)

です。(※②の「範囲の更新忘れ」か「範囲指定の間違い」と思う方は、「範囲の更新忘れ」か「範囲指定の間違い」(別記事)を先に参照してください。)

かん

普通のピボットテーブル利用の場合は、②の「範囲の更新忘れ」か「範囲指定の間違い」が多いと思いますが、私が配信しているテンプレートでは、「エラー表示(空白も含む)の中に答えがある」という場合がよくありますので、【超レア】として記事にしました。

今回の記事では、①のエラー表示(空白も含む)の中に答えがある場合について説明します。

こんな人におすすめの記事です。

こんな人にオススメ
  • 仕訳した参照元データ(データソース)をピボットテーブルで種類別に集計をしたが、集計値(合計)が参照元データの集計値(合計)と合わない、と悩んでいる人
  • 「範囲の更新忘れ」や「範囲指定の間違い」が原因ではなく、参照元データと集計(合計)が合わない原因が分からない人

わかれば簡単なことなんですが、わかるまでに意外と時間を要することがあります。しばらくしないと再度おなじ間違いをするExcelあるあるです。

【エクセル:エラー事例】ピボットテーブルの集計(合計・総計)が参照元データの集計と合わない場合・事例その1(株式投資Excelテンプレートで例示)

当ブログの株式投資Excelテンプレート(楽天SBI証券・NISA対応)を例に説明します。(※数値はアレンジしています)

ピボットテーブルの時価評価額が20万円少ない!

下の図は、データベースの実際の時価評価額が「2311万4250円」であるのに、ピボットテーブルの時価評価額が「2291万4250円」と「20万円」少ないケースです。

【Excel教本に載ってない・誰も教えてくれない】エクセルの失敗あるある:ピボットテーブル集計が合わない①

ピボットテーブルのフィルター機能の確認

 このとき、下の図のように、ピボットテーブルのフィルターは、「1株式・投信等」「2現金・米国債など」「3貴金属・コモ・仮通」の3カ所にチェックが入っています。

【Excel教本に載ってない・誰も教えてくれない】エクセルの失敗あるある:ピボットテーブル集計が合わない①

 これは、下記の「注意(チェック)-1」のように、テンプレート自体がこの3カ所にチェックされているため、その3カ所の合計値を集計しているのです。

 しかし、実際は、「エラー表示」の中に不足している「20万円」があったため、「20万円不足」していたのです。

ピボットテーブル集計時の【注意(チェック)-1】

 これは、楽天証券のWEBサイトのデータで資産の見える化をするテンプレートの使い方の記事を参照しています。

【注意(チェック)-1】■集計表の行ラベルに、「株式、現金、貴金属」などのアセット以外の「#N/A」「空白」等の表記がありますが、それらの時価評価額が「0」であれば、行ラベルのフィルター(逆三角ロート型)を押して、それらのチェックを外してください。■逆に、それらの時価評価額が「0」でなく数値が入っていれば、コード表への未入力(ミス)が考えられます。

※ 注意1のチェック箇所
※ 注意1のチェック箇所

 この間違いの原因は、理由がわかればなんでもないのですが、最初は、表示されていないのでなかなかわかりません。

 ですから、ピボットテーブルでの集計値が合わない場合は、フィルターを3カ所だけでなく、「すべて選択」にチェックを入れて全体を表示させ「エラーの中(または空白)」に数値が入ってないかを確認してください。

かん

  特に私の株式投資テンプレートの場合、不揃いデータを集計するという、一見すると邪道なやり方ですから、エラー表示や空白表示はあって当然で、参照元のデータソースに多数あります。ですから、最初の段階で、フィルターでエラー表示や空白表示ハズして設定して配信していますので、充分注意をしてください。

フィルター機能を「すべて選択」し間違い箇所がないかを確認する(エラー表示や空白に数値があれば間違い)

【Excel教本に載ってない・誰も教えてくれない】エクセルの失敗あるある:ピボットテーブル集計が合わない①

 そうすれば、下の図のように、エラー表示の「#N/A」に「20万円」の表示があることがわかります。

【Excel教本に載ってない・誰も教えてくれない】エクセルの失敗あるある:ピボットテーブル集計が合わない①

「#N/A」に「20万円」表示の箇所と原因の確認

 このエラー表示の「#N/A」に「20万円」の表示がある部分をクリックすれば、別シートに、「#N/A」が全て表示され、その中に「20万円」その箇所が表示されます。それが、下の図です。

【Excel教本に載ってない・誰も教えてくれない】エクセルの失敗あるある:ピボットテーブル集計が合わない①
【Excel教本に載ってない・誰も教えてくれない】エクセルの失敗あるある:ピボットテーブル集計が合わない①

 その箇所を元のデータベースで見ると、下の図のように、エラー表示となっており、その原因が、

  • VLOOKUP関数が参照する「コード表」に新規の銘柄「00-KKSBI証券」が未登載

であったため、ということがわかりました。

【Excel教本に載ってない・誰も教えてくれない】エクセルの失敗あるある:ピボットテーブル集計が合わない①

【エクセル:エラー事例】ピボットテーブルの集計(合計・総計)が参照元データの集計と合わない場合の原因と対策

【原因】 

原因は

  • VLOOKUP関数の参照先の「コード表」に新規の銘柄「00-KKSBI証券」が未登載

でした。ですから、その銘柄をコード表に登載すれば問題は解決します。

【対策】~新規銘柄をコード表へ搭載

 コード表への搭載方法は下記を参照してください。

株式投資【Excelテンプレート】コード表の作り方
コード表の作成方法(株管理ポートフォリオ作成用テンプレート)ポートフォリオ作成用テンプレートの中のコード表の説明です。このコード表の特徴は、オリジナルの項目を作ることにより、思い通りの角度からMy資産の見える化可視化が可能になることです。 証券会社のWEBサイトや市販のアプリでは思い通りの見える化可視化や分析ができないと思われている方に向いています。 社員あの方で出口戦略や親子2世代運用でお悩みの方もどうぞご利用ください。...

 VLOOKUP関数については←ことらを(Microsoftサポートより)

まとめ:【エクセル:エラー事例】ピボットテーブルの集計(合計・総計)が参照元データの集計と合わない場合の原因と対策

 今回の記事は、こんな人におすすめの記事です。

こんな人にオススメ
  • 仕訳した参照元データ(データソース)をピボットテーブルで種類別に集計をしたが、集計値(合計)が参照元データの集計値(合計)と合わない、とエラーで悩んでいる人
  • 「範囲の更新忘れ」や「範囲指定の間違い」が原因ではなく、参照元データと集計(合計)が合わない原因が分からない人

Excelのデータベースをピボットテーブルで集計したとき、参照元データ(データソース)の集計値とピボットテーブルの集計値が合わない(一致しない)原因は、そのほとんどが

  1. エラー表示(空白も含む)の中に答えがある(この記事)
  2. 「範囲の更新忘れ」か「範囲指定の間違い」(別記事)

の2つです。

そのうち最初の

  • ①の「エラー表示(空白も含む)の中に答えがある場合」

をこの記事で紹介しました。

そのエラー表示の原因は、

  • VLOOKUP関数の参照先の「コード表」に新規の銘柄「00-KKSBI証券」が未登載

だったからでした。

わかれば簡単なことなんですが、データが多くなるとわかるまでに意外と時間を要することがあります。しばらくしないと再度おなじ間違いをするExcelあるあるですから、しっかりと覚えておいて下さいね。

【関連記事】そのほか、ピボットテーブルのよるあるミスを下に書き出して記事にしましたので参照してください。

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

 株管理ポートフォリオ作成の基本記事はコチラです。

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

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

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

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

 

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

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

2022年7月27日
らくぶる
更新(2023/02/19):配信テンプレートの修正更新(【C】データベースの列ADの「銘柄」を【B】コード表からVLOOKUP関数で引く …
【NISA枠・拡大拡充に備えて】株式投資信託・資産運用管理ポートフォリオ作成用Excelテンプレートの使い方(老後資金の親子2世代運用版)【SBI証券・楽天証券】<無料>※スプレッドシート対応 2-3-1.NISAの割合を知りたい人

【NISA枠・拡大拡充に備えて】株式投資信託・資産運用管理ポートフォリオ作成用Excelテンプレートの使い方(老後資金の親子2世代運用版)【SBI証券・楽天証券】<無料>※スプレッドシート対応

2022年12月11日
らくぶる
【更新】2023/01/30 Googleスプレッドシート版のダウンロード追加 今後のNISA枠の拡大・拡充により、一個人のNISA …
【おすすめ】マネーフォワードMEの便利な使い方:【図解エクセル】<無料>最速1分>株価暴落を想定したポートフォリオの作り方とテンプレートの使い方(リバランスのやり方)【SBI・楽天証券等・親子2世代運用】 2-2-1.暴落下落リスク

【おすすめ】マネーフォワードMEの便利な使い方:【図解エクセル】<無料>最速1分>株価暴落を想定したポートフォリオの作り方とテンプレートの使い方(リバランスのやり方)【SBI・楽天証券等・親子2世代運用】

2022年6月4日
らくぶる
更新(2023/02/19):配信テンプレートの修正更新(【C】データベースの列ADの「銘柄」を【B】コード表からVLOOKUP関数で引く …