常用电子表格查找和引用函数.docx
《常用电子表格查找和引用函数.docx》由会员分享，可在线阅读，更多相关《常用电子表格查找和引用函数.docx（15页珍藏版）》请在冰豆网上搜索。
常用电子表格查找和引用函数
面准备讲查找和引用函数，看看大家最希望第一个讲哪一个函数，个人认为可以先讲VLOOKUP和LOOKUP。
ADDRESS 以文本形式返回对工作表中某个单元格的引用
AREAS 返回引用中的区域个数
CHOOSE 从值的列表中选择一个值67楼
COLUMN 返回引用的列标
COLUMNS 返回引用中的列数
HLOOKUP 在数组的首行查找并返回指定单元格的值
HYPERLINK 创建快捷方式或跳转，以打开存储在网络服务器、Intranet 或 Internet 上的文档
INDEX 使用索引从引用或数组中选择值 index.rar
此文件为xuzhi4433 整理
INDIRECT 返回由文本值表示的引用
LOOKUP 在向量或数组中查找值
MATCH 在引用或数组中查找值
OFFSET 从给定引用中返回引用偏移量点击进入
ROW 返回引用的行号
ROWS 返回引用中的行数
RTD 从支持 COM 自动化的程序中返回实时数据
TRANSPOSE 返回数组的转置
VLOOKUP 在数组第一列中查找，然后在行之间移动以返回单元格的值
本文于2006-09-12 13:
40:
11.015被 飞雨飘 修改过。
这是本帖的第1次修改。
本文于2006-09-17 09:
42:
54.089被 飞雨飘 修改过。
这是本帖的第2次修改。
[回复][引用][本帖链接][只看该作者]
窗体顶端
∙昵称：
飞雨飘
∙用户名：
dengyf
∙头衔：
太平洋舰队秘书长
∙积分：
30809
∙贡献：
7012
∙发帖：
3973
∙精华：
26
∙注册：
2005-07-05
∙查看资料
沙发
飞雨飘
发表于2006-09-0812:
02:
39最后由飞雨飘于2006-09-2912:
09:
27修改
先讲VLOOKUP之基础篇
精确查找
语法
VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）
Lookup_value 为需要在表格数组 （数组：
用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。
数组区域共用一个公式；数组常量是用作参数的一组常量。
）第一列中查找的数值。
Lookup_value 可以为数值或引用。
若 lookup_value 小于 table_array 第一列中的最小值，VLOOKUP 返回错误值 #N/A。
Table_array 为两列或多列数据。
执行对一个区域或区域名称的引用。
table_array 第一列中的值是由 lookup_value 搜索的值。
这些值可以是文本、数字或逻辑值。
不区分大小写。
Col_index_num 为 table_array 中待返回的匹配值的列序号。
Col_index_num 为 1 时，返回 table_array 第一列中的数值；col_index_num 为 2，返回 table_array 第二列中的数值，以此类推。
如果 col_index_num ：
小于 1，VLOOKUP 返回错误值 #VALUE!
。
大于 table_array 的列数，VLOOKUP 返回错误值 #REF!
。
Range_lookup 为逻辑值，指定希望 VLOOKUP 查找精确的匹配值还是近似匹配值：
如果为 TRUE 或省略，则返回精确匹配值或近似匹配值。
也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值。
table_array 第一列中的值必须以升序排序；否则 VLOOKUP 可能无法返回正确的值。
可以选择“数据”菜单上的“排序”命令，再选择“递增”，将这些值按升序排序。
有关详细信息，请参阅默认排序次序。
如果为 FALSE，VLOOKUP 将只寻找精确匹配值。
在此情况下，table_array 第一列的值不需要排序。
如果 table_array 第一列中有两个或多个值与 lookup_value 匹配，则使用第一个找到的值。
如果找不到精确匹配值，则返回错误值 #N/A。
说明
在 table_array 第一列中搜索文本值时，请确保 table_array 第一列中的数据没有前置空格、后置空格、直引号（' 或 "）与弯引号（‘或“）不一致或非打印字符。
在上述情况下，VLOOKUP 可能返回不正确或非预期的值。
有关用于清除文本数据的函数的详细信息，请参阅文本和数据函数。
在搜索数字或日期值时，请确保 table_array 第一列中的数据未保存为文本值。
在该情况下，VLOOKUP 可能返回不正确或非预期的值。
有关详细信息，请参阅将保存为文本的数字转换为数字值。
以上有一点注意，即VLOOKUP如果非精确查找，必须排序，而精确查找则不必。
精确查找之本表查找
姓名工号性别籍贯出生年月
甲A0001男北京1980-8-5
乙A0002女天津1985-9-6
丙A0003男河北1985-3-7
丁A0004女河南1989-12-8
输入姓名查找对工人的籍贯和出生日
姓名籍贯出生年月籍贯公式 =VLOOKUP（A11,$A$1:
$E$5,4,FALSE）
甲北京29438出生年月公式 =VLOOKUP（A11,$A$1:
$E$5,5,FALSE）
1980-8-5格式改为日期 =VLOOKUP（A11,$A$1:
$E$5,5,FALSE）
1980-8-5用公式直接改格式 =TEXT（VLOOKUP（A11,$A$1:
$E$5,5,FALSE）,"YYYY-M-D"）
以上公式中的FALSE也可用0（零）替代
=VLOOKUP（A11,$A$1:
$E$5,4,0）
效果是一样了。
精确查找之跨表查找
姓名籍贯出生年月籍贯公式 =VLOOKUP（A11,Sheet2!
$A$1:
$E$5,4,FALSE）
甲北京29438出生年月公式 =VLOOKUP（A11,Sheet2!
$A$1:
$E$5,5,FALSE）
1980-8-5格式改为日期 =VLOOKUP（A11,Sheet2!
$A$1:
$E$5,5,FALSE）
1980-8-5用公式直接改格式 =TEXT（VLOOKUP（A11,Sheet2!
$A$1:
$E$5,5,FALSE）,"YYYY-M-D"）
跨表查询,查询SHEET2内的内容
动画操作示范
LOOKUP之基础篇
精确查找
函数 LOOKUP 有两种语法形式：
向量和数组。
向量为只包含一行或一列的区域。
函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。
如果需要指定包含待查找数值的区域，则可以使用函数 LOOKUP 的这种形式。
函数 LOOKUP 的另一种形式为自动在第一列或第一行中查找数值。
语法 1
向量形式
LOOKUP（lookup_value,lookup_vector,result_vector）
Lookup_value 为函数 LOOKUP 在第一个向量中所要查找的数值。
Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。
Lookup_vector 为只包含一行或一列的区域。
Lookup_vector 的数值可以为文本、数字或逻辑值。
要点 Lookup_vector 的数值必须按升序排序：
...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则，函数 LOOKUP 不能返回正确的结果。
文本不区分大小写。
Result_vector 只包含一行或一列的区域，其大小必须与 lookup_vector 相同。
说明
如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。
如果 lookup_value 小于 lookup_vector 中的最小值，函数 LOOKUP 返回错误值 #N/A。
语法 2
数组形式
LOOKUP（lookup_value,array）
Lookup_value 为函数 LOOKUP 在数组中所要查找的数值。
Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。
如果函数 LOOKUP 找不到 lookup_value，则使用数组中小于或等于 lookup_value 的最大数值。
如果 lookup_value 小于第一行或第一列（取决于数组的维数）的最小值，函数 LOOKUP 返回错误值 #N/A。
Array 为包含文本、数字或逻辑值的单元格区域，它的值用于与 lookup_value 进行比较。
函数 LOOKUP 的数组形式与函数 HLOOKUP 和函数 VLOOKUP 非常相似。
不同之处在于函数 HLOOKUP 在第一行查找 lookup_value，函数 VLOOKUP 在第一列查找，而函数 LOOKUP 则按照数组的维数查找。
如果数组所包含的区域宽度大，高度小（即列数多于行数），函数 LOOKUP 在第一行查找 lookup_value。
如果数组为正方形，或者所包含的区域高度大，宽度小（即行数多于列数），函数 LOOKUP 在第一列查找 lookup_value。
函数 HLOOKUP 和函数 VLOOKUP 允许按行或按列索引，而函数 LOOKUP 总是选择行或列的最后一个数值。
要点 数组中的数值必须按升序排序：
...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则，函数 LOOKUP 不能返回正确的结果。
文本不区分大小写。
精确查找之本表查找
姓名工号性别籍贯出生年月
甲A0001男北京1980-8-5
乙A0002女天津1985-9-6
丙A0003男河北1985-3-7
丁A0004女河南1989-12-8
输入姓名查找对工人的籍贯和出生日
姓名籍贯出生年月籍贯公式 =LOOKUP（1,0/（$A$1:
$A$5=A11）,$D$1:
$D$5）
甲北京29438出生年月公式 =LOOKUP（1,0/（$A$1:
$A$5=A11）,$E$1:
$E$5）
1980-8-5格式改为日期 =LOOKUP（1,0/（$A$1:
$A$5=A11）,$E$1:
$E$5）
1980-8-5用公式直接改格式 =TEXT（LOOKUP（1,0/（$A$1:
$A$5=A11）,$E$1:
$E$5）,"YYYY-M-D"）
根据姓名在$D$1:
$D$5中查找籍贯，$D$1:
$D$5区域最好使用绝对引用（可在公式中选定区域按F4转换），便于复制。
查找的姓名不必位于$a$1:
$e$5的第一列；精确查找，采用0/（XXX=YYY）条件等式
跨表查询,查询SHEET2内的内容
姓名籍贯出生年月籍贯公式 =LOOKUP（1,0/（Sheet2!
$A$1:
$A$5=A2）,Sheet2!
$D$1:
$D$5）
甲北京29438出生年月公式 =LOOKUP（1,0/（Sheet2!
$A$1:
$A$5=A2）,Sheet2!
$E$1:
$E$5）
1980-8-5格式改为日期 =LOOKUP（1,0/（Sheet2!
$A$1:
$A$5=A2）,Sheet2!
$E$1:
$E$5）
1980-8-5用公式直接改格式 =TEXT（LOOKUP（1,0/（Sheet2!
$A$1:
$A$5=A2）,Sheet2!
$E$1:
$E$5）,"YYYY-M-D"）
VLOOKUP和LOOKUP模糊查找运用
计算个税
超额税率速算扣除数
050
5001025
200015125
500020375
20000251375
40000303375
60000356375
800004010375
10000015375
工资额超额应纳税额
55003900460 =B13*LOOKUP（B13,A2:
A10,B2:
B9）/100-LOOKUP（B13,A2:
A10,C2:
C9）
460 =B13*（VLOOKUP（B13,A2:
B10,2）/100）-VLOOKUP（B13,A2:
C10,3）
小字典——————模糊查找之拼音对应表
吖A
八B
嚓C
咑D
鵽E
发F
猤G
铪H
夻J
咔K
垃L
嘸M
旀N
噢O
妑P
七Q
囕R
仨S
他T
屲W
夕X
丫Y
帀Z
出题：
一、关于用VLOOKUP和LOOKUP查找空单格出现0值的问题解决方法
二、对下表进行横向拖动填充柄进行工号、性别、籍贯、出生年月的查找。
如输入甲一，在工号内填入公式向右拖至出生年月，即可查出对应数值，需用LOOKUP和VLOOKUP做出。
三、对下表进行模糊查找，如输入丙，即对查出姓名、工号、性别……
姓名工号性别籍贯出生年月
甲一A0001男北京1980-8-5
乙二A0002女天津1985-9-6
丙三A0003男河北1985-3-7
丁四A0004女河南1989-12-8
————————————————————————————————————————————————————————————————————————————————————————————————
以下将开讲两个函数的初级综合运用。
因为是综合运用，讲解比较麻烦，还是先出题吧。
等有了答案，如果需要再对答案进行必要的讲解。
性别姓名工号籍贯出生年月
男甲一A0001北京1980-8-5
女乙二A0002天津1985-9-6
男丙三A0003河北1985-3-7
女丁四A0004河南1989-12-8
女丙三A0005江苏1984-6-3
一、要求性别是女，姓名是丙三的工号是什么。
二、要求姓名是丙三的工号是什么（两个工号）
三、工号是A0003的人姓名是什么？
以上属综合运用，可合理添加辅助列。
窗体底端
升级会员 