EXCEL快速計算任何難度的道路中線邊線坐標Word

1、excel快速計算任何難度的道路中線、邊線坐標主程序 public i as integer public pi as double sub tp() dim ii as integer dim k(1000) as double dim xzq, yzq, kq, xzh, yzh, kzh, xjd, yjd, kjd, khy, kyh as double '直線區(qū)域 pi = 3.14159265358979 xzq = 71862.642 yzq = 63474.651 kq = 0 '因為直線連接終點為zh點，與圓曲線起點為同一點，所以在直線區(qū)域不定義zh點參數(shù) &

2、#39;直線區(qū)域 '曲線區(qū)域 xzh = 71858.3267 yzh = 63375.2684 kzh = 99.4763 xhz = 71909.3687 yhz = 63283.8076 '曲線區(qū)域定義內(nèi)容有：zh（坐標、里程）、hz（坐標、里程）、jd（坐標、里程） khz = 212.3392 'r（半徑）、ls（緩和曲線長度）、hy（里程）、yh（里程） xjd = 71855.658 yjd = 63313.806 kjd = 160.9966 ls = 30 r = 75 khy = 129.4763 kyh = 182.3385 '曲線區(qū)域i

3、= 2 '從第二格開始讀取數(shù)據(jù)所以定義i=2 ii = 1 '樁號從第一個開始啟用，所以定義ii=2do k(ii) = workbooks("單交點平曲線.xls").worksheets("sheet1").cells(i, 1) '定義樁號等于讀取數(shù)據(jù) if workbooks("單交點平曲線.xls").worksheets("sheet1").cells(i, 1) = "" then exit do '當沒有數(shù)據(jù)讀取時退出循環(huán) if k(ii) <

4、; kq then '若計算點超過計算起點給予提示并退出程序 msgbox ("豬啊！你的輸入的樁號居然超過計算起點樁號") exit sub elseif k(ii) <= kzh then '若計算點在zh點前，則進入直線程序 call zx(xzq, yzq, kq, xzh, yzh, kzh, k(ii) elseif kzh < k(ii) and k(ii) <= khy then '若計算點在zh和hy之間則調(diào)入前段緩和曲線程序 call qhhqx(xzh, yzh, kzh, xhz, yhz, khz, xjd

5、, yjd, kjd, ls, r, k(ii) elseif khy < k(ii) and k(ii) <= kyh then '若計算點在hy和yh之間則調(diào)入圓曲線程序 call yqx(xzh, yzh, kzh, xhz, yhz, khz, xjd, yjd, kjd, ls, r, k(ii) elseif kyh < k(ii) and k(ii) <= khz then '若計算點在yh和hz之間則調(diào)入后段緩和曲線程序 call hhhqx(xzh, yzh, kzh, xhz, yhz, khz, xjd, yjd, kjd, ls,

6、 r, k(ii) else msgbox ("笨啊！數(shù)據(jù)已超出計算范圍了") '若出現(xiàn)超出范圍的樁號則給與提示并退出程序 exit sub end if i = i + 1 ii = ii + 1 loop end sub直線模塊 sub zx(byval xzq as double, byval yzq as double, byval kq as double, byval xzh as double, byval yzh as double, byval kzh as double, paramarray k() fw = fwj(xzh, xzq, yzh

7、, yzq) '首先調(diào)入方位角程序計算直線方位角 x = xzq + (k(ii) - kq) * cos(fw) '然后根據(jù)樁號和長度計算出坐標值 y = yzq + (k(ii) - kq) * sin(fw) zdfm = dfm(fw) '將弧度形式的前進方位角轉(zhuǎn)換度分秒形式 '輸出坐標值以弧度和度分秒形式的前進方位角 workbooks("單交點平曲線.xls").worksheets("sheet1").cells(i, 2) = x workbooks("單交點平曲線.xls").work

8、sheets("sheet1").cells(i, 3) = y workbooks("單交點平曲線.xls").worksheets("sheet1").cells(i, 4) = fw workbooks("單交點平曲線.xls").worksheets("sheet1").cells(i, 5) = zdfm end sub圓曲線模塊 sub yqx(byval xzh as double, byval yzh as double, byval kzh as double, byval x

9、hz as double, byval yhz as double, byval khz as double, byval xjd as double, byval yjd as double, byval kjd as double, byval ls as double, byval r as double, paramarray k() l = abs(k(ii) - kzh) '計算zh點（因為以直緩點起算）到待求樁號的弧度長度 ly = l - ls / 2 '計算圓弧長度 p = ls 2 / 24 / r - ls 4 / 2688 / r 3 '曲線內(nèi)移

10、值 m = ls / 2 - ls 3 / 240 / r 2 '曲線切線長增量 u = r * sin(ly / r) + m '偏量坐標計算 v = r * (1 - cos(ly / r) + p '調(diào)入方位角 fwq = fwj(xjd, xzh, yjd, yzh) '計算zh點方位角 fwh = fwj(xhz, xjd, yhz, yjd) '計算hz點方位角（此角作用是用來推算曲線是左偏還是右偏） '調(diào)入偏角判定 nq = n(fwq, fwh) '計算偏角方向，左偏為-1右偏為1 '計算坐標 x = u * co

11、s(fwq) - nq * v * sin(fwq) + xzh y = u * sin(fwq) + nq * v * cos(fwq) + yzhd = (90 * (2 * l - ls) / pi / r) * pi / 180 '計算圓曲線上的偏角（此句要點為角度必須轉(zhuǎn)換為弧度即：pi/180） fw = fwq + d * nq '計算前進方位角 zdfm = dfm(fw) '將弧度形式的前進方位角轉(zhuǎn)換度分秒形式 '輸出坐標值以弧度和度分秒形式的前進方位角 workbooks("單交點平曲線.xls").worksheets(&

12、quot;sheet1").cells(i, 2) = x workbooks("單交點平曲線.xls").worksheets("sheet1").cells(i, 3) = y workbooks("單交點平曲線.xls").worksheets("sheet1").cells(i, 4) = fw workbooks("單交點平曲線.xls").worksheets("sheet1").cells(i, 5) = zdfm end sub后緩和段模塊 sub

13、hhhqx(byval xzh as double, byval yzh as double, byval kzh as double, byval xhz as double, byval yhz as double, byval khz as double, byval xjd as double, byval yjd as double, byval kjd as double, byval ls as double, byval r as double, paramarray k() l = abs(k(ii) - khz) '計算測點到hz點的距離（后緩和曲線是以hz點為起點

14、） u = l - l 5 / 40 / r 2 / ls 2 + l 9 / r 4 / ls 4 / 3456 '計算偏量 v = l 3 / 6 / r / ls - l 7 / 336 / r 3 / ls 3 rem t = atn(v / u) rem s = sqr(u 2 + v 2) '調(diào)入方位角計算 fwq = fwj(xjd, xzh, yjd, yzh) '計算zh點方位角 fwh = fwj(xhz, xjd, yhz, yjd) '計算hz點方位角（此角作用是用來推算曲線是左偏還是右偏） '調(diào)入偏角判定 nh = n(fwh,

15、 fwq) '計算偏角方向，左偏為-1右偏為1（注意：因為是從后hz點起算，所以必須將hz點方位角放在前zh放在后） '結(jié)果計算 rem x = xzh + s * cos(fwq + nq * t) rem y = yzh + s * sin(fwq + nq * t) x = xhz - (u * cos(fwh) - nh * v * sin(fwh) '經(jīng)過測試，計算結(jié)果中的兩種公式計算結(jié)果是一樣的 y = yhz - (u * sin(fwh) + nh * v * cos(fwh)d = (90 * l * l / pi / r / ls) * pi / 1

16、80 '計算緩和曲線上的偏角（此句要點為角度必須轉(zhuǎn)換為弧度即：pi/180） fw = fwh + d * nh '計算前進方位角 zdfm = dfm(fw) '將弧度形式的前進方位角轉(zhuǎn)換度分秒形式 '輸出坐標值以弧度和度分秒形式的前進方位角 workbooks("單交點平曲線.xls").worksheets("sheet1").cells(i, 2) = x workbooks("單交點平曲線.xls").worksheets("sheet1").cells(i, 3) = y

17、 workbooks("單交點平曲線.xls").worksheets("sheet1").cells(i, 4) = fw workbooks("單交點平曲線.xls").worksheets("sheet1").cells(i, 5) = zdfm end sub偏角模塊 function n(byval fw1 as double, byval fw2 as double) as double pj = fw1 + pi - fw2 '前進的右角pj if pj - pi > 0 then &#

18、39;當右角pj-pi 0時為左偏否則為右偏 n = -1 else: n = 1 end if end function方位角模塊 function fwj(byval x1 as double, byval x2 as double, byval y1 as double, byval y2 as double) as double '計算增量 x0 = x1 - x2 y0 = y1 - y2 '由增量判斷方位角所在象限，不同象限取不同的值 if x0 = 0 and y0 > 0 then fwj = pi / 2 '當在大地坐標中偏量在x軸上的值時 el

19、seif x0 = 0 and y0 < 0 then fwj = 3 * pi / 2 '當在大地坐標中偏量在負x軸上的值時 elseif x0 < 0 then fwj = atn(y0 / x0) + pi '當在大地坐標中偏量在第二第三象限上的值時 elseif x0 > 0 and y0 < 0 then fwj = atn(y0 / x0) + 2 * pi '當在大地坐標中偏量在第四象限上的值時 else fwj = atn(y0 / x0) '當在大地坐標中偏量在第一象限上的值時 end if end function度分

20、秒模塊 function dfm(byval ao as double) as variant ao = ao * 180 / pi '將弧度轉(zhuǎn)化為度 jd = int(ao) '計算度 jf = int(ao * 60 - jd * 60) '計算分 jmx = (ao - jd - jf / 60) * 3600 '計算秒 jm = left(jmx, 8) '因為拆分出來的秒數(shù)經(jīng)常占到十多位，所以只取秒數(shù)的前八位 dfm = jd & "°" & jf & "" &

21、jm & "" '連接度分秒 end function邊樁公式(此公式在電子表格中直接輸入)： =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

22、*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+ra

23、dians(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+j

24、9*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+radia

25、ns(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) =b

26、14+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+k1