常用Excel函数：这些足够平常用了

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

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

无论是条件求和、条件计数、加权平均，这些常用的统计计算它都能做得到！

SUMPRODUCT 函数的工作原理为：在给定的几组数组中，将数组间对应的元素先相乘（PRODUCT），后相加（SUM）。

就凭这个相乘、相加的计算，就能实现很多功能。

语法：

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

其中：

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

array2, array3,... 为可选。

可以是 2 到 255 个数组参数，其相应元素需要进行相乘并求和。

PS：数组参数必须具有相同的维数。

否则，函数 SUMPRODUCT 将返回 #VALUE! 错误值 #REF!。

一、基础应用

SUMPRODUCT 函数的最典型应用：

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

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

=SUMPRODUCT(B3:B9,C3:C9)

公式所起到的作用，就像我在旁边蓝色虚框中标出的一样。

先将 B3 到 B9 中的每一个数，分别与 C3 到 C9 中的数相乘。

然后再将这些乘积进行加和，就得到了我们要的结果。

二、多条件计数

求成绩大于 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。

此计算过程，与下面几个例子很相似，之后就不再赘述。

三、多条件求和

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

D13 单元格中的公式为：

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

也就是将刚才的列再与「成绩」那一列，D3：D11 进行相乘，然后再进行加和，便可得到。

四、加权平均值（权重已知）

加权平均值的用法也很常见。

例如期末成绩由下面 3 部分构成，每一部分的权重分别为 20%，30%，50%。

E4 单元格中公式为：

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

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

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

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

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

这样的情况也很好处理，我们只需用跟上个例子一样的过程。

让「数量」和「成本」分别相乘后再相加，然后在除以「数量」的和即可。

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

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

六、交叉查询

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

通常我们熟悉的解决方法是用 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。

七、分组排名

下面这个例子，「小组」这一列中有两个不同的值，「一组」和「二组」。

现在我们要针对「销售额」按不同的组进行排名。

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」，这就变成我们想要的「排名」的样子了。

总结：

SUMPRODUCT 基础应用；

SUMPRODUCT 多条件计数；

SUMPRODUCT 多条件求和；

计算加权平均值（权重已知）；

计算加权平均值（权重未知）；

交叉查询；

分组排名。

其实所有这些技巧，根本不需要很厉害的技术功底，也可以学会。

Excel 技巧就是个无底洞，10 年也学不完。相比之下，更加重要的，是数据管理和表格设计的思维。当然，在业务场景中，去恰到好处地运用 Excel 技术，才能做到四两拨千斤。

如果喜欢的话，请点击关注，您的关注就是在下持续更新的动力。

以上是在下从网上学习整理发布，如有冒犯，联系必删。