Excel函数太多太难学?先学个万能的吧

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

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

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

今日大纲:

① SUMPRODUCT基础应用

② SUMPRODUCT多条件计数

③ SUMPRODUCT多条件求和

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

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

⑥ 交叉查询

⑦ 分组排名

SUMPRODUCT函数的工作原理为:

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

语法

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

其中:

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

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

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


1、基础应用

SUMPRODUCT函数的最典型应用:

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

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

=SUMPRODUCT(B3:B9,C3:C9)

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


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.

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


3、多条件求和

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

D13单元格中的公式为:

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

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


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

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

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

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


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-

推荐阅读更多精彩内容

  • 【1】7,9,-1,5,( ) A、4;B、2;C、-1;D、-3 分析:选D,7+9=16;9+(-1)=8;(...
    Alex_bingo阅读 4,470评论 0 14
  • 岁月静好 容颜未老 你还是你 我还是我 沧桑的是脸庞 不老的是那颗初心 念念不忘 必有回响 问问问 切切切 一种相...
    夏一墨阅读 30评论 0 0
  • 你的住房合同收到了,我犹豫了很久以什么方式给你,直接拿给你吧,很尴尬,毕竟那天你大声吼了我,不给你我又做不来。想着...
    安子澄阅读 16评论 0 0
  • 端午节,为每年农历五月初五。据《荆楚岁时记》记载,因仲夏登高,顺阳在上,五月是仲夏,它的第一个午日正是登高顺阳好天...
    灵动空间阅读 127评论 0 0