统计案例Excel上机.ppt

评分标准 第1题 25分 其中第1问5分 第2问和第3问各10分 第2 3 4题 各25分 其中 1 正确应用统计方法5分 2 正确使用Excel进行统计分析10分 3 根据统计分析结果得出较有建设性的结论10分 Excel函数 包括统计函数 的使用 选择适当的函数值存放的单元格 使用 插入 菜单 函数 选项 或使用 常用 工具栏中的 粘贴函数 按钮进入 粘贴函数 对话框 在 函数分类 列表中选择 统计 在 函数名 列表中选择相应的函数 对话框内提示函数的语法和功能 点击 确定 按钮 出现输入数据或单元格范围的对话框 输入数据或单元格范围 点击 确定 按钮 在函数值存放的单元格即计算出 返回 函数值 Excel制表 绘图 1 单击 插入 菜单 选择 图表 选项 或单击 常用 工具栏中的 图表向导 按钮 出现 图表向导 4步骤之1 图表类型 对话框 在 图表类型 列表中选择相应的图表 2 单击 下一步 出现 图表向导 4步骤之2 图表数据源 对话框 选择数据区域 3 单击 下一步 出现 图表向导 4步骤之3 图表选项 对话框 选择 数据标志 选项卡中的数据标志为 显示百分比 4 在 标题 选项卡中的图表标题文本框中输入图的标题 5 在 图例 选项卡中 点击 显示图例 复选框使之被选中 并在 位置 单选框中选择 靠右 6 单击 下一步 出现 图表向导 4步骤之4 图表位置 对话框 当前的选择表示该图表作为对象 插入 显示在 图表 工作表内 7 单击 完成 时间序列数据 线图 表示时间序列数据趋势的图形时间一般绘在横轴 数据绘在纵轴图形的长宽比例大致为10 74 一般情况下 纵轴数据下端应从 0 开始 以便于比较 数据与 0 之间的间距过大时 可以采取折断的符号将纵轴折断 时间序列数据 线图 例 我国1991 2003年城乡居民家庭的人均收入数据如右表 试绘制线图 时间序列数据 线图 指数平滑法 exponentialsmoothing 是加权平均的一种特殊形式对过去的观察值加权平均进行预测的一种方法观察值时间越远 其权数随之呈现指数的下降 因而称为指数平滑有一次指数平滑 二次指数平滑 三次指数平滑等一次指数平滑法也可用于对时间序列进行修匀 以消除随机波动 找出序列的变化趋势 一次指数平滑 singleexponentialsmoothing 只有一个平滑系数观察值离预测时期越久远 权数变得越小以一段时期的预测值与观察值的线性组合作为第t 1期的预测值 其预测模型为 Yt为第t期的实际观察值Ft为第t期的预测值 为平滑系数 0 1 一次指数平滑 的确定 不同的 会对预测结果产生不同的影响当时间序列有较大的随机波动时 宜选较大的 以便能很快跟上近期的变化当时间序列比较平稳时 宜选较小的 选择 时 还应考虑预测误差用误差均方来衡量预测误差的大小确定 时 可选择几个进行预测 然后找出预测误差最小的作为最后的值 一次指数平滑 例题分析 用Excel进行指数平滑预测第1步 选择 工具 下拉菜单第2步 选择 数据分析 并选择 指数平滑 然后 确定 第3步 当对话框出现时在 输入区域 中输入数据区域在 阻尼系数 注意 阻尼系数 1 输入的值选择 确定 例 对居民消费价格指数数据 选择适当的平滑系数 采用Excel进行指数平滑预测 计算出预测误差 并将原序列和预测后的序列绘制成图形进行比较 一次指数平滑 例题分析 一次指数平滑 例题分析 分类数据整理 频数分布表 例题分析 例 一家市场调查公司为研究不同品牌饮料的市场占有率 对随机抽取的一家超市进行了调查 调查员在某天对50名顾客购买饮料的品牌进行了记录 如果一个顾客购买某一品牌的饮料 就将这一饮料的品牌名字记录一次 右边就是记录的原始数据 使用Excel计数函数 COUNTIF 如果只需要计算某一类别的数据个数 可以使用Excel中的统计函数 COUNTIF 在对话框 Range 后输入数据区域 在 Criteria 后输入数字 表达式 字符串等 计数单元格必须符合的条件 即可得出结果例如 我们要计算出可口可乐出现的频数 在 Range 后输入A1 A50 数据所在的区域 在 Criteria 后输入 可口可乐 结果为15 如果数据区域是数值型数据 计算符合特定条件的数据个数 则可在 Criteria 后输入 某一数值 某一数值 某一数值 等等 使用Excel频数函数 FREQUENCY Excel的 直方图 工具的缺陷是 频数分布及直方图没有与数据链接 当改变任何一个数据时 频数分布表和直方图不会跟着改变 使用统计函数 FREQUENCY 创建频数分布表和直方图可解决这一问题 具体步骤是 1 选择与接受区域相临近的单元格区域 作为频数分布表输出的区域 2 选择统计函数中的 FREQUENCY 函数 3 在对话框 Date array 后输入数据区域 在 Bins array 后输入接受区域 4 按F2键 同时按下 Ctrl Shift Enter 组合键 即得到频数分布 用Excel计算描述统计量 将120个销售量的数据输入到Excel工作表中 然后按下列步骤操作第1步 选择 工具 下拉菜单第2步 选择 数据分析 选项第3步 在分析工具中选择 描述统计 然后选择 确定 第4步 当对话框出现时在 输入区域 方框内键入数据区域在 输出选项 中选择输出区域选择 汇总统计 选择 确定 数据透视表 可以从复杂的数据中提取有用的信息可以对数据表的重要信息按使用者的习惯或分析要求进行汇总和作图形成一个符合需要的交叉表 列联表 在利用数据透视表时 数据源表中的首行必须有列标题 数据透视表 用Excel创建数据透视表 第1步 在Excel工作表中建立数据清单第2步 选中数据清单中的任意单元格 并选择 数据 菜单中的 数据透视表和数据透视图 第3步 确定数据源区域第4步 在 向导 3步骤之3 中选择数据透视表的输出位置 然后选择 布局 第5步 在 向导 布局 对话框中 依次将 分类变量 拖左边的 行 区域 上边的 列 区域 将需要汇总的 变量 拖至 数据区域 第6步 然后单击 确定 自动返回 向导 3步骤之3 对话框 然后单击 完成 即可输出数据透视表 方差分析 分析4个行业之间的服务质量是否有显著差异 也就是要判断 行业 对 投诉次数 是否有显著影响作出这种判断最终被归结为检验这四个行业被投诉次数的均值是否相等若它们的均值相等 则意味着 行业 对投诉次数是没有影响的 即它们之间的服务质量没有显著差异 若均值不全相等 则意味着 行业 对投诉次数是有影响的 它们之间的服务质量有显著差异 构造检验的统计量 计算总误差平方和SST 全部观察值与总平均值的离差平方和反映全部观察值的离散状况其计算公式为 构造检验的统计量 计算组间平方和SSA 各组平均值与总平均值的离差平方和反映各总体的样本均值之间的差异程度该平方和既包括随机误差 也包括系统误差计算公式为 前例的计算结果SSA 1456 608696 构造检验的统计量 计算组内平方和SSE 每个水平或组的各样本数据与其组平均值的离差平方和反映每个样本各观察值的离散状况该平方和反映的是随机误差的大小计算公式为 前例的计算结果SSE 2708 构造检验的统计量 三个平方和的关系 总离差平方和 SST 误差项离差平方和 SSE 水平项离差平方和 SSA 之间的关系 SST SSA SSE 前例的计算结果4164 608696 1456 608696 2708 构造检验的统计量 计算均方MS 各误差平方和的大小与观察值的多少有关 为消除观察值多少对误差平方和大小的影响 需要将其平均 这就是均方 也称为方差由误差平方和除以相应的自由度求得三个平方和对应的自由度分别是SST的自由度为n 1 其中n为全部观察值的个数SSA的自由度为k 1 其中k为因素水平 总体 的个数SSE的自由度为n k 构造检验的统计量 计算均方MS 组间方差 SSA的均方 记为MSA 计算公式为 组内方差 SSE的均方 记为MSE 计算公式为 构造检验的统计量 计算检验统计量F 将MSA和MSE进行对比 即得到所需要的检验统计量F当H0为真时 二者的比值服从分子自由度为k 1 分母自由度为n k的F分布 即 构造检验的统计量 F分布与拒绝域 如果均值相等 F MSA MSE 1 统计决策 将统计量的值F与给定的显著性水平 的临界值F 进行比较 作出对原假设H0的决策根据给定的显著性水平 在F分布表中查找与第一自由度df1 k 1 第二自由度df2 n k相应的临界值F 若F F 则拒绝原假设H0 表明均值之间的差异是显著的 所检验的因素对观察值有显著影响若F F 则不拒绝原假设H0 无证据表明所检验的因素对观察值有显著影响 单因素方差分析表 基本结构 单因素方差分析 例题分析 用Excel进行方差分析 Excel分析步骤 第1步 选择 工具 下拉菜单第2步 选择 数据分析 选项第3步 在分析工具中选择 单因素方差分析 然后选择 确定 第4步 当对话框出现在 输入区域 方框内键入数据单元格区域在 方框内键入0 05 可根据需要确定 在 输出选项 中选择输出区域 相关分析 相关系数一般可以从正负符号和绝对数值的大小两个层面理解 正负说明现象之间是正相关还是负相关 绝对数值的大小说明两现象之间线性相关的密切程度 1 r的取值在 1到 1之间 2 r 1 为完全正相关 r 1为完全负相关 表明变量之间为完全线性相关 即函数关系 3 r 0 表明两变量无线性相关关系 4 r 0 表明变量之间为正相关 r 0 表明变量之间为负相关 5 r的绝对值越接近于1 表明线性相关关系越密切 r越接近于0 表明线性相关关系越不密切 相关程度可分为以下几种情况 为无线性相关 0 3 0 5 为低度线性相关 0 5 0 8 为显著线性相关 0 8 一般称为高度线性相关 以上说明必须建立在相关系数通过显著性检验的基础之上 利用EXCEL计算相关系数 以表1的资料为例 处理的简要步骤与结果如下 在EXCEL主页面中 从 工具 数据分析 相关系数 进入相关关系窗口做相应处理得以下结果 回归分析 一 回归分析的概念和特点1 回归分析的概念回归分析就是对具有相关关系的变量之间数量变化的一般关系进行测定 确定一个相关的数学表达式 以便于进行估计或预测的统计方法 2 回归分析的特点 1 在变量之间 必须根据研究目的具体确定哪些是自变量 哪个是因变量 2 回归方程的作用在于 在给定自变量的数值情况下来估计因变量的可能值 一个回归方程只能做一种推算 推算的结果表明变量之间具体的变动关系 3 直线回归方程中 自变量的系数为回归系数 回归系数的符号为正时 表示正相关 回归系数的符号为负时 表示负相关 4 确定回归方程时 只要求因变量是随机的 而自变量是给定的数值 二 一元线性回归分析 1 一元线性回归模型的确定设有两个变量和 变量的取值随变量取值的变化而变化 我们称为因变量 为自变量 反之亦然 一般来说 对于具有线性相关关系的两个变量 可以用一条直线方程来表示它们之间的关系 即 倚回归方程 倚回归方程 建立一元线性回归模型 例 求不良贷款对贷款余额的回归方程 回归方程为 y 0 8295 0 037895x回归系数 0 037895表示 贷款余额每增加1亿元 不良贷款平均增加0 037895亿元 用Excel进行回归分析 估计方程的求法 例题分析 不良贷款对贷款余额回归方程的图示 回归系数的检验 P值的应用 P 0 000000 0 05 拒绝原假设 不良贷款与贷款余额之间有显著的线性关系 三 多元回归模型与回归方程 例 一家大型商业银行在多个地区设有分行 为弄清楚不良贷款形成的原因 抽取了该银行所属的25家分行2002年的有关业务数据 试建立不良贷款y与贷款余额x1 累计应收贷款x2 贷款项目个数x3和固定资产投资额x4的线性回归方程 并解释各回归系数的含义 用Excel进行回归 第1步 选择 工具 下拉菜单第2步 选择 数据分析 选项第3步 在分析工具中选择 回归 选择 确定 第4步 当对话框出现时在 Y值输入区域 设置框内键入Y的数据区域在 X值输入区域 设置框内键入X的数据区域在 置信度 选项中给出所需的数值在 输出选项 中选择输出区域在 残差 分析选项中选择所需的选项