EXCEL輔助計算公路中樁與邊樁參數(shù)的應用

申請免費試用、咨詢電話：400-8352-114

簡介： 在現(xiàn)今的道路橋梁建設中， 全站儀以其高精度、高效率的特點，在測量放樣中得到廣泛的應用，它將傳統(tǒng)的二維測量提升到了三維測量的高度。但大量的坐標計算加重了測量人員的工作強度，也影響了全站儀的使用效率。本人根據(jù)《公路工程施工測量》提供的FX-4500程序，利用EXCEL函數(shù)與邏輯循環(huán)語來計算公路中樁及邊樁參數(shù)，大大提高了計算的可靠性和速度。
關鍵字：EXCEL 計算 公路中樁 公路邊樁

一、前言

在當前的高速公路施工中，全站儀以其高精度、使用簡便的特點在施工測量放樣中得到廣泛的應用。它的使用還有一個意義，它將測量從傳統(tǒng)的二維測量提升到三維測量的高度，使測量效率大為提高。全站儀使用時需計算大量的坐標，坐標的正確和精度直接關系到測量結果的準確度；另外，對于現(xiàn)場計算的坐標，能否迅速的得出結果也會影響全站儀的使用效率，所以準確、快速的坐標計算也是全站儀使用中的關鍵一環(huán)。

坐標的計算涉及到大量的數(shù)據(jù)和公式，計算時極易出錯。測量中使用最多、計算最為復雜的是公路中樁、邊樁坐標和方位角，其他的施工測量都是以此作為參照，經(jīng)過簡單運算即可得結果。現(xiàn)測量員普遍采用可編程計算器幫助計算，使用前需根據(jù)不同的計算器特性輸入程序，計算時根據(jù)不同的路段選擇不同的程序輸入?yún)?shù)，逐個得到坐標或方位角，還得反復校核。這對于大批量計算來說是一個不小的工程。

現(xiàn)在，電腦在施工單位已得到使用普及，在工程量及數(shù)測量數(shù)據(jù)處理過程中，Office中EXCEL經(jīng)常是測量技術人員利用的工具。本人根據(jù)本人從2002年至2005年的利用電腦處理測量資料的經(jīng)驗結合EXCEL的強大功能，總結出了用電腦計算公路中樁、左側、右側坐標的可行方法，并利用EXCEL的邏輯語句編制了一個小的應用程序。希望能把測量人員從重復性的勞動中解脫出來。

二、工作原理

1.應用程序的目標

上文已講到施工測量中使用最多、計算最為復雜的是公路中線逐點坐標和切線方位角，既中樁的X值、Y值。EXCEL的主要目標就是計算公路中線的中樁參數(shù)。

2．源程序

L1  F：N：G：R：U：Q：J：K起算要素

L2  Defm  4 擴大內存

L3  M=I/2-I^3/240/R^2  M為加設緩和曲線后使切線增長的距離

L4  P=I^2/24/R-I^4/2688/R^3  P為加設緩和曲線，圓曲線相對于切線的內移量

L5  E=（R+P）/cos(N/2)-R  E外矢距在EXCEL中角度默認為弧度。

L6  L=πRN/180+I  L為曲線長含緩和段

L7  T=M+（R+P）tan（N/2）切線長

L8  A=Q-T：B=A+I：D=A+L：C=D-I  ZH：HY：HZ：YH里程樁號

L9   Rec（T，F+180） ZH點坐標增量計算

L10  Z[1]=V+J：Z[2]=W+K  ZH點坐標（V，W坐標增量，計算機內部運算）

L11  Rec（T，F+GN） HZ點坐標增量計算

L12  Z[3]=V+J：Z[4]=W+K  HZ點坐標

LBI  θ

L14  {H，S，E}  待點要素

L15  H<A＝＞GOTO  1 第一段直線上任一點坐標計算

L16  ≠＞H<B＝＞GOTO  2 第一段緩和曲線上任一點坐標計算

L17  ≠＞H<C＝＞GOTO  3 圓曲線上任一點坐標計算

L18  ≠＞H<D＝＞GOTO  4 第二段緩和曲線上任一點坐標計算

L19  ≠＞GOTO  5　第二段直線上任一點坐標計算

L20  LBI  I  第一段直線坐標計算開始

L21  Rec（Q-H，F+180） 中樁坐標增量計算

L22  X=V+J：Y=W+K  中樁坐標

L23  X=X+V：“X”=◢

L24  Y=Y+W：“Y”=◢第一段直線上任一點坐標計算結果

L25  GOTO θ 

L26  LbI  2第一段緩和曲線坐標計算開始

L27  Z=H-A所求點到ZH距離

L28  O=90Z^2/R/I/π 所求點的方位角

L29 X=Z-Z^5/40/R^2/I^2+Z^9/3456/R^4/I^4

L30  Z=Z^6/6/R/I-Z^7/336/R^3/I^3+Z^11/42440/R^5/I^5 第一段緩和曲線上任一點切線支距法坐標計算

L31  LBI  6  第一段緩和曲線、圓曲線坐標換算計算

L32  Rec（X，F）

L33  X=Z[1]+V：Y=Z[2]+W

L34  Rec（X，F+90G）

L35  X=X+V：Y=Y+W

L36  LbI  3  圓曲線上任一點切線支距法計算開始

L37  Z=H-A-I/2

L38  O=180Z/R/π Z所對中心角

L39  X=Z-Z^3/6/R^2+Z^5/120/R^4+M

L40  Z=Z^2/2/R-Z^4/R^3/24+Z^6/720/R^5+P

L41  GOTO 6

L42  LbI  4  第二段緩和曲線計算開始

L43  Z=D-H到HZ點長度

L44  O=90Z^2/R/I/π Z所對中心角

L45  X=Z-Z^5/40/R^2/I^2+Z^9/3456/R^4/I4

L46  Z=Z^3/6/R/I-Z^7/336/R^3/I^3+Z^11/42240/R^5/I^5

L47  Rec（X，F+GN+180）

L48  X=Z[3]+V：Y=Z[4]+W

L49  Rec（X，F+GN+180-90G）

L50  X=X+V：Y=Y+W

L51  X=X+V：“X”=◢

L52  Y=Y+V：“Y”=◢

L53  GOTO θ

程序中符號說明

F-第一直線段正方位角（即后視切線上任一點（如ZH）至交點的方位角；

N—交點轉向角（即偏角）；右偏角為正，左偏角為負，輸入轉向角時不得輸入“+、-”

G—條件（左偏輸入-1，右偏輸入+1）；

R—半徑；

I—緩和曲線長度（m）（圓曲線I輸入0）；

Q—交點里程樁號；

J—交點X坐標值；

K—交點Y坐標值；

H—所求點里程樁號；

X、Y—所求點X、Y坐標值。

3．基本思路

本文基本思路是在EXECEL表格中建立若干個工作表，利用EXECL的邏輯循環(huán)語句（IF，VLOOKUP）與函數(shù)計算公式把這些工作表鏈接一起。本文利用蒲石河對外永久公路（K1+200-K2+100段）中樁及邊樁計算做出EXECL實例（路寬10米，路基8米，路肩2米），下面以此展開敘述：

⑴、首先在電子表中建立9個工作表，分別命名為基本資料、JD7、JD8、JD9、JD10、JD11、JD12、JD13、成果。

⑵、a首先在基本資料工作表中輸入如下表相對的數(shù)據(jù)。在電子表格中，三角函數(shù)計算以弧度為單位，所以角度值必須在單元格中必須利用pi()/180在角度與弧度的轉換。在轉向角輸入時，向左轉時在G對應的單元格中輸入-1，向右轉時在G對應的單元格中輸入1（如表1）。方位角所對應的數(shù)據(jù)可以直接從設計提供的參數(shù)表輸入，另外可以用幾個單元格鏈接在一起計算，在表2中利用9個單元格鏈接起來計算。例如利用JD7與JD6所對應的XY值相減，得出JD7所對行數(shù)據(jù)的△X△Y值，在JD7所對應行的反切值1數(shù)據(jù)中輸入公式=IF(E4="","",IF(F4=0,"",ATAN(G4/F4)))，在JD7所對應行的反切值2數(shù)據(jù)中輸入公式=IF(E4="","",IF(G4=0,"",ATAN(F4/G4)))，在JD7所對應行的數(shù)據(jù)1中輸入公式=IF(AND(F4>0,G4=0),0,(""))，在JD7所對應行的數(shù)據(jù)2中輸入公式=IF(AND(G4>0,F4>0),H4,IF(AND(G4>0,F4=0),PI()/2,IF(AND(G4>0,F4<0),PI()/2-I4,(""))))，在JD7所對應行的數(shù)據(jù)3中輸入公式IF(AND(F4<0,G4=0),PI(),(""))，在JD7所對應行的數(shù)據(jù)4輸入公式=IF(AND(G4<0,F4<0),PI()+H4,IF(AND(F4=0,G4<0),1.5*PI(),IF(AND(G4<0,F4>0),1.5*PI()-I4,(""))))，在JD7所對應行數(shù)據(jù)方位角弧度中輸入公式=IF(AND(F4>0,G4=0),J4,IF(G4>0,K4,IF(AND(G4=0,F4<0),L4,IF((G4<0),M4,("")))))。

表1

表2

b利用已輸入的基本資料數(shù)據(jù)，計算出如下表表3對應的單元格的數(shù)據(jù)?，F(xiàn)以JD7所對應行做公式敘述。在M所對應單元格輸入公式IF((OR(AD4=0,AE4=0))," ",AE4/2-AE4^3/240/AD4^2)，在P所對應單元格輸入公式IF(AD4=0,"",AE4^2/24/AD4-AE4^4/2688/AD4^3)，在E所對應單元格輸入公式IF(OR(D4="",E4=""),"",(AD4+AH4)/COS(AC4/2)-AD4)，在L所對應單元格輸入公式IF(OR(D4="",E4=""),"",PI()*AD4*AC4/PI()+AE4)，在T所對應單元格輸入公式IF(OR(D4="",E4=""),"",IF(AG4="",0,AG4+(AD4+AH4)*TAN(AC4/2)))，在ZH所對單元格輸入公式IF(OR(D4="",E4=""),"",C4-AK4)，在HY所對應單元格輸入公式IF(OR(D4="",E4=""),"",AL4+AE4)，在QZ所對應單元輸入公式IF(OR(D4="",E4=""),"",AM4/2+AO4/2)，在YH所對應單元格輸入公式IF(OR(D4="",E4=""),"",AP4-AE4)，在HZ所對應單元格輸入公式IF(OR(D4="",E4=""),"",AL4+AJ4)，在BC所對單元格輸入步長。

表3

c．以上基本資料工作表以JD7所對應行做例子，JD8至JD13所對應行輸入公式采用電子表格中的編輯欄中的向下填充功能，向下填充公式。

⑶.然后利用源程序開始在工作表JD7、JD8、JD9、JD10、JD11、JD12、JD13中進行編單元格?，F(xiàn)在以JD7所對應的工作表進行實例敘述。工作表在計算過程中只能上一個交點的HZ點到本交點的HZ點間的數(shù)據(jù)進行計算。

a、例如表4，在工作表的第一個單元格輸入數(shù)字7（與JD7所對應的數(shù)字7一致），然后把JHZH、JDX、JDY、R、I、P、E、F、G、N、M、BC所對單元格參數(shù)與基本資料所對應的單元格參數(shù)鏈接起來，加寬值輸入設計加寬值，常數(shù)輸入為從路肩開始橫向增加的寬度。

表4

b、例如表5，樁號（ZH-HZ）所對應的單元格中分別輸入公式VLOOKUP(A1,基本資料!$A$3:$AQ$32,38,)、VLOOKUP(A1,基本資料!$A$3:$AQ$32,39,)、VLOOKUP(A1,基本資料!$A$3:$AQ$32,40,)、VLOOKUP(A1,基本資料!$A$3:$AQ$32,41,)、VLOOKUP(A1,基本資料!$A$3:$AQ$32,42,)。在ZH所對應的參數(shù)單元格輸入公式IF(AND(A8>=A$7,A8<=A$8),A$3-A8,IF(AND(A8>=A$8,A8<=A$9),A8-A$8,IF(AND(A8>=A$9,A8<=A$11),(A8-A$8-E$3/2),IF(AND(A8>=A$11,A8<=A$12),A$12-A8,""))))，利用編輯工具欄中向下填充公式。在ZH點與HZ點對應的X、Y單元格中分別輸入公式B3+F3*COS(A5+PI())、C3+F3*SIN(A5+PI())、B3+F3*COS(A5+B5*C5)、C3+F3*SIN(A5+B5*C5)，首先計算出ZH點與HZ點所對應的X、Y坐標。在ZH點所對應的切線方位角單元格IF(A8<=A$8,A$5,IF(AND(A8>=A$8,A8<=A$9),0.5*B8^2/D$3/E$3*B$5+A$5,IF(AND(A8>=A$9,A8<=A$11),B8/D$3*B$5+A$5,IF(AND(A8>=A$11,A8<=A$12),0.5*B8^2/D$3/E$3*(-B$5)+A$5+B$5*C$5,IF(A8>=A$12,A$5+B$5*C$5)))))，然后利用向下填充功能把公式向下填充，計算相對應的樁號在切線方向的方位角。在HY點所對應的X、Y單元格中輸入相應公式IF(AND(A9>=A$7,A9<=A$8),ROUND(B9*COS(A$5+PI())+B$3,3),IF(AND(A9>=A$8,A9<=A$9),ROUND(C$8+COS(A$5)*(B9-B9^5/40/D$3^2/E$3^2+B9^9/3469/D$3^4/E$3^4)+COS(A$5+PI()/2*B$5)*(B9^3/6/D$3/E$3-B9^7/336/D$3^3/E$3^3+B9^11/42240/D$3^5/E$3^5),3),IF(AND(A9>=A$9,A9<=A$11),ROUND(C$8+COS(A$5)*(B9-B9^3/6/D$3^2+B9^5/120/D$3^4+D$5)+COS(A$5+PI()/2*B$5)*(B9^2/2/D$3-B9^4/D$3^3/24+B9^6/720/D$3^5+I$3),3),IF(AND(A9>=A$11,A9<=A$12),ROUND(C$12+COS(A$5+B$5*C$5+PI())*(B9-B9^5/40/D$3^2/E$3^2+B9^9/3469/D$3^4/E$3^4)+COS(A$5+PI()+B$5*C$5-PI()/2*B$5)*(B9^3/6/D$3/E$3-B9^7/336/D$3^3/E$3^3+B9^11/42240/D$3^5/E$3^5),3)," "))))與IF(AND(A9>=A$7,A9<=A$8),ROUND(B9*SIN(A$5+PI())+C$3,3),IF(AND(A9>=A$8,A9<=A$9),ROUND(D$8+SIN(A$5)*(B9-B9^5/40/D$3^2/E$3^2+B9^9/3469/D$3^4/E$3^4)+SIN(A$5+PI()/2*B$5)*(B9^3/6/D$3/E$3-B9^7/336/D$3^3/E$3^3+B9^11/42240/D$3^5/E$3^5),3),IF(AND(A9>=A$9,A9<=A$11),ROUND(D$8+SIN(A$5)*(B9-B9^3/6/D$3^2+B9^5/120/D$3^4+D$5)+SIN(A$5+PI()/2*B$5)*(B9^2/2/D$3-B9^4/D$3^3/24+B9^6/720/D$3^5+I$3),3),IF(AND(A9>=A$11,A9<=A$12),ROUND(D$12+SIN(A$5+B$5*C$5+PI())*(B9-B9^5/40/D$3^2/E$3^2+B9^9/3469/D$3^4/E$3^4)+SIN(A$5+PI()+B$5*C$5-PI()/2*B$5)*(B9^3/6/D$3/E$3-B9^7/336/D$3^3/E$3^3+B9^11/42240/D$3^5/E$3^5),3)," "))))。在公路中樁HY、QZ、YH及ZH點以下所對應的X、Y單元格填充以上公式，利用以公式計算出中樁坐標。ZH點以下所對樁號內輸入上一個交點處HZ點樁號，在與其相鄰的遞增樁號取5的倍數(shù)，例如在表5中樁號1100以下的樁號中輸入公式A14+E$5，E$5所表示的步長為5。在左、右側任意值分別輸入寬度所對公式IF(B$5>0,5,IF(A8<=A$8,5,IF(AND(A8>=A$8,A8<A$9),(A8-A$8)/E$3*F$5+5,IF(AND(A8>=A$9,A8<A$11),5+F$5,IF(AND(A8>=A$11,A8<A$12),ABS(A8-A$12)/E$3*F$5+5,IF(A8>=A$12,5))))))+I$5、IF(B$5<0,5,IF(A8<=A$8,5,IF(AND(A8>=A$8,A8<A$9),(A8-A$8)/E$3*F$5+5,IF(AND(A8>=A$9,A8<A$11),5+F$5,IF(AND(A8>=A$11,A8<A$12),ABS(A8-A$12)/E$3*F$5+5,IF(A8>=A$12,5))))))+I$5，利用EXCEL的相下填充功能，向下填充公式，計算左、右側的任意寬度。在ZH所對應左側任側任意值X、Y單元格內分別輸入公式C8+I8*COS(F8-PI()/2)與公式D8+I8*SIN(F8-PI()/2)，在ZH所對應右側任意值X、Y單元格內分別輸入公式C8+T8*COS(F8+PI()/2)與D8+T8*SIN(F8+PI()/2)，同樣利用向下填充功能復制單元格公式。

表5

c．利用上述方式，在工作表JD8-JD13編輯公式。

⑷工作表成果得出所有在K1+100-K2+100段的中樁及左右側任意點坐標。參數(shù)欄所對應值為從路肩橫向增加值（變換常數(shù)）。例如在樁號1100所對應行，左側任意值X、Y單元格輸入公式IF(AND(B4>=1100,B4<=1250),VLOOKUP(B4,JD7!A:V,10,FALSE),IF(AND(B4>=1255,B4<=1360),VLOOKUP(B4,JD8!A:V,10,FALSE),IF(AND(B4>=1365,B4<=1585),VLOOKUP(B4,JD9!A:V,10,FALSE),IF(AND(B4>=1590,B4<=1760),VLOOKUP(B4,JD10!A:V,10,FALSE),IF(AND(B4>=1765,B4<=1875),VLOOKUP(B4,JD11!A:V,10,FALSE),IF(AND(B4>=1880,B4<=2030),VLOOKUP(B4,JD12!A:V,10,FALSE),IF(AND(B4>=2035,B4<=2105),VLOOKUP(B4,JD13!A:V,10,FALSE),"")))))))與IF(AND(B4>=1100,B4<=1250),VLOOKUP(B4,JD7!A:V,11,FALSE),IF(AND(B4>=1255,B4<=1360),VLOOKUP(B4,JD8!A:V,11,FALSE),IF(AND(B4>=1365,B4<=1585),VLOOKUP(B4,JD9!A:V,11,FALSE),IF(AND(B4>=1590,B4<=1760),VLOOKUP(B4,JD10!A:V,11,FALSE),IF(AND(B4>=1765,B4<=1875),VLOOKUP(B4,JD11!A:V,11,FALSE),IF(AND(B4>=1880,B4<=2030),VLOOKUP(B4,JD12!A:V,11,FALSE),IF(AND(B4>=2035,B4<=2105),VLOOKUP(B4,JD13!A:V,11,FALSE),"")))))))，在中樁坐標所對應的X、Y單元格輸入公式IF(AND(B4>=1100,B4<=1250),VLOOKUP(B4,JD7!A:V,3,FALSE),IF(AND(B4>=1255,B4<=1360),VLOOKUP(B4,JD8!A:V,3,FALSE),IF(AND(B4>=1365,B4<=1585),VLOOKUP(B4,JD9!A:V,3,FALSE),IF(AND(B4>=1590,B4<=1760),VLOOKUP(B4,JD10!A:V,3,FALSE),IF(AND(B4>=1765,B4<=1875),VLOOKUP(B4,JD11!A:V,3,FALSE),IF(AND(B4>=1880,B4<=2030),VLOOKUP(B4,JD12!A:V,3,FALSE),IF(AND(B4>=2035,B4<=2105),VLOOKUP(B4,JD13!A:V,3,FALSE),"")))))))與IF(AND(B4>=1100,B4<=1250),VLOOKUP(B4,JD7!A:V,4,FALSE),IF(AND(B4>=1255,B4<=1360),VLOOKUP(B4,JD8!A:V,4,FALSE),IF(AND(B4>=1365,B4<=1585),VLOOKUP(B4,JD9!A:V,4,FALSE),IF(AND(B4>=1590,B4<=1760),VLOOKUP(B4,JD10!A:V,4,FALSE),IF(AND(B4>=1765,B4<=1875),VLOOKUP(B4,JD11!A:V,4,FALSE),IF(AND(B4>=1880,B4<=2030),VLOOKUP(B4,JD12!A:V,4,FALSE),IF(AND(B4>=2035,B4<=2105),VLOOKUP(B4,JD13!A:V,4,FALSE),"")))))))，在右側任意值所對應的X、Y單元格中輸入公式IF(AND(B4>=1100,B4<=1250),VLOOKUP(B4,JD7!A:V,21,FALSE),IF(AND(B4>=1255,B4<=1360),VLOOKUP(B4,JD8!A:V,21,FALSE),IF(AND(B4>=1365,B4<=1585),VLOOKUP(B4,JD9!A:V,21,FALSE),IF(AND(B4>=1590,B4<=1760),VLOOKUP(B4,JD10!A:V,21,FALSE),IF(AND(B4>=1765,B4<=1875),VLOOKUP(B4,JD11!A:V,21,FALSE),IF(AND(B4>=1880,B4<=2030),VLOOKUP(B4,JD12!A:V,21,FALSE),IF(AND(B4>=2035,B4<=2105),VLOOKUP(B4,JD13!A:V,21,FALSE),"")))))))與IF(AND(B4>=1100,B4<=1250),VLOOKUP(B4,JD7!A:V,22,FALSE),IF(AND(B4>=1255,B4<=1360),VLOOKUP(B4,JD8!A:V,22,FALSE),IF(AND(B4>=1365,B4<=1585),VLOOKUP(B4,JD9!A:V,22,FALSE),IF(AND(B4>=1590,B4<=1760),VLOOKUP(B4,JD10!A:V,22,FALSE),IF(AND(B4>=1765,B4<=1875),VLOOKUP(B4,JD11!A:V,22,FALSE),IF(AND(B4>=1880,B4<=2030),VLOOKUP(B4,JD12!A:V,22,FALSE),IF(AND(B4>=2035,B4<=2105),VLOOKUP(B4,JD13!A:V,22,FALSE),"")))))))，在切線方位角所對應的單元格中輸入公式IF(AND(B4>=1100,B4<=1250),VLOOKUP(B4,JD7!A:V,8,FALSE),IF(AND(B4>=1255,B4<=1360),VLOOKUP(B4,JD8!A:V,8,FALSE),IF(AND(B4>=1365,B4<=1585),VLOOKUP(B4,JD9!A:V,8,FALSE),IF(AND(B4>=1590,B4<=1760),VLOOKUP(B4,JD10!A:V,8,FALSE),IF(AND(B4>=1765,B4<=1875),VLOOKUP(B4,JD11!A:V,8,FALSE),IF(AND(B4>=1880,B4<=2030),VLOOKUP(B4,JD12!A:V,8,FALSE),IF(AND(B4>=2035,B4<=2105),VLOOKUP(B4,JD13!A:V,8,FALSE),"")))))))，利用向下填充功能就得出樁號5米遞增的坐標與方位角。

表6

三、總結

本文的工作原理基于計算機常規(guī)軟件EXCEL的應用，提高工程測量內業(yè)計算速度與準確度，充分發(fā)揮了現(xiàn)有辦公設備的功能，極大地提高了工作效率。