如何在EXCEL中解一元多次方程

Horse · 发表于 1970-1-1 08:00:00

记得小Y在金融资产讲义里提到了在EXCEL中解出那个实际利率，而不是使用内插法，今日小Y无事，列举几种在EXCEL中解一元多次方程的方法，供大家参考，不足之处，还请大家指正。

当时的例子为：

59×（1＋r）-1＋59×（1＋r）-2＋59×（1＋r）-3＋59×（1＋r）-4＋（59＋1250）×（1＋r）-5＝1000

这实际是一个一元五次方程，其实可以将这个方程弄得更复杂些，比如说加个三角函数COS之类的，再对r进行开多次方等等，其解决方法是相同的。

方法一：试算法

根据财务知识，我们知道r的值为0-1之间，为此，我们在B2中输入公式：=59*(1+a2)^-1+59*(1+a2)^-2+59*(1+a2)^-3+59*(1+a2)^-4+(59+1250)*(1+a2)^-5-1000，然后选中B2的填充柄，按住CTRL键，往下复制，我们在A2中根据精度需要，如果是保留小数点后三点，则输入0.001，然后在A3里输入0.002，选中A2和A3，选中填充柄，按住CTRL键往下复制，在B列结果为正负交界处选择一个B列绝对值最小的数，其对应行的A列值即为r的值，假如是K。

如果对精度要求特别的高，则可以以K为基数，不停的在此基础上增加小数位数，至到对应的B例的值最接近零为止。

方法二：单变量求解

在A1单元格里输入公式：=59*(1+a2)^-1+59*(1+a2)^-2+59*(1+a2)^-3+59*(1+a2)^-4+(59+1250)*(1+a2)^-5-1000

点击“工具”——“单变量求解”，将目标单元格选定为A1，将目标值输入0，将可变单元格选择为A2，点击确定，A2单元格的内容即为上述等式里R的值。如果不将等式里的1000移入到公式里，则可直接在目标值里输入1000，然后确定，A2的结果与公式里包含“-1000”的结果是一样的。

方法三：VBA逼近

Subtest()

Dim i AsDouble

Dim j AsDouble

Dim k AsDouble

Dim k1 AsDouble

Dim m AsDouble

For i = 0To 1 Step 0.01 ‘精度为0.01

j = 59 *(1 + i) ^ -1 + 59 * (1 + i) ^ -2 + 59 * (1 + i) ^ -3 + 59 * (1 + i)^ -4 + (59 + 1250) * (1 + i) ^ (-5) - 1000

If j< 0 Then

k =j

m =i

ExitFor

EndIf

k1 = 59 *(1 + m) ^ -1 + 59 * (1 + m) ^ -2 + 59 * (1 + m) ^ -3 + 59 * (1 + m)^ -4 + (59 + 1250) * (1 + m) ^ (-5) - 1000

IfAbs(k1) > Abs(j) Then

MsgBoxi

Else

MsgBoxm

EndIf

EndSub

这段代码看起来比较复杂，变量也多，其实是为了更好的控制精度。运行速度倒是比较快的，就是步长为0.0000001的时候，运行时间也就一秒左右。

方法四、牛顿迭代法

该方法比较专业，但在编程中却是十分常用。

牛顿迭代法又称牛顿切线法，其公式如下：
F’(x0)=f(x0)/(x-x0)
x1=x0-f(x0)/f’(x0)
其中x0为根的接近值，f(x)为原方程，f'(x)为原方程的导数方程，x1为所求的根
注：导数方程，就是变量的系数与变量的方数之间的积，同时，各变量的方次减一。这个表述不太准确，差不多就是这样的吧。
使用该方法时，我们对原等式进行变形，并将(1+r)=X
通过变形得到如下等式：
1000*x^5-59*x^4-59*x^3-59*x^2-59*x-1309=0
同时：
F=(1000*x^4-59*x^3-59*x^2-59*x-59)*x-1309
F1=(((5000*x-236)*x-177)*x-118)*x-59
如果我们只求X在0附近的一个根，根据牛顿迭代法，我们可以写出如下代码：
Sub test()
Dim x0 As Double
Dim x1 As Double
Dim f As Double
Dim f1 As Double
x1 = 0.008
Do Until Abs((x1 - x0)) < 0.00001 '控制精度
x0 = x1
f = (1000 * x0 ^ 4 - 59 * x0 ^ 3 - 59 * x0 ^ 2 - 59 * x0 - 59) *x0 – 1309
f1=5000*x0^4-236*x0^3-177*x0^2-118*x0-59
x1 = x0 - f / f1
Loop
MsgBox x1
End Sub
由此得出X1=1.09995
R=x1-1=0.0995

上述三种方法都有不足之处，第一，需要知道值的区间范围，第二种方法也只能给出多个正确答案中的一个，不过在经济实践中，基本值就只有一个正确答案。

至于利用EXCEL解方程组和给出符合条件的全部值，等小Y学习下算法再补上，以前小Y从来没有写过与数学沾边的程序。

登录/注册后可看大图

该贴已经同步到 Horse的微博

zjwmcl · 发表于 1970-1-1 08:00:00

鼓励原创！创新万岁！

fengyaleng · 发表于 1970-1-1 08:00:00

真的能用execl表做出来？楼主太厉害了。记下来学习

[应用交流] 如何在EXCEL中解一元多次方程

评分