14207班课时一小结 www.excelhome.net 学员:sunli1080 幽茉434361257
第一节 常用查找引用函数基础及应用实例
主讲:ychexcel
一、Vlookup函数(从左向右查找)
1、功能:从数据表首列查找指定的值,并返回同行指定列处的内容。
2、语法:Vlookup(查找值,查找区域,列序号,[查找方式])
简单介绍一下第四个参数:
逻辑值的第四参数:用户通过这个参数告诉Excel查找时是进行精确匹配(当参数值为False时)还是近似匹配(当参数值为True时)。精确匹配:就是查找等于查找值(第一参数)的第一条记录,如果找不到,返回错误。近似匹配:是当找不到查找值(第一参数)时,则返回小于查找值的最大数值(查找区域,即vlookup的第二参数需要先升序排列)。
第四参数不是0,就是1,或者写了一个逗号,甚至什么也不写。这是因为可以用所有不是0的数来代替True,用0代替False,再一个不写或者只写逗号,
这就涉及到有关函数公式里“缺省”与“省略”这两个概念。缺省就是缺少的,没有的。省略就是略去的,忽略不写的,但是省略的参数必须给它留出位置,用逗号隔开。在Excel的函数里,有些参数是可以缺省的,有些参数是可以省略不写的。Vlookup的第四参数就是一个可以缺省,也可以省略的参数。
3、图片实例1:用物料编号来查找物料名称
数据区:
求:J2:J8的值?
J2=VLOOKUP(I2,$C$2:$G$11,2,FALSE) (FALSE代表是精确查找)
J2=VLOOKUP(I2,C2:G11,2,0) (0可以代表FALSE)
J2=VLOOKUP(I2,C2:G11,2,) (省略FALSE表示模糊查找)
J2=VLOOKUP(I2,C2:G11,2,1) (1也代表精确查找)
COLUMN返回给定引用的列标。(返回各自的序号)
语法:COLUMN(其列标的单元格或单元格区域)
C21=COLUMN(C1) D21=COLMN(D1)
C19=VLOOKUP($B19,$C$2:$G$11,2,)
C19=VLOOKUP($B19,$C$2:$G$11,COLUMN(),)
二、Match函数
1、功能:在指定方式下查找指定值并返回其在目标区域的位置。
2、语法:Match(查找值,查找区域,查找方式)
3、图片实例2:
根据B24给出条件,求:C24:F25的值?
第一种C27=MATCH(C23,$C$1:$G$1,)
C24=VLOOKUP($B24,$C$2:$G$12,$C27,)
第二种C24=VLOOKUP($B24,$C$2:$G$12, MATCH(C23,$C$1:$G$1,),)
D27 =MATCH(D23,$C$1:$G$1,)
图片实例3:根据含糊条件查找。求F37:F40的值?
F37=VLOOKUP(D37,A37:B41,2,)
F40=VLOOKUP(D40,A37:B41,2,)
图片实例4:根据辅助列给出的条件查找。求H57:H60的值?
辅助列(动态捆绑)
A47=B57&”|”&C57
E47=F57&”|”&G57
第一种H57=VLOOKUP(E57,A57:D66,4,)
第二种H57=VLOOKUP(F57&G57,IF({1,0},B57:B66&C57:C66,D57:D66),2)
三、INDEX函数(从右向左查找)
1、语法:INDEX(查找区域,行号,列标)
图片实例5:
D71=INDEX(B69:C78,2,2)
D72=INDEX(B69:B78,2)
D73=INDEX(B70:C70,,1)
F69=INDEX(B69:B78,MATCH(E69,C69:C78,))
F69=VLOOKUP(E69,IF({1,0},C69:C78,B69:B78),2)
F69=VLOOKUP(E69,CHOOSE({1,2},C69:C78,B69:B78),2)
F69=INDEX(B69:B78,MATCH(E69,$C$69:$C$78,))
图片实例6:
H44为’1 H45为1 H46设备为邮政编码
注:如果I44:I46出现错误值,请查找格式设置匹配情况。
I44=VLOOKUP(--H44,B44:F53,5,)
I45=VLOOKUP(H45,B44:F53,5,)
图片实例7:
F82=VLOOKUP(E82,$B$82:$C$85,2)
图片实例8:根据两个条件查找。求G3?
G3=INDEX(B2:D11,MATCH(G1,A2:A11,),MATCH(G2,B1:D1,))
图片实例9:
G15{=SUM(LOOKUP(F14:F16,A14:B23))}
图片实例10:
E26=INDEX(A26:A35,MATCH(D26,B26:B35,))
图片实例11:逆向查询数据
图片实例12:使用Match取得产品生产日期
B2=MATCH(LEFT(RIGHT(A2,5)),$G$1:$G$26)+2001
C2=LOOKUP(LEFT(RIGHT(A2,5)),G:H)
D2=VLOOKUP(LEFT(RIGHT(A2,5)),G:H,2)
G1=LEFT(CHAR(ROW()+64))
图片实例13:
G2=INDEX($B$2:$B$5,MATCH(E2,$A$2:$A$5))
H2=VLOOKUP(E2,A:B,2)
I2=LOOKUP(E2,A:B)
J2=LOOKUP(E2,$A$2:$A$5,$B$2:$B$5)
图片实例14:动态引用
H11{=SUM(OFFSET(G1,MATCH(H10,G1:G8,)-1,1,,3))}
H12{=SUM(VLOOKUP(H10,G2:J8,COLUMN(H:J)-6,))}
图片实例15:
M18=SUM(INDEX(N2:N13,MATCH(N15,M2:M13,)):INDEX(N2:N13,MATCH(N16,M2:M13,)))
N18{=SUM(OFFSET($N$2,MATCH(N15,$M$2:$M$13,0)-1,,MATCH(N16,$M$2:$M$13,0)-MATCH(N15,$M$2:$M$13,0)+1,))}
课时一作业内容
数据区
第一题:根据数据区,通过工号(工号可变),返回查询结果。如果工号错误,返回“不存在”。在b5单元格输入公式,然后向右拖动完成。
效果图
B5=
C5=
第二题:双重查询,根据工号和查询情况(两者皆可变),返回查询结果。在C4单元格输入公式,然后拖动完成。
第三题:将数据区变成效果图所示工资条模样,要求仅在A4单元格输入公式,然后拖动批量完成。