函数14207班课时一小结

       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单元格输入公式，然后拖动批量完成。