Excel批量反算桩号

1、新建一个Excel表，按ALT+F11进入ExcelVB编辑器。依次点击插入模块，将下面的内容全部复制到右边的空白窗口里面（曲线元素请自行修改，下面有说明）。保存后就可以在这个新建的Excel表里用了（如果出现#NAME?请百度“Excel启用宏”）。中桩X中桩中桩X中桩Y中桩方位角中桩高程坐标反算桩号坐标反算距中桩距离=XPS(DKI,1)=XPS(DKI,2)=XPS(DKI,3)=SHU(DKI)=ZHUANG(X,Y,1)=ZHUANG(X,Y,2)FunctionXPS(DKI,Z)辛普森公式，5800程序改编DimQXAsVariantDimA,B,C,D,E,F,GAsDoubl

2、eDimP,Q,I,J,M,N,KAsDouble曲线元素，请自行更改ElselfDKIv终点桩号THENQX=Array(起点桩号，终点桩号，起点X，起点Y，起点方位角(弧度)，起点半径,终点半径)IfDKI20058.839ThenQX=Array(0,0,0,0,0,0,0)ElseIfDKI20238.839ThenQX=Array(20058.839,20238.839,4201152.834,465521.276,5.65395484192746,0,1500)ElseIfDKI20816.464ThenQX=Array(20238.839,20816.464,4201300.42

3、6,465418.29,5.71395484980653,1500,1500)ElseIfDKI20996.464ThenQX=Array(20816.464,20996.464,4201834.24,465207.124,6.09903818731417,1500,0)ElseIfDKI21116.464ThenQX=Array(20996.464,21116.464,4202012.345,465181.272,6.15903858304419,0,-1244.01)ElseIfDKI21538.232ThenQX=Array(21116.464,21538.232,4202131.155

4、,465164.502,6.11080747575542,-1244.01,-1244.01)ElseIfDKI21658.232ThenQX=Array(21538.232,21658.232,4202526.61,465023.77,5.77176839646516,-1244.01,0)ElseIfDKI21850.133ThenQX=Array(21658.232,21850.133,4202629.304,464961.713,5.72353724069503,0,0)ElseIfDKI21970.133ThenQX=Array(21850.133,21970.133,4202791

5、.929,464859.835,5.72353724069503,0,1252.482)ElseIfDKI22050.81ThenQX=Array(21970.133,22050.81,4202894.615,464797.767,5.77144211685777,1252.482,1252.482)ElseIfDKI22310.81ThenQX=Array(22050.81,22310.81,4202966.18,464760.552,5.83585582000829,1252.482,2900)ElseIfDKI23162.892ThenQX=Array(22310.81,23162.89

6、2,4203208.994,464668.271,5.98447753865125,2900,2900)ElseIfDKI23418.664ThenQX=Array(23162.892,23418.664,4204048.252,464539.886,6.27829891856905,0,0)ElseIfDKI23843.077ThenQX=Array(23418.664,23843.077,4204304.021,464538.637,6.27829887008768,-3000,-3000)ElseIfDKI24987.311ThenQX=Array(23843.077,24987.311

7、,4204726.868,464506.599,6.13682786235288,0,0)ElseIfDKI25107.311ThenQX=Array(24987.311,25107.311,4205858.869,464339.73,6.1368282986852,0,-1000)ElseIfDKI25597.197ThenQX=Array(25107.311,25597.197,4205977.194,464319.862,6.07682829080613,-1000,-1000)ElseIfDKI25717.197ThenQX=Array(25597.197,25717.197,4206

8、413.637,464108.336,5.58694229503935,-1000,0)ElseIfDKI26198.491ThenQX=Array(25717.197,26198.491,4206502.543,464027.767,5.52694228716029,0,0)ElseIfDKI26318.491ThenQX=Array(26198.491,26318.491,4206852.646,463697.506,5.5269428689367,0,-1250)ElseIfDKI26668.593ThenQX=Array(26318.491,26668.593,4206938.599,

9、463613.786,5.47894287232972,-1250,-1250)ElseIfDKI26788.593ThenQX=Array(26668.593,26788.593,4207143.201,463331.103,5.19886125757867,-1250,0)ElseIfDKI27337.747ThenQX=Array(26788.593,27337.747,4207195.875,463223.295,5.15086126097169,0,0)ElseIfDKI27487.747ThenQX=Array(27337.747,27487.747,4207429.022,462

10、726.09,5.15086174578537,0,1000)Else:QX=Array(0,0,0,0,0,0,0)EndIf以下不用更改A=QX(2)B=QX(3)C=QX(4)IfQX(5)0ThenD=1/QX(5)ElseD=0D=QX(5)IfQX(6)0ThenE=1/QX(6)ElseE=0E=QX(6)F=QX(0)G=QX(1)P=(E-D)/Abs(G-F)Q=Abs(DKI-F)I=P*QJ=C+(I+2*D)*Q/2M=C+(I/4+2*D)*Q/8N=C+3*(3*I/4+2*D)*Q/8K=C+(I/2+2*D)*Q/4IfZ=1ThenXPS=A+Q*(Cos(

11、C)+4*(Cos(M)+Cos(N)+2*Cos(K)+Cos(J)/12IfZ=2ThenXPS=B+Q*(Sin(C)+4*(Sin(M)+Sin(N)+2*Sin(K)+Sin(J)/12IfZ=3ThenXPS=JEndFunctionFunctionshu(L)DimSQXAsVariantDimA,B,C,D,R,T,E,F,H,X,YAsDouble曲线元素，请自行更改ElselfLv终点桩号THENSQX=Array(变坡点，H,R,T,E,Il,I2)IfLv20483ThenSQX=Array(0,0,0,0,0,0,0)ElseIfLv=2l225ThenSQX=Arr

12、ay(20740,785.679,l2000,l29,0.693,0.0035,0.025)ElseIfL=22009.19ThenSQX=Array(21360,801.179,30000,135,0.304,0.025,0.034)ElseIfL=22797.109ThenSQX=Array(22160,828.379,14160.563,150.81,0.803,0.034,0.0127)ElseIfL=23636.923ThenSQX=Array(22980,838.793,7075.103,182.891,2.364,0.0127,-0.039)ElseIfL=24692.69The

13、nSQX=Array(23740,809.153,12884.625,103.077,0.412,-0.039,-0.023)ElseIfL=25717.197ThenSQX=Array(24840,783.853,6137.917,147.31,1.768,-0.023,0.025)ElseIfL=26467.197ThenSQX=Array(25820,808.353,14686.143,102.803,0.36,0.025,0.039)ElseIfL=0ThenF=1ElseF=-1F=ABS(C-D)/(C-D)X=B-TY=B+TIfLXThenH=A-(B-L)*CElseIfLBThenH=A-(B-L)*C-F*(L-X)人2/(2*R)ElseIfL=BThenH=A-F*EElseIfLYThenH=A-(B-L)*DEndIfshu=HEndFunctionFunctionZHUANG(X,Y,Z)DimW,L,J,DKIAsDouble坐标反算，只需更改DKI=路线起点桩号DKI=20058.839以下不用更改DoL=(XPS(DKI,1)-X)人2+(XPS(DKI,2)-Y)人2)人0.5IfL=0ThenExitDoJ=Sin(XPS(DKI,3)-1.5707963267949-Application.WorksheetFunction