LiteFolio開発チームです。株のポートフォリオをエクセルで管理したいけれど、どんな列を作ればいいのか、平均取得単価はどう計算すればいいのか分からない——そんな悩みをよく聞きます。結論として、取引記録シートと保有一覧シートを分け、必要な列を最小限に絞れば、投資記録のエクセルは自作できます。この記事ではシート設計から数式、そして自作エクセルが続かなくなったときの選択肢まで、具体的に解説します。
記録の手間を減らしたい方向けに、手入力型の記録アプリ「LiteFolio」を公開中です
LiteFolioの詳細を見る →エクセル管理は「シートを分ける」ことから始まる
株のポートフォリオをエクセルで管理する際、最初に決めるべきことは「取引記録シート」と「保有一覧シート」を分けることです。理由は単純で、1枚のシートに買付・売却・保有状況をすべて詰め込むと、行が増えるたびに集計式が崩れやすくなるためです。
例えば、A銘柄を3回に分けて買い、1回売却したとします。この履歴を1枚のシートに時系列でそのまま書いていくと、「今いくら持っているか」を知るには毎回自分で該当行を目で探して足し引きする必要が出てきます。これでは銘柄数が増えるほど破綻します。
そこで、取引記録シートには「いつ何をしたか」だけを時系列で追記し、保有一覧シートには「今どうなっているか」を数式で自動計算する、という役割分担にします。この2枚構成が、エクセルでの投資記録の基本形です。
取引記録シートに必要な列構成
取引記録シートに必要な列は、日付・銘柄・売買区分・数量・単価・手数料の6つです。理由は、この6項目さえあれば平均取得単価も損益も後から計算できるためです。逆にこれ以上増やすと入力の手間が増え、続けにくくなります。
具体的な列の例は次のとおりです。
売買区分は「買付」「売却」の文字列で入れる方法と、買付を+1、売却を-1として数量に符号を持たせる方法があります。後者にしておくと、後述の集計式がシンプルになるため、慣れないうちはD列を「区分」、E列の数量に符号を持たせるやり方がおすすめです。
配当・入出金・分割も、同じ取引記録シートに「取引種別」の列を1つ追加して混在させる方法と、別シートに分ける方法があります。件数が少ないうちは種別列で十分ですが、後ほど配当だけの集計が煩雑になる点は後述します。
複数の証券口座を使っている場合は「口座」列を追加する
複数の証券口座で同じ銘柄を持っている場合、取引記録シートに「口座」列を追加しておくと、後から口座別・全体別の両方で集計できます。列を追加せずに口座ごとにシートを分けてしまうと、全体のポートフォリオを見るために毎回シートを行き来することになります。
複数の証券口座をまとめて管理する方法を見る →保有一覧シートで平均取得単価と損益を出す数式
保有一覧シートの目的は、銘柄ごとに「今の保有数量」「平均取得単価」「評価損益」を自動表示することです。これができれば、取引記録シートに1行追記するだけで保有一覧が更新される仕組みが完成します。まず基本の考え方を、単純な数式で押さえておきましょう。
平均取得単価の基本形
平均取得単価は、「買付にかかった総額 ÷ 買付した総数量」で求められます。3回に分けて買った場合の考え方は、次の加重平均の形が基本です。
これをシート上の数式にすると、銘柄ごとの合計金額を合計数量で割る形になります。銘柄コードでフィルタしながら合計する場合は、SUMIF() を組み合わせるのが基本です。
ここでの注意点は、売却分を含めて割ってしまわないことです。売却は数量が減るだけで、平均取得単価そのものは（一部売却の場合）変わりません。売買区分ごとに範囲を分けて集計するか、「区分＝買付」の条件を必ずSUMIFに入れる必要があります。
より詳しい計算パターン（一部売却時の扱いや複数回の買付が混在する場合の考え方）は、平均取得単価の計算方法で個別に解説しています。
実現損益の基本形
売却時の実現損益は、「売却金額 −（平均取得単価 × 売却数量）− 手数料」で求められます。売却した行に対して、その時点の平均取得単価を参照する数式を組んでおくと、売却するたびに損益が自動で出ます。
配当記録の持ち方は「銘柄・日付・金額・税」を分けて残す
配当記録は、銘柄・受取日・受取金額・税額を最低限の列として別枠で残すのが基本です。理由は、配当は買付・売却と数式の構造が異なり、同じ集計ロジックに混ぜると平均取得単価の式が崩れやすいためです。
配当記録シート（または取引記録シート内の配当専用の行群）には、次の列があると年間の受取配当を集計しやすくなります。
年間の受取配当合計は、この配当記録シートに対して SUMIFS() で年を条件にすれば集計できます。配当の記録・管理の考え方をより詳しく知りたい方は、配当金の記録・管理方法も参考にしてください。
自作エクセルが続かなくなる4つの限界
ここまでの設計で投資記録エクセルは十分に機能しますが、運用を続けるうちにスマホでの入力・外出先での閲覧・数式の破損・配当や分割の管理という4つの限界に必ず当たります。これはエクセルの作り方が悪いわけではなく、表計算ソフトの構造上避けにくい問題です。
具体的には、証券口座を見ながらスマホでスプレッドシートに数値を打ち込むのは列がずれやすく時間もかかります。外出先で保有状況をパッと確認したくても、クラウド版でも開くまでのタップ数が多い。行の挿入や範囲選択のミスでSUMIFの参照範囲がずれ、集計が合わなくなる。株式分割が起きると、過去の取引記録すべての数量・単価を手動で調整し直す必要が出てきます。
これらの限界は、記録を続ける意欲そのものとは無関係に発生します。数式が壊れているのに気づかないまま古いデータを見続けてしまうリスクもあり、精度を求めるほどエクセル管理の保守コストは上がっていきます。
数式の保守から離れて、手入力の記録だけに集中したい方はLiteFolioもご検討ください
LiteFolioについて詳しく見る →エクセルの次の選択肢は「手入力アプリ」
エクセルの限界に当たったときの選択肢は、証券口座と連携する自動取得型のアプリに乗り換えるか、エクセルと同じ「自分で入力する」主導権を保ったまま、集計だけを自動化する手入力型アプリに移るかの2択です。前者は楽ですが、口座連携に抵抗がある人や、複数口座・NISA枠などを自分の分類で管理したい人には向きません。
手入力型アプリであれば、エクセルで組んでいた「日付・銘柄・売買区分・数量・単価・手数料」という記録項目はそのまま引き継げます。違いは、平均取得単価や実現損益の計算式を自分で組む必要がなく、入力するだけで自動的に反映される点です。口座連携なしで使えるアプリの考え方は口座連携なしで使える投資管理アプリでも解説しています。実際の画面はこのようなイメージです。
エクセルでの投資記録は、取引記録シートと保有一覧シートを分け、6つの列と加重平均の数式で組むのが基本形です。まずは自分の手でここまで作ってみることで、自分にとって本当に必要な記録項目が見えてきます。その上で入力の手間や数式の保守が負担になってきたら、記録項目を変えずに集計だけを自動化する手入力アプリへの移行を検討する、という順番がスムーズです。
LiteFolio
買付・売却・配当・入出金・手数料・株式分割を手入力で記録する、証券口座に接続しない投資記録アプリです。エクセルと同じ「自分で入力する」自由さを保ったまま、平均取得単価・実現損益・資産配分などの集計を自動化します。データはまず端末に保存されるローカルファーストです。iOS・Androidで公開中です。
LiteFolioについて詳しく見るLiteFolioは記録・可視化ツールであり、投資助言を行うものではありません。
よくある質問
エクセルとGoogleスプレッドシート、どちらで管理すべきですか？
シート設計や数式の考え方はどちらでも同じです。外出先でスマホから確認・追記する機会が多い方はスプレッドシート、PCでの操作が中心で数式を凝りたい方はエクセルが向いています。どちらも数式の保守やスマホ入力のしづらさという限界は共通して発生します。
株式分割があった場合、エクセルはどう直せばいいですか？
分割比率に応じて、対象銘柄の過去の取引記録すべての数量を分割後の比率で掛け、単価を同じ比率で割ります。行数が多いほど手作業の負担が増えるため、分割が多い銘柄を持つ場合はこの調整が管理コストの大きな部分を占めます。
複数の証券口座をまとめてエクセルで管理できますか？
できます。取引記録シートに「口座」列を追加し、保有一覧シートの集計条件に口座を加えれば、口座別と全体の両方を同じシートから見られます。詳しくは複数の証券口座をまとめて管理する方法で解説しています。
LiteFolioは無料で使えますか？
LiteFolioはiOS・Androidで公開中です。料金プランの最新情報は、App StoreまたはGoogle Playでご確認ください。クラウド同期などの一部機能は有料プラン（Pro）として提供される予定ですが、基本の記録機能の料金体系は現時点で確定していません。最新情報はLiteFolioのページでご確認ください。