基于Excel-VBA的公路線路坐標正反算程序設計

鐘宏斌，劉慶元

(1.中南大學 地球科學與信息物理學院，湖南 長沙 410083；2.長沙市軌道交通集團有限公司，湖南 長沙 410009)

在公路與鐵路工程建設中，測量進場后首要工作就是線路放樣要素的計算。Excel作為常用的辦公軟件具有較強的數據處理和編輯排版能力，它的公式、函數變化萬千、應用廣泛，但幾乎沒有在測量工程線路坐標正反算中應用。本文根據多年生產實踐和多個高速公路項目的實際工作經驗，編寫的基于Excel-VBA自定義函數的線路坐標正反算程序，可實現與Excel的公式、函數融合應用。

1 Gauss-Legendre通用公式

公路工程線型構型復雜多變，但都是由直線、圓曲線和緩和曲線三種基本曲線組成[1-2]。本程序采用通用Gauss-Legendre公式[3-6]，可適用于這三種曲線。

1.1 曲線元上任意點處切線方位角的計算

文獻[3]已給出曲線元上任意點處切線方位角的計算公式：

(1)

式中，α為待求點切線方位角;αA為線元起點A處切線方位角;G為線元的偏轉系數;ρA為線元起點A處曲率;l為待求點至起點A處曲線長;ρAB為線元起終點的曲率差;LS為線元長度。

其中G的取值為1、-1或0，當曲線元左偏時，G=-1；線元右偏時，G=1；線元為直線時，G=0。

1.2 曲線上任意點的坐標計算公式

文獻[3]、[4]已給出線元任意點坐標計算的Gauss-Legendre通用公式：

(2)

式中,X、Y為待求點坐標;XA、YA為線元起點A坐標。

文獻[4]指出，當節點數n=5時，式(2)即可滿足工程中所有的線路構型的坐標計算精度要求。5節點公式中Ri、Vi的值為：R1=R5=0.118 463 442 5;R2=R4=0.239 314 335 2;R3=0.284 444 444 4;V1=1-V5=0.046 910 077 0;V2=1-V4=0.230 765 344 9;V3=0.5。

將Ri、Vi的值代入式(2)，可用于計算線元上任意點的坐標，即線路中樁坐標。

邊樁坐標的計算公式如下：

(3)

式中，B為邊樁到中樁的距離(左邊距為負,右邊距為正)。

式(2)、(3)為線路任意點坐標計算的Gauss-Legendre通用公式。

2 基于Excel-VBA的線路坐標正反算程序實現

本程序基于Excel-VBA，將曲線要素數據庫直接錄入于工作表，計算過程以代碼封裝于VBA[7]，使用方法以函數的形式交由用戶，其程序運行流程圖如圖1所示。

圖1 程序運行流程圖

2.1 構建曲線要素數據庫

在空白工作表中構建如表1所示表格格式(第一行為表名，第五行為表頭，第六行開始是線元數據，J3單元格輸入線元個數；有效數據輸入列為A-J列)。

為保證程序的正常運行，作如下約定：(1)表格結構須與表1一致；(2)要填寫正確的線元個數；(3)起點方位角單位為度(不能是度分秒的格式)，并保留盡量多的小數位數；(4)本工作表表名可以按需命名。

表1 曲線要素數據庫格式

根據設計提供的《直線、曲線及轉角一覽表》，計算、提取線元要素，填寫曲線要素數據庫的工作表，將工作簿命名存盤。

2.2 坐標正算函數主程序

程序以自定義函數[8]的方式驅動，故主程序為函數，定義兩個函數名，其格式為：(1)坐標正算X坐標函數：ZSX("工作表名",里程,偏距)；(2)坐標正算Y坐標函數：ZSY("工作表名",里程,偏距)。注意函數中工作表名要用英文雙引號引起來。主程序實現以下功能：

(1)判斷用戶輸入的里程、偏距和工作表名(即曲線要素數據庫表名，下同)，提取相應曲線要素。實現代碼[9-10]：

nCount = Sheets(WorksheetName).Cells(3, 10).Value

Fori= 1 TonCount

If Mileage >= Sheets(WorksheetName).Cells(i+ 5, 2).Value And Mileage <= Sheets(WorksheetName).Cells(i+ 5, 3).Value Then

ForJ= 1 To 9

quxian(J) = Sheets(WorksheetName).Cells(i+ 5,J+ 1).Value

NextJ

Nexti

(2)調用坐標正算子程序計算，返回結果至單元格。

2.3 坐標正算子程序

坐標正算子程序采用5節點Gauss-Legendre通用公式，中樁坐標計算實現代碼：

r(0) = 0.118 463 442 5:r(4) =r(0):r(1) = 0.239 314 335 2:r(3) =r(1):r(2)=0.284 444 444 4

v(0) = 0.046 910 077:v(4) = 1 -v(0):v(1) = 0.230 765 344 9:v(3) = 1 -v(1):v(2) = 0.5

Pi1 = Application.WorksheetFunction.pi()

Length1=Abs(MyMileage1-MyArray1(1))

resultsX1= MyArray1(3)

resultsY1= MyArray1(4)

Fori= 0 To 4

IntegralItem1 = MyArray1(5) + MyArray1(9) * (v(i) * Length1 / MyArray1(7) +Length1 ^ 2 * v(i) ^ 2 * (1 / MyArray1(8) - 1 / MyArray1(7)) / (2 * MyArray1(6)))

resultsX1 = resultsX1+ Length1 *r(i) * Cos(IntegralItem1)

resultsY1= resultsY1+ Length1 *r(i) * Sin(IntegralItem1)

Nexti

2.4 線路切線方位角計算程序

定義主程序函數：ZSA("工作表名",里程)。在函數中判斷用戶輸入的里程和工作表名，提取曲線要素，調用切線方位角子程序進行計算，將結果返回至單元格。切線方位角子程序實現代碼：

Length1 = Abs(MyMileage1 - MyArray1(1))

Azimuth1 = MyArray1(5) + MyArray1(9) * (Length1 / MyArray1(7) + (1 / MyArray1(8) - 1 / MyArray1(7)) * Length1 ^ 2 / (2 * MyArray1(6)))

2.5 坐標反算程序

定義函數名：FSSlantDistance("工作表名",大概里程,X坐標,Y坐標)。坐標反算程序用于根據坐標反算里程、偏距，為防止因為多值問題(多由回頭曲線、水滴型曲線等特殊曲線線型產生)，需用戶提供大概里程(如無多值問題，大概里程可以隨意賦值，只要不超過線路里程范圍即可)。采用迭代法計算，以大概里程為里程初始值，以用戶輸入的坐標到大概里程的中樁坐標所在線路法線的距離為迭代值，迭代直至該距離為無窮小。實現過程如下：

(1)根據用戶輸入的大概里程ProbablyMileage，調用坐標正算程序，求得該里程中樁坐標CalX、CalY和切線方位角，并計算出切線方位角的法向方位角VerticalAzimuth。

(2)計算待求點(X,Y)到點(CalX、CalY)沿VerticalAzimuth方向的距離DeltaSlantDistance。實現代碼：

DeltaSlantDistance = (Y- CalY) * Cos(VerticalAzimuth) - (X- CalX) * Sin(VerticalAzimuth)

(3)判斷DeltaSlantDistance值大?。?/p>

若值足夠小，則求出點(X,Y)到點(CalX、CalY)的距離OutputSlantDistance1，返回反算結果(里程：ProbablyMileage，偏距OutputSlantDis-tance1)，跳出循環；

若值過大，以ProbablyMileage + DeltaSlantDistance作為新的大概里程，返回步驟(1)，進入下一循環。

實現代碼：

If Abs(DeltaSlantDistance) < 10 ^ (-8) Then

OutputSlantDistance1 = ((Y- CalculationY) ^ 2 + (X-CalculationX) ^ 2) ^ 0.5

A1= VerticalAzimuth + 90 * PI / 180

X1= CalculationX:Y1= CalculationY:X2 =X1+ cos(A1):Y2 =Y1+ sin(A1)

If (Y1 -Y) * (X2 -X) - (X1 -X) * (Y2 -Y) > 0 Then OutputSlantDistance1 = -OutputSlantDistance1

Else

ProbablyMileage = ProbablyMileage + DeltaSlantDistance

End If

步驟(1)、(2)、(3)均在循環體內，以上代碼省略了循環體。

至此，線路坐標正反算的程序已具備相應功能。

3 算 例

下面，以某項目的一段線路直曲表為例，驗證編寫的線路坐標正反算程序(以下稱本程序)正確性。設計提供曲線要素如表2所示。

表2 直線、曲線及轉角表

將表2的曲線要素按線元法錄入曲線要素數據庫，并存于名為“右平”的工作表中。因該線路所有線元均以相切銜接，即下一線元起點坐標、方位角為上一線元終點坐標、方位角，故在錄入要素時，部分要素可用本程序直接計算。直線段半徑為無窮大，則半徑填接近于無窮大的數即可，如1090。所有曲線要素內容如表3所示。

表3 程序曲線要素數據庫工作表數據錄入表

計算結果即為完整的曲線要素表，如表4所示。

表4 程序曲線要素數據庫工作表計算結果表

根據線元法的特性和以上曲線要素錄入方法，若某個線元出錯，則線路終點坐標、方位角必然出錯，故只需復核線路終點的坐標、方位角即可。程序計算結果與設計對比如表5所示。

由表5可知，本程序計算結果與設計幾乎一致，其誤差因設計《直曲表》精度低于本程序小數點保留位數引起。

表5 程序曲線要素數據庫計算結果復核表

下面以里程K40+900右偏5 m為例，驗證程序計算邊樁坐標正確性。正算中樁坐標、方位角：X=4 102.790 0;Y=8 996.693 8;α=167.305 173 179 975°。手算與本程序計算結果對比如表6所示。

表6 程序邊樁坐標正算計算結果復核表

由表6可知，邊樁坐標正算結果正確無誤。下面用坐標正算程序來驗證坐標反算。其對比表如表7所示。

表7 程序坐標反算計算結果復核表

由表7可知，坐標反算的里程、偏距與正算時賦值一致，故坐標反算程序計算正確。

筆者在多個項目的應用中，也驗證了本程序計算正確、精度達到施工要求。

4 結 語

隨著科技的進步，測量儀器越來越先進，電腦與儀器的交互功能越來越強大、方便。本程序最大的優勢在于：(1)通過電腦計算的線路坐標，按照儀器所需的格式進行編排，可方便的導入儀器；(2)從儀器導出的數據可在電腦上按施工和竣工要求進行更多數據編輯處理任務；(3)特別是在現代測量過程中，坐標計算、數據處理、格式轉換都需要與Office軟件相結合，以達到數據流無阻礙的流動，形成我們需要的各種數據報表和應用格式?；贓xcel-VBA自定義函數的公路線路坐標正反算程序，采用Gauss-Legendre通用公式來統一計算模型，界面簡潔明了，通俗易懂；可以計算直線、圓曲線、緩和曲線甚至包括不完全緩和曲線等類型線元，覆蓋了工程施工過程中所有的線路形式，功能強大、計算精度高。將計算程序轉為Excel自定義函數，與Excel自身功能融為一體，可隨意調用，便于排版，便于與其他軟件、儀器交互；簡潔的函數輸入，避免了普通線路坐標計算程序繁瑣的操作過程，加快了操作速度?；贓xcel-VBA自定義函數的公路線路坐標正反算程序，可以給測量人員、施工管理人員節省時間，提高工作效率，改善程序使用體驗，適合于目前工程測量中數據的日常計算和批量處理。