excel度分秒、小数度之间的互换
度分秒换为小数度
将单元格数字格式设置为自定义
000"°"00"′"00"″"
ALT+41443"°"
ALT+41444"′"
ALT+41445"〃"
将此格式直接复制过去就可以，输入时直接输入一连串数字，如
135度12分32秒，输入1351232，单元格显示135°12′32″
在A1输入你想要的度数，如 12.354897任意找个格输入
=ROUNDDOWN(A1,0)&"°"&ROUNDDOWN((A1-ROUNDDOWN(A1,0))*60,0)&"′"&((A1 -ROUNDDOWN(A1,0))*60-ROUNDDOWN((A1-ROUNDDOWN(A1,0))*60,0))*60&"〃" 算出来就是12°21′17.6291999999977〃
120 12 12转换为000.0000000°
=SUM(MID(A1,1,3))+MID(A1,4,2)/60+MID(A1,6,7)/3600
=IF(P23>0,TRUNC(P23)&"°"&TRUNC((P23-TRUNC(P23))*60)&"′"&TRUNC(((P2 3-TRUNC(P23))*60-TRUNC((P23-TRUNC(P23))*60))*60)&"""","-"&TRUNC(-P23 )&"°"&TRUNC((-P23-TRUNC(-P23))*60)&"′"&TRUNC(((-P23-TRUNC(-P23))*6 0-TRUNC((-P23-TRUNC(-P23))*60))*60)&"""")
000"°"00"′"00"″"转换为000.0000000
=LEFT(A1,FIND("°",A1)-1)+(MID(A1,FIND("°",A1)+1,FIND("′",A1)-FIND ("°",A1)-1)+MID(A1,FIND("′",A1)+1,LEN(A1)-FIND("′",A1)-1)/60)/60 =LEFT(A1,FIND("°",A1)-1)+(MID(A1,FIND("°",A1)+1,FIND("′",A1)-FIND ("°",A1)-1))/60+(MID(A1,FIND("′",A1)+1,FIND("″",A1)-FIND("′",A1) -1))/3600
000"°"00"′"00"″"转换为000 00 00
LEFT(A1,FIND("°",A1)-1)+MID(A1,FIND("°",A1)+1,FIND("´",A1)-1-FIND( "°",A1))/60
178°38′42″转化成178.3842
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"″",),"′",),"°",".")
小数的角度转换成度分秒格式
B1=INT(A1)&"度"&INT((A1-INT(A1))*100)&"分"&INT((A1*100-INT(A1*100))*100)&"秒"
C1=INT(A1)+INT((A1-INT(A1))*100)/60+INT((A1*100-INT(A1*100))*100)/36 00
B1是转换为度分秒
C1是转换为度
120.2033333°
=TEXT(INT(A2),"0")&"."&TEXT(INT((A2-INT(A2))*60),"00")&"."&TEXT(((A2 -INT(A2))*60-INT((A2-INT(A2))*60))*60*10000,"0000")
=TEXT(INT(A2),"0")&"."&TEXT(INT((A2-INT(A2))*60),"00")&"."&TEXT(((A2 -INT(A2))*60-INT((A2-INT(A2))*60))*60*1,"0")
在A1输入你想要的度数，如 12.354897
任意找个格输入
=ROUNDDOWN(A1,0)&"°"&ROUNDDOWN((A1-ROUNDDOWN(A1,0))*60,0)&"′"&((A1 -ROUNDDOWN(A1,0))*60-ROUNDDOWN((A1-ROUNDDOWN(A1,0))*60,0))*60&"〃" 算出来就是12°21′17.6291999999977〃。