把年月數(shù)變成月數(shù)，很簡單。但只有腦回路大的人才可能寫出更簡單的公式。通常，思路越簡單公式越長。案例中巧用RIGHTB提取位于雙字節(jié)后，字節(jié)變化幅度在1以內(nèi)的單字節(jié)數(shù)據(jù)的思路大家可以多多借鑒。

要求：把工齡x年x月?lián)Q成以月為單位的數(shù)量。沒有入職日期。

好像很簡單，年數(shù)*12+月數(shù)就是結(jié)果。各位可寫出公式后閱讀，看看能否碰出火花來。

1.老函數(shù)+最簡單思路

=IFERROR(LEFT(F2,FIND("年",F2)-1)*12,0)+IFERROR(MID(F2,IFERROR(FIND("年",F2)+1,1),FIND("個(gè)",F2)-IFERROR(FIND("年",F2)+1,1)),0)

公式解析：

最簡單思路——月數(shù)位于“年”“個(gè)”之間。

前半部分提取年數(shù)再*12，后半部分提取月數(shù)。

為了滿足只有年和只有月的情況，公式用了4次IFERROR函數(shù)。

2.老函數(shù)+轉(zhuǎn)個(gè)彎思路

=IFERROR(LEFT(F2,FIND("年",F2)-1)*12,0)+IFERROR(--RIGHTB(LEFT(F2,LEN(F2)-2),2),)

公式解析：

轉(zhuǎn)個(gè)彎思路——去掉最后兩字，月數(shù)位于雙字節(jié)文字后，在最右的2字節(jié)內(nèi)，字節(jié)幅度變化在1以內(nèi)。

LEFT(F2,LEN(F2)-2)把最右邊兩個(gè)字去掉，再用RIGHTB取右邊的兩個(gè)字節(jié)就得到了月數(shù)。

附送一個(gè)新函數(shù)用轉(zhuǎn)彎思路的公式：

=TEXTBEFORE(F2,"年",,,,0)*12+RIGHTB(TEXTBEFORE(F2,"個(gè)",,,,0),2)

3.顛覆性思路

=LOOKUP(9^9,SUBSTITUTE(SUBSTITUTE({"","0年"}&F2,"年"," "),"個(gè)月","/12")*12)

公式解析：

顛覆性思路——把含月的工齡，如4年11個(gè)月，的月份計(jì)算“4*12+11”變成帶分?jǐn)?shù)計(jì)算“（4 11/12）*12；整年的計(jì)算保持不變。

①首先用{"","0年"}&F2得到一個(gè)新數(shù)組。

②用SUBSTITUTE將“年”字替換成空格。

③繼續(xù)用SUBSTITUTE將“個(gè)月”替換成/12。

④將上一步得到的數(shù)組*12，得到如下結(jié)果。

這一步包含三種格式的數(shù)據(jù)計(jì)算。

首先是帶分?jǐn)?shù)計(jì)算。如“4 11/12”“0 8/12”，整數(shù)+空格+分?jǐn)?shù)，它們是帶分?jǐn)?shù)，*12分別得到月份59、8。

其次是純分?jǐn)?shù)表示的日期計(jì)算。如“3/12”，被默認(rèn)為日期，*12得到一個(gè)較大的數(shù)539964。

最后是文本計(jì)算。如“0 4 11/12”“0 5”，*12會返回錯(cuò)誤值。

⑤用LOOKUP在每行中查找一個(gè)極大的數(shù)，得到不為錯(cuò)誤值的最右側(cè)的一個(gè)數(shù)。LOOKUP查找規(guī)則見文末推薦。

不得不說這個(gè)思路真不是一般人能想到的。

碰出你的火花了嗎？歡迎留言分享。

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

做Excel高手，快速提升工作效率，部落窩教育Excel精品好課任你選擇！

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

相關(guān)推薦：

Excel 365新函數(shù)：TEXTBEFORE和TEXTAFTER

LOOKUP查找規(guī)則：一文講透LOOKUP二分查找

從含有多個(gè)數(shù)字的文本中提取特定數(shù)字的方法

Excel跑道圖表——弧形的條形圖

版權(quán)申明：

本文作者老菜鳥；部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。