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

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点，与圆曲线起点为同一点，所以在直线区域不定义ZH点参数'直线区域'曲线区域xzh=71858.3267yzh=63375.2684kzh=99.4763xhz=71909.3687yhz=63283.8076'曲线区域定义内容有：ZH（坐标、里程）、HZ（坐标、里程）、JD（坐标、里程）khz=212.3392'R（半径）、LS（缓和曲线长度）、HY（里程）、YH（里程）xjd=71855.658yjd=63313.806kjd=160.9966ls=30r=75khy=129.4763kyh=182.3385'曲线区域i=2'从第二格开始读取数据所以定义I=2ii=1'桩号从第一个开始启用，所以定义II=2Dok(ii)=Workbooks("单交点平曲线.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)ElseIfkzh<k(ii)Andk(ii)<=khyThen'若计算点在ZH和HY之间则调入前段缓和曲线程序Callqhhqx(xzh,yzh,kzh,xhz,yhz,khz,xjd,yjd,kjd,ls,r,k(ii)ElseIfkhy<k(ii)Andk(ii)<=kyhThen'若计算点在HY和YH之间则调入圆曲线程序Callyqx(xzh,yzh,kzh,xhz,yhz,khz,xjd,yjd,kjd,ls,r,k(ii)ElseIfkyh<k(ii)Andk(ii)<=khzThen'若计算点在YH和HZ之间则调入后段缓和曲线程序Callhhhqx(xzh,yzh,kzh,xhz,yhz,khz,xjd,yjd,kjd,ls,r,k(ii)ElseMsgBox("笨啊！数据已超出计算范围了")'若出现超出范围的桩号则给与提示并退出程序ExitSubEndIfi=i+1ii=ii+1LoopEndSub直线模块Subzx(ByValxzqAsDouble,ByValyzqAsDouble,ByValkqAsDouble,ByValxzhAsDouble,ByValyzhAsDouble,ByValkzhAsDouble,ParamArrayk()fw=fwj(xzh,xzq,yzh,yzq)'首先调入方位角程序计算直线方位角x=xzq+(k(ii)-kq)*Cos(fw)'然后根据桩号和长度计算出坐标值y=yzq+(k(ii)-kq)*Sin(fw)zdfm=dfm(fw)'将弧度形式的前进方位角转换度分秒形式'输出坐标值以弧度和度分秒形式的前进方位角Workbooks("单交点平曲线.xls").Worksheets("sheet1").Cells(i,2)=xWorkbooks("单交点平曲线.xls").Worksheets("sheet1").Cells(i,3)=yWorkbooks("单交点平曲线.xls").Worksheets("sheet1").Cells(i,4)=fwWorkbooks("单交点平曲线.xls").Worksheets("sheet1").Cells(i,5)=zdfmEndSub圆曲线模块Subyqx(ByValxzhAsDouble,ByValyzhAsDouble,ByValkzhAsDouble,ByValxhzAsDouble,ByValyhzAsDouble,ByValkhzAsDouble,ByValxjdAsDouble,ByValyjdAsDouble,ByValkjdAsDouble,ByVallsAsDouble,ByValrAsDouble,ParamArrayk()l=Abs(k(ii)-kzh)'计算ZH点（因为以直缓点起算）到待求桩号的弧度长度ly=l-ls/2'计算圆弧长度p=ls2/24/r-ls4/2688/r3'曲线内移值m=ls/2-ls3/240/r2'曲线切线长增量u=r*Sin(ly/r)+m'偏量坐标计算v=r*(1-Cos(ly/r)+p'调入方位角fwq=fwj(xjd,xzh,yjd,yzh)'计算ZH点方位角fwh=fwj(xhz,xjd,yhz,yjd)'计算HZ点方位角（此角作用是用来推算曲线是左偏还是右偏）'调入偏角判定nq=n(fwq,fwh)'计算偏角方向，左偏为-1右偏为1'计算坐标x=u*Cos(fwq)-nq*v*Sin(fwq)+xzhy=u*Sin(fwq)+nq*v*Cos(fwq)+yzhd=(90*(2*l-ls)/pi/r)*pi/180'计算圆曲线上的偏角（此句要点为角度必须转换为弧度即：pi/180）fw=fwq+d*nq'计算前进方位角zdfm=dfm(fw)'将弧度形式的前进方位角转换度分秒形式'输出坐标值以弧度和度分秒形式的前进方位角Workbooks("单交点平曲线.xls").Worksheets("sheet1").Cells(i,2)=xWorkbooks("单交点平曲线.xls").Worksheets("sheet1").Cells(i,3)=yWorkbooks("单交点平曲线.xls").Worksheets("sheet1").Cells(i,4)=fwWorkbooks("单交点平曲线.xls").Worksheets("sheet1").Cells(i,5)=zdfmEndSub后缓和段模块Subhhhqx(ByValxzhAsDouble,ByValyzhAsDouble,ByValkzhAsDouble,ByValxhzAsDouble,ByValyhzAsDouble,ByValkhzAsDouble,ByValxjdAsDouble,ByValyjdAsDouble,ByValkjdAsDouble,ByVallsAsDouble,ByValrAsDouble,ParamArrayk()l=Abs(k(ii)-khz)'计算测点到HZ点的距离（后缓和曲线是以HZ点为起点）u=l-l5/40/r2/ls2+l9/r4/ls4/3456'计算偏量v=l3/6/r/ls-l7/336/r3/ls3Remt=Atn(v/u)Rems=Sqr(u2+v2)'调入方位角计算fwq=fwj(xjd,xzh,yjd,yzh)'计算ZH点方位角fwh=fwj(xhz,xjd,yhz,yjd)'计算HZ点方位角（此角作用是用来推算曲线是左偏还是右偏）'调入偏角判定nh=n(fwh,fwq)'计算偏角方向，左偏为-1右偏为1（注意：因为是从后HZ点起算，所以必须将HZ点方位角放在前ZH放在后）'结果计算Remx=xzh+s*Cos(fwq+nq*t)Remy=yzh+s*Sin(fwq+nq*t)x=xhz-(u*Cos(fwh)-nh*v*Sin(fwh)'经过测试，计算结果中的两种公式计算结果是一样的y=yhz-(u*Sin(fwh)+nh*v*Cos(fwh)d=(90*l*l/pi/r/ls)*pi/180'计算缓和曲线上的偏角（此句要点为角度必须转换为弧度即：pi/180）fw=fwh+d*nh'计算前进方位角zdfm=dfm(fw)'将弧度形式的前进方位角转换度分秒形式'输出坐标值以弧度和度分秒形式的前进方位角Workbooks("单交点平曲线.xls").Worksheets("sheet1").Cells(i,2)=xWorkbooks("单交点平曲线.xls").Worksheets("sheet1").Cells(i,3)=yWorkbooks("单交点平曲线.xls").Worksheets("sheet1").Cells(i,4)=fwWorkbooks("单交点平曲线.xls").Worksheets("sheet1").Cells(i,5)=zdfmEndSub偏角模块Functionn(ByValfw1AsDouble,ByValfw2AsDouble)AsDoublepj=fw1+pi-fw2'前进的右角pjIfpj-pi>0Then'当右角pj-pi0时为左偏否则为右偏n=-1Else:n=1EndIfEndFunction方位角模块Functionfwj(ByValx1AsDouble,ByValx2AsDouble,ByValy1AsDouble,ByValy2AsDouble)AsDouble'计算增量x0=x1-x2y0=y1-y2'由增量判断方位角所在象限，不同象限取不同的值Ifx0=0Andy0>0Thenfwj=pi/2'当在大地坐标中偏量在X轴上的值时ElseIfx0=0Andy0<0Thenfwj=3*pi/2'当在大地坐标中偏量在负X轴上的值时ElseIfx0<0Thenfwj=Atn(y0/x0)+pi'当在大地坐标中偏量在第二第三象限上的值时ElseIfx0>0Andy0<0Thenfwj=Atn(y0/x0)+2*pi'当在大地坐标中偏量在第四象限上的值时Elsefwj=Atn(y0/x0)'当在大地坐标中偏量在第一象限上的值时EndIfEndFunction度分秒模块Functiondfm(ByValaoAsDouble)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(M2)=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+RADIANS(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+RADIANS(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)=B11+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+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)=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+RADIANS(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()=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自动计算曲线坐标给大家研究研究欢迎在此基础上开发增加新模块-