当前位置：文档之家 › 举例说明电子表格中的SUMPRODUCT函数应用

举例说明电子表格中的SUMPRODUCT函数应用

  • 格式：docx
  • 大小：26.49 KB
  • 文档页数：7

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 0

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") ...

这样，第一个数组返回的，就是右边表格中第四列的值。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 或者 -- 了。

条件排名和多条件排名

上周说过怎么用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) 5 =SUMPRODUCT(($A$1:$A$5<$A5)*1)+1

合集下载

如何使用SUMPRODUCT和SUMIFS函数进行多条件求和与计数的Excel高级方法

如何使用SUMPRODUCT和SUMIFS函数进行多条件求和与计数的Excel高级方法

如何使用SUMPRODUCT和SUMIFS函数进行多条件求和与计数的Excel高级方法

Excel是一款功能强大的电子表格软件，它提供了多种函数来帮助用户进行数据分析和计算。其中，SUMPRODUCT函数和SUMIFS函数是常用的高级函数，可以用于进行多条件求和和计数。本文将详细介绍如何使用SUMPRODUCT和SUMIFS函数进行多条件求和与计数的Excel高级方法。

一、SUMPRODUCT函数的用法及多条件求和

SUMPRODUCT函数是一个非常强大的函数，它可以将一组数组相乘，并返回结果的和。在Excel中，我们可以利用SUMPRODUCT函数实现多条件的求和操作。

1.1 SUMPRODUCT函数的基本用法

首先，我们来了解一下SUMPRODUCT函数的基本用法。SUMPRODUCT函数的语法如下：

SUMPRODUCT(array1, array2, ...)

其中，array1、array2等为要相乘的数组。

举个例子，假设我们有一个销售数据表格，其中包含了产品种类、地区和销售数量三列数据。我们要根据产品种类和地区来计算销售数量的总和。可以使用SUMPRODUCT函数来实现，具体公式如下： =SUMPRODUCT((产品种类范围="产品A")*(地区范围="地区1")*销售数量范围)

其中，“产品种类范围”、“地区范围”和“销售数量范围”分别表示产品种类、地区和销售数量所在的数据范围。

1.2 SUMPRODUCT函数的多条件求和

SUMPRODUCT函数的强大之处在于可以结合多个条件进行求和。我们可以在上述基本用法的基础上添加更多的条件，来实现多条件的求和操作。

例如，我们要求解销售数据表格中“产品A”在“地区1”和“地区2”的销售数量总和。可以使用以下公式：

=SUMPRODUCT((产品种类范围="产品A")*((地区范围="地区1")+(地区范围="地区2"))*销售数量范围)

利用SUMPRODUCT和COUNTIF函数进行多条件求和与计数的Excel高级技巧

利用SUMPRODUCT和COUNTIF函数进行多条件求和与计数的Excel高级技巧

利用SUMPRODUCT和COUNTIF函数进行多条件求和与计数的Excel高级技巧

Excel是一种功能强大的办公软件，广泛应用于数据分析、统计和管理等领域。SUMPRODUCT和COUNTIF函数是Excel中常用的函数，它们可以帮助我们进行多条件的求和并计数。本篇文章将介绍如何利用SUMPRODUCT和COUNTIF函数进行多条件求和和计数的Excel高级技巧。

一、SUMPRODUCT函数介绍及用法

SUMPRODUCT函数是一种在Excel中用于求和的函数，它可以根据给定的条件进行多条件求和。该函数的语法如下：

SUMPRODUCT(array1, array2, ...)

array1, array2, ...：需要进行求和运算的数组或范围。

例如，假设有一个销售数据的表格，其中包含了产品名称、销售数量和销售额等信息。我们需要根据产品名称和日期这两个条件，计算出销售数量的总和。使用SUMPRODUCT函数可以轻松实现此功能。

具体步骤如下：

1. 在一个新的单元格中，输入SUMPRODUCT函数的公式如下：

=SUMPRODUCT((A2:A10="产品A")*(B2:B10="2021-01-01"),

C2:C10) A2:A10为产品名称的范围，B2:B10为日期的范围，C2:C10为销售数量的范围。

2. 按下Enter键，即可求得满足条件的销售数量总和。

二、COUNTIF函数介绍及用法

COUNTIF函数是一种在Excel中用于计数的函数，它可以根据给定的条件进行计数。该函数的语法如下：

COUNTIF(range, criteria)

range：需要进行计数的范围。

criteria：指定的条件。

例如，假设我们有一个学生成绩表格，其中包含了学生姓名、科目和成绩等信息。我们需要根据科目和分数这两个条件，计算出满足条件的学生人数。使用COUNTIF函数可以轻松实现此功能。

sumproduct函数二维表用法

sumproduct函数二维表用法

Sumproduct函数是Excel中常用的函数之一，它主要用于计算多个数组或范围中对应元素的乘积之和。在二维表中，Sumproduct函数的用法更加灵活，可以帮助用户更方便地进行数据处理和分析。本文将介绍Sumproduct函数在二维表中的用法，帮助读者更好地运用这一函数进行数据分析和计算。

一、基本语法

让我们来了解一下Sumproduct函数的基本语法。在Excel中，Sumproduct函数的基本语法如下：

=SUMPRODUCT(array1, [array2], [array3], …)

其中，array1, array2, array3等表示要进行相乘并求和的数组或范围。这些数组或范围可以是单行或单列，也可以是多行多列。在二维表中，通常将Sumproduct函数用于多行多列的数组或范围。

二、二维表中的应用

在二维表中，Sumproduct函数可以用于多种计算和分析。下面我们将分别介绍Sumproduct函数在二维表中的几种常见用法。

1. 单一数组或范围

我们来看一下在二维表中使用Sumproduct函数计算单一数组或范围的情况。假设我们有一个销售数据表，其中包括产品名称、销售数量和销售金额等字段。我们想要计算每个产品的总销售额，可以使用如下公式： =SUMPRODUCT(销售数量范围, 销售金额范围)

这样就可以得到每个产品的总销售额，而不需要单独再添加一列进行计算。

2. 多个数组或范围

除了计算单一数组或范围外，Sumproduct函数还可以用于计算多个数组或范围的乘积之和。我们希望计算某个产品在某个时间段内的销售额，可以使用如下公式：

=SUMPRODUCT((产品名称范围="A产品")*(销售日期范围>=开始日期)*(销售日期范围<=结束日期)*销售金额范围)

这样就可以按照指定条件计算某个产品在指定时间段内的销售额。

3. 条件计数

除了计算乘积之和外，Sumproduct函数还可以结合条件进行计数。我们希望统计某个产品在某个时间段内的销售次数，可以使用如下公式：

Excel高级函数SUMPRODUCT和IF的组合应用

Excel高级函数SUMPRODUCT和IF的组合应用

Excel高级函数SUMPRODUCT和IF的组合应用

Excel是一款强大的电子表格软件，它提供了许多高级函数来帮助用户进行数据分析和计算。其中，SUMPRODUCT和IF函数是常用的两个函数，它们可以通过组合应用实现更加灵活和复杂的计算操作。本文将深入探讨SUMPRODUCT和IF函数的组合应用，介绍它们的基本用法和实际应用场景。

一、SUMPRODUCT函数

SUMPRODUCT函数是Excel中的一种数组函数，用于实现数组的乘法运算并返回结果的和。其基本语法为：

SUMPRODUCT(array1, array2, ...)

其中，array1, array2, ...表示需要相乘的数组。

1.1 基本用法

首先我们来看一个简单的示例，假设我们有一张销售数据表格，其中包含了商品的名称、销售数量和销售金额。我们希望计算每个商品的销售总额，可以使用SUMPRODUCT函数来实现。

首先，在一个单独的列中，我们可以使用IF函数来筛选出特定商品的销售数量和销售金额。假设商品名称保存在A列，销售数量保存在B列，销售金额保存在C列，我们需要计算的商品名称保存在E列。则可以使用如下公式：=IF(A2=E2, B2*C2, 0)。 然后，我们可以使用SUMPRODUCT函数来计算每个商品的销售总额，公式为：=SUMPRODUCT((A2:A10=E2)*C2:C10)。

1.2 高级用法

除了基本的乘法运算，SUMPRODUCT函数还支持更复杂的计算操作。例如，我们可以使用SUMPRODUCT函数计算某个特定条件下的加权平均值。

假设我们有一组学生的成绩数据，其中包含了科目、成绩和权重。我们希望计算某个特定科目的加权平均值。首先，在一个单独的列中，我们可以使用IF函数来筛选出特定科目的成绩和权重。假设科目保存在A列，成绩保存在B列，权重保存在C列，我们需要计算的科目保存在E2单元格。可以使用如下公式：=IF(A2=E2, B2*C2, 0)。