Excel SumProduct函数数组与多条件表达的使用方法及7个实例

在 excel 中，SUMPRODUCT函数既可用于求和又可用于求乘积之和，它分为数组和多条件两种表达式，其中数组表达式用于求乘积之和，最多可以有 255 个数组；多条件表达用于求满足指定条件的和，它又分为两种形式，一种是用逗号分隔且条件前加 --，另一种用星号（*）把求和区域和条件连接，无论哪种条件形式，各条件之间表示“与”的关系。

SumProduct函数各条件之间除可用星号（*）表示“与”关系外，还可以用加号（+）表示“或”的关系，通常是在一个公式中用加号把几个条件连接起来表示“或”关系。

一、Excel SumProduct函数语法

1、数组表达式：SUMPRODUCT(Array1, [Array2], [Array3], ...)

中文表达式：SUMPRODUCT(数组1, [数组2], [数组3], ...)

说明：SumProduct函数最少要有一个数组，最多只能有 255 数组；若有两个以上数组，数组必须有相同的维数，否则会返回值错误#VALUE!；非数值型被当作0处理；每个数组必须有相同的维数；计算方法为：把每个数组的对应元素相乘，然后求和。

2、条件表达式：SUMPRODUCT(求和区域, --(条件1)，[--(条件2)，...])

或 SUMPRODUCT((求和区域)*(条件1)*[(条件2)，...])

说明：条件表达式中用 [] 括起来的为可选项，即SumProduct函数至少要有一个条件；若有两个以上的条件，条件之间是“与”关系，即要同时满足多个条件；“求和区域”与条件的顺序可以交换；条件前面的 -- 是把逻辑值或空值转为数值以便于计算，星号（*）表示“与”关系。

二、Excel SumProduct函数数组表达式使用实例

（一）有两个数组的实例

1、假如要求服装销量的营业额。选中 F11 单元格，把公式 =SUMPRODUCT(E2:E10,F2:F10) 复制到 F11，按回车，返回 509432.3；操作过程步骤，如图1所示：

图1

2、公式共引用了两个单元格区域（数组），即 E2:E10 和 F2:F10，计算方法为：E2 * F2 + E3 * F3 + … + E10 * F10。

提示：如果仅求和只写一个数组即可，例如对服装销量求和 =SUMPRODUCT(F2:F10)。

（二）非数值型被当作0处理的实例

1、选中 B1 单元格，把公式 =SUMPRODUCT(A1:A5) 复制到 B1，按回车，返回 3；操作过程步骤，如图2所示：

图2

2、A1:A5 中只有一个数字 3，而返回结果为 3，说明非数值型都被忽略，逻辑值 True 也不转为 1。

（三）返回值错误 #VALUE! 的实例

1、选中 C1 单元格，把公式 =SUMPRODUCT(A1:A5,B2:B5) 复制到 C1，按回车，返回值错误 #VALUE!；操作过程步骤，如图3所示：

图3

2、公式中 A1:A5 比 B2:B5 多一个单元格，也就是数组维数不同，因此返回值错误 #VALUE!。

三、Excel SumProduct函数多条件表达式使用实例

（一）SUMPRODUCT(求和区域, --(条件1)，[--(条件2)，...])

1、假如要求“分类”为“女装”且“价格”大于 70 的服装销量之和。选中 F11 单元格，把公式 =SUMPRODUCT(F2:F10, --(C2:C10="女装"),--(E2:E10>70)) 复制到 F11，按回车，返回求和结果 1420；操作过程步骤，如图4所示：

图4

2、公式说明：

A、公式的求和区域为 F2:F10，条件为 --(C2:C10="女装"),--(E2:E10>70)；第一个条件 --(C2:C10="女装") 用于从 C2 到 C10 中依次取出每一个分类，并跟“女装”比较，如果相等返回 True，否则返回 False；最后返回数组{TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE}。

B、第二个条件 --(E2:E10>70) 用于从 E2 到 E10 中依次取出每一个价格并跟 70 比较，如果大于 70，返回 True，否则返回 False；最后返回数组 {TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}。

C、则两个条件  --(C2:C10="女装"),--(E2:E10>70) 变为 --({TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE}),--({TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE})，进一步计算把 True 转为 1，False 转为 0，即 {1;1;1;0;1;1;0;1;1},{1;0;0;1;1;1;1;0;0}，再把两个数组对应元素相乘，结果为 {1;0;0;0;0;1;1;0;0}。

D、F2:F10 返回数组 {329;569;638;897;528;563;982;765;892}，则公式变为 =SUMPRODUCT({329;569;638;897;528;563;982;765;892},{1;0;0;0;0;1;1;0;0})，再把两个数组对应元素相乘并相加，因此求和结果为 1420。

（二）SUMPRODUCT((求和区域)*(条件1)*[(条件2)，...])

1、同样以求“分类”为“女装”且“价格”大于 70 的服装销量之和为例。选中 F11 单元格，把公式 =SUMPRODUCT((F2:F10)*(C2:C10="女装")*(E2:E10>70)) 复制到 F11，按回车，返回求和结果 1420；操作过程步骤，如图5所示：

图5

2、公式说明：

公式 =SUMPRODUCT((F2:F10)*(C2:C10="女装")*(E2:E10>70)) 是上例中的公式 =SUMPRODUCT(F2:F10, --(C2:C10="女装"),--(E2:E10>70)) 的另一种写法，只不过它把三个参数用 * 连接来表示“与”的关系。

四、Excel SumProduct函数扩展使用实例

（一）用 * 和 + 组合“与”和“或”的条件

1、假如要求“大类”为“女装”且“小类”为“衬衫或T恤”的所有服装销量之和。选中 F11 单元格，把公式 =SUMPRODUCT((F2:F10)*(C2:C10="女装")*((D2:D10="衬衫")+(D2:D10="T恤"))) 复制到 F11，按回车，返回求和结果 2312；操作过程步骤，如图6所示：

图6

2、公式说明：

公式的条件为 (C2:C10="女装")*((D2:D10="衬衫")+(D2:D10="T恤"))，前一个条件与后两个条件是“与”关系，用 * 连接；后两个条件是“或”关系，用 + 连接；意思是选出 C2:C10 为“女装”且 D2:D10 中为“衬衫”或“T恤”的服装销量。

（二）SumProduct函数求和区域有文本的处理方法

1、假如要求“大类”为“女装”且“小类”为“衬衫” 服装销量之和，销量列有文本。选中 F11 单元格，把公式 =SUMPRODUCT(IF(ISTEXT(F2:F10),0,F2:F10)*(C2:C10="女装")*(D2:D10="衬衫")) 复制到 F11，按 Ctrl + Shift + 回车，返回求和结果 1221，操作过程步骤，如图7所示：

图7

2、公式说明：

A、公式中的 IF(ISTEXT(F2:F10),0,F2:F10) 为求和区域部分，由于求和区域 F2:F10 有文本（无），如果直接写 F2:F10 会返回值错误#VALUE!，所以要用 If 来判断。

B、ISTEXT(F2:F10) 用于判断 F2:F10 中每个元素是否为文本，如果是文本，返回 True，否则返回 False，由于只有 F7 是文本，所以返回数组 {FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}，

C、接着，依次从数组中取出每一个元素，如果为真，返回 0，否则返回 F2:F10 中对应的值，例如第一次取数组第一个元素 FALSE，返回329，第二次取数组第二个元素 FALSE，返回 569，其它的以此类推，最后返回数组 {329;569;638;897;528;0;982;765;892}。

3、上面的公式也可以改为 =SUM(IF(ISTEXT(F2:F10),0,F2:F10)*(C2:C10="女装")*(D2:D10="衬衫"))，也要按 Ctrl + Shift + 回车。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。