offset函数高级应用

OFFSET引用函数OFFSET(reference,rows,cols,[height],[width])以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。基本原理基点reference公式：=OFFSET(A5,2,2,5,3)=$C$7:$E$11偏移后基点COLUMNS:2HEIGHT:5WIDTH:3以上图示为OFFSET常规偏移示例，函数结果为引用（REF）。注意事项1、首先必须注意，reference必须为引用（包括函数产生的三维引用），不能为常量或数组。2、行偏移_rows、列偏移_cols为必输项，如省略必须用,逗号进行占位，缺省值为0(即不偏移)；行数_height和列数_width为可选项，可省略，缺省视为与基点行数和列数相同。常规知识点一、基点与行数、列数的关系。通常情况下，如果基点以单元格区域作为引用范围，如果不指定行数或列数，系统默认是引用区域的行数和列数来确定结果。公式1=OFFSET(A5:C5,2,2)公式结果$C$7:$E$7公式2=OFFSET(A5,2,2,,3)公式结果$C$7:$E$7由于引用基点为A5:C53列区域，同时公式1中未指定行数或列数，所以最终结果仍然为3列区域，公式1与公式2结果相同，两者等价。公式3=OFFSET(A5:C9,2,2)公式结果$C$7:$E$11公式4=OFFSET(A5,2,2,5,3)公式结果$C$7:$E$11同理，公式3与公式4结果相同，两个公式等价。二、行数与列数对引用基点的影响。在上面的公式1和公式3中，都是引用基点为单元格区域引用，同时都未设置行数与列数的情况下的结果。但有时，基点为区域引用时，用户同时又指定了行数或(和)列数，那么用户必须清楚了解实际引用的基点区域，才能得出正确结果。例如已经将整个P32:R36区域定义为名称DATA：=OFFSET应用基础!$P$32:$R$36例1：利用名称作为基点，得到右图的浅蓝色子区域公式：=OFFSET(DATA,1,,2)公式结果$P$33:$R$34检验：6由于公式中指定了最终结果的行数(2)，未指定列数，那么公式实际上以P32:R32区域作为引用基点，公式等同于：公式1=OFFSET(P32:R32,1,,2)公式2=OFFSET(P32,1,,2,3)检验：6例2：利用名称作为基点，得到右图中红色字体区域公式：=OFFSET(DATA,2,1,3,2)公式结果$Q$34:$R$36检验：6由于公式中指定了最终结果的行数(3)和列数(2)，那么系统只能将DATA中的左上角单元格(P32)作为引用基点，并不是整个DATA区域。公式等同于：=OFFSET(P32,2,1,3,2)公式结果$Q$34:$R$36检验：6ROWS:2以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。2、行偏移_rows、列偏移_cols为必输项，如省略必须用,逗号进行占位，缺省值为0(即不偏移)；行数_height和列数_width为可选项，可省略，缺省视为与基点行数和列数相同。通常情况下，如果基点以单元格区域作为引用范围，如果不指定行数或列数，系统默认是引用区域的行数和列数来确定结果。由于引用基点为A5:C53列区域，同时公式1中未指定行数或列数，所以最终结果仍然为3列区域，公式1与公式2结果相同，两者等价。序号姓名工资但有时，基点为区域引用时，用户同时又指定了行数或(和)列数，那么用户必须清楚了解实际引用的基点区域，才能得出正确结果。1A1002B2003C3004D400由于公式中指定了最终结果的行数(2)，未指定列数，那么公式实际上以P32:R32区域作为引用基点，公式等同于：5E500由于公式中指定了最终结果的行数(3)和列数(2)，那么系统只能将DATA中的左上角单元格(P32)作为引用基点，并不是整个DATA区域。基点员工编号员工姓名性别年龄A0711张三男10A0795李四男20A0733王五女30B1234赵六男40A2345张大男50A0123刘二女60偏移B1123田七男50公式=offset(B1,7,2,-3,3)检验：9等价于：=OFFSET(B1,7-3+1,2,3,3)解释：1、通常情况下，用户都是输入正数来控制产生引用的行数和列数，偏移方向为向下取行数或向右取得列数。2、值得提醒的是，EXCEL支持用户在行数_height和列数_width参数中输入负数来产生引用。其中：负行数向上取行数，负列数向左取列数。具体的应用可以参见[先进先出]实例中的应用进行了解：=102&ID=88776&page=1&px=02、值得提醒的是，EXCEL支持用户在行数_height和列数_width参数中输入负数来产生引用。其中：负行数向上取行数，负列数向左取列数。=102&ID=88776&page=1&px=0动态应用实例1：某些时候，我们需要在公式中对姓名范围进行条件判断，如果直接引用姓名列(K2:K11)，可能会因为随着学员数据的录入，数据区域不能随时变化。因此，我们可以通过OFFSET函数产生动态引用来实现。如定义名称NAMES：=OFFSET(动态引用!$K$2,0,0,COUNTA(动态引用!$K:$K)-1)当数据添加或删除时，NAMES名称中的元素也会动态的扩大或减小。注解：1、通过前面的基础知识的讲解，用户应该了解了OFFSET的基本引用。2、这里的公式主要使用了COUNTA()函数来进行实时计数，统计非空单元格的数量。3、由于基点选择K2（不包含姓名字段），而且数据是连续输入的，那么$K:$K非空数据的个数-1就是实际OFFSET的引用的行数。4、最后将结果返回OFFSET，得：OFFSET(K2,0,0,10)，即可返回K2:K11的引用区域。动态应用实例2：按照各部门明细表，使用函数公式来生成各一级部其下的二级部的列表供用户选择。一级部一级部产品开发部项目管理部管理设计二级部平台开发产品开发部开发管理平台开发游戏开发技术支持部系统数据库人力资源部人事部培训部办公室定义名称MainDept=OFFSET($D$19,,,COUNTA($D$19:$D$29))Depts=OFFSET($D$18,MATCH($B$19,MainDept,0),1,,COUNTA(OFFSET($E$18:$H$18,MATCH($B$19,MainDept,0),)))注解：1、这是一个比较典型的OFFSET动态引用的实例。公式中主要通过对一级部名称的MATCH定位，再根据COUNTA来求得实际二级部部门数，最后通过OFFSET得到结果。2、其中最主要公式就是DEPTS名称的公式，上面通过颜色标识的是公式最核心的部分，通过红色部分得到行偏移，通过蓝色部分得到引用列数。3、如“产品开发部”的二级部列表，则通过MATCH查找其位置(2)，通过内嵌OFFSET的动态引用取得E20:H20区域，再通过COUNTA来得到具体部门数，最后通过：=OFFSET($D$18,2,1,,3)来得出最终二级部区域引用($E$20：$G$20)，提供给B20的有效性序列供选择。二级部学号姓名成绩1001A701002B511003C761004D401005E681006F411007G511008H491009I771010J963、由于基点选择K2（不包含姓名字段），而且数据是连续输入的，那么$K:$K非空数据的个数-1就是实际OFFSET的引用的行数。=OFFSET($D$18,MATCH($B$19,MainDept,0),1,,COUNTA(OFFSET($E$18:$H$18,MATCH($B$19,MainDept,0),)))1、这是一个比较典型的OFFSET动态引用的实例。公式中主要通过对一级部名称的MATCH定位，再根据COUNTA来求得实际二级部部门数，最后通过OFFSET得到结果。2、其中最主要公式就是DEPTS名称的公式，上面通过颜色标识的是公式最核心的部分，通过红色部分得到行偏移，通过蓝色部分得到引用列数。3、如“产品开发部”的二级部列表，则通过MATCH查找其位置(2)，通过内嵌OFFSET的动态引用取得E20:H20区域，再通过COUNTA来得到具体部门数，最后通过：=OFFSET($D$18,2,1,,3)来得出最终二级部区域引用($E$20：$G$20)，提供给B20的有效性序列供选择。编号姓名工资奖金实发工资编号姓名工资奖金实发工资A0711张三301040A0711张三301040A0795李四502070A0733王五7030100编号姓名工资奖金实发工资B1234赵六9040130A0795李四502070A2345张大8050130B1123田七6050110编号姓名工资奖金实发工资A0733王五7030100G1公式：=IF(MOD(ROW(),3),OFFSET($A$1,(MOD(ROW()-1,3)0)*ROUND(ROW()/3,),COLUMN(A1)-1),)本实例是OFFSET常用的动态偏移实例，主要通过MOD、ROW、COLUMN函数来产生动态偏移。核心公式主要是用MOD函数来产生循环序列：{0;1;2;0;1;2;…;0;1;2}；通过ROW()/3来产生固定行数(3)的自然数序列，从而通过OFFSET来产生具体引用得结果。通常情况下，生成工资单只用于提供给员工了解各自工资明细，一般不需要再进行计算或统计，因此公式还可以用以下公式代替(注公式所有结果为文本)。G1公式：=OFFSET($A$1,CHOOSE(MOD(ROW(),3)+1,65535,0,ROUND(ROW()/3,)),COLUMN(A1)-1)&另类的数据清单提取请参见[一表拆分多表]应用：=3&replyID=511118&id=186646&skin=0辅助检查00111210212220313230核心公式主要是用MOD函数来产生循环序列：{0;1;2;0;1;2;…;0;1;2}；通过ROW()/3来产生固定行数(3)的自然数序列，从而通过OFFSET来产生具体引用得结果。通常情况下，生成工资单只用于提供给员工了解各自工资明细，一般不需要再进行计算或统计，因此公式还可以用以下公式代替(注公式所有结果为文本)。=3&replyID=511118&id=186646&skin=0姓名学号年级专业课程名称考试日期AA5036500842003计信管多媒体技术基础2006/7/8BB5036500832003计信管多媒体技术基础2006/7/8BB5036500832003计信管信息管理系统2006/7/12CC5036500822003计信管多媒体技术基础2006/7/8CC5036500822003计信管信息管理系统2006/7/9CC5036500822003计信管网络数据库SQL2006/7/10EFG5036500812003计信管多媒体技术基础2006/7/8EFG5036500812003计信管信息管理系统2006/7/12EFG5036500812003计信管网络数据库SQL2006/7/13EFG5036500812003计信管市场营销学2006/7/14高级应用：以上是原表（按姓名排序），希望按每个人单独汇总，用公式实现。公式结果：姓名学号年级专业课程名称考试日期AA5036500842003计信管多媒体技术基础2006/7/8姓名学号年级专业课程名称考试