未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
Excel应用大全 | 如何用函数查询信息?
SIMPLE HEADLINE

如果需要在数据表或指定的单元格范围内查找并返回特定内容,可以使用查找引用类函数完成。常用的 VLOOKUP 函数、LOOKUP 函数、INDIRECT 函数,以及 INDEX 函数、MATCH 函数和 OFFSET 函数等,都属于查找引用类函数。

常规数据查询
常规数据查询

示例 5-39查询购房人放款状态

5-60展示了某房地产销售公司购房贷款台账的部分内容,需要根据O2单元格的姓名,在左侧的数据表中查询对应的放款状态。O2 单元格输入以下公式,返回放款状态为“审批中”。=VLOOKUP(N2,B:L,11,0)

5-6购房贷款台账

VLOOKUP 函数的作用是根据指定的查询值,在查询区域中的首列查找到该内容,并返回与之对应的其他字段的数据。函数语法如下。VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

第一参数是要在单元格区域或数组的第一列中查询的值。在精确匹配模式下,该参数支持使用通配符。

第二参数指定要在哪个区域中进行查询。查询区域的首列必须包含要查询的内容,否则将返回错误值。

第三参数用于指定返回查询区域中第几列的值,注意是查询区域中的第几列,而不是工作表的第几列。

第四参数用于指定查询时的匹配方式,如果为 0 FASLE,表示使用精确匹配方式。如果为 TRUE1 或是直接省略该参数时,则使用近似匹配方式。近似匹配方式通常用于数值类的查询,要求查询区域的首列必须按升序排序,当找不到具体的查询值时,会以小于查询值的最接近值进行匹配。

如果有多条满足条件的记录,VLOOKUP 函数默认返回首个记录的内容。查找时不区分大小写。

本例中,查询值是 N2 单元格中的姓名,查询区域是 B L 列的整列引用。第三参数使用 11,第四参数使用 0,表示在查询区域的首列,即 B 列找到查询的姓名,并返回 B L 列区域中第 11 列与之对应的内容。

任意方向查询数据
ENJOY THE SUMMER

VLOOKUP 函数要求查询区域的首列必须包含要查询的内容。因此在默认情况下,只能实现从左到右的数据查询。而使用 LOOKUP 函数或是使用 MATCH 函数与 INDEX 函数的组合,则可以实现任意方向的数据查询。

示例 5-38根据买受人姓名查询合同号

5-61 展示了某房地产销售公司销售签约台账的部分记录,需要根据 M2 单元格的买受人姓名,在左侧的数据表中查询对应的合同号。

5-61房产销售签约台账

N2 单元格输入以下公式,查询结果为“2019017912”。=LOOKUP(1,0/(M2=C2:C41),B2:B41)

LOOKUP 函数的作用是在一行或一列的范围中查找指定的值,并返回另一行或列中对应位置的值。函数支持忽略空值、逻辑值和错误值来进行数据查询。函数语法包括向量和数组两种形式,分别如下。 

LOOKUP(lookup_value,lookup_vector,[result_vector])

LOOKUP(lookup_value,array)

在向量语法中,第一参数是要查询的内容。第二参数是要查找的范围。第三参数是指定要返回结果的范围,参数必须与第二参数的行(列)数相同,如果第三参数省略,将返回第二参数中对应位置的值。

当需要查找一个不确定的值时,如查找一列或一行数据的最后一个值,LOOKUP 函数的查找范围不需要升序排列。以下公式可返回 A 列最后一个文本。

=LOOKUP(' ',A:A)

“々”通常被看作一个编码较大的字符,输入方法为按住 Alt 键,依次按数字小键盘的 41385。为了便于输入,第一参数也常使用编码较大的汉字“座”。

以下公式可返回 A 列最后一个数值。

=LOOKUP(9E+307,A:A)

9E+307 Excel 里的科学计数法,即 9*10^307,被认为是接近 Excel 允许键入的最大数值。

本例中,就是使用了 LOOKUP 函数的向量语法形式。公式中的“M2=C2:C41”部分,使用 M2 单元格的姓名与 C2:C14 单元格区域的姓名进行逐一对比,得到一组逻辑值构成的内存数组。 

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;;FALSE;FALSE}

再使用 0 除以该内存数组,相除后得到一个由 0 和错误值构成的新内存数组,以此作为LOOKUP 函数的查询区域。其中 0 的位置,就是 C2:C14 单元格区域中等于 M2 单元格中指定姓名的位置。

 {#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;;#DIV/0!;#DIV/0!}

最后使用 1 作为查询值在这个内存数组中进行查找,由于内存数组中不包含 1,因此以小于 1 的最接近值,也就是 0 进行匹配,并返回第三参数 B2:B41 单元格区域中对应位置的内容。

LOOKUP 函数的第二参数可以是多个逻辑判断相乘组成的多条件数组,来完成多条件的数据查询,函数的常用写法如下。=LOOKUP(1,0/(( 条件 1)*( 条件 2)**( 条件 N)), 目标区域或数组 )

示例 5-41 使用 INDEX 函数和 MATCH 函数查询数

图5-62 展示了某家电公司销售记录表的部分内容,需要根据 J 列的业务流水号,在左侧的数据表中查询对应的发票凭证号。

图5-62使用 INDEX 函数和 MATCH 函数查询数据

K2 单元格输入以下公式,查询结果为“6624326242”。=INDEX(A:A,MATCH(J2,F:F,0))

公式中使用了 INDEX 函数和 MATCH 函数的组合。MATCH 函数的作用是在单行或单列的查询范围中查找特定的内容,然后返回该内容在查询范围中的相对位置,计算结果常用于其他函数的参数。函数语法如下。

MATCH(lookup_value,lookup_array,[match_type])

第一参数是要查找的对象,第二参数指定要查询的范围,第三参数用数字的形式指定查询时的匹配方式。当第三参数为 0 时,表示使用精确匹配方式,如果找不到查询内容,公式将返回错误值 #N/A

INDEX 函数的作用是在一个区域引用或数组范围中,根据指定的行号或(和)列号来返回值或引用。INDEX 函数的常用语法形式如下。

 =INDEX(array,row_num,[column_num])

第一参数可以是单元格区域或是一个数组。第二参数和第三参数分别用于指定要返回第几行或(和)第几列的位置。

公式中的 MATCH(J2,F:F,0) 部分,使用 MATCH 函数查询 J2 单元格的业务流水号在 F列中所处的位置,结果为 23

INDEX 函数以 MATCH 函数的计算结果为参数,返回 A 列中的第 23 个元素。

提示:MATCH 函数在使用精确匹配方式时查询内容中可以使用通配符“*” ?”。如果查询范围中有多个符合条件的结果MATCH 函数仅返回查询对象第 1 次出现的位置


近似查询数据
 LOOKUP 函数的数组语法中,LOOKUP 函数在数组的第一行或第一列中查找指定的 值,并返回数组最后一行或最后一列中同一位置的值,常用于数值型内容的查找。
示例 5-42 根据应知应会成绩计算对应等
5-63 展示了某公司员工应知应会考核成绩表的部分内容,需要根据 E 列的员工应知 应会成绩,在右侧的对照表中查询对应的等级。F2 单元格输入以下公式,将公式向下复制到 F10 单元格。=LOOKUP(E2,H$3:I$6)

5-63员工应知应会成绩表

LOOKUP 函数在查找范围中查 找一个明确的值时,查找范围必须升 序排列。如果找不到查询值,则该函 数会与查询区域中小于查询值的最接 近值进行匹配。 

本例中,以 E2 单元格的成绩作 为查询值在 H$3:I$6 单元格区域中进 行查询,并以等于或小于 E2 的最接 近值进行匹配,最终返回第二参数最右侧列对应位置的内容。 

提示:如果查询区域中有多个符合条件的记录LOOKUP 函数默认返回最后一 个记录


多工作表数据汇总
在多工作表的汇总、查询等工作中,经常会用到 INDIRECT 函数。 
示例 5-43 多工作表汇总客户销售额
5-64 展示了某公司销售明细表的部分内容,不同客户的销售记录分别保存在以客户 名称命名的工作表中,各工作表的结构完全相同,其中的 J 列是每笔业务的实际销售额。

5-64销售明细表 

在“汇总表”工作表中,需要汇总各客户的销售总额,如图 5-65 所示。 

“汇总表”工作表 C2 单元格输入以下公式,将公式向下复制到 C11 单元格。=SUM(INDIRECT(B2&'!J:J'))

INDIRECT函数能够将具有引用样式的文本字符串生成具体的单元格引用,函数语法如下。INDIRECT(ref_text,[a1]) 

第一参数是一个具有单元格地址样式的文 本字符串,第二参数是一个逻辑值,用于指定 使用 A1 引用样式还是 R1C1 引用样式。如果 该参数为 TRUE 或省略,第一参数中的文本被 解释为 A1 样式的引用,A1 样式是 Excel 默认 的引用样式。 

本例中,B2 单元格的客户名就是工作 表名称。“B2&'!J:J'”部分,使用连接符将 B2 单元格的工作表名称与字符串“!J:J”连 接,得到具有引用样式的文本字符串“中信化 工 !J:J”。此时的字符串仅具有引用样式而不是真正的引用,还不能用于后续的其他计算。 

接下来使用 INDIRECT 函数,将字符串“中信化工 !J:J”变成“中信化工”工作表 J 列 的整列引用,最后再使用 SUM 函数对这个引用范围进行求和,得到客户“中信化工”的销 售总额。 

公式中的“B2”使用了相对引用,公式向下复制时依次变成“B3”“B3”……分别与 字符串“!J:J”连接后,再用 INDIRECT 函数生成不同工作表 J 列的整列引用,作为 SUM 函数的求和范围,最终实现了快速汇总多工作表数据的目的。 

使用 INDIRECT 函数生成其他工作表的引用时,如果被引用的工作表名称中包含有空格 等特殊符号,公式中的工作表名称前后要加上半角单引号,否则返回错误值 #REF!。例如要 得到“一季度 销售”工作表 B2 单元格的引用,公式应为 =INDIRECT('' 一季度 销售 '!B2')。 

提示:如果严格按照数据管理规范在输入基础数据的时候应该将所有同类型 数据存储在同一张工作表中规范合理的数据源更便于数据的查询与汇总。

END
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP阅读全文并永久保存 更多类似文章
猜你喜欢
类似文章
比vlookup更强大的函数lookup函数(下:数组用法)
这样用LOOKUP,比一般的查找好用百倍!
Vlookup函数实例(全)
轻松成为办公高手
VLOOKUP函数应该怎么用,看完你就明白了~
按指定的次数重复
更多类似文章 >>
生活服务
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!