EXCEL计算概率积分法预计参数.docx

excel 计算概率积分法预计参数韩奎峰 ，武娟 ，冯遵德( 徐州师范大学测绘学院，江苏徐州 221116; 河北中色测绘有限公司，河北廊坊 102800)【摘 要】 excel 软件是最常用的办公软件之一，本文介绍一种利用 excel 软件规划求解功能计算开采沉陷预计概率积分法预计参数的方法，很方便地得到了预计参数的优化解，通过实例验证，运用该方法得到的这些参数的 解满足开采沉陷预计精度的要求，有很好的应用价值。【关键词】 excel; 规划求解; 概率积分法预计参数;开采沉陷预计【文章编号】1009-2307( 2011) 06-0250-03【中图分类号】 o21【文献标识码】 a首先要研究描述实际问题的数学模型。根据 规程，开采沉陷预计概率积分法数学模型如下:任意点 ( x，y) 下沉预计模型和沿 方向水平移动值预 计模型为:1引言煤矿开采对环境的影响很大，煤矿工程技术人员需要 对煤矿开采引起的地表移动和变形值进行预计，以获取煤 矿开采对环境影响的一些数据指标，进而优化开采方案或 者制定科学、合理的保护措施，使煤矿开采对环境的影响 减少或达到保护环境的目的。概率积分法作为目前开采沉 陷预计的主要方法，有很多优点，主要优点是运用计算机 实现了自动化计算，然而用概率积分法预计需要一系列参 数，这些参数关乎预计结果的精度，所以工程技术人员希 望得到高质量的预计参数。我国 建筑物、水体、铁路及 主要井巷煤柱留设与压煤开采规程 ( 下称 规程) 中规 定利用最小二乘原理编制地表移动参数拟合程序，可以得 到概率积分法全部 8 个参数，然而编写这样一个程序并非 易事，况且最小二乘方法对粗差的免疫能力差，不属于智 能化方法1。相关文献显示，目前计算概率积分法预计参 数方法很多，主要有曲线拟合法、空间问题求参法、正交 试验设计求参法、模矢法和支持向量机的方法等等，经研 究，这些方法要么自动化程度低，要么对算法迭代初值要 求很高2-3，如果部分观测数据出现粗差或者突变时，算 法迭代初值选取会变得很难，容易产生局部收敛，从而得 到错误的参数值。规划求解是 excel 软件内置的一个优化 程序，是一个功能强大的分析工具，它具有很好的人机交 互功能，可以非常方便的完成线性，非线性规划求解。根 据概率积分法预计计算的原理，它是一个非线性拟合问题， 本文就介绍用 excel 规划求解的方法计算概率积分法预计 参数。 1 w ( x，y ) = w0 ( x ) w0 ( y )( 1)w0式中:w 槡 ( x l s s ) 槡 x w0 ( x ) = erf0 erf1 2 h1 h21 tan tan( 2)w 槡 y w0 ( y ) = erf0 erf h22 tan sin( + ) ) 槡 ( y ( l + s + s )( 3) h3 4 3sin tan( 4)w0 = mqcos) e ( )x l s1 s2 2u ( x，y， ) = bw0 e ( x 2h1 h1()tantan e (y ( l + s3 + s4) sin( + ) ) 2) ) sincos + ( e ( h2 ) y 2sinh1tantan+ w0 ( y ) cotsin( 5)( 1) 、( 2) 、( 3) 、( 4) 和( 5) 式中: x，y 为预计点在工作面独立定义坐标系中的坐标; w0 为最大下沉值; h1 为走 向采深; h2 和 h3 为工作面上、下山边界的采深; tan 是开 采影响角正切; l、l 为走向和倾向工作面长度，s1 、s2 、s3 、s4 为 拐点偏移距; 为最大下沉角; m 为采厚; q 为下沉系数; b 为2概率积分法预计模型和参数及 excel 软件计算模型的建立x2 e x2 dx。其中:水平移动系数， 为煤层倾角;erf ( x ) =槡 02. 1 概率积分法预计模型和参数excel 规划求解过程中可以调用系统自带的数学模 型，即系统函数，用户也可以自己定义数学模型，以满足 求解过程中自动调用的需要，大部分实际问题都是用户自 己定义数学模型以满足自己的需要，所以要解决实际问题，q、tan、b、s1 、s2 、s3 、s4 为概率积分法地表移动预计参数。用 vba 技术编写新的误差函数 erfcai(x) 及 excel开采沉陷预计数学模型的建立根据实验测试，excel 软件系统自带的误差函数 erf中 x 参数的取值必须大于零，且积分区间定义为 0，2. 2( x)x，不能满足上述概率积分数学模型的计算工作，因为模型中 x，y 取值可以小于零。通过研究，在 excel 环境下可 以建立自己误差函数，以满足开采沉陷预计工作的需要。 定义新的函数有很多种方法，最常用的是利用 excel 软件提供的 vba 技术编写自己的函数，还可以利用动态连接库的方法直接调用其他语言编写的函数。根据 excel 软件 erf 函数的特点，为避免编写更多的代码，决定用 vba 技 术编写自己的 erfcai 函数来满足概率积分法求参的需要， 自定义的 erfcai 函数代码很简单，如下:public function erfcai( x as double) as double作者简介: 韩奎峰 ( 1975-) ，山东夏津人，讲师，博士生，主要从事矿区变形 监测以及生态重建方面的研究工作。e-mai: hankuifeng 126. com收稿日期: 2011-4-6基金 项 目: 国 家 自然科学基金项目 ( 51074139) ; 江苏省高等学校基金项目( 09kjb420003)251第 6 期韩奎峰等 excel 计算概率积分法预计参数if x = 0 thenerfcai = worksheetfunction. erf( x)elseerfcai = -worksheetfunction. erf( -x)end ifend function这样 erfcai 函数就可以满足上述开采沉陷预计数学模 型的需要了。下面给出用 erfcai 函数进行计算验证的例子。图 2 调用规划求解功能图 3 为规划求解功能主界面， 在该对话框中设置求 参目标单元格; 设置目标单元格的求参条件， 按照什么 原则求参，最大 值， 最 小 值 还 是 0 值; 设 置 所 求 参 数， 因为在迭代过程中参数是变化的， 所 以 称 可 变 单 元 格; 还可以根据实际情况添加所求参数的约束条件。 如果用 户有特殊要求还可以点击选项按钮， 系统会弹出 “规划 求解选项” 对话框， 如 图 4 ， 在 该 对 话 框 中， 用 户 可 以 设置运算时间、迭代次数、 精度、 允许误差和收敛度等 等，对于一般 用 户， 系统默认情况 完全可以满足要求， 对于高级 用户可以根据工程实际选择规划 求 解 的 高 级 功能。运用 excel 软 件 进 行概率积分法预计参数求 解，详 细周密的数据准 备、组织和清晰求解思路 是非常重要的，数据准备 有误，思路混乱就会导致 求参失败。本文根据作者 工作实际编制了详细的技 术路线，以保证求参工作 的顺利进行。图 5 就是技 术路线图。图 1 自定义 erfcai 函数试验算例图 1 中 所 示 “下 沉 值” 一 列 调 用 erfcai 函 数 的 格 式为:= erfcai( sqrt( 3. 1415926) ( d6-800) /200) erfcai( sqrt( 3. 1415926) d6 /200)( 6)( 6) 式为 规程 中规定模型中的 erf 函数在该算例中的具体表达公式，其中，800 为工作面长度; 200 为工作 面宽度，d6 为 x 一列的值，即距离 x 轴的距离。计算结果表明，自己定义的 erfcai 函数能够正确计算出误差函数的值。通过建立 erfcai 函数，满足了建立概率积分法预计模 型的需要。有了 erfcai 函数就可以根据( 1) 、( 2) 、( 3) 、( 4) 和( 5) 式编写 excel 环境下的开采沉陷数学计算模型了，本 文仅列出用于 excel 计算的下沉值数学函数式，如下:w( x，y) = m q cos ( ( 4 /180) 3. 1415926) ( 1 /sqrt( 3. 1415926 ) ) ( erfcai ( -sqrt ( 3. 1415926 ) ( x / ( h1 /tgb) ) ) -erfcai ( -sqrt ( 3. 1415926 ) ( ( x-200-s1-s2 ) / ( h1 /4工程实例下面举一个实例。tgb ) ) ) ) ( 1 / sqrt ( 3. 1415926 ) ) ( erfcai ( -sqrt4. 1准备数据根据采矿工作实际情图 5 技术路线图( 3. 1415926) ( y / ( h2 / tgb) ) ) -erfcai( -sqrt( 3. 1415926) ( ( y-( 900-s3-s4) ( sin( a + st) / sin( st) ) ) / ( h3 / tgb) ) ) )( 7)( 7) 式就是用来计算 q，tan，s1 ，s2 ，s3 ，s4 7 个预计参 数的数学模型，其中，m，h1，h2，h3， 为采矿工程常数，为 采厚，走向和倾向采深以及煤层倾角; x，y 为预计变量。计 算水平移动系数 b，就要用到( 5) 式的水平移动预计数学模 型，本文不予介绍。3excel 软件的规划求解功能介绍excel 用户在完全安装软件的情况下，就可以直接调 用该功能了。excel2007 用户可以按照下面的方式加载该 功能，图 2 中给出了详细的调用方法。点击图 2 右图中规划求解按钮就可以启动 “规划求解 参数” 对话框，如图 3。况搜集、整理和计算出各个所需地质采矿条件常数，比如:采深、工作面长宽、煤层厚度和倾角等。根据工作面实际 情况建立工作面( 矩形工作面) 独立坐标系，并进行坐标系 统转换把观测站坐标转换到独立坐标系内。根据观测站观 测情况，整理、计算出观测线上各观测点最终下沉累加值。 根据周边矿区观测站情况，运用类比法整理、计算出参数 初值，把这些数据按照规划求解要求输入电子表格中。图 6 就是该工程实例的数据准备结果。图 3 规划求解对话框图 4 规划求解选项对话框图 6 数据准备252测绘科学第 36 卷4. 2 装入数学模型进行预计计算并设置规划求解条件在图 6 “下沉预测值” 列第一行单元格中输入( 7) 式， 注意，在装入预计模型时一定要认真，要把观测站坐标和 地质采矿条件常数作为系数要输进去，所求参数用 “ $ *$ ” 格式输入， $ * $ 表示列值， 表示行值，软 件在进行迭代运算时会知道并自动改变参数值，而达到收 敛条件时，该参数值就是计算结果值，不要有未知数，否 则计算会失败。然后拖住此列首行单元格右下角拖至数据 结尾处，软件会自动计算出下沉预测值。整个规划求解过 程和结果如下所示。图 8 实测值用规划求解结果预计结果对照曲线图运用 excel 软件计算了几个重要的精度评价指标。首先利用图 7 中 “下沉值预测值” 和 “实测值” 两列数据计 算得到相关系数为 0. 99989，相关性非常好，说明求参结果 可靠; 然后又计算了拟合残差中误差为 5. 6945mm，满足精 度要求; 又计算了最大残差占最大下沉值的比例为 13. 979/17001% 。综合以上评价结果，运用文中方法求得的概 率积分法预计参数是可靠的，满足精度要求。5结束语首先通过研究建立了在 excel 软件环境下的基于概率积分法的开采沉陷预计模型，为达到目的，对系统自带的 误差函数 erf( x) 进行了改造，满足了建立模型的需要; 然 后研究了 excel 软件的规划求解功能的基本操作方法，最 后结合实测数据得到了概率积分法预计参数，并进行了精 度评价，用该法求得的参数满足精度要求。图 7 规划求解结果图 7 中第 3 列就是数学模型预计值， 第 5 列是下沉值减去实测值，第 6 列是第 5 列的平方， 第 7 列最下为 第 6 列的总和值。excel 软件规划求解是以最小二乘原参考文献则为迭代条件的，即:( f预计 f实测 )为最小， 所以要21郭广礼，汪云甲 . 概率积分法参数的稳健估计模型设置求参目标单元格即 “sum 平方” 为最小， 见图 3 最上面 “设置目标单元格”， 按照 “ $ * $ ” 格 式 输 入 “sum 平方” 下面数值所在单元格，见图 3 中间 “可 变单元格”，同样按照 “ $ * $ ” 格式输入预计参 数值所在单元格的行列值即可。 点击选项按钮， 设置选 项，由于此问题比较复杂，可以增加 “最长预算时间”， 此例用 5 min 即 300 s， “精度” 设为 0. 01 ， 其他选项可 以不动，点击确定按钮， 返回规划求解参数对话框， 点 击 “求解” 按钮，进行计算， 软件会在计算过程中提醒 你是否保存计算结果，确定便得到最终的计算结果如图7 最下一行。4. 3 求参结果分析求参结果分析就是利用所求得参数进行开采沉陷预计 得到预计下沉值，然后再和实测下沉值数据进行对照分析， 以评价求参结果。图 8 是利用新参数计算下沉预计值和实 测下沉曲线图对照图，图中显示拟合效果良好。及其应用研究 j . 测绘学报，2000，( 2).2李培现，谭志祥，闫丽丽，邓喀中 . 基于支持向量机的概率积分法参数计算方法 j . 煤 炭 学 报，2010，( 8) .陈勇，郭文兵，文运平 . 基于 matlab 求取地表移 动预计参数的方法研究 j . 河南理工大学学报 ( 自然科学版) .郭凤娟，郭慧娟 . 基于 excel 的线性规划求解j .34内江科技，2010，( 7).5陈雪菱 . excel 规划求解在设施选址中的应用 j .工业工程，2010，( 2).6鲍新华，才文韬，李鸿雁，殷术奎，李哲锋 . excel规划求解在地下水资源管理中应用 j . 工程勘 察，2010，( 3) .李天林 . 基于线性规划模型的 excel 规划求解法的一个7应用 j . 连云港职业技术学院学报，2009，( 4).calculate predicted parameters of probability integral method using excelabstra