第 3卷 第 2期
2003年 6月
上 海 应 用 技 术 学 院 学 报
JOURNALOF~4．ANGHAI INSTI11兀EOFTEO-tNOLOGY
V 1．3 No．2
Jun．2003
文章编号 ：1671—7333(2003)02—0082—04
数据转换与 Excel软件在 多元非线性
回归分析中的应用
刘有毅
(上海应用技术学院 机械与自动化工程学院，上海 ，200235)
摘要： 应用 Excel软件的数据分析工具来处理形式经过适 当转换的试验数据 ，建立多元非线
性回归方程，有利于简化数据采集过程并能大幅度提高数据处理效率，降低试验成本。
关键词 ： 数据转换 ；多项式回归分析；多元非线性回归分析
中图分类号 ：o 212 文献标识码 ：A
Appl ication of Data Conversion and Excel Software
to the Multiple Non-Linear Regression Analysis
LIU You一
(Sd~ol of M融协 cal and A~toma6on f垃 tlg，shangI、8i Imitate of ￡hr_。lo ，shar 200235，C n8)
~dostraet：W e carl process the testing data which has been properly transformed using the data analytic tools，
to build up the poly non—linear regressive equation．That is beneficial to simplify the process of data-collect—
ing，and greatly improves the efficiency of data-treatment and reduces the experimental cost as wel1．
Key words：data conversion；polynom ial regression an alysis；multiple non-linear regression an alysis
在现代许多学科的研究领域中，由于所研究的对象以及过程比较复杂，各种参数之间的相互关系一般
难以用理论推导的方法求得相应的函数关系式。因此目前主要还是用试验的方法进行研究，即根据试验
得到的数据，建立经验公式 ，来表达被研究过程中因变量与各 自变量之间的相关关系。在此过程中，采用
便捷的数据分析工具 ，不仅可以加快数据处理的速度 ，而且还将为采用科学的试验方案创造有利的条件。
1 Excel软件的回归分析功能
在教学和工程实践中经常遇到一个因变量与一个或多个 自变量线性相关的现象。其相关关系如式
(1)所示 ：
Y= b0+b aXl+b2x2+⋯ +6，，l z (1)
要建立上述经验公式，需要对通过试验获得的若干相关数据进行分析处理，求得式中 b0、6】、b2⋯
b 这一系列待定系数。如果采用常规方法来处理数据 ，运算相当繁琐 ，且容易出错。特别是当 自变量
个数较多 、数据量较大时 ，更是如此。而采用 Excel来处理试验数据，则可以获得相当令人满意的效果。
以 Excel 97为例 ，它能处理多达 16个 自变量与一个因变量之间的相关关系；能在快速处理数据的同时 ，
收稿 日期 ：2003—04—3O
作者简介：刘有毅 (1957一)，男，高级工程师
维普资讯
第 2期 刘有毅：数据转换与 Excel软件在多元非线性回归分析中的应用
获得回归分析的精度等参数。由于Excel是最常用的软件之一，因此采用该软件处理数据可节省因添
置专用软件而发生的费用。
2 Excel软件在多元非线性回归分析中的应用
因变量与 自变量之间，在许多情况下是非线性相关的。例如切削力、切削温度与切削用量之间的关
系，其经验公式的类型如式(2)所示。要用 Excel来处理这类问题 ，试验数据必须经过适当的转换 ，将某
些非线性经验公式转化为式(1)所示的线性形式：
Y = 仳 F11zF22⋯z (2)
对式(2)两边取对数得 ：
lny= lnc+F1lnx1+F2lnx2+⋯ +F lnx (3)
显然，式(3)和式(1)的结构形式是相同的。经过以上的线性化处理以后，就可以用 Excel软件来处理了。
试验和工程实践中常见的问题可以分为两类 ：一类是已知变量之间的经验公式的结构类型 ，在求解
待定的系数后，建立经验公式 ；另一类则需要在判定经验公式类型的基础上，进行分析处理 ，建立经验公
式。例如切削温度与切削用量之间的关系属于前一种类型。
例 1 已知切削温度与切削用量之间的关系为：
0= C＆ F2 (4)
式中：0为切削温度，℃ ；＆。为切削深度，mm；厂为进给量，ram／r； 为切削速度 ，m／min；C为与刀具材
料等有关的系数。
通过试验，得到一系列测量数据 ，经分析处理后建立经验公式。为建立切削温度的经验公式 ，以往
为数据和图形处理方便 ，有时就采用双对数坐标纸进行图解作业 。对应地在采集试验数据时，采用单因
数试验法。目前许多教科书仍然采用此种方法。这种方法试验工作量大，所得经验公式 的精度较低。
显然 ，传统的数据处理方法对试验设计存在不利 的影响。而采用 Excel软件的数据分析工具则可以非
常方便地解决多元 回归方程问题。在此条件下 ，我们可以采用正交试验等方法来安排试验，用较少的时
间和较低的成本来获取相关信息。正交试验对试验次序有严格规定 ，同时对试验的安排还有一个基本
的要求 ：即自变量要取等间隔数值。对于本例，采用四因素三水平的正交试验，试验数据见表 1。
表 1 切削温度正交试验数据表
试验号 v／(m·s ) f／(mm·r一 ) ap／mm 0／~(2 lnv lnf lnap ln0
1 40 0．1 1 518．9 3．688 9 —2．302 6 0 6．251 7
2 40 0．22 2．23 597 3．688 9 —1．514 1 0．802 6．391 9
3 40 0．5 5 693 3．688 9 —0．963 1 1．609 4 6．541
4 74．83 0．1 2．23 692 4．315 2 —2．302 6 0．8O2 6．539 6
5 74．83 0．22 5 798 4．315 2 —1．514 1 1．609 4 6．682 1
6 74．83 0．5 1 840 4．315 2 —0．693 1 0 6．733 4
7 140 0．1 5 924．8 4．941 6 —2．302 6 1．609 4 6．829 6
8 140 0．22 1 968 4．941 6 — 1．514 1 0 6．875 2
9 140 0．5 2．23 1 121．6 4．941 6 —0．693 1 0．802 7．022 5
对表 1左侧的实测数据作对数转换 ，得到
表右各因素的对数值。用 Excel软件的数据分
析工具对这些对数值进行处理，得到表 2的回
归分析结果。取反对数后 ，得：
0： 157．45 0·4 ／o· 4a ·039 (5)
至此，就建立了切削温度与切削用量之间
的相关关系的经验公式。
表 2 回归分析结果
维普资讯
上海应用技术学院学报 第 3卷
3 在 多项式 回归分析 中的应用
在非线性相关关系试验数据的分析处理过程中，多项式相关关系是经常遇到的情况。一般情况下，
函数都可以分段用多项式来逼近。例如，在用数控机床加工曲线轮廓时就常常采用抛物线插补。其实
质就是曲线 的多项式逼近。因此多项式回归分析就成了曲线拟合的又一种重要方法。
Y= a0+alX+a2x 十⋯ +am x (6)
式(6)为多项式相关的关系表达式。同样地，我们也可以对其进行转换。
若 1(X)， 2( )，⋯，CM(x)分别是 X的一次、二次、⋯、M 次多项式，则(6)式也可以用 (z)，J
=1，2，⋯，rn来 表示 ：
Y= bo+bl 1(z)+b2 2(z)+⋯ +bail?M(X) (7)
将 (X)看作新变量 ，则式(7)就是一个 M 元线性回归方程。于是 X，Y之间的多项式相关关系就
可以转化为一般多元线性回归方程式的形式。同样可以用回归分析工具进行处理。
根据正交多项式的性质，查询有关资料得到一组满足正交性的多项式：
t(z)=
z)=(x
，
-
一
2~)2一
z = ( (x
，
- x)
(宰 ) 一 (宰 ) +
+t(z)= 。(z) (z)一 一。(z)
(8)
式中：h为自变量的取值间隔，h：X2一z1=z3一X2=⋯=XN—XN一1。为便于数据处理，例中 h=100；
N 为所选样本的个数。例中 N=7。对于 N 个数据只能配到N一1次多项式。
在(8)式中，k+1≤N一1。即：多项式的项数最多为 N一1次。
例 2 通过试验测得二等标准温度灯的灯丝电流和灯丝温度之间的一组数据如表 3所示。根据表
3的数据 ，求 出灯丝电流与温度之间的相关关系的经验公式。
表 3 灯丝温度与电流实测数据
首先 ，我们利用 Excel的图表功能获得温度与电流之间的关系曲线如图 1所示。由该图推测，该曲
线与多项式曲线比较接近，可以用多项式相关类型来逼近。
其次 ，根据试验数据以及变量之间的回归关 系确定选用几次多项式。由于 Excel的数据分析工具
具有强大的计算功能，因此考虑选用四次多项式观察效果。如果精度还不够，可以增加至五次多项式。
利用正交多项式(8)有：
Y = bo+bl 1(z)+62 2(z)+⋯ +bM CM(X) (9)
第三步 ，根据试验次数确定 N=7，多项式的次数为 4，查相应的正交多项式表 ，取出其中有用的部
分，加人 因变量数据后建立表 4。
一 —
函 瞬 一
维普资讯
第 2期 刘有毅 ：数据转换与 Excel软件在多元非线性回归分析中的应用
图 1 灯丝温度与电流关系
表 4 多项式回归分析数据表
序号 1 2 ~3／6 7,tb4／12 Y
第四步 ，利用数据分析工具 ，立即可以获得 回归分析结果，如表 5所示。
分析表 5中的数据可知，采用四次多项式逼近，经验公式已经具有较高的精度，能够满足实际使用
的要求。
表 5 回归分析结果
由 农 5得 ：
：
5．767 143+0．804 893~1(z)+0．050 940~2( )一0．018 167× 1
3( )一
0．000 929× z) (10)
将 h=100，x一=1 100以及式(8)中的 ( )代人(10)式 ，整理后得到所要求的回归方程：
= 5．557 809+0．826 089( )+0．056 127( ) 一
0．003 028( 3_0．000 542( ) (11)
4 结 语
用传统方法建立多自由度线性或非线性的回归方程 ，运算过程 比较繁琐而且容易出错。因此常采
用单因数试验法获取试验数据，然后按 自变量分别进行图解作业，最后合成经验公式。在陆健中、孙家
宁编的《金属切削原理与刀具》一书中，车削力的测定实验采用的也是这种方法。这种方法可以降低数
据处理的难度，但无法处理多因数正交试验数据 ，对试验方法和过程有较多的限制，使试验成本增高 ，而
所得经验公式的精度有限。试验和实验教学的实践表明，采用 Excel软件既可以处理单因数试验的数
据，也可以处理多因数正交试验的数据。用此种方法所得经验公式具有足够的精度。同时可以获得回
归方程的精度等有关数据。特别是当自变量个数较多、数据量较大时 ，其优越性更显著，从而为采用正
交试验等各种优化试验方法创造了非常有利的条件。
除了上述几种形式的非线性相关的经验公式，凡试验数据在经过适当转换 以后，回归方程能转化成
式(1)所示的线性结构的，均可采用 Excel软件进行回归分析，得到相关的经验公式。
参考文献：
[1] 费业泰．误差理论与数据处理[M]．北京：机械工业出版社，1982．
[2] 杨惠连．误差理论与实验设计[M]．北京：机械工业出版社，1988．
维普资讯
