Excel基础入门——常用特殊函数用法详解（八）

时间过得很快，本节课已经到了Excel基础入门课程的最后一课，上节课我们给大家介绍了Excel常用的字符串/文本/日期函数，具体可回顾Excel基础入门——字符串/文本/日期函数详解（七），本节课我们准备给大家介绍Excel中常用的特殊函数，重点介绍substitute/sumproduct/sumproduct+countif函数的使用方法。

一、SUBSTITUTE

基本用法：将手机号码的中间四位换成星号

公式=SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个])，通常与MID函数结合使用。

假设，现在我们需要将成交客户手机号码的中间4位替换位“*”号，可以使用MID函数将手机号的中间4位提取出来，然后再替换成星号即可。如下所示。

公式解读：

第一个参数首先确定需要替换的列为I列中的电话号码，第二个参数使用MID函数将需要替换的旧文本找到，第三个参数就是我们需要替换后到新文本，也就是4个星号，即“****”，最后一个参数为替换选定旧文本的第几个，这里就是1。

注意：日常工作中对客户电话号码/身份证号进行加密处理都可以使用这种方法。

二、SUMPRODUCT

1、基础用法

初看这个函数好像有点复杂，从字面上理解这个函数，它由两部分组成，第一部分是求和sum，第二部分是求积product。因此，SUMPRODUCT函数是把指定几个数组元素对应相乘然后返回乘积之和。

公式=SUMPRODUCT (array1， [array2]， [array3]， ...)

例如，现在我们知道每个客户购买商品的单价，可以通过计算客户购买商品数量与商品单价得到客户购买的总金额。

公式解读：

=SUMPRODUCT(L3:L7,N3:N7)

=L3*N3+L4*N4+L5*N5+L6*N6+L7*N7

=2*48.065+1*43.01+4*26.875+3*7.82+1*12.89

=282.99

2、多条件求和

公式=SUMPRODUCT((范围1=条件1）*（范围2=条件2）.......*（求和范围))

例如，我们要求年龄为31岁且购买数量为1的客户购买总金额，这是一个典型的多条件求和问题，除了我们之前介绍过的SUMIFS函数，使用SUMPRODUCT函数也可以解决此类问题。

公式解读：

=SUMPRODUCT((H3:H7=31)*(L3:L7=1)*(M3:M7))

即从年龄列中找出年龄等于31岁的客户，然后从购买数量列找出购买数量等于1的客户，最后把满足条件的客户购买金额求和。

3、多条件计数

公式=SUMPRODUCT((范围1=条件1）*（范围2=条件2）......)

例如，我们要求客户年龄为28岁且购买数量等于2的客户人数，使用SUMPRODUCT函数进行多条件计数。

公式解读： =SUMPRODUCT((H3:H7=28)*(L3:L7=2))

即从年龄列找到客户年龄为28岁的客户，然后从购买数量列中找到购买数量等于2的客户，将两个条件同时成立的情况进行计数得到最终结果。由于在这里是没有同时符合这两个条件的情况，因此结果为0。

三、SUMPRODUCT+COUNTIF

基本用法：对指定数据列进行排名。

假设，我们想对客户的购买单价进行排名，即对客户的消费能力进行排名，可以使用SUMPRODUCT+COUNTIF组合函数进行计算。

公式解读：

公式的前半部分($N$3:$N$7>N3)返回的是一个数组，求区域$N$3:$N$7中大于N3的单元格个数，公式的后半部分COUNTIF($N$3:$N$7,$N$3:$N$7)可以理解为一个辅助计算，实际没有太多作用，因此整个公式可以化简为：=SUMPRODUCT(($N$3:$N$7>N3)/1)+1，Enter回车得到最终结果。

四、总结

以上就是Excel中常用的部分特殊函数用法，到此Excel的系列内容我们就算正式介绍完了。当然还有一些Excel的一些常用技巧，例如，如何对数据中的重复值去重，如何设置条件格式，如何使用Excel中的透视表/透视图等等我们都没有介绍，因为这些基础内容不在我们本次课程的介绍范围，我们本系列的内容只对部分数据分析中常用到的Excel函数的基础用法进行介绍，掌握了这部分内容已经足够覆盖80%的数据分析日常工作场景，剩下的20%没有介绍的内容希望大家有兴趣自己去学习，感谢大家的理解与支持。下节课开始我们将进入SQL系列内容介绍，敬请期待！

喜欢我们的内容欢迎点赞，分享、关注我们的公众号，带你学习更多数据分析干货内容。