【转】网上抄一段excel中经纬度转小数代码，好用

代码如下：

如果要把经纬度的度分秒转换成十进制度的形式的话，那么就在excel的表格中输入如下简单的公式即可，想要批量转换的话就在一个单元格中输入该公式然后拖拽填充柄即可。

其中D1是想要转换格式的度分秒形式的数据所在的单元格坐标

=LEFT(D1,FIND("°",D1)-1)+MID(D1,FIND("°",D1)+1,FIND("′",D1)-FIND("°",D1)-1)/60+MID(D1,FIND("′",D1)+1,2)/3600


反过来，如果要把十进制度形式的经纬度转换成度分秒形式的经纬度该怎么样呢？那么下面的公式就能帮您解决，该公式的使用方法和上面的一样，是不是感觉很简单呀？

=CONCATENATE(INT(D1),"°",IF(INT((D1-INT(D1))*60)<10,CONCATENATE("0",INT((D1-INT(D1))*60)),INT((D1-INT(D1))*60)),"′",IF(ROUND(((D1-INT(D1))*60-INT((D1-INT(D1))*60))*60,0)<10,CONCATENATE("0",ROUND(((D1-INT(D1))*60-INT((D1-INT(D1))*60))*60,0)),ROUND(((D1-INT(D1))*60-INT((D1-INT(D1))*60))*60,0)),"″")
=CONCATENATE(INT(E1),"°",IF(INT((E1-INT(E1))*60)<10,CONCATENATE("0",INT((E1-INT(E1))*60)),INT((E1-INT(E1))*60)),"′",IF(ROUND(((E1-INT(E1))*60-INT((E1-INT(E1))*60))*60,0)<10,CONCATENATE("0",ROUND(((E1-INT(E1))*60-INT((E1-INT(E1))*60))*60,0)),ROUND(((E1-INT(E1))*60-INT((E1-INT(E1))*60))*60,0)),"″")
还有把十进制度形式的经纬度转换成度分的形式，可用这个公式：

=CONCATENATE(INT(C1),"°",IF(INT((C1-INT(C1))*60)<10,CONCATENATE("0",INT((C1-INT(C1))*60)),ROUND((C1-INT(C1))*60,3)),"′")
 


=CONCATENATE(INT(D2),"°",IF(INT((D2-INT(D2))*60)<10,CONCATENATE("0",INT((D2-INT(D2))*60)),INT((D2-INT(D2))*60)),"′",IF(ROUND(((D2-INT(D2))*60-INT((D2-INT(D2))*60))*60,0)<10,CONCATENATE("0",ROUND(((D2-INT(D2))*60-INT((D2-INT(D2))*60))*60,0)),ROUND(((D2-INT(D2))*60-INT((D2-INT(D2))*60))*60,0)),"″")


=CONCATENATE(INT(E2),"°",IF(INT((E2-INT(E2))*60)<10,CONCATENATE("0",INT((E2-INT(E2))*60)),INT((E2-INT(E2))*60)),"′",IF(ROUND(((E2-INT(E2))*60-INT((E2-INT(E2))*60))*60,0)<10,CONCATENATE("0",ROUND(((E2-INT(E2))*60-INT((E2-INT(E2))*60))*60,0)),ROUND(((E2-INT(E2))*60-INT((E2-INT(E2))*60))*60,0)),"″")