Excel作图和二分法结合解超越方程的一种方法
廖帮全
【摘 要】利用Excel作图和二分法结合解超越方程，既能总体上把握解的概况，又能快速地得到各个解的高精度近似结果，是一种简单、实用的超越方程解法。%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.
【期刊名称】《大学物理实验》
【年(卷),期】2015(000)002
【总页数】4页(P109-112)
【关键词】Excel;超越方程;作图法;二分法
【作 者】廖帮全
【作者单位】天津工业大学，天津 300387
【正文语种】中 文
【中图分类】O4-39
超越方程的解法主要有作图法、迭代法两大类。使用Mathematica、Matlab等软件可以很方便地解超越方程［1］；但这些解法需要一定的软件基础，入门门槛较高；而且软件本身也比较昂贵，软件普及程度较低。Excel软件发布以后，学者们提出了一些利用Excel解超越方程的方法［2-5］。这些利用Excel软件解超越方程的方法，一般以“单变量求解”法或迭代方法为基础，能给出一些解，很有益。但是，单变量求解工具只能给出一个解，而且初始值不同时解的精确程度的差异较大；迭代计算法对初值依赖性也较大，有时可能能给出一个解，有时可能发散。二分法实质是在确定有解的两个数值区间插入中值后迭代计算、快速逼近结果。以上使用Excel软件的3类解法的共同缺点是不能知道解的个数，不能把握整体情况。因此，当超越方程有多个解时，这3类方法都有可能会漏掉一些解。我们提出一种利用Excel作图和二分法结合解超越方程的方法，其中，作图过程与手工绘图过程类似，能从整体上把握解的个数；二分法可以快速逼近，迅速得到每个具体的解。解法思路简单
、过程直观，有一定实用价值。
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中值公式函数
由上述可知，用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中可以看出，除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可知，曲线交点的横坐标介于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′）；