Google Sheets中自动追踪3只ETF组合变动（忽略排序）的公式需求

核心需求 #

在包含月度3只排名型ETF的数据集里，自动计算每月与上月的ETF变动数（忽略排序，仅统计新增/退出的ETF数量），且公式能随新数据添加自动扩展。

解决方案公式 #

方案1：兼容旧版的数组公式（推荐）

将以下公式放在E2单元格（对应"变动计数"列的第二行）：

=ARRAYFORMULA(IF(ROW(A:A)=1,"变动计数",IF(A:A="","",IF(ROW(A:A)=2,"",3-MMULT(--(ISNUMBER(MATCH(B2:D,OFFSET(B2:D,-1,0,ROWS(B:D)-1),0))),SEQUENCE(3,1,1,0))))))

方案2：用BYROW简化逻辑（需Google Sheets支持LAMBDA函数）

同样放在E2单元格：

=ARRAYFORMULA(IF(A:A="","",IF(ROW(A:A)=1,"变动计数",IF(ROW(A:A)=2,"",BYROW(B2:D,LAMBDA(r,3-COUNTA(IFERROR(MATCH(r,OFFSET(r,-1,0,1,3),0)))))))))

公式说明 #

1. 自动扩展逻辑：ARRAYFORMULA 实现批量计算，新添加月份数据时，公式会自动覆盖新行，无需手动下拉。
2. 空值/表头处理：
   • 第一行自动填充"变动计数"表头
   • 空行或无月份数据的行返回空值
   • 1月（第二行）因无上月数据，返回空值
3. 变动数计算核心：
   • 通过MATCH+ISNUMBER判断本月每个ETF是否存在于上月的3只ETF中
   • 统计匹配成功的数量（即交集大小），用总数量3减去该值，得到新增/退出的ETF总数（每退出1只必然对应新增1只，变动数等于两者之和）

示例验证 #

代入你提供的数据集：

• 2月与1月：交集3，3-3=0，符合仅排序无变动的结果
• 3月与2月：交集2，3-2=1，符合1只ETF变更的结果
• 4月与3月：交集3，3-3=0，符合仅排序无变动的结果
• 5月与4月：交集1，3-1=2，符合2只ETF变更的结果

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