Excel在非线性方程求解过程中的应用

Excel在非线性方程求解过程中的应用
曹树国1、2
（1．重庆教育学院教务处，重庆 400067；2. 重庆大学计算机学院，重庆 400044）
摘要在实际工程应用或者“计算方法”课程的学习中，往往会遇到大量的非线性方程的求解，由于在某些特定的场合下无法找到合适的求解软件或不便安装相应的软件，但又为了能够较精确地求解方程的解，使用EXCEL来对非线性方程采用对分法及迭代法来求解方程的近似解，为广大工程设计者及学生在求解非线性方程的解时提供帮助。

关键词 Excel；非线性方程；解
1 引言
“计算方法”是工科学生学习的课程，该课程在工程方面应用相当广泛。

在实际的教学或者在工程应用中，需要进行工程计算时找不到合适的软件，如为此编写专门的程序来实现往往又不太现实，如采用手工计算的方式，则工作量又相当大，而且中间过程易出错。

为解决此问题，我们想到，EXCEL作为OFFICE 办公自动化软件的一部分，有着强大的计算功能，如何将工程或者教学过程中的数学问题直接采用EXCEL 来处理，以解决这些繁琐的计算问题，并能快速获取计算结果。

下面就举例来说明用EXCEL在非线性方程的数值解法中的应用，对如何采用EXCEL来计算工程中的数学问题起到抛砖引玉的作用。

2 求解过程
非线性方程的数值解法中常用的有对分法、迭代法、松驰法、牛顿法、割线法等，但最基本的是对分法和迭代法，我们利用对分法和迭代法的思想来说明使用EXCEL求解非线性方程的数值解的过程。

2.1 对分法
对分法适用于求有限区间内的单实根或奇重实根，其原理是，设f(x)在[a，b]上连续，若f(a)×f(b)<0，即f(a)>0，f(b)<0或f(a)<0，f(b)>0。

则根据连续函数的介值定理，在(a，b)内至少存在一点ξ，使f(ξ)=0。

为了能够求得方程在某个区间内的近似解，则需要将区间大小逐次减半缩小，直至得到满足条件的解为止。

即取[a，b]的中点x0=(a+b)/2，将区间一分为二，若f(x0)=0，则x0就是方程的根，否则就判断方程的根
x*在x0的左侧还是右侧，
若f(a)×f(x0)<0，则x*∈(a，x0)，令a1=a，b1= x0；
若f(a)×f(x0)>0，则x*∈(x0，b)，令a1= x0，b1=b；
按理逐次操作，所得到的新的区是都是前一区间的一半，若每次对分时所取区间中点都不是根，则上述过程将无限地进行下去，当n→∞时，这些区间最终必收缩为一点x*，显然x*就是所求之根。

但在实际应用中，不可能无限制地将上述步骤进行下去，只有当x n足够大（即区间对分次数足够多），x n的误差就可足够小，那么我们就可以将x n作为方程的近似解。

根据以上原理，以下面的例子来说明利用EXCEL来实现对分法求解方程近似解的过程。

例：求x3-3x+1=0在[0，1]中的实根近似解，精确到三位小数。

解：令f(x)= x3-3x+1，有f(0)×f(1)<0，所以方程x3-3x+1=0在[0，1]中必有一个实根。

首先在EXCEL的工作表中录入图1所示的数据。

图1
在D2单元格内输入“=(B2+C2)/2”，在E2单元格内输入“=D2^3-3*D2+1”，即求出f(x)在区间[0，1]中间处的值。

在图1中可以看到F2单元格内数字为1，此数字是用来设定标记用的，因为每次取得中间的f(x)值后都需要对f(a)×f(x)的正负号进行判断，以判定对分后所保留的区间。

为简化计算，我们将
f(a)的值大于0时计为“1”，小于0时计为“-1”。

要利用对分法的思想，在有区间初始值及计算出的中点x，x处的函数值f(x)及f(a)的正负值情况下，我们就可以判定新的区间的左右边界，即得到新的保留区间。

为了能够在EXCEL中进行判断，就需使用EXCEL 所提供的IF函数。

具体做法为：在B3单元格中输入“=IF(E2*F2<0，B2，D2)”，在C3单元格中输入“=IF(E2*F2<0，D2，C2)”，即在某一步k，f(a)与f(x)异号，根所在区间即为前一次区前的前半部分，如果是f(a)与f(x)同号，则根所在区间为前一次区间的后半部分。

同样，在F2单元格内输入
“=IF(B2^3-3*B2+1>0，1，-1)”，以设定f(a)的正负标志。

然后分别拖动B3、C3、D2、C2、F2单元格的“填充柄”将B3、C3、D2、C2、F2单元格填充至B15、C15、D15、E15、F15，可得图2所示的结果。

图2
可见，x∈(0.34729，0.34741209)，但我们需要考查此时解的精度能否达到要求，当k=14时，
b-a=0.34741209-0.34729=0.000122109<0.0005，达到精度要求。

为更精确求解，我们取
x=(0.34729+0.34741209)/2= 0.347351055≈0.3474，因此，用0.3474作为方程在[0，1]区间内的近似解，满足3位小数的精度要求。

2.2 迭代法
由于对分法即可用于求隔根区间，也可用于根的精确化，但要求得较精确的结果计算机时间较长。

我们可以改用迭代法，更好地满足求根的要求。

迭代法是一种重要的逐次逼近法，它的基本思想是：将方程f(x)=0化为等价方程x=φ(x)，然后在隔根区间内取一点x0，按公式x n+1=φ(x n)，(n=0，1，2，3，……)
同理，我们针对上面的例子，来求方程在[0，1]区间内的解的近似值，构造等价方程为：x=(x3+1)/3，取初始值为区间中间值，即x0=0.5，在Excel中按图3所示进行输入。

图3
在C2单元格中输入“=(B2^3+1)/3”，然后拖动C2单元格的“填充柄”，将C2填充至J2，可得如图4所示的效果。

图4
可见，当k>6时，x值已趋于稳定，因此，在保留三位有效数字的基础上，我们可得x=0.3473为方程在[0，1]区间内的近似解。

3 问题总结
通过上述两种方法，可以看出，采用EXCEL在求解非线性方程的近似解时是极为方便的，特别是采用迭代法，能够使用较少的计算步数得到精度较高的解，有着较快的收敛速度。

但实际工程中的数学问题并不全是非线性方程的求解问题，还有许多诸如线性方程组、插值、曲线拟合等问题，这些问题中有很大一部分是可以通过EXCEL来完成的。

只是在实现之前，需要对各类数学问题进行较详细的分析，然后对EXCEL 加以灵活的应用，这样，就可以在不借助专门软件或编写特定程序的基础上求解工程中的一些数学问题，以更好地方便课程的教学及工程中的实际计算需要。

参考文献
[1] 曹璎珞，曹德欣. 计算方法[M].徐州：中国矿业大学出版社，1994
[2] 杨大地，谈骏渝. 实用数值分析[M].重庆：重庆大学出版社，2000
[3] 吴权威，吕琳琳. EXCEL统计应用实务[M].北京：中国水利水电出版社，2004。