查找和引用函数应用技巧

Excel 实战：学生成绩查询

某大学语言测试成绩表如图15-35所示。现在需要实现只输入学生姓名，就能够查询某一学生的成绩或其他信息。

图15-35　学生成绩表

下面介绍如何使用查找与引用函数中的INDEX函数和MATCH函数来实现这种查询功能。

步骤1：选中H3单元格，输入以下公式，如图15-36所示。

=INDEX(E:E,MATCH($G$3,$A:$A,0))

图15-36　在单元格H3输入公式

说明：该公式使用INDEX函数返回成绩表中$G$3所在行对应E列的值，使用MATCH函数查找成绩表中A列与单元格$G$3相同的值。

步骤2：按Enter键确认公式输入。

步骤3：在G3单元格中输入一个学生的姓名，然后按Enter键，可以看到在H3单元格中显示出其成绩，如图15-37所示。

图15-37　输入姓名查询成绩

步骤4：在H5单元格中输入以下公式，如图15-38所示。

=INDEX(E:E,MATCH($G$5,$B:$B,0))

图15-38　在单元格H5中输入公式

说明：该公式使用INDEX函数返回成绩表中$G$5所在行对应E列的值，使用MATCH函数查找成绩表中A列与单元格$G$5相同的值。

步骤5：按Enter键确认公式输入。

步骤6：在G5单元格中输入一个学生的准考证号码，然后按Enter键，可以看到在H5单元格中显示出其成绩，如图15-39所示。

图15-39　使用准考证号码查询学生成绩

Excel 应用VLOOKUP函数实现竖直查找

VLOOKUP函数用于在表格数组的首列查找指定的值，并由此返回表格数组当前行中其他列的值，VLOOKUP中的V表示垂直方向。当比较值位于需要查找的数据左边的一列时，可以使用VLOOKUP而不是HLOOKUP。其语法如下。

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

其中参数lookup_value为需要在表格数组第一列中查找的数值。lookup_value可以为数值或引用。table_array为两列或多列数据。col_index_num为table_array中待返回的匹配值的列序号。range_lookup为逻辑值，指定希望VLOOKUP查找精确的匹配值还是近似匹配值。

【典型案例】实现竖直查找。本例的原始数据如图15-33所示。

步骤1：在B11单元格中输入公式“=VLOOKUP（A10，$A$2：$C$6，2，FALSE）”，用于在“A2：C6”单元格区域中根据A10单元格中输入的姓名查找对应的职务。

步骤2：在C10单元格中输入公式“=VLOOKUP（A10，$A$2：$C$6，3，FALSE）”，用于在“A2：C6”单元格区域中根据A10单元格中输入的姓名查找对应的出生日期。

步骤3：在A10单元格中输入一个员工姓名，例如“李靖”，在B10和C10单元格中就会显示出相应的结果，如图15-34所示。

图15-33　本例的原始数据

图15-34　计算结果

Excel 应用TRANSPOSE函数计算转置单元格区域

TRANSPOSE函数用于返回转置单元格区域，即将一行单元格区域转置成一列单元格区域，反之亦然。在行列数分别与数组的行列数相同的区域中，必须将TRANSPOSE输入为数组公式。使用TRANSPOSE可在工作表中转置数组的垂直和水平方向。其语法如下。

TRANSPOSE(array)

其中参数array为需要进行转置的数组或工作表中的单元格区域。所谓数组的转置就是将数组的第1行作为新数组的第1列，将数组的第2行作为新数组的第2列，依此类推。

【典型案例】返回转置单元格区域。本例的原始数据如图15-31所示。

选中A4~A6单元格，输入以下公式，然后按组合键“Ctrl+Shift+Enter”转换为数组公式，得到的计算结果如图15-32所示。

=TRANSPOSE($A$2:$C$2)

图15-31　原始数据

图15-32　计算结果

Excel 应用RTD函数检索实时数据

RTD函数用于从支持COM自动化的程序中检索实时数据。其语法如下。

=RTD(ProgID,server,topic1,[topic2],...)

其中参数ProgID为已安装在本地计算机上、经过注册的COM自动化加载宏的ProgID名称，该名称用引号引起来。Server为运行加载宏的服务器的名称。topic1，topic2，…为1到253个参数，这些参数放在一起代表一个唯一的实时数据。

例如，以下公式将从LOREM_IPSUM服务器的MyComAddIn.Progid中检索“Price”：

=RTD("MyComAddIn.Progid","LOREM_IPSUM","Price")

Excel 应用ROWS函数计算引用的行数

ROWS函数用于返回引用或数组的行数。其语法如下。

ROWS(array)

其中参数array为需要得到其行数的数组、数组公式或对单元格区域的引用。

【典型案例】返回引用的行数。本例的原始数据如图15-29所示。

步骤1：在A2单元格中输入公式“=ROWS（A1：F7）”，用于返回引用中的行数。

步骤2：在A3单元格中输入公式“=ROWS（{1，2，3，4；5，6，7，8；2，3，4，5}）”，用于返回数组常量中的行数。计算结果如图15-30所示。

图15-29　原始数据

图15-30　计算结果

Excel 应用ROW函数计算行号

ROW函数用于返回引用的行号。其语法如下。

ROW(reference)

其中参数reference为需要得到其行号的单元格或单元格区域。如果省略reference，则假定是对函数ROW所在单元格的引用；如果reference为一个单元格区域，并且函数ROW作为垂直数组输入，则函数ROW将reference的行号以垂直数组的形式返回。reference不能引用多个区域。

【典型案例】返回引用的行号。本例的原始数据如图15-27所示。

步骤1：在A2单元格中输入公式“=ROW（）”，用于返回公式所在行的行号。

步骤2：在A3单元格中输入公式“=ROW（D19）”，用于返回引用所在行的行号。计算结果如图15-28所示。

图15-27　原始数据

图15-28　计算结果

Excel 应用OFFSET函数调整新的引用

OFFSET函数的功能是以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。其语法如下。

OFFSET(reference,rows,cols,height,width)

其中参数reference作为偏移量参照系的引用区域。rows为相对于偏移量参照系的左上角单元格，上（下）偏移的行数。cols为相对于偏移量参照系的左上角单元格，左（右）偏移的列数。height为高度，即所要返回的引用区域的行数，必须为正数。width为宽度，即所要返回的引用区域的列数，必须为正数。

【典型案例】以指定的引用为参照系，通过给定偏移量得到新的引用。本例的原始数据如图15-25所示。

步骤1：在A2单元格中输入公式“=OFFSET（B2，2，3，1，1）”，用于显示E4单元格中的值。

步骤2：在A3单元格中输入公式“=SUM（OFFSET（C3：E5，-2，0，3，3））”，用于对数据区域“C1：E3”求和。

步骤3：在A4单元格中输入公式“=OFFSET（C3：E5，0，-3，3，3）”，返回错误值“#REF！”，因为引用区域不在工作表中。计算结果如图15-26所示。

图15-25　原始数据

图15-26　计算结果

Excel 应用MATCH函数在数组中进行查找

MATCH函数用于返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数而不是LOOKUP函数。其语法如下。

MATCH(lookup_value,lookup_array,match_type)

其中参数lookup_value为需要在数据表中查找的数值。例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。lookup_array为可能包含所要查找的数值的连续单元格区域，应为数组或数组引用。match_type为数字-1、0或1，指明如何在lookup_array中查找lookup_value。

【典型案例】查找在指定方式下与指定数值匹配的数组中元素的相应位置。本例的原始数据如图15-23所示。

步骤1：在A7单元格中输入公式“=MATCH（1.3，B2：B5，1）”，由于此处无正确的匹配，所以返回数据区域“B2：B5”中最接近的下一个值（1.2）的位置。

步骤2：在A8单元格中输入公式“=MATCH（1.7，B2：B5，0）”，用于返回数据区域“B2：B5”中1.7的位置。

步骤3：在A9单元格中输入公式“=MATCH（1.7，B2：B5，-1）”，由于数据区域“B2：B5”不是按降序排列，所以返回错误值“#N/A”。计算结果如图15-24所示。

图15-23　原始数据

图15-24　计算结果

Excel 应用LOOKUP函数查找数据

LOOKUP函数用于从单行或单列区域或者从一个数组返回值。LOOKUP函数具有两种语法形式：向量形式和数组形式。

向量形式

向量是只含一行或一列的区域。LOOKUP的向量形式在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。其语法如下。

LOOKUP(lookup_value,lookup_vector,result_vector)

其中参数lookup_value为LOOKUP函数在第一个向量中搜索的值。lookup_value可以是数字、文本、逻辑值、名称或对值的引用。参数lookup_vector为只包含一行或一列的区域，lookup_vector中的值可以是文本、数字或逻辑值。参数result_vector为只包含一行或一列的区域，它必须与lookup_vector大小相同。

注意：lookup_vector中的值必须以升序顺序放置，如“…，-2，-1，0，1，2，…”、“A-Z”、“FALSE，TRUE”；否则，LOOKUP可能无法提供正确的值。大写文本和小写文本是等同的。

【典型案例】从单行或单列区域或者从一个数组查找数据。本例的原始数据如图15-19所示。

步骤1：在A8单元格中输入公式“=LOOKUP（13，A2：A6，B2：B6）”，用于在A列中查找13，然后返回B列中同一行内的值（兰花厅）。

步骤2：在A9单元格中输入公式“=LOOKUP（18，A2：A6，B2：B6）”，用于在A列中查找18，与接近它的最小值17匹配，然后返回B列中同一行内的值（翠竹轩）。

步骤3：在A10单元格中输入公式“=LOOKUP（22，A2：A6，B2：B6）”，用于在A列中查找22，与接近它的最小值21匹配，然后返回B列中同一行内的值（紫云斋）。

步骤4：在A11单元格中输入公式“=LOOKUP（0，A2：A6，B2：B6）”，用于在A列中查找0，并返回错误，因为0小于lookup_vector区域中“A2：A7”中的最小值。计算结果如图15-20所示。

图15-19　原始数据

图15-20　计算结果

数组形式

LOOKUP的数组形式在数组的第一行或第一列中查找指定的值，并返回数组最后一行或最后一列内同一位置的值。当要匹配的值位于数组的第一行或第一列中时，使用LOOKUP的这种形式；当要指定列或行的位置时，则须使用LOOKUP的另一种形式。

说明：一般情况下，最好使用HLOOKUP或VLOOKUP函数而不是LOOKUP的数组形式，因为LOOKUP的这种形式是为了与其他电子表格程序兼容而提供的。

LOOKUP的数组形式语法如下。

LOOKUP(lookup_value,array)

其中参数lookup_value为LOOKUP在数组中搜索的值。

【典型案例】在数组的第一行或第一列中查找指定的值，并返回数组最后一行或最后一列内同一位置的值。本例的原始数据如图15-21所示。

步骤1：在A2单元格中输入公式“=LOOKUP（”B”，{“A”，”B”，”C”，”D”；5，6，7，8}）”，用于在数组的第一行中查找“B”，查找小于或等于它（“B”）的最大值，然后返回最后一行中同一列内的值。

步骤2：在A3单元格中输入公式“=LOOKUP（”apple”，{“A”，3；”B”，4；”C”，5}）”，用于在数组的第一行中查找“apple”，查找小于或等于它（“A”）的最大值，然后返回最后一列中同一行内的值。计算结果如图15-22所示。

图15-21　原始数据

图15-22　计算结果

Excel 应用INDIRECT函数计算指定的引用

INDIRECT函数用于返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身，则须使用函数INDIRECT。其语法如下。

INDIRECT(ref_text,a1)

ref_text为对单元格的引用，此单元格可以包含A1样式的引用、R1C1样式的引用、定义为引用的名称或对文本字符串单元格的引用。a1为一逻辑值，指明包含在单元格ref_text中的引用的类型。

【典型案例】返回由文本字符串指定的引用。本例的原始数据如图15-17所示。

图15-17　原始数据

步骤1：在A7单元格中输入公式“=INDIRECT（$A$2）”，用于返回A2单元格中的引用值。

步骤2：在A8单元格中输入公式“=INDIRECT（$A$3）”，用于返回A3单元格中的引用值。

步骤3：在A9单元格中输入公式“=INDIRECT（$A$4）”，如果B4单元格有定义名“Beijing”，则返回定义名的值。

步骤4：在A10单元格中输入公式“=INDIRECT（”B”&$A$5）”，用于返回A5单元格中的引用值。计算结果如图15-18所示。

图15-18　计算结果