统计案例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 : 7 4. 一般情况下，纵轴数据下端应从“0”开始， 以便于比较。数据与“0”之间的间距过大时， 可以采取折断的符号将纵轴折断 时间序列数据线图 【例例】我国我国 1991199120032003 年城乡居民家年城乡居民家 庭的人均收入庭的人均收入 数据如右表。数据如右表。 试绘制线图试绘制线图 ￥ 19912003年城乡乡居民家庭人均收入 年份城镇镇居民农农村居民 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 1700.6 2026.6 2577.4 3496.2 4283.0 4838.9 5160.3 5425.1 5854.0 6280.0 6859.0 7702.8 8472.2 708.6 784.0 921.6 1221.0 1577.7 1926.1 2091.1 2162.0 2210.3 2253.4 2366.4 2475.6 2622.2 时间序列数据线图 指数平滑法 (exponential smoothing) 是加权平均的一种特殊形式 对过去的观察值加权平均进行预测的一种方法 观察值时间越远，其权数随之呈现指数的下降， 因而称为指数平滑 有一次指数平滑、二次指数平滑、三次指数平滑 等 一次指数平滑法也可用于对时间序列进行修匀， 以消除随机波动，找出序列的变化趋势 一次指数平滑 (single exponential smoothing) 只有一个平滑系数 观察值离预测时期越久远，权数变得越小 以一段时期的预测值与观察值的线性组合 作为第t+1期的预测值，其预测模型为 Yt为第t期的实际观察值 Ft 为第t期的预测值 为平滑系数 (0 某一数值 ”、“F ，则拒绝原假设H0 ，表明均值之 间的差异是显著的，所检验的因素对观察值有 显著影响 若F0,表明变量之间为正 相关；r0，表明变量之间为 负相关。 （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 = y = - -0.82950.8295 + + 0.0378950.037895 x x 回归系数回归系数 =0.037895 =0.037895 表示，贷款余额每增表示，贷款余额每增 加加1 1亿元，不良贷款平均增加亿元，不良贷款平均增加0.0378950.037895亿元亿元 用Excel进行回归分析 估计方程的求法 (例题分析) 不良贷款对贷款余额回归方程的图示 回归系数的检验 P 值的应用 P P=0.000000=0.000000 =0.05=0.05，拒绝原假设，拒绝原假设，不良贷款与贷不良贷款与贷 款余额之间有显著的线性关系款余额之间有显著的线性关系 （三）多元回归模型与回归方程 【例例】一家大型商业银行在多个地区设有分一家大型商业银行在多个地区设有分 行，为弄清楚不良贷款形成的原因，抽取行，为弄清楚不良贷款形成的原因，抽取 了该银行所属的了该银行所属的2525家分行家分行20022002年的有关业年的有关业 务数据。试建立不良贷款务数据。试建立不良贷款y y与贷款余额与贷款余额x x 1 1 、 累计应收贷款累计应收贷款x x 2 2 、贷款项目个数贷款项目个数x x 3 3 和固定资和固定资 产投资额产投资额x x 4 4 的线性回归方程，并解释各回的线性回归方程，并解释各回 归系数的含义归系数的含义 用用ExcelExcel进行回归进行回归 第1步：选择“工具”下