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])
本例中，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
推荐阅读北京大学出版社Excel数据处理与分析应用大全
内容简介：
本书全面系统地介绍了以 Excel 为核心工具开展数据处理与数据分析的各项技术，深入揭示背后的概念原理，并配以大量典型实用的应用案例，帮助读者全面掌握数据分析工作的必备技能。全书分17 章，内容包括数据分析概述、数据采集、数据输入、数据整理、借助公式快速完成统计计算、借助数据透视表快速完成统计计算、基础统计分析、中心极限定理、假设检验、t 检验和卡方检验、方差分析、回归分析、时间序列分析、规划求解、Excel 数据表格美化、数据可视化和其他常用数据分析工具， 方便读者随时查阅。
本书适合各层次的数据分析从业人员，既可作为初学者的入门指南，又可作为中、高级用户的参考手册。书中大量的实例还适合读者直接在工作中借鉴。
