- =LET(数据,$E4,字符,CHAR(10),个数,LEN(数据),行号,ROW(INDIRECT("1:"&个数)),拆分,MID(数据,行号,1),位置,IF(行号=1,1,IF(行号=个数,个数+2,IF(ISNUMBER(FIND(拆分,字符,1)),行号+1))),查1,SMALL(位置,行号),查2,SMALL(位置,行号+1),时间,IFERROR(TEXT(TRIM(MID(数据,查1,查2-查1-1)),"hh:mm"),""),区1,TEXT(MID(G$2,1,FIND("-",G$2,1)-1),"hh:mm"),区2,TEXT(MID(G$2,FIND("-",G$2,1)+1,LEN(G$2)),"hh:mm"),判断,(时间>=区1)*(时间<=区2),结果,FILTER(时间,判断),转分,MID(结果,1,FIND(":",结果,1)-1)*60+MID(结果,FIND(":",结果,1)+1,2),最大,INDEX(结果,MATCH(MAX(转分),转分,0)),最小,INDEX(结果,MATCH(MIN(转分),转分,0)),IFERROR(IF(SUM(判断)>1,IF(G$3="上班",最小,最大),结果),""))
复制代码
|