Excel中如何对布尔数组取反并在SUMPRODUCT公式中应用？

嘿，我明白你想做什么了——原来的公式是把R列匹配W5到AF5中任意一个值的行，对应的S列加起来，现在要反过来，只加那些R列完全不匹配W5:AF5里任何值的S列数据对吧？你的两个尝试没成功是有原因的，咱们先理清楚问题，再给你几个优雅的解法。

问题根源：你误解了数组运算的逻辑 #

你的原公式=SUMPRODUCT(($R$2:$R$9000=$W$5:$AF$5)*($S$2:$S$9000))里，$R$2:$R$9000=$W$5:$AF$5会生成一个9000行×N列的数组（N是W到AF的列数，比如W到AF是10列的话就是9000×10）。SUMPRODUCT会把这个数组里所有为True（即匹配成功）的位置，对应乘以S列的值，然后全部加起来——这相当于“只要匹配到任意一个W5:AF5的值，就把S列加一次（甚至多次，如果匹配多个值的话）”。

而你用NOT或者<>的时候，生成的也是同样结构的多列数组：只要R列单元格不等于W5:AF5里的某一个值，对应位置就是True，SUMPRODUCT会把这些所有True的情况都加进去，这显然不是你要的“完全不匹配任何值”的结果。

优雅解决方案 #

方案1：用COUNTIF判断“不在集合中”（最直观） #

这个方法逻辑最清晰，直接检查R列单元格是否在W5:AF5这个集合里，不在的话就计入求和：

=SUMPRODUCT(--(COUNTIF($W$5:$AF$5,$R$2:$R$9000)=0)*$S$2:$S$9000)

• COUNTIF($W$5:$AF$5,$R$2:$R$9000)：返回每个R列单元格在W5:AF5中出现的次数，等于0就说明完全不匹配。
• --：把逻辑值True/False转换成1/0，这样才能和S列数值相乘。

方案2：用PRODUCT实现“全不匹配”的逻辑（纯数组运算） #

如果你偏好纯数组操作，不想用COUNTIF，可以用PRODUCT来判断每一行是否所有条件都满足（即R列不等于W5:AF5的每一个值）：

=SUMPRODUCT(PRODUCT(--($R$2:$R$9000<>$W$5:$AF$5),2)*$S$2:$S$9000)

• --($R$2:$R$9000<>$W$5:$AF$5)：把“不等于”的逻辑值转成1（不匹配）和0（匹配）。
• PRODUCT(...,2)：按行计算乘积——如果某一行里有一个0（即匹配了某个值），乘积就是0；只有所有都是1（全不匹配），乘积才是1，这样就只会把符合要求的S列值加进去。

方案3：嵌套SUMPRODUCT判断匹配次数 #

还可以用嵌套SUMPRODUCT来统计每一行的匹配次数，只有次数为0的行才计入求和：

=SUMPRODUCT(--(SUMPRODUCT(--($R$2:$R$9000=$W$5:$AF$5),2)=0)*$S$2:$S$9000)

• 内层的SUMPRODUCT(--($R$2:$R$9000=$W$5:$AF$5),2)：按行统计匹配成功的次数，次数为0就是完全不匹配。

再解释下你的尝试为什么失败 #

• =SUMPRODUCT(NOT($R$2:$R$9000=$W$5:$AF$5)*($S$2:$S$9000))：NOT会把每个匹配的False转成True，不匹配的True转成False，但SUMPRODUCT会把多列数组里所有True的位置都算进去——比如一个R列单元格等于W5但不等于X5，那这个单元格对应的行里会有一个False和多个True，相乘后会被多次累加，结果肯定不对。
• =SUMPRODUCT(($R$2:$R$9000<>$W$5:$AF$5)*($S$2:$S$9000))：和上面同理，只要不等于其中一个值就会生成True，SUMPRODUCT会把这些情况都加起来，而不是只保留“全不匹配”的行。

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