ぼくは資産関係のデータを自身で入力して分析して管理しているのですが、こんなときに気をつける点があります。それは「データベース的な発想でデータを入力」し「ピボットテーブルを使ってデータとビューを切り分ける」ということです。
柔軟すぎる表計算ソフト
ExcelにせよGoogleスプレッドシートにせよ、表計算ソフトのいいところはその柔軟性です。一つのシートに複数の表を作れますし、ワープロのようにいろんな要素を書き込んだり、セル連結して見栄えを整えることもできます。
でも使い捨てのちょっとした表を作るだけならまだしも、毎月内容を更新するようなデータを扱いたいなら、このやり方はダメです。発想を転換しなくてはいけません。
実はぼくが最初に作っていた資産管理表も、ダメシートの典型でした。下記の黒くなっているところには実際の金額が入っていたのですが、まぁよくある表ではあります。
何がダメかというと、
- 同じシートに2つ以上の表がある
- 表の中で集計したり計算している
- 新しいデータを新たなカラムとして追加するようになっている（右に伸びていく）
あたりでしょうか。
こういう設計だと、別の観点で集計したくなったら、数字をコピペして再集計する羽目になってしまいます。数字の並べかえもそのままだとできません。要するに、データの再利用がとってもやりにくいシートなのです。
どんな考え方で表を用意するか
ではどんな考え方でデータを作ったらいいのでしょうか。反省に立って用意し直したのが、現在使っている下記の表です。実際には「数量」と「単価」のところには数字が入っています。
この表のポイントはいくつかあります。
- 取引所ごとの銘柄を一単位として1つのレコード（横の一列）とする
- 各レコードには「銘柄」「取引所」「数量」「所得単価」「単価」「建て通貨」「集計月」「ドル円レート」「銘柄の分類」を記入する
この表の中で、数量 x 単価 x ドル円レート で円建ての集計結果を計算して入力していますが、そのほかの数字は手入力です。また、HYGは「債券」で「海外債券」だというような銘柄の分類は、VLOOKUPで別タブを参照して自動入力させています。
このような表を用意し、実際の分析はピボットテーブルを使います。例えば、このデータに対し、
- 行に「建て通貨」
- 列に「集計月」
- 値に「集計」
でピボットテーブルを作ると、下記のように、ドル建て資産、円建て資産、仮想通貨建て資産の月別集計が一瞬ででます。さらに、そのときのドル円レートもピボットテーブルで出力するようにすれば、右のようなグラフが自動的に更新されていきます。
株式の集計だって簡単です。今度は下記の条件でピボットテーブルを作ります。
- 行に「銘柄」
- 列に「集計月」
- 値に「単価」
- フィルタで「銘柄分類」のうち「株式」
でてきた数字に対してB列とC列の比を計算すれば、それだけで月次リターンがでます。最初のデータさえちゃんと入力すれば、それだけでグラフも自動的に更新されるわけです。
こうやって毎月作成しているのが、ぼくの運用成績のコンテンツ。ほんとグラフ盛りだくさんのコンテンツですが、実は前月のレコードをコピーして追加し、日付を変更、単価を修正するだけで、すべてのグラフが更新されるという仕組みになっています。
この形に移行してから、グラフづくりにはまったく手がかかっておらず、やっているのはそのグラフを見ての分析だけ。一度仕組みを作り上げると便利になる典型です。
データベース的発想
さて、このように「データを入力するシート」と「ピボットテーブルで分析したりグラフを描くシート」を分けるのは、プログラミングでは一般的な考え方です。データとビューの分離といいましょうか、データはデータベースに入れて、ビューのほうはデータベースからデータを引っ張ってきて表示する形です。
これはデータベースを使ったことがある人には当たり前だと思いますが、Excelとかスプレッドシートとか表計算メインの人には意外と慣れていないもの。ポイントはデータの設計と運用です。
と、ぼくは自分で試行錯誤して上記のようなDB的な運用に至ったわけですが、まあ誰でも同じことは考えるわけで、玉川陽介氏の最新作『Excelでできる 不動産投資「資産管理」のすべて』に、そのものズバリこうしたデータベース的なデータを作成するための注意点が載っています。
- 「〃」や「セル結合」を使わず、同じ値を並べる
- 1行ごとに独立したデータとして作成する
下記のような表を作るとき、つい「同じ楽天だから縦のセルを結合しよう」なんて思ってはいけません。毎行、毎回、「楽天」と繰り返し入力するのがポイントです。
- 最小単位の項目で作成する：例えば税込価格のリストを作るか本体価格と消費税を別項目にすべきか。迷ったら分離。あとから合算は簡単だけど分離はできない
- 表記のゆらぎを防ぐ。固有名詞はユニークIDを付与して番号で管理
- 名前の定義を活用
- 集計することを見越してデータリストに集計用フラグを用意：例えば住居、事務所、店舗などの用途フラグ、入居と空室の入居フラグ
このあたりは、実務でバリバリ使っていることがよく分かる内容です。銘柄などは表記ゆらぎが発生しがちで、本来は別表でID管理して、それを参照するようにすべきですが、ぼくはまだそこまでできていません。
- 連続したデータリストを作成する：年度別にシートを分割したくなる気持ちは分かりますが、分けると複数年度をまとめた集計ができなくなる
これも重要です。ぼくのシートはすでに4000行を超えていますが、シートを分けると年度別の分析ができなくなります。データを分割していないから、例えば「VTの4年分の月次単価の推移を抜き出してグラフにする」なんてこともピボットテーブルだけで簡単にできるわけです。
- データを色分けしない
- 入力規則に従う（例えば「実施日」のセルに「2023年10月1日（ただし鍵の引き渡しは翌日）」などと入れてはいけません。実施日には日付だけを入れて、コメントセルにただし鍵の引き渡しは翌日と入れる）
- セルのメモやコメント機能は使わない：抽出や集計が困難
- 数値入力欄に単位を入れない（23.4と入力。23.4m2としない）
- 空欄を作らない（存在しないならゼロ、該当なしならハイフンなど）
データの色分けも基本NGです。ピボットテーブルでは色は参照できませんから。ただデータを入力するときのメモ用には色付は便利で、僕はコピーした銘柄の単価を入力していくときに、入力済みのセルにだけ色をつけてヌケモレがないようにしています。
このように、データとビューを分けると、柔軟に利用できるデータから、提出用の帳票も簡単に作れるようになります。決して帳票に最初から数字を入れようとしないこと。それがExcelなど表計算ソフトをうまく活用するポイントです。
