通过文本你可以学到: ▼Excel相对引用与绝对引用 ▼Vlookup函数如何同时返回多列 ▼Vlookup函数模糊匹配
在入门篇,我向大家讲解了Vlooup函数的基础知识和示例,可以看这里进行回顾: Vlookup函数是比较典型的“会者不难”这一类型的,他有很多的灵活变换。 掌握这些技巧,你会发现看似很难解决的问题,现在有了思路。 - 01 - 相对引用与绝对引用 ▼为什么要讲这个知识点 因为Vlookup函数的四个参数中,有两个涉及到了引用范围的概念。 VLOOKUP(查找目标,查找范围,返回值的列数,精确OR模糊匹配) ①查找目标 ②查找范围 ▼什么是相对引用和绝对引用? 相对引用:引用的是单元格的相对位置。如果函数所在单元格的位置改变,引用也随之改变。默认情况下,Excel中的函数使用相对引用。 绝对引用:如果不希望引用的单元格随着函数的位置变化而变化,则为绝对引用。行号'和'列号'前加上美元符号($),这样就是单元格的绝对引用。 ▼示例 本例中,默认的是使用相对引用。 可以发现,将公式向下复制时,函数中第一个参数由G3变为G4,则顺利查找出G4单元格对应的年龄值;而向右复制时,第一个参数由G3变为H3,H3并不是我们要查找的目标,所以无法正确返回结果。
结论:如果使用相对引用,函数向下复制时,引用的单元格的“行数”会递增;函数向右复制时,引用的单元格的“列数”会递增。 ▼如何切换 将光标定位于函数中引用单元格,按F4,进行四个引用状态的切换。 ①默认完全相对引用 ②按一次F4:行和列绝对引用 ③按二次F4:行绝对引用,列相对引用 ④按三次F4:行相对引用,列绝对引用 - 02 - 同时返回多列值 VLOOKUP(查找目标,查找范围,返回值的列数,精确OR模糊匹配) VLOOKUP函数的第三个参数是查找返回值所在的列数,如果我们需要查找返回多列时,这个列数值需要一个个的更改,比如返回第2列的,参数设置为2,如果需要返回第3列的,就需要把值改为3。。。 列数不多的情况,当然可以手动修改,那如果是几十列呢? 能不能让第3个参数随着函数的位置不同,自动变更?即向后复制时自动变为2,3,4,5。。。 ▼引入新的函数:Column COLUMN函数可以返回指定单元格的列数,比如 =COLUMNS(A1)返回值1(A1所在的列为第一列) =COLUMNS(B3) 返回值2 (B3所在的列为第二列)
▼如何应用 使用COLUMN函数的相对引用,=COLUMN(A1)向右复制时,A1会变成B1,C1,D1。。这样我们用COLUMN函数就可以转换成数字1,2,3,4。。。 注:这里的关键是将VLOOKUP函数的第三个参数设置为动态变化的。 ▼举例说明 需要同时查找性别,年龄,成绩,爱好。 ①在B16单元格中输入公式:=VLOOKUP($A16,$B$2:$F$11,COLUMN(B1),0) ②拖住B16单元格右下角的黑框,向右拖动进行复制,然后向下进行复制 ▼公式说明 ①$A16:这里只有列前边有$符号,意味着列是绝对引用,行是相对引用。这样就能实现在向右复制时,列数保持不变(一直是A列),行递增变化($A16→$A17→$A18) ②$B$2:$F$11:查找范围的引用区域,行和列均为绝对引用。确保函数在复制过程中,查找的范围不会变更。多数情况下,查找范围都是需要固定的。 ③COLUMN(B1):在性别这一列的函数中,第三个参数值需要设定为2(因为性别在查找区域中处于第二列),向右复制是需要递增。 所以关键是COLUMN()的第一个返回值是2即可,这里的参数可以是B列的任一单元格。 - 03 - 模糊匹配 Vlookup函数的最后一个参数,如果是0(False)的话,代表精确匹配,在初级已经讲过了;如果是1(True)的话,是模糊模糊匹配。 模糊匹配如何应用呢? 首先我们需要了解一下VLOOKUP函数模糊查找的两个重要规则: ▼规则一:引用的数字区域一定要从小到大排序(数字是从小到大排序,字符按照首字母排序)。杂乱的数据会返回意想不到的数据。如下面表一列符合模糊查找的前题,表二则不符合。 ▼规则二:模糊查找,给定一个无法精确匹配的数值,它会找到和它最接近,但比它小的那个数。 比如我们要查找雷布斯的年龄(注意原始数据表中并没有雷布斯的名字),所以需要使用模糊查找。 输入公式=VLOOKUP(G5,A2:E11,3,1) 可以看到,返回了25,这是蒋欣的年龄。 为什么会这样,跟着默念:模糊查找会返回和它最接近,但比他小的数值。 注:本列中使用精确匹配会返回错误值,因为函数找不到匹配的值。 但是,模糊查找有什么卵用呢? 当然有卵用!!!
▼最后一个实例 【例】:根据成绩等级根则,算出各位学生的得分等级。 解答:使用Vlookup函数的模糊匹配,结果秒出有木有!! 比用什么if函数简单多了。 公式=VLOOKUP(B10,$A$1:$C$6,3,1) ▼结论 根据模糊查找的规则,VLOOKUP科进行数字的区间查找(即查找给定的数字属于哪个区间)。 学完Vlookup入门教程,再结合上面这三个知识点,已经能够解决80%的查找问题。但是你还是会碰到Vlookup无法解决的查找问题,比如:如何从右向左查找、如何多条件查找…… - 04 - 总结 ①当需要引用的单元格随函数位置变化而变化时,使用相对引用,反之使用绝对引用;F4键可以快速进行相对引用和绝对引用的切换。 ②COLUMN函数可以创造返回列数的动态变化 ③模糊查找可以找到数值的区间 ●本文编号319,以后想阅读这篇文章直接输入319即可 ●输入m可以获取到全部文章目录 ●输入c可以获取到全部动画下载地址 |
|
来自: 是在下 > 《Vloookup函数》