OFFSET函数的另外一个经典应用—定义名称

Excel函数 2023-01-08

从零讲解了OFFSET函数的使用，本文最主要通过一个实例来讲解OFFSET函数在定义名称方面的运用。

　　下图所示的工作表命名为：名称实例，其中A1：C13区域为源数据，本讲座要完成的作业是通过源数据，使用OFFSET函数定义一个叫“AA”的名称，然后完成下图1、2、3个班级的实考人数、总分、最高分、优秀数、平均分的统计。

OFFSET函数之定义名称

　　本题最核心的一个问题就是定义AA名称。

　　单击菜单“插入”——“名称”——“定义”，在“名称”框中输入名称“AA”，然后在下面的引用位置输入：=OFFSET(名称实例!$A$1,MATCH(名称实例!$D17,名称实例!$A$2:$A$13,),2,COUNTIF(名称实例!$A$2:$A$13,名称实例!$D17))

　　引用位置输入公式的含义，解释如下。把上面这个公式分成两部分来查看，就很容易了。

　　第一部分：MATCH(名称实例!$D17,名称实例!$A$2:$A$13,)，这个值是OFFSET的第二个参数，使用MATCH来精确查找位置。代表偏移的行数就由MATCH部分的值来控制。这个公式的意思就是查找D17单元格的值在A2：A13区域中的位置。

　　MATCH函数也是一个查找函数。MATCH 函数会返回中匹配值的位置而不是匹配值本身。在使用时，输入单值它就返回单值，输入多值就返回多值。MATCH函数在众多的数字中只查找第一次出现的，后来出现的它返回的也是第一次出现的位置。MATCH函数一般都和别的函数嵌套使用。

　　第二部分：COUNTIF(名称实例!$A$2:$A$13,名称实例!$D17)，这个值是OFFSET的第四个参数，高度就由COUNTIF的值来控制。

　　定义完名称之后，在E17单元格输入公式：=COUNTA(AA)，然后下拉，就可以求出各个班级的实考人数。

　　在F17单元格输入公式：=SUM(AA)，然后下拉，就可以求出各个班级的总分。

　　在G17单元格输入公式：=MAX(AA)，然后下拉，就可以求出各个班级的最分。

　　在J17单元格套用COUNTIF函数的公式：countif(区域，条件)，输入公式：=COUNTIF(AA,">=85")，然后下拉，就可以求出各个班级的优秀人数。在这个公式中，我们假定优秀人数的分数为大于等于85分。

　　在K17单元格输入公式：=AVERAGE(AA)，然后下拉，就可以求出各个班级的平均分。