Offset函数常用套路集合

1、Offset 函数常用套路集合Offset函数在实际工作中有很多应用，我们经常用其他 函数公式给Offset的参数赋值，生成动态引用，从而实现丰 富多彩的功能。今天就来总结一下Offset都有哪些用法。1、分组求和如下图所示，在A1:L6的区域内有每个月的销售数据， 我们需要将每行数据按照季度汇总， 也就是每三个单元格作 为一组进行求和。A呂.C_0EF HtJKLMN0P1JanFflbMarAprMayJunJuFAugSapOctNWDecQI020304250030010010030040030&400400J0200200900800110090035005001001001

2、00500WOtoo40010030050011007006009004002003005004005QQ20010030030Q3002009001400UM&005001004005004003DD30010030010030020010001200700BOO640010050020010020050040040010040030010005001300600我们在M2单元格中输入以下公式:=SUM(OFFSET($A2,(COLUM N()-COLUMN($M$1)*3,3)然后向右、 向下拖拉复制公式填充， 这样就完成了所有 的求和了。2、间隔取值还是上面的例子，假如我们想要

3、取一、四、七、十这几个月的值该怎么做呢?如果是一次取一个单元格的值的话，只需要OFFSET就可以了，不需要SUM了。在M1单元格输入以下公式：=OFFSET($A2,(COLUM N()-COLUMN($M$1)*3,1)3、动态确定求和的数据区域以下是数据区域，需要在其他区域按照产品获取某些月 份的总和。我们在P2单元格输入以下公式:=SUMIF($A:$A,$02,0FFSET($A:$A,MATCH(P$1,$B$1:$M这个公式中的Offset的参数是由Match函数来确定的，以A列为基点，确定P1单元格的值在B1:M1中的第几个位 置，就是要移动几列。ABc0EFt产品JanFehM

4、arMay2AAAA300200JOO5003 BBBB4005D0JOO1001CW4 CCCC540100wowAAAA20050G100SOO5006BBB850010&500300100$1,0)PQR1产品FebMaySep2AAAA7007006003 BBBB6002006004rCCCC100500200QCOOMGCOO占dsTMJu300300100300300ooooO:0加却动莉ooooOcoo比如Feb在第2个位置，就从A列移动2列到了C列。这样再用sumif求和就可以了当我们需要获取不连续区域的内容时，就可以用类似的 公式，通过match确定是从那一列取值。

5、4、动态显示最近几天的总数量在以下数据区域中显示了一段日期的销量，我们需要动 态地显示最近3天的销量。在E2单元格中输入以下公式：=SUM(0FFSET(B1,C0UNT(B:B)-D2+1,D2)或者=SUM(OFFSET(B1,COUNT(B:B),-D2)第一个公式的Offset参数是，确定B列有多少个数字， 再减掉需要显示的天数+1。可以这么理解，如果参数只是COUNT(B:B)，则到达了 最后一个单元格B9，也就是显示了最近1天的数据，如果要 显示最近两天的数据， 就需要在COUNT(B:B)的基础上减1， 也就等于-D2+1o第二个公式中的行高使用了负值，这个实际上是可以的，也是常

6、见的用法之一。ABCDE1曰期數重最近天数i肖重23月日360033月1汨2004匚3月1310053S14H20063月佔日40073月倜日300&3月仃日20093月让日1005、创建二级下拉菜单以下是每个部门及对应的二级部门，二级部门个数还不 一样。我们需要设定选择不同的一级部门后，生成对应的二级 部门的列表。A日C0E1一织棊单二级菓单2供应锚管理计划采购物谎3品质进料检杳 挨应商品战管理在绒品奩4生产组装电理我们需要创建两个名称：一级菜单=OFFSET（二级下拉菜单！$A$1,1,C0UNTA（二级下拉菜单!$A:$A）-1）二级菜单=OFFSET（二级下拉菜单!$B$1,M

7、ATCH（二级下拉菜单!G2,一 级菜单,0）,COUNTA（OFFSET（二级 下拉菜单!$B$1:$E$1,MATCH（二级下拉菜单!G2,一级菜单,0）,）在【公式】的【名称管理器】中点【新建】按钮可以插入名称引用位置過：|Xj0 | =OH=ET匸示拉吏单!$A$HCOUNTAC鱼卞拉乘单11|R关闭L _一级菜单可以动态地确定A列的区域范围。二级菜单会根据选择的一级菜单，动态的返回对应的二 级菜单区域。在G2单元格设置数据有效性，设置序列”来源=一级菜单。同样的方法，在H2设置数据有效性，来源=二级菜单名称莒理器L删雜鋳选国”名称 _ _ _ _ 数值引用位蚤动态数据透视表区域 =O

8、FFSET（动态的数据透视表1SAS1 d回二圾菜单_fj_OFFSET（二级下拉菜单r$B$1TMATCH（一级菜单；.-OFFSET（二级下拉菜里皿$数摇验证“J6、创建动态数据透视表定义名称：动态数据透视表区域=OFFSET（动态的数据透视表！$A$1,C0UNTA（动态的 数据透视表！$A:$A）,COUNTA（动态的数据透视表！$1:$1）这个是非常常见的用法，从A1单元格区域开始扩展， 最终行高就是A列有多少个数据，列宽就是第一行有多少个 数据，这里要注意A列和第一行都不能有空值，否则确认的区域就不正确。当我们在数据区域中增加行后，只需要刷新 数据透视表就可以将增加的数据包含进去。我们在插入数据透视表时输入定义好的名称动态数据透视表区域”就可以了，在定义名称时可以自己命名一个简单点的名字请选择妾分析的数摇远择一区域利区域：动态站竜视表区观國族外關娠風U)-Ja,Ti i j -使用此Jj审前琳醪 Q醪放置數籍進视叢爺位晋新工件表4砚有工作表位u：动态的数据