聚热点 juredian

我折腾到半夜,同事用这个“万能”函数,30秒查找提取数据交给领导

编按

Hello大家好,Find函数是Excel中非常常见的查找函数,除了自身的查找定位功能外,它还经常与其它函数嵌套使用。今天我们就来学习一下Find函数搭配Average、Left、Mid函数解决区间数据平均值的问题。对于一长串的嵌套函数,各位小伙伴也不要害怕,阿硕老师今天一步一步给你讲得明明白白的。连函数苦手小E都能学会,相信你也可以~

哈喽,大家好,欢迎来到部落窝教育!我是阿硕。最近,有小伙伴咨询了这样一个问题:公司每月的销售任务是一个区间,但是公司领导想让我计算一下区间的平均值,该如何做?

这位小伙伴的数据如下图所示。A列中的数据是月份,B列中的数据是公司制定的月销售任务。根据公司核算制度的要求,月销售任务的平均值是月销售任务的最低值与最高值的平均值。下面,就让我们一起来看一看如何解决这个问题吧!

【思路分析】

通过上图,我们可以看到,公司的月销售任务是一个区间,这个区间是由短斜杠“-”连接的,“-”的两端,分别是月销售任务区间的最低值和最高值。所以,我们解决这个问题的思路,就是先找到“-”的位置,然后再分别提取其左、右两侧的数据,最后,再计算平均值。

扫码入群,下载Excel图表文件练习

Step1 通过FIND函数定位“-”

在C2中输入“=FIND("-",B2,1)”,然后下拉复制填充公式,即可找到“-”在月销售任务这一列数据中的位置,如下图所示。

【函数释义】

FIND函数的作用是对数据中某个字符串进行定位,返回其位置的值。

①FIND函数的第一参数是要定位的某个字符串,在本例中为“-”,所以我们以“-”作为第一参数;

②FIND函数的第二参数是含有要查找的字符串的单元格,在本例中,由于我们是要判断“-”在B列中的位置,所以我们用B2单元格作为第二参数(注:函数下拉之后,就会依次变成B2、B3、B4、B5);

③FIND函数的第三参数是定位的起始位置,在本例中,我们是从B2单元格的第1个字符开始查找“-”,所以我们以“1”作为第三参数。

④由上图可见,FIND函数的返回值为依次为6、6、7、7,这就表明“-”在B2:B5中的位置分别是在第6、6、7、7位。

Step2 使用LEFT函数提取“-”左侧的数据

刚才我们已经通过FIND函数定位到了“-”的位置,接下来,让我们来提取“-”左侧的数据。由于月销售任务的最低值是“-”左侧的内容,所以我们使用LEFT函数来提取“-”左侧的数据。

我们在D2中输入“=LEFT(B2,C2-1)”,然后下拉复制填充公式,得到的结果如下图所示。

【函数释义】

这里有一点需要注意:由于 “-”分别位于B2:B5的第6、6、7、7位,而B2:B5的前5、5、6、6位数字刚好是我们需要的内容, “-”的位置与我们要提取的数字的位数刚好相差1。所以我们在写LEFT函数时,第二参数应为FIND函数的返回值再减1,即为“C2-1”。

Step3 使用MID函数提取“-”右侧的数据

提取完“-”左侧的数据,让我们再来提取“-”右侧的数据,即月销售任务的最高值。

要实现这一需求,我们可以使用MID函数。MID函数的作用,就是从数据中间的某一位置开始,向右侧提取若干个连续的数据。

我们在D2中输入“=MID(B2,C2+1,99)”,然后下拉复制填充公式,得到的结果如下图所示。

【函数释义】

①刚才我们已经说过,“-”分别位于B2:B5的第6、6、7、7位,所以从B2:B5的第7、7、8、8位开始直至数据的末尾,恰好是我们需要提取的数据,“-”的位置与我们要提取的数据长度之间,刚好也相差1。所以我们在写MID函数时,第二参数应为FIND函数的返回值再加1,即为“C2+1”。

②我们重点来看一下MID函数的第三参数。对于B2、B3来说,应该用MID函数向右提取5位数,对于B4、B5来说,应该用MID函数向右提取6位数。可以看到,提取的位数并不统一。那么,MID函数的第三参数应该如何写呢?阿硕是使用“99”来作为MID函数的第三参数的。这是为什么呢?

因为向右提取的数据的位数是不确定的(假设5月份的销售任务是900000-1100000,则我们要向右提取7位数),所以我们可以找一个比较大(能够涵盖实际应用中可能遇到的数据长度)并且方便输入的数字来作为MID函数的第三参数!

根据常用的使用习惯,我们一般使用“99”来作为第三参数。因为实际工作中的数据一般不会超过99位,并且输入两个“9”相对于输入两个不同的数字还是相对更符合我们“偷懒”的需求的!

有的小伙伴可能还会问,“-”后面的数据不足99位,但是我们要提取99位,会不会出问题?答案是不会!因为如果数据位数不足99位的话,MID函数提取的数据是以实际长度为准的。

Step 4 使用AVERAGE函数计算平均值

好了,提取出了“-”左、右两侧的数据之后,接下来,让我们计算平均值。计算平均值,我们可以使用AVERAGE函数。

我们在F2中输入“=AVERAGE(--D2,--E2)”,然后下拉复制填充公式,得到的结果如下图所示。

【函数释义】

有的小伙伴可能会问,为什么在D2和E2前面加上了减负运算(“--”)呢?

这是因为,LEFT函数和MID函数都是文本函数,它们的返回值都是文本格式。刚才我们所写的的LEFT函数和MID函数,得到的结果虽然看上去都是数字,但是它们的数据格式实际上却是文本,也就是说,它们是文本型的数据。

所以,我们需要通过减负运算把它们变成数值型的数据,才可以让它们作为AVERAGE函数的参数参加运算。因为如果不这样,就会出现“#DIV/0!”的错误提示。

Step5 函数嵌套

好了,小伙伴们,分步骤的函数我们已经都写出来了,下面,我们只要将函数嵌套在一起就行了。

我们在G2中输入“=AVERAGE(--LEFT(B2,FIND("-",B2,1)-1),--MID(B2,FIND("-",B2,1)+1,99))”,就可以得到我们想要计算的月销售任务的平均值啦!本步骤的结果如下图所示。

小彩蛋:

有的小伙伴可能觉得在写AVERAGE函数的时候,用减负运算有那么一丢丢麻烦,想问问有没有什么办法可以避免这个问题?

感兴趣的小伙伴,可以在H2中输入“=(LEFT(B2,FIND("-",B2,1)-1)+MID(B2,FIND("-",B2,1)+1,99))/2”,然后下拉复制填充公式,得到的结果如下图所示。

可以看到,这里并没有用到减负运算,这是为什么呢?请小伙伴们自己来思考一下哦!

搜索建议:
热传

 开学周记

开学周记合集8篇愉快又充实的一周又告一段落了,我们一定都积累了不少宝贵的经历,让我们通过周记发表自己的意见和想法吧。什么样的周记才是好的周记呢?以下是小编精心整...(展开)