旅行社管理实验.pdf
旅行社管理实验.pdf
统计学实训 《Excel 在统计中的应用》 实训指导书 经济与管理学院 刘红红 Excel 在统计中的应用 经济与管理各专业(本科)均开设统计学，总学时 48：理论课时 40、实 训课时 8。实训内容: 1. Excel 在数据整理中的应用 2. 用“图表向导”工具绘制统计图表举例 3. Excel 在描述统计中的应用 4. Excel 在抽样推断中的应用 5. 用 Excel 进行相关与回归分析 6. 用 Excel 计算各种动态分析指标 7. 用 Excel 进行时间序列分析 1. Excel 在数据整理中的应用 数据的整理与显示 1. 要弄清所面对的数据类型 不同类型的数据，采取不同的处理方式和方法 2. 对分类数据和顺序数据主要是作分类整理 3. 对数值型数据则主要是作分组整理 4. 适合于低层次数据的整理和显示方法也适合于高层次的数据；但适 合于高层次数据的整理和显示方法并不适合于低层次的数据 1.1 品质数据的整理与显示 1.1.1 分类数据的整理与图示 分类数据整理的基本过程： 1. 列出各类别 2. 计算各类别的频数 3. 制作频数分布表 4. 用图形显示数据 分类数据的整理可计算的统计量： 1. 频数(frequency) ：落在各类别中的数据个数. 2. 比例(proportion) ：某一类别数据占全部数据的比值. 3. 百分比(percentage) ：将对比的基数作为 100 而计算的比值. 4. 比率(ratio) ：不同类别数值的比值. 1.1.2 顺序数据的整理与图示 顺序数据的整理可计算的统计量与图示 1.累积频数(cumulative frequencies)：各类别频数的逐级. 2.累积频率(cumulative percentages)：各类别频率(百分比)的逐级累加. 3.顺序数据的图示---环形图(doughnut chart):环形图中间有一个“空洞”，样 本或总体中的每一部分数据用环中的一段表示. 1.2 数值型数据的整理与显示 1.2.1 数据分组 1.2.2 数值型数据的图示 分组数据—直方图和折线图(histogram)和(frequency polygon) Excel：点击“工具——数据分析——直方图”，选择原始数据为输入区 域，再接受区域中输入分组栏，即各组的上限-1，即可得到频数分布表 和直方图。 未分组数据—茎叶图和箱线图(stem-and-leaf display)和(box plot) 时间序列数据—线图(line plot) 2. 用“图表向导”工具绘制统计图表举例 图形功能举例： 1. 利用图表向导作图： ①条形图 ②饼图 ③环形图 ④直方图 ⑤茎叶图 ⑥箱线图 ⑦趋势线 2. 在图形上添加曲线等附加信息。 表格功能举例： 1. 公式复制时的相对地址与绝对地址 2. 报表汇总—分两种情况 3. 作数据透视表 数据透视表 Excel 制作： 第 1 步：在 Excel 工作表中建立数据清单 第 2 步：选中数据清单中的任意单元格，并选择【数据】菜单中的【数据 透视表和数据透视图】 第 3 步：确定数据源区域 第 4 步：在【向导—3 步骤之 3】中选择数据透视表的输出位置，然后选 择【布局】 第 5 步：在【向导—布局】对话框中，依次将“分类变量”拖至左边的“行” 区域，上边的“列”区域，将需要汇总的“变量” 拖至“数据区域” 第 6 步：然后单击【确定】，自动返回【向导—3 步骤之 3】对话框。然后 单击【完成】 ，即可输出数据透视表 3. Excel 在描述统计中的应用 3.1 描述统计量 3.1.1 反映集中趋势的描述统计量 常用的反映集中趋势的描述统计量有三个：均值、中位数和众数。前 一个平均数是根据所有标志值计算的，又被称为数值平均数，后两个平均 数是根据与其所处位置有关的部分标志值计算的，又被称为位置平均数。 3.1.2 反映离中趋势的描述统计量 常用的反映离中趋势的描述统计量（简称离中指标）有三个：全距（极 差）、平均差和标准差（方差）。当对两组数据的差异程度进行相对比较时， 往往要计算离散系数，包括全距（极差）系数、平均差系数和标准差系数， 它等于相应的离中指标除以均值，这样可以消除由于平均数的不同或单位 的差异而造成的影响。 3.1.2 反映分布趋势的描述统计量 常用的反映分布趋势的描述统计量有两个：偏斜度和峰值 偏斜度:反映以平均值为中心的分布的不对称程度 。 峰度:反映与正态分布相比某一分布的尖锐度或平坦度。 3.2 用 Excel 计算描述统计量 将已知数据输入到 Excel 工作表中，然后按下列步骤操作: 第 1 步：选择【工具】下拉菜单 第 2 步：选择【数据分析】选项 第 3 步：在分析工具中选择【描述统计】 ，然后选择【确定】 第 4 步：当对话框出现时，在【输入区域】方框内键入数据区域、在【输 出选项】中选择输出区域、选择【汇总统计】、选择【确定】 4. Excel 在抽样推断中的应用 4.1 简单随机抽样(用 Excel 对分类数据随机抽样) (以 30 个学生为例): 第 1 步：将 30 个学生的名单录入到 Excel 工作表中的一列 第 2 步：给每个学生一个数字代码，分别为 1，2…，30，顺序排列，将代 码录入到 Excel 工作表中的一列，与学生名单相对应 第 3 步：选择【工具】下拉菜单，并选择【数据分析】选项，然后在【数 据分析】选项中选择【抽样】 第 4 步：在【抽样】对话框中的【输入区域】中输入学生代码区域，在【抽 样方法】中单击【随机】 。在【样本数】中输入需要抽样的学生个 数。在【输出区域】中选择抽样结果放置的区域。 【确定】后即得到 要抽取的样本 简单随机抽样(用 Excel 对数值型数据随机抽样) 第 1 步：将原始数据录入到 Excel 工作表中的一列 第 2 步：选择【工具】下拉菜单，并选择【数据分析】选项 ，然后在【数 据分析】选项中选择【抽样】 第 3 步：在【抽样】对话框中的【输入区域】中输入原始数据区域，在【抽 样方法】中单击【随机】。在【样本数】中输入需要抽样的数据个数。 在【输出区域】中选择抽样结果放置的区域。【确定】后即得到要抽 取的样本数据 4.2 总体均值区间估计 设: 是总体 X 的一个样本，X~N(μ，σ )，求总体均值μ的置信区间。 2 1．正态总体、方差σ 已知，或非正态总体、大样本，求μ的置信区间 2 ⎧ 构造总体均值μ的置信区间为： ⎨ x − zα ⎩ 2 σ n , x + zα 2 σ ⎫ ⎬ n⎭ 2．正态总体、方差σ 未知、小样本，求μ的置信区间 2 ⎧ 构造均值μ的置信区间为： ⎨ x − t α ⎩ 例 2 s s ⎫ , x + tα ⎬ n n⎭ 2 从某班男生中随机抽取 10 名学生，测得其身高（cm）分别为 170、 175、172、168、165、178、180、176、177、164，以 95%的置信度估计 本班男生的平均身高。 在 95%的置信度下，本班男生身高的置信区间为（168.5063658， 176.4936342） 。计算结果如下图所示 总体均值置信区间的计算 4.2 总体比例区间估计 样本比例抽样分布的数量特征如下： μ p = π i 样本比例抽样分布的标准差为 σ p = π (1 − π ) n 标准正态分布，确定围绕 π 值的置信区间是： ⎧ ⎨ p − zα 2 ⎩ p(1 − p ) , p + zα n 2 p(1 − p ) ⎫ ⎬ n ⎭ 5. 用 Excel 进行相关与回归分析 5.1 相关分析 1.相关分析要解决的问题 变量之间是否存在关系？ 如果存在关系，它们之间是什么样的关系？ 变量之间的关系强度如何？ 样本所反映的变量之间的关系能否代表总体变量之间的关 系？ 2.为解决这些问题，在进行相关分析时，对总体有以下两个主要假定 两个变量之间是线性关系 两个变量都是随机变量 3. 相关系数 (计算公式) 样本相关系数的计算公式 r = 或化简为 r = ∑ ( x − x )( y − y ) ∑ (x − x ) ⋅ ∑ ( y − y) 2 2 n∑ xy − ∑ x ∑ y n∑ x 2 − (∑ x ) ⋅ n∑ y 2 − (∑ y ) 2 2 5.2 一元线性回归 1. 涉及一个自变量的回归 2. 因变量 y 与自变量 x 之间为线性关系 被预测或被解释的变量称为因变量(dependent variable)，用 y 表示 用来预测或用来解释因变量的一个或多个变量称为自变量 (independent variable)，用 x 表示 3. 因变量与自变量之间的关系用一个线性方程来表示 5.2.1 一元线性回归模型 (基本假定) 1. 因变量 y 与自变量 x 之间具有线性关系 2. 在重复抽样中，自变量 x 的取值是固定的，即假定 x 是非随机的 3. 误差项ε是一个期望值为 0 的随机变量，即 E(ε)=0。对于一个给 定的 x 值，y 的期望值为 E ( y ) = β 0 + β 1 x 4. 对于所有的 x 值，ε的方差σ 都相同 2 5. 误差项ε是一个服从正态分布的随机变量，且相互独立。 即ε~N(0 , σ 2 ) 独立性意味着对于一个特定的 x 值，它所对应的ε与其他 x 值所对应的ε不相关 对于一个特定的 x 值，它所对应的 y 值与其他 x 所对应的 y 值也不相关。 5.2.2 回归方程(regression equation) 一元线性回归方程的形式如下: E( y ) = β 0 + β 1 x 方程的图示是一条直线，也称为直线回归方程 β 0 是回归直线在 y 轴上的截距，是当 x=0 时 y 的期望值 β 1 是直线的斜率，称为回归系数，表示当 x 每变动一个单位时，y 的平均变动值 5.2.3 估计的回归方程(estimated regression equation) 1. 总体回归参数 β 0 和 β 1 是未知的，必须利用样本数据去估计 2. 用样本统计量 βˆ0 和 β̂1 代替回归方程中的未知参数 β 0 和 β 1 ，就得到 了估计的回归方程 3. 一元线性回归中估计的回归方程为: yˆ = βˆ0 + βˆ1 x 5.2.4 用 Excel 进行回归分析 第 1 步：选择“工具”下拉菜单 第 2 步：选择【数据分析】选项 第 3 步：在分析工具中选择【回归】 ，选择【确定】 第 4 步：当对话框出现时 ，在【Y 值输入区域】设置框内键入 Y 的数 据区域；在【X 值输入区域】设置框内键入 X 的数据区域； 在【置信度】选项中给出所需的数值；在【输出选项】中选 择输出区域；在【残差】分析选项中选择所需的选项。 5.3 相关与回归的显著性检验 线性关系的检验 (检验的步骤) 1.提出假设: H 0 ： β 1 =0 线性关系不显著 2.计算检验统计量 F F= SSR 1 MSR = ~ F (1 , n − 2) SSE n − 2 MSE 3.确定显著性水平 α，并根据分子自由度 1 和分母自由度 n-2 找出临界 值Fα 4.作出决策：若 F>F α,拒绝 H 0 ；若 F