OFFSET函数常用套路

用于生成数据区域的引用，再将这个引用作为半制品，作为动态图表的数据源、或是作为其他函数的参数，举办二次加工。

=OFFSET(基点,偏移的行数,偏移的列数,[新引用的行数],[新引用的列数])
第二参数利用正数时，暗示从基地向下偏移，负数暗示向上偏移。
第三参数利用正数时，暗示向右偏移，利用负数时暗示向左偏移。
第四和第五参数是可选的，假如省略这两个参数，新引用的区域就是和基点一样的巨细。

如下图，要将A2:D7单位格中多行多列的姓名，转换到一列中。
F2单位格公式为：

=OFFSET($A$2,(ROW(A1)-1)/4,MOD(ROW(A1)-1,4))&””

OFFSET函数的基点为A2。

向下偏移的行数为(ROW(A1)-1)/4，这部门公式下拉时，可以获得从0开始，按0.25递增的序号，即0，0.25，0.5，0.75，1，1.25，1.5……OFFSET函数对带有小数的参数自动向下取整，向下偏移的行数依次为0 0 0 0 1 1 1 1 2 2 2 2……也就是公式每下拉四行，就从数据源中向下偏移一行。
向右偏移的列数为MOD(ROW(A1)-1,4)，这部门公式下拉时，可以获得0 1 2 3 0 1 2 3……的轮回序列序列，也就是公式每下拉一个行，就从数据源向右偏移一列，下拉到第五行时，偏移的列数又会从0开始。
偏移行数和偏移列数二者团结，最终形成1 2 3 4 2 2 3 4 3 2 3 4 ……这样的偏移方法。

如下图所示，要计较从1月份到指定月份的累计销售额。
F4单位格公式为：
=SUM(OFFSET(B2,0,0,MATCH(F2,A2:A13,0)))

MATCH(F2,A2:A13,0)部门，先利用MATCH函数计较出F2单位格中的月份在A2:A13中的位置，功效为9.
OFFSET函数以B2单位格为基点，向下偏移0行，向右偏移0列，以MATCH函数的计较功效作为新引用的行数，最终获得B2:B10单位格区域的引用，再利用SUM函数计较这个区域中的总和，获得从1月份到指定月份的销售总额。

如下图所示，A列和B列是销售流水记录，要计较出最近7天的平均销量。
F4单位格公式为：
=AVERAGE(OFFSET(B1,COUNT(B:B),0,-7))

先利用COUNT函数，抖客教程网，统计出B列的数值个数。
OFFSET函数以B1为基点，以COUNT的功效作为向下偏移的行数，也就是B列有几多个数值，就向下偏移几多行。
这时候就相当于到了B列数值的最后一行，给定的偏移列数是0，新引用的行数是-7，获得从B列数值的最后一行开始，再向上7行这样一个动态的区域。
假如B列的数值增加了，COUNT函数的计数功效就增加了，OFFSET函数的行偏移参数也就随之变革。
就相当于一竿子捅到底，然厥后个烧鸡大窝脖儿，向上引用7行，所以获得的始终是最后7行的引用。
最后利用AVERAGE函数计较出这个引用区域中的平均值。

如下图，是各食堂的采购记录，需要计较筛选后的商品总价。
G1单位格公式为：
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW(1:9),0))*C2:C10*D2:D10)

要计较筛选后的内容，首先需要判定单位格是不是处于显示状态。
先来看OFFSET(A1,ROW(1:9),0)部门，OFFSET函数以A1单位格为基点，向下偏移的行数是ROW(1:9)的计较功效，暗示依次向下偏移1~9行，最终获得9个引用区域，每个单位格区域由一个单位格组成。
这里涉及到多维引用的常识点了，小同伴们假如犯模糊，可以先保藏一下。
接下来利用SUBTOTAL函数对OFFSET函数获得的多个引用区域举办处理惩罚，第一参数利用3，暗示利用COUNTA函数的计较法则，即依次统计A2~A9这九个单位格区域中的不为空的单位格个数。
假如单位格处于显示状态，则对这个单位格的统计功效为1，不然统计功效为0。
这部门公式获得雷同下面的结果：
{0;0;0;0;0;1;1;1;1}

再用SUBTOTAL函数的功效乘以C列的单价和D列的数量，假如单位格处于显示状态，则相当于1*数量*单价，不然相当于0*数量*单价。
最后利用SUMPRODUCT函数对乘积举办求和，这样就获得筛选后的商品总价了。
除了以上通例的用法，OFFSET函数还常常用于动态图表的建造，这些内容咱们今后继承分享。