设置主页 | 收藏本站 Excel学习网-公益型的EXCEL在线学习网站,助您轻松办公!

使用规划求解求出最优生长曲线

2014年04月17日 13:21 发布来源:Excel网

    Excel支持线性近似、多项式近似等六种近似曲线,但是不支持生长曲线。

    生长曲线的特征与Product Life cycle(PLc)十分相似,首先缓慢增长,然后急速增长,随后又缓慢增长,最终达到饱和状态,是一种s型曲线。经常用于表示商品销量、广告效果等。

    使用规划求解求出最优生长曲线,进行预测。

    例题:用最优生长曲线预测

    表1是某商店新产品累计销量的数据。根据这些数据,预测从第16周到第24周的累计销量。

使用规划求解求出最优生长曲线

表1

    成长曲线包括逻辑曲线、龚培子曲线等许多类型。本节使用经常用于预测销量等的逻辑曲线。

    逻辑曲线的方程如下:

    y=a/(1+6P")

    假设商品的累计销量是y,周是z(1,2,3,…,l 5),abc是决定逻辑曲线形状的参数(未知参数)。a表示最终累计销量,b大约是a的1/10,c是0<c<1。

    求解参数d.b.c,就是求当实际值和运算结果(用逻辑曲线方程求出的数值)的误差平方的总和最小时的a,b,c的数值。

    首先,制作如图1所示的工作表。规划求解可以求出参数a,b,c的数值,但是需要首先设定适当的数值。因为累计销量是190,因此假设a是250,b是25,c是0.5。

Excel数据分析

图1

    接着,输入运算公式。在单元格C8中输入逻辑斯蒂曲线方程。=$B$2/(1+$B$3*EXP(-$B$4*A8))”。EXP是Excel的指数函数。因为参数a,b,c的单元格固定,所以作为绝对引用(添加$)。然后把C8的公式一直复制到C31。

    为了求实际销量(实际值)和用公式求出的数值(运算结果)之间的差的平方,即(实际值-运算结果)2,在单元格D8中输入“=(B8一c8)^2”,然后把D8的公式一直复制到D22。

    最后,为了求误差平方的总和,在单元格D23中输入公式“=SUMM(D8:D22)”。上述数学公式的设定一览表,如图2所示。

Excel规划求解

图2

    接下来,运行规划求解。单击“工具”-“规划求解”,弹出“规划求解参数”对话框。在“设置目的单元格”中指定显示误差平方总和的单元格D23。在“等于”中选择“最小值”。在“可变单元格”中指定表示参数a,b,c的单元格B2——B4。单击“选项”按钮,在“规划求解选项”对话框中选中“假定非负”。

    最后,在“规划求解参数”对话框中单击。求解”按钮,显示如图3所示的结果。

Excel分析

图3

    参数a,b,c的值是:a=320.89,b=58.45,c=0.29。因此,最优逻辑曲线方程是:

    累计销量=32l/(1十58/5e-0.29*周)

    用这个方程计算出第16周到第24周的累计销量的预测值(运算结果)。显示在单元格c23——c31中。将运算结果图表化后如图3所示,从中可以看出。实际值和用规划求解求出的最优逻辑曲线非常稳合。

Excel图表

图4

    作为参考,把上述数据用Excel支持的近似曲线中的线性近似和对数近似表示出来,分别如图4和5所示。关于指数近似、幂近似、多项式近似,请读者自己尝试操作。 

Excel近似曲线

图5

Excel图表分析

图6

    使用Excel规划求解求出最优生长曲线,还是比较简单的,根据图表我们也可以清楚的看到数据分析预测的情况。

原创文章如转载请注明:转载自Excel学习网 [ http://www.excelcn.com/]
需要保留本文链接地址:http://www.excelcn.com/shujufenxi/478.html
分享到:
网站地图 | XML地图 | 免责声明 | 关于我们 | Excel学习网:优秀的EXCEL在线学习和资源分享网站。
版权所有: CopyRight © 2013-2018 www.excelcn.com All Rights Reserved.
豫ICP备12002644号