如何在筛选行中使用INDEX MATCH实现Excel加权计算？

核心需求 #

基于两张表格计算筛选后可见行的加权平均值：

• 表1：映射Letter到对应因子值
• 表2：每行Tag匹配表1Letter，需计算「数值×对应因子」的总和，再除以对应因子的总和

原全量计算公式在筛选表2后失效，以下是适配方案：

方案1：Excel 365/2021 动态数组公式（推荐） #

=LET(
    visibleTags, FILTER(A13:A17, SUBTOTAL(103, OFFSET(A13, ROW(A13:A17)-ROW(A13), 0, 1))),
    visibleValues, FILTER(B13:B17, SUBTOTAL(103, OFFSET(A13, ROW(A13:A17)-ROW(A13), 0, 1))),
    factors, XLOOKUP(visibleTags, A3:A6, B3:B6),
    SUMPRODUCT(visibleValues, factors) / SUM(factors)
)

原理说明：

1. SUBTOTAL(103, OFFSET(...))：生成判断数组，可见行返回1，隐藏行返回0（103代表忽略隐藏行的COUNTA统计）
2. FILTER：提取表2中可见行的Tag和数值
3. XLOOKUP：匹配可见Tag对应的因子值
4. 最后按需求计算加权平均，自动适配筛选状态

方案2：兼容旧版Excel公式 #

=SUMPRODUCT(B13:B17, INDEX(B3:B6, MATCH(A13:A17, A3:A6, 0)), SUBTOTAL(103, OFFSET(A13, ROW(A13:A17)-ROW(A13), 0, 1))) / SUMPRODUCT(INDEX(B3:B6, MATCH(A13:A17, A3:A6, 0)), SUBTOTAL(103, OFFSET(A13, ROW(A13:A17)-ROW(A13), 0, 1)))

原理说明：

• 用SUBTOTAL(103,...)作为可见性判断权重，和数值、因子一起传入SUMPRODUCT，自动忽略隐藏行的计算
• 分子：计算可见行「数值×因子」的总和
• 分母：计算可见行对应因子的总和

验证示例 #

当筛选表2中Tag=A的行时，公式会自动计算：
(3*0.5 + 5*0.5) / (0.5 + 0.5) = 4，完全符合需求。

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