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

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

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

利用这些函数和公式，我们可以方便地在表格里编公式或程序，实现逻辑，数据，统计，等等方面的计算。下面我们就一些日常用到的简单的函数，做一点浅显的介绍。2024/3/821、日期与时间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!。2024/3/832、数学和三角这类函数是我们在实际工作中最常用到的。举例：ABS(number)返回数字的绝对值。Number

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

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

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

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

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

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

指数，底数按该指数次幂乘方。可以用“^”运算符代替函数POWER来表示对底数乘方的幂次，例如5^2。2024/3/853、逻辑函数本类我们重点看一下条件选择函数的用法。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层。2024/3/86二、应用excel函数算隧道设计路面标高在隧道施工中，要控制结构物的尺寸和标高，经常涉及到计算给定里程处的设计路面标高。下面我们学习一个利用excel函数计算带有竖曲线的隧道路面标高应用实例。以禾荷围左线隧道为例，隧道从一号进口里程ZK42+643到二号隧道出口里程ZK43+649，为两个不同坡比的下坡，其中包括两段竖曲线。我们的目标是编一个公式，可以计算线路上任意一里程处的设计路面标高。禾荷围左线纵曲线要素如下表所示：2024/3/87禾荷围左线隧道竖曲线表

里程标高竖曲线半径切线长小里程侧坡度大里程侧坡度JD1ZK42+465122.56217000306.4171.605%-2%JD2ZK43+570100.46225000153.846-2%-0.769%表中两个竖曲线把线路设计标高分成了5个不同的区段，每个区段的曲线要素和算法都有不同，这给我们现场计算带来很多麻烦，我们拿到一个里程，先好看它位于哪个区段，在去查这个区段的曲线参数，然后定算法，这样就比较麻烦。五个区段的具体参数和算法如下表所示：2024/3/88起始里程终止里程长度(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%2024/3/89单坡段的设计标高很容易计算，找一个基准点按照坡比推算即可。即，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%)2024/3/810下面我要做的就是，给定一个里里程，计算机自动判断所在的区间然后自动选择公式计算出结果，这就涉及到一个按照条件选择的问题，用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，将”%”用小数表示，平方用幂函数符号“^”表示如下面表格所示：2024/3/811注意：里程的输入方法，因为我们的里程左线格式为：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)))))2024/3/812三、应用excel函数算隧道开挖轮廓超欠挖隧道开挖面放样，支护面、衬砌轮廓的测量数据分析，要求能够通过测点的三维坐标来计算测点相对于参考断面的径向超欠情况。也即，根据测量队提供的测点里程，距隧道中线距离和实测标高，再参照隧道断面尺寸，来计算测点与断面轮廓的相对位置，如果点落入轮廓内侧则表示欠挖或者侵限。本节也以禾荷围隧道为例，阐述利用excel函数算隧道开挖轮廓超欠挖1、隧道断面尺寸和参数如下图所示

隧道洞身部分为三心圆，而且左右侧对称。所以仔细分析可以根据圆心的不同将洞身分为两个部分进行计算，即拱部46度14分12秒×2圆心为o1的部分和边墙圆心为o1的部分。

则用x表示测点距隧道中线距离，y表示测点表报，b表示测点里程处的设计路面标高（可以用上节中公式求出），c表示超欠挖（“-”表示欠挖），L表示里程。如下图所示：2024/3/813禾荷围隧道断面图2024/3/8142、超欠挖计算方法分析隧道断面参数可知，只要分析测点距本段圆弧圆心的距离与参考断面半径比较即可知超欠挖数值。则：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)）2024/3/8153、excel函数的应用用excel函数和计算机语言将上式写入excel单元格：Tan()函数要求引用值为弧度，所以将46°14’12”可转换为(46+(14+(12/60))/60)*pi()/180或者RADIANS(46+(14+(12/6