TEXT和SUMPRODUCT強(qiáng)強(qiáng)聯(lián)合,只為解決一個(gè)“微不足道”的編號(hào)問題??
?
作者:老菜鳥來源:部落窩教育發(fā)布時(shí)間:2020-04-29 17:33:32點(diǎn)擊:5079
編按:
哈嘍,大家好!最近小編收到一位群友的求助,他說自己被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ù),為了便于理解,我們對(duì)在同一個(gè)月中多次生產(chǎn)的商品用不同顏色標(biāo)注出來,之后再看就清楚了。
以丹參為例,雖然一共出現(xiàn)了四次,但是在4月份只有三次,因此對(duì)應(yīng)的批號(hào)分別為200401-200403,所以這個(gè)問題的本質(zhì)還是編號(hào)。
搞清楚了這一點(diǎn),我們?cè)賮矸治鰡栴}該如何解決。
正如前面分析的,批號(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中,只對(duì)區(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")是對(duì)日期(年月)進(jìn)行判斷,$B$2:B2=B2是對(duì)品名進(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è)問題對(duì)于學(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ù)中對(duì)于區(qū)域的要求,這一點(diǎn)算是一個(gè)隱藏的知識(shí)點(diǎn)吧。
學(xué)習(xí)函數(shù)就是這樣,對(duì)于基本用法了解之后,還需要不斷地去了解更多細(xì)節(jié)方面的知識(shí)點(diǎn),最終才能實(shí)現(xiàn)活學(xué)活用,關(guān)于今天的內(nèi)容,你有什么想法和收獲,歡迎留言和大家一同交流。
本文配套的練習(xí)課件請(qǐng)加入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的常用套路》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!