当前位置: > 热议

Excel中最全的查询引用技巧,都在此文,值得收藏学习!

时间:2022-04-23 08:19:40 热议 我要投稿

对数据的存储和处理是Excel的拿手好戏,数据的处理中,就包括数据的查询引用,如果我们不掌握一定的查询引用技巧,在海量的数据中找到或调用我们需要的数据,就会比较困难。今天,小编带给大家的是Excel中的查询引用技巧。

一、Excel查询引用:Vlookup函数法。

目的:根据“员工姓名”查询对应的“月薪”。

使用函数:Vlookup。

函数功能:在指定的数据区域中,搜索首列中满足指定条件的元素,确定待检索单元格在区域中的行号后,再进一步返回指定单元格的值。

语法结构:=Vlookup(查询值,数据范围,返回值所在的列,匹配方式)。

参数解读:匹配方式有2个值,分别为0和1,0为精准查询,1为模糊查询。

方法:

在单元格中输入公式:=VLOOKUP(J3,B3:G12,6,0)。

解读:

1、公式中,J3为需要查询的值,B3:G12为数据范围,6为“月薪”所在的相对列数,0为精准匹配。

2、因为查询值J3在数据表中是从B列开始的,所以数据范围只能从B列开始;而需要查询的为“月薪”,所以G列必须包含在相对数据范围中,所以最小的数据范围为B3:G12,当然包含H列也是没有问题的哦!

3、第3个参数“6”是从相对的数据范围中开始计算的,即“月薪”在查询范围B3:G12 中,是第6列。

二、Excel查询引用:Hlookup函数法。

目的:根据员工的“年终考核”结果查询对应的“奖金”。

使用函数:Hlookup。

函数功能:搜索指定的数据区域中首行满足条件的元素,确定待检索单元格在区域中的列序号,再进一步返回选定单元格的值。

语法结构:=Hlookup(查询值,数据范围,返回值所在的行,匹配方式)。

参数解读:匹配方式有2个值,分别为0和1,0为精准查询,1为模糊查询。

方法:

在单元格中输入公式:=HLOOKUP(H3,$M$3:$Q$4,2,0)。

解读:

1、公式中,H3为需要查询的值,M3:Q4为数据范围,2为返回值所在的相对行数,0为精准查询。

2、习惯了使用Vlookup函数的亲,此处一定要明白,Hlookup函数为横向函数,在指定的数据范围中按列去寻找查询值,即相对数据范围中的第一行为查询值。

3、第3个参数“2”是从相对的数据范围中开始计算的,即“奖金”在查询范围M3:Q4中,是第2行。

三、Excel查询引用:Lookup函数法。

目的:根据“员工姓名”查询对应的“月薪”。

使用函数:Lookup函数法。

函数功能:从单行或单列 或 数组中查找指定的值。

语法结构:

向量形式:=Lookup(查询值,查询值所在的列,返回值所在的列)。

数组形式:=Lookup(查询值,数据范围)。

参数解读:

当Lookup函数在使用数组形式查询数据时,数据范围的首列为查询值所在的列,最后一列为返回值所在的列。

方法:

在目标单元格中输入公式:=LOOKUP(1,0/(B3:B12=J3),G3:G12)。

解读:

1、公式=LOOKUP(1,0/(B3:B12=J3),G3:G12)为Lookup函数的变异模式,如果使用常规的向量形式或数组形式,查询值必须按照升序排序后,方可以得到正确的结果。

2、公式=LOOKUP(1,0/(B3:B12=J3),G3:G12)其实质仍然为向量形式,但要明白次函数的一个特点,当找不到查找值时,会以小于查找值的最大值进行匹配。

3、当查询值为“徐庶”,条件判断B3:B12=J3的返回值为{0,0,0,0,0,0,0,0,1,0},所以0/(B3:B12=J3)的返回值为{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;},根据Lookup函数的特点,返回G列中和0在同一行的值。

四、Excel查询引用:Index+Match。

目的:根据“员工姓名”查询对应的“月薪”。

使用函数:Index、Match。

函数功能:

(1)Index:从指定的数据区域中,返回指定行、列交叉处的值或引用。

(2)Match:返回指定值在指定范围中的相对位置。

语法结构:

(1)=Index(数据范围,行,[列]),当省略参数“列”时,默认值为1。

(2)=Match(查询值,查询值所在的列,[匹配模式]),省略匹配模式时,默认值为精准匹配。

方法:

在目标单元格中输入公式:=INDEX(G3:G12,MATCH(J3,B3:B12,0))。

解读:

Index+Match的组合查询引用时比较经典的查询方式,应用率非常的高,其原理就是用Match函数定位出当前值所在的行,将值返回给Index函数的第二个参数,然后定位出需要返回的值。