课堂二（查找和引用函数）12月18日更新

课堂二（查找和引用函数）12月18日更新
为了区分开和飞雨课堂第一课时间系函数，现重新开贴。
下面准备讲查找和引用函数，看看大家最希望第一个讲哪一个函数，个人认为可以先讲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   在数组第一列中查找，然后在行之间移动以返回单元格的值

先讲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模糊查找运用
计算个税

超额 税率 速算扣除数
0 5 0
500 10 25
2000 15 125
5000 20 375
20000 25 1375
40000 30 3375
60000 35 6375
80000 40 10375
100000 15375

工资额 超额 应纳税额
5500 3900 460  =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的人姓名是什么？
以上属综合运用，可合理添加辅助列。