常用电子表格查找和引用函数.docx

1、常用电子表格查找和引用函数面准备讲查找和引用函数，看看大家最希望第一个讲哪一个函数，个人认为可以先讲VLOOKUP和LOOKUP。ADDRESS以文本形式返回对工作表中某个单元格的引用AREAS返回引用中的区域个数CHOOSE从值的列表中选择一个值67楼COLUMN返回引用的列标COLUMNS返回引用中的列数HLOOKUP在数组的首行查找并返回指定单元格的值HYPERLINK创建快捷方式或跳转，以打开存储在网络服务器、Intranet或Internet上的文档INDEX使用索引从引用或数组中选择值index.rar此文件为xuzhi4433整理INDIRECT返回由文本值表示的引用LOOKUP

2、在向量或数组中查找值MATCH在引用或数组中查找值OFFSET从给定引用中返回引用偏移量点击进入ROW返回引用的行号ROWS返回引用中的行数RTD从支持COM自动化的程序中返回实时数据TRANSPOSE返回数组的转置VLOOKUP在数组第一列中查找，然后在行之间移动以返回单元格的值本文于2006-09-1213:40:11.015被飞雨飘修改过。这是本帖的第1次修改。本文于2006-09-1709:42:54.089被飞雨飘修改过。这是本帖的第2次修改。 回复 引用 本帖链接 只看该作者 窗体顶端 昵称：飞雨飘 用户名：dengyf 头衔：太平洋舰队秘书长 积分：30809 贡献：7012 发

3、帖：3973 精华：26 注册：2005-07-05 查看资料 沙发 飞雨飘 发表于 2006-09-08 12:02:39 最后由 飞雨飘 于 2006-09-29 12:09:27 修改 先讲VLOOKUP之基础篇精确查找语法VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value为需要在表格数组（数组：用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）第一列中查找的数值。Lookup_value可以为数值或引用。若lookup

4、_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!。大于

5、table_array的列数，VLOOKUP返回错误值#REF!。Range_lookup为逻辑值，指定希望VLOOKUP查找精确的匹配值还是近似匹配值：如果为TRUE或省略，则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。table_array第一列中的值必须以升序排序；否则VLOOKUP可能无法返回正确的值。可以选择“数据”菜单上的“排序”命令，再选择“递增”，将这些值按升序排序。有关详细信息，请参阅默认排序次序。如果为FALSE，VLOOKUP将只寻找精确匹配值。在此情况下，table_array第一列的值不需要排序。如果tab

6、le_array第一列中有两个或多个值与lookup_value匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值#N/A。说明在table_array第一列中搜索文本值时，请确保table_array第一列中的数据没有前置空格、后置空格、直引号（或）与弯引号（或“）不一致或非打印字符。在上述情况下，VLOOKUP可能返回不正确或非预期的值。有关用于清除文本数据的函数的详细信息，请参阅文本和数据函数。在搜索数字或日期值时，请确保table_array第一列中的数据未保存为文本值。在该情况下，VLOOKUP可能返回不正确或非预期的值。有关详细信息，请参阅将保存为文本的数字转换为数字值

7、。以上有一点注意，即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

8、:$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,

9、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

10、_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相同。说明如果

11、函数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小于第一行或第一列（取决

12、于数组的维数）的最小值，函数LOOKUP返回错误值#N/A。Array为包含文本、数字或逻辑值的单元格区域，它的值用于与lookup_value进行比较。函数LOOKUP的数组形式与函数HLOOKUP和函数VLOOKUP非常相似。不同之处在于函数HLOOKUP在第一行查找lookup_value，函数VLOOKUP在第一列查找，而函数LOOKUP则按照数组的维数查找。如果数组所包含的区域宽度大，高度小（即列数多于行数），函数LOOKUP在第一行查找lookup_value。如果数组为正方形，或者所包含的区域高度大，宽度小（即行数多于列数），函数LOOKUP在第一列查找lookup_value。

13、函数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

14、=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）

15、条件等式 跨表查询,查询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)

16、,YYYY-M-D)VLOOKUP和LOOKUP模糊查找运用计算个税超额 税率 速算扣除数0 5 0500 10 252000 15 1255000 20 37520000 25 137540000 30 337560000 35 637580000 40 10375100000 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)小字典模糊查找之拼音对应

17、表吖 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的人姓名是什么？ 以上属综合运用，可合理添加辅助列。 窗体底端