《EXCL在数学建模和土木工程中的应用.doc》由会员分享，可在线阅读，更多相关《EXCL在数学建模和土木工程中的应用.doc（38页珍藏版）》请在三一办公上搜索。
1、环境质量评价实验指导（适用专业：农业资源与环境、水土保持与荒漠化专业）黑龙江八一农垦大学植物科技学院资环系目 录实验一 Excel 在建立数学模型的应用1实验二 用Excel进行等标污染指数、等标污染负荷、污染负荷比的运算方法9实验三 Excel在大气环境质量评价中的应用13实验四 用Excel模板进行有关河流湖泊水质评价的应用21实验五 环境系统最优化 26 实验一 Excel 在建立数学模型的应用一、实验目的：练习掌握线性回归分析、曲线拟合及参数估计等数学建模问题二、实验原理：数学模型的建立过程中，从数据分析、参数估计直至模型的检验，数据计算的工作量十分巨大，没有计算机的帮助要完成这些工作
2、是很难想象的。Microsoft Excel就是完成该项工作的一种简便有效的工具。三、实验内容：1、污水处理的线性回归分析: 表2-4 某污水处理厂3、4月份的日常监测台帐序号3月份记录(mg/L)4月份记录(mg/L)入水COD出水COD入水出水计算值出水COD1678123695138.481522631118654132.861563942216777149.7119041022173856160.532025940184824156.1520269481501054187.662267802197885164.511968992156932170.9420891010197833157
3、.3815810728128885164.5116511800136933171.0813812826154788151.2211913691156973176.561521454398715141.22134157711861028184.116216690175871162.5913817743108807153.8212018712102900166.5615819584134771148.8912320841118755146.713921870182855160.4127221120186682136.6912123654144757146.9717524695152743145.0
4、5138例2-2 某污水处理厂提供的3、4月份的日常监测台帐如表2-4所示，试根据3月份的数据建立其出水COD对应入水COD的线性回归模型，然后用 4月份的数据进行验证。解:首先建立Excel的工作表，输入污水处理厂监测的原始数据。在2.2 中已介绍了Microsoft Excel的“分析工具库”。线性回归也是属于该工具库的内容。在“工具”菜单中，单击“数据分析”命令。如果“数据分析”命令没有出现在“工具”菜单中，则需要通过加载宏安装“分析工具库”，与此同时也将“规划求解”安装备用。如图2-5所示。完成了加载宏的安装过程，在“工具”菜单中，单击“数据分析”命令，选择线性回归操作。按照对话框要求
5、在Y 值输入区域输入对因变量数据区域的引用，该区域必须由单列数据组成。这里选择输入3月份的出水COD的数据区域；在 X 值输入区域输入对应入水COD数据。回归统计的一些主要结果如表2-5。图2-5 加载宏安装“分析工具库”和“规划求解”表2-5 出水COD对应入水COD回归统计结果Multiple R0.630237Intercept43.25682X Variable 10.136996标准误差26.22009观测值24因此，出水COD对应入水COD的线性回归的模型形式是：Y = 0.137X + 43.257相关系数 R=0.63，观测值24个。查阅相关关系检验表，R0.01(22)=0.
6、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)300V重度污染健康人群明显强烈症状, 提前出现某些疾病表3-5 二氧化硫和可吸入颗粒物的 API 分级标准。（g/nm3）空气质量描述 空气质量等
7、级 API 二氧化硫浓度 可吸入颗粒物 严重污染 V500 2620 400 2100 中度污染 IV 300 1600 轻度污染 III200 250 350 良 II 100 150 150 优 I50 50 50 将监测点的各项污染物浓度日均值与各自的分级标准限值相比较，确定对应于该浓度值时API所在的API指数区间，再按照插值法计算该污染物浓度的API值。式中：Ii第 i种污染物的污染分指数； Ci第 i种污染物的实测浓度值；Ii,n第 i种污染物转折点的污染分项指数； Ii,n+1第 i种污染物+1转折点的污染分项指数； Ci,n转折点上i种污染物 (对应于Ii,n)的浓度限值； C
8、i,n+1转折点上 i种污染物 (对应于Ii,n+1 )的浓度限值。1） 确定监测点的API指数及首要污染物。当各污染物的分指数Ii 计算完毕后，取API = Max (I1,I2,In) 为该监测点所在区域的空气污染指数（API），相应的该项污染物即为该区域的首要污染物（Critical Pollutants）。每天，我们分别计算出各监测点的污染指数，这个指数所对应级别就定义为这个监测点的空气质量级别，对应的污染物就是这个监测点的主要污染物。API越小、空气质量越好。使用API比使用级别说明空气质量更详细。比如 API 等于101和API等于200，都属于级，但实际上101是接近良好的水平，
9、而200是接近中度污染的水平。目前我们采用的办法是各测点报空气污染指数，全市报级别并报平均空气污染指数和首要污染物。例3-2 用分析仪器测得某监测站点某日的二氧化硫日均浓度值为80g/nm3，当日测得的可吸入颗粒物浓度值是200g/nm3，计算API并指明首要污染物。解：根据二氧化硫日均浓度值80g/nm3，查表3-3 API 在50-100之间，插值计算：同理，根据测得的可吸入颗粒物浓度值是200g/nm3，计算API因此该测点的污染指数是125，首要污染物是可吸入颗粒物。实验二 用Excel进行等标污染指数、等标污染负荷、污染负荷比的运算方法一、实验目的：掌握用Excel运算等标污染指数、
10、等标污染负荷、污染负荷比的方法二、实验原理：等标污染指数等标污染负荷污染物的等标污染负荷评价范围内的等标污染负荷污染负荷比污染源对于这个评价范围的污染负荷比该污染物对于这个评价范围的污染负荷比三 实验内容：例4-5 已知某地区建有造纸厂，酿造厂和食品厂。 其污水排放量和污染物监测结果如表4-4,试确定该地区的主要污染物和主要污染源表4-4:各厂污水排放量和污染物浓度（mg/l）(附污染物排放标准)项目排放标准造纸厂酿造厂食品厂污水量(m3/s)0.420. 420.63挥发酚0.50.570.150.08COD(Cr)100758865532SS70636188120S1.04.620.010
11、.01解：使用 Excel 进行成批的数据运算，如图4-1所示。操作步骤如下： （1）首先计算各污染源的单项等标污染负荷。单元 C11 对应于造纸厂挥发酚的等标污染负荷，输入公式：“ = C4/$B4*C$3 ” 相当于执行 “=0.57/0.5*0.42”C4的内容是造纸厂挥发酚浓度，B4的内容是挥发酚排放标准， C3是造纸厂的污水流量。“$”是Excel的绝对引用符号，以写有公式的单元C11为源区域，复制到目标区域C11:E14，$B4中的“$”保证了在向酿造厂、食品厂进行横向复制时，不会脱离排放标准一栏。C$3中的“$”符号保证了在向COD、SS、S等项目进行纵向复制时，不会脱离排放流量
12、一行。（2）等标污染负荷求和将计算所得的各污染源的单项等标污染负荷，分别按行和列的方向求和。单元 B11 有“=SUM(C11:E11)”，并扩展到B11:B14；单元 B15 有“=SUM(B11:B14)”，并扩展到B15:E15。单元 F15 有“=C15/$B$15”，并扩展到F15:I15。（3）计算各单项的污染负荷比单元 F11 对应于造纸厂挥发酚占该评价范围总等标污染负荷的污染负荷比，输入公式：“= C11/$B$15 ”图4-1 计算污染源等标污染负荷的Excel工作表 表4-5 计算污染源等标污染负荷算式。单元坐标算式C11= C4/$B4*C$3C11:E14从区域C11复
13、制到区域C11:E14 B11=SUM(C11:E11)B11:B14从区域B11复制到区域B11:B14 B15=SUM(B11:B14)B15:E15从区域B15复制到区域B15:I15 F11= C11/$B$15F11:H14从区域F11复制到区域F11:H14 F15= C15/$B$15F15:I15从区域F15复制到区域F15:I15I11=SUM(F11:H11)I11:I14从区域I11复制到区域I11:I14以写有公式的单元 F11为源区域，复制到目标区域F11:H14，$B$15中有两个“$”符号保证了在向酿造厂、食品厂进行横向复制，向COD、SS、S等项目进行纵向复制时
14、，不会脱离该评价范围的总等标污染负荷所在单元，$B$15。（4）污染负荷比汇总计算将计算所得的各污染源的单项污染负荷比，分别按行和列的方向求和。单元 I11 有“=SUM(F11:H11)”，并扩展到I11:I14。（5）污染物和污染源排序比较污染物和污染源的污染负荷比数值，由大到小进行排序。各单元中输入的算式如表4-5。 实验三 Excel在大气环境质量评价中的应用一 实验目的：要求学生熟练掌握Excel模版进行大气环境质量评价计算二 实验原理：（1）我国烟气抬升高度的计算方法（2）给定风速条件下地面的最大浓度：（3）熏烟型扩散模式三 实验内容例5-2已知, 北京处于 116.28E, 40
15、.0N， 求三月上旬的日出与日落时间(北京时间), 并画出纳布可夫日高图。解：三月上旬 -5，计算正午12点h：h90 -（-）= 45计算日出日落真太阳时，由 0=SinSin+ CosCosCos=85.7，求出距正午12点时间为 t=85.7/15= 5.71 (h)=5小时43 分；图5-11 纳布可夫日高图真太阳时h画出纳布可夫日高图如图5-11，使用日高图可查出其他时刻的日高角（例如8：00）。由经度求时间补偿，t=(120-116.28)4分/度= 14.9(分)由日落日出真太阳时: 125小时43 分，求得日出的北京时间为 6：02；日落的北京时间为 17：28。例5-3 某电
16、厂烟囱有效高度150 m，S02排放量151gs。夏季晴朗下午，大气稳定度 B级， 烟羽轴处风速为4ms。若上部存在逆温层，使垂直混合限制在1.5km之内。确定下风向 3km和 11km处的地面轴线 S02浓度。解：按照5-56计算烟流达到逆温层的z 查表5-10 2=0.057025, 2 =1.09356；代入5-46, ； 解出XD值为：4967m。（1）3km4.97km, （2）24.97km 11 km 例5-4 某电厂烟囱有效高度150m，S02 排放量151gs。夜间和上午有效烟囱高度风速为4ms，夜间稳定度E级。若清晨烟流全部发生熏烟现象，确定下风向16km处的地面轴线S02
17、浓度。解：查表5-10解出E级16km处y=733m, z= 96m；hf = H+z=150+296=342(m)式5-62为熏烟扩散时地面上的横向扩散参数yf：例5-7 某地( P=100 kPa)两工厂烟囱在城市的位置以图5-13中的平面坐标表示A(15,15)、B(150,150)（以 m计），高度分别为100m 和 80m,SO2 排放量分别为180g/s 和130 g/s；TSP 排放量分别为340g/s 和300 g/s；烟气温度均为100,当地平均气温冬季为 -10,春秋季节为 15；其烟气流量分别为 135 M3/s和 124M3/s。（1） 分别求两污染源在风速与 X 方向
18、平行，C 稳定度和相应情况的热排放率Qh, 危险风速, 地面绝对最大浓度值及发生部位（以平面坐标表示）。（2） 若在接受点C(950, 110)，风向平行X，地面风速2.5 m/s, C 稳定度，考虑叠加效果。 YXA (15,15)B(150,150)C(950,110)图5-13工厂和测点位置的平面坐标（3） 若在接受点C(950,110)，地面风速0.8 m/s, 其他条件同上，考虑叠加效果。解：（1）求解地面绝对最大浓度；由5-35式计算污染源热释放率，如 A 源冬季有抬升公式5-40式：；由表5-4 no =0.292， n1=3/5， n2=2/5；对照公式5-40式 ；有：在危险
19、风速条件下，有H=Hs；求得危险风速；由5-51式，地面最大浓度处；查表5-10代入5-46,，解出 xm ,并计算；表5-12 地面绝对最大浓度的计算用表ABCDEF1A(15,15)B(150,150)2项目和公式单位冬季春秋冬季春秋3TK11085110854Hsm10010080805QvM3/s1351351241246Qh=350*T/Ts*QvkJ/s13934.310767.412798.939890.087B=0.292*Qh(3/5)*Hs(2/5)m2/s564.721483.782490.8208420.4748u=B/Hsm/s5.64724.83786.135265
20、.25599z=Hs*SQR(2)m141.421141.421113.1371113.13710Xm=(z/2)(1/2)m2525.212525.211980.091980.0911y=1*X(1)m238.39238.39192.221192.2212=z/(2*PI()*B*e*Hs*y)5.59E-078.45E-076.38E-079.64E-0713SO214源强Qg/s18018013013015Cm=q*1000mg/m30.100.150.080.1316TSP17源强Qg/s34034030030018Cm=q*1000mg/m30.190.290.190.2919令，由
21、5-53式计算地面绝对最大浓度值为：当我们求解此类问题时，无论是手工计算还是使用Excel的电子表格，使用表5-12形式的计算用表可以起到减少差错和提高效率的作用。特别是对于多种污染因子和气象因素的类似操作，使用Excel一次输入算式后即可用复制粘贴方法进行成批的数据运算。在一次完成模板制作后，只需改变自变量数值，结果将自动生成。表5-13列出此例中对应单元的算式和获得表5-12运算结果的复制粘贴使用方法。地面绝对最大浓度出现的坐标应是：A(2540,15)、B(2130,150)。 (2) 求解接受点C(950,110)的污染物浓度； 用幂指数风速廓线模式（5-2），表5-13 对应单元格的
22、算式和复制粘贴区域源单元对应单元格的算式复制粘贴区域C7=350*C4/373*C6C7-F7C8=0.292*C7(3/5)*C5(2/5)C8-F8C9=C8/C5C9-F9C10=C5*SQRT(2)C10-F10C11=(C10/0.106803)(1/0.917595)C11-F11C12=0.232123*C110.885157C12-F12C13=C10/(2*PI()*C8*EXP(1)*C4*C12)C13-F13C16=C$13*C15*1000C16-F16; C19-F19将地面风速折算成高空风速。按步骤（1）中抬升公式计算目前气象条件和排放条件下的抬升高度和有效源高，
23、代入地面任一点浓度公式（5-31）：；使用Excel的电子表格，输入算式后制成模板，能够适应解此类问题的需要。如表5-14。表5-14 叠加求解高架源污染物地面浓度的 Excel表格模板ABCDEFG1项目和公式单位源A源B受点C参数值2源强Qg/s18013010.9242793坐标xm1515095010.1771544坐标ym1515011020.9175955Hsm1008020.1068036QhkJ/s107679890.1U102.57Uh = U10 *(Hs/10)pm/s3.96223.7893p0.28H=0.292*Qh(3/5)*Hs(2/5)/um122.1110.
24、969H=H+Hsm222.1190.9610X=x坐标差m93580011Y=y坐标差m95-4012y=1*X(1)m98.67685.43113z=2*X(2)m56.83149.25414C=Q*1000/(PI()*u*y*z)*exp(-(y2/2/y2+H2/2/z2)mg/m30.00080.00130.00215表5-15 对应单元格的算式和复制粘贴区域源单元对应单元格的算式复制粘贴区域C3-C7作为已知条件输入C8= $G$7 *(C6/10)$G$8C8-D8C9=0.292*C7(3/5)*C6(2/5)/C8C9-D9C10=C9+C6C10-D10C11=$E4-C
25、4C11-D12C13=$G$4*C11($G$3)C13-D13C14=$G$6*C11($G$5)C14-D14C15=C3*1000/(PI()*C8*C13*C14)*EXP(-(C122/2/C132+C102/2/C142)C15-D15E15=C15+D15(3) 求解小风条件下，接受点C(950,110)的污染物浓度；这时不能直接使用表5-14的Excel模板，因为小风时烟气抬升高度H(m)改变为（5-45）；浓度预测模式变化为5-63 ；式中按（5-64）计算，G按（5-65）计算。使用Excel函数，能够根据（5-67）获得正态函数(S)的值, Excel电子表格解小风问题
26、的模板，如表5-16、表5-17所示。表5-16 解小风问题的Excel表格模板ABCDEFG1项目和公式单位源A源B受点C参数值2源强Qg/s1801303坐标xm15150950010.354坐标ym15150110020.215dT/dz(不得小于0.01）K/m0.010.016Hsm100807QhkJ/s10767.439890.08U100.88Uh = U10 *(Hs/10)pm/s1.2679151.212573p0.29H=5.5*Qh(1/4)* (dT/dz+0.0098)(-3/8)m243.8604238.733410H=H+Hsm343.8604318.7334
27、11X=x坐标差m93580012Y=y坐标差m95-4013=x2+y2+(01/02*H)21.21E+069.24E+0514S=u*x/(01*)2.80E-033.00E-0315(S)=Normdist(s,0,1,true()0.5011150.50119716G=exp(-u2*0.5/012)*(1+ sqrt(2*PI()*S*exp(S2/2)*(S)7.10E+024.06E+0217C(小风）=2*Q*G*1000/ (2*PI()(3/2)*02*2)10-5 mg/m35.263.749.0018表5-17 解小风问题对应单元格的算式和复制粘贴区域源单元对应单元格
28、的算式复制粘贴区域C10=5.5*C8(1/4)*(C6+0.0098)(-3/8) C10-D10C11=C10+C7C11-D11C14=C122+C132+($G$4/$G$5*C11)2C14-D12C15=C9*C12/($G$4*C14)C13-D13C16=NORMDIST(C15,0,1,TRUE()C16-D16C17=EXP(-C92*0.5/$G$42)*(1+ SQRT(2*PI()*C15*EXP(C152/2)*C16)C17-D17C18=2*C3*C17*1000/(2*PI()(3/2)*$G$5*C142)C18-D18E18=C18+D18通过该例题的分析
29、，我们提供了在 Excel环境下的计算模板，只要代入相应的原始条件，便能方便地获得结果。每一个中间环节均在表格中显示出来，既免除了复杂的编程操作，又更方便使用，学生可按此例建立自己的实用模板。 实验四 用Excel模板进行有关河流湖泊水质模型的计算与预测一 实验目的：练习用Excel模板进行有关河流湖泊水质模型的计算与预测。二 实验原理：一维模型的浓度计算公式：如果忽略扩散项，沿程的坐标x=ut,dC/dt=-k1C , 代入初始条件 x=0, C=C0方程的解为 无限大均匀流场：若在无限大均匀流场中，坐标原点设在污染物排放点，污染物浓度的分布呈高斯分布，则方程式的解为。（6-13）式中 Q
30、是连续点源的源强 (g/s)，结果 C 的单位为(g/m3= mg/L)。考虑河岸反射时的污染物迁移扩散方程：自然界的河流都有河岸，河岸对污染物的扩散起阻挡及反射作用，增加了河水中污染。多数排污口位于岸边的一侧。对于半无限均匀流场，仅考虑本河岸反射。如果岸边排放源位于河流纵向坐标 x=0处，岸边排放连续点的像源与原点源重合，下游任一点的浓度为： （6-14）对于需要考虑本岸与对岸反射的情况，如果河宽为 B，只计河岸一次反射时的二维静态河流岸边排放连续点源水质模型的解为（6-15）完成横向均匀混合的距离：根据横向浓度分布状况，若某断面上河对岸浓度达到同一断面最大浓度的5，定义为污染物到达对岸。这
31、一距离称为污染物到达对岸的纵向距离，用镜像法计算。本岸C(Lb,0) 计算时不计对岸的反射项。污染物到达对岸C(Lb,B)，只需要考虑一次反射。使用6-15式计算浓度，并按定义C(Lb,B)/C(Lb,0)=0.05 解出的纵向距离Lb为：（6-16）虽然理论上讲，用镜像法计算时，如果纵向距离相当大，两岸反射会多次发生。然而，多数情况下，随着纵向距离的增加，虚源的作用衰减得十分迅速。正态分布曲线趋于平坦，横向浓度分布趋于均匀。实际上应用中，若断面上最大浓度与最小浓度之差不超过5，可以认为污染物已经达到了均匀混合。由排放点至完成横向均匀混合的断面的距离称为完全混合距离。由理论分析和实验确定的完全
32、混合距离，按污染源在河流中心排放和污染源在河流岸边排放的不同情况，可将完全混合距离表示为：（6-17）中心排放情况，（6-18）岸边排放情况，三 实验内容： 例6-3 一重污染均匀河段，已知河流流速为1.3km/d,BOD衰减速度常数K1=0.30 l/d ,水中复氧速度常数K2=0.65 l/d，起始段面河水中BOD和溶解氧浓度值分别为42mg/L和4.6mg/L，分析该河流DO、BOD5的发展趋势并绘制相应图形。解：（1）使用原模型根据6-26式解出溶解氧达到 0的点A, 对于x0 河流中的溶解氧开始上升，求得 LB 的数值。由此往后的溶解氧和BOD的变化仍遵循以此点状态为初始条件的S-P模型。计算富氧点起始B，LB的数值。LB=fCs=(0.65/0.3)*9.2=20mg/L（3）对于A-B河段，原S-P模型失效，由于A-B河段中必然有LfD，即k2Lk1Cs，BOD的降解速度受到获氧速度的制约，6-25的第一式成为 这时BOD的降解速度是一个常数。积分并由 x=ut,代入边界条件，LA、LB求解 AB段长度xAB 有： 即在D5单元格内输入算式“=（F5-F6）*B4/(B3*D2)”。（4）以复氧点起始B为初始条件，重复S-P模板的相应过程求解河流以后各点的DO、BOD5的发展趋势，绘制DO与失效模型的比较，如
