ExcelVBA自定義函數(shù)計算線路坐標反算方法

中圖分類號：U452 文獻標志碼：A 文章編號：2095-2945（2025）18-0158-04

Abstract：SurveyingofLinearEngineeringProjectstypicallyinvolvestheseting-outandmappingoflongitudinalandcros sectionsforlinearbuildings（structures）suchashighways，railways，subways，andpowertransmisionlines.Thealignmentofsuch projectsisneverastraightlinebutratheracombinationofmultiplestraightsegmentsandcurves.Insubwayalignments， horizontalcurvesaregenerallcomposedofstraightlines，circularcurves，andtranstion（spiral）curves.Thispaperintroducesa customfunctionforcoordinateinversecalculation（ie.，deteminingchainagefromcordinates）basedontheintersectionpoint method，usingaovetioalaligentonsistingofaightlinsrasitiocvesdicularurves.Timproveiecy numeroussoftware tolsareavailable today.Drawingonnearlyadecadeofexperienceinsubwaytunnelconstructionand operationalmonitoring，thisstudyleveragesExcelVBAforsecondarydevelopmenttoexpeditedataprocesing.AcustomExcel functionwasdeveloped，takingthealignmentparameters（basedontheintersectionpointmethod）asinputs.Byinputing measuredpointcordinates，thefunctionautomaticallyidentifiesthecorespondingalignmentsegment（straight，transition，or circularcurve）andcomputesthechainage.SinceExcelfunctionsareuser-friendlyandallowbatchprocessingviacopy-paste operations，thismethodenablesbatchcalculationofchainagesfromcoordinatedata.Thisapproachalsofacilitatessubsequent tasks such as cross-section ploting and overbreak/underbreak analysis in tunnel construction.

Keywords：Excel VBA programming;coordinate-to-chainage inversecalculation;automatic inversecomputation;custom functions;batch processing

簡單。

對于大多數(shù)從事線路工程測量的人員來說，平曲線測量儀器主要有全站儀或GNSS（全球導航衛(wèi)星系統(tǒng)），而地鐵線路上平曲線基本上是有直線、圓曲線、緩和曲線3部分組成，對于實測坐標反算對應線路里程位置，還是大量依賴專業(yè)測量軟件或者在線路CAD圖紙上展點量距。而Excel軟件仍然是眾多內業(yè)數(shù)據處理人員最受歡迎的軟件之一，若把坐標反算編寫成公式，就像調用Excel基礎公式一樣，處理數(shù)據將變得

一般繪制線路縱/橫斷面圖是一件工作量很大的工作，每個斷面有很多碎部點，每個碎部點都需要計算它對應的線路中線點里程及中心坐標，以便計算碎部點相對于線路中心的偏距。為了使自己的工作效率提高，省去大量碎部點在CAD圖紙展點、量距。特編寫了自定義函數(shù)公式：線路坐標反算。通過Excel本身的填充功能，實現(xiàn)了碎部點批量坐標反算的工作，為每個碎部點計算偏距大大節(jié)約了時間，使工作效率成倍提高。但由于坐標反算需要用到坐標正算的公式，而坐標正算自定義函數(shù)筆者之前發(fā)表過一篇相關論文《ExcelVBA自定義函數(shù)批量計算隧道線路坐標正算方法》，本文不再重點寫坐標正算公式編寫方法，將著重寫坐標反算函數(shù)編寫方法和流程，希望對以后編程線路坐標反算測量人員提供一個思路

1坐標反算函數(shù)程序編寫流程

編寫線路坐標反算公式的流程大致可細分為以下步驟： ① 采用交點法設定線路基礎參數(shù)； ② 利用自定義函數(shù)實現(xiàn)參數(shù)的引用； ③ 構建線路關鍵節(jié)點參數(shù)的列表； ④ 智能化判定坐標點所歸屬的線段并計算對應里程樁號； ⑤ 通過函數(shù)輸出計算得出里程樁號。

1.1設計平曲線的基礎參數(shù)

在平曲線參數(shù)的總體設計中，力求簡化線路的平面參數(shù)，通過交點法對線路曲線參數(shù)進行計算。平面參數(shù)的設計內容涉及：交點的命名 ?X 軸坐標、Y軸坐標、緩和曲線的參數(shù)、圓曲線的數(shù)據及起始點的里程。鑒于平面參數(shù)的重要性，必須對包含這些參數(shù)的文件進行保護，以避免不慎刪除或修改。

1.2實施坐標反算函數(shù)的參數(shù)調用

函數(shù)的調用過程分為2個主要部分： ① 對平曲線基礎參數(shù)的調用，利用數(shù)組提取參數(shù)部分的所有數(shù)據；② 對測量點坐標的調用，通過自定義函數(shù)為變量賦值，提取計算所需的坐標 X 和坐標Y，建議坐標參數(shù)數(shù)據類型使用Double雙精度浮點型以確保計算精度。

1.2.1 配置線路參數(shù)

把交點的 X 軸坐標與 Y 軸坐標組合，存人二維數(shù)組 arr1（X 坐標， Y 坐標）。再將初始緩和曲線、標準圓曲線、終極緩和曲線的數(shù)據存入另一二維數(shù)組 arr2 1 Ls1 值， R 值， Ls2 值）。同時，把起始點里程值指定給變量 d 。

1.2.2引用輸入坐標變量參數(shù)

把輸人函數(shù)所提供的 X 坐標值分配給變量 x ，并將輸入函數(shù)的 Y 坐標值分配給變量 y 。

1.3生成線路關鍵節(jié)點參數(shù)集

在自動識別坐標點所屬的線元段落之前，必須先將整條線路劃分為多個段落，并詳盡記錄每個線元的里程位置、坐標點、方位角、線路類型（如圓曲線、直線、緩和曲線）線路方向以及曲線的相關要素（包括切線長度、圓曲線的長度、曲線總長度、切線與曲線的差距、外矢距、圓心位置坐標、轉向角度和方位線之間的夾角等）。接著，計算每個節(jié)點的里程與坐標，并將這些數(shù)據保存在相應的數(shù)組里。

1.3.1 測定相鄰交點間距與方向角

依據 arr1（X，Y） 坐標數(shù)組，推算出每對連續(xù)交點之間的方向角以及它們之間的距離，并將這些數(shù)據存人 （方向角，距離長度）數(shù)組中。

1.3.2 確定線路偏轉方向

采用迭代方式求得每一段線路的偏轉方向，并將結果保存在 arr4 （線路偏轉方向“-1為左偏， +1 為右偏”，相鄰交點方向角差的絕對值，相鄰方向角差值）數(shù)組內。若2個相鄰交點的方向角差絕對值等于方向角差值，即 |a_i+1-a_i|=（a_i+1-a_i） ，其中 α_a 代表交點間線段的方位角，表明線路右偏，否則線路左偏。

1.3.3 求取曲線參數(shù)

利用圓曲線基本要素的公式，依次計算每條曲線的相關參數(shù)，并將結果記錄于 arr5 （切線長度，圓曲線長度，曲線總長度，切線與曲線差，外矢距離，圓心 X 坐標，圓心Y坐標）數(shù)組中。圓曲線內移值 p ，切線增量 q ，交點轉角 Δ ，切線長 T ，圓曲線長 L_y ，曲線長 L ，切曲差 J， ，外矢距 E 。

圓心坐標推算如下： β=180^°-|a_i+1-a_i| ，方位角 a_JD-0= a_i±（180^°-β/2） ，圓心在線路左側“-”，圓心在線路右側4 。從 arr1（X，Y） 提取交點 W 點坐標，有交點 JD 坐標和方位角 a_JD-0 ，即可得出圓心坐標。

1.3.4對各節(jié)點進行里程計算

將里程、節(jié)點橫坐標以及縱坐標存入數(shù)組arr6中。涉及整條線路的所有節(jié)點里程（包括起始點、直緩點 ZH. 緩圓點 HY 、圓緩點 YH 緩直點 HZ 以及終點）。首先根據每個頂點的調整后里程（頂點里程減去切線長度）來確定每個ZH點與 HZ 點的里程，隨后利用ZH點里程加上或減去緩和曲線長度 Ls ，推算出每個HY點或YH點的里程。通過應用線路坐標的計算公式，確定每個節(jié)點的對應橫縱坐標。在僅存在圓曲線的線路轉向情況下，將圓曲線視作緩和曲線長度為零的特殊復合曲線處理；通過這種方式，可以完整地確定每個節(jié)點的里程與坐標。節(jié)點里程的計算是核心步驟，首要任務是算出每個交點JD1、JD2… 的切曲差，以交點數(shù)量為循環(huán)的次數(shù)，進而得出整條線路所有節(jié)點的里程。合并上述4個數(shù)組（ 至 arr6 ），它們共同構成了線路節(jié)點參數(shù)的

數(shù)據列表。

1.4判斷測量坐標點所在線元并反算里程

1.4.1判斷測量的坐標點 （x，y） 所在線段

先計算測量點坐標位于哪個線段，再按順序依次判斷落在該線段中的直線段、圓曲線段、緩和曲線段。最后根據所在線段相應的公式反算出線路里程。

計算測量點坐標位于哪個線段。計算測量點在每條線段的投影長度、方向，測量點到線段的距離。根據點到直線的垂足公式，計算出測量點到每個線段的垂足 O_i 的坐標，根據復數(shù)運算法則，計算每個線段起點與該線段的垂足坐標組成的復數(shù)計算出測量點在該線段上的投影長度、方向，根據測量點坐標與垂直點坐標組合的復數(shù)計算出測量點到線段的距離（復數(shù)的模長）和方位角（復數(shù)的幅角），根據方位角判斷測量點在線段的左側還是右側。判斷測量點坐標所在線段應滿足以下3個條件。投影點到線段起點距離為正值（與線路前進方向一致）；投影點到線元起點距離不能大于該線元長度；如果上述2個條件都滿足，就比較測量點距離哪個線段最近，即為測量點所在線段。

1.4.2 測量點坐標反算里程

1）直線段坐標反算里程。確定測量點所在線段后，先判斷是否位于直線段。測量點在直線段的投影點為P ，若 P 點位于直線段，則 P 點里程 P_L=L_i+L_p+arr5（JD1 的切曲差），并且還應滿足 arr6（JD1 中的 HZ 節(jié)點里程） ∣?P_L?arr6（JD2 中的ZH節(jié)點里程），則該測量點（Φ_x，y） 的反算里程即為 P_L ，結束程序并輸出結果。

2）圓曲線段坐標反算里程。若不滿足直線段條件，則判斷測量點位于第幾個曲線中，若 L_p1 ，則位于第一個曲線中，若 L_pgt;L_{（JD1-JD2）}-T₂ ，則位于第二個曲線中。根據測量點坐標與圓心坐標組合成復數(shù)，計算方位角（復數(shù)輻角）；圓心坐標與交點 JD 坐標組合成復數(shù)，并計算方位角（復數(shù)幅角）；2個方位角相減差值的絕對值為 δ 。若 ，則測量點位于圓曲線。若是位于第一曲線內，則測量點反算里程 P_L=arr6（JD1 中 YH 節(jié)點里程） ；若位于第二曲線內，則測量點反算里程 P_L=arr6（JD2 中 HY 節(jié)點里程） 0結束程序并輸出結果。

3）緩和曲線段坐標反算里程。若不滿足直線段和圓曲線段條件，則測量點位于緩和曲線，若位于第一曲線內，則緩和曲線位于反向完整緩和曲線；若位于第二曲線內，則緩和曲線位于正向完整緩和曲線。

通過擬合圓弧確定測量點在緩和曲線的初始位置。擬合圓弧參數(shù)確定，擬合圓弧與緩和曲線相對位置關系，過完整緩和曲線的起點 s 終點 ρ_e ，以 R^′=2R 為半徑做圓弧，該圓弧為緩和曲線的擬合圓弧，擬合圓弧和緩和曲線的交點為 ?_m ，緩和曲線在 s～m 點之間，擬合圓弧位于緩和曲線外側，緩和曲線在 m～e 點之間，擬合圓弧位于緩和曲線內側。根據 s 點和 e 點坐標，計算 ?_m 坐標，計算 s～e 的方位角 α_se 及弦長 C_se ；計算 方位角 α_m^′o^′=α_se±90^° ，最后可以計算出擬合圓弧圓心 0^′ 的坐標。根據 s 點坐標，擬合圓弧圓心 o 的坐標，測量點坐標 p（x，y） ，計算出圓心角 θ_sp ，根據計算出的圓心角 θ_sp 求出測量點垂足 p^′ 對應的擬合圓弧弧長 l_p 。 l_p=π×R×θ_sp/90 。緩和曲線的初始樁號 Z_p⁰=Z_s+l_p. （204號 。由于緩和曲線和擬合圓弧存在偏角，所以需要考垂足點初始線長的改正值，及相應參考文獻，可得

正向緩和曲線初始線長改正數(shù)為： dl=δ/（C_p0-p^-1±l/ A² ）

反向緩和曲線初始線長改正數(shù)為 ：dl=δ/（C_p0-p^-1?l/ A² ， 式中：A為緩和曲線回旋線參數(shù)， ± 與線路偏轉方向和 測量點在線路左側右側有關。右邊樁線路左偏取 ?₊，? 右邊樁線路右偏取“-”，左邊樁線路左偏取“-”，左邊 樁線路右偏取‘ ^°+ ’。

改正后的垂足點樁號為 Z_p=Z_p⁰+dl ，將 Z_p 值代人線路坐標正算公式，計算出 p 點的線路坐標，最后根據點斜式方程，計算出 p 點的垂線方程殘差 f（l_p） ，如果垂線方程殘差 f（l_p）=tan α_p（y_p-y）+（x_p+x）lt;0.001m ，則反算緩和曲線完成，線路反算坐標里程為 Z_p ，若超過 f （l_p）gt;0.001m ，則把 Z_p 值賦值給 Z_p⁰ 重新迭代計算一遍Z_p ，直到 f（l_p）lt;0.001m ，結束程序并輸出結果。

2 項目應用

2.1 自定義函數(shù)的應用

首先，將電路設計的相關參數(shù)導人指定的“參數(shù)表平面\"工作表，并進行鎖定保護。接著，在除“參數(shù)表平面\"外的Excel任意空白單元格內，輸入待測里程的坐標點，應用函數(shù)‘ ”，按回車鍵即刻獲取計算結果。這樣，每個工作簿即代表一條電路路徑，利用Excel內建的填充功能，能夠輕松實現(xiàn)坐標點的批量反算。

實例應用1：批量正算后再反算坐標點對應線路里程，如圖1、圖2所示。通過自定義函數(shù)相互正反算法，也是間接檢驗函數(shù)公式正確性。

實例應用2：通過批量計算里程點坐標展點。也可以在展點圖上，批量計算完成后，將坐標和里程批量導入CAD，批量標注線路里程文字。

實例應用3：橫斷面繪制上的應用，把自定義反算函數(shù)稍加修改，變成線路偏距函數(shù)，可以很快測量出點相于線路的偏移量，并且負值在線路左側，正值在線路右側。

2.2 自定義函數(shù)在路線坐標計算中的優(yōu)勢分析

在Excel的數(shù)據處理過程中，我們頻繁依賴于其內置的函數(shù)，如求和函數(shù)sum條件函數(shù)if等，只需輸入相應的參數(shù)即可快速獲得結果。借鑒這一便捷性，通過ExcelVBA的二次開發(fā)，成功開發(fā)出自定義函數(shù)，使得輸入特定坐標后，能夠迅速計算出相應線路的里程數(shù)。

商業(yè)軟件評測：以下是對幾款主流商業(yè)軟件的對比分析，突出各自的優(yōu)點。首先是測量員專用軟件，此軟件需在安卓設備上使用，但需注意的是，高級功能需開通會員，費用相對較高。接著是卡西歐計算器系列，如5800或9860型號，這些設備在操作時需依賴電腦軟件進行參數(shù)設置，且僅支持單點計算，手動輸入點的過程較為繁瑣。至于徠卡隧道軟件，其安裝過程需要程序和密碼狗同時進行，才能正常使用。該軟件支持全站儀數(shù)據格式的導入，但若使用其他格式數(shù)據，則需要進行復雜的數(shù)據轉換。自定義函數(shù)公式工具則是基于Excel的，無需獨立安裝程序，一個工作簿即可代表一條線路，操作便捷，支持單點和批量計算，無需排序，操作過程簡單，且能夠容納其他類型的數(shù)據，導入通用格式數(shù)據編輯也相當簡便，無需學習其他軟件的操作方法。

3結束語

在確保函數(shù)公式簡潔明了、提升工作效率的前提下，本文主要探討的是自定義函數(shù)在坐標反算程序設計中的運用技巧，探討如何使函數(shù)具備判斷與計算的能力，主要服務于線性工程中的建筑物坐標向里程的轉換計算。有興趣的同行可以基于這一思路進一步開發(fā)，比如引入線路偏差參數(shù)，計算出偏移位置的坐標，進而完成道路邊緣樁位的坐標確定。Excel作為廣受歡迎的數(shù)據處理工具，即便對于非專業(yè)測量人員而言，也易于掌握，使得測量數(shù)據處理過程更為便捷。期待本文能為讀者帶來實用價值，若文中存在疏漏或不足，懇請各位同仁不吝賜教，提出寶貴意見。

參考文獻：

[1]張正祿.工程測量[M].武漢：武漢大學出版社，2005.

[2]覃輝.CASIOfx-5800P編程計算器公路與鐵路施工測量程序[M].上海：同濟大學出版社，2009.

[3]羅剛君.ExcelVBA程序開發(fā)自學寶典[M].：電子工業(yè)出版社，2011.

[4]劉祖軍.高鐵軌道精調中的線路坐標反算原理分析[J].山東工業(yè)技術，2016（6）：93.

[5]謝江偉，賈云朋，孫青龍.ExcelVBA自定義函數(shù)批量計算隧道線路坐標正算方法[J].建筑技術，2020，51（7）：772-775.