q函数表格怎么看_函数太多太难学？先学这个“万能”的吧！

2.24-29 秋叶系列课程大促

扫码领粉丝专享 990 元优惠券 !

本文作者：朱莉 来源：Excel 小超人(ID：Julie1391) 本文编辑：小叮、竺兰

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

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

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

今日知识点：

❶ SUMPRODUCT 基础应用；

❷ SUMPRODUCT 多条件计数；

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

其实所有这些技巧， 根本不需要很厉害的技术功底，也可以学会。 Excel 技巧就是个无底洞，10 年也学不完。相比之下， 更加重要的，是数据管理和表格设计的思维。 在业务场景中，去恰到好处地运用 Excel 技术，才能四两拨千斤。

两张表格，找出其中重复和缺失的数据，你要多久？ 给一个编号，把所有相关信息列出来，你要用多久？ 按照不同的业绩级别，按不同比例计算奖金，你要多久？ 收集来的数据乱七八糟，全部整理好，你又要用多久？

如果你只会靠肉眼，拼手工，可以预见，摆在你面前的只有一条路。 而当你在熬夜加班，痛摔键盘的时候，高手可能已经呼呼大睡了。职场过来人都知道 Excel 有多重要，那怎么提高 Excel 技能水平？为了解救职场表格表妹，秋叶团队筹备半年精心打磨，开办了【 秋叶 Excel 数据处理学习班】。 过去的大半年时间里，我们已经成功开班 9 期，累计帮助超过 1500 人提高效率早下班。如果你也想像他们一样，玩转 Excel 早下班，就一起来秋叶 Excel 数据处理学习班吧！

▲ 上下滑动查看

学习班还配备了 秋叶 Excel 团队明星讲师和助教天使，提供点评指导、答疑服务。 一个思路，可能就能够帮你节省半天的工作时间。

老师们在外面培训一次，价格都上万元

这次，小 E 帮你们申请到了特别福利！

原价 799 元的学习班

大促期间仅需 639 元！

领券立省 160 元！

扫码了解详情 ↓↓↓

除了给你超值价格，我们还要给你超多赠礼！

外面高价都买不到的 Excel 表格模板，现在买课就送！

❶ Excel 常用快捷键速查表，35 个常用函数速查表。轻松掌握函数常规用法，解决 80% 的工作难题！

❷ 精选好看的表格模板免费送！

此外你还能获得价值 198 元的 PPT 福利课、3 个月幕布会员！太太太超值啦！

一天一杯奶茶钱，名师集训帮你 系统提升学习 Excel 从此高效办公不加班！ 赶紧扫码了解课程详情 和我一起提升 Excel 水平吧！ ↓↓↓

除了名师集训重点攻克，我们还配套网课随查随学，这次促销，小 E 给你们申请到了「学习班+网课」套餐优惠！

组合购买仅需 679 元，让你碎片时间也能学起来！

扫码买套装更优惠！ ↓↓↓ ————— 你可能还想了解 ————— Q：课程有时间、次数限制吗？ A：《和秋叶一起学 Excel》网课不限时间和次数，随时可学，长期有效。秋叶 Excel 数据处理学习班，每期 21 天，直播课可反复回看。 Q：买课后怎么学习？ A：《和秋叶一起学 Excel》网课，用买课账号登录网易云课堂网站/APP，在「我的学习」里即可找到课程，参与学习。秋叶 Excel 数据处理学习班，买课后添加班主任微信(qiuyeExcel)，可以选择 2020 上半年任一期入群学习。有更多疑问，在公众号 【秋叶 PPT】后台回复关键词 「B」，联系客服小姐姐哦~ 点击 【 了解课程详情， 让 Excel 为 2020 升职加薪提速！