Excel 应用SUMPRODUCT函数计算数组间元素乘积之和

SUMPRODUCT函数的功能是,在给定的几组数组中,将数组间对应的元素相乘,并计算乘积之和。其语法如下:


SUMPRODUCT(array1,array2,array3,...)

其中参数array1、array2、array3、…为2~255个数组,其相应元素需要进行相乘并求和。

【典型案例】某商场在某一天将某两类产品A和B拿出来搞促销,以带动其他产品的销售。结束促销后,商场要统计这两类产品的销售总额,本例的原始数据如图13-83所示。

步骤1:在F2单元格中输入公式“=SUMPRODUCT((A2:A10=”A”)*(C2:C10)*(D2:D10))”,然后按组合键“Ctrl+Shift+Enter”,计算产品类型为A类的产品的销售总额。

步骤2:在F3单元格中输入公式“=SUMPRODUCT((A2:A10=”B”)*(C2:C10)*(D2:D10))”,然后按组合键“Ctrl+Shift+Enter”,计算产品类型为B类的产品的销售总额。计算结果如图13-84所示。

图13-83 原始数据

图13-84 计算结果

说明:上例中所返回的乘积之和,与以数组形式输入的公式“=SUM(A2:B4*C2:D4)”计算的结果相同。使用数组公式可以为类似于SUMPRODUCT函数的计算提供更为通用的解法,如使用公式“=SUM(A2:B4^2)”并按组合键“Ctrl+Shift+Enter”可以计算A2:B4中所有元素的平方和。

【使用指南】数组参数必须具有相同的维数,否则,函数SUMPRODUCT将返回错误值“#VALUE!”。函数SUMPRODUCT将非数值型的数组元素作为0处理。

登记现金日记账函数:SUMPRODUCT、INDEX函数

我们先来学习一下登记现金日记账相关函数的语法和功能,这里我们重点讲解SUMPRODUCT函数和INDEX函数。

SUMPRODUCT函数的语法和功能

我们主要从函数的含义、函数的语法格式、函数在日常办公中运用的实例介绍以及函数使用的注意点这4个方面对SUMPRODUCT函数进行讲解。

含义

SUMPRODUCT函数的适用范围在给定的几组数组中,先把数组间对应的元素相乘,然后返回乘积之和,如图3-1所示。

图3-1 SUMPRODUCT函数含义

从字面上可以看出,SUMPRODUCT由两个英文单词组成,sum是和,product是积,所以是乘积之和的意思。

SUMPRODUCT函数的语法格式

SUMPRODUCT(array1,array2,array3,…),array为数组,如图3-2所示。

SUMPRODUCT函数实例——基础用法

当SUMPRODUCT函数的参数中只有一个数组时,即对数组{1;2;3;4;5;6;7}进行求和,1+2+3+4+5+6+7=28,如图3-3所示。

当sumproduct函数中的参数为两个数组时,两个数组的所有元素对应相乘,如图3-4所示。

公式“=sumproduct(A2:A8,B2:B8)”可转化为“=sumproduct(数组1,数组2)”,即“=sumproduct({1;2;3;4;5;6;7},{1;2;3;4;5;6;7})”=1*1+2*2+3*3+4*4+5*5+6*6+7*7=140。

图3-2 SUMPRODUCT函数的语法格式

图3-3 对一个数组求和

图3-4 对两个数组计算

当sumproduct函数中的参数为3个数组时,3个数组的所有元素对应相乘,在E4单元格中输入的公式为“=SUMPRODUCT(A2:A8,B2:B8,C2:C8)”,如图3-5所示。

图3-5 3个数组元素对应相乘

SUMPRODUCT函数实例——多条件求和

我们先来看第一个实例:单条件求和——统计成都发货平台的发货量,如图3-6所示。

图3-6 单条件求和

在E2单元格中输入公式“=SUMPRODUCT((A2:A13=”成都”)*(B2:B13))”。

看到这个公式你可能有疑惑,它跟语法格式好像不一样,其实把它看作只有一个参数,因为当函数中出现由TRUE和FALSE组成的逻辑数组时,公式要写成“=SUMPRODUCT((A2:A13=”成都”)*1,(B2:B13))”格式,乘以1,把它转化成数组才能参与运算,否则就写成最上面的那种形式。

公式分解如下:

“=SUMPRODUCT({数组1}*{数组2})”

“=SUMPRODUCT({TRUE;…..TRUE;…..TRUE}*{11012;…41568;…12506})”

=1*11012+1*41568+1*12506=65086

第二个实例:多条件求和——求发货平台为成都、收货平台为重庆的发货量,如图3-7所示。在E2单元格中输入公式“=SUMPRODUCT((A2:A13=”成都”)*(C2:C13=”重庆”)*(D2:D13))”即可求出结果。

图3-7 多条件求和

SUMPRODUCT函数使用的注意点

  1. SUMPRODUCT函数后面的参数必须是数组,即行和列的维度是一致的。参数维数不一致会返回错误值#VALUE!
  2. SUMPRODUCT函数中以逗号分隔的各个参数必须为数字型数据。
  3. 如果是判断的结果逻辑值,就要乘以1转换为数字。
  4. 如果不用逗号而直接用*号连接,就相当于乘法运算,就不必乘以1。

INDEX函数的语法和功能

和讲解SUMPRODUCT函数一样,我们主要从函数的含义、函数的语法格式、函数在日常办公中运用的实例介绍以及函数使用的注意点这4个方面对INDEX函数进行讲解。

INDEX函数的含义

返回数据表区域的值或对值的引用,如图3-8所示。

图3-8 INDEX函数的含义

Index函数的两种形式:数组和引用。

1)数组形式——返回数组中指定单元格或单元格数组的数值。

2)引用形式——返回引用中指定单元格或单元格区域的引用。

INDEX函数的语法格式

数组形式=INDEX(array,row_num,column_num)=INDEX(数据表区域,行数,列数)

引用形式=index(reference,row_num,column_num,area_num)

=INDEX(一个或多个单元格区域的引用,行数,列数,从第几个选择区域内引用),如图3-9所示。

图3-9 INDEX函数的语法格式

INDEX函数数组形式实例

第一个实例:如图3-10所示,在B8单元格中输入公式“=INDEX(B3:D6,4,3)”,其中,数据表区域(B3:D6),数(4),列数(3),返回数据表区域(B3:D6)第4行第3列的值120。

图3-10 在B8单元格中输入公式

第二个实例:通过INDEX函数和MATCH函数实现单条件匹配查找。

如图3-11所示,利用INDEX进行匹配查找,当数据很多时,我们不可能通过点数来确定INDEX函数中的行数和列数,而是要通过MATCH函数来确定行数和列数。

在B9单元格中输入以下公式:

=INDEX($F$2:$I$6,MATCH(A9,$F$2:$F$6,0),MATCH($B$8,$F$2:$I$2,0))

这里使用绝对引用要注意,B8代表6月份不变要使用绝对引用。

图3-11 单条件匹配查找

INDEX函数引用形式实例

第一个实例:如图3-12所示,在B8单元格中输入公式“=INDEX((B3:D6,G3:I6),4,3)”,其中,一个或多个单元格区域的引用(两个区域B3:D6,G3:I6),行数(4),列数(3),从第几个选择区域内引用(省略,默认第一个区域B3:D6),所以返回120。

图3-12 从第一个区域内引用

第二个实例:如图3-13所示,在B8单元格中输入公式“=INDEX((B3:D6,G3:I6),4,3,2)”,其中,一个或多个单元格区域的引用(两个区域B3:D6,G3:I6),行数(4),列数(3),从第几个选择区域内引用(第二个区域G3:I6),所以返回500。

INDEX函数使用的注意点

Row_num和Column_num必须指向数组中的某个单元格,否则,INDEX函数出错,返回#REF!错误值。

图3-13 从第二个区域内引用