sum函数的使用方法excel（穿越时间Excel升级之路连载6）

sum函数的使用方法excel（穿越时间Excel升级之路连载6）今天，我们走到Excel升级之路连载6：神级函数SUMPRODUCT的惊鸿一瞥在连载5的后半部分，我们研究了SUM函数和数组的结合，清楚了区域数组在Excel中的应用，但实际上来说，SUM和数组结合的用法并不是太普遍，毕竟按Ctrl Shift Enter键才能够执行数组运算。有没有更加简单高效的函数呢？答案是肯定的。这就是Excel中既能求积、又能求和、还能判断的神级函数——SUMPRODUCT穿越时间•Excel升级之路连载3：数据类型检测与相互转换技巧穿越时间•Excel升级之路连载4：精通公式运算规则和字符比较穿越时间•Excel升级之路连载5：数组的理想照进现实

跟我一起，穿越时间！

回顾一下，经过前面5期的连载，我们对Excel中的数组已经有了全面的认识，知识储备已足够支持我们再上一个台阶，如果没有看过前面几期的连载，建议先点击头像或者链接跳转：

穿越时间•Excel升级之路连载1：Office2010安装体验及个性设置

穿越时间•Excel升级之路连载2：打牢Excel 2010中的数据类型基础

穿越时间•Excel升级之路连载3：数据类型检测与相互转换技巧

穿越时间•Excel升级之路连载4：精通公式运算规则和字符比较

穿越时间•Excel升级之路连载5：数组的理想照进现实

在连载5的后半部分，我们研究了SUM函数和数组的结合，清楚了区域数组在Excel中的应用，但实际上来说，SUM和数组结合的用法并不是太普遍，毕竟按Ctrl Shift Enter键才能够执行数组运算。有没有更加简单高效的函数呢？答案是肯定的。这就是Excel中既能求积、又能求和、还能判断的神级函数——SUMPRODUCT

今天，我们走到Excel升级之路连载6：神级函数SUMPRODUCT的惊鸿一瞥

一、SUMPRODUCT函数初体验

话说天下武林纷争不断，爱恨情仇轮番上演，几经波折之后，各派比拼的内容已不再限于单一的武学修为，而是渐渐延伸到了经济文化领域，文化实力的较量日渐扩大，文创产品方兴未艾，各派上下销售文创产品的热情一再高涨……

（本系列连载中的销售情境数据仅为演示学习需要而虚构）

在下图记录中可以看到清月派周婉悦和玉玲师太有四次开单销售文创纪念品的记录，现在想求一下销售总额，如何计算？如何一步到位计算出结果？

这个问题很简单，每一行的单价乘以数量然后再求和即可。

13*1000 9*1000 6*800 6*1000=32800

写成公式就是：=P2*Q2 P3*Q3 P4*Q4 P5*Q5

上述算法停留在原始社会的水平，虽然可以解决问题，但是有点麻烦。

我们借助连载5中的小练习=SUM(A1:E1*{1 2 3 4 5}=25)的数组基本原理，将P列13、9、6、6看作一个销售数量的数组，将Q列1000、1000、800、1000看作一个销售单价的数组，用数组的原理来进行计算。

公式：=SUM(P2:P5*Q2:Q5) 按下Ctrl Shift Enter键之后即可直接得到结果32800

复习一下计算过程，

原式=sum({13;9;6;6;}*{1000;1000;800;1000})

=sum({13*1000;9*1000;6*800;6*1000})

=sum({13000;9000;4800;6000})

=13000 9000 4800 6000

=32800

其计算过程就是SUM函数的单参数下的数组运算，计算数组P2:P5*Q2:Q5，相当于求出了每一行数量乘以单价的销售额，他们构成了一个新数组，而sum函数最终求和时相当于把新数组中的每一项相加，结果就是总的销售额。（如果不能理解请看前面的连载）

但是现在，我们如果采用SUMPRODUCT函数，一键即可得出结果:

=SUMPRODUCT(P2:P5*Q2:Q5)

或者

=SUMPRODUCT(P2:P5 Q2:Q5) 按下Enter键即可得出结果32800

就是这么高效，具体SUMPRODUCT是怎样实现的？为什么上面两种写法都可以？

我们继续往下看。

二、SUMPRODUCT函数再体验

SUMPRODUCT函数可以返回相应的数组或区域乘积的和，其格式为：

=SUMPRODUCT (array1， [array2]， [array3]， ...)

其中array1为其相应元素需要进行相乘并求和的第一个数组参数。

通俗易懂地说，SUMPRODUCT函数可以直接识别数组，对参数中的数组先求积再求和，一下子完成两种运算。

这里有一个关键：参数是通过逗号分隔的，不同参数间先相乘，有了乘积后求和。这是理解后面各种特殊形式的不二法门。

如果我们从字面上看SUMPRODUCT中sum是求和，product是求积，SUMPRODUCT就是对积进行求和。

举个例子：

现在有两个区域数组，A1:E1和A3:E3，有三个公式：

=SUM(A1:E1*A3:E3) 按Ctrl Shift Enter键执行数组运算

=SUMPRODUCT(A1:E1 A3:E3) 按Enter键计算

=SUMPRODUCT(A1:E1*A3:E3) 按Enter键计算

他们的结果都是130

公式一：=SUM(A1:E1*A3:E3) 按Ctrl Shift Enter键执行数组运算的过程是先计算数组A1:E1和A3:E3的乘积，构成新数组，然后sum函数求数组元素的和。这里不再赘述。

公式二：=SUMPRODUCT(A1:E1 A3:E3) 按Enter键计算。

这就是最经典最易懂的SUMPRODUCT函数写法了，A1:E1和A3:E3之间使用的是逗号，数组A1:E1和数组A3:E3分别是SUMPRODUCT函数的两个参数，遵循先求积再求和的顺序，运算过程如下：

原式=SUMPRODUCT({1*6 2*7 3*8 4*9 5*10})=SUMPRODUCT({6 14 24 36 50})=130

{1*6 2*7 3*8 4*9 5*10}的产生是由SUMPRODUCT函数造成的。

公式三：=SUMPRODUCT(A1:E1*A3:E3) 按Enter键计算

这种写法和公式二相比较，A1:E1和A3:E3之间使用的是*号，而不是逗号

因此A1:E1*A3:E3是SUMPRODUCT函数的一个参数，此时没有参数间的求积步骤，只有求和步骤。

原式=SUMPRODUCT({1*6 2*7 3*8 4*9 5*10})=SUMPRODUCT({6 14 24 36 50})=130

{1*6 2*7 3*8 4*9 5*10}的产生是由*乘号造成的。

看到这里，也许有人就要有疑问了，为什么要分多参数写法和单参数写法？

是不是可以随便写？

继续了解SUMPRODUCT函数的注意事项后，也许你就会有答案。

三、SUMPRODUCT函数注意事项

1、SUMPRODUCT对常量数组的运算举例

我们来看几个简单的公式：

=SUMPRODUCT(5) =5

相当于对单一参数中单独的元素 5 求和，因为没有第二个参数，所以没有求积的步骤；单独的元素 5也没有第二个元素和其相加，故结果和本身一样还是5

=SUMPRODUCT({5})=5

相当于对单一参数中的常量数组{5}求和，因为没有第二个参数，所以没有求积的步骤；单独的元素 5也没有第二个元素和其相加，故结果和本身一样还是5

=SUMPRODUCT({5 6}) =5 6=11

相当于对单一参数中数组{5 6}中的元素 5 和元素 6求和，因为没有第二个参数，所以没有求积的步骤，元素 5 和元素 6求和，结果为11

=SUMPRODUCT(5 6) =5*6=30

相当于对两个参数中单独的元素5和6求积再求和

=SUMPRODUCT({1 2 3 4 5} {6 7 8 9 10}) =1*6 2*7 3*8 4*9 5*10=130

这就是第二部分的例子，相当于对两个参数中的数组{1 2 3 4 5}和数组{6 7 8 9 10}求积再求和，

数组求积遵循数组的运算规律。

2、SUMPRODUCT中的运算符

（1）SUMPRODUCT默认操作是乘法，但也可以执行加减除运算。

这时需将分隔数组参数的逗号替换为所需的算术运算符 (*、/、 、-)执行所有操作后，结果将像往常一样进行求和。

上面这两句话是什么意思？

它告诉我们，SUMPRODUCT对用逗号分隔多个参数会默认先求积，最后再求积的和；如果需要SUMPRODUCT函数中执行其他运算，那这种运算必须在一个参数内完成。

（2）使用算术运算符，需要考虑将数组参数括在括号中，并使用括号对数组参数进行分组以控制算术运算的顺序。这是比较复杂的自定义运算。

3、SUMPRODUCT函数要求各个数组必须具有相同的维数，否则SUMPRODUCT会返回错误值#VALUE!

这个是比较好理解的，不知道你注意到了没有，无论是第一部分的实例，还是第二部分的例子，SUMPRODUCT多个参数中的数组都是规格尺寸一样的。

因此只需要遵循数组的对应运算规则，不会产生#N/A的情况，这在连载5数组的运算中详细讨论过，因此SUMPRODUCT简化了涉及的数组运算的难度。

举个例子：

尺寸不同的情况：两个数组{1 2 3 4 5} {6 7 8}，即数组A8:E8，数组A10:C10

=SUM(A8:E8*A10:C10) =SUM({1 2 3 4 5}*{6 7 8}) 数组运算会出错#N/A

=SUMPRODUCT(A8:E8 A10:C10) =SUMPRODUCT({1 2 3 4 5} {6 7 8}) 因数组尺寸不同，也会出错#VALUE!

=SUMPRODUCT(A8:E8*A10:C10) =SUMPRODUCT({1 2 3 4 5}*{6 7 8}) 出错#N/A

但是，

尺寸相同的情况：{1 2 3 4 5} {6 7 8 "穿越时间" TRUE}

=SUM(A8:E8*A10:E10) =SUM({1 2 3 4 5}*{6 7 8 "穿越时间" TRUE}) 报错#VALUE!

=SUMPRODUCT(A8:E8 A10:E10) =SUMPRODUCT({1 2 3 4 5} {6 7 8 "穿越时间" TRUE}) 结果为44

=SUMPRODUCT(A8:E8*A10:E10) =SUMPRODUCT({1 2 3 4 5}*{6 7 8 "穿越时间" TRUE})会返回错误#VALUE!

中间那种写法不出错，A10:E10中有文本型和逻辑值呢，为什么还不出错？这就是SUMPRODUCT的下一个注意点。

3、SUMPRODUCT 将非数值数组条目视为零，注意是针对单个参数计算完成所生成的数组中的条目。

非数值数包括逻辑值、文本字符串，SUMPRODUCT将其视为0，返回0

非数值数不包括错误值，如果数组元素中包含错误值，SUMPRODUCT返回错误值。

上面的多参数公式：

=SUMPRODUCT(A8:E8 A10:E10) =SUMPRODUCT({1 2 3 4 5} {6 7 8 "穿越时间" TRUE}) 结果为44

具体运算中，"穿越时间" TRUE被视为0，原式=SUMPRODUCT({1 2 3 4 5} {6 7 8 0 0})，所以避免了尺寸不同的问题，计算结果为1*6 2*7 3*8 4*0 5*0=44

上面的单参数公式：

=SUMPRODUCT(A8:E8*A10:E10) =SUMPRODUCT({1 2 3 4 5}*{6 7 8 "穿越时间" TRUE})会返回错误#VALUE!

具体运算中，原式=SUMPRODUCT({1*6 2*7 3*8 4*"穿越时间" 5* TRUE})=SUMPRODUCT({6 14 24 #VALUE! 5})，

注意运算中逻辑型值被视为了1（TRUE被视为1，FLASE被视为0）；文本型值无法计算产生错误#VALUE，进而错误值#VALUE无法参与求和，且不被忽略，因此最终结果就是#VALUE

=SUM(A8:E8*A10:E10) =SUM({1 2 3 4 5}*{6 7 8 "穿越时间" TRUE}) 报错#VALUE!的原因和上述一致。

看到这里，我们可以体会到SUMPRODUCT函数的过人之处了。

你是否还有疑问，上面的计算中，逻辑值TRUE有时被视为0，有时又被视为1 ？

注意SUMPRODUCT会将单个参数计算完成时所生成的数组中的条目里的逻辑值视为0

例如=SUMPRODUCT({TRUE TRUE})= SUMPRODUCT ({0 0})=0

但是单个参数计算过程中，如果出现逻辑值和数值的运算，则TRUE相当于1，FALSE相当于0

=SUMPRODUCT({TRUE TRUE}*{TRUE FALSE})=SUMPRODUCT({TRUE*TRUE TRUE*FALSE})=SUMPRODUCT({1 0})=1

那文本型数字是什么情况？

SUMPRODUCT会将单个参数计算完成时所生成的数组中的条目里的文本型数据（包括汉字、字母、文本型数字）视为0

=SUMPRODUCT({"穿越时间" "Excel" "770"})=SUMPRODUCT({0 0 0})=0

但是单个参数计算过程中，

如果出现文本型汉字或文本型字母的运算，因其不能运算，最终会导致SUMPRODUCT出错。

=SUMPRODUCT({1 2}*{"穿越时间" "Excel"})=SUMPRODUCT({#VALUE! #VALUE!})结果为#VALUE!

如果出现文本型数字的运算，程序会将其视为数字并继续计算得出数值型结果：

=SUMPRODUCT({1 2}*{"100" "200"})=SUMPRODUCT({100 400})=500

以上SUMPRODUCT对单个参数计算完成时所生成的数组中的条目里的逻辑值、文本型数据、空单元格的处理与SUM执行数组运算时对数组或引用的处理是一致的。

4、为获得最佳性能，SUMPRODUCT不应与完整列引用一同使用。

例如我们很少写=SUMPRODUCT (A:A B:B) ，一般都是引用具体的单元格区域。

最后，我们通过实际的例子练习一下，为以后的实际应用打下基础：

注意区分：

= SUMPRODUCT (B1)= SUMPRODUCT ("穿越时间") 的运算结果为#VALUE!

=SUMPRODUCT({"穿越时间"})的运算结果为0

=SUMPRODUCT(A1:A3*B1:B3)运算结果为#VALUE!

=SUMPRODUCT(A1:A3 B1:B3)=SUMPRODUCT({0;5;7} {0 6 8})=2*0 5*6 7*8=86

=SUMPRODUCT(A1=B1)=SUMPRODUCT(FALSE)的运算结果为#VALUE!

=SUMPRODUCT((A1=B1)*1)=SUMPRODUCT(FALSE*1)=SUMPRODUCT(0)=0

=SUMPRODUCT({FALSE})的运算结果为0

注意以下两个，*1产生了什么作用使结果不同？（如果不清楚请仔细看前面的连载）

=SUMPRODUCT(B1={"穿越时间" "丽云流金"})=SUMPRODUCT({B1="穿越时间" B1="丽云流金"})=SUMPRODUCT({FALSE TRUE})=0

=SUMPRODUCT((B1={"穿越时间" "丽云流金"})*1)=SUMPRODUCT(({B1="穿越时间" B1="丽云流金"})*1)=SUMPRODUCT(({FALSE TRUE})*1)=SUMPRODUCT({0 1})=1

好了，以上就是连载6的全部内容，你是否爱上了SUMPRODUCT函数？如果有不理解的可以先看前面的连载打牢基础。

点击头像或链接跳转：

穿越时间•Excel升级之路连载1：Office2010安装体验及个性设置

穿越时间•Excel升级之路连载2：打牢Excel 2010中的数据类型基础

穿越时间•Excel升级之路连载3：数据类型检测与相互转换技巧

穿越时间•Excel升级之路连载4：精通公式运算规则和字符比较

穿越时间•Excel升级之路连载5：数组的理想照进现实

更多精彩，敬请关注，投币赞赏，感谢支持。

（原创连载，个人观点保留，禁止任何未经授权的非本人账号复制文章到其他平台发布）