VIP學(xué)員的問(wèn)題，要從B列的打卡時(shí)間里面提取出上班和下班的時(shí)間，同時(shí)找到對(duì)應(yīng)的補(bǔ)助費(fèi)用。

打卡時(shí)間是以+作為分隔符號(hào)，還含有4種無(wú)關(guān)的符號(hào)[、]、 (空格)、-。問(wèn)題不難，只是用傳統(tǒng)方法，步驟比較多而已。

按快捷鍵Ctrl+H，進(jìn)行4次查找替換，從[開始全部替換，再重復(fù)替換其他符號(hào)。

最后按分隔符號(hào)+分列。

傳統(tǒng)方法知道有這么回事就行，現(xiàn)在主要學(xué)習(xí)新函數(shù)的用法。

新函數(shù)SUBSTITUTES，比傳統(tǒng)替換函數(shù)多了一個(gè)S哦，別搞錯(cuò)，可以實(shí)現(xiàn)多個(gè)符號(hào)同時(shí)替換掉。{"符號(hào)1","符號(hào)2","符號(hào)3","符號(hào)4"}，有多少符號(hào)都可以一次寫進(jìn)去，再用{ }括起來(lái)。

=SUBSTITUTES(B2,{"[","]"," ","-"},)

按+分列，可以用新函數(shù)TEXTSPLIT。

=TEXTSPLIT(O2,"+")

分開后上下班的時(shí)間是文本格式，再用--轉(zhuǎn)換成數(shù)值格式，再設(shè)置單元格為時(shí)間格式。

=--TEXTSPLIT(O2,"+")

只有一個(gè)打卡時(shí)間轉(zhuǎn)換后會(huì)出現(xiàn)錯(cuò)誤值，嵌套IFERROR函數(shù)讓錯(cuò)誤值顯示空白。

=IFERROR(--TEXTSPLIT(O2,"+"),"")

最后，全部函數(shù)組合起來(lái)，就可以提取上下班時(shí)間。

=IFERROR(--TEXTSPLIT(SUBSTITUTES(B2,{"[","]"," ","-"},),"+"),"")

補(bǔ)助的話，用IF、AND這2個(gè)函數(shù)就可以解決了。

早餐只要是上班8點(diǎn)前的就補(bǔ)助4元。

=IF(C2-"8:00"<=0,4,0)

中餐只要是上班8點(diǎn)前，下班17:30就補(bǔ)助10元。同時(shí)滿足就用AND。

=IF(AND(C2-"8:00"<=0,D2-"17:30">=0),10,0)

漏打卡前面用""表示，直接運(yùn)算就返回錯(cuò)誤值，可以套N變成0。

=IF(AND(C2-"8:00"<=0,N(D2)-"17:30">=0),10,0)

晚餐補(bǔ)助，將17:30改成19:00就可以。

=IF(AND(C2-"8:00"<=0,N(D2)-"19:00">=0),10,0)

最后，再補(bǔ)充說(shuō)明，下班漏打卡，這里如果寫上9:00，后期寫公式會(huì)更方便。

上下班時(shí)間完整，而右邊有對(duì)應(yīng)表，直接就可以用LOOKUP函數(shù)查找。查找區(qū)域$J$2:$J$4固定死，返回區(qū)域K$2:K$4列字母不鎖定，這樣右拉就變成L$2:L$4、M$2:M$4，從而可以依次返回所有列。

=IF($C2-"8:00"<=0,LOOKUP($D2,$J$2:$J$4,K$2:K$4),0)

考勤之所以麻煩，在于每個(gè)公司的規(guī)則都不一樣，每個(gè)公司的打卡記錄都不相同，這樣相當(dāng)于全部公式都得從頭開始設(shè)置，很難找到可以借鑒的案例。

新函數(shù)案例補(bǔ)充說(shuō)明。

SUBSTITUTES語(yǔ)法：

=SUBSTITUTES(文本,舊內(nèi)容,新內(nèi)容)

要替換的內(nèi)容，可以是一個(gè)，也可以是多個(gè)，比如將八段錦替換成八部金剛功。

=SUBSTITUTES(A1,"八段錦","八部金剛功")

=SUBSTITUTES(A1,{"八段錦","。"},{"八部金剛功","！"})

TEXTSPLIT語(yǔ)法：

=TEXTSPLIT(文本,按列拆分,按行拆分)

按列拆分：

=TEXTSPLIT(A1,"、")

按行拆分：

=TEXTSPLIT(A1,,"、")