函数公式、职场模板、财务应用、分析图表、练习题、软件工具、表格合并、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)
=LINEST(B2:B5,C2:C5,1,0)
=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}是常量数组，相关知识请阅读：
由于生成的结果是一个矩形区域，所以我们可以使用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))
欢迎加入Excel偷懒的技术读者微信群，学习更多Excel技巧。
加偷懒小助手拉你入群，微信号toulanxzs
Excel偷懒的技术微信公众号的OFFICE图书：
本文使用 文章同步助手 同步
网友评论