|
★ 编按 ★ 哈喽，大家好，欢迎来到部落窝教育！我是阿硕。最近，有小伙伴咨询了这样一个问题：公司每月的销售任务是一个区间，但是公司领导想让我计算一下区间的平均值，该如何做？ 这位小伙伴的数据如下图所示。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”，然后下拉复制填充公式，得到的结果如下图所示。 可以看到，这里并没有用到减负运算，这是为什么呢？请小伙伴们自己来思考一下哦！   |
|
|
