度分秒转换

提供文件下载
http://www.excelba.com/showart.asp?art_id=126

一、角度的度分秒与小数点格式互相转换
EXCEL能将角度转换成度"°"分"′"秒"″"格式吗?
如将:120.999722222222°转换成120°59′59″.
32.55°转换成32°33′0″
我知道可以用函数RADIANS将角度转换为弧度，但我们工程测量上用到的角度计算是以度分秒（比如：126°12′36″）来记录并进行计算的，在Excel中怎样用这些数据进行计算？
1.设定公式“=A1/24”,并将单元格的格式设定为自定义格式:[h]°mm′ss″
2.直接设定公式=TEXT(A1/24,"[h]°mm′ss″")
第1种方法，方便于在计算时将度分秒转为度，这时只需乘24即可
第2种方法，度分秒转为度时，需要用公式=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"°",":"),"′",":"),"″",)*24
(度分秒符号用单位软键盘上的符号)
二、度/分/秒的输入问题
在工程计算中经常要用到度、分、秒，通常的做法就是插入特殊字符来输入。但是在EXCEL中显示的结果却不近人意，三者之间的字符间距太大，看起来就象刚学会写字的人写的东西。其实这是由字体引起来的，一般情况下系统默认的字体是宋体，在这种情况下输入度分秒的时候，它们之间的间距就会比较大。下面几种字体的效果就比较好：
Arial Unicode Ms , Batang , Dotum , Gungsuh , New Gulim , Gulim 。
当然也不只是这几种，大家也可以自己测试一下，找一下其它合适的字体。
转载请注明:本文来自:Excel吧 ( www.excelba.com) 详细出处参考： http://www.excelba.com/showart.asp?art_id=126

转自 excel home 论坛 由于没有源程序
请暂时自行解读，其实我也在在读，呵呵
等读懂 或者回头有 成型文件再发上

MicroSoft FAQ

Converting Decimal Degrees to Degrees/Minutes/Seconds

Function Convert_Degree(Decimal_Deg) As Variant
With Application
'Set degree to Integer of Argument Passed
Degrees = Int(Decimal_Deg)
'Set minutes to 60 times the number to the right
'of the decimal for the variable Decimal_Deg
Minutes = (Decimal_Deg - Degrees) * 60
'Set seconds to 60 times the number to the right of the
'decimal for the variable Minute
Seconds = Format(((Minutes - Int(Minutes)) * 60), "0")
'Returns the Result of degree conversion
'(for example, 10.46 = 10~ 27 ' 36")
Convert_Degree = " " & Degrees & "° " & Int(Minutes) & "' " _
& Seconds + Chr(34)
End With
End Function

Example:
A1 =10.6
B1 = Convert_Degree(A1)

Converting Degrees/Minutes/Seconds to Decimal Degrees

Function Convert_Decimal(Degree_Deg As String) As Double
' Declare the variables to be double precision floating-point.
Dim degrees As Double
Dim minutes As Double
Dim seconds As Double
' Set degree to value before "°" of Argument Passed.
degrees = Val(Left(Degree_Deg, InStr(1, Degree_Deg, "°") - 1))
' Set minutes to the value between the "°" and the "'"
' of the text string for the variable Degree_Deg divided by
' 60. The Val function converts the text string to a number.
minutes = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "°") + 2, _
InStr(1, Degree_Deg, "'") - InStr(1, Degree_Deg, _
"°") - 2)) / 60
' Set seconds to the number to the right of "'" that is
' converted to a value and then divided by 3600.
seconds = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "'") + _
2, Len(Degree_Deg) - InStr(1, Degree_Deg, "'") - 2)) _
/ 3600
Convert_Decimal = degrees + minutes + seconds
End Function


Example:
=Convert_Decimal("10° 27' 36""")