編按：

哈嘍，大家好！最近小編收到一位群友的求助，他說自己被excel中的編號(hào)問題給難住了。這是這么回事呢？編號(hào)不就是1、2、3、4、5嗎，直接下拉單元格就能搞定，這有何難？一起來看看下面這篇文章，你就明白了！

近日看到一個(gè)群友的求助，覺得比較有意思，想和大家分享一下。

這是一個(gè)看似普通的編號(hào)問題，可竟然動(dòng)用了TEXT和SUMPRODUCT兩個(gè)重量級(jí)的函數(shù)共同出手才得以解決。

以往遇到的編號(hào)問題，大多數(shù)都是COUNTIF的拿手好戲，但是今天這個(gè)問題COUNTIF完全插不上手，來看看模擬的數(shù)據(jù)吧。

如圖所示，C列的批號(hào)要求用公式生成，說是批號(hào)，其實(shí)就是一個(gè)編號(hào)的問題，為什么這么說，還得從這個(gè)批號(hào)的規(guī)律來解釋。

大家仔細(xì)觀察一下就不難發(fā)現(xiàn)，在這個(gè)六位的批號(hào)中，其實(shí)是由兩部分組成的，左邊四位是生產(chǎn)日期的年和月，右邊兩位就是該產(chǎn)品在同一個(gè)月內(nèi)生產(chǎn)的次數(shù)，為了便于理解，我們對在同一個(gè)月中多次生產(chǎn)的商品用不同顏色標(biāo)注出來，之后再看就清楚了。

以丹參為例，雖然一共出現(xiàn)了四次，但是在4月份只有三次，因此對應(yīng)的批號(hào)分別為200401-200403，所以這個(gè)問題的本質(zhì)還是編號(hào)。

搞清楚了這一點(diǎn)，我們再來分析問題該如何解決。

正如前面分析的，批號(hào)是由兩部分組成的，第一部分很容易，可以直接用TEXT函數(shù)從生產(chǎn)日期中得到，公式為：TEXT(A2,"yymm")。

TEXT函數(shù)的教程之前分享過很多篇，不再細(xì)說了，公式中的"yymm"表示將日期按照兩位年兩位月的格式顯示結(jié)果。

問題的難點(diǎn)在于第二部分，同一個(gè)月內(nèi)出現(xiàn)的次數(shù)，如果有一個(gè)輔助列的話，COUNTIFS就可以輕松解決，公式為：=COUNTIFS($D$2:D2,D2,$B$2:B2,B2)

公式中有兩個(gè)條件，日期（其實(shí)是年月）和品名，關(guān)于COUNTIFS的用法，可以參閱往期教程《同樣是countifs函數(shù)，為什么同事卻使得比你好？原因在這里！》，這里要重點(diǎn)說明的是條件區(qū)域的寫法，$D$2:D2和$B$2:B2中，只對區(qū)域的起始單元格鎖定，這樣得到的就是累計(jì)多條件計(jì)數(shù)的結(jié)果。

如果條件區(qū)域是整列的話，得到的次數(shù)就不是累計(jì)的，而是最終出現(xiàn)的總次數(shù)，通過上圖中的結(jié)果很容易搞明白這一點(diǎn)。

現(xiàn)在的問題是，如果沒有這個(gè)輔助列，還能用COUNTIFS嗎？

答案是不行！

因?yàn)?span>COUNTIFS的特點(diǎn)就是條件區(qū)域只能使用單元格區(qū)域，而不能使用其他公式。

如果要使用公式作為條件區(qū)域的話，會(huì)彈出一個(gè)提示框：

同樣的情況在COUNTIF和SUMIF、SUMIFS中都是類似的，只能使用單元格區(qū)域，這一點(diǎn)很重要。

因此，如果要在不使用輔助列的情況下解決這個(gè)問題，就必須用到SUMPRODUCT函數(shù)。

公式看起來會(huì)稍微有點(diǎn)長，=SUMPRODUCT((TEXT($A$2:A2,"yymm")=TEXT(A2,"yymm"))*($B$2:B2=B2))

這個(gè)公式就是在沒有輔助列的情況下實(shí)現(xiàn)了多條件的累計(jì)計(jì)數(shù)，公式中的TEXT($A$2:A2,"yymm")=TEXT(A2,"yymm")是對日期（年月）進(jìn)行判斷，$B$2:B2=B2是對品名進(jìn)行判斷，分別得到兩組邏輯值，兩組邏輯值相乘后由SUMPRODUCT函數(shù)相加就能得到計(jì)數(shù)結(jié)果。

這個(gè)函數(shù)的用法可以參考之前的教程《加了*的 SUMPRODUCT函數(shù)無所不能》。

至此，最終的公式也就呼之欲出了，第一部分由TEXT得到年月，第二部分如果直接使用SUMPRODUCT得到的計(jì)數(shù)結(jié)果還不行，因?yàn)橛?jì)算結(jié)果必須是兩位數(shù)，如果不足兩位的要在前面補(bǔ)零，這就還得用到TEXT函數(shù)，這種用法的格式是TEXT(要處理的數(shù)字, "00")，有幾個(gè)0就表示得到的結(jié)果是幾位數(shù)，因此最終的公式就是：

=TEXT(A2,"yymm")&TEXT(SUMPRODUCT((TEXT($A$2:A2,"yymm")=TEXT(A2,"yymm"))*($B$2:B2=B2)),"00")

結(jié)束語：今天這個(gè)問題不是很常見，但是這個(gè)問題對于學(xué)習(xí)公式函數(shù)是很有價(jià)值的，公式中包含了很多重要的知識(shí)點(diǎn)，總結(jié)如下。

1.TEXT函數(shù)中日期代碼y和m的用法；

2.TEXT函數(shù)中關(guān)于指定數(shù)字位數(shù)的用法；

3.SUMPRODUCT函數(shù)實(shí)現(xiàn)多條件累計(jì)計(jì)數(shù)的用法，重點(diǎn)是條件區(qū)域中$符號(hào)的用法；

4.COUNTIF(S)、SUMIF(S)等函數(shù)中對于區(qū)域的要求，這一點(diǎn)算是一個(gè)隱藏的知識(shí)點(diǎn)吧。

學(xué)習(xí)函數(shù)就是這樣，對于基本用法了解之后，還需要不斷地去了解更多細(xì)節(jié)方面的知識(shí)點(diǎn)，最終才能實(shí)現(xiàn)活學(xué)活用，關(guān)于今天的內(nèi)容，你有什么想法和收獲，歡迎留言和大家一同交流。

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

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

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

相關(guān)推薦：

Excel中SUMPRODUCT函數(shù)的用法《加了*的 SUMPRODUCT函數(shù)無所不能》

Excel中countifs函數(shù)的用法《同樣是countifs函數(shù)，為什么同事卻使得比你好？原因在這里！》

Excel中SUM函數(shù)的應(yīng)用《SUM函數(shù)特殊應(yīng)用：序號(hào)填充、提成比例計(jì)算、打印標(biāo)簽制作》

Excel中TEXT函數(shù)的應(yīng)用《5分鐘，學(xué)會(huì)文本函數(shù)之王——TEXT的常用套路》