回归直线预测Excel教程

回归分析法是根据过去若干期间的业务量（销售额或销售量等）及资金占用额的历史资料进行分析，它应用最小平方法的原理计算不变资金和单位业务量变动资金，并据此预测计划期资金需要量的一种方法。回归分析法一般用直线方程式Y＝a＋bX来预测资金需要量。其参数计算公式如下：

式中，y表示业务量；a表示固定不变资金；b表示单位业务量变动资金；x表示资金占用额；n表示历史数据的期间项数；∑表示求和。

某公司近5年产销量及资金占用见表4-2。经预测，2019年的产销量为278万件，请计算分析该年度资金占用额是多少。

表4-2　某公司近5年产销量与资金占用表

Excel中回归分析的成果如图4-3所示。

(www.xing528.com)

（1）在“Excel财务会计”工作簿中新建“回归直线”工作表。根据表4-2，录入文本数据、已知数值等。进行手动或自动换行、其他格式设置。

（2）设置上标与下标。在F2单元格录入全部字符“Xi2”；选定字符“i”，右击，选择并进入“设置单元格格式”对话框；勾选特殊效果中的“下标”项，单击“确定”按钮。

选定F2单元格的字符“2”右击，选择并进入“设置单元格格式”对话框，勾选“上标”项。

（3）乘积、平方与求和。在E3单元格键入公式“＝C3∗D3”；F3单元格键入公式“＝C3＾2”；C8单元格键入公式“＝SUM（C3：C7）”。

图4-3　回归分析（Excel 2016开发工具选项卡）

自动填充E4至E7、F4至F7、D8至F8单元区域的公式。

（4）用计数函数Count求年度项数。选定B8单元格，单击编辑框前插入函数按钮进入“插入函数”对话框，选择“统计”类别中的Count函数进入“函数参数”对话框；在参数中键入需要计数的单元区域“B3：B7”；单击“确定”按钮，编辑框将显示公式“＝COUNT（B3：B7）”。

也可以直接在B8单元格键入计数函数公式“＝COUNT（B3：B7）”。

（5）输入方程式的参数公式。在C9单元格中输入参数a的公式“＝（D8-C10∗C8）/B8”；在C10单元格中输入参数b的公式“＝（B8∗E8-C8∗D8）/（B8∗F8-C8＾2）”。

（6）键入预测公式。在D11单元格中，按直线方程式公式Y＝a＋bx的格式，键入其预测公式“＝C9＋C10∗C11”。