统计案例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”之间的间距过大时，可以采取折断的符号将纵轴折断,时间序列数据线图,【例】我国19912003年城乡居民家庭的人均收入数据如右表。试绘制线图,时间序列数据线图,指数平滑法 (exponential smoothing),是加权平均的一种特殊形式 对过去的观察值加权平均进行预测的一种方法 观察值时间越远，其权数随之呈现指数的下降，因而称为指数平滑 有一次指数平滑、二次指数平滑、三次指数平滑等 一次指数平滑法也可用于对时间序列进行修匀，以消除随机波动，找出序列的变化趋势,一次指数平滑 (single exponential smoothing),只有一个平滑系数 观察值离预测时期越久远，权数变得越小 以一段时期的预测值与观察值的线性组合作为第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/MSE1,统计决策, 将统计量的值F与给定的显著性水平的临界值F进行比较，作出对原假设H0的决策 根据给定的显著性水平，在F分布表中查找与第一自由度df1k-1、第二自由度df2=n-k 相应的临界值 F 若FF ，则拒绝原假设H0 ，表明均值之间的差异是显著的，所检验的因素对观察值有显著影响 若FF ，则不拒绝原假设H0 ，无证据表明所检验的因素对观察值有显著影响,单因素方差分析表 (基本结构),单因素方差分析 (例题分析),用Excel进行方差分析 (Excel分析步骤),第1步：选择【工具 】下拉菜单 第2步：选择【数据分析】选项 第3步：在分析工具中选择【单因素方差分析】 然后选择【确定】 第4步：当对话框出现 在【输入区域 】方框内键入数据单元格区域 在【】方框内键入0.05(可根据需要确定) 在【输出选项 】中选择输出区域,相关分析,相关系数一般可以从正负符号和绝对数值的大小两个层面理解。正负说明现象之间是正相关还是负相关。绝对数值的大小说明两现象之间线性相关的密切程度。,（1）r的取值在-1到+1之间。 （2）r=+1，为完全正相关；r=-1为完全负相关。表明变量之间为完全线性相关，即函数关系。 （3）r=0，表明两变量无线性相关关系。 （4）r0,表明变量之间为正相关；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 = -0.8295 + 0.037895 x 回归系数 =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的数据区域 在【置信度】选项中给出所需的数值 在【输出选项】中选择输出区域 在【残差】分析选项中选择所需的选项,