大家好，我是永不止步的老牛

今天我们来了解下Excel的Sumproduct函数的实用用法

 Sumproduct函数的语法是：

Sumproduct(array1,[array2],[array3],...)，其中

array1：必需，表示表示一个数组

array2：可选，表示表示一个数组

array3,...：可选，表示表示一个数组

这里要注意一下

  • 参数是多个数组，但最多不能超过255个

  • 参数最少要有一个数组，就是说第一个参数是必需项

  • 多个参数时，数组必需具备相同的维度，否则Sumproduct函数返回错误

  • 会自动将非数值型数组元素按0来处理

就是说这个函数将多个数组对应元素相乘，并返回乘积之和，如果只有一个数组，那么返回这个数组各元素之和。

用例子来解释一下

Sumproduct({1;2;3})：返回的是1+2+3=6

Sumproduct({1;2;3},{1;2;3})：返回的是1*1+2*2+3*3=1+4+9=14

Sumproduct({1;2;3},{1;2;3},{1;2;3})：返回的是1*1*1+2*2*2+3*3*3=1+8+27=36

我们用例子演示一下Sumproduct函数使用的使用场景

1. 基础用法

A． 一个参数

 统计销售数量总数

=SUMPRODUCT(F4:F13)

表示计算F4:F13的各元素之和，就是1+12+1+8+8=5+5+8+1+2=53

B． 两个参数

 统计销售额总数

=SUMPRODUCT(E4:E13,F4:F13)

表示计算E4:E13和F4:F13各元素乘积之和

就是4999*3+3599*12+2888*1……4999*1+3599*2=204593

C． 三个参数

 统计提成总数

=SUMPRODUCT(E4:E13,F4:F13,G4:G13)

表示计算E4:E13、F4:F13和G4:G13各元素乘积之和，就是

4999*3*0.05+3599*12*0.2+2888*1*0.05……4999*1*0.05+3599*2*0.05=2786.84

2. 带条件计算

A． 单条件求和

统计电视机的销售数量

=SUMPRODUCT((C4:C13="电视机")*F4:F13)

C4:C13="电视机"：表示在C4:C13区域内逐项和“电视机”比较，等于返回TRUE，不等于返回FALSE，

用返回的数组{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}和F4:F13对应元素相乘，并返回乘积之和

就是0*3+0*12+1*1+0*8+0*8+0*5+1*5+1*8+0*1+0*2=14

SUMPRODUCT函数会充分用到TRUE等于1，FALSE等于0

B． 多条件求和

统计销售一部电视机的销售数量

=SUMPRODUCT((B4:B13="销售一部")*(C4:C13="电视机")*F4:F13)

多条件和单条件原理一样，只是多进行了一次比对，更多条件只需要增加对应比对项

C． 模糊条件求和

统计电脑和电视机的销售数量

=SUMPRODUCT(ISNUMBER(FIND("电",C4:C13))*F4:F13)

FIND函数：表示在C4:C13各单元格内中查找包含“电”的产品，找到就返回在单元格的位置，没找到就返回错误#VALUE!

FIND("电",C4:C13)：返回的是{1;#VALUE!;1;#VALUE!;1;1;1;1;1;#VALUE!}

这里的#VALUE!导致我们没法计算，所以再用ISNUMBER函数转换一下

ISNUMBER函数：判断是不是数值，是就返回TRUE，不是就返回FALSE

ISNUMBER(FIND("电",C4:C13))：返回的是

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

现在就和单条件一样了，两个数组对应元素相乘，并返回乘积之和

D． 单条件计数

 统计电视机的销售员人数

=SUMPRODUCT(N(C4:C13="电视机"))

C4:C13="电视机"：返回的是

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

如果只有这一个数组，数组里面不是数字，而是TRUE或FALSE，

我们前面说过：SUMPRODUCT函数会自动将非数值型数组元素按0来处理

所以如果不加处理，结果将会返回0，计算不正确了

处理办法很多，包含TRUE和FALSE的数组，和运算符运算都会自动变成1和0

所以我们只要给(C4:C13="电视机")*1，就会变成{0,0,1,0,0,0,1,1,0,0}

那+1可以让TRUE和FALSE变成数值吗？可以，但是结果就不对了

(C4:C13="电视机")+1，就会变成{1,1,2,1,1,1,2,2,1,1}

还可以加“--”，变成“--(C4:C13="电视机")”，结果也是{0,0,1,0,0,0,1,1,0,0}

“--”：就是将不能参与计算的字符转换成可以计算的数字

还可以用N函数，

N函数：将不是数值形式的值转换为数值形式，TRUE转换成1，FALSE转换成0，日期转换成序列值，其他的值转换成0

N((C4:C13="电视机"))：返回的是{0,0,1,0,0,0,1,1,0,0}

那么N函数和“--”的区别是，对SUMPRODUCT来说，区别在：

—(“1”)：结果是1

N(“1”)：结果是0，“1”是文本，N函数将文本全部转换成0

E． 多条件计数

统计销售一部电视机的销售员人数

=SUMPRODUCT((B4:B13="销售一部")*(C4:C13="电视机"))

多条件因为有了*运算符，所以不需要用N函数转换

F． 模糊条件计数

统计电脑和电视机的销售员人数

=SUMPRODUCT(N(ISNUMBER(FIND("电",C4:C13))))

FIND("电",C4:C13)：返回的是{1;#VALUE!;1;#VALUE!;1;1;1;1;1;#VALUE!}

ISNUMBER(FIND("电",C4:C13))：返回的是

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

N(ISNUMBER(FIND("电",C4:C13)))：返回的是{1;0;1;0;1;1;1;1;1;0}

3. 其他场景

A． 跨列统计

统计三次应聘的笔试平均分

=SUMPRODUCT(($C$3:$H$3=I$3)*$C4:$H4)/3

$C$3:$H$3=I$3：返回的是{TRUE,FALSE,TRUE,FALSE,TRUE,FALSE}

下来自己练习时，这里需要注意$的使用

B． 加权重统计

 

统计总分，笔试占70%，面试占30%

=SUMPRODUCT(I4:J4*$I$13:$J$13)

这是基本用法，但是一种经典使用场景，需要注意$的使用

C． 排名不间断

 

对总分排名，有相同名次时，下个名次不跳跃

RANK排名函数，如果碰到并列名次，下一个排名会断开，如图，没有第4名，因为第3名是两名人员并列

而在很多场景，需要碰到并列名次时，下一个排名不间断，这也是SUMPRODUCT的一个经典使用场景

=SUMPRODUCT(($K$4:$K$12>K4)/COUNTIF($K$4:$K$12,$K$4:$K$12))+1

$K$4:$K$12>K4：{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

COUNTIF($K$4:$K$12,$K$4:$K$12)：{1;1;1;2;2;1;1;1;1}

这里不好理解的，可以下来自己练习一下，练习时，可以在公式栏，选中函数，比如选中COUNTIF($K$4:$K$12,$K$4:$K$12)，然后按F9，可以看到函数的返回值

D． 表格转换

 

如下图，将表一转换成表二的样子

=SUMPRODUCT(($B$4:$B$13=$J4)*($C$4:$C$13=K$3)*$F$4:$F$13)

这其实是多条件的一种经典使用场景，需要注意$的使用

Excel函数及技巧持续更新中，如果对你有帮助，请关注点赞支持一下。

更多推荐

Excel的Sumproduct函数详解