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)输入数组常量时,应当使用逗号分隔同一行中的数据,用分号分隔不同行中的数据。