Excel 在建立数学模型的应用….doc

《环境质量评价》实验指导 （适用专业：农业资源与环境、水土保持与荒漠化专业） 黑龙江八一农垦大学植物科技学院资环系 目 录 实验一 Excel 在建立数学模型的应用…………………………………………………1 实验二 用Excel进行等标污染指数、等标污染负荷、污染负荷比的运算方法…9 实验三 Excel在大气环境用Excel模板进行有关河流湖泊水质…21 实验五 环境系统最优化……………………………………………………………… 26 实验一 Excel 在建立数学模型的应用 一、实验目的：练习掌握线性回归分析、曲线拟合及参数估计等数学建模问题 三、实验内容： 1、污水处理的线性回归分析: 表2-4 某污水处理厂3、4月份的日常监测台帐 序号 3月份记录(mg/L) 4月份记录(mg/L) 入水COD 出水COD 入水 出水计算值 出水COD 1 678 123 695 138.48 152 2 631 118 654 132.86 156 3 942 216 777 149.71 190 4 1022 173 856 160.53 202 5 940 184 824 156.15 202 6 948 150 1054 187.66 226 7 802 197 885 164.51 196 8 992 156 932 170.94 208 9 1010 197 833 157.38 158 10 728 128 885 164.51 165 11 800 136 933 171.08 138 12 826 154 788 151.22 119 13 691 156 973 176.56 152 14 543 98 715 141.22 134 15 771 186 1028 184.1 162 16 690 175 871 162.59 138 17 743 108 807 153.82 120 18 712 102 900 166.56 158 19 584 134 771 148.89 123 20 841 118 755 146.7 139 21 870 182 855 160.4 127 22 1120 186 682 136.69 121 23 654 144 757 146.97 175 24 695 152 743 145.05 138 例2-2 某污水处理厂提供的3、4月份的日常监测台帐如表2-4所示，试根据3月份的数据建立其出水COD对应入水COD的线性回归模型，然后用 4月份的数据进行验证。 解:首先建立Excel的工作表，输入污水处理厂监测的原始数据。在2.2 中已介绍了Microsoft Excel的“分析工具库”。线性回归也是属于该工具库的内容。在“工具”菜单中，单击“数据分析”命令。如果“数据分析”命令没有出现在“工具”菜单中，则需要通过加载宏安装“分析工具库”，与此同时也将“规划求解”安装备用。如图2-5所示。 完成了加载宏的安装过程，在“工具”菜单中，单击“数据分析”命令，选择线性回归操作。按照对话框要求在Y 值输入区域输入对因变量数据区域的引用，该区域必须由单列数据组成。这里选择输入3月份的出水COD的数据区域；在 X 值输入区域输入对应入水COD数据。回归统计的一些主要结果如表2-5。 图2-5 加载宏安装“分析工具库”和“规划求解” 表2-5 出水COD对应入水COD回归统计结果 Multiple R 0.630237 Intercept 43.25682 X Variable 1 0.136996 标准误差 26.22009 观测值 24 因此，出水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月份数据）之间的相关关系显著。这里需要注意的是前后两个相关系数所具有的不同含意，前者表示模型中两个变量间的线性关系，后者表示的