Excel动态数组FILTER实现股票组合持仓成本追踪

核心思路 #

直接基于原始交易记录，通过公式自动完成以下步骤：

• 生成从交易起始日到今日的所有月末日期序列
• 对每个月末日期，筛选该日期前的全部交易
• 按TICKER+EXCHANGE分组，提取每组最新的累计持仓数据
• 过滤掉已清仓（累计数量和成本均为0）的记录
• 整合所有月度持仓数据，附加对应月末日期字段，输出标准化表格

完整公式 #

=LET(
    // 1. 定义数据源（根据实际表格调整范围和字段位置）
    Transactions, 交易记录!A:F, // 需包含列：DATE, TICKER, EXCHANGE, ..., CUMULATIVE UNITS, CUMULATIVE COST
    Headers, INDEX(Transactions, 1, 0),
    Data, INDEX(Transactions, 2, 0):INDEX(Transactions, ROWS(Transactions), 0),
    // 2. 获取时间边界
    StartDate, MIN(INDEX(Data, 0, 1)),
    EndDate, TODAY(),
    // 3. 生成月度月末日期序列
    MonthEnds, EOMONTH(StartDate, SEQUENCE(DATEDIF(StartDate, EndDate, "M") + 1, 1, 0)),
    // 4. 遍历每个月末日期生成持仓数据
    Result, REDUCE(
        // 初始化表头：新增「月末日期」字段
        HSTACK("月末日期", FILTER(Headers, Headers={"TICKER","EXCHANGE","CUMULATIVE UNITS","CUMULATIVE COST"})),
        MonthEnds,
        LAMBDA(acc, currentDate,
            LET(
                // 筛选当前月末前的所有交易
                FilteredTrades, FILTER(Data, INDEX(Data, 0, 1) <= currentDate),
                // 按股票+交易所分组，取每组最新交易记录
                UniquePairs, UNIQUE(INDEX(FilteredTrades, 0, 2) & "|" & INDEX(FilteredTrades, 0, 3)),
                LatestHoldings, MAP(
                    UniquePairs,
                    LAMBDA(pairKey,
                        LET(
                            SplitKey, TEXTSPLIT(pairKey, "|"),
                            Ticker, SplitKey[1],
                            Exchange, SplitKey[2],
                            PairTrades, FILTER(FilteredTrades, (INDEX(FilteredTrades,0,2)=Ticker)*(INDEX(FilteredTrades,0,3)=Exchange)),
                            SortedTrades, SORT(PairTrades, 1, -1), // 按日期降序排序
                            TAKE(SortedTrades, 1) // 取最新一条交易
                        )
                    )
                ),
                // 过滤已清仓的记录（累计数量/成本不为0）
                ActivePositions, FILTER(LatestHoldings, (INDEX(LatestHoldings,0,5)<>0)+(INDEX(LatestHoldings,0,6)<>0)),
                // 附加当前月末日期，合并到结果集
                OutputRow, IF(ROWS(ActivePositions)>0, HSTACK(REPT(currentDate, ROWS(ActivePositions)), INDEX(ActivePositions,0,2), INDEX(ActivePositions,0,3), INDEX(ActivePositions,0,5), INDEX(ActivePositions,0,6)), ""),
                VSTACK(acc, IF(OutputRow<>"", OutputRow, ""))
            )
        )
    ),
    // 5. 清理结果中的空行
    FILTER(Result, INDEX(Result, 0, 1) <> "")
)

公式拆解说明 #

• LET函数：集中定义所有变量，大幅提升公式的可读性和计算效率，避免重复引用数据源
• MonthEnds序列生成：通过EOMONTH+SEQUENCE自动生成从交易起始月到当前月的所有月末日期，无需手动维护日期列表
• REDUCE+LAMBDA遍历：逐个处理每个月末日期，累计生成所有月度的持仓数据
• MAP分组取最新：对每个TICKER+EXCHANGE组合，筛选对应交易后按日期降序，提取最新的累计持仓数据，确保数据是截止到月末的最新状态
• 持仓过滤逻辑：用(A<>0)+(B<>0)实现逻辑或判断，保留任意一个指标不为0的持仓记录，排除已清仓的股票
• 数据整合：通过HSTACK添加月末日期字段，VSTACK合并所有月度数据，最后用FILTER清理空行，输出整洁的结果表格

使用注意事项 #

• 需确保使用的Excel版本支持动态数组函数（Excel 365/2021及以上）
• 请根据实际表格结构调整Transactions的范围，以及字段对应的列索引（比如DATE在第1列，TICKER在第2列等）
• 生成的结果会随TODAY()自动更新，无需手动刷新，可直接用于制作折线图、柱状图等分析图表

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