offset函数和match Offset函数和match函数结合 收藏 0

Excel 公式函数/查找函数之OFFSET+MATCH

本文于2023年4月6日首发于本人同名公众号：Excel活学活用，更多文章敬请关注！

关于Excel查找函数，我们已介绍过VLOOKUP(Excel 公式函数/查找函数之VLOOKUP)，LOOKUP(Excel 公式函数/查找函数之LOOKUP)，INDEX+MATCH(Excel 公式函数/查找函数之INDEX+MATCH)，我们今天来聊聊OFFSET+MATCH。

OFFSE，它的意思是\”偏移\”，我们先来看下它的用法：OFFSET 函数在 Excel 中用于返回一个基于给定参考点的单元格或范围。这个参考点可以是单元格或范围，而不能是某个具体的单元格地址（表示地址的文本）。公式示例：

reference 是必需的参数，它是一个单元格或范围，它是要偏移的参考点。

rows 是必需的参数，它是偏移量的行数。可以是正数（表示向下偏移）或负数（表示向上偏移），也可以是0，表示包括参考点。

cols 是必需的参数，它是偏移量的列数。可以是正数（表示向右偏移）或负数（表示向左偏移），也可以是0，表示包括参考点。

height 是可选的参数，它指定返回范围的高度。如果省略，则假定高度与 reference 相同。

width 是可选的参数，它指定返回范围的宽度。如果省略，则假定宽度与 reference 相同。

OFFSET 函数返回一个从 reference 开始的新范围。新范围的大小由 height 和 width 参数指定，如果省略这些参数，则使用与 reference 相同的大小。

OFFSET查找功能举例：

该公式表示从数据源最左上单元格开始，通过MATCH函数计算偏移量，后面的参数省略，我们得到与编码对应的名称、数量等信息。

它跟INDEX有点像，但含义是显著不同的。

OFFSET的常用方法是返回一个单元格区域，例如：

从数据源最左上单元格开始，行列偏移量都为0，返回一个10行，5列的单元格区域。不过，这种固定返回行、列的应用范围是有限的，通常需要结合其他函数来使用，比如：

这种用法，常用于定义名称，得到一个动态的数据区域，比如我们在前面定义的名称\”库存商品明细表\”：

利用OFFSET函数结合COUNTA函数来实现对库存商品明细表数据区域的动态引用，随着数据的增加、删除，这个区域会跟着动态变化，非常实用。公式中最后一个参数“5”，也可以用COUNTA函数来处理，只不过我在演示的时候，第5列之外还有数据，这个引用就不准确了。

这里顺便提一下，COUNTA函数用来计算非空单元格数量，如果源数据是从A1开始，那么正好是我们源数据的行数。

OFFSET函数在数据验证功能中，常用来定义动态下拉列表，也是非常方便、非常实用的，具体可参见前文Excel 公式函数/数据验证/动态下拉列表。

好，今天我们就分享到这里，我们下期再会。

本文于2023年4月6日首发于本人同名公众号：Excel活学活用，更多文章敬请关注！

OFFSET函数常用套路

小伙伴们好啊，今天老祝和大家一起来学习OFFSET函数的常用套路。

1、函数作用：

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

2、函数用法：

=OFFSET(基点,偏移的行数,偏移的列数,[新引用的行数],[新引用的列数])

第二参数使用正数时，表示从基地向下偏移，负数表示向上偏移。

第三参数使用正数时，表示向右偏移，使用负数时表示向左偏移。

第四和第五参数是可选的，如果省略这两个参数，新引用的区域就是和基点一样的大小。

1）行列转置

如下图，要将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 ……这样的偏移方式。

2）计算指定区间的销售额

如下图所示，要计算从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月份到指定月份的销售总额。

3）计算最近7天的平均销量

如下图所示，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函数计算出这个引用区域中的平均值。

4）计算筛选后的商品总价

如下图，是各食堂的采购记录，需要计算筛选后的商品总价。

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函数还经常用于动态图表的制作，这些内容咱们以后继续分享。

图文制作：祝洪忠

Excel｜累进提成，IF望尘莫及，可以用LOOKUP、SUM、OFFSET、MATCH来解决

问题情景

累进销售提成：

按照规定的销售额分级区间，以相应的提成率计算各区间中的提成额，最后进行汇总。

比如销售额为4750，则累进提成计算式为：=1000*1%+1000*1.2%+1000*1.4%+1000*1.6%+750*1.8%=65.5。

举例数据如下：

此示例的问题，只用IF的多重嵌套是解决不了的，因为IF最多只允许有9重嵌套。即使区间没那么多，但IF写出的函数太长太啰嗦，也不是首选。

我们只能另寻他法。

视频讲解

视频加载中…

关键操作添加辅助列

增加“区间最低值”与“区间提成”两个辅助列，区间提成是用1000乘以提成率得来的。如下图：

逐步分析

以B12单元格4750对应的累进提成为例，4750对应的销售额区间为F7的4000～4999，所以对应是提成应该为： C12=(B12－G7)*H7+SUM(I3:I6)

解决三个问题：

• 查找B12对应的区间最低值G7: LOOKUP(B12,$G$3:$G$13)

• 查找G7对应的提成率H7:LOOKUP(B12,$G$3:$G$13,$H$3:$H$13)

• 计算SUM(I3:I6)，I3:I6区域的表示：OFFSET(I3,,,MATCH(B12,$G$3:$G$13,1)-1,)

其中MATCH(B12,$G$3:$G$13,1)，是指在G3:G13区域内查找比B12小的最大值的位次。

C12处的公式可以写为：

=(B12-LOOKUP(B12,$G$3:$G$12))*LOOKUP(B12,$G$3:$G$12,$H$3:$H$12)+SUM(OFFSET($I$3,,,MATCH(B12,$G$3:$G$13,1)-1,))

但，公式向上和向下填充时，会出现错误：

原因是：如果销售额已经小于1000，MATCH(B12,$G$3:$G$13,1)-1的返回值是0。

销售额已经小于1000时，前面再无分级区间对应的提成累进，所以，公式再进一步完善为：

=(B3-LOOKUP(B3,$G$3:$G$13))*LOOKUP(B3,$G$3:$G$13,$H$3:$H$13)+IF(B3<1000,0,SUM(OFFSET($I$3,,,MATCH(B3,$G$3:$G$13,1)-1,)))

至此，累进提成计算完毕：

扩展

此种方法可适用于各种分区间累计的问题，比如累进税率等。

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com