SUMPRODUCT函数满足“或”的要求，实现多条件求和！

Excel情报局

职场联盟Excel

生产挖掘分享Excel基础技能

Excel爱好者大本营

用1%的Excel基础搞定99%的职场问题

做一个超级实用的Excel公众号

Excel是门手艺玩转需要勇气

数万Excel爱好者聚集地

SUPER EXCEL MAN

    

1

职场实例

小伙伴们大家好，今天我们来继续讲解Excel使用中非常实用且强大的函数：SUMPRODUCT函数，上一次我们讲到了SUMPRODUCT函数实现类似SUMIFS函数多条件求和的功能。而今天我们要学习的是：如果多个条件是“或”的关系，而不单单必须是“且”的关系，如何实现多条件求和呢？

多个条件“且”的含义是：

当所有条件都成立时，返回结果为true，数值表示为1，反之有任意一个不成立，则返回false，数值表示为0。

多个条件“或”的含义是：

当所有条件中有任意一个是成立的，返回结果为true，如果都不成立，才返回false。

这个问题的解决实际上已经超出了SUMIFS函数的使用范围了！

如下图所示：

A1:D9单元格区域为一份人员性别年龄销量表，我们想要在F2单元格单元格统计出男性人员或者年龄大于30岁的人员的销量总和。

例如：张三、赵五、赵七、吴十都是男性，所以其销量204、230、204、201都符合汇总要求；而周九虽然是女性，但是其年龄符合大于30岁的条件，所以其销量132也符合汇总要求，故符合条件的销量总和为：204+230+204+201+132=971

2

解题思路

今天我们着重讲解利用SUMPRODUCT函数配合SIGN函数实现多个条件是“或”的关系的条件求和，下面我们就来看一下具体操作方法。

SUMPRODUCT函数在给定的几组数组中，把数组间对应的元素相乘，最后返回乘积之和。

SUMPRODUCT常规函数公式：

=sumproduct（数组1,数组2,数组3, ……）

数组里面的相应元素进行相乘后，再将乘积求和。

常规运算过程如下演示：

=SUMPRODUCT({1;2;3},{4;5;6})

=1*4+2*5+3*6

=32

首先我们在F2单元格输入函数公式：

=(B2:B9="男")+(C2:C9>30)

其实这一步主要做的是一系列的逻辑判断，来获取相应的数组元素。

我们选中公式部分：

(B2:B9="男")

按下F9键，查看数组返回元素：

{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE}

即该区域单元格如果等于“男”，即返回逻辑真值TRUE，否则返回逻辑假值FALSE。

我们选中公式部分：

(C2:C9>30)

按下F9键，查看数组返回元素：

{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}

即该区域单元格数值大于30的话，即返回逻辑真值TRUE，否则返回逻辑假值FALSE。

(B2:B9="男")+(C2:C9>30)两个逻辑判断进行相加：

{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE}+{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}

在Excel中我们都知道逻辑值TRUE用数字1代表，逻辑值FALSE用数字0代表。两个数组相加，即各个元素会依次进行相加，得到新的数组：

={1;0;1;0;1;0;0;1}+{1;0;1;0;1;0;1;0}

={2;0;2;0;2;0;1;1}

我们发现新的数组结果：

={2;0;2;0;2;0;1;1}

如果两个条件都符合的话会返回数字2，即两个TRUE相加等于2；如果两个条件都不符合的话会返回数字0，即两个FALSE相加等于0；如果两个条件有其一符合的话会返回数字1，即一个TRUE加一个FALSE等于1。

即只要数组元素结果大于0的就符合“或”的要求。但是数组元素2不属于逻辑值范畴，所以我们可以将其转换为1即可。

这里我们使用的是SIGN函数：

=SIGN((B2:B9="男")+(C2:C9>30))

SIGN函数用于返回数字的符号。当数字大于0时返回1，等于0时返回0，小于0时返回-1。

所以SIGN函数的运算过程会如下演化：

=SIGN({2;0;2;0;2;0;1;1})

={1;0;1;0;1;0;1;1}

所以只要符合“或”条件的，数组元素全部转换成了固定的逻辑值数字“1”。

最后用SUMPRODUCT函数的常规思路就可以了：

=SUMPRODUCT(SIGN((B2:B9="男")+(C2:C9>30)),D2:D9)

将第2参数D2:D9展开，实质就是用数组引用了D列的销量数据：

{204;208;230;236;204;288;132;201}

SUMPRODUCT函数的运算过程：

=SUMPRODUCT({1;0;1;0;1;0;1;1},D2:D9)

=SUMPRODUCT({1;0;1;0;1;0;1;1},{204;208;230;236;204;288;132;201})

SUMPRODUCT函数会对数组里面的相应元素进行相乘后，再将乘积求和。

=1*204+0*208+1*230+0*236+1*204+0*288+1*132+1*201

=971

回顾关键内容，善用图片表达，学会建立联系，拓展深度广度，浓缩关键概念，应用到行动中，善于归纳总结，尝试进行分享。