www.adminn.cn
站长正能量分享网!

在excel中如何进行区间查询

AD:阿里云服务器企业会员更优惠 腾讯云香港,韩国免备案服务器1.8折优惠

在制作成绩表格的时候经常会用到区间查询,比如在某个区间成绩属于优异,而另两个成绩的区间就是及格,这种情况我们应该使用什么函数进行解决呢?下文将为你讲解两种函数的使用,尤其是功能超强大的frequency函数要特别注意哦!

LOOKUP函数

第一个函数是大家耳熟能详的LOOKUP。

举个例子,如下图,A1:B6区域是一张成绩评价标准表,小于60不及格,大于等于60小于80为及格,大于等于80小于90为优良……依次类推。

现在我们要对某个同学的成绩做出评判。

G2单元格输入下面的公式,并向下填充:

=LOOKUP(F2,A$2:B$6)

假设这位同学大红花,她图表成绩得了98分,那么她的最终评价是:优异。

假设某天发挥严重失常,只考了68分,那么该公式返回的结果为:及格。

需要说明的是,LOOKUP的这个查询套路,即

LOOKUP(查找值,查找区域)

要求查找区域的首列数据升序排列,否则公式可能得出错误的结果。

FREQUENCY函数

第二个函数微信里平时很少给大家介绍,是FREQUENCY。

在EXCEL函数里,FREQUENCY是一个运算效率很高、功能异常强大的函数,在条件计数、查询、排序等方面都有很多妙用,但它又是一个难点函数,以至于用的人很少,少到什么地步?夜晚的时候,呼吸着纯净的雾霾,你抬头数数天上的星星就知道咧。

今天先简单介绍一个FREQUENCY区间查询的套路,感兴趣的亲们可以到ExcelHome论坛对这个函数自我充电下。

依然举个例子。

如上图,A1:B6单元格区域依然是一份成绩评价标准表。

F2:F3区域依然是大红花同学的两次得分记录,现在,依然需要对她的两次得分进行评级。

看起来似乎和上面的例题并无区别,但需要说明的是评分标准。

评分标准并不是0-9得A,10-14得B……诸如此类,而是寻找最接近的值,进而得出结果。

比如大红花的首次得分为13,13距离10相差3,距离15相差2,结论,13更靠近15,所以,大红花的评级结果为15所对应的C级,而非10所对应的B级。

简单的说,就是靠近谁,就属于谁——真是一个单细胞的女汉子啊。

G2输入公式,并向下填充:

=LOOKUP(1,0/FREQUENCY(0,ABS(A$2:A$6-F2)),B$2:B$6)

简单说明一下这个公式的运算过程。

ABS(A$2:A$6-F2)部分,计算得出A2:A6区域和F2之间差的绝对值(正数和零),得到一个内存数组:

{13;3;2;12;17}

Frequency函数,以ABS函数的计算结果为分段区间,对0进行计频。

由于frequency函数只在分段点首次出现时统计频数,且统计小于等于此分段点,大于上一分段点的频数,所以0所返回的计频位置,总是处于最接近0的那个分段点,本例中这个分段点是2,计数为1,其余分段点,计数为0。

依然得到一个内存数组:

{0;0;1;0;0;0}

上面那段话对于不懂FREQUENCY的小伙伴们而言简直如同天书,好吧,FREQUENCY函数将0扔到了最接近它的那个值身上,从此以后0就归那个值了,那个值得到了一个0,计数为1,从此过上了幸福的生活,其他人啥都没得到,所以都返回0,打了光棍——嗯,就是这么回事。

最后又是一个LOOKUP的查询套路:

LOOKUP(1,0/(条件),目标区域或数组)

0/FREQUENCY(0,ABS(A$2:A$6-F2)),构建一个由0和错误值#DIV/0!组成的数组,再用永远大于0的1作为LOOKUP的查找值,即可快速得出0所对应的目标区域结果。

此处的LOOKUP目标区域为B$2:B$6,因此得分13,返回15所对应的B4的值C。

此外需要说明的是,FREQUENCY函数支持分段区间乱序,所以并不需要得分区域必须升序排列。

模板优惠价: (点击购买)
版权声明:本文采用知识共享 署名4.0国际许可协议 [BY-NC-SA] 进行授权
文章名称:《在excel中如何进行区间查询》
文章链接:https://www.adminn.cn/news/26326.html
本站资源模板仅供个人学习交流,请于下载后24小时内删除,不允许用于商业用途,否则法律问题自行承担。2021.5月起,网站调整,暂不再分享免费模板。谢谢理解

Adminn.Cn 站长分享圈

帝国CMS精品模板腾讯云优惠券,代金券

本站源码仅供本地环境下学习借鉴研究使用!

源码请勿用于任何涉灰站点!净化网络,站长更有责!

支付宝扫一扫打赏