用 Excel 做投资记录和分析，关键不在于把表格做得多么花哨，而在于建立"交易流水 → 持仓汇总 → 收益率分析 → 资产配置"这样一套能互相勾稽的数据链。下面是一套经过验证的实操框架。

️ 整体架构：分表不混表

建议在一个工作簿里建 4 张工作表，各司其职：

工作表；作用；核心字段

交易流水​；逐笔记录所有买卖；日期、标的代码/名称、买卖方向、价格、数量、手续费、账户、买卖理由

持仓总览​；自动汇总当前持仓；标的、持有份额、持仓成本价、总成本、最新净值/市价、当前市值、浮动盈亏、盈亏比例、持仓占比

收益分析​；多维度衡量成果；统计周期、期间买入/卖出、期末市值、绝对收益、期间收益率、已实现/未实现收益分离

资产配置​；看钱分布在哪；资产类别（股票/ETF/固收/现金/黄金）、市值、占比、与目标配置的偏离

交易流水是唯一的手工入口，其他三张表通过公式从流水里自动抓取数据——这样能避免数据多处维护导致的不一致。

模块一：交易流水表（最基础的"事实层"）

这是整个系统的地基，每一笔交易记一行，不要合并单元格，不要空行。

建议字段：

基础信息：日期、标的代码、标的名称、买卖方向（买/卖）

成交信息：价格、数量、手续费/佣金

金额：=价格×数量+手续费（买入为正流出，卖出为流入）

标签：资产类别（股票/基金/ETF/固收）、策略类型（价值/成长/技术/定投）

复盘字段：买入理由、卖出理由、是否按计划执行

理由栏尽量写可验证的条件（如"PE低于行业均值且ROE连续3年>15%"），而不是"看好""别人推荐"。这是后续复盘能不能定位问题的关键。

对于基金定投场景，额外加两列：SIP 金额（每期定投额）和分红/股息到账，这两类现金流会直接影响最终的真实收益率。

模块二：持仓总览表（自动汇总的"现状层"）

这张表的核心是平均成本法处理多次买卖：

持仓成本价​ = 总成本 ÷ 总份额（同一标的多次买入时必须用加权平均，不能用首次买入价）

当前市值​ = 持有份额 × 最新净值

浮动盈亏​ = 当前市值 - 总成本

持仓占比​ = 当前市值 ÷ 全组合总市值

最新净值/市价的更新有两种方式：

手动更新：定期从基金公司官网或行情软件查了填进去，简单可靠

数据导入：Excel「数据」选项卡的 Power Query / 获取数据功能，可从财经网站批量拉取——但需要注意第三方数据源的稳定性

⚠️ 自动联网获取行情虽然省事，但可能因数据源变动而失效，重要决策前务必人工核对一次。

模块三：收益率计算（最容易算错的环节）

不要用简单的 (当前市值-总投入)÷总投入​ —— 当你有多次不定额投入、部分赎回时，这种算法会严重失真。

正确做法：用 XIRR 函数

微软官方对 XIRR 的定义是"返回一组不一定定期发生的现金流的内部收益率"，正是为投资场景设计的（定期现金流才用 IRR）。

操作步骤：

A 列放日期，B 列放现金流金额

投入记为负值（钱流出），赎回/当前市值记为正值（钱流入）

最后一行用今天日期 + 当前组合市值作为正数

空白处输入：=XIRR(B2:B100, A2:A100)

示例（5 个月定投后赎回）：

日期；现金流

2024-02-01；-8000

2024-03-01；-8000

2024-04-01；-8000

2024-05-01；-8000

2024-06-01；-8000

2024-07-01；44500

公式 =XIRR(B2:B7, A2:A7) 返回的就是考虑时间价值的真实年化收益率。

关键提醒：

必须有至少一个正值和一个负值，否则 XIRR 报 #NUM! 错误

日期必须用 DATE 函数或真实日期格式，文本日期会出错

当前市值必须作为最后一笔正现金流纳入，否则算出来的是不完整的

还要区分两类收益

已实现收益：已卖出部分带来的盈亏

未实现收益：账面浮动盈亏

两者要分开列示，因为已实现收益才是真金白银到手的钱，未实现收益会随市价波动。

模块四：资产配置与可视化

在「资产配置」表里用 SUMIF 按资产类别汇总市值和占比，然后：

插入饼图看大类资产分布（股/债/现金/黄金）

插入柱状图看各行业、各市场的暴露

在持仓总览表里用条件格式——盈利标绿、亏损标红，一眼定位强弱项

目标配置和实际配置并列摆放，差额列用条件格式高亮，方便你判断是否需要再平衡。

进阶分析：让 Excel 真正帮你"复盘"

基础表搭好后，可以加入这几个分析维度：

1. 回撤监控

记录账户净值高点和当前净值，最大回撤 = (高点-当前)÷高点。当回撤突破心理线时强制自己做动作，而不是跌完才后悔。

2. 时间维度收益

用数据透视表按"年/季/月"分组统计收益率，看清自己在哪段时间赚钱、哪段时间亏钱。

3. 策略归因

在交易流水里给每笔交易打"策略标签"，期末用透视表看哪类策略的胜率和盈亏比最高——这是复盘最有价值的部分。

4. 单笔交易复盘

对每笔已平仓交易计算持有天数、盈亏比例，结合当初写的"买入理由"和"卖出理由"，回头检验逻辑是否兑现。

⚙️ 几个让表格更好用的技巧

数据验证下拉框：给"买卖方向""资产类别""策略类型"做下拉列表，避免手工输入不一致

数据透视表：快速按标的、时间段、策略维度切分统计数据

Goal Seek（单变量求解）：反推"每月需要投多少才能达到目标退休金"

Scenario Manager（方案管理器）：模拟牛市/熊市/震荡市下组合的表

⚠️ 常见踩坑提醒：

忘记把"当前市值"作为正现金流放进 XIRR —— 算出来完全不对

投入/赎回的正负号搞反 —— XIRR 必错

手续费没计入 —— 长期会累积明显偏差

只记单只标的盈亏，不看组合整体 —— 局部最优不等于全局最优

落地建议

如果你是第一次搭，不要追求一步到位。第一周先把"交易流水表"用起来，把历史交易补录进去；第二周加入"持仓总览"和 XIRR 计算；第三周再做资产配置和分析透视。等这三张表跑顺了，你已经超过了 90% 的散户——因为绝大多数人亏钱的根源就是"记不住历史、握不住仓位、控不住情绪"，而这套表恰好治这三个毛病。

需要的话，我可以针对你的具体场景（A股股票为主 / 基金定投为主 / 美股港股混合）给一份更贴合的字段设计和公式模板，告诉我你的主要投资品种就行。

发布于16小时前 增城