LINEST函数：财务预测、审计评估必会！

函数公式、职场模板、财务应用、分析图表、练习题、软件工具、表格合并、Office 365、Power Query、表格美化、符号作用、条件格式、学会骗、一本不正经、避坑指南、数据整理、筛选技巧、偷懒宝典

👆专题文章👆

👇最新文章👇

·  正  ·  文  ·  来  ·  啦  ·

财务预测
2020年3月，本公众号（Excel偷懒的技术）写了一篇介绍如何使用折线图的趋势线来做预测疫情的发展：
后来，在此文基础上，进一步拓展，介绍如何使用TREND函数，根据已知数据，来预测、评估：
从这篇文章我们知道，利用TREND函数，只能得到未来数据的预测值（结果），但是，不知道这些结果是根据什么方程式计算得来的。
要想知道计算未来值的方程式（线性回归方程），就要用到LINEST函数。
本文就来介绍LINEST函数。
一、统计知识
佛曰：“一切诸果，皆从因起，一切诸报，皆从业起。”事出必有因，有果必有因。从统计学上来讲，有自变量，有因变量。问题的关键是：自变量、因变量的关系如何，相关性有多大。用什么来度量？
比如：差旅费、招待费肯定与销售规模有一定关系；我们的工资与所在的行业、城市、学历、工作年限有一定的关系。我们需要知道的是他们的关系到底有多大。
能不能根据已有的数据，来发现其因果关系，并用一个公式来描述。
比如，某商品的销售量与当地人口数量存在一定的比例关系，我们就可利用已有历史数据绘制散点图。如果能找到一个线条，刚好穿过所有的点。那么生成该线条的公式，就能完美地描述销量和人口的因果关系。
但是，这在现实生活中是几乎不可能，样本数量越多，越不可能让线条穿过所有的点。只能找最接近的线。怎么才算最接近的线条？ 那就是：
所有观测点到直线的距离和最小，也就是“误差的平方和最小”。
生成这条最接近的线条的公式，就是线性回归方程式。
在Excel中，线性回归方程的系数可以用LINEST函数来计算，不管是多元线性回归还是多项式回归，都可用LINEST函数来计算。
二、LINEST函数
语法：
=LINEST (已知因变量数组，已知自变量数组，[const], [stats])
第三参数[const]：指定是否将常量 b 强制设为 0，
第四参数[stats]：是否返回附加回归统计值
LINEST函数使用数组公式输入（Excel 2019之前的版本请按Ctrl+Shift+Enter完成输入），计算的结果也是数值数组。
生成的结果如下：
提醒：
最常用的就是第一行，以及第三行的R平方。
如果第四参数为0（FALSE），则返回的结果只有第一行
下面我们来看具体运用。
案例1：一元线性回归

B2:C5为人口和销售量数据，根据此数据，可以用LINEST函数计算一元回归方程的各个系数
=LINEST(B2:B5,C2:C5,1,1)
如果将第四参数设为0，
=LINEST(B2:B5,C2:C5,1,0)
公式结果如下：
案例2：多元线性回归
用于多元线性回归预测
A15单元格的公式为：
=LINEST(E2:E12,A2:D12,TRUE,TRUE)

提醒：

请注意计算结果中m₁、m₂……mn与X₁、X₂……的对应关系。

所以，如果根据计算出的A15:D15的系数，手动计算评估A23:D23数据所对应的价值，其公式为：

=D15*A23+C15*B23+B15*C23+A15*D23+E15

其结果 与TREND计算的结果完全一样：

=TREND(E2:E12,A2:D12,A23:D23)

案例3：多项式回归预测

LINEST函数还可用于多项式回归预测。

我们来看一下鲢鱼体长与每千克尾数的关系，第一张图是一元线性回归。

可用公式

=LINEST(D5:D35,C5:C35)

求出其系数。

可以看出，其趋势线与数据的拟合度并不好，R平方值为0.8996。

我们将趋势线改为多项式，阶数为2

趋势线公式为：

y = 2.2836x2 - 71.701x + 590.68

趋势线与大多数点基本上重合了，拟合度很高。

接下来，在B列添加一列数据，数据等于C列值的平方。

然后再用LINEST函数对B列C列和D列进行计算

=LINEST(D5:D35,B5:C35,1,1)

得到的系数与多项式图表的公式中的系数完全相符。

这说明，LINEST函数不但可以用于多元线性预测，还可用于多项式预测。

如果每次都要去添加辅助列来构造计算，就比较麻烦。能否不添加辅助列？

我们可以使用下面的数组公式：

=LINEST(D5:D35,C5:C35^{1,2,3},1,1)

解释：

^表示几次方，

{1，2，3}是常量数组，相关知识请阅读：

大括号，用处大，{1,0} {1;0}中间分号逗号是干啥？

由于生成的结果是一个矩形区域，所以我们可以使用INDEX函数来引用各个系数，然后右拉填充：

=INDEX($G$35:$J$39,1,COLUMN(A1))

多项式各系数的完整公式为：

=INDEX(LINEST($D$5:$D$35,$C$5:$C$35^{1,2,3},1,1),1,COLUMN(A1))

赞 (0)

相关推荐