Excel作图和二分法结合解超越方程的一种方法

廖帮全

（天津工业大学，天津 300387）

Excel作图和二分法结合解超越方程的一种方法

廖帮全

（天津工业大学，天津 300387）

利用Excel作图和二分法结合解超越方程，既能总体上把握解的概况，又能快速地得到各个解的高精度近似结果，是一种简单、实用的超越方程解法。

Excel；超越方程；作图法；二分法

超越方程的解法主要有作图法、迭代法两大类。使用Mathematica、Matlab等软件可以很方便地解超越方程［1］；但这些解法需要一定的软件基础，入门门槛较高；而且软件本身也比较昂贵，软件普及程度较低。Excel软件发布以后，学者们提出了一些利用Excel解超越方程的方法［2-5］。这些利用Excel软件解超越方程的方法，一般以“单变量求解”法或迭代方法为基础，能给出一些解，很有益。但是，单变量求解工具只能给出一个解，而且初始值不同时解的精确程度的差异较大；迭代计算法对初值依赖性也较大，有时可能能给出一个解，有时可能发散。二分法实质是在确定有解的两个数值区间插入中值后迭代计算、快速逼近结果。以上使用Excel软件的3类解法的共同缺点是不能知道解的个数，不能把握整体情况。因此，当超越方程有多个解时，这3类方法都有可能会漏掉一些解。我们提出一种利用Excel作图和二分法结合解超越方程的方法，其中，作图过程与手工绘图过程类似，能从整体上把握解的个数；二分法可以快速逼近，迅速得到每个具体的解。解法思路简单、过程直观，有一定实用价值。

1　单变量求解工具及迭代法给出的解的情况

1.1单变量求解工具给出的解的情况

物理学中在从黑体辐射的普朗克公式推导维恩位移定律时会遇到一个超越方程［6，7］，如下所示，

现在我们使用几种方法对方程（1）进行求解。使用参考文献［2-7］所提供的Excel软件中的“单变量求解”法求解时，在其中“可变单元格”对应的单元格中不输入数值（空单元格）或输入0时解为0；输入1时，解为-7.027 69×10-5。这给出了超越方程（1）的平庸解x=0的近似解，没有给出真正需要的解x=4.965 1的近似解。

我们知道方程（1）有两个解，于是，尝试改变“可变单元格”对应的单元格中输入的数值，以确认“单变量求解”工具能否得到另一个解。我们发现，在“可变单元格”对应的单元格中输入其它数值，比如-3、-2、-1、-0.5、0.5、1.5等值时得到的仍是零解的近似解；输入数值2、3等值时，可得到超越方程（1）的另一个解的近似解。这表明，在输入某些初值时可以得到方程的另一个真正需要的解。但问题是由于使用这种方法求解时并不知道有几个解，尝试输入不同数值时不可能无限次尝试，因而存在得到一些解后就不再尝试的可能性；而且，即便想更多地尝试也不知道应该怎么尝试，只能盲目尝试；由此可见，对有多个解的方程存在漏掉某些解的可能性。

1.2迭代计算法给出的解的情况

使用参考文献［5］所提供的迭代计算法对方程（1）求解时，可将方程（1）变形如下，

当初值为负值时，包括1.1节方法中的近似解-7.02769×10-5，计算结果发散；当初值为0时解为0；当初值为正数时才能得到另一真正需要的解。

另外，这个零解不是迭代计算法计算得到的，即，如果不输入初值“0”，无论是输入正值还是负值都不能得到零解。这表明，这个零解实际是碰巧输入初值0后才得到的，是一个特殊的解。如果某个方程也存在这类特殊数值的解，则依靠迭代计算法自身很难得到这类解。

从这里可以看出，迭代计算法确实对初值有较大依赖性［5］。同时，迭代计算法也有不知道存在几个解的问题，在尝试变更初值时也具有盲目性。

由上述可知，用Excel软件解超越方程时，单变量求解法和迭代计算法自身都不能知道解的个数，存在漏掉一些解的可能性；另外，尝试变更初值时也都具有盲目性。

作图法能直观看出解的个数，二分法是一种快速逼近结果的方法，如果将作图法和二分法结合起来求解超越方程，可以弥补单变量求解法和迭代计算法的不足，有一定的优势。

2　用Excel作图和二分法结合解超越方程的方法及示例

解超越方程的一种常用方法是作图法。其原理是，画出两条曲线，曲线的交点即为方程的解。这种方法的优点是直观，能看出曲线的变化趋势，可以整体上把握有几个解；对可能有解的区域，可以采用减小数据间隔、增加数据个数的方法进一步确认。作图法的缺点是手工绘图时曲线的交点实际上有一定大小，难以对应到小刻度范围，因而精度有限、得到的解比较粗略。

Excel有作图功能。用Excel作图时，可以插入很多数据，得到很小刻度范围内的曲线变化情况，可以对图形进行放大，从而弥补手工作图的不足。

用Excel软件作图时，利用两列数据绘制曲线。其中，两列数据可以都是手动输入的，也可以一列数据是手动输入的，另一列数据是在前一列数据基础上通过一定的函数关系自动计算出来的。在解超越方程前，我们将超越方程简单变形，变成f（x）=0的齐次方程形式。齐次方程的左边是一个函数y=f（x），可以据此绘制曲线；齐次方程的右边等价于y=0的一条曲线，即X轴；这两条曲线的交点，也就是齐次方程左边函数对应的曲线与X轴的交点，即是方程的解。

下面以超越方程（1）为例，详细说明解超越方程的过程。这里，Excel软件以Excel 2003为例。

令y=5e-x+x-5，在Excel中绘制曲线。具体方法如下:在Excel中任选一单元格，比如B3，在B3开始的列里输入-2、-1、0、1、2、……；在前一列数据顶端右侧选一单元格，比如C3，在此单元格中输入函数“=5∗EXP（-B3）+（B3）-5”，会得到一个数据，此例中为29.9；然后选中此单元格，按住填充柄往下拉，可得到一列数据，如表1所示。

表1　方程（1）左端函数对应的两列数据

利用表1的两列数据，绘制平滑线散点图，可得到图1。

图1　根据表1的数据绘制的平滑线散点图

从图1中可以看出，除x=0的点外，曲线与X轴还有一个交点，其横坐标在5附近，放大后可以进一步看出此点介于4、5之间。这两个交点是方程的两个解。进一步观察可知，在x＜-2 或x＞10的情况下，曲线都呈远离X轴的变化趋势，故不会再有其它解。为了求方程的非零解，可以在4和5之间插入数据，逐步逼近。在B列中4和5之间插入数据时不必等间隔地插入4. 1、4.2、……、4.9等9个数据，可以插入4和5的中间值，即，在此待插单元格（比如，B10）中输入“=（B9+B11）/2”，即插入了4.5。选中C9单元格，按住填充柄下拉到C10，C10值就自动算出了。同时，图1上的曲线图中也自动增加了此插入值对应的点、曲线也自动再次平滑了，如图2所示。

图2　插入数值后曲线变化情况

观察图2可知，曲线交点的横坐标介于4.5 和5之间。观察数据可知，x列4.5对应的f（x）＜0，而5对应的f（x）＞0；要达到f（x）=0，x值对应的f（x）值应介于这两个符号不同的f（x）值之间。因此，要得到更精确的解，需在此两行中间插入x、f（x）数据。根据这个规律，可在数据表格中f（x）值符号相反的相邻两行中插入其对应x值的中值，逐步逼近，得到方程的近似解。

插入中值可以有多种方法。一种方法是利用Excel软件中的函数功能实现自动插入中值。这种方法的指导思想是:在某两个异号的f （x1）、f（x2）对应的x1、x2之间插入（x1，x2）的中点x′，让Excel算出f（x′）；利用Excel中的逻辑函数判断f（x′）与f（x1）、f（x2）的同号异号关系，并在x′及与f（x′）异号的f（x1）或f（x2）对应的x1或x2之间插入新的中间值，如此循环，快速逼近。

下面是结合表1的数值利用Excel软件中的函数功能实现自动插入中间值及逐步逼近的方法:

（1）在9行和10行之间插入若干行；

（2）单元格B9中的值4是区间的一个端点值，在单元格E9中输入区间的另一个端点值5（也可以用引用单元格的方式引用），这里，B9、E9中的值对应x1、x2；在G9中插入公式“=（B9+ E9）/2”（即中点x′）；

（3）单元格C9中公式“=5∗EXP（-B9）+B9-5”保留，在单元格F9、H9中分别输入公式“=5∗EXP （-E9）+E9-5”、“=5∗EXP（-G9）+G9-5”后，在C9、F9、H9中显示相应的f（x1）、f（x2）、f（x′）；

（4）在单元格B10中输入“=IF（C9∗H9＜0，B9，G9）”，在单元格E10中输入“=IF（F9∗H9＜0，E9，G9）”，以根据f（x1）、f（x2）、f（x′）的同号异号关系确定下一个区间的端点B10、E10取B9、G9还是取G9、E9；

（5）选中C9，按住填充柄往下拉，得C10；选中F9、G9、H9，按住填充柄往下拉，得F10、G10、H10；再选中B10～H10，按住填充柄往下拉若干行，Excel会自动计算，直到f（x）在小数点后28位近似为0；

（6）曲线图上也对应增加了这些数值对应的点。

图3给出了多次插入数据后对应f（x）值与0差异在-5×10-14到5×10-14之间的x值局部数据图及多次插入数值后的曲线。从这数据及曲线可以看出，在取小数点后14位时，方程的非零解介于4. 96511423174422与4.96511423174434之间，约为4.96511423174428。

图3　多次插入中值后的局部数据图

当方程有多个解时，可以在曲线与X轴的每一个交点附近分别插入数值进行逐步逼近，从而求得每一个解。

另外，在插入中值时，也可以每一步都手动插入，得到的结果一致，但操作过程需特别细心，此处不赘述。

3　有关的一点讨论

3.1二分法的一点改进

二分法实质是在x1、x2之间插入数值时x1、x2的系数都按0.5计算。这样插入数值，收敛速度有点慢。为加快收敛速度，可以改变x1、x2的系数值。通过观察发现|f（x1）|、|f（x2）|一般不相等，其中有一个会小一点。我们可以对其中小一点的f（x）赋予更大的权重；比如，插入的数值变更为｛|f（x2）|/［|f（x1）|+|f（x2）|］｝x1+｛1-|f（x2）|/［|f （x1）|+|f（x2）|］｝x2；更特别的情况是，在|f（x1）|＜1且|f（x2）|＜1时，可以在将二分法中插入的数值简单变更为|f（x2）|x1+（1-|f（x2）|）x2。

结合本例，可以在第2小节中第3步和第4步之间插入1步:在单元格I9中输入“=ABS（F9）/ （ABS（C9）+ABS（F9））”，在单元格G9中输入“= B9∗I9+E9∗（1-I9）”（其中，ABS（C9）表示数值为C9的绝对值）。同样方法处理，可以得到解介于4. 96511423174406和5之间，约为4.96511423174428。按二分法插入数值，经过44次插值才得到结果；按这种改进的方法插入数值，经过5次插值即可得到结果，大大加快了收敛速度。

3.2插入数值的区间不必特别精准

从图1上可以粗略看出曲线与X轴交点位于（4，6）之间，我们也可以在（4，6）之间插入数值。按二分法插入数值时，得到的解与在（4，5）之间按二分法插入数值得到的解相同；插值次数为45次。按二分法的改进方法插入数值时，经过8次插值，得到解介于4.96511423174416和6之间，约为4.96511423174428。在（4，6）间插入数值与在（4，5）间插入数值得到的解的结果、插值次数基本相当，这表明插入数值的区间不必特别精准就能得到解。

3.3计算结果的精度

Excel 2003函数计算结果能精确到小数点后30位。用这种方法得到的自变量的解可以精确到小数点后14位。如果要得到更高精度的解，则需要换用其它工具，比如，编写程序求解。一般来说，精确到小数点后14位可以满足常规科研、工程的需要。

3.4其它超越方程的解法

其它超越方程，解法类似。当有些函数在Excel中的对应形式不知道时，可以在Excel的函数功能中查找函数的表达式并引用。方法如下:点击“f（x）（插入函数）”，弹出对话框→或选择类别（C）”中选择“数学与三角函数”→从“选择函数（N）”中选择需要的函数形式。如果这种方法不能直接找到想要的函数，可以将方程变换到通过这里的函数来表达的式子。

3.5对于方程解的情况的判断

观察曲线是否与X轴相交，很容易判断方程是否有解及解的个数。但，描绘曲线时，数据总有一个范围，不可能取所有值；如果方程取值范围过窄，或者取值间隔过大，可能导致本来有解但曲线上显示不相交的情况。这时，可加大取值范围观察曲线在X轴从正负方向上变大时的变化趋势，在曲线与X轴可能相交的区域缩小取值间隔并对图形进行放大。如果能结合方程的实际意义规定的取值范围来描绘曲线，那么判断曲线是否相交、相交几次将会更方便，判断方程是否有解、有几个解也更方便。

4　结 论

利用Excel作图功能画出平滑线散点图，在曲线与X轴各个交点附近插入数值，不断逼近，可以得到超越方程的近似解。此方法以绘制曲线为基础，比较直观，多次插入数值后解的精度很高，能满足常规科研、工程的需要，不失为超越方程的一种简单、实用的解法。

［1］ 佘明亮，李必文，佘雅.用MATLAB解决齿轮计算中两类超越三角函数方程的求解问题［J］.南华大学学报:自然科学版，2006，20（2）:44-46.

［2］ 罗传义，时景荣，戴传波.解超越方程的电子表格法［J］.吉林化工学院学报，2003，20（3）:60-62.

［3］ 何文全，饶拱维.Excel在求解方程式中的应用［J］.现代计算机:专业版，2008，（10）:193-195.

［4］ 余秉勤.Excel在变量求解中的应用［J］.武钢职工大学学报，2000:12（4）:22-28.

［5］ 徐稼红.Excel在中学数学建模教学中的运用［J］.中国电化教育，2002（11）:29-31.

［6］ 周世勋.量子力学教程［M］.北京:高等教育出版社，1979:5-15.

［7］ 刘晓军，刘立伟，高广君.维恩位移定律的证明［J］.齐齐哈尔大学学报，2002，18（2）:92-93.

A Method to Solve Transcendental Equation by Combining Excel Graphing and Dichotomy

LIAO Bang-quan
（Tianjin Polytechnic University，Tianjin 300387）.

Combining Excel graphing and dichotomy，we can solve transcendental equation.Through this method we can know the solutions’number and obtain the solutions quickly.It is one kind of simple，practical method for solving transcendental equation.

Excel；transcendental equation；graphing method；dichotomy

O4-39

A

10.14139/j.cnki.cn22-1228.2015.02.031

1007-2934（2015）02-0109-04

2014-11-12