送人玫瑰,手有余香,请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径!
【置顶公众号】或者【设为星标】及时接收更新不迷路
小伙伴们好,今天和大家分享一则FREQUENCY函数的小应用。我们在日常工作中,总会遇到“要找和一个数差值最小的一个数”这种情况。而FREQUENCY函数就是解决这类问题的最好的工具。
来一起看看题目是怎样的吧!
这种题目,源数据量不是很大的情况下,做一个辅助列,也能很快找到正确的答案。下面我们就一起来看看FREQUNECY是如何处理的吧!
FREQUENCY函数的作用就是在大于等于某一个数的最小值上计频。
在单元格C2中输入公式“=LOOKUP(,0/FREQUENCY(0,($A$2:$A$10-100)^2),$A$2:$A$10)”即可。
思路:
两个数字的差额最小,那么他们差的平方值也一定是最小的。为什么要做平方值呢?因为两数相减,结果有正有负。将结果全部转换为正值,方便FREQUENCY应用
FREQUENCY函数在上述结果中对“0”计频,找到最小的哪一个差的平方值,其结果为{0;0;0;1;0;0;0;0;0;0}
0/()部分,将大于0的数字都转换为0,所有0都转换为错误值
接下来就很简单了,利用LOOKUP函数的二分法就可以很快找到正确答案了!
我们换一种更加直接的思路来试着解一解这道题目。
既然要找最接近的值,那么就先把最小的差值给找出来。然后就可以利用IF函数对满足条件的数据进行处理了。
在单元格C2中输入公式“=SUM(IF(ABS($A$2:$A$10-100)=MIN(ABS($A$2:$A$10-100)),A2:A10,0))”,三键回车。
思路:
ABS($A$2:$A$10-100)部分,是差值的绝对值
MIN(ABS($A$2:$A$10-100))部分,是最小的差值
接下来利用IF函数,对于满足条件ABS($A$2:$A$10-100)=MIN(ABS($A$2:$A$10-100))的,返回单元格区域A2:A10中对应的数值;不满足的返回0
SUM函数求和
不过这里朋友们要注意一下,如果源数据中没有重复的数值,这个公式最外层嵌套SUM函数是没有问题的。如果有重复的数值,那么这里了就不能嵌套SUM函数了。可以改用MAX函数。朋友们,你们知道这是为什么吗?
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
我就知道你“在看”
联系客服