Excel 应用TREND函数计算沿线性趋势的值

TREND函数用于计算一条线性回归拟合线的值。即找到适合已知数组known_y’s和known_x’s的直线(用最小二乘法),并返回指定数组new_x’s在直线上对应的y值。TREND函数的语法如下。


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

其中:

参数known_y’s是关系表达式y=mx+b中已知的y值集合。

  • 如果数组known_y’s在单独一列中,则known_x’s的每一列被视为一个独立的变量。
  • 如果数组known_y’s在单独一行中,则known_x’s的每一行被视为一个独立的变量。

known_x’s是关系表达式y=mx+b中已知的可选x值集合。

  • 数组known_x’s可以包含一组或多组变量。如果仅使用一个变量,那么只要known_x’s和known_y’s具有相同的维数,则它们可以是任何形状的区域。如果用到多个变量,则known_y’s必须为向量(即必须为一行或一列)。
  • 如果省略known_x’s,则假设该数组为{1,2,3,…},其大小与known_y’s相同。

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一样。
  • 如果known_x’s和new_x’s都省略,将假设它们为数组{1,2,3,…},大小与known_y’s相同。

const为一逻辑值,用于指定是否将常量b强制设为0。

  • 如果const为TRUE或省略,b将按正常计算。
  • 如果const为FALSE,b将被设为0(零),m将被调整以使y=mx。

典型案例

基础数据如图16-161所示。

步骤1:打开例子工作簿“TREND.xlsx”。

步骤2:选中单元格区域C2:C13,按F2键,然后输入公式“=TREND(B2:B13,A2:A13)”,按“Ctrl+Shift+Enter”键以数组公式的形式输入,用于计算对应的资产原值。

步骤3:选中单元格区域B15:B19,按F2键,然后输入公式“=TREND(B2:B13,A2:A13,A15:A19)”,按“Ctrl+Shift+Enter”键以数组公式的形式输入,用于计算预测的资产原值。计算结果如图16-162所示。

图16-161 基础数据

图16-162 计算结果

使用指南

可以使用TREND函数计算同一变量的不同乘方的回归值来拟合多项式曲线。例如,假设A列包含y值,B列含有x值。可以在C列中输入“x^2”,在D列中输入“x^3”,等等,然后根据A列,对B列到D列进行回归计算。

对于返回结果为数组的公式,必须以数组公式的形式输入。当为参数(如known_x’s)输入数组常量时,应当使用逗号分隔同一行中的数据,用分号分隔不同行中的数据。

Excel 应用FORECAST函数计算沿线性趋势的值

FORECAST函数用于根据已有的数值计算或预测未来值。此预测值为基于给定的x值推导出的y值。已知的数值为已有的x值和y值,再利用线性回归对新值进行预测。可以使用该函数对未来销售额、库存需求或消费趋势进行预测。FORECAST函数的语法如下。


FORECAST(x,known_y's,known_x's)

其中参数x为需要进行预测的数据点。known_y’s为因变量数组或数据区域。known_x’s为自变量数组或数据区域。

典型案例

已知一组给定的X和Y值,基于给定的X值25预测一个Y值。基础数据如图16-121所示。

步骤1:打开例子工作簿“FORECAST.xlsx”。

步骤2:在单元格A8中输入公式“=FORECAST(25,A2:A6,B2:B6)”,基于给定的X值25预测一个Y值。计算结果如图16-122所示。

图16-121 基础数据

图16-122 计算结果

使用指南

如果x为非数值型,函数FORECAST返回错误值“#VALUE!”;如果known_y’s和known_x’s为空或含有不同个数的数据点,函数FORECAST返回错误值“#N/A”;如果known_x’s的方差为零,函数FORECAST返回错误值“#DIV/0!”。函数FORECAST的计算公式为a+bx,式中:

且其中x和y是样本平均值AVERAGE(known_x’s)和AVERAGE(known_y’s)。

Excel 应用LINEST函数计算线性趋势的参数

LINEST函数用于使用最小二乘法对已知数据进行最佳直线拟合,然后返回描述此直线的数组。也可以将LINEST与其他函数结合以便计算未知参数中其他类型的线性模型的统计值,包括多项式、对数、指数和幂级数。因为此函数返回数值数组,所以必须以数组公式的形式输入。LINEST函数的语法如下。


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

其中参数known_y’s是关系表达式y=mx+b中已知的y值集合。

· 如果数组known_y’s在单独一列中,则known_x’s的每一列被视为一个独立的变量。

· 如果数组known_y’s在单独一行中,则known_x’s的每一行被视为一个独立的变量。

known_x’s是关系表达式y=mx+b中已知的可选x值集合。

· 数组known_x’s可以包含一组或多组变量。如果仅使用一个变量,那么只要known_x’s和known_y’s具有相同的维数,则它们可以是任何形状的区域。如果用到多个变量,则known_y’s必须为向量(即必须为一行或一列)。

· 如果省略known_x’s,则假设该数组为{1,2,3,…},其大小与known_y’s相同。

const为一逻辑值,用于指定是否将常量b强制设为0。

· 如果const为TRUE或省略,b将按正常计算。

· 如果const为FALSE,b将被设为0,并同时调整m值使y=mx。

stats为一逻辑值,指定是否返回附加回归统计值。

· 如果stats为TRUE,则LINEST函数返回附加回归统计值,这时返回的数组为{mn,mn-1,…,m1,b;sen,sen-1,…,se1,seb;r2,sey;F,df;ssreg,ssresid}。

· 如果stats为FALSE或省略,LINEST函数只返回系数m和常量b。

【背景知识】直线的公式为:y=mx+b或y=m1x1+m2x2+…+b(如果有多个区域的x值)。

其中,因变量y是自变量x的函数值。m值是与每个x值相对应的系数,b为常量。注意y、x和m可以是向量。LINEST函数返回的数组为{mn,mn-1,…,m1,b}。LINEST函数还可返回附加回归统计值。

附加回归统计值如表16-1所示。

表16-1 附加回归统计值

【典型案例】已知某公司1~6月份的产品销售额,估算第8个月的销售值。基础数据如图16-119所示。

步骤1:打开例子工作簿“LINEST.xlsx”。

步骤2:在单元格A9中输入公式“=SUM(LINEST(B2:B7,A2:A7)*{8,1})”,用于估算第8个月的销售值。计算结果如图16-120所示。

【使用指南】

1)可以使用斜率和y轴截距描述任何直线:

· 斜率(m):通常记为m,如果需要计算斜率,则选取直线上的两点,(x1,y1)和(x2,y2);斜率等于(y2-y1)/(x2-x1)。

· y轴截距(b):通常记为b,直线的y轴的截距为直线通过y轴时与y轴交点的数值。

直线的公式为y=mx+b。如果知道了m和b的值,将y或x的值代入公式就可计算出直线上的任意一点。另外还可以使用TREND函数来得到结果。

2)当只有一个自变量x时,可直接利用下面公式得到斜率和y轴截距值。

· 斜率公式如下。


=INDEX(LINEST(known_y's,known_x's),1)

图16-119 基础数据

图16-120 计算结果

· y轴截距公式如下。


=INDEX(LINEST(known_y's,known_x's),2)

3)数据的离散程度决定了LINEST函数计算的精确度。数据越接近线性,LINEST模型就越精确。LINEST函数使用最小二乘法来判定最适合数据的模型。当只有一个自变量x时,m和b是根据下面的公式计算出的:

其中x和y是样本平均值,例如x=AVERAGE(knownx’s)和y=AVERAGE(known_y’s)。

4)直线和曲线函数LINEST和LOGEST可用来计算与给定数据拟合程度最高的直线或指数曲线。但需要判断两者中哪一个更适合数据。可以用函数TREND(known_y’s,known_x’s)来计算直线,或用函数GROWTH(known_y’s,known_x’s)来计算指数曲线。这些不带参数new_x’s的函数可在实际数据点上根据直线或曲线来返回y的数组值,然后可以将预测值与实际值进行比较。另外还可以用图表方式来直观地比较二者。

5)回归分析时,Excel计算每一点的y的估计值和实际值的平方差。这些平方差之和称为残差平方和(ssresid)。然后Excel计算总平方和(sstotal)。当const=TRUE或被删除时,总平方和是y的实际值和平均值的平方差之和。当const=FALSE时,总平方和是y的实际值的平方和(不需要从每个y值中减去平均值)。回归平方和(ssreg)可通过公式“ssreg=sstotal-ssresid”计算出来。残差平方和与总平方和的比值越小,判定系数r2的值就越大。r2是表示回归分析公式的结果反映变量间关系的程度的标志,其值等于ssreg和sstotal的比值。

6)在某些情况下,一个或多个x列可能没有出现在其他x列中的预测值(假设y’s和x’s位于列中)。换句话说,删除一个或多个x列可能导致同样精度的y预测值。在这种情况下,这些多余的x列应该从回归模型中删除。这种现象被称为“共线”,因为任何多余的x列可表示为多个非多余x列的和。LINEST将检查是否存在共线,并在识别出来之后从回归模型中删除任何多余的x列。由于包含0系数以及0se’s,所以已删除的x列能在LINEST输出中被识别出来。如果一个或多个多余的列被删除,则将影响df,原因是df取决于被实际用于预测目的的x列的个数。如果由于删除多余的x列而更改了df,则也会影响sey和F的值。

实际上,出现共线的情况应该相对很少。但是,如果某些x列仅包含0’s和1’s作为一个实验中的对象是否属于某个组的指示器,则很可能引起共线。如果const=TRUE或被删除,则LINEST可有效地插入所有包含1’s的其他x列以便模型化截取。如果在一列中,1对应于每个男性对象,0对应于非男性对象;而在另一列中,1对应于每个女性对象,0对应于非女性对象,那么后一列就是多余的,因为其中的项可通过从所有包含1’s(由LINEST添加)的另一列中减去“男性指示器”列中的项来获得。

7)df的计算方法如下所示(没有x列由于共线而从模型中被删除):如果存在known_x’s的k列和const=TRUE或被删除,那么df=n–k–1;如果const=FALSE,那么df=n-k。在这两种情况下,每次由于共线而删除一个x列都会使df加1。

8)对于返回结果为数组的公式,必须以数组公式的形式输入。

当输入一个数组常量(如known_x’s)作为参数时,以逗号作为同一行中各数值的分隔符,以分号作为不同行中各数值的分隔符。分隔符可能因“控制面板”的“区域和语言选项”中区域设置的不同而有所不同。

9)注意,如果y的回归分析预测值超出了用来计算公式的y值的范围,它们可能是无效的。

函数LINEST中使用的下层算法与函数SLOPE和INTERCEPT中使用的下层算法不同。当数据未定且共线时,这些算法之间的差异会导致不同的结果。例如,如果参数known_y’s的数据点为0,参数known_x’s的数据点为1:

·LINEST返回值0。LINEST算法用来返回共线数据的合理结果,在这种情况下至少可找到一个答案。

·SLOPE和INTERCEPT返回错误“#DIV/0!”。SLOPE和INTERCEPT算法用来查找一个且仅一个答案,在这种情况下可能有多个答案。

10)除了使用LOGEST计算其他回归分析类型的统计值外,还可以使用LINEST计算其他回归分析类型的范围,方法是将x和y变量的函数作为LINEST的x和y系列输入。例如,下面的公式。


=LINEST(yvalues,xvalues^COLUMN($A:$C))

将在使用y值的单个列和x值的单个列计算下面的方程式的近似立方(多项式次数为3)值时运行:


y=m1*x+m2*x^2+m3*x^3+b

可以调整此公式以计算其他类型的回归,但是在某些情况下,需要调整输出值和其他统计值。

Excel 应用INTERCEPT函数计算线性回归线的截距

INTERCEPT函数用于利用现有的x值与y值计算直线与y轴的截距。截距为穿过已知的known_x’s和known_y’s数据点的线性回归线与y轴的交点。当自变量为0(零)时,使用INTERCEPT函数可以决定因变量的值。例如,当所有的数据点都是在室温或更高的温度下取得的,可以用INTERCEPT函数预测在0℃时金属的电阻。INTERCEPT函数的语法如下。


INTERCEPT(known_y's,known_x's)

其中参数known_y’s为因变量的观察值或数据集合,known_x’s为自变量的观察值或数据集合。

典型案例

已知一组x、y值,计算直线与y轴的截距。基础数据如图16-117所示。

步骤1:打开例子工作簿“INTERCEPT.xlsx”。

步骤2:在单元格A8中输入公式“=INTERCEPT(A2:A6,B2:B6)”,用于计算已知直线与y轴的截距。计算结果如图16-118所示。

图16-117 基础数据

图16-118 计算结果

使用指南

参数可以是数字,或者是包含数字的名称、数组或引用。如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略,但包含零值的单元格将计算在内。如果known_y’s和known_x’s所包含的数据点个数不相等或不包含任何数据点,则函数INTERCEPT返回错误值“#N/A”。回归线a的截距公式为:

公式中斜率b计算如下:

其中x和y是样本平均值AVERAGE(known_x’s)和AVERAGE(known_y’s)。

函数SLOPE和INTERCEPT中使用的下层算法与函数LINEST中使用的下层算法不同。当数据未定且共线时,这些算法之间的差异会导致不同的结果。例如,如果参数known_y’s的数据点为0,参数known_x’s的数据点为1:

·SLOPE和INTERCEPT返回错误“#DIV/0!”。INTERCEPT和SLOPE算法用来查找一个且仅一个答案,在这种情况下可能有多个答案。

·LINEST返回值0。LINEST算法用来返回共线数据的合理结果,在这种情况下至少可找到一个答案。

Excel 应用STEYX函数计算通过线性回归法预测每个x的y值时所产生的标准误差

STEYX函数用于计算通过线性回归法计算每个x的y预测值时所产生的标准误差。标准误差用来度量根据单个x变量计算出的y预测值的误差量。STEYX函数的语法如下。


STEYX(known_y's,known_x's)

其中参数known_y’s为因变量数据点数组或区域,known_x’s为自变量数据点数组或区域。

典型案例

已知一组x、y值,用线性回归法计算每个x的y预测值时所产生的标准误差。基础数据如图16-115所示。

步骤1:打开例子工作簿“STEYX.xlsx”。

步骤2:在单元格A10中输入公式“=STEYX(A2:A8,B2:B8)”,用线性回归法计算每个x的y预测值时所产生的标准误差。计算结果如图16-116所示。

图16-115 基础数据

图16-116 计算结果

使用指南

参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略,但包含零值的单元格将计算在内。

如果参数为错误值或为不能转换成数字的文本,将会导致错误;如果known_y’s和known_x’s的数据点个数不同,函数STEYX返回错误值“#N/A”。如果known_y’s和known_x’s为空或其数据点个数小于3,函数STEYX返回错误值“#DIV/0!”。预测值y的标准误差计算公式如下。

其中x和y是样本平均值AVERAGE(known_x’s)和AVERAGE(known_y’s),且n是样本大小。

Excel 应用SLOPE函数计算线性回归线的斜率

SLOPE函数用于计算根据known_y’s和known_x’s中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的垂直距离与水平距离的比值,也就是回归直线的变化率。SLOPE函数的语法如下。


SLOPE(known_y's,known_x's)

其中参数known_y’s为数字型因变量数据点数组或单元格区域,known_x’s为自变量数据点集合。

典型案例

已知一组x、y值,根据这些数据点计算拟合的线性回归直线的斜率。基础数据如图16-113所示。

步骤1:打开例子工作簿“SLOPE.xlsx”。

步骤2:在单元格A10中输入公式“=SLOPE(A2:A8,B2:B8)”,用于计算线性回归直线的斜率。计算结果如图16-114所示。

图16-113 基础数据

图16-114 计算结果

使用指南

1)参数可以是数字,或者是包含数字的名称、数组或引用。

2)如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略,但包含零值的单元格将计算在内。

3)如果known_y’s和known_x’s为空或其数据点个数不同,函数SLOPE返回错误值“#N/A”。回归直线的斜率计算公式如下。

其中x和y是样本平均值AVERAGE(known_x’s)和AVERAGE(known_y’s)。

4)函数SLOPE和INTERCEPT中使用的下层算法与函数LINEST中使用的下层算法不同。当数据未定且共线时,这些算法之间的差异会导致不同的结果。例如,如果参数known_y’s的数据点为0,参数known_x’s的数据点为1:

·SLOPE和INTERCEPT返回错误“#DIV/0!”。SLOPE和INTERCEPT算法用来查找一个且仅一个答案,在这种情况下可能有多个答案。

·LINEST返回值0。LINEST算法用来返回共线数据的合理结果,在这种情况下至少可找到一个答案。

Excel 计算线性回归线截距:INTERCEPT函数

INTERCEPT函数用于利用现有的x值与y值计算直线与y轴的截距。截距为穿过已知的known_x’s和known_y’s数据点的线性回归线与y轴的交点。当自变量为0(零)时,使用INTERCEPT函数可以决定因变量的值。例如,当所有的数据点都是在室温或更高的温度下取得的,可以用INTERCEPT函数预测在0℃时金属的电阻。INTERCEPT函数的语法如下:


INTERCEPT(known_y's,known_x's)

其中,known_y’s参数为因变的观察值或数据集合,known_x’s参数为自变的观察值或数据集合。下面通过实例详细讲解该函数的使用方法与技巧。

打开“INTERCEPT函数.xlsx”工作簿,切换至“Sheet1”工作表,本例中的原始数据如图18-70所示。该工作表中记录了一组x、y值,要求计算直线与y轴的截距。具体的操作步骤如下。

选中A8单元格,在编辑栏中输入公式“=INTERCEPT(A2:A6,B2:B6)”,利用上面已知的x值与y值计算直线与y轴的截距,输入完成后按“Enter”键返回计算结果,如图18-71所示。

图18-70 原始数据

图18-71 计算截距

参数可以是数字,或者包含数字的名称、数组或引用。如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略;但包含零值的单元格将计算在内。如果known_y’s和known_x’s所包含的数据点个数不相等或不包含任何数据点,则函数INTERCEPT返回错误值“#N/A”。回归线a的截距公式为:

公式中斜率b计算如下:

其中x和y是样本平均值AVERAGE(known_x’s)和AVERAGE(known_y’s),n为样本大小。

Excel 线性回归法预测标准误差:STEYX函数

STEYX函数用于计算通过线性回归法计算每个x的y预测值时所产生的标准误差。标准误差用来度量根据单个x变量计算出的y预测值的误差量。STEYX函数的语法如下:


STEYX(known_y's,known_x's)

其中,known_y’s参数为因变量数据点数组或区,known_x’s参数为自变量数据点数组或区域。下面通过实例详细讲解该函数的使用方法与技巧。

打开“STEYX函数.xlsx”工作簿,切换至“Sheet1”工作表,本例中的原始数据如图18-68所示。该工作表中记录了一组x、y值,要求用线性回归法计算每个x的y预测值时所产生的标准误差。具体的操作步骤如下。

选中A10单元格,在编辑栏中输入公式“=STEYX(A2:A8,B2:B8)”,用线性回归法计算每个x的y预测值时所产生的标准误差,输入完成后按“Enter”键返回计算结果,如图18-69所示。

图18-68 原始数据

图18-69 计算标准误差

参数可以是数字或者包含数字的名称、数组或引用。逻辑值和直接键入参数列表中代表数字的文本被计算在内。如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略;但包含零值的单元格将计算在内。如果参数为错误值或为不能转换成数字的文本,将会导致错误。如果known_y’s和known_x’s的数据点个数不同,函数STEYX返回错误值“#N/A”。如果known_y’s和known_x’s为空或其数据点个数小于3,函数STEYX返回错误值“#DIV/0!”。预测值y的标准误差计算公式如下:

其中x和y是样本平均值AVERAGE(known_x’s)和AVERAGE(known_y’s),且n是样本大小。

Excel 计算线性回归线斜率:SLOPE函数

SLOPE函数用于计算根据known_y’s和known_x’s中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的垂直距离与水平距离的比值,也就是回归直线的变化率。SLOPE函数的语法如下:


SLOPE(known_y's,known_x's)

其中,known_y’s参数为数字型因变量数据点数组或单元格区域,known_x’s参数为自变量数据点集合。下面通过实例详细讲解该函数的使用方法与技巧。

打开“SLOPE函数.xlsx”工作簿,切换至“Sheet1”工作表,本例中的原始数据如图18-66所示。该工作表中记录了一组x、y值,要求根据这些数据点计算拟合的线性回归直线的斜率。具体的操作步骤如下。

选中A10单元格,在编辑栏中输入公式“=SLOPE(A2:A8,B2:B8)”,用于计算线性回归直线的斜率,输入完成后按“Enter”键返回计算结果,如图18-67所示。

图18-66 原始数据

图18-67 计算斜率

注意:

1)参数可以是数字,或者是包含数字的名称、数组或引用。

2)如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略;但包含零值的单元格将计算在内。

3)如果known_y’s参数和known_x’s参数为空或其数据点个数不同,函数SLOPE返回错误值“#N/A”。回归直线的斜率计算公式如下:

其中x和y是样本平均值AVERAGE(known_x’s)和AVERAGE(known_y’s),n是样本大小。