二维码 购物车
部落窝在线教育欢迎您!

困扰多年的区间型数据求平均值问题,Find函数一秒解决!

 

作者:阿硕来源:部落窝教育发布时间:2021-10-12 10:37:50点击:485

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

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

 

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

 

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

 

 

思路分析:

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

 

Step 1  通过FIND函数定位“-

 

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

 

 

函数释义:

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

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

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

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

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

 

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

 

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

 

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

 

 

函数释义:

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

 

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

 

提取完“-”左侧的数据,让我们再来提取“-”右侧的数据,即月销售任务的最高值。要实现这一需求,我们可以使用MID函数。MID函数的作用,就是从数据中间的某一位置开始,向右侧提取若干个连续的数据。我们在D2中输入“=MID(B2,C2+1,99)”,然后下拉复制填充公式,得到的结果如下图所示。

 

 

函数释义:

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

 

(2)  我们重点来看一下MID函数的第三参数。对于B2B3来说,应该用MID函数向右提取5位数,对于B4B5来说,应该用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)”,然后下拉复制填充公式,得到的结果如下图所示。

 

 

函数释义:

有的小伙伴可能会问,为什么在D2E2前面加上了减负运算(“--”)呢?这是因为,LEFT函数和MID函数都是文本函数,它们的返回值都是文本格式。刚才我们所写的LEFT函数和MID函数,得到的结果虽然看上去都是数字,但是它们的数据格式实际上却是文本,也就是说,它们是文本型的数据。所以,我们需要通过减负运算把它们变成数值型的数据,才可以让它们作为AVERAGE函数的参数参加运算。因为如果不这样,就会出现“#DIV/0!”的错误提示。

 

Step 5  函数嵌套

 

好了,小伙伴们,分步骤的函数我们已经都写出来了,下面,我们只要将函数嵌套在一起就行了。我们在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”,然后下拉复制填充公式,得到的结果如下图所示。可以看到,这里并没有用到减负运算,这是为什么呢?请小伙伴们自己来思考一下哦!

 

 

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

7个Excel小技巧,提高表格查看效率

Excel运用规范1:一个单元格只记录一条信息

快速整理不规范的Excel表格的7个公式

9条最实用的计算excel中关于日期的公式!(建议收藏)

版权申明:

本文作者阿硕;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。