资源简介

(共24张PPT)
基础统计实务
项目九：Excel 在统计分析中的应用
Excel 在统计分析中的应用
操作步骤：
第1步：选择“工具”下拉菜单，选择“加载宏”命令。
第2步：在弹出的“加载宏”的对话框中选择“分析工具库”复选框，单击“确定”按钮（如图9-1），即可安装好“分析工具库”插件。安装完成后，在“工具”菜单栏中就出现了一个“数据分析”的命令（如图9-2）.
第3步：选择“数据分析”命令，打开“分析工具库”对话框（图9-3），就可以见到统计中常用的数据分析模块，如相关系数、描述统计、回归、抽样等功能。
9.1 Excel数据分析程序的安装
图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 在统计分析中的应用
输入区域：在此输入待分析数据区域的单元格范围。
接收区域（可选）：在此输入接收区域的单元格范围，该区域应包含一组可选的用来计算频数的边界值。这些值应当按升序排列。
标志：如果输入区域的第一行或第一列中包含标志项，则选中此复选框；
输出区域：在此输入计算结果显示的单元格地址。
柏拉图：选中此复选框，可以在输出表中同时显示按升序、降序排列频率数据。如果此复选框被清除，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步：在线性拟合图的任意数据点上右击，在弹出的菜单中执行“添加趋势线”命令，弹出“添加趋势线”对话框。在该对话框的“类型”选项卡中选择“线性”趋势预测/回归分析类型，在“选择数据系列”中选择“Y”，如图9－24所示。切换到“选项”选项卡，勾选“显示公式”、“显示R平方值”复选框，如图9－25所示。
图9－24“类型”选项卡设置
图9－25“选项”选项卡设置
Excel 在统计分析中的应用
图9－26长期趋势线和直线趋势方程
预测2012年的人均GDP，令 ，代入直线方程 ，可得：
Excel 在统计分析中的应用

展开更多......

收起↑