上周介绍了if逻辑判断、rand随机函数、abs绝对值函数、round取整函数的使用方法,这周来介绍Excel里最为常用的查找引用函数。 阅读提示:
一、Vlookup 01 vlookup =vlookup(找什么单元格,在哪个区域找,找目标区域哪一列的值,模糊/精确) Vlookup函数通常对两个表(表1,表2)进行操作,两个表中含有某一个相同的字段(学号),要从表2中匹配出来表1中没有的字段(年龄)。 Lookup value:要找的单元格,此处为A2,学号这个单元格。 Table array:从哪个区域找,此处为表2所在的区域。 Col index num:选择区域的第几列,默认序号是从1开始。 Range lookup:0是精确查找,1是模糊查找,一般使用精确查找的较多。 查找的结果如图,我们注意到1907一行没有找到,这是因为表2里没有1907. 注意1:要查找的区域与查找区域的相同的字段必须为首列,否则会出错。如上图,我们把区域选择成F:H,学号是共同的字段,但学号一列不在所选区域的首列,这时Excel会找不到该值。 注意2:相同字段格式要相同。若查找区域格式为数字,而被查找区域格式为文本,则查不出来。此时可以对被查找区域的文本格式字段进行分列处理。 注意3:如果被查找区域相同字段里有多个相同的单元格,如表2中学号1901出现两次,系统默认只取第一个出现的值,即20. 这里注意,选择区域的时候除了F:G这样选以外,还可以$F$2:$G$8这样选,但注意这样选的时候要加绝对引用的符号。 02 vlookup模糊匹配 vlookup模糊匹配类似if函数嵌套,查找的是某个区间内的值,而不是精确到某个值的值。 记得上节内容if函数里的if嵌套的例子吗,对不同成绩段的学术给予及格、良好、优秀的评定,我们用的是if嵌套,IF(E2>90,"优秀",IF(E2>=70,"良好",IF(E2>=60,"及格","不及格"))),vlookup模糊匹配同样可以实现,模糊匹配和精确匹配的原理一样,只是把精确匹配的0改为1即可。 可以发现,用vlookup模糊匹配做出来的东西和if嵌套是一样的。所以说,函数这个东西是很相通的,关键不在于用什么函数,在于对函数的理解。 03 hlookup Hlookup和vlookup的原理一模一样,只是vlookup里列字段,是对列进行匹配,hlookup是对行进行匹配,行为字段,如下图。 二、Match+index函数 Match函数确定坐标,Index函数精确定位坐标下对应的值。 Match函数返回指定范围内值所在的序号。 Lookup value:是要查找的值,这里是G26单元格张三 Lookup array:是要查找的范围,张三属于姓名,所以范围是姓名一列 Match type :同vlookup一样精确匹配0 最后返回的值是1,张三在查找区域的第一行。 同样的,我们查找平均分这个单元格在B25:E25这个区域的第4个值。 结合index函数定位, Array:查找区域 Row num:第几行,用match函数算出 Column num:第几列,用match函数算出 最后定位出的结果可以结合数据有效性进行展示。 相对引用& 绝对引用
Excel的查找引用的函数就介绍到这里 了,下期讲解日期、年月和文本函数。 @文章属原创,转载请联系作者 @作者:虾壳,世界500强企业数据分析从业者 |
|