第1个回答 2019-04-02
图上数据不规范而且又没有行标号和列标号的!!你这样的格式不是一般的玩EXCEL、WPS表格的路数啊!!!!!关键还是要你规范数据结构,否则会给你带来无穷的麻烦啊!!!!!!
现假设你的数据是在A2开始的A列里的,并且仅仅只限于你图上的几个规格的日期时间数据格式,则可在B2单元格输入公式:
=IF(ISNUMBER(FIND("天",A2)),24*60*LEFT(A2,FIND("天",A2)-1)+TEXT(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(A2,1,FIND("天",A2),""),"时",":"),"分",":"),"秒",),"[M]"),1*TEXT(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(FIND("时",A2)),A2,"00:"&IF(ISNUMBER(FIND("分",A2)),A2,"00:"&A2)),"时",":"),"分",":"),"秒",),"[M]"))
复制并下拉,即可得到分钟数值,由于秒数未达1分钟,所以就没有计入了。若要把秒数按分钟数的小数计入并保留2位小数,则公式改为:
=IF(ISNUMBER(FIND("天",A2)),ROUND(24*60*LEFT(A2,FIND("天",A2)-1)+TEXT(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(A2,1,FIND("天",A2),""),"时",":"),"分",":"),"秒",),"[s]")/60,2),ROUND(TEXT(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(FIND("时",A2)),A2,"00:"&IF(ISNUMBER(FIND("分",A2)),A2,"00:"&A2)),"时",":"),"分",":"),"秒",),"[s]")/60,2))
复制并下拉,即可本回答被提问者采纳