EXCEL下的几个自定义函数

本人自编的，觉得还不错，拿出来与大家分享。
1角度转换函数
由于EXCEL提供的有关角度计算的函数均以弧度为单位，而测量中用的是60进制的角度单位，从而无法直接进行与角度有关的计算。因此，本人编写了两个角度转换的自定义函数DEM()和DMS()，方便在EXCEL中计算。具体步骤为：新建一个EXCEL文件，在菜单中按[工具] -> [宏] -> [Visual Basic编辑器]，打开VBA界面，在其模块代码窗口中输入以下的程序代码：
Function DEG(DMS As Double) As Double
Dim D As Double
Dim m As Double
Dim S As Double
Dim TEMP As Double
TEMP = DMS
If DMS < 0 Then DMS = Abs(DMS)
D = Int(DMS)
m = Int(((DMS - Int(DMS)) + 0.00005) * 100)
S = DMS * 10000 - Int(DMS) * 10000 - m * 100
DEG = (D * 3600 + m * 60 + S) / 3600
If TEMP < 0 Then DEG = DEG * -1
End Function
Function DMS(DEG As Double) As Double
Dim D As Double
Dim m As Double
Dim S As Double
Dim Temp As Double
If DEG <= 0 Then
Temp = DEG
DEG = Abs(DEG)
End If
D = Int(DEG)
m = (DEG - Int(DEG)) * 60
S = (m - Int(m)) * 60
If Abs(S - 60) < (0.1 ^ 5) Then
S = 0
m = m + 1
End If
DMS = D + Int(m) / 100 + S / 10000
If Temp < 0 Then DMS = DMS * -1!
End Function
再在VBA中创建一个新的宏定义，宏名可任意，但注意不要与EXCEL中的函数名重复，输入以下程序：
Sub dd()
End Sub
建立好以上宏定义程序后，在EXCEL中就可以使用自己定义的函数，还可以进行编程，运行更为复杂的计算。
2 反算距离、方位角函数
Function D(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Double
Dim m As Double
Dim n As Double
m = x2 - x1
n = y2 - y1
D = Sqr(m * m + n * n)
End Function
Function A(x1 As Double, y1 As Double, x2 As Double, y2 As Double) As Double
Dim m As Double
Dim n As Double
m = x2 - x1
n = y2 - y1
A = Atn(n / m) * 180 / pi
If m < 0 Then A = A + 180
If A < 0 Then a =A+ 360
A= DMS(A)
End Function
函数中的x1、y1、x2、y2分别为两点的X、Y坐标，在EXCEL中引用两点的坐标，就可以分别计算出其边长及方位角。

EXCEL下的几个自定义函数

相关文章