在现代社会中,Excel已经成为人们工作生活中必不可少的工具之一。通过查询引用Excel数据,可以帮助我们更快更准确地分析和处理大量数据。而掌握Excel的7种高手应用技巧,则可以让我们更加高效地进行数据处理和分析。掌握2个以上的高手技巧,不仅可以提升工作效率,还可以让我们在Excel使用中更加得心应手,成为真正的Excel高手。
在Excel中,使用最多的可能就是数据的查询引用,除了简单的筛选查询之外,我们还可以使用Vlookup等函数公式来实现查询引用。一、Excel查询引用:Vlookup函数法。
功能:在指定的数据范围内查询返回符合要求的指定值。
语法结构:=Vlookup(查询值,数据范围,返回值所在的列数,匹配模式)。
其中匹配模式分为“0”和“1”两种,“0”为精准查询,“1”为模糊查询。
目的:根据“销售员”的姓名查询对应的“销量”。
方法:
在目标单元格中输入公式:=VLOOKUP(H3,B3:D9,3,0)。
解读:
公式=VLOOKUP(H3,B3:D9,3,0)中,H3为查询值,B3:D3为查询的数据范围,在此范围中,目的是返回“销量”,而销量在此范围的第3列,所以第三个参数为“3”,最后一个参数“0”为精准查询。
二、Excel查询引用:Lookup函数法。
功能:从单列、单行或指定的数据区域中返回符合条件的值。
Lookup函数有两种使用形式:向量形式和数组形式。
1、向量形式。
语法结构:=Lookup(查询值,查询值所在的列,返回值所在的列)。
目的:根据“销售员”的姓名查询对应的“销量”。
方法:
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(查询值,查询值和返回值所在的数据范围)。
条件:查询值和返回值必须在数据范围的第一列和最后一列,否则无法得到正确的结果。
目的:根据“销售员”的姓名查询对应的“销量”。
方法:
1、选定数据源,以“销售员”为【主要关键字】进行【升序】排序。
2、在目标单元格中输入公式:=LOOKUP(H3,B3:D9)。
解读:
数据范围B3:D9中,B列为“查询值”H3所在的列,D列为返回值“销量”所在的列。所以数据范围以B列开始,以D列结束。
3、Lookup“改进”形式。
从“向量形式”和“数组形式”的应用中已经知道,要得到正确的查询结果,必须先以“查询值”为【主要关键字】进行【升序】排序,如果不排序,能否实现查询呢?答案当然是肯定的……
语法结构:=Lookup(1,0/查询条件,返回值范围)。
目的:根据“销售员”的姓名查询对应的“销量”。
方法:
在目标单元格中输入公式:=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(小于)三种。
目的:根据“销售员”的姓名查询对应的“销量”。
方法:
在目标单元格中输入公式:=OFFSET(D2,MATCH(H3,B3:B9,0),0)。
解读:
公式中,以D2为参照单元格,用Match定位H3值在B3:B9这个范围中的相对位置,返回值作为Offset函数的第2个参数,最后用Offset提取符合条件的值。
四、Excel查询引用:Index+Match组合函数法。
Index函数功能:返回给定的单元格区域中,行列交叉处的值或引用。
语法:=Index(数据范围,行,[列]),当省略“列”时,默认为0。
目的:根据“销售员”的姓名查询对应的“销量”。
方法:
在目标单元格中输入公式:=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样式。
目的:根据“销售员”的姓名查询对应的“销量”。
方法:
在目标单元格中输入公式:=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(行,列)。
目的:根据“销售员”的姓名查询对应的“销量”。
方法:
在目标单元格中输入公式:=INDIRECT(ADDRESS(MATCH(H3,B3:B9,0)+2,4))。
解读:
以“王东”为例,首先用Match函数定位其在B3:B9中的相对位置,返回值1,1+2,4作为Address函数的参数,暨返回$C$4作为Indirect的参数,最后返回值“66”。
七、Excel查询引用:Dget函数法。
功能:从数据库中返回符合条件且唯一存在的值。
语法结构:=Dget(范围数据库,返回值列数,条件数据库)。
目的:根据“销售员”的姓名查询对应的“销量”。
方法:
在目标单元格中输入公式:=DGET(B2:D9,3,H2:H3)。
解读:
Dget函数的第一个和第三个参数已经很明确的说了,是*数据库,简单的理解就是包含“标题”的数据范围。所以第一、第三个参数从B2:D9、H2:H3,而不是从B3:D9或直接的H3。
结束语:
从上述的示例中已经知道,Excel数据查询引用绝不是Lookup或Vlookup的专利,除了这两个函数外,还有很多的函数或公式都可以实现……对于使用技巧,你Get到了吗?如果在学习过程中有疑问或对Excel数据查询引用有独到的见解,欢迎亲在留言区留言讨论哦!
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载
Excel数据查询引用,7种高手应用技巧,掌握2个以上都是高手
立即下载