Excel中的有关预测函数及其应用(2)

时间:2016.09.12频道:Excel

4.3.1 LINEST函数 LINEST函数的功能是使用最小二乘法计算对已知数据进行最佳线性拟合的直线方程,并返回描述此线性模型的数组。因为此函数返回数值为数组,故必须以数组公式的形式输入。 函数公式为 = LINEST(known_y's,known_x's,const,stats) 下面举

4.3.1 LINEST函数

LINEST函数的功能是使用最小二乘法计算对已知数据进行最佳线性拟合的直线方程,并返回描述此线性模型的数组。因为此函数返回数值为数组,故必须以数组公式的形式输入。

函数公式为

= LINEST(known_y's,known_x's,const,stats)

下面举例说明LINEST函数的应用。

1.一元线性回归分析

LINEST函数可用于一元线性回归分析,也可以用于多元线性回归分析,以及时间数列的自回归分析。

当只有一个自变量 x (即一元线性回归分析)时,可直接利用下面的公式得到斜率和 y 轴的截距值以及相关系数:

斜率:INDEX(LINEST(known_y's,known_x's),1,1);或INDEX(LINEST(known_y's,known_x's),1)

截距:INDEX(LINEST(known_y's,known_x's),1,2);或INDEX(LINEST(known_y's,known_x's),2)

相关系数:INDEX(LINEST(known_y's,known_x's,true,true),3,1)

【例4-1】某企业1~9月份的总成本与人工小时及机器工时的数据如图4-1所示。假设总成本与人工小时之间存在着线性关系,则在单元格B13中插入公式“=INDEX(LINEST(B2:B10,D2:D10),2)”,在单元格B14插入公式“=INDEX(LINEST(B2:B10,D2:D10),1)”,在单元格B15插入公式“=INDEX(LINEST(B2:B10,D2:D10,TRUE,TRUE),3,1)”,即得总成本与人工小时的一元线性回归分析方程为:Y=562.72756+4.41444X1,相关系数为R2=0.99801,如图4-1所示。

图4-1 一元线性回归分析

2.多元线性回归分析

仍以例4-1的数据为例,首先选取单元格区域A17:D21,再以数组公式方式输入公式“=LINEST(B2:B10,C2:D10,TRUE,TRUE)”,即得该二元线性回归的有关参数如图4-2所示,从而得到:

图4-2 二元线性回归分析

回归方程:Y = 471.4366+3.6165X1+3.4323X2

相关系数:R2 =0.9990

标准差:Sey =11.7792。

4.3.2 LOGEST函数

LOGEST函数的功能是在回归分析中,计算最符合观测数据组的指数回归拟合曲线,并返回描述该指数模型的数组。由于这个函数返回一个数组,必须以数组公式输入。

LOGEST函数的公式为

= LOGEST(known_y's,known_x's,const,stats)

【例4-2】某企业12个月某产品的生产量(X)与生产成本(Y)的有关资料如图4-3所示,假设它们之间有如下关系:。选取单元格区域B15:C18,输入公式“=LOGEST(C2:C13,B2:B13,TRUE,TRUE)”(数组公式输入),即得回归参数,如图4-3所示,参数m=0.8887,参数b=1891.7729,生产成本与生产量的回归曲线为:Y=1791.7729×0.8887X,相关系数R2=0.95885。

图4-3 指数回归

回归方程的系数及相关系数也可以利用下面的公式直接计算

参数m:INDEX(LOGEST(C2:C13,B2:B13),1)=0.8887

参数b:INDEX(LOGEST(C2:C13,B2:B13),1,2)=1791.7729

相关系数R2:=INDEX(LOGEST(C2:C13,B2:B13,TRUE,TRUE),3,1)= 0.95885

4.3.3 TREND函数

TREND函数的功能是返回一条线性回归拟合线的一组纵坐标值(y 值),即找到适合给定的数组 known_y's 和 known_x's 的直线(用最小二乘法),并返回指定数组 new_x's 值在直线上对应的 y 值。

TREND函数的公式为

= TREND(known_y's,known_x's,new_x's,const)

式中 new_x's —— 需要函数 TREND 返回对应 y 值的新 x 值。 new_x's 与 known_x's 一样,每个独立变量必须为单独的一行(或一列)。因此,如果 known_y's 是单列的,known_x's 和 new_x's 应该有同样的列数,如果 known_y's 是单行的,known_x's 和 new_x's 应该有同样的行数。如果省略 new_x's,将假设它和 known_x's 一样。

【例4-3】某企业过去一年的销售量为下列数据:{300,356,374,410,453,487,501,534,572,621,650,670},将它们保存在单元格A1:A12中,则下一年的1、2、3月的销售量预测步骤为:选中单元格区域B1:B3,输入公式“=TREND(A1:A12,,{13;14;15})”(数组公式输入),即得来年的1、2、3月份的销售量分别为710、743和777。这个公式默认{1;2;3;4;5;6;7;8;9;10;11;12}作为known_x's的参数,故数组{13;14;15}就对应其后的3个月份。

相关阅读Readings
  • Excel2000编辑技巧

    (一)单元格编辑技巧如^00100005a^ (二)提高数据录入准确性 1.有效性检查 Excel 2000可以对单元格内的数据类型进行限制,并能核对输入单...

    04.14
  • 发掘Excel2000的“隐藏功能”

    使用隐藏功能 (一)在“文件”菜单中隐藏最后使用过的工作簿清单 1、从“工具”菜单中选择“选项”命令,然后单击“常规”选项卡。 2、清...

    04.15
  • Excel易失性函数小结

    souce: Excel易失性函数小结,文字结构有调整,改正若干错别字。 所谓“易失函数”,Volatile functions,就是指使用这些函数后,会引发...

    04.16
  • EXCEL 快捷鍵

    Ctrl+Home快捷键 强迫回到最前一个单元格 Ctrl+PgDn快捷键 到下一个工作表 Ctrl+PgUP快捷键 到上一个工作表。 按F4快捷键 切换相对值为绝...

    04.16
  • Excel打印问与答

    问:我想打印彩色的页眉,却不能为页眉应用彩色格式。如何解决? 答:页眉中的文字只能打印为黑色,即便使用彩色打印机,也不能对页眉和...

    04.20
Copyright © 2010-2017 bor-consulting.com 博瑞玩家 All Rights Reserved 粤ICP备12021207号