EXCEL快速計(jì)算任何難度的道路中線邊線坐標(biāo)Word

1、excel快速計(jì)算任何難度的道路中線、邊線坐標(biāo)主程序 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 '因?yàn)橹本€連接終點(diǎn)為zh點(diǎn)，與圓曲線起點(diǎn)為同一點(diǎn)，所以在直線區(qū)域不定義zh點(diǎn)參數(shù) &

2、#39;直線區(qū)域 '曲線區(qū)域 xzh = 71858.3267 yzh = 63375.2684 kzh = 99.4763 xhz = 71909.3687 yhz = 63283.8076 '曲線區(qū)域定義內(nèi)容有：zh（坐標(biāo)、里程）、hz（坐標(biāo)、里程）、jd（坐標(biāo)、里程） 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 '樁號(hào)從第一個(gè)開始啟用，所以定義ii=2do k(ii) = workbooks("單交點(diǎn)平曲線.xls").worksheets("sheet1").cells(i, 1) '定義樁號(hào)等于讀取數(shù)據(jù) if workbooks("單交點(diǎn)平曲線.xls").worksheets("sheet1").cells(i, 1) = "" then exit do '當(dāng)沒有數(shù)據(jù)讀取時(shí)退出循環(huán) if k(ii) <

4、; kq then '若計(jì)算點(diǎn)超過計(jì)算起點(diǎn)給予提示并退出程序 msgbox ("豬??！你的輸入的樁號(hào)居然超過計(jì)算起點(diǎn)樁號(hào)") exit sub elseif k(ii) <= kzh then '若計(jì)算點(diǎn)在zh點(diǎn)前，則進(jìn)入直線程序 call zx(xzq, yzq, kq, xzh, yzh, kzh, k(ii) elseif kzh < k(ii) and k(ii) <= khy then '若計(jì)算點(diǎn)在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 '若計(jì)算點(diǎn)在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 '若計(jì)算點(diǎn)在yh和hz之間則調(diào)入后段緩和曲線程序 call hhhqx(xzh, yzh, kzh, xhz, yhz, khz, xjd, yjd, kjd, ls,

6、 r, k(ii) else msgbox ("笨?。?shù)據(jù)已超出計(jì)算范圍了") '若出現(xiàn)超出范圍的樁號(hào)則給與提示并退出程序 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)入方位角程序計(jì)算直線方位角 x = xzq + (k(ii) - kq) * cos(fw) '然后根據(jù)樁號(hào)和長度計(jì)算出坐標(biāo)值 y = yzq + (k(ii) - kq) * sin(fw) zdfm = dfm(fw) '將弧度形式的前進(jìn)方位角轉(zhuǎn)換度分秒形式 '輸出坐標(biāo)值以弧度和度分秒形式的前進(jìn)方位角 workbooks("單交點(diǎn)平曲線.xls").worksheets("sheet1").cells(i, 2) = x workbooks("單交點(diǎn)平曲線.xls").work

8、sheets("sheet1").cells(i, 3) = y workbooks("單交點(diǎn)平曲線.xls").worksheets("sheet1").cells(i, 4) = fw workbooks("單交點(diǎn)平曲線.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) '計(jì)算zh點(diǎn)（因?yàn)橐灾本忺c(diǎn)起算）到待求樁號(hào)的弧度長度 ly = l - ls / 2 '計(jì)算圓弧長度 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 '偏量坐標(biāo)計(jì)算 v = r * (1 - cos(ly / r) + p '調(diào)入方位角 fwq = fwj(xjd, xzh, yjd, yzh) '計(jì)算zh點(diǎn)方位角 fwh = fwj(xhz, xjd, yhz, yjd) '計(jì)算hz點(diǎn)方位角（此角作用是用來推算曲線是左偏還是右偏） '調(diào)入偏角判定 nq = n(fwq, fwh) '計(jì)算偏角方向，左偏為-1右偏為1 '計(jì)算坐標(biāo) 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 '計(jì)算圓曲線上的偏角（此句要點(diǎn)為角度必須轉(zhuǎn)換為弧度即：pi/180） fw = fwq + d * nq '計(jì)算前進(jìn)方位角 zdfm = dfm(fw) '將弧度形式的前進(jìn)方位角轉(zhuǎn)換度分秒形式 '輸出坐標(biāo)值以弧度和度分秒形式的前進(jìn)方位角 workbooks("單交點(diǎn)平曲線.xls").worksheets(&

12、quot;sheet1").cells(i, 2) = x workbooks("單交點(diǎn)平曲線.xls").worksheets("sheet1").cells(i, 3) = y workbooks("單交點(diǎn)平曲線.xls").worksheets("sheet1").cells(i, 4) = fw workbooks("單交點(diǎn)平曲線.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) '計(jì)算測點(diǎn)到hz點(diǎn)的距離（后緩和曲線是以hz點(diǎn)為起點(diǎn)

14、） u = l - l 5 / 40 / r 2 / ls 2 + l 9 / r 4 / ls 4 / 3456 '計(jì)算偏量 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)入方位角計(jì)算 fwq = fwj(xjd, xzh, yjd, yzh) '計(jì)算zh點(diǎn)方位角 fwh = fwj(xhz, xjd, yhz, yjd) '計(jì)算hz點(diǎn)方位角（此角作用是用來推算曲線是左偏還是右偏） '調(diào)入偏角判定 nh = n(fwh,

15、 fwq) '計(jì)算偏角方向，左偏為-1右偏為1（注意：因?yàn)槭菑暮骽z點(diǎn)起算，所以必須將hz點(diǎn)方位角放在前zh放在后） '結(jié)果計(jì)算 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)過測試，計(jì)算結(jié)果中的兩種公式計(jì)算結(jié)果是一樣的 y = yhz - (u * sin(fwh) + nh * v * cos(fwh)d = (90 * l * l / pi / r / ls) * pi / 1

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

17、 workbooks("單交點(diǎn)平曲線.xls").worksheets("sheet1").cells(i, 4) = fw workbooks("單交點(diǎn)平曲線.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 '前進(jìn)的右角pj if pj - pi > 0 then &#

18、39;當(dāng)右角pj-pi 0時(shí)為左偏否則為右偏 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 '計(jì)算增量 x0 = x1 - x2 y0 = y1 - y2 '由增量判斷方位角所在象限，不同象限取不同的值 if x0 = 0 and y0 > 0 then fwj = pi / 2 '當(dāng)在大地坐標(biāo)中偏量在x軸上的值時(shí) el

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

20、秒模塊 function dfm(byval ao as double) as variant ao = 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 & "" &

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