EXCEL中回归函数分析处理监控量测数据

EXCEL中回归函数分析处理监控量测数据

xxx

（中铁xx局x公司）

【摘要】本文通过例题讲述了利用电子表格（Excel）处理隧道监控量测数据的详细步骤，以及回归成果在围岩收敛基本稳定判定中的应用，不需第三方软件的情况下，在Excel内完成所有数据的回归分析工作，可使监控量测数据分析更准确、更快捷、更及时、更方便观测数据的管理，为隧道施工及时提供反馈及预测信息，使施工更科学、更安全。

【关键词】隧道围岩变形监控量测回归分析回归函数Excel

我国铁路隧道的设计越来越多地采用了复合式衬砌形式，复合式衬砌一般由锚喷支护和模筑混凝土衬砌两部分组成，为了掌握施工中围岩稳定程度与支护受力、变形的力学动态或信息，以判断设计、施工的安全与经济，必须将现场监控量测项目列入施工组织设计，并在施工中认真实施。《铁路工程质量检验评定标准》JTG F80/1-2004第10.1.2条规定：采用钻爆法施工、设计为复合式衬砌的隧道，承包商必须按照设计和施工规范要求的频率和量测项目进行监控量测，用量测信息指导施工并提交系统、完整、真实的量测数据和图表。由此可见，监控量测工作是复合式衬砌隧道施工中的一项非常重要的工序。

本文主要介绍利用Excel对收敛量测数据的分析整理及应用。

收敛量测数据的分析整理主要包括：绘制收敛—时间曲线、回归分析、量测成果的分析应用，而以上部分的数据分析整理均可通过Excel来实现，可避免繁琐的手工计算。

一、利用Excel绘制收敛—时间曲线

例1：（某隧道一个断面）收敛观测数据表

1、将表1中的数据输入Excel工作表中：如图1所示

图1：表1的Excel工作表

2、选择区域A1：C12，如图1所示，在工具栏中点击Excel图表向导，在“图表类型”中选择“折线图”：如图2所示，在“子图表类型”中选择第4种折线图，并点击“下一步”，即可得到图3和图4

图2：折线图的绘制图3：折线图的绘制

图4：折线图的绘制

3、点击“完成”，并对图形进行修饰编辑，得到如图5所示的收敛—时间曲线图

收敛--时间曲线

0123456

781

2

3

4

5

6

7

8

9

10

时间（d）

收敛值（m m ）

图5： 收敛—时间曲线

二、 利用Excel 对收敛观测数据进行回归分析

1、《铁路隧道施工技术规范》JTJ042—94中要求采用回归分析时，根据测试数据散点分布规律，可选用下列之一的函数式关系：

✓ 对数函数：

)

t 1(lg /b a u )

t 1(lg a u ++=+⨯=

✓ 指数函数：

b/t

-bt e

a u )e -(1a u ⨯=⨯=-

✓ 双曲函数：

]

)bt

11(1[a u bt

a /t u 2+-=+=

式中：a 、b ——回归常数；

t ——初读数后的时间（d ）； u ——位移值（㎜）。 2、非线性回归方程的线性化

仍以例1为例：根据例1的收敛--时间曲线走向（图5）选用指数函数公式t

/b e

a u -⨯=作为回

归函数可能合理些（如果不能做出较明确的判断，可以分别按以上几种函数进行回归，取拟合的最优者），由于指数函数为非线性函数，但可将其线性化，方法如下：

t /b e a u -⨯=…………….式（1）

对其两边取自然对数，得：

)t 1(b lna lnu -+=

令t

1t lnu u -

='=' 则t b lna u '+='……………式（2）

式（2）则是式（1）线性化的直线方程，其参数的确定可按线性回归的方法确定。 3、Excel 的数据分析工具

利用Excel 对观测数据进行回归分析，要使用Excel 的数据分析工具，使用数据分析工具之前，要确认“分析工具库”处于加载状态：

图6：加载分析工具库

如果在“工具”→“加载宏”中找不到“分析工具库”，则必须通过添加Office附件的方法来添加“分析工具库”。

4、利用Excel分析例1的观测数据

以例1中的水平收敛观测数据为例：首先在图1所示的Excel工作表中计算出线性化方程式（2）中的u'和t'，点击Excel菜单中的“工具”→“数据分析”，出现数据分析窗口：图7，选择“回归”，确定后出现回归分析窗口：图8，在Y值输入区域选择：$E$2:$E$10；在X值输入区域选择：$D$2:$D$10；选择“确定”，得到回归分析结果：图9。

图7：数据分析窗口

图8：回归分析窗口

图9：回归分析结果

从图9的回归分结果可得：截距02705.2lna =，则592.7a =；43099.1b =，代入式（1）则得到水平收敛观测数据的回归方程：t

-1.43099

e

592.7u ⨯=………….式（3）；

判定系数：9918.0r 2

=；

由回归方程式（3），取∞=t ，可得到水平收敛位移值的终值592mm .7u =； 按照以上相同的步骤，可得到拱顶下沉观测数据的回归方程：t

0949

.1413e

.4u -=………….. 式（4）；

判定系数：9979.0r 2

=； 由回归方程式（4），取∞=t ，可得到拱顶下沉位移值的终值 4.413mm u =；

三、 量测成果的分析和应用

1、 周边位移相对值分析

周边位移相对值是指两测点间实测位移累计值（或用回归分析推算的最终位移值）与两测点间距离之比，或拱顶实测位移值与隧道宽度之比。如例1中水平收敛两测点间间距L=6637.74㎜，水平收敛位移终值u=7.592㎜，则周边位移相对值为：u/L=7.592㎜/6637.74㎜=0.11%，此值应小于隧道周边允许位移相对值，如果测得的周边位移相对值超过允许位移相对值，说明初期支护设计参