EXCL在数学建模和土木工程中的应用

《环境质量评价》实验指导（适用专业：农业资源与环境、水土保持与荒漠化专业）黑龙江八一农垦大学植物科技学院资环系目录实验一Excel在建立数学模型的应用…………………………………………………1实验二用Excel进行等标污染指数、等标污染负荷、污染负荷比的运算方法……9实验三Excel在大气环境质量评价中的应用……………………………………13实验四用Excel模板进行有关河流湖泊水质评价的应用……………………………21实验五环境系统最优化………………………………………………………………261实验一Excel在建立数学模型的应用一、实验目的：练习掌握线性回归分析、曲线拟合及参数估计等数学建模问题二、实验原理：数学模型的建立过程中，从数据分析、参数估计直至模型的检验，数据计算的工作量十分巨大，没有计算机的帮助要完成这些工作是很难想象的。MicrosoftExcel就是完成该项工作的一种简便有效的工具。三、实验内容：1、污水处理的线性回归分析:表2-4某污水处理厂3、4月份的日常监测台帐序号3月份记录(mg/L)4月份记录(mg/L)入水COD出水COD入水出水计算值出水COD1678123695138.481522631118654132.861563942216777149.7119041022173856160.532025940184824156.1520269481501054187.662267802197885164.511968992156932170.9420891010197833157.3815810728128885164.5116511800136933171.0813812826154788151.2211913691156973176.561521454398715141.22134157711861028184.116216690175871162.5913817743108807153.8212018712102900166.5615819584134771148.8912320841118755146.713921870182855160.4127221120186682136.6912123654144757146.9717524695152743145.051382例2-2某污水处理厂提供的3、4月份的日常监测台帐如表2-4所示，试根据3月份的数据建立其出水COD对应入水COD的线性回归模型，然后用4月份的数据进行验证。解:首先建立Excel的工作表，输入污水处理厂监测的原始数据。在2.2中已介绍了MicrosoftExcel的“分析工具库”。线性回归也是属于该工具库的内容。在“工具”菜单中，单击“数据分析”命令。如果“数据分析”命令没有出现在“工具”菜单中，则需要通过加载宏安装“分析工具库”，与此同时也将“规划求解”安装备用。如图2-5所示。完成了加载宏的安装过程，在“工具”菜单中，单击“数据分析”命令，选择线性回归操作。按照对话框要求在Y值输入区域输入对因变量数据区域的引用，该区域必须由单列数据组成。这里选择输入3月份的出水COD的数据区域；在X值输入区域输入对应入水COD数据。回归统计的一些主要结果如表2-5。图2-5加载宏安装“分析工具库”和“规划求解”表2-5出水COD对应入水COD回归统计结果MultipleR0.630237Intercept43.25682XVariable10.136996标准误差26.22009观测值243因此，出水COD对应入水COD的线性回归的模型形式是：Y=0.137X+43.257相关系数R=0.63，观测值24个。查阅相关关系检验表，R0.01(22)=0.515；由于这里|R|R0.01(n-2)，说明3月份数据的出水COD与入水COD两者之间，存在高度显著的线性相关关系。使用模型Y=0.137X+43.257，根据4月份入水COD数据求出出水COD的计算值；选择Y值输入区域为4月份的出水COD数据，在X值输入区域输入对应出水COD的计算值，再次进行线性回归操作：观测值仍为24个，相关系数R=0.45，查阅相关关系检验表，R0.05(22)=0.404；由于这里R0.05(n-2)|R|≤R0.01(n-2)，说明根据3月份数据归纳出的数学模型与新的数据观测组（4月份数据）之间的相关关系显著。这里需要注意的是前后两个相关系数所具有的不同含意，前者表示模型中两个变量间的线性关系，后者表示的是数学模型估算值与观测值之间的相关关系。4月份出水COD对应入水COD数据与模型估算值的比较如图2-6所示050100150200250050010001500计算值进水CODmg/L出水CODmg/L2结构分析和曲线拟合从建立数学模型的过程中，对于两个变量x和y的试验或观测数据，我们需要确定模型的结构，然后使用最小二乘法进行参数估值来获得数学模型。半机理模型结构的建立，在于对事物运动或反应机理的认识。对于用初等函数表示的模型结构，使用Excel能够帮助我们迅速获得模型的完整形式，并能分析结构的合理性。这就是曲线拟合；即寻求能够代表x和y函数关系的数学模型。使用Excel工作表进行曲线拟合的操作，是在图表菜单下，选定数据系列，使用趋势线命令，获得对话框，如图2-7所示。Excel趋势线所提供的模型结构形式，如表2-6所示。4表2-6Excel趋势线所提供的模型结构名称趋势线计算方程备注线性bmxy(2-11)代表斜率b代表截距m对数bxcyln(2-12)c和b代表常数，函数ln代表自然对数多项式55221....xcxcxcby(2-13)可选择多项式阶数，b和Ci代表常数乘幂bcxy(2-14)其中c的b为常数指数bxcey(2-15)c和b为常数，e代表自然对数的底数移动平均nAAAFntttt11.....(2-16)n是“周期”选项,设置移动平均使用数据点数目，用来消除数据的波动图2-7Excel图表菜单下，添加趋势线命令对话框例2-3十二胺降解实验数据如表2-7所示，使用Excel工作表进行曲线拟合。表2-7十二胺降解实验数据时间(h)013579232731浓度(mg/L)2.32.221.921.61.521.070.730.50.45解：在趋势线命令中分别选择模型结构形式为线性和指数模型，拟合结果如图2-8所示。指数模型又分别指定和不指定是否必须通过初始浓度2.3mg/L。注意在图2-7中有个选项页，如果需要在图中显示出模型的表达式、R2，或者需要限制趋势线必须通过初始浓度标记的函数点，均在选项页进行操作。5从获得的三个数学模型来看，指数模型tey0519.016.2与实验数据拟合的相关系数高达98.6%（R2=0.9726）,应是较好的选择。十二胺降解实验和模型y=2.1608e-0.0519xR2=0.9726y=-0.0568x+2.0367R2=0.9004y=2.3e-0.0547xR2=0.967300.511.522.5010203040(h)mg/L图2-8用线性和指数模型拟合十二胺降解实验3用Excel进行参数估计例2-4根据对某一种反应的分析，获得灰箱模型为：21lnxbxacy试根据表2-8所示的一组实验观测值，进行灰箱模型的参数估值，并讨论其是否可信。解：首先建立Excel的工作表，输入已知的实验数据，在新的两列中分别通过输入计算式，用复制命令或拖动鼠标求得对应的x10.5和ln(x2)，该反应测定的原始实验数据和两列中间计算结果均列入表2-8。在“工具”菜单中，单击“数据分析”命令，选择回归操作。按照对话框要求在Y值输入区域输入因变量y数据区域的引用(第3列)；在X值输入区域输入第4,5两列。回归分析的一些主要结果如表2-9。因此经确定参数后模型的形式是：21ln3.472.651.13xxy其相关系数R=0.94；查阅表2-3,n-2=11时的5%和1%置信度的R分别为0.553和0.684，说明该模型与观测值之间相关关系高度显著。6表2-8原始实验数据和两列中间计算结果第1列第2列第3列第4列第5列x1x2yx10.5ln(x2)0.21.514.80.38070.405512.516.610.91631.43.315.61.22371.19391.83.516.91.42291.25282.24.5717.41.60491.519534.8218.41.93321.57283.45.519.92.0841.70473.8618.42.22781.79184.6718.42.49841.945957.520.22.62652.01495.48.1720.42.75072.10055.88.519.72.87122.14016.69.521.73.10262.2513表2-9回归分析的一些主要结果参数名称参数估值回归统计Intercept13.50766MultipleR0.94304XVariable16.72025RSquare0.889324XVariable2-4.30189观测值134空气污染指数空气污染指数的分级标准是：（1）空气质量指数API50对应的污染物浓度为国家空气质量日均值一级标准；（2）API100对应的污染物浓度为国家空气质量日均值二级标准；（3）API更高值段的分级对应于各种污染物对人体健康产生不同影响时的浓度限制。目前试行的空气污染指数分级标准如表3-4所示。7为了对空气污染综合分级，首先需按单项污染因子计算空气污染指数。参照上述空气污染指数分级标准的一些原则，每个单项有着各自的分级标准，表3-5列出了二氧化硫和可吸入颗粒物的分级标准。空气污染指数API的计算方法如下：计算各单项污染物的API指数。表3-4空气污染指数分级标准(试行)（2000年4月27日发布）API空气质量级别空气质量状况对健康的影响0～50I优可正常活动51～100II良可正常活动101～150IIIIII1轻微污染长期接触,易感人群出现症状151～200III2轻度污染长期接触,健康人群出现症状201～250IVIV1中度污染一定时间接触后,健康人群出现症状251～300IV2中度重污染一定时间接触后,心脏病和肺病患者症状显著加剧300V重度污染健康人群明显强烈症状,提前出现某些疾病表3-5二氧化硫和可吸入颗粒物的API分级标准。（μg/nm3）将监测点的各项污染物浓度日均值与各自的分级标准限值相比较，确定对应于该浓度值时API所在的API指数区间，再按照插值法计算该污染物浓度的API值。空气质量描述空气质量等级API二氧化硫浓度可吸入颗粒物严重污染V50026204002100中度污染IV3001600轻度污染III200250350良II100150150优I5050508ninininininiiiIIICCCCI,,1,,1,,)()()(式中：Ii—第i种污染物的污染分指数；Ci—第i种污染物的实测浓度值；Ii,n—第i种污染物ｎ转折点的污染分项指数；Ii,n+1—第i种污染物ｎ+1转折点的污染分项指数；Ci,n—ｎ转折点上i种污染物(对应于Ii,n)的浓度限值；Ci,n+1—ｎ转折点上i种污染物(对应于Ii,n+1)的浓度限值。1）确定监测点的API指数及首要污染物。当各污染物的分指数Ii计算完毕后，取API=Max(I1,I2,…In)为该监测点所在区域的空气污染指数（API），相应的该项污染物即为该区域的首要污染物（CriticalPollutants）。每天，我们分别计算出各监测点的污染指数，这个指数所对应级别就定义为这个监测点的空气质量级别，对应的污染物就是这个监测点的主要污染物。API越小、空气质量越好。使用API比使用级别说明空气质量更详细。比如API等于101和API等于200，都属于Ⅲ级，但实际上101是接近良好的水平，而200是接近中度污染的水平。目前我们采用的办法是各测点报空气污染指数，全市报级别并