EXCEL中的引用函数OFFSET是必须要掌握的函数之一,它在EXCEL中的应用非常广泛,从复杂的数据汇总、到数据透视表再到高级动态图表,都离不开功能强大的OFFSET函数。 例1:先举一个例子来感受一个OFFSET函数 如下图所示,在D2中输入: = OFFSET(A1,1,2,1,1),表示引用以A1为基点,向下偏移1行、向右偏移2列的值。 各参数解释如下: A1:引用的单元格。 1: 表示要移动的行数。 正数意味着向下移动,负数意味着向上移动。 2: 表示要移动的列数。 正数意味着向右移动,负数意味着向左移动。 1(倒数第二个值):(可选。) 表示要返回的数据行数。 这个数必须是正数。 1(最后值):(可选。) 表示要返回的数据列数。 这个数必须是正数。 下面再举一些例子对这个函数进行说明。 例2:提取最后的报价 D2单元格内是写好的公式返回的最后报价。 D2 = OFFSET(B1,COUNTA(B:B)-1,) COUNTA(B:B)-1返回B列有报价的单元格数量。公式是从B1开始向下引用最后一行,偏移的列为为0,参数省略。 例3:按条件计算区域数据之和 如下图所示,当条件有所变化时,公式自动计算条件所对应的部门数据之和。 = SUM(OFFSET(A2:A8,,MATCH(G2,A1:E1,)-1)) 本例中向下偏移量为0,故省略OFFSET函数中第2个参数;MATCH函数返回向右偏移的列数。 例4:将二维数组转化为一维数组 如下图所示,左部分为二维数据,在F2单元格中输入公式: OFFSET(A$1,INT((ROW(A1)-1)/4),MOD(ROW(A4),4)) 下拉填充公式,即可转化为一维数组。 以单元格A1为引用点,用INT((ROW(A8)-1)/4)返回向下偏移量,用MOD(ROW(A11),4))返回向右的偏移量。 例5:按条件动态引用区域数据 如下图所示,按条件引用各地区的整行数据,该整行数据作为动态图表的数据源。 选中B9:F9输入数组公式: = OFFSET(B1:F1,MATCH(A9,A2:A5,),) 输入完毕后按CTRL + SHIFT +ENTER形成数组公式。 OFFSET公式中MATCH函数返回向下偏移的行数,向右偏移为0,该参数省略。 |
|