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,表示使用精确匹配方式。如果为 TRUE、1 或是直接省略该参数时,则使用近似匹配方式。近似匹配方式通常用于数值类的查询,要求查询区域的首列必须按升序排序,当找不到具体的查询值时,会以小于查询值的最接近值进行匹配。

如果有多条满足条件的记录,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 键,依次按数字小键盘的 4、 1、3、8、5。为了便于输入,第一参数也常使用编码较大的汉字“座”。

以下公式可返回 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
(0)

相关推荐