私達の周囲にあるいろいろな数字を集計する方法として、Excel（Googleスプレッドシートを含む）の集計機能が活用されていますが、不揃いのデータを目の前にして
- 不揃いのデータをExcelのデータベースして扱えないか？
- Excel教本・教科書を見ても不揃いのデータの集計・可視化はどこにも載っていない？
- 誰も教えてくれない？
と悩んでいる方が多いかと思います。そんな悩みを持つ方に向けて事例付きで記事を作成しましたので紹介します。
なお、この方法は完成形でもエラー表示をそのまま表示させていますので、「エクセル：エラー事例」としてタイトルを付けました。
現在、シニア投資初心者として、エクセルやスプレッドシートのピボットテーブルで株式投資のポートフォリオの可視化をするテンプレートの配信（楽天SBI証券・NISA対応）を主体にしたブログを書いていますが、そもそもを考えたとき、サラリーマン時代に、様々な不揃いなデータをExcelで揃えて統一し集計可視化見える化する仕事をしていたことから、証券会社WEBサイトのデータを見たとき、「これならば簡単に見える化ができるな」と思ったのが株式投資Excelテンプレート作成の始まりでした。
振り返って見て、「こんなやり方が載っているExcel教本はないだろう、誰も教えてくれないだろう」と思い、今回、基本的な流れを押さえ、基本的な事例を紹介する記事を書きました。何かお役に立てば幸いです。
不揃いデータとは、要するに「テーブル機能」が使えないデータベースということじゃな。
現在、当ブログでは証券会社WEBサイトのデータを取得（コピペ）して集計し可視化する「株式投資用のExcelテンプレート（楽天SBI証券・NISA対応）（Googleスプレッドシート版あり）」を配信していますので、それを事例に説明します。
【エクセル：エラー事例】不揃いデータからピボットテーブルとVLOOKUP関数で株管理ポートフォリオ(円グラフ)を作成する方法の「基本的な流れ」と「注意事項」～株式投資Excelテンプレート作成に関して
基本的な流れ（不揃いデータを揃えるExcelテンプレート作成から可視化まで）
- 不揃いのデータのコピペ～証券会社のWEBサイト上のデータをコピーし、Excelにテキスト貼付をおこなう。
- 不揃いのデータの１行化（データベース化）～①のデータのうち必要データを関数や数式を利用して１行に並べてデータベース化をする。
- オリジナルな仕訳～別途コード表を作成しVLOOKUP関数で読み込む。
- 集計や可視化（見える化）～②のデータベースを使っておこなう。
注意事項（経験談より：不揃いデータを揃えるExcelテンプレート作成の各事例に共通）
- Excelテンプレートのデータベースは、大筋、左から右への流れで作成する。※思考通りに左から右へ
- ただし、Excelテンプレートのデータベースの列は多め（余分）に確保しておく。（後で列挿入しなくていいようにしておく。）※列挿入は多大な時間の無駄遣い
- データの１行化（データベース化）の際は、別会社との連結（合体）を見据えて、データベースの中の主要なデータは同じ列に揃えておく。※これは大原則
- 当然、時系列（変遷・推移）対応のためにも、上記の事項全ては重要。※時系列でデータを連結させる必要があるため
- 上記のデータベースではテーブル機能は使わない、そもそも使えない。※統一された関数数式が使えないため＆色分けがわかりやすさのために意外と重要となるため（コード表ではテーブル機能を採用）
- 不要データは、ピボットテーブルのフィルター機能等で排除する。
証券会社３社の事例（不揃いデータを揃える株式投資Excelテンプレート）
それぞれ3社の証券会社の事例は、下記の通りです。これが一番の基本です。
【図解エクセル】株式資産の見える化テンプレの使い方（楽天証券用）・1人分
【図解エクセル】株式資産の見える化テンプレの使い方（SBI証券用）・1人分
【図解Excel】ネオモバイル証券のテンプレートにヤフーファイナンスのポートフォリオを利用する方法（2022年9月のヤフーファイナンスの様式変更後に対応）・1人分
証券会社３社データを連結し一体化した事例は、下記の通りです。
【図解Excelテンプレート】【ネオモバ×Yahooファイナンス】株式投資でMy資産ポートフォリオの見える化ができる証券3社合体版の基本テンプレートの使い方・1人分
コード表は下記を参照してください。
マネーフォワードMEを使って証券会社３社合体して見える化した事例（不揃いデータを揃える株式投資Excelテンプレート）
マネーフォワードMEを使った証券会社3社合体の株式投資Excelテンプレートでポートフォリオの見える化
【図解エクセル】【マネーフォワードME版】エクセルで資産管理ポートフォリオの見える化！（楽天証券＋SBI証券＋SBIネオモバイル証券）テンプレートの使い方
注意事項（プライバシーポリシー等）
マクロは使っておらず、カスタマイズは可能です。
ダウンロードは自由ですが、ご利用は自己責任でお願いします。
※ダウンロードの際は、プライバシーポリシー/免責事項/著作権/リンク等を参照下さい。
当ファイルを利用することにより生じた損害等の一切の責任を負いかねますのでご了承ください。
質問や要望などありましたら、お問い合わせフォームにご連絡ください。できるだけのことはさせていただきます。
まとめ：【エクセル：エラー事例】不揃いデータからピボットテーブルとVLOOKUP関数で株管理ポートフォリオを作成する方法
私と同じようなシニアの方で、不揃いのデータを目の前にして
- 不揃いのデータをExcelのデータベースとして扱えないか？
- Excel教本・教科書を見ても不揃いのデータの集計・可視化はどこにも載っていない？
- 誰も教えてくれない？
と悩んでいる方も多いのではないかと思います。
今回、証券会社の株式投資Excelテンプレートを事例として使いましたが、基本的に
- 不揃いのデータのコピペ～証券会社のWEBサイト上のデータをコピーし、Excelにテキスト貼付をおこなう。
- 不揃いのデータの１行化（データベース化）～①のデータのうち必要データを関数や数式を利用して１行に並べてデータベース化をする。（テーブル機能が使えない）
- オリジナルな仕訳～別途コード表を作成しVLOOKUP関数で読み込む。
- 集計や可視化（見える化）～②のデータベースを使っておこなう。
などの方法は、他のデータにも応用できますので、活用してみてください。
この方法でやれば、マクロなど使っておらず、意外と簡単に集計や見える化が行えます。
先ほども言いましたが、振り返って見て「こんなやり方を教えてくれるExcel教本はないだろうな、誰も教えないだろうな」と思い、そんな観点から記事を書きました。何かお役に立てば幸いです。
【関連記事】
そもそも論から株式投資Excelテンプレートを作成するようになった理由
株管理ポートフォリオ作成の基本記事は(他社分あり）
親子2世代運用についてはコチラ↓
投資信託の内訳を含めたポートフォリオの作り方は、コチラ↓
リスク別テンプレート一覧についてはコチラ↓
これまでに公開したテンプレートに関する記事一覧はコチラです。
※【楽天市場】楽天市場で「エクセル・ピボットテーブル」関連の商品購入はこちらから↗
※【メルカリ】メルカリで「エクセル・ピボットテーブル」関連の商品購入はこちらから↗
