EXCLE函數(shù)和隧道工程應(yīng)用實例

EXCLE函數(shù)和隧道工程應(yīng)用實例工程部：王亮2023/3/51一、excel內(nèi)置函數(shù)簡介

我們知道Excel的計算功能是非常強大的，這和它完善的函數(shù)系統(tǒng)是分不開的。按照用途主要可可分為：財務(wù)、日期與時間、工程、性息、邏輯、數(shù)學(xué)與三角、統(tǒng)計、數(shù)據(jù)庫、文本等等。

利用這些函數(shù)和公式，我們可以方便地在表格里編公式或程序，實現(xiàn)邏輯，數(shù)據(jù)，統(tǒng)計，等等方面的計算。下面我們就一些日常用到的簡單的函數(shù)，做一點淺顯的介紹。2023/3/521、日期與時間Excel可將日期存儲為可用于計算的序列號。默認(rèn)情況下，1900年1月1日的序列號是1，而2008年1月1日的序列號是39448，這是因為它距1900年1月1日有39448天。時間值為日期值的一部分，并用十進制數(shù)來表示（例如12:00PM可表示為0.5，因為此時是一天的一半）。

DATE(year,month,day)返回特定日期的序列號如果year位于0（零）到1899（包含）之間，則Excel會將該值加上1900，再計算年份。例如：DATE(108,1,2)將返回2008年1月2日(1900+108)。如果year位于1900到9999（包含）之間，則Excel將使用該數(shù)值作為年份。例如：DATE(2008,1,2)將返回2008年1月2日。如果year小于0或大于等于10000，則Excel將返回錯誤值#NUM!。2023/3/532、數(shù)學(xué)和三角這類函數(shù)是我們在實際工作中最常用到的。舉例：ABS(number)返回數(shù)字的絕對值。Number

需要計算其絕對值的實數(shù)。PI()返回數(shù)學(xué)常量π，精確到小數(shù)點后14位。RADIANS(angle)將角度轉(zhuǎn)換為弧度，Angle為需要轉(zhuǎn)換成弧度的角度SQRT(number)返回正平方根。其中Number

要計算平方根的數(shù)。SUMSQ(number1,number2,...)返回參數(shù)的平方和。其中Number1,number2,...

為1到255個需要求平方和的參數(shù)，也可以使用數(shù)組或?qū)?shù)組的引用來代替以逗號分隔的參數(shù)。2023/3/54TAN(number)返回給定角度的正切值，Number

為要求正切的角度，以弧度表示。FACT(number)返回某數(shù)的階乘，一個數(shù)的階乘等于1*2*3*...*該數(shù)，Number

為要計算其階乘的非負(fù)數(shù)。如果Number不是整數(shù)，則截尾取整。POWER(number,power)返回給定數(shù)字的乘冪。Number

底數(shù)，可以為任意實數(shù)。Power

指數(shù)，底數(shù)按該指數(shù)次冪乘方?？梢杂谩癪”運算符代替函數(shù)POWER來表示對底數(shù)乘方的冪次，例如5^2。2023/3/553、邏輯函數(shù)本類我們重點看一下條件選擇函數(shù)的用法。IF(logical_test,value_if_true,value_if_false)

其中Logical_test

表示計算結(jié)果為TRUE或FALSE的任意值或表達式。此參數(shù)可使用任何比較運算符。Value_if_true

是logical_test為TRUE時返回的值或表達式Value_if_false

是logical_test為FALSE時返回的值或表達式。此函數(shù)可以嵌套使用，最多可以嵌套64層。2023/3/56二、應(yīng)用excel函數(shù)算隧道設(shè)計路面標(biāo)高在隧道施工中，要控制結(jié)構(gòu)物的尺寸和標(biāo)高，經(jīng)常涉及到計算給定里程處的設(shè)計路面標(biāo)高。下面我們學(xué)習(xí)一個利用excel函數(shù)計算帶有豎曲線的隧道路面標(biāo)高應(yīng)用實例。以禾荷圍左線隧道為例，隧道從一號進口里程ZK42+643到二號隧道出口里程ZK43+649，為兩個不同坡比的下坡，其中包括兩段豎曲線。我們的目標(biāo)是編一個公式，可以計算線路上任意一里程處的設(shè)計路面標(biāo)高。禾荷圍左線縱曲線要素如下表所示：2023/3/57禾荷圍左線隧道豎曲線表

里程標(biāo)高豎曲線半徑切線長小里程側(cè)坡度大里程側(cè)坡度JD1ZK42+465122.56217000306.4171.605%-2%JD2ZK43+570100.46225000153.846-2%-0.769%表中兩個豎曲線把線路設(shè)計標(biāo)高分成了5個不同的區(qū)段，每個區(qū)段的曲線要素和算法都有不同，這給我們現(xiàn)場計算帶來很多麻煩，我們拿到一個里程，先好看它位于哪個區(qū)段，在去查這個區(qū)段的曲線參數(shù)，然后定算法，這樣就比較麻煩。五個區(qū)段的具體參數(shù)和算法如下表所示：2023/3/58起始里程終止里程長度(M)縱坡豎曲線示意簡圖1ZK42+465ZK42+771.417306.417-2%JD1i1=1.605i2=-2%2ZK42+771.417ZK43+416.154644.737-2%i=-2%3ZK43+416.154ZK43+570153.846-2%JD2i1=-2%i2=-0.769%4ZK43+570ZK43+723.846153.846-0.769%JD2i1=-2%i2=-0.769%5ZK43+723.846ZK43+849125.154-0.769%-0.769%2023/3/59單坡段的設(shè)計標(biāo)高很容易計算，找一個基準(zhǔn)點按照坡比推算即可。即，H=h0+i*L。其中：h0為基準(zhǔn)點標(biāo)高，L為計算點到基準(zhǔn)點的距離。有豎曲線影響的地段，初考慮坡度影響外，還要考慮豎曲線的影響，其中豎曲線計算公式為：H=(L-L0)2/2R其中：L為計算點里程；L0直線和曲線切點里程；R為豎曲線半徑。例如上表中第1段的設(shè)計標(biāo)高計算公式為：H=122.562+(L-42465)*(-2%)-(42771.417-L)

2/2*17000第2段的計算公式：H=122.562+(L-42465)*(-2%)第3段計算公式：H=100.462+(L-43570)*(-2%)+(43570-L)

2/2*25000第4段計算公式：

H=100.462+(L-43570)*(-0.769%)+(43570-L)

2/2*25000第5段計算公式：

H=100.462+(L-43570)*(-0.769%)2023/3/510下面我要要做的就就是，給給定一個個里里程程，計算算機自動動判斷所所在的區(qū)區(qū)間然后后自動選選擇公式式計算出出結(jié)果，，這就涉涉及到一一個按照照條件選選擇的問問題，用用IF函數(shù)嵌套套使用可可以很完完美地解解決這個個問題。。即:H=IF(L<42771.417,H=122.562+(L-42465)*(-2%)-(42771.417-L)2/2*17000,IF(L<43416.154,H=122.562+(L-42465)*(-2%),IF(L<43570,H=100.462+(L-43570)*(-2%)+(43416.154-L)2/2*25000,IF(L<43723.846,100.462+(L-43570)*(-0.769%)+(43723.846-L)2/2*25000,H=100.462+(L-43570)*(-0.769%)))))然后將以以上公式式寫入EXCEL單元格中中，用里里程所屬屬的的單單元格序序號代替替L，將”%”用小數(shù)表表示，平平方用冪冪函數(shù)符符號“^”表示如如下面表表格所示示：2023/3/411注意：里里程的輸輸入方法法，因為為我們的的里程左左線格式式為：ZK43+570.000,其中包括括了字符符串“ZK”和符號號“+”，在運運算的時時候我們們只要求求數(shù)值部部分43570.000參與計算算，而在在顯示的的時候又又希望按按照正常常格式顯顯示，這這和excel給定的數(shù)數(shù)據(jù)格式式（如日日期、數(shù)數(shù)值、文文本、等等）都不不匹配，，所以我我們自定定義一種種數(shù)據(jù)格格式：““ZK”00”+”000.000,其中帶雙雙引號的的將其定定義為字字符串，，不參與與數(shù)學(xué)運運算和輸輸入，只只顯示時時自動插插入。=IF(B2<42771.417,122.56+(B2-42465)*(-0.02)-((42771.417-B2)^2)/(2*17000),IF(B2<43416.154,122.562+(B2-42465)*(-0.02),IF(B2<43570,100.462+(B2-43570)*(-0.02)+((43416.154-B2)^2)/(2*25000),IF(B2<43723.846,100.462+(B2-43570)*(-0.00769)+(43723.846-B2)^2/(2*25000),100.462+(B2-43570)*(-0.00769)))))2023/3/412三、應(yīng)用用excel函數(shù)算隧隧道開挖挖輪廓超超欠挖隧道開挖挖面放樣樣，支護護面、襯襯砌輪廓廓的測量量數(shù)據(jù)分分析，要要求能夠夠通過測測點的三三維坐標(biāo)標(biāo)來計算算測點相相對于參參考斷面面的徑向向超欠情情況。也即，根根據(jù)測測量隊提提供的測測點里程程，距隧隧道中線線距離和和實測標(biāo)標(biāo)高，再再參照隧隧道斷面面尺寸，，來計算算測點與與斷面輪輪廓的相相對位置置，如果果點落入入輪廓內(nèi)內(nèi)側(cè)則表表示欠挖挖或者侵侵限。本節(jié)也以以禾荷圍圍隧道為為例，闡闡述利用用excel函數(shù)算隧隧道開挖挖輪廓超超欠挖1、隧道斷斷面尺寸寸和參數(shù)數(shù)如下圖所所示隧道洞身身部分為為三心圓圓，而且且左右側(cè)側(cè)對稱。。所以仔仔細分析析可以根根據(jù)圓心心的不同同將洞身身分為兩兩個部分分進行計計算，，即拱部部46度14分12秒×2圓心為o1的部分和和邊墻圓圓心為o1的部分。。則用x表示測點點距隧道道中線距距離，y表示測點點表報，，b表示測點點里程處處的設(shè)計計路面標(biāo)標(biāo)高（可可以用上上節(jié)中公公式求出出），c表示超欠欠挖（““-”表示欠欠挖），，L表示里程程。如下下圖所示示：2023/3/413禾荷圍隧隧道斷面面圖2023/3/4142、超欠挖計算算方法分析隧道斷面面參數(shù)可知，，只要分析測測點距本段圓圓弧圓心的距距離與參考斷斷面半徑比較較即可知超欠欠挖數(shù)值。則則：1）當(dāng)測點在o1圓心范圍時,也即|x|/(y-b+0.5)<tan(46°14’12”)時：C=√(x2+(y-b+0.5)2)-R2）當(dāng)測點在o2圓心范圍時,也即|x|/(y-b+0.5)>=tan(46°14’12””)時：C=√((|x|-2)2+(y-b-1.42)2)–(R-2.77)同樣適用if函數(shù)來實現(xiàn)條條件選擇，公公式大概如下下：C=if（|x|/(y-b+0.5)<tan(46°14’12”)，√(x2+(y-b+0.5)2)-R，√((|x|-2)2+(y-b-1.42)2)–(R-2.77)）2023/3/4153、excel函數(shù)的應(yīng)用用excel函數(shù)和計算機機語言將上式式寫入excel單元格：Tan()函數(shù)要求引用用值為弧度，，所以將46°14’’12”可轉(zhuǎn)換為(46+(14+(12/60))/60)*pi()/180或者RADIAN