offset函数能返回一个区域么（引用函数中的极品）

昨天我们介绍了Excel函数家族低调的王者：Index函数。今天我们来聊聊函数家族的红娘：OFFSET函数。为什么此函数能够被称之为“红娘”呢？这是由这个函数本身所干的活所决定的：此函数就是给其他函数或者Excel的其他功能引路搭桥的。我们来看看微软官方的说法吧：

offset函数：以指定的引用为参照系，通过给定偏移量返回新的引用。

这个什么鬼，完全看不懂嘛。下面我来试着图解一下，请看下图：

offset函数图解

offset函数主要用于返回单元格或者单元格区域的引用。它共计有5个参数：

Reference：起始单元格或者单元格区域，又叫基点或者参考系；

Rows：上下偏移的行数，负数向上偏移（不常用），正数向下偏移；

Cols:左右偏移的列数，负数向左偏移（不常用），正数向右偏移；

Height:返回的区域高度，1为1个单元格高度，此参数可省略，省略时，offset返回的引用区域的高度将与Reference区域的高度保持一致；

Width:返回的区域宽度，1为1个单元格宽度，此参数可省略，省略时，offset返回的引用区域的宽度将与Reference区域的宽度保持一致；

例如如上图所示，通过公式：

=offset(B3,3,2,3,4)

offset函数将返回D6:G8这样一个区域。简单了解了一下offset函数的基本参数，下面我们来看看offset函数在职场中的实际运用吧。

一、offset函数轻松提取业绩统计

如下图所示，我们如何快速地完成右侧的表格呢？显然，部分同学会想到使用vlookup函数（大众情人的称号不是白叫的）。因此他们写出的公式估计是这个样子的：

=VLOOKUP(E17,$A$17:$B$51,2,)

这个公式非常简单，也很好理解。但是针对下面的情况就不一定好用了（高手用通配符当然也是分分钟）：

情况稍微复杂一些

这时候我们可以使用offset函数，我们分析一下左侧的表格，发现“年总计”总是出现在四个季度之后，也就是说，假如我们以B16为基点，我们要得到的B21,B26,B31……的值，因此offset第一个参数reference为B16；第二个参数Rows，行偏移为5的1倍，2倍，3倍……，因此我们rows参数可以使用row(1:1)*5；第三个参数列偏移，我们的基点在B16单元格，要返回的单元格也在B列，因此无需偏移，因此这个参数留出位置即可；因为我们要返回的单元格均为1个单元格，第4个和第5个参数直接省略即可。最终公式如下：

=OFFSET(B$16,ROW(1:1)*5,)

offset函数基础应用

这只是offset函数的小试牛刀。或许很多朋友还是会疑惑，就这点本事还不值得我去学习他啊。是的，如果就那么点本事的确没有必要学习。然而，offset函数要远比我们想象得到的强大的多。无论是当其他函数的红娘（主营业务）还是和数据验证配合一起创建动态二级下拉列表或者和名称功能创建动态图表，我们随处都可见offset函数的身影。继续看下面的实例。

二、offset函数甘当其他函数的“红娘”--案例一

如下图所示，我们需要快速地得到最高月平均工资，该如何做呢？

求最高月平均工资

很多朋友遇到此类问题，通常选择添加辅助列解决，在H列新建一个辅助列：月平均工资，然后用average算出每个人的月平均工资，再用max函数得到最高的月平均工资，如下图所示：

最简单的辅助列

的确，用这种办法确实挺简单实用的。然而在实际工作中，某些固定好的表格是不允许调整表格结构的，遇到这种情况通过以上的方法就行不通了。这时候一定要想到offset函数，在很多情况下它是可以完全取代辅助列的。思路和使用辅助列的思路一样，我们首先要引用到每个人的所有月工资数据，然后算出每个人的月平均工资，最后再去求最大值。

首先我们如何快速得到每个人的月工资数据呢？

我们知道月工资在B:G列，因此我们的基点为B56:G56；

每多偏移一行就能得到一个人的工资数据，行偏移为1时，得到张三的月工资数据（B57:G57）,行偏移为2时，得到李四的月工资数据（B58:G58）……，我们需要分别得到张三到重八8个人的数据，因此第二参数为：{1,2,3,4,5,,6,7,8}，我们也可以通过row(1:8)得到这样一个参数；

第三个参数列偏移为0，第4个和第5个参数可以省略；

因此返回每个人月工资数据的公式为：=offset(B56:G56,row(1:8),)

然后如何得到每个人的月平均工资呢？

由于offset函数得到的将会是一个多维数组，显然我们使用average函数没法得到多个人的月平均工资。这里我们可以使用subtotal函数来做。公式为：=subtotal(1,offset(B56:G56,row(1:8),)) 。这个函数我不多作解释，有兴趣的朋友可以参阅我写的subtotal函数的教程Subtotal函数：各种简单统计，用我就够了。

最后，我们可以利用max函数快速得到月平均工资最高值。最终公式如下：

{=MAX(SUBTOTAL(1,OFFSET(B56:G56,ROW(1:8),)))}

最终效果图

三、offset函数甘当其他函数的“红娘”--案例二

还是刚才的表格，不过咱们的需求变了一下：我们需要快速求出6个月中有两个月以上工资不到1000的人数有多少，这个怎么来解决呢？

求人数

我们先来理一下思路，跟上面一个案例一样，我们要统计2个月以上工资不到1000的人数，首先我们得要引用到每个人的工资数据，这个跟案例一样；第二我们要统计每个人六个月的工资中，有几个月的工资是低于1000的，这个我们可以countif函数来解决；第三我们要判断一下哪些人超过1个月的工资是低于1000的，最后我们来统计人数。下面是每一步的公式：

Step1：求每个人的工资数据

=offset(B56:G56,row(1:8),)

Step2：统计每个人月工资低于1000的月份数

=countif(offset(B56:G56,row(1:8),),"<1000")

Step3：判断每个人低于1000的月份数是否超过1个月

=--(countif(offset(B56:G56,row(1:8),),"<1000")>1)

Step4：统计人数

{=sum(--(countif(offset(B56:G56,row(1:8),),"<1000")>1)))}

最终结果：

{=SUM(--(COUNTIF(OFFSET(B56:G56,ROW(1:8),),"<1000")>1))}

最终效果图

关于offset函数 ，今天就先分享到这里，更多内容请持续关注：傲看今朝。

,