查询与引用函数应用

Excel实战：实现学生成绩查询

打开“学生成绩查询.xlsx”工作簿，某大学语言测试成绩表如图16-67所示。现在需要实现只输入学生姓名，就能够查询某一学生的成绩或其他信息。

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

图16-67　学生成绩表

STEP01：选中H3单元格，在编辑栏中输入公式“=INDEX(E:E,MATCH($G$3,$A:$A,0))”，输入完成后按“Enter”键返回计算结果，如图16-68所示。该公式使用INDEX函数返回成绩表中$G$3所在行对应E列的值，使用MATCH函数查找成绩表中A列与单元格$G$3相同的值。

STEP02：选中G3单元格，在单元格中输入一个学生的姓名，这里输入“田珊”，然后按“Enter”键返回，可以看到在H3单元格中显示出其成绩，如图16-69所示。

图16-68　输入成绩公式

图16-69　输入学生姓名查询成绩

STEP03：选中H5单元格，在编辑栏中输入公式“=INDEX(E:E,MATCH($G$5,$B:$B,0))”，输入完成后按“Enter”键返回计算结果，如图16-70所示。该公式使用INDEX函数返回成绩表中$G$5所在行对应E列的值，使用MATCH函数查找成绩表中A列与单元格$G$5相同的值。

STEP04：选中G5单元格，在单元格中输入一个学生的准考证号码，这里输入“1525509012510716”，然后按“Enter”键返回，可以看到在H5单元格中显示出其成绩，如图16-71所示。

图16-70　在H5单元格中输入公式

图16-71　使用准考证号码查询学生成绩

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

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

TRANSPOSE(array)

其中，array参数为需要进行转置的数组或工作表中的单元格区域。所谓数组的转置就是，将数组的第1行作为新数组的第1列，数组的第2行作为新数组的第2列，依此类推。下面通过实例详细讲解该函数的使用方法与技巧。

打开“TRANSPOSE函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-65所示。要求根据工作表中的数据内容，返回转置单元格区域。具体操作步骤如下。

选中A4:A6单元格区域，在公式编辑框中输入公式“=TRANSPOSE($A$2:$C$2)”，然后按“Ctrl+Shift+Enter”组合键转换为数组公式，得到的计算结果如图16-66所示。

图16-65　原始数据

图16-66　返回转置单元格区域

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参数为需要得到其行数的数组、数组公式或对单元格区域的引用。下面通过实例详细讲解该函数的使用方法与技巧。

打开“ROWS函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-62所示。要求根据工作表中的数据内容，返回引用的行数。具体操作步骤如下。

图16-62　原始数据

STEP01：选中A2单元格，在编辑栏中输入公式“=ROWS(A1:F7)”，用于返回引用中的行数，输入完成后按“Enter”键返回计算结果，如图16-63所示。

STEP02：选中A3单元格，在编辑栏中输入公式“=ROWS({1,2,3,4;5,6,7,8;2,3,4,5})”，用于返回数组常量中的行数，输入完成后按“Enter”键返回计算结果，如图16-64所示。

图16-63　返回引用中的行数

图16-64　返回数组常量中的行数

Excel 计算行号：ROW函数

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

ROW(reference)

其中，reference参数为需要得到其行号的单元格或单元格区域。如果省略reference参数，则假定是对函数ROW所在单元格的引用。如果reference参数为一个单元格区域，并且函数ROW作为垂直数组输入，则函数ROW将reference参数的行号以垂直数组的形式返回。reference参数不能引用多个区域。下面通过实例详细讲解该函数的使用方法与技巧。

打开“ROW函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-59所示。要求根据工作表中的数据内容，返回引用的行号。具体操作步骤如下。

STEP01：选中A2单元格，在编辑栏中输入公式“=ROW()”，用于返回公式所在行的行号，输入完成后按“Enter”键返回计算结果，如图16-60所示。

图16-59　原始数据

图16-60　返回公式所在行的行号

STEP02：选中A3单元格，在编辑栏中输入公式“=ROW(D19)”，用于返回引用所在行的行号，输入完成后按“Enter”键返回计算结果，如图16-61所示。

图16-61　返回引用所在行的行号

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

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

INDIRECT(ref_text,a1)

其中，ref_text参数为对单元格的引用，此单元格可以包含A1样式的引用、R1C1样式的引用、定义为引用的名称或对文本字符串单元格的引用。a1为一逻辑值，指明包含在单元格ref_text中的引用的类型。下面通过实例详细讲解该函数的使用方法与技巧。

打开“INDIRECT函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-54所示。要求根据工作表中的数据内容，返回由文本字符串指定的引用。具体操作步骤如下。

图16-54　原始数据

STEP01：选中A7单元格，在编辑栏中输入公式“=INDIRECT($A$2)”，用于返回A2单元格中的引用值，输入完成后按“Enter”键返回计算结果，如图16-55所示。

STEP02：选中A8单元格，在编辑栏中输入公式“=INDIRECT($A$3)”，用于返回A3单元格中的引用值，输入完成后按“Enter”键返回计算结果，如图16-56所示。

图16-55　返回单元格A2的引用值

图16-56　返回A8单元格中的引用值

STEP03：选中A9单元格，在编辑栏中输入公式“=INDIRECT($A$4)”，用于返回A4单元格中的引用值，如果B4单元格有定义名“Beijing”，则返回定义名的值（2008），输入完成后按“Enter”键返回计算结果，如图16-57所示。

STEP04：选中A10单元格，在编辑栏中输入公式“=INDIRECT(“B”&$A$5)”，用于返回A5单元格中的引用值，输入完成后按“Enter”键返回计算结果，如图16-58所示。

图16-57　返回A4单元格中的引用值

图16-58　返回A5单元格中的引用值

Excel 返回区域值或值引用：INDEX函数

INDEX函数用于返回表或区域中的值或值的引用，它两种形式：数组形式和引用形式。

数组形式

返回表格或数组中的元素值，此元素由行序号和列序号的索引值给定。当函数INDEX的第1个参数为数组常量时，使用数组形式。其语法如下：

INDEX(array,row_num,column_num)

其中，array参数为单元格区域或数组常量。如果数组只包含一行或一列，则相对应的参数row_num参数或column_num参数为可选参数。如果数组有多行和多列，但只使用row_num参数或column_num参数，函数INDEX返回数组中的整行或整列，且返回值也为数组。row_num参数为数组中某行的行号，函数从该行返回数值。如果省略row_num参数，则必须有column_num参数。column_num参数为数组中某列的列标，函数从该列返回数值。如果省略column_num参数，则必须有row_num参数。下面通过实例详细讲解该函数的使用方法与技巧。

打开“INDEX.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-46所示。要求根据工作表中的数据内容，返回表格或数组中的元素值。具体操作步骤如下。

STEP01：选中A5单元格，在编辑栏中输入公式“=INDEX(A2:B3,2,2)”，用于返回位于区域中第2行和第2列交叉处的数值（沙和尚），输入完成后按“Enter”键返回计算结果，如图16-47所示。

图16-46　原始数据

图16-47　A5单元格返回结果

STEP02：选中A6单元格，在编辑栏中输入公式“=INDEX(A2:B3,2,1)”，用于返回位于区域中第2行和第1列交叉处的数值（唐三藏），输入完成后按“Enter”键返回计算结果，如图16-48所示。

引用形式

返回指定的行与列交叉处的单元格引用。如果引用由不连续的选定区域组成，可以选择某一选定区域。其语法如下：

INDEX(reference,row_num,column_num,area_num)

其中，reference参数为对一个或多个单元格区域的引用。如果为引用输入一个不连续的区域，必须将其用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数row_num参数或column_num参数分别为可选项。例如，对于单行的引用，可以使用函数INDEX(reference,,column_num)。

row_num参数为引用中某行的行号，函数从该行返回一个引用。column_num参数为引用中某列的列标，函数从该列返回一个引用。area_num参数为选择引用中的一个区域，返回该区域中row_num参数和column_num参数的交叉区域。选中或输入的第1个区域序号为1，第2个为2，依此类推。如果省略area_num参数，则函数INDEX使用区域1。

例如，如果引用描述的单元格为(A1:C4,D1:E4,F1:H4)，则area_num 1为区域A1:C4，area_num 2为区域D1:E4，而area_num 3为区域F1:H4。下面通过实例详细讲解该函数的使用方法与技巧。

打开“INDEX函数.xlsx”工作簿，切换至“Sheet2”工作表，本例中的原始数据如图16-49所示。要求根据工作表中的数据内容，返回指定的行与列交叉处的单元格引用。具体操作步骤如下。

图16-48　A6单元格返回结果

图16-49　原始数据

STEP01：选中A12单元格，在编辑栏中输入公式“=INDEX(A2:C6,2,3)”，用于返回区域A2:C6中第2行和第3列的交叉处，即C3单元格的内容，输入完成后按“Enter”键返回计算结果，如图16-50所示。

STEP02：选中A13单元格，在编辑栏中输入公式“=INDEX((A1:C6,A7:C10),2,2,2)”，用于返回第2个区域A8:C11中第2行和第2列的交叉处，即B8单元格的内容，输入完成后按“Enter”键返回计算结果，如图16-51所示。

图16-50　A12单元格返回结果

图16-51　A13单元格返回结果

STEP03：选中A14单元格，在编辑栏中输入公式“=SUM(INDEX(A1:C10,0,3,1))”，用于对第1个区域A1:C10中的第3列求和，即对C1:C10求和，输入完成后按“Enter”键返回计算结果，如图16-52所示。

STEP04：选中A15单元格，在编辑栏中输入公式“=SUM(B2:INDEX(A2:C6,5,2))”，用于返回以B2单元格开始到A2:C6单元格区域中第5行和第2列交叉处结束的单元格区域的和，即B2:B6单元格区域的和，输入完成后按“Enter”键返回计算结果，如图16-53所示。

图16-52　A14单元格返回结果

图16-53　A15单元格返回结果

Excel 计算数组或引用列数：COLUMNS函数

COLUMNS函数用于返回数组或引用的列数。其语法如下：

COLUMNS(array)

其中，array参数为需要得到其列数的数组或数组公式，或对单元格区域的引用。将reference参数中的列标以水平数组的形式返回。reference参数不能引用多个区域。下面通过实例详细讲解该函数的使用方法与技巧。

打开“COLUMNS函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-43所示。要求根据工作表中的数据内容，返回数组或引用的列数。具体操作步骤如下。

图16-43　原始数据

STEP01：选中A2单元格，在编辑栏中输入公式“=COLUMNS(A1:D5)”，用于返回引用中的列数，输入完成后按“Enter”键返回计算结果，如图16-44所示。

STEP02：选中A3单元格，在编辑栏中输入公式“=COLUMNS({1,2,3,4;4,5,6,7;5,6,7,8})”，用于返回数组常量中的列数(3)，输入完成后按“Enter”键返回计算结果，如图16-45所示。

图16-44　返回引用中的列数

图16-45　返回数组常量中的列数

Excel 计算给定引用列标：COLUMN函数

COLUMN函数用于返回给定引用的列标。其语法如下：

COLUMN(reference)

其中，reference参数为需要得到其列标的单元格或单元格区域。如果省略reference，则假定为是对函数COLUMN所在单元格的引用。如果reference参数为一个单元格区域，并且函数COLUMN作为水平数组输入，则函数COLUMN将reference参数中的列标以水平数组的形式返回。reference参数不能引用多个区域。下面通过实例详细讲解该函数的使用方法与技巧。

打开“COLUMN函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-40所示。要求根据工作表中的数据内容，返回给定引用的列标。具体操作步骤如下。

STEP01：选中A2单元格，在编辑栏中输入公式“=COLUMN()”，用于返回公式所在列的列标，输入完成后按“Enter”键返回计算结果，如图16-41所示。

图16-40　原始数据

STEP02：选中A3单元格，在编辑栏中输入公式“=COLUMN(B15)”，用于返回B15的列标，输入完成后按“Enter”键返回计算结果，如图16-42所示。

图16-41　计算公式所在列

图16-42　计算引用的列

Excel 计算引用区域个数：AREAS函数

AREAS函数用于返回引用中包含的区域个数，区域表示连续的单元格区域或某个单元格。其语法如下：

AREAS(reference)

其中，reference参数表示对某个单元格或单元格区域的引用，也可以引用多个区域。如果需要将几个引用指定为一个参数，则必须用括号括起来，以免Excel将逗号作为参数间的分隔符。下面通过实例详细讲解该函数的使用方法与技巧。

打开“AREAS函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图16-36所示。要求根据工作表中的数据内容，返回引用中包含的区域个数。具体操作步骤如下。

STEP01：选中A2单元格，在编辑栏中输入公式“=AREAS(B1:D5)”，用来计算引用中包含的区域个数，输入完成后按“Enter”键返回，即可得出计算结果为1，如图16-37所示。

图16-36　原始数据

图16-37　A2单元格计算结果

STEP02：选中A3单元格，在编辑栏中输入公式“=AREAS((B1:D5,E5,F6:I9,G6))”，用来计算引用中包含的区域个数，输入完成后按“Enter”键返回，即可得出计算结果为4，如图16-38所示。

STEP03：选中A4单元格，在编辑栏中输入公式“=AREAS(B1:D5 B2)”，用来计算引用中包含的区域个数，输入完成后按“Enter”键返回，即可得出计算结果为1，如图16-39所示。

图16-38　A3单元格返回结果

图16-39　A4单元格返回结果