Excel在化工计算中的若干应用

2024年9月14日发(作者：)

广 东 化 工 2019年 第7期

·

266

· 第46卷总第393期

Excel在化工计算中的若干应用

刘松晖

(杭州职业技术学院 临江学院，浙江 杭州 310018)

[摘 要]在化工问题研究和化工教学中，经常涉及两方面的复杂计算问题：一是把实验数据整理为方程式，以定量描述过程自变量与因变量

之间的关系，即回归分析问题，其中主要包括一元线性回归、多元线性回归；二是非线性方程求解问题，包括一元非线性方程和多元非线性方

程的求解。本文以几个典型的化工计算问题为例介绍如何采用Excel 2010的回归分析、单变量求解和规划求解功能解决上述计算问题，从中可

以看出相对采用Matlab、Fortran等语言编程计算方法，Excel具有快捷、高效的特点。

[关键词]Excel；一元线性回归；多元线性回归；单变量求解；规划求解

[中图分类号]TQ011 [文献标识码]A [文章编号]1007-1865(2019)07-0266-02

Several Applications of Excel to Chemical Engineering Computation

Liu Songhui

(Linjiang institute, Hangzhou Vocational & Technical College, Hangzhou 310018, China)

Abstract: Two types of complex computation problems are common in chemical engineering research and education. One is the regression analysis problems

that make experiment data to math equations to descript quantitatively relationship between the independent variable and dependent variable, this includes univariate

linear regression and multivariate linear regression; The other is the solving problems of nonlinear equations which includes univariate nonlinear equations and

multivariate nonlinear equations. Some typical chemical engineering computation problems are taken for example in this paper to demonstrate how to solve those

problems with regression analysis function, goal seeking function and programming solver function of Excel 2010. As this paper shows, Excel is more efficient and

quicker than Matlab or Fortran programming computation.

Keywords: excel；univariate linear regression；multivariate linear regression；goal seeking；programming solver

在化工研究和教学过程中，经常会遇到许多复杂的计算问题。

[1]

这些问题大致可以归为两类：一是回归分析问题，即把实验数

据整理为方程，或已知方程的结构但需要求取方程的关键参数，

主要涉及一元线性回归、多元线性回归或者可以转化为线性回归

的非线性回归问题；二是化工方程的求解问题，其中许多是非线

性方程、多元非线性方程。

处理这两类计算问题，一般可利用Matlab或Fortran等数学

软件编程解决，但首先需要花费大量精力掌握这些复杂的编程语

言。再者，一次编程只能解决一个问题，通用性较差，人机界面

也不够友好。

Excel是微软公司开发的一款功能强大的电子表格软件，

Excel 2010版提供了强大的数据分析功能，其中的回归分析、单

变量求解和规划求解等功能特别适合用以解决前述的化工计算问

题，且操作起来方便快捷、计算结果简单直观。

本文以几个常见的化工计算问题介绍利用Excel 2010来解决

上述两类问题的步骤及需要注意的地方。

预测法或多元线性回归方法。下面以三个例子说明具体解题步骤。

2.1 趋势线预测法

例1对离心泵性能进行测试的实验中，得到流量q

v

、压头H

的数据如下表

[2]

，求qv与H的函数关系。

表1 标准溶液浓度与吸光值关系

Tab.1 The relationship between the concentration of standard

solution and the value of absorption

序号 流量q

v

/(m

3

/h)

1

2

3

4

5

6

0.000

0.738

1.316

2.483

3.181

3.531

压头H/m 序号 流量q

v

/(m

3

/h) 压头H/m

17.26

16.96

16.85

16.30

15.57

14.85

7

8

9

10

11

12

4.232

4.841

5.421

5.769

6.299

6.523

13.89

12.73

11.54

10.77

9.213

8.785

1 加载Excel 2010相应计算模块

Excel 2010中回归分析功能由分析工具库提供，方程规划求

解功能则由规划求解项提供，默认情况下这两项功能并没有加载

到Excel中，因此应该首先加载相应的功能模块。

点击Excel 2010的“文件”选项卡，在最左边下拉菜单中点

击“选项”，在弹出的“Excel选项”对话框中点击最左边选项中

的“加载项”，在右边“加载项”选项框中选择“分析工具库”，

然后点击对话框最下方的“转到”，将弹出“加载宏”对话框，

将其中的“分析工具库”和“规划求解项”选中即可。

2 利用Excel 2010进行回归分析

在利用Excel 2010进行回归分析时，对于只有一个自变量的

情形，可以采用趋势线预测法对曲线进行拟合；对于多元线性回

归情形则需要用到其回归数据分析功能；对于某些非线性回归情

形，可视情况将其转化为相应的线性回归问题，然后采用趋势线

Excel 2010解题操作步骤：

将表1中流量q

v

列、压头H列分别输入Excel工作表的A列

和B列中，同时选中A、B列，点击Excel 2010“插入”选项卡

中的“散点图”可得压头H和流量q

v

之间关系的散点图，选择图

中数据点右键点击，在弹出对话框中选择“添加趋势线”，根据化

工原理有关知识，离心泵压头与流量为二次函数关系，所以在弹

出的对话框中选择“多项式”，阶次设为2，勾选“显示公式”

和“显示R平方值”，点击“关闭”后如图1所示，从中可以看

出压头H和流量qv之间的关系为：

，其中0＜R

2

≤1表示曲线拟合

的可信程度，R

2

值越大说明拟合度越高。

[收稿日期] 2019-03-20

[作者简介] 刘松晖(1979-)，男，江西赣州人，硕士研究生，主要研究方向为化工过程建模。

2019年

第7期 广 东 化 工

第46卷 总第393期 ·

267

·

行数据分别输入Excel工作表中的A、B和C列中，点击Excel 2010

“数据”选项卡中的“数据分析”，在弹出的“数据分析”对话

框中选择“回归”，将弹出“回归”对话框。

将A列数据中设为“回归”对话框中的“Y值输入区域”，

B和C列数据设为“X值输入区域”，并在工作表中选择合适的

输出区域，即可得如图2中的分析结果。

图2中最下方表格中“Intercept”对应“Coefficients”列中的

项约为-0.01337，此即为方程的截距，也就是说方程(1)中

b

0

=-0.01337；“X variable 1”对应“Coefficients”列中的项约为

2.04*10

-4

，即方程(1)中b

1

=2.04*10

-4

；同理，方程(1)中b

2

=2.19*10

-4

。

回归结果为：。y=2.04*10

-4

x

1

+2.19*10

-4

x

2

-0.01337。

图2中第一、二张表格表明本次回归分析结果的可信程度，

其各项指标的含义可参见相关文献

[4]

。

图1 压头H与流量qv函数关系

Fig.1 The function relationship between the concentration of

pressure head H and the discharge qv

2.2 多元线性回归

例2利用分光光度法同时测定锌和铜，为了更准确测定，可

使用线性拟合法。文献

[3]

根据锌试剂与铜、锌形成配合物的吸收

光谱图，选择了5个波长分别测得这两种配合物的摩尔吸光系数

及混合物溶液吸光度如表2。

表2 Cu，Zn光度分析法试验数据

Tab.2 Spectrophotometric experimental data of Cu and Zn

波长λ/nm

(Cu)

(Zn)

吸光度(As)

500

400

770

6.7

550

1330

1880

308.9

575

1720

1930

105.5

600

1860

2190

19.0

650

570

1450

231.7

图 2 回归分析结果

Fig. 2 The result of regression analysis

根据朗博-比尔(Lambert-Beer)定律有以下关系：

(1)

其中，y为混合物溶液吸光度As，b

1

、b

2

为溶液中铜、锌的

浓度，x

1

、x

2

为某一波长时Cu和Zn配合物的吸收系数。可见该

问题是一个典型的二元线性回归问题。

Excel 2010解题操作步骤：

将表2中的“吸光度(As)”行，“(Cu)”行，“(Zn)”

表3 初始反应速度与分压的关系

Tab. 3 The relationship between initial reaction rate and partial pressures

序号

Nu*10

-2

Re*10

-4

Pr

1

1.80166

2.4465

7.76

2

1.68500

2.3816

7.74

3

1.50699

2.0519

7.70

4

1.27699

1.7143

7.67

5

1.07833

1.3785

7.63

6

0.83500

1.0352

7.64

7

0.402777

1.4202

0.71

8

0.56722

2.2224

0.71

9

0.7206

3.02088

0.71

2.3 非线性回归

例3流体在圆形直管内做强制湍流传热，努赛尔准数Nu，雷

诺数Re，普朗特准数Pr的实验数据已测如表3所示

[2]

，已知对流

传热准数关系式为

Nu=BRe

m

Pr

n

(2)

试求三参数之间的函数关系,即要求求出B、m和n。

10

0.8457

3.77722

0.71

11

0.9353

4.4459

0.71

12

0.95799

4.5472

0.711

Excel 2010解题思路：

从题中可以看出，Nu与Re和Pr存在非线性关系，而Excel

只能进行线性回归，因此必须将(2)式转化为多元线性回归问题。

(2)式两边去自然对数，可转化为：

Ln(Nu)=Ln(B)+mLn(Re)+nLn(Pr)

令

y=Ln(Nu)，x

1

=Ln(Re)，x

2

=Ln(Pr) (3)

则原问题化为求取方程y=mx

1

+nx

2

+Ln(B)的系数m、n和Ln(B)

的多元线性回归问题。将表3的数据按(3)式进行转换，之后的方

法步骤和2.2多元线性回归一样，最终结果为B=0.3473，

m=0.7836，n=0.4380。

将非线性函数转化为线性函数再进行回归是常用的方法，除

本例外还可以对其他多种类型的非线性函数进行线性化

[5]

。

系：

(3)

其中，K

w

=1*10，K

1

=5.8*10，K

2

=1.7*10

-8

。

Excel 2010解题操作步骤：

将各个参数名输入到Excel的A3-A8中，A1输入“方程左边”，

A2输入“C”，A9输入“pH值”；将各个参数的值输入到对应

的B3-B8中，B2输入1e-7，即假设溶液中的氢离子浓度为1*10-7，

B9输入“=log10(B2)”B1输入(3)的左边，注意(3)中C应该输入

B2，C1输入B3，依此类推，最终B1输入为：

“=B2+B4*B2/(B2+B6)+B5*B2/(B2+B7)-B8/B2-B3”。

单击Excel 2010的“数据”选项卡中“模拟分析”，选择其

中的“单变量求解”，在弹出的对话框中，“目标单元格”设为

“B1”，“目标值”设为0，“可变单元格”设为“$B2$2”，然

后点击“确定”可得氢离子浓度C=3.1*10-9，pH值为8.50。

单变量求解的实质是Excel不断改变C的值，使得方程左边

即B1单元格接近目标值，在满足一定的精度要求下，即停止计

算。Excel 2010可在“文件-选项-公式-计算选项”中查看和修改计

算精度，在单变量求解过程中应该试着改变精度多计算几次，只

有在结果不再发生较大变化时，此时的结果才符合我们的要求。

(下转第256页)

-14-10

3 利用Excel 2010求解化工方程

化工研究中的方程多为非线性方程，有时是多变量的非线性

方程组。对于一元非线性方程，可以利用Excel 2010的单变量求

解功能方便的求取其中一个解，并结合物理实际判定该解的合理

性；对于非线性方程组，则可利用Excel的规划求解功能。

3.1 单变量求解方法

例4已知溶液体系中含有浓度C

1

=0.02 mol/L的HCL，C

2

=0.02

mol/L的NH3，C

3

=0.02 mol/L的三羟乙基胺TEA，计算溶液的pH

值。已知在该体系中，氢离子浓度C与C

1

、C

2

和C

3

存在如下关

广 东 化 工 2019年 第7期

·

256

· 第46卷总第393期

加装冲淋系统。

2.2 加强高压高温设备的安全管理

使用者在经过专业培训且考核合格后，才能操作使用大型精

密仪器，平时应严格按规范对仪器设备进行维护保养，并对相关

人员进行考核。对于特种设备如高压气瓶，应按规范定期检验其

技术性能。须将气瓶置于阴凉且远离电热源的环境中，严禁将不

同种类的气瓶混杂放置；开阀门时须站立于气瓶的出气口侧面，

应缓慢开启以防静电的产生。严禁将钢瓶内的气体用尽，须维持

大于0.5 kg表压的剩余压力，须用专业气瓶柜固定气瓶以防摔倒。

严禁将易燃药品放入高温炉中加热。

2.3 规范药品试剂的使用管理

购置危险化学品须严格经高校主管部门审批，将化学试剂用

于实验教学时，必须严格按照《危险化学品安全管理条例》的规

范使用，必须用专业性危险药品仓库储存危险化学品，且由专业

实验人员负责管理，严禁将化学品混合存储，在储存危险药品的

范围内禁止使用明火。须用专门的有毒药品柜储存有毒试剂，在

有毒试剂的管理过程中须严格执行使用申请、领导审批、两人签

字等制度。应采取对策，使易燃品和助燃物两类物品的接触机会

减少，以预防燃烧甚至爆炸。根据每次实验对药品的实际要求，

需多少领多少，达到每次用药量的最小化目标。

2.4 加强对危险废弃物的安全管理

进行环境工程实验教学时，在不影响教学质量的前提下，应

尽量实现实验的小型化。加强对实验教学“三废”的处理力度，

废弃药剂须经过规范处理后才能倒掉，例如应收集废酸与废碱，

让其发生化学中和反应，最后再排放；对于有毒药品如氯化汞等，

须对其集中规范处理，以防止严重污染环境；有毒的固体废物必

须首先对其化学处理，最终在距生活区较远的特定地点进行安全

填埋，同时记录好填埋地点。对于产生有害气体的实验环节，须

在大风量通风橱内进行。

2.5 完善并认真落实安全管理制度

完善实验室安全管理制度，详细设立操作性较强的专门性管

理制度，提高管理的有效性。如实验室准入规范、实验室日志规

章、实验室耗材管理制度、大型精密仪器使用规范、定期安全核

查规范、实验人员防护规范、实验室处分制度、实验室假期管理

细则、实验室安全应急预案等，通过这些制度提高安全意识，规

范实验行为。运行过程中应适时签订责任书，进一步增强相应人

员的责任感，日常的节假日之前须对各实验分室进行统一的安全

检查。努力营造实验室安全文化，将实验室安全标志、仪器设备

安全标志等张贴齐全，强化实验安全氛围。

2.6 强化学生实验中的安全意识

学生是参与实验过程的主要组成部分，强化学生的实验安全

意识，有助于从根本上确保实验安全。应时常提醒学生按照规范

使用器具，不能将药品随意使用，对于废弃物应首先考虑回收，

确实不能回收的试剂须经无害化处理后才能排出。如果操作挥发

性溶剂，须戴上防毒面具；严禁在实验室中饮食或抽烟；做完实

验后须及时按照规范的方法洗手。实验时须穿好实验服，使用硫

酸等腐蚀性试剂时须佩戴护目镜与橡胶手套，操作时应谨慎操作，

以防止其溅出伤人。实验教学中对可能出现隐患的环节要早预防，

对学生的不规范操作须及时纠正，并制定相应的安全应急预案，

以便在紧急时刻可以有序救助。

3 结语

环境工程实验教学的安全管理是一个由完善硬件配置、严格

管理制度、强化安全意识等方面组成的系统性工程。做好安全工

作需要从细节做起、从点滴做起，需要每位参与者的努力，需要

每个部门的严抓落实。只有实验教师与学生的全力配合、实验室

管理员的认真负责、管理部门的严格管理支持，才能把各种安全

条例真正落实，才能保证实验教学的安全进行。今后在实验教学

管理中，须继续努力，让安全管理更加规范化、精细化，建设更

加安全高效的教学环境。

参考文献

[1]龙绛雪，姜艳宏，金磊，等．地方应用技术型高校实验室安全管理的探

索与实践[J]．高教论坛，2017(28)：27-29．

[2]常文英．大学化学开放实验安全管理体系研究[J]．实验室科学，2011，

14(3)：190-193．

[3]张庆，唐沈，朱宗强，等．高校环境工程专业实验室的安全管理现状及

对策[J]．教育教学论坛，2019(5)：12-13．

[4]吕绿洲，林海，董颖博，等．高校环境工程专业实验室的安全管理[J]．实

验技术与管理，2017，34(6)：243-245．

(本文文献格式：荣琨，杨茜，罗杰，等．高校环境工程专业实验

教学安全隐患与预防措施[J]．广东化工，2019，46(7)：255-256)

(上接第267页)

3.2 规划求解方法

例5以天然气为原料制取甲醇是将天然气先脱硫，再进行蒸

汽转化制取合成气。设循环比为x，驰放气中CO的摩尔分率为y，

由组分平衡式、CO转化率和选择性及驰放气组成的限制条件可

得方程组：

(4)

(5)

[6]

求x和y的大小。

Excel 2010解题操作步骤：

在Excel单元格A1、B1、C1、D1中分别输入“x”、“y”、“方

程(4)”、“方程(5)”；在C2单元格中输入方程(4)的左边、D2单元

格中输入方程(5)左边，其中x和y分别用A2和B2表示。A2和

B2输入0，即x和y的初值为0，如图3所示。

4 结论

本文介绍了利用Excel 2010进行化工方程回归分析和方程求

解的步骤，从中可以看出Excel在处理此类问题时非常便捷、简

单，无需学习复杂难懂的编程语言。

需要注意的是，本文并没有深入探讨回归分析中各个指标的

含义和方程求解时初值的选取、解的合理性判断等问题。因此，

在处理具体计算问题时，要想获得正确的结果，除了要掌握本文

所述的Excel操作方法之外，操作者还必须具备一定的相关数学

基础。

参考文献

[1]隋志军，杨榛，魏永明．化工数值计算与MATLAB[M]．上海：华东理

工大学出版社，2015．

[2]黄泽恩，高晓新．Origin软件在化工原理实验数据处理中的应用[J]．山

东化工，2016，45(6)：108-109．

[3]王中华，山桂云，林英武．Excel多元线性回归及在化学中的应用[J]．计

算机与应用化学，2005，22(9)：000817-820．

[4]贾俊平．统计学[M]．中国人民大学出版社，2016．

[5]赵延龄，温晓光，范福海．用Excel求取化工数据组回归方程[J]．计算

机与应用化学，2004，21(4)：142-146．

[6]郑立辉，吴高明．EXCEL在化工工艺计算中的应用[J]．化学工程师，

2001(4)：24-25．

图3 初始输入结果

Fig.3 The result of initial input

点击Excel 2010的“数据”选项卡中“规划求解”，弹出规划求

解参数对话框。将其中的“设置目标”设为$C$2，“目标值”为0，“通

过更改可变单元格”设为$A$2:$B$2，添加“遵循约束”为$D$2=0，

其余为默认选项，然后点击“求解”，A2和B2单元格分别变为

13.71711和0.707325，此即方程组的解。

(本文文献格式：刘松晖．Excel在化工计算中的若干应用[J]．广

东化工，2019，46(7)：266-267)