凤凰平台注册开户_凤凰彩票app下载安装_凤凰彩票投注网

热门关键词: 凤凰平台注册开户,凤凰彩票app下载安装,凤凰彩票投注网

凤凰彩票投注网:Excel函数太多太难学,在Excel表

作者: 凤凰彩票投注网  发布:2019-11-05

问题:在Excel表格里怎样算乘积?

很多朋友觉得Excel函数太多了,而且每种都有不同的应用条件,根本记不过来。

如果你也有同样的感受,不想学习太多的函数公式,那么也一定要学习的函数就是-SUMPRODUCT。

无论是条件求和、条件计数、加权平均,这些常用的统计计算它都能做得到!还在为函数太多记不住发愁吗?快来学习它吧~~

今日大纲:

① SUMPRODUCT基础应用

② SUMPRODUCT多条件计数

③ SUMPRODUCT多条件求和

④ 计算加权平均值(权重已知)

⑤ 计算加权平均值(权重未知)

⑥ 交叉查询

⑦ 分组排名

SUMPRODUCT函数的工作原理为:

在给定的几组数组中,将数组间对应的元素先相乘(PRODUCT),后相加(SUM)。但是就凭这个相乘、相加的计算,就能实现很多功能。

语法

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

其中:

array1 必需,它是相应元素需要进行相乘并求和的第一个数组参数。

array2, array3,... 为可选。可以是 2 到 255 个数组参数,其相应元素需要进行相乘并求和。

数组参数必须具有相同的维数。 否则,函数 SUMPRODUCT 将返回 #VALUE! 错误值 #REF!。

函数解析

SUMPRODUCT函数是Excel中的数学函数,将给定的几个数组间对应元素相乘,并返回乘积之和。

其基本语法为:

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

SUMPRODUCT函数语法具有下列参数:

Array1:必需。其相应元素需要进行相乘并求和的第一个数组参数。

Array2, array3,...:可选。2到255个数组参数,其相应元素需要进行相乘并求和。

特别注意:

数组参数必须具有相同的维数。否则,函数SUMPRODUCT将返回#VALUE!错误值#REF!。

基本用法

SUMPRODUCT函数最基本的用法是:

数组间对应的元素相乘,并返回乘积之和。

如下图:

凤凰彩票投注网 1

公式:=SUMPRODUCT(B2:B9,C2:C9)

该公式的含义是:

B2*C2+B3*C3+B4*C4+B5*C5+B6*C6+B7*C7+B8*C8+B9*C9

回答:

1、基础应用

SUMPRODUCT函数的最典型应用:

下面例子中有单价和数量,我们要求所有产品的总价。

其中A12单元格中的公式为:

=SUMPRODUCT(B3:B9,C3:C9)

公式所起到的作用,就像我在旁边蓝色虚框中标出的一样,先将B3到B9中的每一个数,分别与C3到C9中的数相乘。然后再将这些乘积进行加和,就得到了我们要的结果。

单条件求和

如下图,计算女员工业绩得分和:

凤凰彩票投注网 2

公式:

=SUMPRODUCT((B2:B11="女")*凤凰彩票投注网,C2:C11)

其中:

B2:B11="女":

将B2:B11内每个单元格值与“女”比较,凡是性别为“女”的是TRUE,否则是FALSE,结果返回一组逻辑值:

{FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;}

(B2:B11="女")*C2:C11:

将上述逻辑数组内的值与对应的C2:C11的数值相乘。

1、直接计算

2、多条件计数

求成绩大于80分的女生的数量:

我们一共有两个条件:

① 成绩,也就是D列的数,要大于80;

② 性别,也就是C列,为“女”。

D13单元格中的公式为:

=SUMPRODUCT(($C$3:$C$11=$C$3)*($D$3:$D$11>80))

公式中,

$C$3:$C$11=$C$3代表,C3到C11区域等于C3,也就是“女”。符合条件的,会返回“TRUE”(相当于1),不符合条件的,返回“FALSE”(相当于0).

$D$3:$D$11>80,同理,这几个单元格“成绩”大于80的,返回“TRUE”(1),不符合条件的,返回“FALSE”(0)。

然后两个数组分别相乘。

1*0=0

0*0=0

1*1=1

就得到下面第3列的结果。

然后在将这一列结果进行相加,就得到2.

(此计算过程,下面几个例子很相似,就不再赘述)

多条件求和

如下图,计算女员工业绩得分高于15的得分和:

凤凰彩票投注网 3

公式:

=SUMPRODUCT((B2:B11="女")*(C2:C11>15),C2:C11)

多条件求和的通用写法是:

=SUMPRODUCT((条件一)*(条件二)*……*(条件N),求和范围)

在单元格输入算式,例如输入“=25*35”,回车即可得出875的结果(注意,在算式前必须输等号)。

3、多条件求和

下面求和的例子,跟计数的区别就在于,最后多乘一个数组。

D13单元格中的公式为:

=SUMPRODUCT(($C$3:$C$11=$C$3)*($D$3:$D$11>80)*($D$3:$D$11))

也就是将刚才的列再与“成绩”那一列,D3:D11进行相乘,然后在进行加和,便可得到。

模糊条件求和

如下图,计算销售部门女员工业绩得分和:

凤凰彩票投注网 4

销售部门不止一个,要查找所有的销售部门,就要按照关键字“销售”查找,就属于模糊查找。

公式:

=SUMPRODUCT(ISNUMBER(FIND("销售",A2:A11))*(C2:C11="女"),D2:D11)

其中:

FIND("销售",A2:A11):

在A2:A11各单元格值中查找"销售",如果能查到,返回"销售"在单元格值中位置,如果差不到,返回错误值#VALUE!。

本部分的结果是:

{#VALUE!;1;1;1;#VALUE!;1;#VALUE!;#VALUE!;1;#VALUE! }

ISNUMBER(FIND("销售",A2:A11)):

判断上述数值中各值是不是数字,如果是,返回TRUE,否则返回FALSE,所以,本部分公式的结果是:

{FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}

凤凰彩票投注网 5凤凰彩票投注网 6

4、加权平均值(权重已知)

加权平均值的用法也很常见。例如期末成绩由下面3部分构成,每一部分的权重分别为20%,30%,50%。

E4单元格中公式为:

=SUMPRODUCT(B4:D4,B$3:D$3)

与之前例子不同的是,这次是不同行之间先相乘,然后再把列进行相加。

B$3:D$3用了“混合引用”,目的是为了保证公式向下复制时,这一行数据的引用不会发生变化。

2、算式计算

5、加权平均值(权重未知)

这里的权重未知,指的是没有一个明确的百分比,只有一个绝对的数量。

这样的情况也很好处理,我们只需用跟上个例子一样的过程,让“数量”和“成本”分别相乘后再相加,然后在除以“数量”的和即可。

图中A9单元格中的公式为:

=SUMPRODUCT(B3:B6,C3:C6)/SUM(B3:B6)

例如,在第一行c列(即c1)输入“=a1*b1”,回车即可得到a1乘以b1,即“1*11”的结果。把鼠标放在c1单元格右下角交叉处,空心十字变成实心十字,按住鼠标不放往下拖动,就可自动形成一组对应的乘法公式。

6、交叉查询

交叉查询用在这里可以说又是一神作了。

通常我们熟悉的解决方法是用INDEX+MATCH函数来完成。

但是,万能的SUMPRODUCT也一样能行。

下图中,我们要求“超市3”“火龙果”的价格。

D13单元格中的公式为:

=SUMPRODUCT(($B$4:$B$9=B13)*($C$3:$G$3=C13)*($C$4:$G$9))

前两段分开看很好理解,就是判断是否符合条件,符合的返回"TRUE",不符合的返回"FALSE"。

但是,它俩分别是一个列数组和一个行数组,相乘后得到的是一个二维的矩阵。

($B$4:$B$9=B13)*($C$3:$G$3=C13)

如下图:

然后,再把($C$4:$G$9),这个区域的每一个单元格,分别与上面的结果相乘。

最后在将所有的数相加,就得到9.8.

凤凰彩票投注网 7

7、分组排名

下面这个例子,"小组"这一列中有两个不同的值,“一组”和“二组”。现在我们要针对“销售额”按不同的组进行排名。

D3单元格中公式为:

=SUMPRODUCT(($B$3:$B$10=B3)*($C$3:$C$10>C3))+1

($B$3:$B$10=B3)不解释了

($C$3:$C$10>C3)就是拿所有的“销售额”跟C3(这里是相对引用,向下复制时会变成C4,C5,……)比,比它大的会返回TRUE。

但是这样一来排第一的数,是没有比它大的,会返回0。所以我们在公式的最后面有个“+1”,这就变成我们想要的“排名”的样子了。

写了这么多,一直像是自说自话,

要不咱们也来点互动?

在评论区告诉我,

你最想学习的Excel知识是什么?

-END-

凤凰彩票投注网 8

回答:

直接计算乘积的就不用说了,说说这个函数比较有意思,刚开始使用的时候可能会觉的不好理解,但熟悉后会发现这个函数非常实用,能解决工作中的很多问题。

这个函数的的主要功能是在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。

就字面上可以看出,组成sumproduct的两个单词sum是和,product是积,sumproduct所以是乘积之和的意思:

凤凰彩票投注网 9

SUMPRODUCT的函数语法为:SUMPRODUCT(array1,array2,array3, ...)其中Array为数组。

如下图中我们要对一组数组求和时可用公式:=sumproduct(A2:A8),当数组的内容比较简单时可以直接将数组写入公式中:==SUMPRODUCT({1;2;3;4;5;6;7}):

凤凰彩票投注网 10

当需要对表中两个数组相乘时,写入公式“=SUMPRODUCT(A2:A8,B2:B8)”我们发现值与验证公式“=A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8”的值一致:

凤凰彩票投注网 11凤凰彩票投注网 12

当需要对表中三个数组相乘时,写入公式“=SUMPRODUCT(A2:A8,B2:B8,C2:C8)”

凤凰彩票投注网 13

实际应用:多条件求和

如一般的仓储人员的发货台长会记录出货仓库和收获地点两个维度的数据,如下图我们要求从仓库1发出到重庆的商品总数,公式为:=SUMPRODUCT((A2:A14="仓库1")*(B2:B14="重庆")*(C2:C14))

凤凰彩票投注网 14

多条件计数

还是仓库发货的例子,我们知道仓储人员在发货时,每一笔记录对应一个订单,如我们要查从仓库1发出到重庆的订单数量,公式为:=SUMPRODUCT((A2:A14="仓库1")*(B2:B14="重庆"))

凤凰彩票投注网 15

逻辑解释:在以上两个案例中A2:A14="仓库1",本质上是做了条件判断,在A2:A14间每一个单元格与“仓库1”这个值做判断,满足条件返回判断值“TRUE”,不满足条件返回判断值“FALSE”,在以上的案例中(A2:A14="仓库1")实质的值为({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}),(B2:B14="重庆")的值为({TRUE;0;0;0;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}),而在程序语言中一般TRUE用“1”表示,FALSE用“0”表示,故公式本身的运算逻辑为SUMPRODUCT({1;0;0;0;0;0;0;0;0;1;0;1},{1;0;0;0;0;0;1;0;0;1;0;0})=2

排名

在部门业绩排名、班级成绩排名计算时,我们可以用SUMPRODUCT来做公式计算,如下表需要按照订单数量计算部门中每个小组的销售排名,公式为=SUMPRODUCT((B20<$B$20:$B$28)*1)+1)

凤凰彩票投注网 16

逻辑解释:数组公式B20<$B$20:$B$28同样是做为条件判断,满足条件的返回值为“TRUE”,不满的值“FALSE”,要做排名计算我们需要计算出大于B20单元格的个数,则需要把逻辑值转变为数字值故需要乘以“1”,B20<$B$20:$B$28)*1,此时=SUMPRODUCT((B20<$B$20:$B$28)*1)的值相当于求出大于B20单元个的个数及8个,也就是说排在部门1前面的部门有8个这个时候再加“1”得出的值就是部门1在所有部门中的排名。

SUMPRODUCT函数在数学课程中可以用于数组计算,在工作中可以实现计划考核的制作大家不妨尝试。

更多财税职场学习资讯,欢迎关注秀财网

回答:

一、直接计算的凤凰彩票投注网 17

如要单元格A2:A8和单元格C2:C8各自相乘,将结果填入D2:D8,只要先在D2输入:=A2*C2回车,再往下填充即可

二、如果一列数据要同乘以一个数,如下例,我们只要在D2输入:=A2*$C$2回车,载往下填充即可

如果同乘以的乘数简单的话,还可以直接在D2输入公式:=A2*某数,回车再往下拉即可~

凤凰彩票投注网 18

三、选择性粘贴——乘

复制数据,再在要粘贴的数据单元格中,选择性粘贴中选择“乘”,乘积就在粘贴的单元格上了,完成

凤凰彩票投注网 19

本文由凤凰平台注册开户发布于凤凰彩票投注网,转载请注明出处:凤凰彩票投注网:Excel函数太多太难学,在Excel表

关键词: