Excel SUMPRODUCT函数应用
SUMPRODUCT是一个很特别的公式，表面的作用跟我们平常用的这函数的目的不大一样。特别是在2003版或以前的Excel中，还没有SUMIFS，COUNTIFS的功能（就是多条件的SUMIF和COUNTIF），就是在2007版以后，多了SUMIFS，COUNTIFS这两个函数，还是有学习这个函数的意义的。典型的脑子转个弯，就可以达到不同效果。
用途：
给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。比如说第一个数组是{1,2,3}，第二个数组是{6,7,8}，SUMPRODUCT会把两个数组相对应的值相乘，就是 1*6，2*7，3*8，然后把结果相加，就是 1*6 + 2*7 + 3*8。
语法：
SUMPRODUCT(数组1,数组2,数组3,...)
数组1/数组2/数组3等 是需要进行相乘并求和的数组，一般是单元格范围的索引。需要注意
的是所有数组内的元素数目必须相同，如果是单元格范围，每个单元格范围的大小必须相同。
例子：
A | B | C | D | |
1 | 1 | 5 | 9 | 2 |
2 | 2 | 6 | 10 | 4 |
3 | 3 | 7 | a | 5 |
4 | 4 | 8 | b | 7 |
公式 | 结果 | 解释 |
=SUMPRODUCT(A1:A4,B1:B4) | 70 | 两个数组的所有元素对应相乘，然后把乘积相加，即 1*5 + 2*6 + 3*7 + 4*8。 |
=SUMPRODUCT(A1:A4,C1:C4) | 29 | 非数值型会被视同为 0 处理，所以是 1*9 + 2*10 + 3*0 + 4*0。 |
=SUMPRODUCT(A1:A4,B1:B4,C1:C4) | 165 | 三个数组的所有元素对应相乘，然后把乘积相加，即 1*5*9 + 2*6*10 + 3*7*0 + 4*8*0。（把非数值视同 0 ） |
=SUMPRODUCT(A1:B4,B1:C4) | 175 | 多行/列数据也是可以用SUMPRODUCT的，等于1*5 + 2*6 + 3*7 + 4*8 + 5*9 + 6*10 + 7*0 + 8*0。 |
=SUMPRODUCT(A1:A4,B1:B3) | #VALUE! | 数组的个数不一样，返回错误值#VALUE!。 |
=SUMPRODUCT(A1:A4,A1:D1) | #VALUE! | 数组的个数一样，可是形状（行数和列数）不一样，同样返回错误值#VALUE!。 |
=SUMPRODUCT({1,2,3,4},{5,6,7,8}) | 70 | 直接输入数组也可以使用这个函数。（不过一般这样用比较少） |
=SUMPRODUCT({1,2,3,4},A1:D1) | 46 | 这个就用的比较多了。如果一个数组是固定的值，可以考虑直接输在公式中。 |
=SUMPRODUCT({1,2,3,4},A1:A4) | #VALUE! | 把行变成列就不行了。这是因为形状不一样。在数组中，逗号是视同为新的一列，分号才是新的一行。 比如A1到C2单元格，如果用数组直接列出他们的值，就是{1,5,9;2,6,10}。一行一行的列出，每一行用分号区分，同一行的不同单元格就用逗号区分。 |
=SUMPRODUCT({1;2;3;4},A1:A4) | 30 | 把上面公式中的逗号改成分号就得到想要的结果了。 |
=SUMPRODUCT(A1:A4,3) | #VALUE! | 如果想要把每个单元格乘上同一个常数，需要把常数先乘以数组，不然，由于常数不是数组，会返回错误#VALUE!。应该用 =SUMPRODUCT(A1:A4*3) |
多条件加总和多条件计数
SUMPRODUCT是一个很特别的函数。它原本的用途是把两个或以上数组对应的数值相乘后把乘积相加。 可是更多时候，我们会把它用作多条件加总/多条件计数。就是在2007版中，Excel增加了SUMIFS（多条件加总）和 COUNTIFS（多条件计数）两个函数，用SUMPRODUCT 还是有SUMPRODUCT的优势。
首先，SUMPRODUCT在定义条件的时候，由于是直接用等式或其他比较公式作为条件， 比SUMIFS和COUNTIFS更灵活。
第二，学会用SUMPRODUCT去做多条件加总/多条件计数后， 会更容易理解SUMPRODUCT的其他用途，如"RANKIF"（条件排序）， 或多条件VLOOKUP等。
另外，（也可能是习惯的问题）SUMPRODUCT直接用公式作为条件，看上去更直观。
我们先看看用SUMPRODUCT简单替代SUMIF/COUNTIF。
A | B | |
1 | a | 1 |
2 | c | 2 |
3 | A | 3 |
4 | b | 4 |
5 | a | 5 |
比如说，针对左边两列数值，我们需要加总所有第一列是 "a" 或 "A" 对应第二列的值（1+3+5）。 直接用SUMIF，公式就是：
=SUMIF(A1:A5,"a",B1:B5)
A1:A5是条件范围，"a"是条件的要求数值，B1:B5是加总范围。
如果用SUMPRODUCT，公式就是：=SUMPRODUCT((A1:A5="a")*1,B1:B5)
A | B | 各元素公式 | 结果 | |
1 | a | 1 | (A1="a")*1 | 1 |
2 | c | 2 | (A2="a")*1 | 0 |
3 | A | 3 | (A3="a")*1 | 1 |
4 | b | 4 | (A4="a")*1 | 0constantly work out |
5 | a | 5 | (A5="a")*1 | 1 |
公式里的第一个数组(A1:A5="a")*1反映了条件，我们把这数组中五个元素分别在左边表格的第三列中列出。
第一个元素是(A1="a")*1， A1="a"会返回 “TRUE” ， SUMPRODUCT会把所有非数值的元素忽略，以我们要把 “TRUE” / “FALSE” 变成数值 1 或 0。
一般使用的方法是把条件乘以1，也可以在前面加两个负号。 比如(A1="a")*1，(A2="a")*1 ... 或
--(A1="a")，--(A2="a") ...
--(A1="a")，--(A2="a") ...
这样，第一个数组返回的，就是右边表格中第四列的值。SUMPRODUCT((A1:A5="a")*1,B1:B5) 实际上就是 “1*1 + 0*2 + 1*3 + 0*4 + 1*5” ，等于符合条件的的就加总，不符合条件的的就不加。
实际上SUMPRODUCT并不一定要求两个或以上的数组，一个数组也是可以的。所以有时候写公式的时候，会直接把要乘的都放第一个数组里：
=SUMPRODUCT((A1:A5="a")*B1:B5)
我自己的习惯是把条件都放在第一个数组，把加总的放在第二个数组，方便自己纠错的时候分析。
要注意的是，条件前后必须用（），不然，由于乘号的优先顺序比较高， A1:A5="a"*B1:B5就会被视同为A1:A5=("a"*B1:B5)，公式自然就报错了。
要做COUNTIF，跟上面SUMIF的方式很类似， 只需要把*B1:B5改成*1就可以了。如下：
=SUMPRODUCT((A1:A5="a")*1)
说SUMPRODUCT比SUMIFS和COUNTIFS更灵活， 是因为SUMPRODUCT的条件是用公式定义的。比如说在上面的例子中，如果只需要加小写的"a"，不加大写的"A"， 用SUMIF就只能加辅助列，计算那个单元格是小写的"a"；用SUMPRODUCT就可以一步到位了：
=SUMPRODUCT(EXACT(A1:A5,"a")*B1:B5)
理解了上面单个条件加总/计数，要设计多条件加总/多条件计数，实际上就是用:
=SUMPRODUCT((条件一)*(条件二)*(条件三),加总范围)
A | B | C | |
1 | A | a | 1 |
2 | B | c | 2 |
3 | B | a | 3 |
4 | C | b | 4 |
5 | A | a | 5 |
比如要把A列=A，B列=a 对应的C列数字加总：
=SUMPRODUCT((A1:A5="A")*(B1:B5="a"),C1:C5)
要计算A列=A，B列=a 的个数：
=SUMPRODUCT((A1:A5="A")*(B1:B5="a"))
只要多于一个条件，就不用 *1 或者 -- 了。
条件排名和多条件排名restful风格传参
上周说过怎么用SUMPRODUCT做多条件加总和多条件计数，实际上SUMPRODUCT也能做条件排名/多条件排名。
我们先看看简单的排名（从大到小）。
A | B | B列公式 | |
1网站源码下载后怎么用 | 4 | 2 | =RANK($A1,$A$1:$A$5) |
2 | 2 | 4 | =RANK($A2,$A$1:$A$5) |
3 | 4 | 2 | =RANK($A3,$A$1:$A$5) |
4 | 1 | 5 | =RANK($A4,$A$1:$A$5) |
5 | 5 | 1 | =RANK($A5,$A$1:$A$5) |
左面的公式是直接用RANK函数得出的结果。 A列中，5是最大（第5行），所以排第1；1是最小（第4行），所以排最后（第5）。 RANK函数对相同数字会返回一样的排名，可是会影响后面的排名。 比如A列中有两个4（第1行和第3行），都排第2，可是由于有两个第2，就没有第3了。这跟我们一般理解的排名也是一样的。
用SUMPRODUCT做简单的排名，同样要考虑条件。从大到小排名，实际上就是算出有多少个在列表里面的数是大于自己的。如下面Excel表格里的公式：
A | B | B列公式 | |
1 | 4 | 2 | =SUMPRODUCT(($A$1:$A$5>$A1)*1)+1 |
2 | 2 | 4 | =SUMPRODUCT(($A$1:$A$5>$A2)*1)+1 |
3 | 4 | 2 | =SUMPRODUCT(($A$1:$A$5>$A3)*1)+1 |
4 | 1 | 5 | =SUMPRODUCT(($A$1:$A$5>$A4)*1)+1 |
5 | 5 | 1 | =SUMPRODUCT(($A$1:$A$5>$A5)*1)+1 |
如果你会用SUMPRODUCT做条件计数和多条件计数，这个对你应该一点难度都没有。
SUMPRODUCT(($A$1:$A$5>$A1)*1)算出了A列中有多少个数大于A1。+1就是自己的排名了。
你可能会想，我们为什么不把公式改成SUMPRODUCT(($A$1:$A$5>=$A1)*1)，让SUMPRODUCT在计算时包括了等于自己的项，不就不用+1了吗？
当列表中没有相同数值时，这是没有问题的。可是如果有相同数值，那就会出现问题了。
你可以试试把B1的公式改成SUMPRODUCT(($A$1:$A$5>=$A1)*1)，下拉到B2:B5，你会发现B1和B3的结果就会变成3，而不是我们希望的2了。
我们有时候会需要从小到大排名（如赛跑时间），用下面的公式就可以了：
A | B | B列公式 | C | C列公式 | |
1 | 4 | 3 | =RANK($A1,$A$1:$A$5,1) | 3 | =SUMPRODUCT(($A$1:$A$5<$A1)*1)+1 |
2 | 2 | 2 | =RANK($A2,$A$1:$A$5,1) | 2 | =SUMPRODUCT(($A$1:$A$5<$A2)*1)+1 |
3 | 4 | 3 | =RANK($A3,$A$1:$A$5,1) | 3 | =SUMPRODUCT(($A$1:$A$5<$A3)*1)+1 |
4 | 1 | 1 | =RANK($A4,$A$1:$A$5,1) | 1 | =SUMPRODUCT(($A$1:$A$5<$A4)*1)+1 |
5 | 5 | 5 | =RANK($A5,$A$1:$A$5,1) | in advance sumifs函数怎么用例子5 | =SUMPRODUCT(($A$1:$A$5<$A5)*1)+1 |
学会了怎么用SUMPRODUCT替代RANK函数，条件排名 / 多条件排名实际就只是把条件直接用乘号放在SUMPRODUCT的数组里：
A | B | C | C列公式（区分男女从大到小排名） | D | D列公式（区分男女从小到大排名） | |
1 | 男 | 4 | 1 | =SUMPRODUCT(($A$1:$A$5=$A1)*($B$1:$B$5>$B1)*1)+1 | 3 | =SUMPRODUCT(($A$1:$A$5=$A1)*($B$1:$B$5<$B1)*1)+1 |
2 | 男 | 2 | 2 | =SUMPRODUCT(($A$1:$A$5=$A2)*($B$1:$B$5>$B2)*1)+1 | 2 | =SUMPRODUCT(($A$1:$A$5=$A2)*($B$1:$B$5<$B2)*1)+1 |
3 | 女 | 4 | 2 | =SUMPRODUCT(($A$1:$A$5=$A3)*($B$1:$B$5>$B3)*1)+1 | 1 | =SUMPRODUCT(($A$1:$A$5=$A3)*($B$1:$B$5<$B3)*1)+1 |
4 | 男 | 1 | 3 | =SUMPRODUCT(($A$1:$A$5=$A4)*($B$1:$B$5>$B4)*1)+1 | 1 | =SUMPRODUCT(($A$1:$A$5=$A4)*($B$1:$B$5<$B4)*1)+1 |
5 | 女 | 5 | 1 | =SUMPRODUCT(($A$1:$A$5=$A5)*($B$1:$B$5>$B5)*1)+1 | 2 | =SUMPRODUCT(($A$1:$A$5=$A5)*($B$1:$B$5<$B5)*1)+1 |
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论