EXCEL图文教程>如何计算员工累计达标的月份，你需要SUBTOTAL函数

编按：哈喽，大家好，今天给大家讲解一个比较常用，但是又不那么容易解决的问题——计算累计达到某考核指标的月份。这里会用到OFFSET函数来构建数组，然后再用SUBTOTAL函数对月销售数据组求和，最后还会用到LOOKUP函数，还不会的同学，赶紧来看看吧！

案例数据如下图所示。其中，A列中的数据为员工姓名，B列至M列中的数据是每名员工在1-12月份某产品的销售数量。现在，想要计算每名员工累计销量达到100时所在的月份。

今天用到的“神级”函数比较多，请大家提前做好烧脑准备！

Step1  利用OFFSET函数构建月销售数据数组

首先，我们在N2中输入“=OFFSET(B2,0,0,1,13-ROW($1:$12))”，然后向下复制填充公式，得到的结果如下图所示。

可以看到，函数返回了“#VALUE!”错误，这个错误我们暂时不用理会它。因为在下一步的计算中，我们就可以把它给消除了。下面，我们重点看一下这个OFFSET函数的参数及它的作用。

【函数释义】

1.我们知道，OFFSET函数又称为“跑马圈地”函数，它可以获取一个区域作为函数的返回值。此处，我们写的OFFSET函数，其第一参数是B2，意思是以B2单元格作为起点；其第二参数、第三参数均为0，意思是向下和向右的偏移量均为0，偏移之后得到的新的起点还是B2；OFFSET函数的第四参数用来指定返回值的行数，此处为1，意思是返回的数据行数是1行，即返回第二行（员工1）的相关数据，。

2.OFFSET函数的第五参数用来指定返回值的列数，此处为13-ROW($1:$12)。这是什么意思呢？我们先看ROW($1:$12)这一部分，它是用来产生一个1至12的序列（代表1至12月）并且构成一个月份值数组 “”；13-ROW($1:$12)的作用，是将月份值数组进行降序排列，即此时数组变成“”。

3.综上，OFFSET函数在此处的作用，就是依次返回从B2单元格开始，然后向右数12列、11列、10列、…、2列、1列的数据区域，并将这些区域构成一个新的月销售数据数组。这个月销售数据数组中的数据分别是:

（返回12列数据）；

（返回11列数据）；

（返回10列数据）；

……

（返回2列数据）；

（返回1列数据）。

所以，N2中的数据实际上是：

{；

；

；

……

;

}

（注：这是一个二维数组，小伙伴们知道即可，以后有机会再单独讲。）

二、利用SUBTOTAL函数对月销售数据数组求和

我们将N2中的函数修改为“=SUBTOTAL(9,OFFSET(B2,0,0,1,13-ROW($1:$12)))”，然后向下复制填充公式，得到的结果如下图所示。

可以看到，N2:N6单元格中现在均有数据显示,我们上一步写的OFFSET函数所产生的“#VALUE!”错误也消失了。我们可以用鼠标单击N2单元格，然后在函数栏中选中公式，之后再按下F9键查看N2中的具体内容。可以看到，N2中的数据是一个数组，其中的数据为“”，如下图所示。

【函数释义】

SUBTOTAL是一个综合性函数，当它的第一参数为9的时候，意味着使用其分类求和功能。此处，我们就是用SUBTOTAL函数对上一步计算中我们得到的二维数组中的每一个一维数组分别进行求和。以员工1 为例，月份、OFFSET函数、SUBTOTAL函数的对应关系如下图所示。

三、用考核指标做减法，构建升序差值数组

我们将N2中的函数修改为“=100-SUBTOTAL(9,OFFSET(B3,0,0,1,13-ROW($1:$12)))”，得到的结果如下图所示。

现在，让我们再次通过F9键查看N2中的内容。可以看到，N2中的数据是“{-87;-84;-75;-57;-47;-43;-25;-9;3;26;55;89}”。请大家注意，这是一个由升序排列的差值所构成的一维数组，如下图所示。

【函数释义】

在本例中，由于考核指标是100，所以我们用100去减。实际应用中，考核指标是多少，我们用这个数去减SUBTOTAL函数就可以啦。经过相减，就可以把

我们之前的得到的月销量求和的值由降序排变为升序排列（注：数据的值有所变化，但无碍，因为我们后续要用到的是数据的次序而非实际的数据值）。

四、使用LOOKUP函数匹配月份

我们将N2中的函数修改为“=LOOKUP(0,100-SUBTOTAL(9,OFFSET(B2,0,0,1,13-ROW($1:$12))),13-ROW($1:$12))”，得到的结果如下图所示。

【函数释义】

1、对于LOOKUP函数来说，必须先对第2参数（也就是查找值数组）进行升序排列，这就是上一步我们构建升序差值数组的原因所在。

2、LOOKUP函数的第1参数是它的查找值，根据LOOKUP函数的运算规则，它优先精确查找第1参数，如果查找的数据中存在这个值，则返回对应的值（由第3参数而定）；如果查找的数据中没有这个值，那么将查找小于目标值的最大值。在本例中，以员工1为例，他的100-SUBTOTAL构成的升序差值数组中没有0，那么小于0的最大值就是-9。

3、LOOKUP函数的第3参数，是函数的返回值数组。此处，我们要注意，在构建第三参数的时候，月份是倒序的，只有这样，才能与100-SUBTOTAL中的值以及月份值一一对应，具体的对应关系如下图所示。

可以看到，由于在数据区域中找不到0这个值，所以LOOKUP函数就去查找小于0的最大值，此处即为-9，而-9对应的月份数为5，所以，函数的返回值就是5。因此，可以判断出，员工1在5月份的时候销售任务累计达到了100。

五、修正并完善函数

经过上一步的LOOKUP函数的计算，我们可以看到，员工1、2、3、4的达标月份均已经计算出来了，而员工5的达标月份出现了“#N/A”。通过手工计算，我们可以发现，员工5全年的总销售数量为99，是未达标的。另外，从函数的返回结果我们可以看到，员工5的升序差值数组（100-SUBTOTAL）为“”（可通过F9键查看），没有任何小于0的值，所以导致出现了“#N/A”的错误。

原因我们找到了，接下来，我们再用IFERROR函数来做一下修正就行了。我们将N2中的公式修改为“=IFERROR(LOOKUP(0,100-SUBTOTAL(9,OFFSET(B2,0,0,1,13-ROW($1:$12))),13-ROW($1:$12)),"未达标") ”，得到的结果如下图所示。

另外，如果小伙伴们想在月份的数值之后显示“月”字，则只需在LOOKUP函数的后面加上一个连接函数就行。我们将N2中的公式修改为“=IFERROR(LOOKUP(0,100-SUBTOTAL(9,OFFSET(B2,0,0,1,13-ROW($1:$12))),13-ROW($1:$12))&"月","未达标") ”，然后向下复制填充，得到的结果如下图所示。

好了，今天的内容就是这些，你学会了吗？

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫相关推荐：

将excel中的数据按照指定数量进行重复，非得用VBA才能实现吗？

如何在特定位置批量插入空行等12种实用办公技巧

4种删除excel重复值的小妙招，速收藏

Power Query的数据替换技巧比Excel函数更万能！

版权申明：

本文作者阿硕；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。