1、Excel 在建立数学模型的应用环境质量评价实验指导（适用专业：农业资源与环境、水土保持与荒漠化专业）黑龙江八一农垦大学植物科技学院资环系目 录实验一 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.942
3、0891010197833157.3815810728128885164.5116511800136933171.0813812826154788151.2211913691156973176.561521454398715141.22134157711861028184.116216690175871162.5913817743108807153.8212018712102900166.5615819584134771148.8912320841118755146.713921870182855160.4127221120186682136.6912123654144757146.97175
4、24695152743145.05138例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个。查阅相关关系
6、检验表，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)300V重度污染健康人群明显强烈症状, 提前出现某些疾病表3-5 二氧化硫和可吸入颗粒物的 API 分级标准。（g/
7、nm3）空气质量描述 空气质量等级 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种污染物 (
8、对应于Ii,n)的浓度限值； Ci,n+1转折点上 i种污染物 (对应于Ii,n+1 )的浓度限值。1） 确定监测点的API指数及首要污染物。当各污染物的分指数Ii 计算完毕后，取API = Max (I1,I2,In) 为该监测点所在区域的空气污染指数（API），相应的该项污染物即为该区域的首要污染物（Critical Pollutants）。每天，我们分别计算出各监测点的污染指数，这个指数所对应级别就定义为这个监测点的空气质量级别，对应的污染物就是这个监测点的主要污染物。API越小、空气质量越好。使用API比使用级别说明空气质量更详细。比如 API 等于101和API等于200，都属于级，
9、但实际上101是接近良好的水平，而200是接近中度污染的水平。目前我们采用的办法是各测点报空气污染指数，全市报级别并报平均空气污染指数和首要污染物。例3-2 用分析仪器测得某监测站点某日的二氧化硫日均浓度值为80g/nm3，当日测得的可吸入颗粒物浓度值是200g/nm3，计算API并指明首要污染物。解：根据二氧化硫日均浓度值80g/nm3，查表3-3 API 在50-100之间，插值计算：同理，根据测得的可吸入颗粒物浓度值是200g/nm3，计算API因此该测点的污染指数是125，首要污染物是可吸入颗粒物。实验二 用Excel进行等标污染指数、等标污染负荷、污染负荷比的运算方法一、实验目的：掌
10、握用Excel运算等标污染指数、等标污染负荷、污染负荷比的方法二、实验原理：等标污染指数等标污染负荷污染物的等标污染负荷评价范围内的等标污染负荷污染负荷比污染源对于这个评价范围的污染负荷比该污染物对于这个评价范围的污染负荷比三 实验内容：例4-5 已知某地区建有造纸厂，酿造厂和食品厂。 其污水排放量和污染物监测结果如表4-4,试确定该地区的主要污染物和主要污染源表4-4:各厂污水排放量和污染物浓度（mg/l）(附污染物排放标准)项目排放标准造纸厂酿造厂食品厂污水量(m3/s)0.421. 420.63挥发酚0.50.570.150.08COD(Cr)100758865532SS70636188
11、120S1.04.620.010.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
13、$3C11:E14从区域C11复制到区域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中有两个“$”符号保证了在向酿造厂、食品厂进行横向复制，向CO
14、D、SS、S等项目进行纵向复制时，不会脱离该评价范围的总等标污染负荷所在单元，$B$15。（4）污染负荷比汇总计算将计算所得的各污染源的单项污染负荷比，分别按行和列的方向求和。单元 I11 有“=SUM(F11:H11)”，并扩展到I11:I14。（5）污染物和污染源排序比较污染物和污染源的污染负荷比数值，由大到小进行排序。各单元中输入的算式如表4-5。 实验三 Excel在大气环境质量评价中的应用一 实验目的：要求学生熟练掌握Excel模版进行大气环境质量评价计算二 实验原理：（1）我国烟气抬升高度的计算方法（2）给定风速条件下地面的最大浓度： （3）熏烟型扩散模式三 实验内容例5-2已知,
15、 北京处于 116.28E, 40.0N， 求三月上旬的日出与日落时间(北京时间), 并画出纳布可夫日高图。解：三月上旬 -5，计算正午12点h：h90 -（-）= 45计算日出日落真太阳时，由 0=SinSin+ CosCosCos=85.7，求出距正午12点时间为 t=85.7/15= 5.71 (h)=5小时43 分；画出纳布可夫日高图如图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 稳定度，考虑叠加效果。 （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式 ；有：在危险风速条件下，有H=Hs；求得危险风速；由5-51式，地面最大浓度处；查表5-10代入5-46,，
19、解出 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.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令，由5-
