Excel 财务应用 非线性回归预测
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
Excel 财务应用非线性回归预测
如果回归模型的因变量是自变量的一次以上函数形式,回归规律在图形上表现为形态各异的各种曲线,称为非线性回归。
在实际预测时,常常遇到非线性情况,即一组数据的分布与直线偏差甚大。一般来说,非线性关系分为两类,一类是可线性化的,另一类是不可线性化的。可线性化的是指变量之间呈非线性关系。
1.非线性回归分析
回归方程的建立对有些模型,如:
等,y对自变量x都不是线性的,但y对参数和而言是线性的,在这种情况下,我们只需把、、等视作变量,用简单的代换就可将上述模型化为线性模型。其中
可分别为、、等。
对于另外一些模型,如:
等,虽然y对x和参数都不是线性的,但也可通过适当变换化为线性模型。
对于上述这些可化为线性模型的回归问题,一般先将其化为线性模型,然后再用最小二乘法求出参数的估计值,最后再经过适当的变换,得到所求回归曲线。
常用的可变换为线性的曲线主要有六种。
●幂函数
幂函数的一般形式为y=x^a,其函数图像如图8-55所示。
图8-55 幂函数图8-56 指数函数
●指数函数
指数函数的一般形式为y=a^x(a>0且a≠1),其函数图像如图8-56所示。
●双曲线函数
数学上指一动点移动于一个平面上,与平面上两个定点的距离的差的绝对值始终为一定值时所成的轨迹叫做双曲线。
双曲线函数的一般形式为,其函数图像如图8-57所示。
图8-57 双曲线函数 图8-58 对数函数
● 对数函数
对数函数实际上就是指数函数的反函数,可表示为y=a+blnx ,其函数图像如图8-58所示。 ● 指数函数
指数函数的一般形式为y=a^x(a>0且a ≠1),其函数图像如图8-59所示。
● S 型曲线
S 型曲线是一个以横坐标表示时间,纵坐标表示工作量完成情况的曲线图,如图8-60所示。
图8-59 指数函数 图8-60 S 型曲线
2.非线性相关计算与分析
本节主要介绍如何使用Excel 来解决不能线性化的非线性回归预测。
例如,炼钢厂出钢时盛钢水的钢包在使用过程中受钢水和炉渣侵蚀,其容积不断增大。如图8-61所示是钢包使用不同次数时钢包容积(由于容积不便测量,故以钢包盛满钢水重量表示)的一组实测数据。试预测使用次数在23、25、30和40时的容积。
在工作表中,输入“钢包使用次数与容积实测数据”的相关信息,如图8-61所示。
图8-61 输入相关数据 图8-62 线性预测值
选择D4至D7单元格区域,并输入“=TREND(B3:B11,A3:A11,C4:C7)”公式,按下Ctrl+Shift+Enter 键,即可得到如图8-62所示的结果。 上面使用的TREND 函数返回一条线性回归拟合线的值。即找到适合已知数组known_y's 和known_x's 的直线(用最小二乘法),并返回指定数组new_x's
在直线上对应的
y
值。
输入
数据 输入公式
预测结果
语法:TREND(known_y's,known_x's,new_x's,const)
其中,Known_y's是关系表达式y =mx+b中已知的y值集合。Known_x's是关系表达式y=mx+b中已知的可选x值集合。New_x's为需要函数TREND返回对应y值的新x值。
从得到的预测结果中可以看出,如果使用次数达到一定值时,容积将降低为零甚到为负数,显然这是不符合逻辑的,说明使用线性模型来预测结果是不合理的。
下面来使用非线性预测的方法来进行回归分析。
(1)作非线性回归分析
选择C11和D11单元格,输入“=LOGEST(B3:B11,A3:A11)”公式,同时按下Ctrl+Shift+Enter 键,即可得到非线性回归结果,如图8-63所示。
输入
图8-63 非线性回归结果
LOGEST函数在回归分析中,计算最符合数据的指数回归拟合曲线,并返回描述该曲线的数值数组。因为此函数返回数值数组,所以必须以数组公式的形式输入。
此曲线的公式为:y = b*m^x 或y = (b*(m1^x1)*(m2^x2)*_)(如果有多个x值)其中因变量y是自变量x的函数。m值是各指数x的底,而b值是常量值。
语法:LOGEST(known_y's,known_x's,const,stats)
其中,Known_y's是满足指数回归拟合曲线y=b*m^x的一组已知的y值。Known_x's是满足指数回归拟合曲线y=b*m^x的一组已知的x值,为可选参数。Stats为一逻辑值,指定是否返回附加回归统计值。
(2)计算预测值和预测统计量
如果需要知道预测值和各预测统计量,可以选择C11至D15单元格区域,并输入“=LOGEST(B3:B11,A3:A11,1,1)”公式。然后按下Ctrl+Shift+Enter键,即可得到如图8-64所示的结果。
预测统
计量
图8-64 预测值和预测统计量
(3)计算斜率和截距
为了得出函数的表达式,从而完成预测,首先需要计算斜率和截距。
前面章节中介绍了斜率和截距的计算方法,这里不再详细介绍。分别选择A17和A18单元格,输入“=INDEX(LOGEST(B3:B11,A3:A11),1)”和“=INDEX(LOGEST(B3:B11,A3:A11),2)”公式,即可计算出斜率和截距,如图8-65所示。
计算斜率
和截距
图8-65 直接计算斜率和截距
(4)完成预测
得到上面的斜率和截距后,可以直接输入“=A$18*A$17^C4”公式。然后复制该公式至该列的其他单元格中,效果如图8-66所示。
预测
结果
图8-66 预测结果