查询引用,在Excel中的应用非常广泛,其中Vlookup函数可以称为查询引用之王,可以解决95%以上的查询引用问题。
一、Vlookup函数功能及语法结构。
功能:在指定的数据范围内返回符合查询要求的值。
语法:=Vlookup(查询值,数据范围,返回值列数,匹配模式)。
其中匹配模式有两种,分别为“0”或“1”。其中“0”为精准匹配,“1”为模糊匹配。
目的:查询“销售员”的“销售额”。
方法:
在目标单元格中输入公式:=VLOOKUP(H3,B3:D9,3,0)。
解读:
1、公式中H3单元格的值为查询值,B3:D9为需要查询的数据范围,第三个参数“3”为返回“数据范围”中第3列的值,“0”为精准匹配模式。
2、一句话解读:在B3:D9数据范围中,返回第3列中与查询值H3完全匹配的值。
二、Vlookup函数查询引用之反向查询。
目的:根据“工号”查询“姓名”。
方法:
在目标单元格中输入公式:=VLOOKUP(I3,IF(,C3:C9,B3:B9),2,0)。
解读:
1、公式=VLOOKUP(I3,IF(,C3:C9,B3:B9),2,0)中的第二个参数IF(,C3:C9,B3:B9)的作用为利用C3:C9和B3:B9单元格的值,形成一个形的数组,数组的值以“工号”,“销售员”的形式存储。
2、因为最终的目的是返回“销售员”,且形成的新数组中值以“工号”,“销售员”的形式存储,所以第三个参数为2。
三、Vlookup函数查询引用之多条件查询。
目的:根据“姓名”和“工号”查询对应的“销售额”。
方法:
1、在目标单元格中输入公式:=VLOOKUP(I3&J3,IF(,B3:B9&C3:C9,D3:E9),2,0)
2、Ctrl+Shift+Enter填充。
解读:
多条件查询时只需用“&”将多个条件或范围连接在一起即可。
四、Vlookup函数查询引用之多条件反向查询。
目的:查询“销售员”在指定“地区”的“销售额”。
方法:
1、在目标单元格中输入公式:=VLOOKUP(I3&J3,IF(,B3:B9&F3:F9,E3:E9),2,0)。
2、Ctrl+Shift+Enter填充。
解读:
多条件反向查询时只需用“&”将多个条件或范围连接在一起即可。
五、Vlookup函数查询引用之隐藏无匹配值。
目的:查询“销售员”在指定“地区”的“销售额”,当没有“销售额”时单元格为空。
方法:
1、在目标单元格中输入公式:=IFERROR(VLOOKUP(I3&J3,IF(,B3:B9&F3:F9,E3:E9),2,0),"")。
2、Ctrl+Shift+Enter填充。
解读:
1、Iferror函数的作用为判断一个表达式或公式是否存在错误,如果存在错误,则返回指定值,否则返回表达式或公式的执行结果。语法结构为:=Iferror(表达式或公式,返回值)。
2、当“销售员”在指定“地区”没有销售记录时,返回“”,暨空值,达到隐藏的目的。
六、Vlookup函数查询引用之“一对多”查询。
目的:根据销售记录表查询销售员的所有销售记录。
步骤:
1、插入辅助列。
方法:
1、在“姓名”列的前面插入一列。
2、输入公式:=COUNTIF(C$3:C3,$J$3)。
2、查询销售地区。
方法:
1、在目标单元格中输入公式:=IFERROR(VLOOKUP(ROW(A1),$B$3:$G$9,6,0),"")。
2、Ctrl+Shift+Enter填充。
3、拖动填充柄向下填充。
解读:
公式=IFERROR(VLOOKUP(ROW(A1),$B$3:$G$9,6,0),"")中的查询值用Row(a1)代替,其值从1开始不断的增加,当有对应的值时返回。
3、查询销售额。
方法:
1、在目标单元格中输入公式:=IFERROR(VLOOKUP($J$3&$K3,IF(,C3:C9&G3:G9,F3:F9),2,0),"")。
2、Ctrl+Shift+Enter填充。
3、拖动填充柄填充。
解读:
1、公式中$J$3&$K3的引用方式一定要注意,J3为绝对引用,而K3为混合引用或相对引用。
2、选择相应的销售员,其地区和销量自动更新,达到“一对多”查询的目的。
七、Vlookup函数查询引用之批量精准查询。
目的:根据实际需要批量精准查询对应数据。
方法:
在目标单元格中输入公式:=VLOOKUP(I3,C2:F9,MATCH(J$2,C$2:F$2,0),0)。
解读:
1、Match函数的作用为:返回定位值在指定范围中的相对位置,语法结构为:=Match(定位置,范围,匹配模式)。其中匹配模式有-1、0、1三种,分别为:“大于”、“精准匹配”、“小于”。
2、参数的引用要注意“相对”、“绝对”或“混合”引用。
结束语:
本文从实际需求出发,详细的解读了Vlookup函数在查询引用中的部分使用技巧,你Get到了吗?如果亲有疑问或更多的关于Vlookup函数的应用技巧,欢迎在留言区留言讨论哦!
领取专属 10元无门槛券
私享最新 技术干货