6.2　引用函数应用

例　返回由文本字符串指定的引用

如果需要按照给定的行号合列标建立文本类型的单元格地址，可通过ADDRESS函数实现。

ADDRESS函数的语法为：=ADDRESS(row_num, column_num,abs_num,a1,sheet_text)，其中各参数的含义介绍如下。

·row_num：在单元格引用中使用的行号。

·column_num：在单元格引用中使用的列号。

·abs_num：指定返回的单元格引用类型，为1或省略时返回绝对引用；为2时返回绝对行号，相对列标；为3时返回相对行号，绝对列标；为4时返回相对引用。

·a1：用以指定A1或R1C1引用格式的逻辑值。若为TRUE或省略，返回A1样式的引用；若为FALSE，返回R1C1样式的引用。

·sheet_text：为一文本，用于指定作为外部引用的工作表的名称。

下面举例说明函数的是否方法。

例1：返回“C2”单元格的绝对引用

具体操作：在需要显示结果的单元格中输入公式：=ADDRESS(2,3)，然后按下“Enter”键确认即可。

例2：在R1C1引用样式中的绝对行号，相对列标

具体操作：在需要显示结果的单元格中输入公式：=ADDRESS(2,3,2,FALSE)，然后按下“Enter”键确认即可。

例　查询抽查的产品编号地址

质检部门对市场或超市的产品进行质量抽查时，这个过程是随机的。

下面利用公式根据抽查产品的编号来统计该产品编号所在的单元格位置，具体操作如下。

01 打开工作表，输入一批产品的编号，并从中挑选出几个要抽查的产品编号。

02 选中“C3”单元格，在其中输入公式：=ADDRESS (3,1)，按下“Enter”键确认，即可显示产品编号“B-T121”的单元格地址为“$A$5”。

03 按照上一步操作，在“C4”和“C5”单元格中输入计算地址的公式来确定其他抽查产品编号的地址，结果如图所示。

例　计算引用中包含的区域个数

如果需要知道一个公式引用中包含的单元格区域或单元格的个数，可通过AREAS函数实现。

AREAS函数的语法为：=AREAS(reference)，其中参数reference为对某个单元格或单元格区域的引用，也可以引用多个区域。

注意：如果需要将几个引用指定为一个参数，则多个引用间需要用逗号隔开，而且必须用括号“（）”括起来，以免Excel将逗号视为字段分隔符。

假设某个销售公司在市内多个区都有分销处，现在将几个分销处合并为一个区域分布，要求统计区域的总数。

01 打开工作表，在每个单元格区域中输入区域名、负责人和员工人数等信息。

02 在需要显示统计区域个数的单元格中输入公式：=AREAS ((A4:B6, D4:E6,A8:B10,D8:E10))，然后按下“Enter”键确认即可。

例　返回给定引用的列标

如果需要返回给定引用的列标，可通过COLUMN函数实现。COLUMN函数的语法为：=COLUMN（reference），其中reference参数为需要得到其列标的单元格或单元格区域。

需要注意的是，若省略参数或该参数为一个单元格区域，且函数以水平数组公式形式输入，则函数将以水平数组形式返回参数的列号；若参数为一个单元格区域，并且函数不是以水平数组公式的形式输入的，则函数将返回最左侧列的列号；若省略参数，则假定该参数为对COLUMN函数所在单元格的引用。

下面举例说明函数的使用方法：Excel工作表的列表默认显示为“A”、“B”、“C”等，若超过26列则以两个字母表示，如“AB”、“IV”等，若超过702列则以3位字符表示。现在需要查询单元格中的字母对应的是第几列。

01 在“A9”单元格中输入公式：=COLUMN(INDIRECT (A9&1))。

02 按下“Enter”键确认，公式返回指定列表为工作表中的第几列。

提示：本例首先利用INDIRECT函数将单元格中的字母连接数字1，将其转换为单元格引用，即将字符串“AB1”转换为引用单元格“AB1”，然后再利用COLUMN函数技术单元格“AB1”位于工作表中的第几列。

例　返回指定行列交叉处引用的单元格

如果需要返回表或区域中的值或值的引用，可通过INDEX函数实现。INDEX函数的语法为：=INDEX(array,row_num,column_num)，各参数的含义介绍如下。

·Array：指定数组。

·row_num：数组中某行的行序号。

·column_num：数组中某列的列序号。

下面举例说明如何返回指定行列交叉处引用的单元格。

例1：返回名为“平原”的选手的比赛成绩

01 在“A1:C8”单元格区域中，根据需要输入相关数据。

02 在需要显示结果的单元格中输入公式：=INDEX(A1:C8,4,2)，然后按下“Enter”键确认即可。

例2：返回数组常量中第二列、第一行的值

01 在“A1:C8”单元格区域中，根据需要输入相关数据。

02 在需要显示结果的单元格中输入公式：=INDEX({1,2;3,4}, 0,2)，然后按下“Enter”键确认即可。

例　多区域计数

假设工作表中有2个班级的成绩，现在需要统计成绩不及格的人数，具体操作如下。

01 打开工作表，在其中输入2个班级的学生成绩。

02 选中需要统计不及格人数的单元格，在其中输入公式：=SUM(COUNTIF(INDIRECT({"C2:C9","F2:F9"}),"<60"))，然后按下“Enter”键确认，即可得到结果。

例　计算多个工作表中成绩的最大平均值

假设3个班的成绩分别置于同一个工作簿的3个工作表中，现在需要计算3个班的最高平均成绩是多少。

01 打开工作簿，分别在3个工作表中输入3个班的成绩，其中单元格区域须一致，如A2:A6为学生姓名，B2:B6为成绩。

02 在单元格中输入公式：=MAX (SUBTOTAL(1, INDIRECT({"1","2","3"}&"班!B2:B6")))，按下“Enter”键确认，得到3个班中的最高平均成绩。

提示：本例先利用常量数组分别表示3个工作表的数据区，接着利用INDIRECT函数将字符串转换为区域引用，而AVERAGE函数无法跨表计算平均值，故使用SUBTOTAL函数计算平均值，最后则使用MAX函数求取最大值。

例　返回引用的行号

如果需要返回引用的行号，可通过ROW函数实现。ROW函数的语法为：=ROW(reference)，其中参数reference为指定需要得到其行号的单元格或单元格引用。

注意：如果省略reference，则假定是对函数ROW所在单元格的引用。如果reference为一个单元格区域，并且函数ROW作为垂直数组输入，则函数ROW将以垂直数组的形式返回reference的行号。

下面举例说明ROW函数的使用方法。

·返回公式所在行的行号。具体操作为：在需要显示结果的单元格中输入公式：=ROW()，然后按下“Enter”键确认即可。

·返回引用中第一行、第二行、第三行的行号。具体操作为：选中需要显示结果的单元格区域，在其中输入公式：=ROW (B2:D5)，然后按下“Ctrl+Shift+Enter”组合键确认即可。

例　产生每两行空一行后累加1的编号

假设需要在A列产生自然数序列编号，从1开始，且每个两行空一行，即让每个组的成员之间产生一个空行，以便查看。

01 打开工作表，选中“A1”单元格，在其中输入公式：=IF(ROW()=1,1,IF(MOD(ROW(),3),COUNT(OFFSET(A$1,,,ROW()-1))+1,""))。

02 按下“Enter”键确认，公式将返回1。

03 用填充柄功能填充，在A列每两行空一行产生递加1的序列。

04 在编号右侧输入成员姓名即可。

在本例中，需要对当前单元格前面的区域进行计数，而A1单元格前不存在单元格，OFFSET函数则会产生错误引用。此时利用IF函数配合ROW函数避开此问题，直接用IF函数为公式赋值，而不会计算OFFSET函数的引用了。

本例使用ROW函数判断当前行的行号，若等于1 则返回1，对于其他行，当行号为3的整数倍数时，返回空白，相邻的下一行则从A1开始至当前行的上一行结束的区域中的数字个数加1。

提示：本例还可以采用公式：=IF(ROW()=1,1,IF(MOD (ROW(),3),MAX(A$1:A1)+1,""))，此时公式直接对前一个非空白单元格递加1来完成。

例　检查每个志愿是否与前面的重复

假设每个考生需要填写9个志愿，且不能重复，现在需要检查区域中的志愿是否和前面的重复。

01 打开工作表，填写好志愿和编号。选中需要显示检测结果的单元格，在其中输入公式：=MATCH(C2,$C$2:$C$10,)<>ROWS($2:2)。

02 按下“Enter”键确认，公式将返回对第一个志愿是否与前面的志愿相重复进行判断的逻辑值。

03 使用填充柄功能将公式向下填充，即可得到所有填写的志愿是否与前面的志愿重复的逻辑结果。其中“FALSE”表示无重复，“TRUE”表示有重复。

提示：本例中的ROWS函数的参数采用混合引用，引用区域随着公式的填充变化，从而使引用区域的行数逐个累加1，再用MATCH函数计算每个志愿的排位，并用排位与第2行开始、当前行前面的区域行数进行比较，故使用“<>”判断是否与前面的志愿重复。

例　返回数组或引用的行数

如果需要返回引用或数组的行数，可通过ROWS函数实现。ROWS函数的语法为：=ROWS（array），其中array参数为指定需要得到其行数的数组、数组公式或对单元格区域的引用。

假设需要返回“A2:H5”单元格区域的行数，具体操作为：在需要显示结果的单元格中输入公式：=ROWS(A2:H5)，然后按下“Enter”键确认即可。

例　将行转置成列

如果需要返回转置单元格区域，即将一行单元格区域转置成一列单元格区域，可通过TRANSPOSE函数实现。TRANSPOSE函数的语法为：=TRANSPOSE（array），其中array参数为指定需要转置的单元格区域或数组。

下面举例说明函数的使用方法。

例1：将A列中的星期数转换为一行显示

01 在“A2:A8”单元格区域中输入星期数。

02 选中需要显示转换结果的区域，如“B1:H1”。

03 在编辑栏中输入公式：=TRANSPOSE($A$2:$A$8)，然后按下“Ctrl+Shift+Enter”组合键确认即可。

例2：将第1行中的星期数转换为一列显示

01 在“B1:H1”单元格区域中输入星期数。

02 选中需要显示转换结果的区域，如“A2:A8”。

03 在编辑栏中输入公式：=TRANSPOSE($B$1:$H$1)，然后按下“Ctrl+Shift+Enter”组合键确认。