在Excel中,使用最多的可能就是数据的查询引用,除了简单的筛选查询之外,我们还可以使用VLOOKUP等函数公式来实现查询引用。


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

功能:在指定的数据范围内查询返回符合要求的指定值。

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

其中匹配模式分为“0”和“1”两种,“0”为精准查询,“1”为模糊查询。

目的:根据“销售员”的姓名查询对应的“销量”。

excel数据的引用方法是(Excel数据查询引用7种应用技巧)(1)

方法:

在目标单元格中输入公式:=VLOOKUP(H3,B3:D9,3,0)。

解读:

公式=VLOOKUP(H3,B3:D9,3,0)中,H3为查询值,B3:D3为查询的数据范围,在此范围中,目的是返回“销量”,而销量在此范围的第3列,所以第三个参数为“3”,最后一个参数“0”为精准查询。


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

功能:从单列、单行或指定的数据区域中返回符合条件的值。

Lookup函数有两种使用形式:向量形式和数组形式。

1、向量形式。

语法结构:=Lookup(查询值,查询值所在的列,返回值所在的列)。

目的:根据“销售员”的姓名查询对应的“销量”。

excel数据的引用方法是(Excel数据查询引用7种应用技巧)(2)

方法:

1、选定数据源,以“销售员”为【主要关键字】进行【升序】排序。

2、在目标单元格中输入公式:=LOOKUP(H3,B3:B9,D3:D9)。

解读:

1、从动图的演示结果可以看出,没有对“销售员”进行排序之前,查询的结果并不准确。原因在于Lookup的“缺点”所致。所以在用Lookup函数查询引用数据时,必须先对查询值所在列的值进行升序排序。

2、公式=LOOKUP(H3,B3:B9,D3:D9)中,H3为查询值,B3:B9为查询值所在的列,D3:D9为返回值所在的列。


2、数组形式。

语法结构:=Lookup(查询值,查询值和返回值所在的数据范围)。

条件:查询值和返回值必须在数据范围的第一列和最后一列,否则无法得到正确的结果。

目的:根据“销售员”的姓名查询对应的“销量”。

excel数据的引用方法是(Excel数据查询引用7种应用技巧)(3)

方法:

1、选定数据源,以“销售员”为【主要关键字】进行【升序】排序。

2、在目标单元格中输入公式:=LOOKUP(H3,B3:D9)。

解读:

数据范围B3:D9中,B列为“查询值”H3所在的列,D列为返回值“销量”所在的列。所以数据范围以B列开始,以D列结束。


3、Lookup“改进”形式。

从“向量形式”和“数组形式”的应用中已经知道,要得到正确的查询结果,必须先以“查询值”为【主要关键字】进行【升序】排序,如果不排序,能否实现查询呢?答案当然是肯定的……

语法结构:=Lookup(1,0/查询条件,返回值范围)。

目的:根据“销售员”的姓名查询对应的“销量”。

excel数据的引用方法是(Excel数据查询引用7种应用技巧)(4)

方法:

在目标单元格中输入公式:=LOOKUP(1,0/(B3:B9=H3),D3:D9)。

解读:

1、公式=LOOKUP(1,0/(B3:B9=H3),D3:D9)中,查询值H3如果和B3:B9范围内的值相等,则返回。

2、其公式的本质为“数组形式”,查询值为1,0/(B3:B9=H3)形成了一个以0和False为值的新数据范围,返回值范围为D3:D9。


三、Excel查询引用:Offset Match组合函数法。

Offset函数功能:以指定的参照为引用,通过给定的偏移量返回新的引用。

语法结构:=Offset(参照单元格,行,列,[高],[宽度])。

Match函数功能:返回定位值在指定范围中的相对位置。

语法结构:=Match(定位值,定位范围,匹配模式)。其中匹配模式有:-1(大于)、0(精准)、1(小于)三种。

目的:根据“销售员”的姓名查询对应的“销量”。

excel数据的引用方法是(Excel数据查询引用7种应用技巧)(5)

方法:

在目标单元格中输入公式:=OFFSET(D2,MATCH(H3,B3:B9,0),0)。

解读:

公式中,以D2为参照单元格,用Match定位H3值在B3:B9这个范围中的相对位置,返回值作为Offset函数的第2个参数,最后用Offset提取符合条件的值。


四、Excel查询引用:Index Match组合函数法。

Index函数功能:返回给定的单元格区域中,行列交叉处的值或引用。

语法:=Index(数据范围,行,[列]),当省略“列”时,默认为0。

目的:根据“销售员”的姓名查询对应的“销量”。

excel数据的引用方法是(Excel数据查询引用7种应用技巧)(6)

方法:

在目标单元格中输入公式:=INDEX(D3:D9,MATCH(H3,B3:B9,0))。

解读:

公式=INDEX(D3:D9,MATCH(H3,B3:B9,0))中,首先用Match函数定位H3在B3:B9范围中的相对位置,作为Index函数的第2个参数,然后从D3:D9范围中返回相应位置的值。


五、Exce查询引用:Indirect Match组合函数法。

Indirect函数功能:返回文本字符串所指定的引用。

语法结构:=Indirect(单元格引用,[引用样式])。引用样式分为:A1和R1C1样式。默认为A1样式。

目的:根据“销售员”的姓名查询对应的“销量”。

excel数据的引用方法是(Excel数据查询引用7种应用技巧)(7)

方法:

在目标单元格中输入公式:=INDIRECT("d"&MATCH(H3,B3:B9,0) 2)。

解读:

返回的销量在D3:D9单元格区域中,公式=INDIRECT("d"&MATCH(H3,B3:B9,0) 2)中,首先用Match函数定位H3在B3:B9范围中的相对位置,以“王东”为例,则返回1,然后继续 2,暨用Indirect函数返回D3单元格的值。


六、Excel查询引用:Indirect Address Match组合函数法。

Address函数功能:返回指定行、列交叉单元格绝对地址。

语法结构:=Address(行,列)。

目的:根据“销售员”的姓名查询对应的“销量”。

excel数据的引用方法是(Excel数据查询引用7种应用技巧)(8)

方法:

在目标单元格中输入公式:=INDIRECT(ADDRESS(MATCH(H3,B3:B9,0) 2,4))。

解读:

以“王东”为例,首先用Match函数定位其在B3:B9中的相对位置,返回值1,1 2,4作为Address函数的参数,暨返回$C$4作为Indirect的参数,最后返回值“66”。


七、Excel查询引用:Dget函数法。

功能:从数据库中返回符合条件且唯一存在的值。

语法结构:=Dget(范围数据库,返回值列数,条件数据库)。

目的:根据“销售员”的姓名查询对应的“销量”。

excel数据的引用方法是(Excel数据查询引用7种应用技巧)(9)

方法:

在目标单元格中输入公式:=DGET(B2:D9,3,H2:H3)。

解读:

Dget函数的第一个和第三个参数已经很明确的说了,是*数据库,简单的理解就是包含“标题”的数据范围。所以第一、第三个参数从B2:D9、H2:H3,而不是从B3:D9或直接的H3。


结束语:

从上述的示例中已经知道,Excel数据查询引用绝不是Lookup或Vlookup的专利,除了这两个函数外,还有很多的函数或公式都可以实现……对于使用技巧,你Get到了吗?如果在学习过程中有疑问或对Excel数据查询引用有独到的见解,欢迎亲在留言区留言讨论哦!

,