|
今天与大家分享一个群友的问题，有一列数据，需要求出最小值及其上下所在位置（共三个数）的平均值，如图： 最小值为2，那么就要计算绿色三个数据的平均值，这就一个按条件确定区域并进行统计的实例。 在日常遇到的问题中，可能与本例完全一样的很少见，但是同一类型的可能会经常遇到，今天通过对这个例子进行分析，希望带给大家一种解决问题的思路，闲话打住，开始分析这个案例。 思路分析 本例的最终目的是要计算平均值，所以一定需要AVERAGE函数（如果是求和的话换成SUM函数即可），我们知道，AVERAGE函数是计算给定区域数值的平均值，如何确定统计区域（绿色的三个单元格）就变成了关键问题，并且这个区域是需要使用函数来确定（作为AVERAGE的参数）。 能够得到一个引用区域的函数无非是offset，indirect和index（最后这个很少人知道哦），具体使用哪个就看个人喜好了，本例选择offset函数。关于这个函数的基本用法可以参考本文：揭开Offset函数神秘的面纱 简单来说，使用offset函数得到一个区域，需要提供五个信息（五个参数），设定一个相对固定的基点、区域起始位置相对于基点的行列偏移量（这是两个信息）、区域的大小（高度和宽度）。 就本例而言，五个信息的确定可以这样分析： 1、基点我们选择A1单元格（如果你的数据并不是从第一行开始的话，可以根据实际情况进行调整）； 2、区域的大小可以确定为3（高度）和1（宽度），区域起始位置A13（注意这个起始位置是随着最小值位置进行变化的）； 3、区域相对于基点的列偏移可以确定为0，因为在同一列，行偏移是最小值所在的单元格（A14）向上一行。 如果这段分析能看明白，后面的就容易多了，如果看不明白的话，建议先巩固一下基础方面的能力。 通过以上分析，可以发现区域的确定与最小值在这一列的位置密切相关，因此考虑用match函数，关于这个函数的用法可以参考：【函数学堂】MATCH函数面面观（上）、【函数学堂】MATCH函数面面观（下）。 同时还需要最小值函数min，大体思路就有了。 公式构造 对于新手来说，理清楚思路是第一步，确定使用哪些函数，接下来就需要一步一步通过函数嵌套来得到最终的结果。 在初期往往做不到直接写出很多函数嵌套的公式，这时候借助辅助列来实现分步写公式就非常有必要了（辅助列一直是老菜鸟的最爱）。 第一步：使用min函数找到最小值=MIN(A2:A24) 这一步非常基础，应该都没问题。 第二步：使用match确定最小值的位置=MATCH(C2,A2:A24,0) 为了方便理解，b列加了一列备注位置，也不算很难理解，最小值在这一列（A2:A24）的第13个位置。我感觉这一步应该也没问题。 第三步：使用offset函数确定区域（重点和难点来了）=OFFSET(A1,D2-1,0,3,1) 公式是错误值，难道有问题了？ 其实没问题，我们可以利用f9（在编辑栏选择公式按f9键）将公式结果显示出来： 正好就是需要统计的三个数字，之所以显示为错误值是因为公式结果是一个一列三行的区域（高度3宽度1），无法在一个单元格进行显示造成的。 不理解公式的话，再回头去看看前一段的思路分析吧。 如果第三步也没问题的话，就简单了。最后一步只需要求平均值即可。 =AVERAGE(OFFSET(A1,D2-1,0,3,1))，直接使用offset这部分作为average的参数。 至此，我们通过四步得到最终的结果。 完成公式合并 公式的合并其实就很简单了，=AVERAGE(OFFSET(A1,D2-1,0,3,1))在这个公式里，D2是match函数，只需要将D2中=后面的内容复制，粘贴在这个公式里即可：=AVERAGE(OFFSET(A1,MATCH(C2,A2:A24,0)-1,0,3,1))，说简单点，就是用MATCH(C2,A2:A24,0)替换=AVERAGE(OFFSET(A1,D2-1,0,3,1))中的D2。 完成后再用min函数替换公式里的“C2”：=AVERAGE(OFFSET(A1,MATCH(MIN(A2:A24),A2:A24,0)-1,0,3,1)) 这样就完成了函数的嵌套，为了美观，可以在最外层再来个round，=ROUND(AVERAGE(OFFSET(A1,MATCH(MIN(A2:A24),A2:A24,0)-1,0,3,1)),2) 结语 今天这个例子老菜鸟讲的比较罗嗦，并且也不一定是最简单的公式，只求能够起到抛砖引玉的作用，让初学者可以借鉴一二，了解分析问题的思路以及辅助列在函数嵌套方面的作用。 同时这个公式只能适用于正常情况，也就是数据区域没有空白单元格，并且最小值不会出现在第一个和最后一个位置上，如果是特殊情况，就需要另寻思路 在日常运用中，本例可以借鉴的地方很多，使用min确定核心条件有可能会变成max确定条件，求平均值有可能会变成求和，上下位置有可能会变成下方的几个位置等等。总之一句话，理解原理以后活学活用，将知识变为技能才是最终目的。 感谢长期关注本平台的各位老师、朋友的支持，老菜鸟有两件事需要大家帮忙：1、转发本文让更多人看到；2、推送的第二篇文章点开看看就行，不需要转发点赞 |
|
|
