您现在的位置是:余姚会计培训 >> 会计知识库 >> 浏览文章

余姚会计培训:Excel函数太多太难学?

2018/1/27 9:51:22本站原创 【字体:

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

余姚会计培训:Excel函数太多太难学?

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

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

今日大纲:

① SUMPRODUCT基础应用

② SUMPRODUCT多条件计数

③ SUMPRODUCT多条件求和

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

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

⑥ 交叉查询

⑦ 分组排名

SUMPRODUCT函数的工作原理为:

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

语法

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

其中:

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

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

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

1基础应用

SUMPRODUCT函数的最典型应用:

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

余姚会计培训:Excel函数太多太难学?

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

=SUMPRODUCT(B3:B9,C3:C9)

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

2多条件计数

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

我们一共有两个条件:

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

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

余姚会计培训:Excel函数太多太难学?

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列的结果。

余姚会计培训:Excel函数太多太难学?

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

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

3多条件求和

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

余姚会计培训:Excel函数太多太难学?

D13单元格中的公式为:

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

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

余姚会计培训:Excel函数太多太难学?

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

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

余姚会计培训:Excel函数太多太难学?

E4单元格中公式为:

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

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

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

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

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

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

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

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

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)这个区域的每一个单元格,分别与上面的结果相乘。

余姚会计培训:Excel函数太多太难学?

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

7分组排名

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

余姚会计培训:Excel函数太多太难学?

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”,这就变成我们想要的“排名”的样子了


热门课程

每日推荐