2010-09-23 05:22:21| 分类：
EXCEL学习 | 标签：
函数 公式 查找 match vlookup |字号
订阅第一部分 学习心得
本节课程，ychexcel 老师以VLOOKUP函数为主，着重介绍了VLOOKUP函数的参数和查询方式、查询技巧。
VLOOKUP函数
用途：使用 VLOOKUP 函数搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值。
语法：VLOOKUP(查找值,查找区域,列数,[查找方式])
参数：
查找值：必需。要在表格或区域的第一列中搜索的值。查找值 参数可以是值或引用。如果为查找值 参数提供的值小于查找区域 参数第一列中的最小值，则 VLOOKUP 将返回错误值 #N/A。
查找区域：必需。包含数据的单元格区域。可以使用对区域（例如，A2:D8）或区域名称的引用。查找区域 第一列中的值是由 查找值 搜索的值。这些值可以是文本、数字或逻辑值。文本不区分大小写。
列号：必需。查找区域 参数中必须返回的匹配值的列号。列数 参数为 1 时，返回 查找区域 第一列中的值；列号 参数为 2 时，返回 查找区域第二列中的值，依此类推。
如果 列号 参数：
小于 1，则 VLOOKUP 返回错误值 #VALUE!。
大于 查找区域 的列数，则 VLOOKUP 返回错误值 #REF!。
查找方式：可选。一个逻辑值，指定希望 VLOOKUP 查找精确匹配值还是近似匹配值：
如果 查找方式 为 TRUE 或被省略，则返回精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于查找值 的最大值。
要点 如果 查找方式 为 TRUE 或被省略，则必须按升序排列 查找区域 第一列中的值；否则，VLOOKUP 可能无法返回正确的值。
如果 查找方式 为 FALSE，则不需要对查找区域 第一列中的值进行排序。
如果 查找方式 参数为 FALSE，VLOOKUP 将只查找精确匹配值。如果 查找区域 的第一列中有两个或更多值与 查找值 匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值 #N/A。
注释：
在 查找区域 的第一列中查找文本值时，要确保 查找区域 第一列中的数据不包含前空格、尾空格、非打印字符或者未使用不一致的直引号（' 或 "）与弯引号（‘ 或 “）。在搜索数字或日期值时，应确保查找区域 第一列中的数据未存储为文本值。否则，VLOOKUP 可能返回不正确或意外的值。
如果 查找方式 为 FALSE 且 查找值 为文本，则可以在查找值 中使用通配符 － 问号 (?) 和星号 (*)。问号匹配任意单个字符；星号匹配任意字符序列。如果要查找实际的问号或星号，要在问号（？）或星号（*）字符前键入波形符 (~)。
通过反复学习查找函数的帮助文件，本人深沉体会到：
1、学习EXCEL，帮助文件是离自己最近的老师。
2、弄懂函数参数的意义，是学好函数、熟练使用函数的先决条件，否则，在构建函数时可能得不到正确的结果，特别是在函数嵌套时，往往出错。
3、多在EH论坛上查看别人回答提问者帖子，拜读贤哲们的公式构建技巧、理解大师们的解题思路，是学好EXCEL函数的捷径。
4、参与回答别人的提问，是帮助别人解决问题、提高自己EXCEL水平的两利的行为。同时又是加深自己对函数的记忆和理解的最佳方法。
通过本次学习对查找引用函数参数重新加深了认识：
1、reference参数一般地为单元格区域的引用;也可以为INDIRECT函数或OFFSET函数构建的单元格或单元格区域。
２、Array参数可以为 单元格区域、数组常量。
３、Lookup_value为查找值【可以为数值、引用或文本字符串】;table_array为查找区域【可以为区域、常量数组、区域名称或数组名称】;row_index_num为查找的行号【一般为数字】;col_index_num为查找的列号【一般为数字】、range_lookup为查找方式【一般为逻辑值】;row_num、col_num、index_num等这些含num的参数【一般为数值】。
对OFFSET函数的基本理解：
语法 OFFSET(reference, rows, cols, [height], [width])
① Reference
② Rows
③ Cols
④ Height
⑤ Width
②~⑤的数值设置一定不可越出EXCEL的边界，否则，就会出现#REF!
第二部分 课后作业与课前练习解题思路
A、课后作业钥匙思路
第一题：暂时做了六种解法
１、解题公式=IF(COUNTIF(数据区!$A:$A,$A5),VLOOKUP($A5,数据区!$A:$G,COLUMN(),),"不存在")
采用VLOOKUP函数普通用法，用COLUMN()获得动态列号是这一公式的核心，设置绝对引用和混合引用是关键。
２、解题公式=IF(COUNTIF(数据区!$A:$A,$A$5),OFFSET(数据区!$A$1,MATCH($A$5,数据区!$A:$A,)-1,MATCH(B$4,数据区!1:1,)-1),"不存在")
采用OFFSET函数，用MATCH函数获取偏移的动态行、列数。
３、解题公式=IF(COUNTIF(数据区!$A:$A,$A5),INDEX(数据区!$A:$G,MATCH($A$5,数据区!$A:$A,),COLUMN()),"不存在")
采用INDEX函数数组形式，用MATCH函数获得动态行数，用COLUMN()获得动态列数。
４、=IF(COUNTIF(数据区!$A:$A,$A$5),INDIRECT("数据区!R"&MATCH($A$5,数据区!$A:$A,)&"C"&MATCH(B$4,数据区!1:1,),),"不存在")
采用INDIRECT函数的R1C1－样式构建公式，用MATCH函数获取R1C1－样式的动态数字。
５、解题公式=IF(COUNTIF(数据区!$A:$A,$A5),VLOOKUP($A5,数据区!$A:$G,COLUMN(B:G),),"不存在")
采用了VLOOKUP函数的数组解法，而COLUMN(B:G)函数是构成本数组函数的关键。
６、解题公式=IF(COUNTIF(数据区!$A:$A,$A$5),HLOOKUP(B$4,数据区!$B:$G,MATCH($A5,数据区!$A:$A,),),"不存在")
采用HLOOKUP函数的普通解法，关键在于选对MATCH函数的Lookup_value参数的值。
以上公式的排错方法避免了一直延用的IF(ISNA(公式),”不存在”,公式)的用法，而采用了IF(COUNTIF(数据区!$A:$A,$A$5),公式,"不存在")的方式，从而缩短了公式长度，减少了计算机内存的使用，提高了EXCEL的运算速度。
第二题、暂时做了五种解法
１、解题公式=INDIRECT(ADDRESS(MATCH(A4,数据区!A:A,),MATCH(B4,数据区!$1:$1,),,,"数据区"))
用INDIRECT+ADDRESS+MATCH构建公式，而两个MATCH函数是核心公式。
２、解题公式=OFFSET(数据区!$A$1,MATCH(A4,数据区!A:A,)-1,MATCH(B4,数据区!$1:$1,)-1)
采用OFFSET函数，用两个MATCH函数获得动态的偏移量是关键。
３、钥匙公式=INDEX(数据区!$B$2:$G$11,MATCH(A4,数据区!A:A,)-1,MATCH(B4,数据区!$1:$1,)-1)
采用INDEX函数的数组形式，用两个MATCH函数获得动态的行数和列数。
４、解题公式=HLOOKUP(B4,数据区!$B:$G,MATCH(A4,数据区!A:A,),)
采用HLOOKUP函数普通用法，用MATCH函数获取动态行号是核心。
５、解题公式=VLOOKUP(A4,数据区!A:G,MATCH(B4,数据区!$1:$1,),)
采用VLOKOKUP函数普通用法，用MATCH函数获取动态列号是本公式的核心公式。
从上面的公式来看，MATCH函数，在查找引用函数中有着非常重要的作用。
第三题、=CHOOSE(MOD(ROW()-3,3)+1," ",数据区!A$1,INDEX(数据区!A:A,ROUNDUP((ROW()-3)/3,)+1))
采用CHOOSE函数特性构建公式，CHOOSE函数第一参数有“如果 index_num为小数，则在在使用前将被截尾取整”的特性，因而，在此用MOD(ROW()-3,3)+1计算出CHOOSE函数取数的位置，由于要求的结果表格位于第四行，故而MOD函数中用了ROW()-3与３[３是受工资条设置要求影响的]的余数，然后加１，加１的作用是为了排错，因为如MOD(６,3)，其结果为0，将导致公式出错[ 帮助文件中：如果 index_num 小于 1 或大于列表中最后一个值的序号，函数 CHOOSE 返回错误值 #VALUE!]。
解决了第一参数的问题，剩余的就是设置CHOOSE函数的第二、三、四参数了。
第二参数设置了空行。
第三参数也非常简单，就是为了求出工资条的表头。
第四参数用INDEX函数构建，其核心公式为ROUNDUP(ROW()-3)/3,)，ROUNDUP函数是按要求向上舍入的函数，从而取得INDEX函数的取值行号。
B、课前练习解题思路
练习一、
分析：
首先，等级转换的标准是考试得分与本科目最高分的比值在权重的哪个范围，
然后求出对应的等级。
有了分析的结论，接下来就是要如何求出考试得分和科目最高分
单元格公式解读：
1、
考试得分
091010138
28
28
75
44
20
27
32
91
76
421
因为结果区域的科目排列顺序与成绩登记顺序不一，所以公式
VLOOKUP(M25,$A:$K,MATCH(N$6,$A$2:$K$2,),)公式中的关键是
取得结果区域科目在成绩登记区域的列数字，这里采用match函数
取得列数值
也可以
28
28
75
44
20
27
32
91
76
421
2、
科目最高分
50
50
120
50
50
70
50
120
120
680
3、
计算比值
0.56
0.56
0.63
0.88
0.4
0.39
0.64
0.76
0.63
0.62
4、
等级转换
C+
C+
B
A
C
C
B
B+
B
B
公式后半部分之所以采用人工编辑数组，是因为Lookup函数向量形式的特性决定的
（参见：LOOKUP向量形式帮助文件中 红色字体部分）
如果等级转换标准区域设置如蓝色区域，则公式可简化为：
C+
C+
B
A
C
C
B
B+
B
B
5、
组合公式
C+
C+
B
A
C
C
B
B+
B
B
多单元格数组公式解读：
1、
考试得分
取得各科位于源数据中的列数值
7
10
4
5
6
9
8
2
3
11
取得各科的分数
28
28
75
44
20
27
32
91
76
421
2、
取得各科目最高分
50
50
120
50
50
70
50
120
120
680
3、
计算各科目考试得分与最高分的比值
0.56
0.56
0.63
0.9
0.4
0.39
0.64
0.76
0.63
0.619118
4、
等级转换
C+
C+
B
A
C
C
B
B+
B
B
5、
公式重组
C+
C+
B
A
C
C
B
B+
B
B
练习二
本练习目的是要考察观察源数据的仔细程度和重新构建查找区域的能力
分析：
仔细观察源数据[基础数据]，与本要求结果对照，不难发现：
1、我们实际要查询的是标件名称的零件编码、零件单价和零件产地。
2、而源数据中，标件名称不在同一列。
3、认真观察后发现，可以用标件类型在 基础数据 工作表中第三行，确定标件类型的位置。
4、有了标件类型的位置，就给我们重新构建查询区域，提供了一种可能。
动手操作：
标件名称
螺栓
=OFFSET(INDIRECT("基础数据!"&CHAR(MATCH(J14,基础数据!2:2,)+64)&3),,100,4)
1
基点确认
方法１、INDIRECT法
基础数据!R3C2
5×8螺栓
基础数据!B3
5×8螺栓
方法２、ADDRESS法
基础数据!$B$3
5×8螺栓
方法３、OFFSET法
5×8螺栓
2
构建查询区域
基础数据!B3:F102
3
建立查询公式
90101001
0.08
杭州标件厂
4、排错 参见F4公式
练习三
统计下列区域中B,D,F列含有A的个数(不连续区域统计)
A
W
A
W
A
结果
B
S
D
S
A
方法1
8
=SUM(COUNTIF(INDIRECT({"B4:B10","D4:D10","F4:F10"}),"A"))
D
A
G
F
A
方法2
8
{=SUM(N(CHOOSE({1,2,3},B4:B10,D4:D10,F4:F10)="A"))}
E
D
H
A
D
方法3
8
=SUM(COUNTIF(OFFSET(B4:B10,,{0,2,4}),"A"))
G
W
A
S
C
A
S
D
F
C
A
F
H
D
F
练习四
首先，我们应该能够确定的是，这是一个INDEX函数的数组形式的使用。
那么，在多单元格数组公式中如何用，如何构建是理解这类公式的难点！
理解这类公式的方法有多种，下面我介绍一个常用的方法：
疱丁解牛法
先应解决的问题是第几行、第几列
A
列的问题
1、
查找关键
1
2
2
1
2
1
2、
找相同、贴标签：
1001
2002
2003
1004
2005
1006
贴标签的作用是将符合相同条件的数据加以标注，方便后续使用。
这里我们将条件扩大1000倍，是先予后取，如果不先给予，直接将条件与列数相加，将出现列数超出array的区域范围的情况！
3、
按图索骥
1001
1001
1004
1004
1006
1006
2002
2002
2003
2003
2005
2005
上面的公式主要的作用是给符合需要转换的数据打上标签，并排出顺序
4、
去伪存真
001
001
1
1
004
004
4
4
006
006
6
6
002
002
2
2
003
003
3
3
005
005
5
5
从上面的结果看我们取的是符合条件列号列表
B
行的问题
1
2
C
对号入座
1
张三
1
赵二
1
孙六
2
李四
2
王五
2
钱一
D
结构重组
结构重组这是我个人的提法，意思就是将上述我们分步得到的公式进行组合，使之形成一个数组公式。
1
张三
1
赵二
1
孙六
2
李四
2
王五
2
钱一
EH培训二〇一〇年九月二十三日
