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

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

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

最后按分隔符號+分列。

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

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

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

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

=TEXTSPLIT(O2,"+")

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

=--TEXTSPLIT(O2,"+")

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

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

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

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

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

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

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

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

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

漏打卡前面用""表示，直接運算就返回錯誤值，可以套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ǔ)充說明，下班漏打卡，這里如果寫上9:00，后期寫公式會更方便。

上下班時間完整，而右邊有對應(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)

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

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

SUBSTITUTES語法：

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

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

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

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

TEXTSPLIT語法：

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

按列拆分：

=TEXTSPLIT(A1,"、")

按行拆分：

=TEXTSPLIT(A1,,"、")