EXCEL快速計(jì)算任何難度的道路中線、邊線坐標(biāo)復(fù)習(xí)課程

1、Good is good, but better carries it.精益求精，善益求善。EXCEL快速計(jì)算任何難度的道路中線、邊線坐標(biāo)-EXCEL快速計(jì)算任何難度的道路中線、邊線坐標(biāo)主程序PubliciAsIntegerPublicpiAsDoubleSubTP()DimiiAsIntegerDimk(1000)AsDoubleDimxzq,yzq,kq,xzh,yzh,kzh,xjd,yjd,kjd,khy,kyhAsDouble直線區(qū)域pi=3.14159265358979xzq=71862.642yzq=63474.651kq=0因?yàn)橹本€連接終點(diǎn)為ZH點(diǎn)，與圓曲線起點(diǎn)為同一點(diǎn)，所以在

2、直線區(qū)域不定義ZH點(diǎn)參數(shù)直線區(qū)域曲線區(qū)域xzh=71858.3267yzh=63375.2684kzh=99.4763xhz=71909.3687yhz=63283.8076曲線區(qū)域定義內(nèi)容有：ZH（坐標(biāo)、里程）、HZ（坐標(biāo)、里程）、JD（坐標(biāo)、里程）khz=212.3392R（半徑）、LS（緩和曲線長度）、HY（里程）、YH（里程）xjd=71855.658yjd=63313.806kjd=160.9966ls=30r=75khy=129.4763kyh=182.3385曲線區(qū)域i=2從第二格開始讀取數(shù)據(jù)所以定義I=2ii=1樁號(hào)從第一個(gè)開始啟用，所以定義II=2Dok(ii)=Workbo

3、oks(單交點(diǎn)平曲線.xls).Worksheets(sheet1).Cells(i,1)定義樁號(hào)等于讀取數(shù)據(jù)IfWorkbooks(單交點(diǎn)平曲線.xls).Worksheets(sheet1).Cells(i,1)=ThenExitDo當(dāng)沒有數(shù)據(jù)讀取時(shí)退出循環(huán)Ifk(ii)kqThen若計(jì)算點(diǎn)超過計(jì)算起點(diǎn)給予提示并退出程序MsgBox(豬啊！你的輸入的樁號(hào)居然超過計(jì)算起點(diǎn)樁號(hào))ExitSubElseIfk(ii)=kzhThen若計(jì)算點(diǎn)在ZH點(diǎn)前，則進(jìn)入直線程序Callzx(xzq,yzq,kq,xzh,yzh,kzh,k(ii)ElseIfkzhk(ii)Andk(ii)=khyThen若

4、計(jì)算點(diǎn)在ZH和HY之間則調(diào)入前段緩和曲線程序Callqhhqx(xzh,yzh,kzh,xhz,yhz,khz,xjd,yjd,kjd,ls,r,k(ii)ElseIfkhyk(ii)Andk(ii)=kyhThen若計(jì)算點(diǎn)在HY和YH之間則調(diào)入圓曲線程序Callyqx(xzh,yzh,kzh,xhz,yhz,khz,xjd,yjd,kjd,ls,r,k(ii)ElseIfkyhk(ii)Andk(ii)0Then當(dāng)右角pj-pi0時(shí)為左偏否則為右偏n=-1Else:n=1EndIfEndFunction方位角模塊Functionfwj(ByValx1AsDouble,ByValx2AsDou

5、ble,ByValy1AsDouble,ByValy2AsDouble)AsDouble計(jì)算增量x0=x1-x2y0=y1-y2由增量判斷方位角所在象限，不同象限取不同的值Ifx0=0Andy00Thenfwj=pi/2當(dāng)在大地坐標(biāo)中偏量在X軸上的值時(shí)ElseIfx0=0Andy00Thenfwj=3*pi/2當(dāng)在大地坐標(biāo)中偏量在負(fù)X軸上的值時(shí)ElseIfx00Andy00Thenfwj=Atn(y0/x0)+2*pi當(dāng)在大地坐標(biāo)中偏量在第四象限上的值時(shí)Elsefwj=Atn(y0/x0)當(dāng)在大地坐標(biāo)中偏量在第一象限上的值時(shí)EndIfEndFunction度分秒模塊Functiondfm(By

6、ValaoAsDouble)AsVariantao=ao*180/pi將弧度轉(zhuǎn)化為度jd=Int(ao)計(jì)算度jf=Int(ao*60-jd*60)計(jì)算分jmx=(ao-jd-jf/60)*3600計(jì)算秒jm=Left(jmx,8)因?yàn)椴鸱殖鰜淼拿霐?shù)經(jīng)常占到十多位，所以只取秒數(shù)的前八位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()=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*CO