项目九 Excel 在统计分析中的应用

Excel在统计分析中的应用操作步骤：第1步：选择“工具”下拉菜单，选择“加载宏”命令。第2步：在弹出的“加载宏”的对话框中选择“分析工具库”复选框，单击“确定”按钮（如图9-1），即可安装好“分析工具库”插件。安装完成后，在“工具”菜单栏中就出现了一个“数据分析”的命令（如图9-2）.第3步：选择“数据分析”命令，打开“分析工具库”对话框（图9-3），就可以见到统计中常用的数据分析模块，如相关系数、描述统计、回归、抽样等功能。9.1Excel数据分析程序的安装图9-1加载分析库工具库的界面

图9-2加载分析工具库后“工具”菜单

图9-3数据分析对话框Excel在统计分析中的应用9.2用Excel整理统计数据第1步：打开Excel界面，在“A1”单元格输入“业务收入计划”，并从上到下依此在“A列”输入40个分公司的数据。根据实际的需要可以将数据分成若干个组，可以将“B1”单元格输入“各组上限”，请注意，Excel在统计各组频数时，是按“各组上限在包含在本组内”的原则处理的。第2步：选择“工具”下拉菜单，选择“数据分析”。第3步：在分析工具中选择“直方图”，如图9－4。Excel在统计分析中的应用第4步：当出现“直方图”对话框时，在“输入区域”方框内键入$A$1：$A$41（“$”符号起到固定单元格坐标的作用，表示绝对引用地址），40个数据已输入该区域内（如果是分组排列的，就应选择整个矩形区域）。在“接收区域”方框内键入$B$1：$B$9，因为在“输入区域”和“接收区域”都包含了标志，所以勾选“标志”复选框。在“输出区域”方框内键入$D$1。对话框中，勾选“累积百分率”、“图表输出”复选框（如图9－5）。最后点击“确定”，就可得到结果。Excel在统计分析中的应用4输入区域：在此输入待分析数据区域的单元格范围。接收区域（可选）：在此输入接收区域的单元格范围，该区域应包含一组可选的用来计算频数的边界值。这些值应当按升序排列。标志：如果输入区域的第一行或第一列中包含标志项，则选中此复选框；输出区域：在此输入计算结果显示的单元格地址。柏拉图：选中此复选框，可以在输出表中同时显示按升序、降序排列频率数据。如果此复选框被清除，Excel将只按升序来排列数据。累积百分比：选中此复选框，可以在输出结果中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。有关结果如图9－6。完整的结果通常包括三列和一个频数分布图，第一列是数值的区间范围，第二列是数值分布的频数（不是频率），第三列是频数分布的累积百分比。Excel在统计分析中的应用Excel在统计分析中的应用用鼠标左键单击图中任一直条形，然后右键单击，在弹出的快捷菜单中选取“数据系列”格式，弹出“数据系列格式”对话框。在对话框中选择“选项”标签，把“分类间距”宽度改为0，如图9－7所示。按确定后即可得到直方图，见图9－8所示。Excel在统计分析中的应用9.3用Excel计算描述统计量第1步：打开Excel界面，在“A1”单元格输入“成绩”，并从上到下依此在“A列”输入50个学生的成绩。

第2步：选择“工具”下拉菜单，再选择“数据分析”选项。第3步：在分析工具中选择“描述统计”。第4步：当出现对话框时，在“输入区域”方框内键入A1：A51(或用鼠标选择这区域)，“分组方式”选择“逐列”单选框，因为“输入区域”包含了“成绩”这个标志，所以勾选“标志位于第一行”复选框，在“输出选项”中选择输出区域(在此选择$D$1)，再选择“汇总统计”(该选项给出全部描述统计量)，如图9－9。最后，选择确定。

Excel在统计分析中的应用9.4.1用Excel进行相关分析第1步：选择“工具”下拉菜单，再选择“数据分析”选项。

第2步：在分析工具中选择“相关系数”。

第3步：当出现对话框时，在“输入区域”方框内键入A1：B11，勾选“标志位于第一行”复选框，在“输出选项”中选择输出区域选择“新工作表组”。得图9－12，点击“确定”。Excel在统计分析中的应用9.4.2用Excel进行回归分析第1步：选择“工具”下拉菜单，选择“数据分析”选项。

第2步：在分析工具中选择“回归”。

第3步：当出现对话框时，在“输入Y的区域”方框内键入B2：B11，在“输入X的区域”方框内键入A2：A11，在“输出选项”中选择“新工作表”，如图9－14。点击“确定”。得到图9－15所示的结果。Excel在统计分析中的应用所以，该例题中得到的一元线性回归方程为：

Excel在统计分析中的应用9.5.1用移动平均法进行预测根据9.4.1中的表9－3中的资料，利用Excel对其进行三项移动平均。操作步骤：第1步：将原始数据录入Excel表格中，如图9－16所示。Excel在统计分析中的应用第2步：选择“工具”下拉菜单，选择“数据分析”选项。

第3步：在分析工具中选择“移动平均”。第4步：弹出移动平均对话框，相应作如下输入，即可得到如附图9－3所示的对话框。在“输入区域”内输入：“$B$2：$B$11”，即原始数据所在的单元格区域。在“间隔”内输入：“3”，表示使用三步移动平均法。在“输出区域”内输入：“$C$1”，即将输出区域的左上角单元格定义为$C$1。选择“图表输出”复选框和“标准误差”复选框。第5步：单击“确定”按钮。便可得到移动平均结果，如图9－18所示。分析：在附图9－18中，“C3：C10”对应的数据即为三步移动平均的预测值；单元格区域“D5：D10”即为标准误差。Excel在统计分析中的应用图9－17“移动平均”对话框“移动平均”输出结果Excel在统计分析中的应用9.5.2用指数平滑法进行预测根据9.4.1中的表9－3中的资料，利用Excel对其进行指数平滑法进行预测。操作步骤：第1步：将原始数据录入Excel表格中，如图9－16所示。第2步：选择“工具”下拉菜单。第3步：选择“数据分析”选项。第4步：在分析工具中选择“指数平滑”，单击“确定”按钮。第5步：在弹出“指数平滑”对话框，作相应输入，即可得到如图9－19所示的对话框。在“输入区域”内输入：“$B$2：$B$11”，即原始数据所在的单元格区域。在“阻尼系数”内输入：“0.5”（阻尼系数=1-平滑系数=1-0.5=0.5）。在“输出区域”内输入：“$D$2”，即将输出区域的左上角单元格定义为$D$2。选择“图表输出”复选框和“标准误差”复选框。Excel在统计分析中的应用Excel在统计分析中的应用第6步：单击“确定”按钮。经过以上各步骤操作，即可得到指数平滑结果，如图9－20所示。“C3：C12”对应的数据即为指数平滑的预测值；单元格区域“E6：E11”即为标准误差。Excel在统计分析中的应用9.5.2用回归法进行预测根据9.4.1中的表9－2中的资料，利用Excel对其进行指数平滑法进行预测。操作步骤：第1步：将原始数据录入Excel表格中，为简化计算，设置时间序号t列，如图9－21所示。第2步：选择“工具”下拉菜单。第3步：选择“数据分析”选项。第4步：在分析工具中选择“回归”，单击“确定”按钮。第5步：当出现对话框时，在“Y值输入区域”方框内输入：“$B$2：$B$11”，在“X值输入区域”方框内输入：“$C$2：$C$11”，在“输出选项”中选择“输出区域”，“输出区域”方框中输入：“$E$1”，勾选“线性拟合图”复选框，如图9－22。点击“确定”，得到回归分析结果和线性拟合图，如图9－23。Excel在统计分析中的应用图9－21输入原始数据图9－22“回归”对话框Excel在统计分析中的应用图9－23回归分析结果和线性拟合图Excel在统计分析中的应用第6步：在线性拟合图的任意数据点上右击，在弹出的菜单中执行“添加趋势线”命令，弹出“添加趋势线”对话框。在该对话框的“类型”选项卡中选择“线性”趋势预测/回归分析类型，在“选择数据系列”中