EXCEL快速计算任何难度的道路中线、边线坐标复习课程.doc

《EXCEL快速计算任何难度的道路中线、边线坐标复习课程.doc》由会员分享，可在线阅读，更多相关《EXCEL快速计算任何难度的道路中线、边线坐标复习课程.doc（15页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Good is good, but better carries it.精益求精，善益求善。EXCEL快速计算任何难度的道路中线、边线坐标-EXCEL快速计算任何难度的道路中线、边线坐标主程序PubliciAsIntegerPublicpiAsDoubleSubTP()DimiiAsIntegerDimk(1000)AsDoubleDimxzq,yzq,kq,xzh,yzh,kzh,xjd,yjd,kjd,khy,kyhAsDouble直线区域pi=3.14159265358979xzq=71862.642yzq=63474.651kq=0因为直线连接终点为ZH点，与圆曲线起点为同一点，所以在

2、直线区域不定义ZH点参数直线区域曲线区域xzh=71858.3267yzh=63375.2684kzh=99.4763xhz=71909.3687yhz=63283.8076曲线区域定义内容有：ZH（坐标、里程）、HZ（坐标、里程）、JD（坐标、里程）khz=212.3392R（半径）、LS（缓和曲线长度）、HY（里程）、YH（里程）xjd=71855.658yjd=63313.806kjd=160.9966ls=30r=75khy=129.4763kyh=182.3385曲线区域i=2从第二格开始读取数据所以定义I=2ii=1桩号从第一个开始启用，所以定义II=2Dok(ii)=Workbo

3、oks(单交点平曲线.xls).Worksheets(sheet1).Cells(i,1)定义桩号等于读取数据IfWorkbooks(单交点平曲线.xls).Worksheets(sheet1).Cells(i,1)=ThenExitDo当没有数据读取时退出循环Ifk(ii)kqThen若计算点超过计算起点给予提示并退出程序MsgBox(猪啊！你的输入的桩号居然超过计算起点桩号)ExitSubElseIfk(ii)=kzhThen若计算点在ZH点前，则进入直线程序Callzx(xzq,yzq,kq,xzh,yzh,kzh,k(ii)ElseIfkzhk(ii)Andk(ii)=khyThen若

4、计算点在ZH和HY之间则调入前段缓和曲线程序Callqhhqx(xzh,yzh,kzh,xhz,yhz,khz,xjd,yjd,kjd,ls,r,k(ii)ElseIfkhyk(ii)Andk(ii)=kyhThen若计算点在HY和YH之间则调入圆曲线程序Callyqx(xzh,yzh,kzh,xhz,yhz,khz,xjd,yjd,kjd,ls,r,k(ii)ElseIfkyhk(ii)Andk(ii)0Then当右角pj-pi0时为左偏否则为右偏n=-1Else:n=1EndIfEndFunction方位角模块Functionfwj(ByValx1AsDouble,ByValx2AsDou

5、ble,ByValy1AsDouble,ByValy2AsDouble)AsDouble计算增量x0=x1-x2y0=y1-y2由增量判断方位角所在象限，不同象限取不同的值Ifx0=0Andy00Thenfwj=pi/2当在大地坐标中偏量在X轴上的值时ElseIfx0=0Andy00Thenfwj=3*pi/2当在大地坐标中偏量在负X轴上的值时ElseIfx00Andy00Thenfwj=Atn(y0/x0)+2*pi当在大地坐标中偏量在第四象限上的值时Elsefwj=Atn(y0/x0)当在大地坐标中偏量在第一象限上的值时EndIfEndFunction度分秒模块Functiondfm(By

6、ValaoAsDouble)AsVariantao=ao*180/pi将弧度转化为度jd=Int(ao)计算度jf=Int(ao*60-jd*60)计算分jmx=(ao-jd-jf/60)*3600计算秒jm=Left(jmx,8)因为拆分出来的秒数经常占到十多位，所以只取秒数的前八位dfm=jd&jf&jm&连接度分秒EndFunction边桩公式(此公式在电子表格中直接输入)：=B2+J2*COS(D2+RADIANS(L2)+PI()=C2+J2*SIN(D2+RADIANS(L2)+PI()=B2+K2*COS(D2+RADIANS(M2)=C2+K2*SIN(D2+RADIANS(M

7、2)=B3+J3*COS(D3+RADIANS(L3)+PI()=C3+J3*SIN(D3+RADIANS(L3)+PI()=B3+K3*COS(D3+RADIANS(M3)=C3+K3*SIN(D3+RADIANS(M3)=B4+J4*COS(D4+RADIANS(L4)+PI()=C4+J4*SIN(D4+RADIANS(L4)+PI()=B4+K4*COS(D4+RADIANS(M4)=C4+K4*SIN(D4+RADIANS(M4)=B5+J5*COS(D5+RADIANS(L5)+PI()=C5+J5*SIN(D5+RADIANS(L5)+PI()=B5+K5*COS(D5+RADI

8、ANS(M5)=C5+K5*SIN(D5+RADIANS(M5)=B6+J6*COS(D6+RADIANS(L6)+PI()=C6+J6*SIN(D6+RADIANS(L6)+PI()=B6+K6*COS(D6+RADIANS(M6)=C6+K6*SIN(D6+RADIANS(M6)=B7+J7*COS(D7+RADIANS(L7)+PI()=C7+J7*SIN(D7+RADIANS(L7)+PI()=B7+K7*COS(D7+RADIANS(M7)=C7+K7*SIN(D7+RADIANS(M7)=B8+J8*COS(D8+RADIANS(L8)+PI()=C8+J8*SIN(D8+RADI

9、ANS(L8)+PI()=B8+K8*COS(D8+RADIANS(M8)=C8+K8*SIN(D8+RADIANS(M8)=B9+J9*COS(D9+RADIANS(L9)+PI()=C9+J9*SIN(D9+RADIANS(L9)+PI()=B9+K9*COS(D9+RADIANS(M9)=C9+K9*SIN(D9+RADIANS(M9)=B10+J10*COS(D10+RADIANS(L10)+PI()=C10+J10*SIN(D10+RADIANS(L10)+PI()=B10+K10*COS(D10+RADIANS(M10)=C10+K10*SIN(D10+RADIANS(M10)=B

10、11+J11*COS(D11+RADIANS(L11)+PI()=C11+J11*SIN(D11+RADIANS(L11)+PI()=B11+K11*COS(D11+RADIANS(M11)=C11+K11*SIN(D11+RADIANS(M11)=B12+J12*COS(D12+RADIANS(L12)+PI()=C12+J12*SIN(D12+RADIANS(L12)+PI()=B12+K12*COS(D12+RADIANS(M12)=C12+K12*SIN(D12+RADIANS(M12)=B13+J13*COS(D13+RADIANS(L13)+PI()=C13+J13*SIN(D13

11、+RADIANS(L13)+PI()=B13+K13*COS(D13+RADIANS(M13)=C13+K13*SIN(D13+RADIANS(M13)=B14+J14*COS(D14+RADIANS(L14)+PI()=C14+J14*SIN(D14+RADIANS(L14)+PI()=B14+K14*COS(D14+RADIANS(M14)=C14+K14*SIN(D14+RADIANS(M14)=B15+J15*COS(D15+RADIANS(L15)+PI()=C15+J15*SIN(D15+RADIANS(L15)+PI()=B15+K15*COS(D15+RADIANS(M15)=

12、C15+K15*SIN(D15+RADIANS(M15)=B16+J16*COS(D16+RADIANS(L16)+PI()=C16+J16*SIN(D16+RADIANS(L16)+PI()=B16+K16*COS(D16+RADIANS(M16)=C16+K16*SIN(D16+RADIANS(M16)=B17+J17*COS(D17+RADIANS(L17)+PI()=C17+J17*SIN(D17+RADIANS(L17)+PI()=B17+K17*COS(D17+RADIANS(M17)=C17+K17*SIN(D17+RADIANS(M17)=B18+J18*COS(D18+RAD

13、IANS(L18)+PI()=C18+J18*SIN(D18+RADIANS(L18)+PI()=B18+K18*COS(D18+RADIANS(M18)=C18+K18*SIN(D18+RADIANS(M18)=B19+J19*COS(D19+RADIANS(L19)+PI()=C19+J19*SIN(D19+RADIANS(L19)+PI()=B19+K19*COS(D19+RADIANS(M19)=C19+K19*SIN(D19+RADIANS(M19)=B20+J20*COS(D20+RADIANS(L20)+PI()=C20+J20*SIN(D20+RADIANS(L20)+PI()

14、=B20+K20*COS(D20+RADIANS(M20)=C20+K20*SIN(D20+RADIANS(M20)=B21+J21*COS(D21+RADIANS(L21)+PI()=C21+J21*SIN(D21+RADIANS(L21)+PI()=B21+K21*COS(D21+RADIANS(M21)=C21+K21*SIN(D21+RADIANS(M21)=B22+J22*COS(D22+RADIANS(L22)+PI()=C22+J22*SIN(D22+RADIANS(L22)+PI()=B22+K22*COS(D22+RADIANS(M22)=C22+K22*SIN(D22+RADIANS(M22)=B23+J23*COS(D23+RADIANS(L23)+PI()=C23+J23*SIN(D23+RADIANS(L23)+PI()=B23+K23*COS(D23+RADIANS(M23)=C23+K23*SIN(D23+RADIANS(M23)呵呵，这个就是传说已久的EXCEL自动计算曲线坐标给大家研究研究欢迎在此基础上开发增加新模块-