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

好酷屋教程网小编为您收集和整理了Excel中的有关预测函数及其应用(2)的相关教程：[导读]4.3.1LINEST函数LINEST函数的功能是使用最小二乘法计算对已知数据进行最佳线性拟合的直线方程，并返回描述此线性模型的数组。因为此函数返回数值为数组，故必须以数组公式的形式输入。函数

[导读]4.3.1LINEST函数LINEST函数的功能是使用最小二乘法计算对已知数据进行最佳线性拟合的直线方程，并返回描述此线性模型的数组。因为此函数返回数值为数组，故必须以数组公式的形式输入。函数公式为=LINEST（known_ys，known_xs，const，stats）下面举例说明LINEST函数的应用。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。

函数

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

函数

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个月份。

以上就是好酷屋教程网小编为您收集和整理的Excel中的有关预测函数及其应用(2)相关内容，如果对您有帮助，请帮忙分享这篇文章^_^

本文来源： https://www.haoku5.com/IT/6409d5828ad19d14e30e5069.html

相关推荐