株管理ポートフォリオ作成用のExcelテンプレートでピボットテーブルのデータを使っているとき、集計(割合・構成比)に間違いがあることに気がつきました。
![【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)](https://rakuenblog.com/wp-content/uploads/2023/01/image-4.png)
ウン、たしかにおかしい。株式と投信は、時価評価額は殆ど同じなのに、時価評価額(%)は、10倍くらいちがう。どういうことじゃ?
この赤枠の中は、ポートフォリオの集計(割合・構成比)のつもりでしたが、よく見ると割合(構成比・%表示)が明らかに間違っていることがわかりました。
※この原因がわかる方は、これ以上この記事を読む必要がありません。
今回の記事は、ピボットテーブルの集計(割合・構成比)が間違っている場合のエラーの原因と対処方法を紹介します。
Excelのピボットテーブルの操作で、ピボットテーブルの集計(割合・構成比)が間違っている場合のエラーの原因と対処方法がわからなくて困っている人
当ブログのテンプレートの場合、この割合(構成比)がポートフォリオやアセットアロケーションの一番の重要な点ですから、確実に理解しておいて下さい。
目次
【エクセル:エラー事例】ピボットテーブルの集計(割合・構成比)が合わない場合のエラーの原因
この原因は、下の集計表を見てもらえばわかると思います。(数字は文頭の集計表と同じ)
![【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)](https://rakuenblog.com/wp-content/uploads/2023/01/image-6.png)
2つの集計表の違いは、
- 文頭の図に、「時価評価額(%)」とある部分が
- 直上の図では、「個数/時価評価額」となっている
ことです。これらは、
- 文頭の図の「時価評価額(%)」は、私が書き換えた任意のフィールド名
- 直上の図の「個数/時価評価額」は、書き換え前の規定のフィールド名
です。
このことから、ピボットテーブルの集計(割合・構成比)が間違っていた原因は、
- 参照元データの「時価評価額の合計金額」の構成比を算出しなければいけないところ、同データの「時価評価額のある商品の個数」の構成比を集計してしまった
ということでした。
数値データの範囲が数値データのみであれば、デフォルトは「合計」になりますが、空欄や文字列があれば「データの個数」になります。
そういうことじゃな。このブログで配信しているテンプレートは、不揃いデータだから、集計範囲にたしかに空欄や文字列があれるので「合計」でなく「個数」になってしまうんじゃな。
ピボットテーブルを一度つくって「更新」だけでいけば問題ないんですが、再設定したときなど「個数」から「合計」に変更し忘れると、冒頭の失敗事例のようになります。気を付けて下さいね。
【エクセル:エラー事例】ピボットテーブルの集計(割合・構成比)が合わない場合のエラーの対処方法
その対処方法を一言で言えば
- ピボットテーブルの「時価評価額(%)」の訂正~「時価評価額のある商品の個数」の構成比を集計を「時価評価額の合計金額」の構成比に換える
ということです。
具体的な方法は次の通りです。
① ピボットテーブルの中にカーソルを置き右クリックする
![【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)](https://rakuenblog.com/wp-content/uploads/2023/01/image-7.png)
② 「値フィールドの設定」をクリックする
![【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)](https://rakuenblog.com/wp-content/uploads/2023/01/image-9.png)
③ 「値フィールドの設定」をクリックし、集計方法が「個数」に設定されていることを確認
![【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)](https://rakuenblog.com/wp-content/uploads/2023/01/image-8.png)
④ 「値フィールドの設定」の集計方法を「個数」(誤り)から「合計」(正解)に変更
![【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)](https://rakuenblog.com/wp-content/uploads/2023/01/image-10.png)
⑤ 「名前の指定」を「合計/時価評価額」を任意の「時価評価額(%)」(⑥)に変更
⑥~⑧「名前の指定」を「時価評価額(%)」に変更した後、「計算の種類」タブ(⑦)をクリックし、⑧の「計算の種類」のプルダウンメニューをクリックする
![【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)](https://rakuenblog.com/wp-content/uploads/2023/01/image-11.png)
⑨~⑩「計算の種類」のプルダウンメニューをクリックして、「総計に対する比率」を選び「OK」をクリックする
![【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)](https://rakuenblog.com/wp-content/uploads/2023/01/image-12.png)
⑪「時価評価額の合計金額」の構成比に変更され無事解決する
![【Excel教本にない失敗事例】ピボットテーブルの集計(割合・構成比)が合わない場合の原因と対処方法(事例③:計算の種類の選択ミス)](https://rakuenblog.com/wp-content/uploads/2023/01/image-14.png)
まとめ:【エクセル:エラー事例】ピボットテーブルの集計(割合・構成比)が合わない場合のエラーの原因と対処法
今回の記事は、
Excelのピボットテーブルの操作で、ピボットテーブルの集計(割合・構成比)が間違っている場合のエラーの原因と対処方法がわからなくて困っている人
に向けた記事です。
今回の原因は
- 参照元データの「時価評価額の合計金額」の構成比を算出しなければいけないところ、同データの「時価評価額のある商品の個数」の構成比を集計してしまった
でした。
対策(対処方法)は、
- ピボットテーブルの「時価評価額(%)」の訂正~「時価評価額のある商品の個数」の構成比を集計を「時価評価額の合計金額」の構成比に換える
ことでした。
わかれば簡単なことなんですが、わかるまでに意外と時間を要することがあります。しばらくしないと再度おなじ間違いをするExcelあるあるですから、忘れないでくださいね。
特に、当ブログのテンプレートの場合、この割合(構成比)がポートフォリオやアセットアロケーションの一番の重要な点ですから、確実に理解しておいて下さい。
【関連記事】
【要注意!】エクセル・スプレッドシート及びピボットテーブルの操作方法で注意する点
![要注意!【エクセル:エラー事例】株管理ポートフォリオ(円グラフ)作成用のエクセル・スプレッドシート及びピボットテーブルの使い方で注意する点](https://rakuenblog.com/wp-content/uploads/2022/11/20221123-portfolio-matome-huzoroi-excel-spreadsheet-pivot-becareful-320x180.jpg)
![【エクセル:エラー事例】ピボットテーブルで参照元データの範囲指定の失敗をなくす3つの方法](https://rakuenblog.com/wp-content/uploads/2022/11/20221105-portfolio-matome-huzoroi-excel-spreadsheet-pivot-area-miss-320x180.jpg)
![【エクセル:エラー事例】ピボットテーブルの集計(合計・総計)が参照元データの集計と合わない、集計できない場合の原因と修正方法(事例②:「範囲の更新忘れ」or「範囲指定の間違い」)](https://rakuenblog.com/wp-content/uploads/2022/10/20221025-portfolio-matome-huzoroi-tyuui-2-320x180.jpg)
株管理ポートフォリオ作成の基本記事
![【いまさら聞けない基本の「き」】エクセルで株管理ポートフォリオ(円グラフ)の作り方とExcelテンプレートの使い方の基本の記事5本<全て無料>(SBI・楽天証券・2024年の新NISA移行対応予定)※Googleスプレッドシート版あり](https://rakuenblog.com/wp-content/uploads/2022/11/20221130-portfolio-matome-kihonnoki-excel-spreadsheet-320x180.jpg)
親子2世代運用のポートフォリオ作成テンプレート使い方の記事一覧
![【おすすめ】老後資金は親子2世代で資産運用・Excelテンプレートで株管理ポートフォリオ(円グラフ)作成」記事一覧【SBI・楽天証券・NISA対応】Googleスプレッドシート版も有<無料>](https://rakuenblog.com/wp-content/uploads/2022/11/20221113-portfolio-mieruka-excel-spreadsheet-howtouse-template-article-list-of-2sedai-unyou-320x180.jpg)
これまでに公開したテンプレートに関する記事一覧はコチラです。
※【楽天市場】楽天市場で「エクセル・ピボットテーブル」関連の商品購入はこちらから↗
※【メルカリ】メルカリで「エクセル・ピボットテーブル」関連の商品購入はこちらから↗