使用Excel函数实现Excel数据分段区间查找的方法
发布时间:2022-03-04 04:03:52
今天本文和大家分享的EXCEL知识是两个函数公式套路,有关数据区间查询的。分别是使用LOOKUP函数和Frequency函数实现Excel数据分段区间查找的方法。
LOOKUP函数
第一个函数是大家耳熟能详的LOOKUP。
举个例子,如下图,A1:B6区域是一张成绩评价标准表。用我拿手的中文翻译一下,小于60不及格,大于等于60小于80为及格,大于等于80小于90为优良……依次类推。
现在我们要对某个同学的成绩做出评判。
G2单元格输入下面的公式,并向下填充:
=LOOKUP(F2,A$2:B$6)
假设这位同学是EH在线培训课程【图表之美】的女汉子大红花,她图表成绩得了98分,那么她的最终评价是:优异。
假设某天大红花因为思念男友某人,导致发挥严重失常,只考了68分,那么该公式返回的结果为:及格。
需要说明的是,LOOKUP函数的这个查询套路,即
LOOKUP(查找值,查找区域)
要求查找区域的首列数据升序排列,否则公式可能得出错误的结果。
Frequency函数
第二个函数微信里平时很少给大家介绍,是Frequency。
在EXCEL函数里,Frequency是一个运算效率很高、功能异常强大的函数,在条件计数、查询、排序等方面都有很多妙用,但它又是一个难点函数,以至于用的人很少,少到什么地步?夜晚的时候,呼吸着纯净的雾霾,你抬头数数天上的星星就知道咧。
今天先简单介绍一个Frequency区间查询的套路,感兴趣的亲们可以到EH论坛对这个函数自我充电下。
依然举个例子。
如上图,A1:B6单元格区域依然是一份成绩评价标准表。
F2:F3区域依然是EH培训班图表之美的高材生大红花同学的两次得分记录,现在,依然需要对她的两次得分进行评级。
看起来似乎和上面的例题并无区别,但需要说明的是评分标准。
评分标准并不是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函数支持分段区间乱序,所以并不需要得分区域必须升序排列。
结束语
这就是今天和大家分享的两个有关区间段查询的函数套路,关于frequency部分,能懂就懂,不懂就算,套路嘛,关键时刻会套用就好,理不理解其实也没啥。呵呵。
猜你喜欢
- 还在寻找一组好看的文字壁纸吗?别急,今天小编给大家带来了6个简约创意文字壁纸合集 ,每一个创意文字壁纸简约的壁纸风格,带给您舒适的视觉体验,
- 据Microsoft-news报道,昨天晚些时候他们推送了新的安全补丁。其中Build 10074获得的安全补丁主要针对IE浏览器,代码为K
- ppt2013怎么设置音频文件图标?ppt中插入了音乐,想让别人知道,该怎么办呢?我们可以插入一个音频图表,下面我们就来看看音频图表的设计方
- 欢迎观看 Luminar Neo 教程,小编带大家学习 Luminar Neo 的基本工具和使用技巧,了解如何在 Luminar Neo 中
- Google Earth谷歌地球安装方法?Google Earth是卫星影像和航拍的数据整合,是一款虚拟地球软件。Google Earth谷
- 怎么用电脑刻录DVD光盘呢?有些用户想要使用电脑光驱刻录光盘,如何使用光驱刻录光盘?本文将给大家介绍通过Nero软件刻录DVD光盘的操作方法
- Win10系统用上了一段时间后,由于卸载过的软件的垃圾残留等各种因素造成了注册表垃圾越来越多,渐渐影响系统的正常运行。那么Win10如何清理
- Windows7系统如何删除用户账户?很多用户为了保护自己的电脑隐私,都会在自己的电脑上设置自己的账户,如果电脑用户开设用户越多,越占用系统
- Win7系统使用IE浏览器浏览网页的时候,会产生非常多的网页缓存,这些缓存占用大量C盘空间并且影响电脑的运行速度,如何清理IE浏览器的缓存呢
- Excel怎么查找重复内容?在表格中有重复的内容,数据比较多,如何快速标记出来呢?1、选中要查找的单元格,点击条件格式-突出显示单元格规则-
- 许多Win10用户都会遇到系统死机没有响应的问题,这个时候大家一般都会按机箱面板上的重启按钮强行重启或长按电源按钮强行关机处理。但是这两种操
- 开机后Ubuntu屏幕显示为最大亮度,重新调整屏幕亮度以后再重启Ubuntu系统,发现屏幕还是显示为最大亮度,所以又要再调整一次。每次重启都
- 本文介绍Microsoft Excel中IMAGINARY函数的语法和用法。函数说明IMAGINARY函数的主要作用是返回以 x+yi 或
- 我们在使用wps文字编辑文档的时候,一般都会开启自动备份功能,那么,我们应该怎样查看备份的文件呢?下面就让小编告诉大家在wps文字中如何查看
- 使用过Win7系统的朋友,当打开“计算机”或者是资源管理器时,会发现左侧的工具栏相比此前的XP系统确实变化很大,我们会发现有一个“库”的名称
- 最近很多的用户们爱上了命运2这款游戏,但是很多用户们在玩游戏的时候,游戏会出现卡死、闪退的情况,导致游戏不能够正常的进行玩耍,那么这个问题需
- 首先,打开一个需要处理的表单,您可以看到第一列已经编号。我们要做的是保持这一栏编号的连续性。用鼠标左键双击单元格编号“1”,并在其中输入函数
- Win 8早已经问世了,专业版已经推出,笔者率先尝试了一下,感觉非常不错,操作体验很流畅,如果是平板的话,哪会爽到家的,台式机也很不错,喜欢
- 在Excel中录入重要的数据都是大家的习惯了,这个时候为了不让他人随意篡改就必须给Excel文档加密,具体要怎么做呢?接下来是小编为大家带来
- Iridient开发人员设置文件仅存储您的设置调整值,不存储任何实际图像数据。有时会交替使用术语“设置”和“预设”。今天分享的就是Iridi