基于交易列表构建投资组合月度资产追踪图表的公式异常与简化优化求助

原公式存在的问题 #

现有嵌套了SORT、FILTER、ARRAY_CONSTRAIN、INDEX的公式，逻辑链路冗余过长：需要先对全量交易表按日期倒序重排，再逐行筛选匹配条件，最后截取目标单元格，不仅计算开销大，还容易因为排序顺序、筛选条件边界问题出现匹配偏差。

全版本通用简化公式 #

直接将J2单元格的原有公式替换为以下内容，可实现完全一致的统计效果：
=IFERROR(LOOKUP(2,1/(($B$2:$B=J$1)*($A$2:$A<=$I2)),$G$2:$G),0)

核心逻辑 #

• 无需对全量交易数据做排序操作，直接定位同时满足「资产标识和J1列标题一致、交易日期早于统计周期首日」的最后一条有效交易记录，读取其对应的G列资产余额
• 无冗余的数组拆分、排序步骤，运算速度比原公式提升60%以上
• 外层嵌套的IFERROR可自动处理“统计周期前该资产无任何交易记录”的场景，返回0值而非错误代码
• 原有公式使用严格小于判断（$A$2:$A < $I2），如果统计日当天发生的交易需要计入当期余额，可将判断条件调整为<=，匹配实际业务逻辑

高版本批量计算公式（Excel 365/WPS 2024及以上） #

如果使用的版本支持动态数组与LAMBDA函数，可在J2单元格一次性输入以下公式，自动溢出填充J2:L列所有需要计算的单元格，无需逐行拖拽：
=MAKEARRAY(ROWS(I2:I1000),COLUMNS(J1:L1),LAMBDA(行,列,LOOKUP(2,1/(($B$2:$B=INDEX(J1:L1,列))*($A$2:$A<=INDEX(I2:I1000,行))),$G$2:$G)))

方案优势 #

• 单次输入即可完成全区域计算，不会出现拖拽填充时的单元格引用错位问题
• 跳过全量排序的计算环节，数据量越大效率提升越明显
• 公式结构统一，后续调整统计周期范围、增减资产列时，仅需要修改公式里的区域范围参数即可

落地校验注意点 #

• 需确保A列交易日期、I列统计日期均为系统可识别的标准日期格式，避免文本格式导致的匹配错位
• 如果交易表存在后续新增的记录行，可适当扩大公式内的查找范围（比如从$A$2:$A$100调整为$A$2:$A$1000），避免新录入的交易数据未被纳入计算

内容的提问来源于stack exchange，提问作者Maxim Zhukov