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

【エクセル:エラー事例】ピボットテーブルの集計(割合・構成比)が合わない場合のエラーの原因と対処方法(事例③:計算の種類の選択ミス)

【エクセル:エラー事例】ピボットテーブルの集計(割合・構成比)が合わない場合のエラーの原因と対処方法
記事内に商品プロモーションを含む場合があります

株管理ポートフォリオ作成用のExcelテンプレートでピボットテーブルのデータを使っているとき、集計(割合・構成比)に間違いがあることに気がつきました。

【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)
※株式投資サンプルデータより
じゃなさん

ウン、たしかにおかしい。株式と投信は、時価評価額は殆ど同じなのに、時価評価額(%)は、10倍くらいちがう。どういうことじゃ?

この赤枠の中は、ポートフォリオの集計(割合・構成比)のつもりでしたが、よく見ると割合(構成比・%表示)が明らかに間違っていることがわかりました。

※この原因がわかる方は、これ以上この記事を読む必要がありません。

今回の記事は、ピボットテーブルの集計(割合・構成比)が間違っている場合のエラーの原因と対処方法を紹介します。

こんな人にオススメ

Excelのピボットテーブルの操作で、ピボットテーブルの集計(割合・構成比)が間違っている場合のエラーの原因と対処方法がわからなくて困っている人

当ブログのテンプレートの場合、この割合(構成比)がポートフォリオやアセットアロケーションの一番の重要な点ですから、確実に理解しておいて下さい。

【エクセル:エラー事例】ピボットテーブルの集計(割合・構成比)が合わない場合のエラーの原因

この原因は、下の集計表を見てもらえばわかると思います。(数字は文頭の集計表と同じ)

【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)
※株式投資サンプルデータより

2つの集計表の違いは、

  1. 文頭の図に、「時価評価額(%)」とある部分が
  2. 直上の図では、「個数/時価評価額」となっている

ことです。これらは、

  1. 文頭の図の「時価評価額(%)」は、私が書き換えた任意のフィールド名
  2. 直上の図の「個数/時価評価額」は、書き換え前の規定のフィールド名

です。

このことから、ピボットテーブルの集計(割合・構成比)が間違っていた原因は、

  • 参照元データの「時価評価額の合計金額」の構成比を算出しなければいけないところ、同データの「時価評価額のある商品の個数」の構成比を集計してしまった

ということでした。

かん

数値データの範囲が数値データのみであれば、デフォルトは「合計」になりますが、空欄や文字列があれば「データの個数」になります。

じゃなさん

そういうことじゃな。このブログで配信しているテンプレートは、不揃いデータだから、集計範囲にたしかに空欄や文字列があれるので「合計」でなく「個数」になってしまうんじゃな。

かん

ピボットテーブルを一度つくって「更新」だけでいけば問題ないんですが、再設定したときなど「個数」から「合計」に変更し忘れると、冒頭の失敗事例のようになります。気を付けて下さいね。

【エクセル:エラー事例】ピボットテーブルの集計(割合・構成比)が合わない場合のエラーの対処方法

 その対処方法を一言で言えば

  • ピボットテーブルの「時価評価額(%)」の訂正~「時価評価額のある商品の個数」の構成比を集計を「時価評価額の合計金額」の構成比に換える

ということです。

具体的な方法は次の通りです。

① ピボットテーブルの中にカーソルを置き右クリックする

【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)

② 「値フィールドの設定」をクリックする

【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)

③ 「値フィールドの設定」をクリックし、集計方法が「個数」に設定されていることを確認

【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)

④ 「値フィールドの設定」の集計方法を「個数」(誤り)から「合計」(正解)に変更

【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)

⑤ 「名前の指定」を「合計/時価評価額」を任意の「時価評価額(%)」(⑥)に変更

⑥~⑧「名前の指定」を「時価評価額(%)」に変更した後、「計算の種類」タブ(⑦)をクリックし、⑧の「計算の種類」のプルダウンメニューをクリックする

【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)

⑨~⑩「計算の種類」のプルダウンメニューをクリックして、「総計に対する比率」を選び「OK」をクリックする

【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)

⑪「時価評価額の合計金額」の構成比に変更され無事解決する

【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)

まとめ:【エクセル:エラー事例】ピボットテーブルの集計(割合・構成比)が合わない場合のエラーの原因と対処法

 今回の記事は、

こんな人にオススメ

Excelのピボットテーブルの操作で、ピボットテーブルの集計(割合・構成比)が間違っている場合のエラーの原因と対処方法がわからなくて困っている人

に向けた記事です。

今回の原因

  • 参照元データの「時価評価額の合計金額」の構成比を算出しなければいけないところ、同データの「時価評価額のある商品の個数」の構成比を集計してしまった

でした。

対策(対処方法)は、

  • ピボットテーブルの「時価評価額(%)」の訂正~「時価評価額のある商品の個数」の構成比を集計を「時価評価額の合計金額」の構成比に換える

ことでした。

わかれば簡単なことなんですが、わかるまでに意外と時間を要することがあります。しばらくしないと再度おなじ間違いをする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テンプレートを多数公開していますので、ぜひお使いください。ピボットテーブルで集計をしており、使いやすさ、分かりやすさを追求して作成しております。  参考になれば幸いです。...

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

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

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

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

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

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

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

2022年8月16日
らくぶる
かん 今回はシニア高齢者の代表者のじゃなさんのMy資産の一部にバランス型の投資信託を保有した場合、全体の株価暴落リスクと円高為替 …
【おすすめ】公開記事<全部>「株管理ポートフォリオ(円グラフ)作成&株価暴落/為替円高リスクの見える化ができるExcelテンプレートの使い方」記事一覧(SBI・楽天証券・NISA対応・Googleスプレッドシート)<無料> 2.株管理ポートフォリオとテンプレート

【おすすめ】公開記事<全部>「株管理ポートフォリオ(円グラフ)作成&株価暴落/為替円高リスクの見える化ができるExcelテンプレートの使い方」記事一覧(SBI・楽天証券・NISA対応・Googleスプレッドシート)<無料>

2022年9月20日
らくぶる
じゃなさん 今回は、公開してる記事全部のリストってことじゃな? かん そうです。今回の記事は、2022年4月から私が配信してきたテ …
【おすすめ】暴落を想定した株管理ポートフォリオ(円グラフ)の作り方とリバランスのやり方の記事一覧(SBI・ネオモバイル・楽天証券・NISA対応・Googleスプレッドシートあり)記事一覧<無料> 2-2-1.暴落下落リスク

【おすすめ】暴落を想定した株管理ポートフォリオ(円グラフ)の作り方とリバランスのやり方の記事一覧(SBI・楽天証券・NISA対応・Googleスプレッドシートあり)記事一覧<無料>

2023年2月13日
らくぶる
かん 今回の記事は、当ブログの中の「暴落想定」の「まとめ記事」です。 もし明日、大幅な株価下落や暴落があると、現在の資産がどのくら …