EXCLE函数和隧道工程应用实例

EXCLE函数和隧道工程应用实例工程部：王亮2023/3/51一、excel内置函数简介

我们知道Excel的计算功能是非常强大的，这和它完善的函数系统是分不开的。按照用途主要可可分为：财务、日期与时间、工程、性息、逻辑、数学与三角、统计、数据库、文本等等。

利用这些函数和公式，我们可以方便地在表格里编公式或程序，实现逻辑，数据，统计，等等方面的计算。下面我们就一些日常用到的简单的函数，做一点浅显的介绍。2023/3/521、日期与时间Excel可将日期存储为可用于计算的序列号。默认情况下，1900年1月1日的序列号是1，而2008年1月1日的序列号是39448，这是因为它距1900年1月1日有39448天。时间值为日期值的一部分，并用十进制数来表示（例如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将使用该数值作为年份。例如：DATE(2008,1,2)将返回2008年1月2日。如果year小于0或大于等于10000，则Excel将返回错误值#NUM!。2023/3/532、数学和三角这类函数是我们在实际工作中最常用到的。举例：ABS(number)返回数字的绝对值。Number

需要计算其绝对值的实数。PI()返回数学常量π，精确到小数点后14位。RADIANS(angle)将角度转换为弧度，Angle为需要转换成弧度的角度SQRT(number)返回正平方根。其中Number

要计算平方根的数。SUMSQ(number1,number2,...)返回参数的平方和。其中Number1,number2,...

为1到255个需要求平方和的参数，也可以使用数组或对数组的引用来代替以逗号分隔的参数。2023/3/54TAN(number)返回给定角度的正切值，Number

为要求正切的角度，以弧度表示。FACT(number)返回某数的阶乘，一个数的阶乘等于1*2*3*...*该数，Number

为要计算其阶乘的非负数。如果Number不是整数，则截尾取整。POWER(number,power)返回给定数字的乘幂。Number

底数，可以为任意实数。Power

指数，底数按该指数次幂乘方。可以用“^”运算符代替函数POWER来表示对底数乘方的幂次，例如5^2。2023/3/553、逻辑函数本类我们重点看一下条件选择函数的用法。IF(logical_test,value_if_true,value_if_false)

其中Logical_test

表示计算结果为TRUE或FALSE的任意值或表达式。此参数可使用任何比较运算符。Value_if_true

是logical_test为TRUE时返回的值或表达式Value_if_false

是logical_test为FALSE时返回的值或表达式。此函数可以嵌套使用，最多可以嵌套64层。2023/3/56二、应用excel函数算隧道设计路面标高在隧道施工中，要控制结构物的尺寸和标高，经常涉及到计算给定里程处的设计路面标高。下面我们学习一个利用excel函数计算带有竖曲线的隧道路面标高应用实例。以禾荷围左线隧道为例，隧道从一号进口里程ZK42+643到二号隧道出口里程ZK43+649，为两个不同坡比的下坡，其中包括两段竖曲线。我们的目标是编一个公式，可以计算线路上任意一里程处的设计路面标高。禾荷围左线纵曲线要素如下表所示：2023/3/57禾荷围左线隧道竖曲线表

里程标高竖曲线半径切线长小里程侧坡度大里程侧坡度JD1ZK42+465122.56217000306.4171.605%-2%JD2ZK43+570100.46225000153.846-2%-0.769%表中两个竖曲线把线路设计标高分成了5个不同的区段，每个区段的曲线要素和算法都有不同，这给我们现场计算带来很多麻烦，我们拿到一个里程，先好看它位于哪个区段，在去查这个区段的曲线参数，然后定算法，这样就比较麻烦。五个区段的具体参数和算法如下表所示：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单坡段的设计标高很容易计算，找一个基准点按照坡比推算即可。即，H=h0+i*L。其中：h0为基准点标高，L为计算点到基准点的距离。有竖曲线影响的地段，初考虑坡度影响外，还要考虑竖曲线的影响，其中竖曲线计算公式为：H=(L-L0)2/2R其中：L为计算点里程；L0直线和曲线切点里程；R为竖曲线半径。例如上表中第1段的设计标高计算公式为：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下面我要要做的就就是，给给定一个个里里程程，计算算机自动动判断所所在的区区间然后后自动选选择公式式计算出出结果，，这就涉涉及到一一个按照照条件选选择的问问题，用用IF函数嵌套套使用可可以很完完美地解解决这个个问题。。即: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，将”%”用小数表表示，平平方用幂幂函数符符号“^”表示如如下面表表格所示示：2023/3/411注意：里里程的输输入方法法，因为为我们的的里程左左线格式式为：ZK43+570.000,其中包括括了字符符串“ZK”和符号号“+”，在运运算的时时候我们们只要求求数值部部分43570.000参与计算算，而在在显示的的时候又又希望按按照正常常格式显显示，这这和excel给定的数数据格式式（如日日期、数数值、文文本、等等）都不不匹配，，所以我我们自定定义一种种数据格格式：““ZK”00”+”000.000,其中带双双引号的的将其定定义为字字符串，，不参与与数学运运算和输输入，只只显示时时自动插插入。=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三、应用用excel函数算隧隧道开挖挖轮廓超超欠挖隧道开挖挖面放样样，支护护面、衬衬砌轮廓廓的测量量数据分分析，要要求能够够通过测测点的三三维坐标标来计算算测点相相对于参参考断面面的径向向超欠情情况。也即，根根据测测量队提提供的测测点里程程，距隧隧道中线线距离和和实测标标高，再再参照隧隧道断面面尺寸，，来计算算测点与与断面轮轮廓的相相对位置置，如果果点落入入轮廓内内侧则表表示欠挖挖或者侵侵限。本节也以以禾荷围围隧道为为例，阐阐述利用用excel函数算隧隧道开挖挖轮廓超超欠挖1、隧道断断面尺寸寸和参数数如下图所所示隧道洞身身部分为为三心圆圆，而且且左右侧侧对称。。所以仔仔细分析析可以根根据圆心心的不同同将洞身身分为两两个部分分进行计计算，，即拱部部46度14分12秒×2圆心为o1的部分和和边墙圆圆心为o1的部分。。则用x表示测点点距隧道道中线距距离，y表示测点点表报，，b表示测点点里程处处的设计计路面标标高（可可以用上上节中公公式求出出），c表示超欠欠挖（““-”表示欠欠挖），，L表示里程程。如下下图所示示：2023/3/413禾荷围隧隧道断面面图2023/3/4142、超欠挖计算算方法分析隧道断面面参数可知，，只要分析测测点距本段圆圆弧圆心的距距离与参考断断面半径比较较即可知超欠欠挖数值。则则：1）当测点在o1圆心范围时,也即|x|/(y-b+0.5)<tan(46°14’12”)时：C=√(x2+(y-b+0.5)2)-R2）当测点在o2圆心范围时,也即|x|/(y-b+0.5)>=tan(46°14’12””)时：C=√((|x|-2)2+(y-b-1.42)2)–(R-2.77)同样适用if函数来实现条条件选择，公公式大概如下下：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函数的应用用excel函数和计算机机语言将上式式写入excel单元格：Tan()函数要求引用用值为弧度，，所以将46°14’’12”可转换为(46+(14+(12/60))/60)*pi()/180或者RADIAN