将表格中经纬度转换成小数格式数据是42°28‘30.85,在表格中写为42.283085用=INT(A2)+INT(A2*100-INT(A2)*100)/60+INT(A2*10000-INT(A2*100)*100)/3600后发现此式只计算到42.2830,剩下的被忽略,请问该怎么更改公式或

Question

将表格中经纬度转换成小数格式
数据是42°28‘30.85,在表格中写为42.283085用=INT(A2)+INT(A2*100-INT(A2)*100)/60+INT(A2*10000-INT(A2*100)*100)/3600后发现此式只计算到42.2830,剩下的被忽略,请问该怎么更改公式
或者写为422830.85,只是公式又该是什么呢?

Answer 1

B2列计算度=INT(A2) C2列计算分=INT((A2-INT(A2))*100) D2列计算秒==(A2-INT(A2)-INT((A2-INT(A2))*100)/100)*10000 你要转换成十进制度的话,E2列直接输入 ==INT(A2)+INT((A2-INT(A2))*100)/60+(A2-INT(A2)-INT((A2-INT(A2))*100)/100)*10000/3600
如果你直接单纯地想把42.283085改写成42°28‘30.85的话,
=INT(A2) & “°” & INT((A2-INT(A2))*100) & “′” & (A2-INT(A2)-INT((A2-INT(A2))*100)/100)*10000 & “″”