Excel散点图趋势线回归分析在隧道工程监控量测中的应用

Excel散点图趋势线回归分析在隧道工程监控量测中的应用 沈永林 中铁五局集团第四工程有限责任公司第十二隧道作业队 贵阳 【摘 要】论述了Excel 2007散点图对数函数趋势线回归分析在隧道工程监控量测中的应用。并结合具体实例，对回归分析步骤进行了详细讲解与说明，出了用Excel散点图趋势线进行回归分析的方法。 【关键词】隧道工程;监控量测;Excel散点图对数函数趋势线回归分析 【引 言】目前部分技术人员对隧道监控量测收敛分析心存疑虑，尤其对回归分析与趋势预测这方面的知识讨论最为频繁且争议最大。规范监控量测数据分析一般采用散点图和回归分析方法，但并未详细介绍使用何种软件与如何分析。就以前隧道施工经验出发，笔者曾采用过《变形数据分析与预测系统》软件与Excel自带的回归分析功能分析等方法进行回归分析与预测，相比而言Excel自带分析功能进行回归分析与预测最为方便快捷与有效。 【正文】 隧道监控量测分为以下几个步骤进行:1阅读监控量测方案与规范―2布??设测点并取得初始监测值―3现场监控量测及分析―4信息反馈与对策。现对?? 几个步骤的操作要求进行详细解释并重点说明第三步中数据分析时采用Excel散点图对数函数趋势线回归分析的方法。 一、阅读监控量测方案与规范 进行隧道监控量测前必须熟读理解《铁路隧道监控量测技术》与监控量测实施方案，并按已批准的方案实施监控量测。 二、现场观测得到原始数据 按照《TB10121-2007铁路隧道监控量测技术规程》的要求做好量测点的埋设，并按规范要求的频率做好数据采集工作。根据规范，拱顶下沉和净空变化是普通隧道内监控量测必测项目。必测项目埋点间距，埋点位置、量测频率以及量测仪器具体要求如表2-1至表2-4所示。 表2-1-监控量测断面间距表 of work enthusiasm and forward-looking. The difficulties and problems of individual cadres indifferent masses as the buck passing, long, make some simple complex problems. Some cadres general talk about pay, do not take the initiative to undertake for the bitter and tired of the work, the lack of courage to play a positive attitude." corrective measures: (LED Leadership: Luo Mingjun, rectification time: before September 25th, insist for a long time) 1, effectively solve the enterprise less, help is not enough. In order to "turn style, solve problems, and do practical things, heart to heart" as the core, in accordance with the provisions of division of Labor Bureau, by the Bureau of Party members and cadres room composition the working group , to help enterprises solve problems, promote the construction of major projects; close ties with the masses, to ask for the people, ask for people to know the people, public opinion, the people, improve people's livelihood. 围岩级别 断面间距(m) ?-? 5-10 ? 10-30 ? 30-50 表2-2-净空变化量测测线数 开挖方法 地段 一般地段 特殊地段 全断面法 一条水平测线 每台阶一条水平测线，两条斜测线 台阶法 每台阶一条水平测线 CD或CRD法上部、双侧壁导坑法左右 侧部，每分部一条水平侧线，两条斜分部开挖法 每分部一条水平测线 测线、其余分部一条水平测线 表2-3按距开挖面距离确定的监控量测频率 监控量测断面距开挖面距离(m) 监控量测频率 (0-1)B 2次/d (1-2)B 1次/d (2-5)B 1次/2-3d >5B 1次/7d 注:B为隧道开挖宽度。 表2-4按位移速度确定的监控量测频率 位移速度(mm/d) 监控量测频率 ?5 2次/d 1,5 1次/d 0.5,1 1次/2-3d 0.2,0.5 1次/3d <0.2 1次/7d 必测项目的监控量测频率应根据测点距开挖面的距离及位移速度分别确定，原则上取2者中频率的较高值。出现异常情况或不良地质时，还应增大监控量测频率。 表4-监控量测必测项目仪器要求表 of work enthusiasm and forward-looking. The difficulties and problems of individual cadres indifferent masses as the buck passing, long, make some simple complex problems. Some cadres general talk about pay, do not take the initiative to undertake for the bitter and tired of the work, the lack of courage to play a positive attitude." corrective measures: (LED Leadership: Luo Mingjun, rectification time: before September 25th, insist for a long time) 1, effectively solve the enterprise less, help is not enough. In order to "turn style, solve problems, and do practical things, heart to heart" as the core, in accordance with the provisions of division of Labor Bureau, by the Bureau of Party members and cadres room composition the working group , to help enterprises solve problems, promote the construction of major projects; close ties with the masses, to ask for the people, ask for people to know the people, public opinion, the people, improve people's livelihood. 施工论文 序号 监控量测项目 常用量测仪器 备注 水准仪挂钢尺或全站1 拱顶下沉 仪 2 净空变化 收敛仪、全站仪 监控量测系统的测试精度应满足要求。拱顶下沉、净空变化测试精度为0.5-1mm，故当采用m为单位时，记录数据需至小数点后3位数。当采用收敛仪记录净空变化数据时，记录位数由测试仪器的精度而定，一般为mm后2位小数。 三、现场监控量测及分析 1、数据采集及录入 严格根据规范和监测方案要求，采集记录好相关数据，录入Excel后备用。以拱顶下沉为例，录入后的数据具备《表3-1-拱顶下沉测量记录表》所示的形式。 勇于跨越of work enthusiasm and forward-looking. The difficulties and problems of individual cadres indifferent masses as the buck passi 3 追求卓越 ng, long, make some simple complex problems. Some cadres general talk about pay, do not take the initiative to undertake for the bitter and tired of the work, the lack of courage to play a positive attitude." corrective measures: (LED Leadership: Luo Mingjun, rectification time: before September 25th, insist for a long time) 1, effectively solve the enterprise less, help is not enough. In order to "turn style, solve problems, and do practical things, heart to heart" as the core, in accordance with the provisions of division of Labor Bureau, by the Bureau of Party members and cadres room composition the working group , to help enterprises solve problems, promote the construction of major projects; close ties with the masses, to ask for the people, ask for people to know the people, public opinion, the people, improve people's livelihood. 表3-1-拱顶下沉量测记录表 标段站前工程施工单中铁五局 单位工程 跳厂坡隧道 施工方法 台阶法 施工部位 LgDK6+447 埋设日期 2011-6-24 名称 施工标 位 量测时间 温相对初度修 正 后 次下沉相对上次下沉 修时间间隔 测线第一次 第二次 第三次 平均值 测点高程 值(Δ下沉值 速度 备注 编号 正年-月-日 时 μ) 值 m m m m mm m mm mm d mm/d 1 2011-6-24 20:01 1247.1050 1247.1047 1247.1047 1247.1048 0 1247.105 1 2011-6-24 4:27 1247.1029 1247.1030 1247.1034 1247.1031 0 1247.103 1.7 1.7 0.5 3.4 1 2011-6-25 8:07 1247.1019 1247.1015 1247.1013 1247.1016 0 1247.102 3.2 1.6 0.5 3.1 1 2011-6-26 11:59 1247.1002 1247.1003 1247.0999 1247.1001 0 1247.100 4.7 1.4 1 1.4 1 2011-6-27 19:53 1247.0989 1247.0985 1247.0987 1247.0987 0 1247.099 6.1 1.4 1 1.4 1 2011-6-28 8:49 1247.0973 1247.0978 1247.0975 1247.0975 0 1247.098 7.3 1.2 1 1.2 1 2011-6-29 3:21 1247.0964 1247.0963 1247.0967 1247.0965 0 1247.096 8.3 1.0 1 1.0 1 2011-6-30 20:57 1247.0955 1247.0957 1247.0956 1247.0956 0 1247.096 9.2 0.9 1 0.9 1 2011-7-2 15:39 1247.0941 1247.0948 1247.0947 1247.0945 0 1247.095 10.3 1.0 2 0.5 1 2011-7-4 23:52 1247.0937 1247.0936 1247.0937 1247.0936 0 1247.094 11.2 0.9 2 0.5 1 2011-7-7 14:55 1247.0927 1247.0922 1247.0923 1247.0924 0 1247.092 12.4 1.2 3 0.4 1 2011-7-10 12:07 1247.0914 1247.0918 1247.0914 1247.0915 0 1247.092 13.3 0.9 3 0.3 1 2011-7-13 20:33 1247.0909 1247.0907 1247.0904 1247.0906 0 1247.091 14.2 0.9 3 0.3 1 2011-7-16 10:27 1247.0901 1247.0895 1247.0896 1247.0897 0 1247.090 15.1 0.9 3 0.3 1 2011-7-23 5:28 1247.0896 1247.0900 1247.0896 1247.0897 0 1247.090 15.1 0.0 7 0.0 1 2011-7-30 23:57 1247.0898 1247.0899 1247.0896 1247.0897 0 1247.090 15.1 0.0 7 0.0 of work enthusiasm and forward-looking. The difficulties and problems of individual cadres indifferent masses as the buck passing, long, make some simple complex problems. Some cadres general talk about pay, do not take the initiative to undertake for the bitter and tired of the work, the lack of courage to play a positive attitude." corrective measures: (LED Leadership: Luo Mingjun, rectification time: before September 25th, insist for a long time) 1, effectively solve the enterprise less, help is not enough. In order to "turn style, solve problems, and do practical things, heart to heart" as the core, in accordance with the provisions of division of Labor Bureau, by the Bureau of Party members and cadres room composition the working group , to help enterprises solve problems, promote the construction of major projects; close ties with the masses, to ask for the people, ask for people to know the people, public opinion, the people, improve people's livelihood. 施工论文 2、数据分析 监控量测数据分析一般采用散点图和回归分析方法。利用Excel中自带的公式功能将原始数据表与分析用数据表链接成一个整体，然后从Excel中插入散点图并链接分析数据进行回归分析与趋势预测。具体步骤如下。 (1)原始数据处理 监控量测记录的原始数据为一系列高程值或宽度值，需要进行简单的加减运算得出变化速率值以及累计下沉值或累计收敛值。 (2)插入散点图 在Excel工具条上依次点击[插入]-[图表]-[散点图]-[带平滑曲线的散点图]后即可插入散点图。 (3)链接数据 鼠标单击散点图，使散点图处于激活状态。点击Excel工具条上的[选择数据]按钮，选择[添加]，依次添加X轴系列值与Y轴系列值。X轴系列值为量测累计时间，Y轴系列为累计下沉值\收敛值或变化速率值。数据链接完成点击确定即可生成实测数据线图。生成实测数据线后根据需要点击[图表布局]选项，可在预设的11种图表样式中自定义图表样式。 (4)添加趋势线 在实测数据线上单击鼠标右键即出现添加趋势线对话框，如《图1-添加趋势线》所示。 图1-添加趋势线 勇于跨越 5 追求卓越 出现设置趋势线格式对话框后，可以选择指数、线性、对数、多项式、幂、移动平均等趋势预测/回归分析类型，隧道施工监控量测一般应选择采用对数函数进行趋势预测与回归分析。中铁隧道勘测设计院有限公司王胜涛硕士在《隧道监控量测的数据回归分析探讨》一文中描述使用对数函数作为回归分析函数，从理论上分析是合理的，工程实例证明也是有效的。函数选择结果如《图2-选择趋势线函数类型》所示。 图2-选择趋势线函数类型 of work enthusiasm and forward-looking. The difficulties and problems of individual cadres indifferent masses as the buck passing, long, make some simple complex problems. Some cadres general talk about pay, do not take the initiative to undertake for the bitter and tired of the work, the lack of courage to play a positive attitude." corrective measures: (LED Leadership: Luo Mingjun, rectification time: before September 25th, insist for a long time) 1, effectively solve the enterprise less, help is not enough. In order to "turn style, solve problems, and do practical things, heart to heart" as the core, in accordance with the provisions of division of Labor Bureau, by the Bureau of Party members and cadres room composition the working group , to help enterprises solve problems, promote the construction of major projects; close ties with the masses, to ask for the people, ask for people to know the people, public opinion, the people, improve people's livelihood. 施工论文 (5)趋势预测 当将图2中的预测趋势设置好一个前推值后能预测围岩总变形量以及围岩稳定时间，为隧道开挖预留变形量以及二次衬砌施工时间提供指导性意见。围岩稳定时间趋势预测如《图3-围岩稳定时间预测图》所示。图中选择已实测的6个下沉速率值进行预测，通过函数预测判定开挖第83天后围岩变形速率趋于零，即已基本稳定。围岩总变形量预测方法与围岩稳定时间趋势预测方法相似。变形速率预测曲线不与横轴相交并成上升趋势时意味着隧道变形加剧，需加强支护，及早施工二次衬砌。累计下沉值预测曲线趋于平滑时的最大值为隧道围岩总变形量，若累计下沉值预测曲线不趋于平滑则预示隧道围岩将持续变形。在选择数据步骤时选择非6个实测数据进行分析时预测结果会与上述刚好选择6个实测数据分析时略有差异，具体选择几个数据进行分析由不同的各种情况自定。上述预测是基于统计学基本原理的，即预测具有一般性意义但对某个特定的隧道施工而言最终实际数据不一定恰好是预测的数据。 图3-围岩稳定时间预测图 勇于跨越 7 追求卓越 通过原始数据处理-插入散点图-链接数据-添加趋势线-趋势预测系列五个 步骤形成的数据自动分析表如《表3-2-拱顶下沉量测分析表》所式。 表3-2-拱顶下沉量测分析表 量测编号:T0P-1 测点位置:拱顶 量测点里程:LgDK6+447 埋设时间: 2011-6-24 地本次下量测累累计下沉时间间下沉速率警报支护参质序号 量测日期 沉值计时间施工工序 值(mm) 隔(d) (mm/d) 等级 照 情(mm) (d) 况 1 2011-6-24 0 0 0 0 0 ? 首次量测 2 2011-6-24 1.6 1.5 0.5 0.5 3.1 ? 开挖 3 2011-6-25 1.5 3.1 0.5 1 3.0 ? / 4 2011-6-26 1.6 4.7 1 2 1.6 ? / 5 2011-6-27 1.6 6.2 1 3 1.6 ? / 白6 2011-6-28 1.5 7.8 1 4 1.5 ? 下部开挖 按设计云7 2011-6-29 1.4 9.1 1 5 1.4 ? / 施工，岩8 2011-6-30 1.2 10.4 1 6 1.2 ? / 9 2011-7-2 1.3 11.6 2 8 0.6 ? / 无需调夹10 2011-7-4 1.1 12.8 2 10 0.6 ? / 整 灰11 2011-7-7 1.2 14.0 3 13 0.4 ? / 岩 12 2011-7-10 0.9 14.9 3 16 0.3 ? / 13 2011-7-13 0.6 15.5 3 19 0.2 ? 仰拱开挖 14 2011-7-16 0.6 16.1 3 22 0.2 ? 仰拱封闭 15 2011-7-23 0.0 16.1 7 29 0.0 ? / 26 量测数据处理结果: 1、推算出最终位移量: Umax = 16.1 mm。 2、推算出基本稳定时间:第36天以后，开挖面距该量测断面76m围岩收敛值达 16.1 mm,收敛率达94,?90,; 位移速度为0.00mm/天?0.2mm/天，围岩基本稳定。 3、评定:该段围岩稳定性适中,初期支护36天后该围岩收敛完成、形成自稳,可进行二次衬砌施工。 计算: 校核: 日期: of work enthusiasm and forward-looking. The difficulties and problems of individual cadres indifferent masses as the buck passing, long, make some simple complex problems. Some cadres general talk about pay, do not take the initiative to undertake for the bitter and tired of the work, the lack of courage to play a positive attitude." corrective measures: (LED Leadership: Luo Mingjun, rectification time: before September 25th, insist for a long time) 1, effectively solve the enterprise less, help is not enough. In order to "turn style, solve problems, and do practical things, heart to heart" as the core, in accordance with the provisions of division of Labor Bureau, by the Bureau of Party members and cadres room composition the working group , to help enterprises solve problems, promote the construction of major projects; close ties with the masses, to ask for the people, ask for people to know the people, public opinion, the people, improve people's livelihood. 施工论文 四、信息反馈与对策 信息反馈应以位移反馈为主，主要依据时态曲线的形态对围岩稳定性、支护结构的工作状态、对周围环境的影响程度进行判定，验证和优化设计参数，指导施工。铁路隧道监控量测规范对位移管理等级的如《表7-位移管理等级表》所式。 表7-位移管理等级表 管理等级 距开挖面1B 距开挖面2B ? UU/3 U>U/3 1B2B注:u为位移实测值，U与U为位移控制基准，可按图纸取值或按规范查表得。 1B2B 在实际施工时应根据监控量测结果，按《图4-工程安全性评价流程图》指导 监控量测结果 施工。 位移(应力)否 继续施工 是否超过? 级管理 安全 否 不安全 位移(应力)综合评价设计 工程对策 是否超过?施工措施，加 级管理 强监控量测 位移(应力)达暂停施工 到?级管理 图4-工程安全性评价流程图 根据工程安全性评价的结果，需要变更设计时，应根据有关铁路工程变更管理办法及时进行设计变更。 工程对策主要包括下列内容 勇于跨越 9 追求卓越 1、一般措施 (1) 稳定开挖工作面措施 (2) 调整开挖方法 (3) 调整初期支护强度和刚度并及时支护 (4) 降低爆破振动影响 (5) 围岩与支护结构间回填注浆 2、辅助措施 (1) 地层预处理，包括注浆加固、降水、冻结等方法 (2) 超前支护，包括超前锚杆(管)、管棚、超前插板、水平高压旋 喷法、预切槽法等。 位移(应力)是未超过?级管理时，按正常程序进行施工，并根据预测出的总变形值，对同类围岩开挖时留置优化后的预留变形量,根据预测出的围岩稳定时间指导二次衬砌施工时间。 【结束语】 合理快速的监控量测分析与预测能优化施工措施，提高企业施工生产率，节约企业施工成本，创造良好效益。运用Excel2007对6个以上的实测数据进行统计分析便基本可对同一性质围岩的总变形量以及围岩稳定时间进行预测。将预测的围岩总变形量值与设计预留变形量值进行对比，可进行优化设计。围岩稳定时间的预测可指导二次衬砌施做时间。采用Excel2007进行回归分析与预测主要过程是:采集数据-通过散点图链接数据-添加趋势线函数-选择对数函数进行趋势预测与回归分析。采用Excel散点图对数函数趋势线回归分析是一种快速有效的监控量测分析方法。 【主要参考文献】 [1]喻渝、倪光斌等.《铁路隧道监控量测技术规程》[S].北京.中国铁道出版社,2009 [2] Microsoft Corporation.《Excel帮助文件》[K]. Redmond.Microsoft Corporation,2006 [3]王秀成、管德鹏等.《铁路隧道工程施工技术指南》[S].北京.中国铁道出版社,2009 [4]王毅才、陶纪南等.《隧道工程》[M].北京.人民交通出版社,2005 [5]王胜涛、梁小勇等.《隧道监控量测的数据回归分析探讨》[J].隧道建设,2009 of work enthusiasm and forward-looking. The difficulties and problems of individual cadres indifferent masses as the buck passing, long, make some simple complex problems. Some cadres general talk about pay, do not take the initiative to undertake for the bitter and tired of the work, the lack of courage to play a positive attitude." corrective measures: (LED Leadership: Luo Mingjun, rectification time: before September 25th, insist for a long time) 1, effectively solve the enterprise less, help is not enough. In order to "turn style, solve problems, and do practical things, heart to heart" as the core, in accordance with the provisions of division of Labor Bureau, by the Bureau of Party members and cadres room composition the working group , to help enterprises solve problems, promote the construction of major projects; close ties with the masses, to ask for the people, ask for people to know the people, public opinion, the people, improve people's livelihood.