ppp模式在网站建设的,网络舆情监测工作,建设实验室网站的意义,个人微信小程序教程一、基础用法
1、精确匹配
公式#xff1a;VLOOKUP(待匹配值#xff0c;查找范围#xff0c;范围列数#xff0c;查找方式)
定义好要输出表的表头和第一列#xff0c;第一列即为要查找和匹配的父内容#xff0c;在第二列输入公式#xff0c;被查找表中一定也要将待查…
一、基础用法
1、精确匹配
公式VLOOKUP(待匹配值查找范围范围列数查找方式)
定义好要输出表的表头和第一列第一列即为要查找和匹配的父内容在第二列输入公式被查找表中一定也要将待查找值放在第一列
公式举例VLOOKUP(B5,$B$54:$D$59,2,0)
一般情况下查找范围要绝对引用选中范围后fnf4切换绝对引用全部/行/列2代表待查找的值在查找范围的第二列0代表精确查找
2、区间匹配
公式VLOOKUP(待匹配值查找范围范围列数查找方式)
与精确查找不同之处在于待匹配值是一个具体值但是对应的查找范围中第一列是一个区间范围这时就要先将这个区间范围的最小值全部单列出来生成一个新列作为查找范围的第一列
公式举例VLOOKUP(B5,$B$54:$D$59,2,1)
此时公式会判断待匹配值与新第一列的数值的大小大于Max最小值后得到匹配值
3、反向查找
公式VLOOKUP(待匹配值查找范围范围列数查找方式)
如果待匹配值在查找范围的右侧就把待匹配值复制一列放到查找范围的最左边作为新的第一列再用公式
4、多条件查找
公式VLOOKUP(待匹配值查找范围范围列数查找方式)
如果待匹配值是两列或多列内容的组合在查找的时候要用将多列内容组合进行查找
公式举例VLOOKUP(A2B2,$B$3:$F$5,2,0)
这里待匹配值就是将A2和B2 的内容连接在一起进行匹配
二、进阶用法
1、屏蔽错误值
在匹配的时候遇到匹配值为空的时候会报错是要将空值为空展示需要用到IFERROR函数
公式举例IFERROR(VLOOKUP(F7,$B$6:$D$11,3,0),)
将原来的VLOOKUP函数放到IFERROR里面报错值统一展示为
2、通配符查找
利用部分关键词到查找范围中去进行匹配需要用到通配符如果是文本值查找需要将*用标注
公式举例VLOOKUP(*F17*,$B$16:$D$21,3,0)
指的是待匹配值中要包括F17的文本内容前后可以有别的内容
3、文本—数值互相查找
1待匹配值是文本查找范围中的第一列是数值此时要将待匹配值转为数值形式将文本*1即可
公式举例VLOOKUP(B27*1,$F$26:$G$31,2,0)
2待匹配值是数值查找范围中的第一列是文本此时要将数值转化为文本形式将数值用变为数值即可
公式举例VLOOKUP(F27,$B$26:$C$31,2,0)
4、查找时去除空格
用替代函数substitute替代范围 ,意味着将替代范围中的空格全部替换为空
公式举例1VLOOKUP(SUBSTITUTE(F38, ,),$B$36:$D$41,3,0)
这是将带匹配值中的空格全部去掉
公式举例2VLOOKUP(F45,SUBSTITUTE($B$44:$D$49, ,),3,0)
这是将查找范围中的空格全部去掉
5、查找时去除不可见字符
在匹配时待匹配值或查找范围中都有可能存在不可见字符此时要用clean函数去掉
公式举例VLOOKUP(CLEAN(F56),CLEAN($B$54:$D$59),3,0)
注意在执行的时候要ctrlshiftenter一起按才行
6、多列批量查找
1多列为顺序排列时
此时要用一个待匹配值查找出多列的信息多列是顺序排列可以一键横拉此时查找范围要用到column函数
公式举例1VLOOKUP($G65,$B$64:$E$69,COLUMN(B1),0)
注意
column( )函数的意思是第几列括号里填具体的单元格如果填A1就是第一列B1就是第二列
此时待查找值要将列用绝对值锁定因为往右一键横拉时待查找值所在列不会发生变化
2多列为乱序排列时
此时要用一个待匹配值查找出多列的信息多列是乱序排列无法一键横拉此时查找范围要用到match函数
公式举例2VLOOKUP($G76,$B$74:$E$79,MATCH(H$74,$B$74:$E$74),0)
注意
match待匹配值匹配范围函数的意思是从匹配范围中找到待匹配值所在列数最后返回数值
此时match中的待匹配值要将行用绝对值锁定因为是根据匹配表格中的第一行表头确定的在查找范围中的第几列同一列用的都是同一个表头所以固定行
7、跨多表查询
当要从多个sheet匹配数据时需要用到INDIRECT( )函数这个函数的作用是间接引用读取内的文本对应位置的数据所以内数据需要加上除非有公式的时候不用加
公式举例VLOOKUP($B126,INDIRECT(表COLUMN(C1)!$A$3:$F$8),6,0)
这里column(c1)代表3indirect后内的含义就是表3中绝对位置处的数据当然这里也可以把“表3”这个sheet的名字换成其他这样就可以直接用一个“”表示了但这样就不能横向拖拽自动填充表2、表3、表4了如果需要引用多表数据就需要每一列都用一个VLOOKUP函数了。
