Excel 在建立数学模型的应用Word文档格式.docx
《Excel 在建立数学模型的应用Word文档格式.docx》由会员分享，可在线阅读，更多相关《Excel 在建立数学模型的应用Word文档格式.docx（40页珍藏版）》请在冰豆网上搜索。
164.51
196
8
992
932
170.94
208
9
1010
833
157.38
158
10
728
128
165
11
800
136
933
171.08
138
12
826
154
788
151.22
119
13
691
973
176.56
14
543
98
715
141.22
134
15
771
186
1028
184.1
162
16
690
175
871
162.59
17
743
108
807
153.82
120
18
712
102
900
166.56
19
584
148.89
20
841
755
146.7
139
21
870
182
855
160.4
127
22
1120
682
136.69
121
23
144
757
146.97
24
145.05
例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回归统计结果
MultipleR
0.630237
Intercept
43.25682
XVariable1
0.136996
标准误差
26.22009
观测值
因此，出水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所示
2结构分析和曲线拟合
从建立数学模型的过程中，对于两个变量x和y的试验或观测数据，我们需要确定模型的结构，然后使用最小二乘法进行参数估值来获得数学模型。
半机理模型结构的建立，在于对事物运动或反应机理的认识。
对于用初等函数表示的模型结构，使用Excel能够帮助我们迅速获得模型的完整形式，并能分析结构的合理性。
这就是曲线拟合；
即寻求能够代表x和y函数关系的数学模型。
使用Excel工作表进行曲线拟合的操作，是在图表菜单下，选定数据系列，使用趋势线命令，获得对话框，如图2-7所示。
Excel趋势线所提供的模型结构形式，如表2-6所示。
表2-6Excel趋势线所提供的模型结构
名称
趋势线计算方程
备注
线性
（2-11）
代表斜率b代表截距m
对数
（2-12）
c和b代表常数，函数ln代表自然对数
多项式
（2-13）
可选择多项式阶数，b和Ci代表常数
乘幂
（2-14）
其中c的b为常数
指数
（2-15）
c和b为常数，e代表自然对数的底数
移动平均
（2-16）
n是“周期”选项,设置移动平均使用数据点数目，用来消除数据的波动
图2-7Excel图表菜单下，添加趋势线命令对话框
例2-3十二胺降解实验数据如表2-7所示，使用Excel工作表进行曲线拟合。
表2-7十二胺降解实验数据
时间（h）
27
31
浓度（mg/L）
2.3
2.22
1.92
1.6
1.52
1.07
0.73
0.5
0.45
解：
在趋势线命令中分别选择模型结构形式为线性和指数模型，拟合结果如图2-8所示。
指数模型又分别指定和不指定是否必须通过初始浓度2.3mg/L。
注意在图2-7中有个选项页，如果需要在图中显示出模型的表达式、R2，或者需要限制趋势线必须通过初始浓度标记的函数点，均在选项页进行操作。
从获得的三个数学模型来看，指数模型
与实验数据拟合的相关系数高达98.6%（R2=0.9726）,应是较好的选择。
图2-8用线性和指数模型拟合十二胺降解实验
3用Excel进行参数估计
例2-4根据对某一种反应的分析，获得灰箱模型为：
试根据表2-8所示的一组实验观测值，进行灰箱模型的参数估值，并讨论其是否可信。
首先建立Excel的工作表，输入已知的实验数据，在新的两列中分别通过输入计算式，用复制命令或拖动鼠标求得对应的x10.5和ln（x2），该反应测定的原始实验数据和两列中间计算结果均列入表2-8。
在“工具”菜单中，单击“数据分析”命令，选择回归操作。
按照对话框要求在Y值输入区域输入因变量y数据区域的引用（第3列）；
在X值输入区域输入第4,5两列。
回归分析的一些主要结果如表2-9。
因此经确定参数后模型的形式是：
其相关系数R=0.94；
查阅表2-3,n-2=11时的5%和1%置信度的R分别为0.553和0.684，说明该模型与观测值之间相关关系高度显著。
表2-8原始实验数据和两列中间计算结果
第1列
第2列
第3列
第4列
第5列
x1
x2
y
x10.5
ln（x2）
0.2
1.5
14.8
0.3807
0.4055
2.5
16.6
0.9163
1.4
3.3
15.6
1.2237
1.1939
1.8
3.5
16.9
1.4229
1.2528
2.2
4.57
17.4
1.6049
1.5195
4.82
18.4
1.9332
1.5728
3.4
5.5
19.9
2.084
1.7047
3.8
2.2278
1.7918
4.6
2.4984
1.9459
7.5
20.2
2.6265
2.0149
5.4
8.17
20.4
2.7507
2.1005
5.8
8.5
19.7
2.8712
2.1401
6.6
9.5
21.7
3.1026
2.2513
表2-9回归分析的一些主要结果
参数名称
参数估值
回归统计
13.50766
0.94304
6.72025
RSquare
0.889324
XVariable2
-4.30189
4空气污染指数
空气污染指数的分级标准是：
（1）空气质量指数API50对应的污染物浓度为国家空气质量日均值一级标准；
（2）API100对应的污染物浓度为国家空气质量日均值二级标准；
（3）API更高值段的分级对应于各种污染物对人体健康产生不同影响时的浓度限制。
目前试行的空气污染指数分级标准如表3-4所示。
为了对空气污染综合分级，首先需按单项污染因子计算空气污染指数。
参照上述空气污染指数分级标准的一些原则，每个单项有着各自的分级标准，表3-5列出了二氧化硫和可吸入颗粒物的分级标准。
空气污染指数API的计算方法如下：
计算各单项污染物的API指数。
表3-4空气污染指数分级标准（试行）（2000年4月27日发布）
API
空气质量级别
空气质量状况
对健康的影响
0～50
I
优
可正常活动
51～100
II
良
101～150
III
III1
轻微污染
长期接触,易感人群出现症状
151～200
III2
轻度污染
长期接触,健康人群出现症状
201～250
IV
IV1
中度污染
一定时间接触后,健康人群出现症状
251～300
IV2
中度重污染
一定时间接触后,心脏病和肺病患者症状显著加剧
>
300
V
重度污染
健康人群明显强烈症状,提前出现某些疾病
表3-5二氧化硫和可吸入颗粒物的API分级标准。
（μg/nm3）
空气质量描述
空气质量等级
API
二氧化硫浓度
可吸入颗粒物
严重污染
500
2620
400
2100
中度污染
IV
300
1600
轻度污染
200
250
350
良
II
100
150
优
50
将监测点的各项污染物浓度日均值与各自的分级标准限值相比较，确定对应于该浓度值时API所在的API指数区间，再按照插值法计算该污染物浓度的API值。
式中：
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是接近中度污染的水平。
目前我们采用的办法是各测点报空气污染指数，全市报级别并报平均空气污染指数和首要污染物。
例3-2用分析仪器测得某监测站点某日的二氧化硫日均浓度值为80μg/nm3，当日测得的可吸入颗粒物浓度值是200μg/nm3，计算API并指明首要污染物。
根据二氧化硫日均浓度值80μg/nm3，查表3-3API在50-100之间，插值计算：
同理，根据测得的可吸入颗粒物浓度值是200μg/nm3，计算API
因此该测点的污染指数是125，首要污染物是可吸入颗粒物。
实验二用Excel进行等标污染指数、等标污染负荷、污染负荷比的运算方法
掌握用Excel运算等标污染指数、等标污染负荷、污染负荷比的方法
等标污染指数
等标污染负荷
污染物的等标污染负荷
评价范围内的等标污染负荷
污染负荷比
污染源对于这个评价范围的污染负荷比
该污染物对于这个评价范围的污染负荷比
三实验内容：
例4-5已知某地区建有造纸厂，酿造厂和食品厂。
其污水排放量和污染物监测结果如表4-4,试确定该地区的主要污染物和主要污染源
表4-4:
各厂污水排放量和污染物浓度（mg/l）
（附污染物排放标准）
项目
排放标准
造纸厂
酿造厂
食品厂
污水量（m3/s）
0.42
1.42
0.63
挥发酚
0.57
0.15
0.08
COD（Cr）
100
758
865
532
SS
70
636
188
S
1.0
4.62
0.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等项目进行纵向复制时，不会脱离排放流量一行。
（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$3
C11:
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$15
F11:
H14
从区域F11复制到区域F11:
H14
F15
=C15/$B$15
F15:
I15
从区域F15复制到区域F15:
I11
=SUM（F11:
H11）
I11:
I14
从区域I11复制到区域I11:
以写有公式的单元F11为源区域，复制到目标区域F11:
H14，$B$15中有两个“$”符号保证了在向酿造厂、食品厂进行横向复制，向COD、SS、S等项目进行纵向复制时，不会脱离该评价范围的总等标污染负荷所在单元，$B$15。
（4）污染负荷比汇总计算
将计算所得的各污染源的单项污染负荷比，分别按行和列的方向求和。
单元I11有“=SUM（F11:
H11）”，并扩展到I11:
I14。
（5）污染物和污染源排序
比较污染物和污染源的污染负荷比数值，由大到小进行排序。
各单元中输入的算式如表4-5。
实验三Excel在大气环境质量评价中的应用
一实验目的：
要求学生熟练掌握Excel模版进行大气环境质量评价计算
二实验原理：
（1）我国烟气抬升高度的计算方法
（2）给定风速条件下地面的最大浓度：
（3）熏烟型扩散模式
三实验内容
例5-2．已知,北京处于116.28°
E,40.0°
N，求三月上旬的日出与日落时间（北京时间）,并画出纳布可夫日高图。
三月上旬δ≈-5°
，
计算正午12点hθ：
hθ≈90°
-（φ-δ）=45°
计算日出日落真太阳时，由0=SinφSinδ+CosφCosδCosω
ω=85.7°
，求出距正午12点时间为t=85.7/15=5.71（h）=5小时43分；
画出纳布可夫日高图如图5-11，使用日高图可查出其他时刻的日高角（例如8：
00）。
由经度求时间补偿，Δt=（120°
-116.28°
）×
4分/度=14.9（分）
由日落日出真太阳时:
12±
5小时43分，求得日出的北京时间为6：
02；
日落的北京时间为17：
28。
例5-3某电厂烟囱有效高度150m，S02排放量151g／s。
夏季晴朗下午，大气稳定度B级，烟羽轴处风速为4m／s。
若上部存在逆温层，使垂直混合限制在1.5km之内。
确定下风向3km和11km处的地面轴线S02浓度。
按照5-56计算烟流达到逆温层的σz
查表5-10γ2=0.057025,α2=1.09356；
代入5-46,
；
解出XD值为：
4967m。
（1）3km<
4.97km,
（2）2×
4.97km<
11km
例5-4某电厂烟囱有效高度150m，S02排放量151g／s。
夜间和上午有效烟囱高度风速为4m／s，夜间稳定度E级。
若清晨烟流全部发生熏烟现象，确定下风向16km处的地面轴线S02浓度。
查表5-10解出E级16km处σy=733m,σz=96m；
hf=H+σz=150+2×
96=342（m）
式5-62为熏烟扩散时地面上的横向扩散参数σyf：
例5-7某地（P=100kPa）两工厂烟囱在城市的位置以图5-13中的平面坐标表示A（15,15）、B（150,150）（以m计），高度分别为100m和80m,SO2排放量分别为180g/s和130g/s；
TSP排放量分别为340g/s和300g/s；
烟气温度均为100℃,当地平均气温冬季为-10℃,春秋季节为15℃；
其烟气流量分别为135M3/s和124M3/s。
（1）分别求两污染源在风速与X方向平行，C稳定度和相应情况的热排放率Qh,危险风速,地面绝对最大浓度值及发生部位（以平面坐标表示）。
（2）若在接受点C（950,110），风向平行X，地面风速2.5m/s,C稳定度，考虑叠加效果。
（3）
若在接受点C（950,110），地面风速0.8m/s,其他条件同上，考虑叠加效果。
（1）求解地面绝对最大浓度；
由5-35式计算污染源热释放率，如A源冬季有
抬升公式5-40式：
由表5-4no=0.292，n1=3/5，n2=2/5；
对照公式5-40式
有：
在危险风速条件下，有ΔH=Hs；
求得危险风速
由5-51式，地面最大浓度处
查表5-10代入5-46,
，解出xm,并计算
表5-12地面绝对最大浓度的计算用表
A
B
C
D
E
F
A（15,15）
B（150,150）
项目和公式
单位
冬季
春秋
ΔT
゜K
110
85
Hs
m
80
Qv
M3/s
135
124
Qh=350*ΔT/Ts*Qv
kJ/s
13934.3
10767.4
12798.93
9890.08
B=0.292*Qh^（3/5）*Hs^（2/5）
m2/s
564.721
483.782
490.8208
420.474
u=B/Hs
m/s
5.6472
4.8378
6.13526
5.2559
σz=Hs*SQR
（2）
141.421
113.1371
113.137
Xm=（σz/γ2）^（1/α2）
2525.21
1980.09
σy=γ1*X^（α1）
238.39
192.221
192.22
Ω=σz/（2*PI（）*B*e*Hs*σy）
5.59E-07
8.45E-07
6.38E-07
9.64E-07
SO2
源强Q
g/s
180
130
Cm=qΩ*1000
mg/m3
0.10
0.13
TSP
340
0.19
0.29
令
，由5-
升级会员 