編按：

哈嘍，大家好，今天給大家講解一個(gè)比較常用，但是又不那么容易解決的問(wèn)題——計(jì)算累計(jì)達(dá)到某考核指標(biāo)的月份。這里會(huì)用到OFFSET函數(shù)來(lái)構(gòu)建數(shù)組，然后再用SUBTOTAL函數(shù)對(duì)月銷(xiāo)售數(shù)據(jù)組求和，最后還會(huì)用到LOOKUP函數(shù)，還不會(huì)的同學(xué)，趕緊來(lái)看看吧！

案例數(shù)據(jù)如下圖所示。其中，A列中的數(shù)據(jù)為員工姓名，B列至M列中的數(shù)據(jù)是每名員工在1-12月份某產(chǎn)品的銷(xiāo)售數(shù)量?，F(xiàn)在，想要計(jì)算每名員工累計(jì)銷(xiāo)量達(dá)到100時(shí)所在的月份。

今天用到的“神級(jí)”函數(shù)比較多，請(qǐng)大家提前做好燒腦準(zhǔn)備！

Step1  利用OFFSET函數(shù)構(gòu)建月銷(xiāo)售數(shù)據(jù)數(shù)組

首先，我們?cè)?span>N2中輸入“=OFFSET(B2,0,0,1,13-ROW($1:$12))”，然后向下復(fù)制填充公式，得到的結(jié)果如下圖所示。

可以看到，函數(shù)返回了“#VALUE!”錯(cuò)誤，這個(gè)錯(cuò)誤我們暫時(shí)不用理會(huì)它。因?yàn)樵谙乱徊降挠?jì)算中，我們就可以把它給消除了。下面，我們重點(diǎn)看一下這個(gè)OFFSET函數(shù)的參數(shù)及它的作用。

【函數(shù)釋義】

1.我們知道，OFFSET函數(shù)又稱為“跑馬圈地”函數(shù)，它可以獲取一個(gè)區(qū)域作為函數(shù)的返回值。此處，我們寫(xiě)的OFFSET函數(shù)，其第一參數(shù)是B2，意思是以B2單元格作為起點(diǎn)；其第二參數(shù)、第三參數(shù)均為0，意思是向下和向右的偏移量均為0，偏移之后得到的新的起點(diǎn)還是B2；OFFSET函數(shù)的第四參數(shù)用來(lái)指定返回值的行數(shù)，此處為1，意思是返回的數(shù)據(jù)行數(shù)是1行，即返回第二行（員工1）的相關(guān)數(shù)據(jù)，。

2.OFFSET函數(shù)的第五參數(shù)用來(lái)指定返回值的列數(shù)，此處為13-ROW($1:$12)。這是什么意思呢？我們先看ROW($1:$12)這一部分，它是用來(lái)產(chǎn)生一個(gè)1至12的序列（代表1至12月）并且構(gòu)成一個(gè)月份值數(shù)組 “{1;2;3;4;5;6;7;8;9;10;11;12}”；13-ROW($1:$12)的作用，是將月份值數(shù)組進(jìn)行降序排列，即此時(shí)數(shù)組變成“{12;11;10;9;8;7;6;5;4;3;2;1}”。

3.綜上，OFFSET函數(shù)在此處的作用，就是依次返回從B2單元格開(kāi)始，然后向右數(shù)12列、11列、10列、…、2列、1列的數(shù)據(jù)區(qū)域，并將這些區(qū)域構(gòu)成一個(gè)新的月銷(xiāo)售數(shù)據(jù)數(shù)組。這個(gè)月銷(xiāo)售數(shù)據(jù)數(shù)組中的數(shù)據(jù)分別是:

{11,34,29,23,12,16,18,4,10,18,9,3}（返回12列數(shù)據(jù)）；

{11,34,29,23,12,16,18,4,10,18,9}（返回11列數(shù)據(jù)）；

{11,34,29,23,12,16,18,4,10,18}（返回10列數(shù)據(jù)）；

……

{11,34}（返回2列數(shù)據(jù)）；

{11}（返回1列數(shù)據(jù)）。

所以，N2中的數(shù)據(jù)實(shí)際上是：

{{11,34,29,23,12,16,18,4,10,18,9,3}；

{11,34,29,23,12,16,18,4,10,18,9}；

{11,34,29,23,12,16,18,4,10,18}；

……

{11,34};

{11}}

（注：這是一個(gè)二維數(shù)組，小伙伴們知道即可，以后有機(jī)會(huì)再單獨(dú)講。）

二、利用SUBTOTAL函數(shù)對(duì)月銷(xiāo)售數(shù)據(jù)數(shù)組求和

我們將N2中的函數(shù)修改為“=SUBTOTAL(9,OFFSET(B2,0,0,1,13-ROW($1:$12)))”，然后向下復(fù)制填充公式，得到的結(jié)果如下圖所示。

可以看到，N2:N6單元格中現(xiàn)在均有數(shù)據(jù)顯示,我們上一步寫(xiě)的OFFSET函數(shù)所產(chǎn)生的“#VALUE!”錯(cuò)誤也消失了。我們可以用鼠標(biāo)單擊N2單元格，然后在函數(shù)欄中選中公式，之后再按下F9鍵查看N2中的具體內(nèi)容?？梢钥吹?，N2中的數(shù)據(jù)是一個(gè)數(shù)組，其中的數(shù)據(jù)為“{187;184;175;157;147;143;125;109;97;74;45;11}”，如下圖所示。

【函數(shù)釋義】

SUBTOTAL是一個(gè)綜合性函數(shù)，當(dāng)它的第一參數(shù)為9的時(shí)候，意味著使用其分類(lèi)求和功能。此處，我們就是用SUBTOTAL函數(shù)對(duì)上一步計(jì)算中我們得到的二維數(shù)組中的每一個(gè)一維數(shù)組分別進(jìn)行求和。以員工1 為例，月份、OFFSET函數(shù)、SUBTOTAL函數(shù)的對(duì)應(yīng)關(guān)系如下圖所示。

三、用考核指標(biāo)做減法，構(gòu)建升序差值數(shù)組

我們將N2中的函數(shù)修改為“=100-SUBTOTAL(9,OFFSET(B3,0,0,1,13-ROW($1:$12)))”，得到的結(jié)果如下圖所示。

現(xiàn)在，讓我們?cè)俅瓮ㄟ^(guò)F9鍵查看N2中的內(nèi)容?？梢钥吹?，N2中的數(shù)據(jù)是“{-87;-84;-75;-57;-47;-43;-25;-9;3;26;55;89}”。請(qǐng)大家注意，這是一個(gè)由升序排列的差值所構(gòu)成的一維數(shù)組，如下圖所示。

【函數(shù)釋義】

在本例中，由于考核指標(biāo)是100，所以我們用100去減。實(shí)際應(yīng)用中，考核指標(biāo)是多少，我們用這個(gè)數(shù)去減SUBTOTAL函數(shù)就可以啦。經(jīng)過(guò)相減，就可以把

我們之前的得到的月銷(xiāo)量求和的值由降序排變?yōu)樯蚺帕校ㄗⅲ簲?shù)據(jù)的值有所變化，但無(wú)礙，因?yàn)槲覀兒罄m(xù)要用到的是數(shù)據(jù)的次序而非實(shí)際的數(shù)據(jù)值）。

四、使用LOOKUP函數(shù)匹配月份

我們將N2中的函數(shù)修改為“=LOOKUP(0,100-SUBTOTAL(9,OFFSET(B2,0,0,1,13-ROW($1:$12))),13-ROW($1:$12))”，得到的結(jié)果如下圖所示。

【函數(shù)釋義】

1、對(duì)于LOOKUP函數(shù)來(lái)說(shuō)，必須先對(duì)第2參數(shù)（也就是查找值數(shù)組）進(jìn)行升序排列，這就是上一步我們構(gòu)建升序差值數(shù)組的原因所在。

2、LOOKUP函數(shù)的第1參數(shù)是它的查找值，根據(jù)LOOKUP函數(shù)的運(yùn)算規(guī)則，它優(yōu)先精確查找第1參數(shù)，如果查找的數(shù)據(jù)中存在這個(gè)值，則返回對(duì)應(yīng)的值（由第3參數(shù)而定）；如果查找的數(shù)據(jù)中沒(méi)有這個(gè)值，那么將查找小于目標(biāo)值的最大值。在本例中，以員工1為例，他的100-SUBTOTAL構(gòu)成的升序差值數(shù)組中沒(méi)有0，那么小于0的最大值就是-9。

3、LOOKUP函數(shù)的第3參數(shù)，是函數(shù)的返回值數(shù)組。此處，我們要注意，在構(gòu)建第三參數(shù)的時(shí)候，月份是倒序的，只有這樣，才能與100-SUBTOTAL中的值以及月份值一一對(duì)應(yīng)，具體的對(duì)應(yīng)關(guān)系如下圖所示。

可以看到，由于在數(shù)據(jù)區(qū)域中找不到0這個(gè)值，所以LOOKUP函數(shù)就去查找小于0的最大值，此處即為-9，而-9對(duì)應(yīng)的月份數(shù)為5，所以，函數(shù)的返回值就是5。因此，可以判斷出，員工1在5月份的時(shí)候銷(xiāo)售任務(wù)累計(jì)達(dá)到了100。

五、修正并完善函數(shù)

經(jīng)過(guò)上一步的LOOKUP函數(shù)的計(jì)算，我們可以看到，員工1、2、3、4的達(dá)標(biāo)月份均已經(jīng)計(jì)算出來(lái)了，而員工5的達(dá)標(biāo)月份出現(xiàn)了“#N/A”。通過(guò)手工計(jì)算，我們可以發(fā)現(xiàn)，員工5全年的總銷(xiāo)售數(shù)量為99，是未達(dá)標(biāo)的。另外，從函數(shù)的返回結(jié)果我們可以看到，員工5的升序差值數(shù)組（100-SUBTOTAL）為“{1;9;11;17;22;25;32;49;67;79;87;94}”（可通過(guò)F9鍵查看），沒(méi)有任何小于0的值，所以導(dǎo)致出現(xiàn)了“#N/A”的錯(cuò)誤。

原因我們找到了，接下來(lái)，我們?cè)儆?span>IFERROR函數(shù)來(lái)做一下修正就行了。我們將N2中的公式修改為“=IFERROR(LOOKUP(0,100-SUBTOTAL(9,OFFSET(B2,0,0,1,13-ROW($1:$12))),13-ROW($1:$12)),"未達(dá)標(biāo)") ”，得到的結(jié)果如下圖所示。

另外，如果小伙伴們想在月份的數(shù)值之后顯示“月”字，則只需在LOOKUP函數(shù)的后面加上一個(gè)連接函數(shù)就行。我們將N2中的公式修改為“=IFERROR(LOOKUP(0,100-SUBTOTAL(9,OFFSET(B2,0,0,1,13-ROW($1:$12))),13-ROW($1:$12))&"月","未達(dá)標(biāo)") ”，然后向下復(fù)制填充，得到的結(jié)果如下圖所示。

好了，今天的內(nèi)容就是這些，你學(xué)會(huì)了嗎？

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車(chē)》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

將excel中的數(shù)據(jù)按照指定數(shù)量進(jìn)行重復(fù)，非得用VBA才能實(shí)現(xiàn)嗎？

如何在特定位置批量插入空行等12種實(shí)用辦公技巧

4種刪除excel重復(fù)值的小妙招，速收藏

Power Query的數(shù)據(jù)替換技巧比Excel函數(shù)更萬(wàn)能！

版權(quán)申明：

本文作者阿碩；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。