使用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部分,能懂就懂,不懂就算,套路嘛,关键时刻会套用就好,理不理解其实也没啥。呵呵。
猜你喜欢
- 我们经常把移动硬盘插入电脑进行下载或者上传文件,电脑会自动识别、检测并显示移动硬盘的盘符,但是有时候也会出现电脑不显示盘符的问题,查了半天也
- 升级到了win11系统以后,发现windows11系统的默认开机音乐非常不好听,想要重新修改开机音乐。那么如何设置win11开机音乐?针对这
- 有的朋友因为升级office办公软件需要将office 2007卸载从而安装高版本的office,但是由于office2007卸载不彻底而无
- Win10电脑相机无法隐藏怎么办?Win10电脑中许多功能都设有隐私功能,以方便保护用户的隐私安全。而有些用户想要改变自己的隐私设置的时候发
- 昨天凌晨,苹果同时给大家推送了iOS 16和iOS 15.7两个正式版的更新,其中iOS16正式版的版本号为20A362,更新包大小在5GB
- iOS15.6 Beta 1发布后的第13 天,苹果给大家推送了iOS / iPadOS 15.6 开发者预览版 Beta 2 更新。iOS
- win10有安全警报功能,用来提示危险应用,能让我们避免错误操作造成不必要的麻烦。可是有些不需要报警报的情况下,它也会弹出,这样次数多了难免
- 下面继续介绍在满足指定的条件时才执行相应代码块的循环结构,接着来看看Do Until循环。Do Until循环与Do While循环的结构相
- Excel 2010也较前一版有很多的改进,但总体来说改变不大,几乎不影响所有目前基于Office 2007产品平台上的应用,不过Offic
- 在编辑Word文档时,我们经常需要输入英文,但是Word文档在输入英文单词时默认会将句首第一个字母大写,有时候我们不希望首字母变成大写,那么
- 在Excel中录入好数据以后经常需要把窗口冻结了再进行数据核对,或许有的朋友并不知道窗口该如何冻结,如果不懂的朋友欢迎一起来学习探讨吧。接下
- 为了让我们的word文档更美观,我们可以在文档中加入水印,让文字在水印的上面,看起来朦朦胧胧,就更有另一番效果。那么下面就由小编给大家分享下
- 本文介绍如何从Word工具栏中启动应用程序,以下操作在Word 2002和Word 2003中通过。单击Word“工具”菜单下的“自定义”命
- 日常工作中,我们常常需要建立一些有规律的数据库。例如我为了管理全乡的农业税,需建立一数据库,该数据库第一个字段名为村名,第二个字段名为 组别
- 本文所介绍的方法仅限于最简单的斜线表头,即只有两个标题的斜线表头。技术要点:使用“单元格格式”对话框中的“边框”选项卡设置斜线,使用“对齐”
- Excel中如何按时间进行排序呢?接下来是小编为大家带来的excel中按时间排序的方法,供大家参考。excel中按时间排序的方法:按时间排序
- 在Excel工作表中输入数据时,利用数据验证功能能够创建下拉列表,用户可以通过选择相应的选项来输入内容,这样可以实现数据的快速输入。使用该功
- 很多人在使用excel的时候都喜欢个性化设置,比如设置个图片当作excel的背景,或则是把excel默认打开的字体换一下,特别是雅黑字体刚出
- sumif函数语法是:SUMIF(range,criteria,sum_range)sumif函数的参数如下:第一个参数:Range为条件区
- 很多的时候我们只是需要对工作表中的部分单元格进行锁定,怎么样才能达到这样的效果呢?今天,小编就教大家在Excel表格中进行锁定部分单元格的操