【原】TEXTSPLIT+SUBSTITUTES处理考勤真的是绝配，再也找不到这么好用的函数组合！

与 30万 粉丝一起学Excel

VIP学员的问题，要从B列的打卡时间里面提取出上班和下班的时间，同时找到对应的补助费用。

打卡时间是以+作为分隔符号，还含有4种无关的符号[、]、 (空格)、-。问题不难，只是用传统方法，步骤比较多而已。

按快捷键Ctrl+H，进行4次查找替换，从[开始全部替换，再重复替换其他符号。

最后按分隔符号+分列。

传统方法知道有这么回事就行，现在主要学习新函数的用法。

新函数SUBSTITUTES，比传统替换函数多了一个S哦，别搞错，可以实现多个符号同时替换掉。{"符号1","符号2","符号3","符号4"}，有多少符号都可以一次写进去，再用{ }括起来。

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

按+分列，可以用新函数TEXTSPLIT。

=TEXTSPLIT(O2,"+")

分开后上下班的时间是文本格式，再用--转换成数值格式，再设置单元格为时间格式。

=--TEXTSPLIT(O2,"+")

只有一个打卡时间转换后会出现错误值，嵌套IFERROR函数让错误值显示空白。

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

最后，全部函数组合起来，就可以提取上下班时间。

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

补助的话，用IF、AND这2个函数就可以解决了。

早餐只要是上班8点前的就补助4元。

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

中餐只要是上班8点前，下班17:30就补助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)

晚餐补助，将17:30改成19:00就可以。

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

最后，再补充说明，下班漏打卡，这里如果写上9:00，后期写公式会更方便。

上下班时间完整，而右边有对应表，直接就可以用LOOKUP函数查找。查找区域$J$2:$J$4固定死，返回区域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)

考勤之所以麻烦，在于每个公司的规则都不一样，每个公司的打卡记录都不相同，这样相当于全部公式都得从头开始设置，很难找到可以借鉴的案例。

新函数案例补充说明。

SUBSTITUTES语法：

=SUBSTITUTES(文本,旧内容,新内容)

要替换的内容，可以是一个，也可以是多个，比如将八段锦替换成八部金刚功。

=SUBSTITUTES(A1,"八段锦","八部金刚功")

假如里面还存在符号。要替换成！。

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

TEXTSPLIT语法：

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

按列拆分：

=TEXTSPLIT(A1,"、")

按行拆分：

=TEXTSPLIT(A1,,"、")