这节课主要是教大家用一下VLOOKUP函数中的近似匹配。

像往常一样,我们先讲原理,再讲实战应用。

excel匹配功能怎么使用(VLOOKUP函数中的近似匹配到底怎么用)(1)

=VLOOKUP(23,B2:B16,1,TRUE)

如上面的图所示,我所写的VLOOKUP函数最后第四个参数是选择的近似匹配,那么可以看出我想要达到的目的是,在左边这一列数字中找到和23最接近的数字,然后我回车一下,看下结果

excel匹配功能怎么使用(VLOOKUP函数中的近似匹配到底怎么用)(2)

结果为22

最后得到的结果为22,显然没有问题,因为在左边的数字表格中和23最接近的数字就是22。OK,那我们再来一个,我把查找数字改为56。

excel匹配功能怎么使用(VLOOKUP函数中的近似匹配到底怎么用)(3)

=VLOOKUP(56,B2:B16,1,TRUE)

然后我回车一下,看下结果

excel匹配功能怎么使用(VLOOKUP函数中的近似匹配到底怎么用)(4)

结果为55

最后结果显示为55,貌似也没有任何问题,因为在左边的数字表格中和56最接近的数字就是55。

其实这里我给大家引导了一个错误的思想,大家可能已经认为VLOOKUP函数的近似匹配就是去查找并引用和查找对象最接近的数值,其实不然,我这里再换个查找对象,来推翻这个理论。

excel匹配功能怎么使用(VLOOKUP函数中的近似匹配到底怎么用)(5)

=VLOOKUP(33,B2:B16,1,TRUE)

这次我查找的数字是33,按道理来讲,最后结果是不是应该是34,因为左边表格中和33最接近的是34。OK,我们回车一下,看看结果

excel匹配功能怎么使用(VLOOKUP函数中的近似匹配到底怎么用)(6)

结果为22

最后结果竟然为22。那么就说明VLOOKUP函数中的近似匹配,并非是完全的去引用和查找对象最相近的数据。那正确的原理是什么呢?下面大家要非常用心阅读了,就是它的原理。

近似匹配其实依然是去匹配和查找对象最接近的数值,但是它只会在小于或者等于查找对象的数值中查找,这个就是VLOOKUP函数近似匹配的原理:匹配查找区域内小于查找值的最大值。

那原理讲完我们说下应用,其实最常见的应用在于计算个人所得税,但是这个是一个嵌套函数,过于复杂,我之前发过一个视频,大家可以去看下,是完整的教学。这里我们实战的案例是通过近似匹配对成绩单进行评级。如图所示

excel匹配功能怎么使用(VLOOKUP函数中的近似匹配到底怎么用)(7)

表格原数据

左边的数据是学生的成绩单,右边是评级标准,我们的目的就是快速的对每一位学生进行评级。

第一步,我写一组数据,分别是评级的总分成绩区间中的最小值

excel匹配功能怎么使用(VLOOKUP函数中的近似匹配到底怎么用)(8)

总分成区别绩最小值

第二步,我写出这个公式来“=VLOOKUP(F2,$J$5:$L$9,3,TRUE)”

excel匹配功能怎么使用(VLOOKUP函数中的近似匹配到底怎么用)(9)

书写函数

为了让大家看清,其中第一个参数是红色背景的总分成绩,第二个参数是黄色背景的区域(这里需要绝对引用,如果不懂什么是绝对引用,下一节课我会讲的),第三个参数是第3列,第四个参数是近似匹配。

OK,我们回车一下,看看结果

excel匹配功能怎么使用(VLOOKUP函数中的近似匹配到底怎么用)(10)

结果正确

254分,属于251—300这个区间,评级属于S级,没有问题。然后我们整体往下拖拽快速填充

excel匹配功能怎么使用(VLOOKUP函数中的近似匹配到底怎么用)(11)

快速填充

检查一下,都是没有问题的,这样我们的结果就达到了。

最后我们再解释一下原理,还是以第一个数据为例

excel匹配功能怎么使用(VLOOKUP函数中的近似匹配到底怎么用)(12)

解释原理

其实这个VLOOKUP函数的意思就是说,在0/101/151/201/251这一组数据中找到与254最接近并且小于它的数值,找到的肯定是251,然后再引用251对应的评级,就是S级,其他同理。