相关分析技巧：Excel箭头映像

回归分析用模拟的直线方程式Y＝a＋bX来预测销量，客观上需要知道销量（因变量）与时间序列（自变量）之间的相关程度。Excel中可将直线拟合函数LINEST作为数值引用函数INDEX的嵌套函数的方法，进行相关系数R2、截距a、斜率b的计算。

1.直线拟合函数LINEST

＝LINEST(known＿y's，known＿x's，const，stats)

式中，known＿y's表示因变量；known＿x's表示自变量；参数const表示是否将截距a强制设为0，如果为TRUE或省略，则按正常的a值计算，如果为FALSE，则将a设为0，此时直线公式为Y＝bX；stats表示是否返回附加回归统计值，如果为FALSE或省略，则函数只返回系数a和b的值，如果stats为TRUE，则函数可返回相关系数R2等统计值。

2.用嵌套函数计算斜率b、截距a、相关系数R2的函数

b＝INDEX(linest(known＿y's，known＿x's，TRUE，TRUE)，1，1)

a＝INDEX(linest(known＿y's，known＿x's，TRUE，TRUE)，1，2)

R2＝INDEX(linest(known＿y's，known＿x's，TRUE，TRUE)，3，1)

式中，INDEX为数值引用函数（见本书以前介绍），在linest返回附加回归统计值的情况下，“1，1”为返回b值，“1，2”返回a值，“3，1”返回R2值，所以用数值引用函数INDEX提取这些值。

3.相关系数的含义

在统计学中，相关系数R2的值为1则完全相关；为0则不相关；大于0.8则显著相关；在0.5～0.8之间则相关；小于0.5则弱相关。只有相关系数大于0.5时，回归直线法的预测结果才具有参考价值。

根据工作任务6-2的资料进行相关性分析并预测未来两年的销售量。

在Excel中进行相关性分析并回归预测未来两年的销售量，如图6-9所示。

图6-9　相关预测与Excel箭头映像（Excel 2019绘图格式选项卡）

（1）录入A列的文字；录入B2至G3单元区域的已知数据；合并A1至G1、B4至C4等单元区域；设置字体字号（包括设置上标等），调整行高、列宽。

（2）计算相关系数。即B4＝INDEX（LINEST（B3：G3，B2：G2，TRUE，TRUE），3，1），这是相关系数R2的函数公式。(www.xing528.com)

（3）用条件IF函数判断相关程度。在B5单元格键入IF函数公式“＝IF（B4>0.8，"显著相关"，IF（B4>0.5，"相关"，"弱相关"））”。

（4）计算直线参数值。B6＝INDEX（LINEST（B3：G3，B2：G2，TRUE，TRUE），1，2），这是截距参数a的函数公式。

B7＝INDEX（LINEST（B3：G3，B2：G2，TRUE，TRUE），1，1），这是斜率参数b的函数公式。

（5）在B8单元格键入公式“＝"Y＝"＆ROUND（B6，2）＆"＋"＆ROUND（B7，2）＆"X"”；即用字符运算显示预测公式。

（6）销量预测。在B9单元格键入“＝ROUND（B6＋B7∗MID（A9，2，1），2）”；在B10单元格键入“＝ROUND（B6＋B7∗MID（A10，2，1），2）”。

（7）在Excel 2007—Excel 2019中插入右弧形箭头，方法如下（Excel 2003及其以前版本见后“Excel版本提示”）：

a.单击“插入/插图/形状”命令，在弹出的下拉菜单中选择“箭头总汇/右弧形箭头”，此时光标变“＋”字状，在工作表中拖动一个箭头图。

b.选定插入的箭头时，将显示8个大小调节柄、2个宽度调节柄及1个旋转柄；通过这些调节柄进行大小、位置的调整。

c.选定插入的箭头，功能区将显示“绘图工具/格式”选项卡；单击该选项卡“形状样式”组右部的对话框启动器，进入如图6-10所示的“设置形状格式”对话框，进行以下设置。

图6-10　设置形状格式对话框（Excel 97—Excel 2019）

d.在Excel 2013—Excel 2019中单击效果按钮，如图6-10（a）所示；在映像列表中，选择预设“半映像”，透明度“50%”，大小“95%”，模糊“0磅”，距离“1磅”。

单击填充线条按钮，在填充列表中，选择“渐变填充”，在预设渐变中选择“底部聚光灯”或“红日西斜”等，在方向中选择“从右下角”；选择线条为“实线、黑色、1磅”。

e.在Excel 2007、Excel 2010中应分别选定左部的“映像”　“填充”　“线条颜色”项，在右部的列表中进行以上格式的设置，如图6-10（b）所示。

（8）取消网格显示；设置打印表格线（边框）。

（1）在Excel 2003及其以前版本中单击“绘图工具栏”的“自选图形/箭头总汇/右弧形箭头”项，在工作表中拖动并插入箭头图。

（2）在Excel 2003及其以前版本中设置填充色，应单击绘图工具栏“填充色”按钮中的“填充效果”命令，进入“填充效果”对话框，如图6-10（c）所示；在“渐变”卡片中选择“双色”（黑色与灰色），透明度为0%～46%，底纹样式为“水平”。

（3）Excel 2003及其以前版本中选定箭头图，单击绘图工具栏线条颜色中的“黑色”。

（4）Excel 2003及其以前版本没有映像设置功能，应设置为“阴影”，方法是：选定箭头图，单击绘图工具栏“阴影样式/阴影设置”，在“阴影颜色”按钮中选择“黑色、透明度66%”（透明度应单击阴影工具栏“阴影颜色/其他阴影颜色”进入“颜色”对话框设置）。