Google Sheets中按人员筛选后对投标金额唯一值求和的问题

数据集 #

我的尝试 #

给John写的公式：

=if(Person Column = "John", sum(unique(Bid Amount Column)), "Error")

给Bob写的公式：

=if(Person Column = "Bob", sum(unique(Bid Amount Column)), "Error")

预期结果 #

实际结果 #

问题很明显：John的公式直接对所有金额去重求和（$10+$20+$15），完全没考虑人员过滤；Bob的公式直接报错，根本没生效。

解决方案 #

方案1：Excel 365/2021及以上版本（用FILTER+UNIQUE+SUM）

如果你的Excel支持动态数组函数，直接用下面的公式（假设单独统计表格的A列是人员姓名，B2对应John，下拉即可）：

=SUM(UNIQUE(FILTER(原表!D:D,原表!A:A=A2,0),,TRUE))

• FILTER(原表!D:D,原表!A:A=A2,0)：先筛选出当前人员的所有投标金额
• UNIQUE(...,TRUE)：开启"完全匹配"模式，确保同一人员的同一项目重复投标只保留一条金额
• SUM：对去重后的金额求和

单独写John的公式就是：

=SUM(UNIQUE(FILTER(D:D,A:A="John",0),,TRUE))

Bob的同理：

=SUM(UNIQUE(FILTER(D:D,A:A="Bob",0),,TRUE))

方案2：兼容旧版Excel（用SUMPRODUCT+COUNTIFS）

如果是旧版Excel不支持动态数组，用这个公式：

=SUMPRODUCT((原表!A:A=A2)*原表!D:D,1/COUNTIFS(原表!A:A,原表!A:A,原表!C:C,原表!C:C))

• COUNTIFS(原表!A:A,原表!A:A,原表!C:C,原表!C:C)：统计每个「人员-项目」组合的出现次数
• 1/：把重复项转为分数（比如重复2次就是0.5），SUMPRODUCT相乘后，重复的金额只会被计算一次
• 最终实现同一项目只算一次金额的求和

原公式出错原因 #

1. IF条件无效：Person Column = "John"只是单个单元格的判断，没有遍历整个人员列，IF只会判断当前单元格是否等于John，而sum(unique(Bid Amount Column))直接对全列去重求和，自然包含了Bob的$15，结果就是$45。
2. Bob公式报错：要么是旧版Excel不支持UNIQUE函数，要么是引用范围错误，导致IF条件不成立时返回"Error"，或者函数本身无法运行。

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