如何基于映射表匹配（VLOOKUP返回TRUE）计算指定数据的平均值与求和

模拟数据结构前提 #

假设你的表格布局如下：

• A列：水果名称（包含apple、orange）
• B列：对应的数据数值
• C列：用于VLOOKUP匹配的关键字
• 匹配参照表区域为$E$2:$F$10：E列存关键字，F列存匹配结果（需匹配结果为"test"）

1. 符合条件的apple数据求和 #

使用SUMPRODUCT实现多条件求和，同时满足「A列为apple」和「VLOOKUP返回"test"」两个条件：

=SUMPRODUCT((A:A="apple")*(VLOOKUP(C:C, $E$2:$F$10, 2, FALSE)="test")*B:B)

逻辑说明 #

• (A:A="apple")：筛选A列是apple的行，符合返回1，否则0
• (VLOOKUP(C:C, $E$2:$F$10, 2, FALSE)="test")：对每行C列关键字执行精确匹配VLOOKUP，结果为"test"则返回1，否则0
• 两个条件相乘后再乘以B列数值，最终SUMPRODUCT对所有符合条件的数值求和

2. 符合条件的apple数据平均值 #

通过「求和结果」除以「符合条件的行数」计算平均值：

=SUMPRODUCT((A:A="apple")*(VLOOKUP(C:C, $E$2:$F$10, 2, FALSE)="test")*B:B)/SUMPRODUCT((A:A="apple")*(VLOOKUP(C:C, $E$2:$F$10, 2, FALSE)="test"))

提示：若没有符合条件的行，公式会返回#DIV/0!，可嵌套IFERROR处理：=IFERROR(上述公式, 0)

3. orange的求和与平均值 #

仅需将公式中的"apple"替换为"orange"即可：

• 求和公式：

=SUMPRODUCT((A:A="orange")*(VLOOKUP(C:C, $E$2:$F$10, 2, FALSE)="test")*B:B)

• 平均值公式：

=SUMPRODUCT((A:A="orange")*(VLOOKUP(C:C, $E$2:$F$10, 2, FALSE)="test")*B:B)/SUMPRODUCT((A:A="orange")*(VLOOKUP(C:C, $E$2:$F$10, 2, FALSE)="test"))

高效替代方案（辅助列+SUMIFS/AVERAGEIFS） #

如果数据量较大，数组运算的SUMPRODUCT效率偏低，可新增辅助列简化操作：

1. 在D2单元格输入公式=VLOOKUP(C2, $E$2:$F$10, 2, FALSE)="test"，下拉填充至所有行，D列将返回TRUE/FALSE标记是否符合VLOOKUP匹配"test"的条件
2. apple求和：=SUMIFS(B:B, A:A, "apple", D:D, TRUE)
3. apple平均值：=AVERAGEIFS(B:B, A:A, "apple", D:D, TRUE)
4. orange的计算只需将公式中的"apple"替换为"orange"即可

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