=IF(LEN(INT((A1-INT(A1))*60))=1,IF(LEN(ROUND(((A1-INT(A1))*60-INT((A1-INT(A1))*60))*60,0))=1,INT(A1)&".0"&INT((A1-INT(A1))*60)&"0"&ROUND(((A1-INT(A1))*60-INT((A1-INT(A1))*60))*60,0),INT(A1)&".0"&INT((A1-INT(A1))*60)&ROUND(((A1-INT(A1))*60-INT((A1-INT(A1))*60))*60,0)),IF(LEN(ROUND(((A1-INT(A1))*60-INT((A1-INT(A1))*60))*60,0))=1,INT(A1)&"."&INT((A1-INT(A1))*60)&"0"&ROUND(((A1-INT(A1))*60-INT((A1-INT(A1))*60))*60,0),INT(A1)&"."&INT((A1-INT(A1))*60)&ROUND(((A1-INT(A1))*60-INT((A1-INT(A1))*60))*60,0)))
下面这个用了text函数
=(ABS(A1)/A1)*INT(ABS(A1))&"."&TEXT(INT((ABS(A1)-INT(ABS(A1)))*60),"00")&TEXT(ROUND(((ABS(A1)-INT(ABS(A1)))*60-INT((ABS(A1)-INT(ABS(A1)))*60))*60,0),"00")
这里A1是 度 所在的单元格
修正了一下,之前个别数差1秒的问题
(23.12.5)