基于Excel的统计分析与预测.pdf
《基于Excel的统计分析与预测.pdf》由会员分享,可在线阅读,更多相关《基于Excel的统计分析与预测.pdf(138页珍藏版)》请在淘文阁 - 分享文档赚钱的网站上搜索。
1、基于Excel的统计分析与预测 北京师范大学管理学院 印德中 2012.6.课程目录课程目录 预备知识预备知识 1 统计方法分类与选择统计方法分类与选择 2 常用统计分析与预测方法常用统计分析与预测方法 3 Excel数据展现数据展现 4 Excel与其它数据软件与其它数据软件 5 习题与演练习题与演练 6 预备知识预备知识 一、安装分析工具库 二、Excel公式与函数 三、统计分析基础知识 1、统计学概述 2、统计数据集中程度的描述 3、统计数据离散程度的描述 四、利用“分析工具库”快速进行描述统计 安装分析工具库安装分析工具库 分析工具库是Excel提供的一组数据分析工具,是应用Excel
2、进行统计分析必备的一组工具。初次安装Excel时并不自动安装分析工具库,需要手动进行加载和安装。首先,打开Excel的“选项”:Excel2007单击“Microsoft Office 按钮”,然后单击“Excel选项”。Excel2010单击“文件”菜单,然后单击“选项”。安装分析工具库安装分析工具库 安装分析工具库安装分析工具库 安装分析工具库安装分析工具库 安装分析工具库安装分析工具库 Excel公式与函数公式与函数 Excel公式(1)输入公式 以等号开头,然后才是公式的表达式(2)复制公式 利用单元格填充柄,或利用单元格复制和粘贴(3)公式中的引用:相对引用、绝对引用(单元格行和列前
3、都加上$、混合引用(单元格行或列之一前加$)、不同工作表间单元格的引用(单元格前加工作表名!)Excel函数 1.Excel包括多种函数,如数据库函数、财务函数、统计函数等。2.函数的4 个要素:等于号、函数名、括号、参数 3.手工输入函数:输入等于号和函数 4.插入函数:自动求和旁边的箭头“其他函数”统计分析概述统计分析概述 统计学概述 统计学是通过搜集、整理、分析数据而推导分析结果的科学方法 统计分析按研究方法可分为两类:描述统计、推断统计 1 描述统计是指利用计算、排序等方法对一大堆杂乱的原始数据整理,使数据容易阅读。2 推断统计是指根据样本数据推测总体数据的性质,并陈述可能发生的误差。
4、统计分析概述统计分析概述 总体总体 统计描述和推断统计描述和推断 样本样本 统计分析概述统计分析概述 统计学的另一种分类:统计学的另一种分类:理论统计学理论统计学 就是数理统计学,主要阐述统计学的数学原理,其理论基础主要是概率论。内容有:概率理论、抽样理论、实验设计、估计理论、假设检验理论、决策理论、非参数统计、序列分析、随机过程等基本方法原理。应用统计学应用统计学 主要是将以上理论应用于各个学科领域。如社会统计学、经济统计学、教育统计学、生物统计学、气象统计学、医学统计学等,着重研究统计方法在该学科的具体应用,阐明这些方法的统计思想,而不是统计方法数学原理的推导与证明。统计数据集中程度的描述
5、统计数据集中程度的描述 统计数据集中程度的描述 集中程度是指一组数据向其中心值靠拢的倾向。测定集中程度就是确定数据一般水平的代表值或者中间值。集中程度通常用以下指标来反映:最常用的集中程度指标是平均数、中位数、众数 (1)平均数)平均数 (2)中位数)中位数 (3)众数)众数 统计数据集中程度的描述统计数据集中程度的描述 平均数 平均数是所有数据相加的和除以这组数据的个数得出的值。平均数考虑到了整个数据组里的所有数据,小组内的每一个数据,无论值高还是值低,都对平均数有影响 Excel提供了均值函数AVERAGE计算算术平均数 数据的个数数据平均数统计数据集中程度的描述统计数据集中程度的描述 中
6、位数 中位数是按顺序排列的一组数据中居于中间位置的数。当数据个数为奇数时,如:18,23,25,27,30,32,37 很简单,上面的数据中居于中间位置的27就是中位数。当数据个数为偶数时,如:18,23,25,26,30,32,37,40 就要先找到位于中间位置的两个数,然后选择它们的中点,上面就是第4个数和第5个数之间,即26和30之间,把这两个数相加再除以2就可以得到中位数28 Excel提供了中位数函数MEDIAN计算中位数 统计数据集中程度的描述统计数据集中程度的描述 众数 众数是总体中出现次数最多的数值。众数只考虑总体中各数值出现频率的多少,不受极端数值的影响,用来说明总体中大多数
7、单位所达到的一般水平。比如某公司对网上购物的人群进行调查,发现其年龄数据如下:21,35,25,26,40,23,26,30,26,52 那么这里面众数就是26 Excel提供了众数函数MODE用于计算众数 统计数据离散程度的描述统计数据离散程度的描述 数据的离散程度是数据分布的另一个重要特征,它是指各变量值远离其中心值的程度,所以也叫离中趋势。离散程度的测定方法有多种,常用的有以下几种:(1)极差)极差 (2)方差)方差 (3)标准差)标准差 统计数据离散程度的描述统计数据离散程度的描述 极差 极差也称全距,是表示一组数据的最大值和最小值的差。极差是描述数据离散程度的最简单的方法,它表明数据
8、的分布范围。极差越小表示数据的分布越集中。Excel中没有专门的极差函数,但可以利用最大值函数和最小值函数之差计算极差,即MAX()-MIN()统计数据离散程度的描述统计数据离散程度的描述 方差 方差是一组数据中各变量值与均值之差的平方的平均数。方差是一个非常重要的统计量,方差值越大,表明数据间的差异就越大。方差的计算公式是:Excel中提供了计算总体方差的函数VARP,计算样本方差的函数VAR。计算总体方差与计算样本方差有所不同,这是因为计算样本方差要将其作为总体方差的估计量,统计上要求估计量要满足一些条件,如一致性、无偏性及有效性。对于下面要讲的标准差也是同样的道理。数据的个数平均数数据方
9、差2)(统计数据离散程度的描述统计数据离散程度的描述 标准差 由于方差是一个平方后的值,所以我们不能将它直接拿来与原始数据进行比较。但是,如果我们将方差进行平方根计算,所得的值就回到了与平均值和原始数据相同的单位上,这个统计量就是标准差。计算标准差的公式是:Excel提供了计算总体标准差函数STDEVP,计算样本标准差的函数STDEV。数据的个数平均数数据标准差2)(利用利用“分析工具分析工具厍厍”快速快速进进行描述行描述统计统计 操作范例:在一次市场调查中,某种药品一周销售额如图所示,现利用统计数据集中程度和离散程度的描述进行基本的统计分析:利用利用“分析工具厍分析工具厍”快速进行描述统计快
10、速进行描述统计 (1)单击“数据”选项卡上“分析”组中的“数据分析”,弹出“数据分析”对话框。利用利用“分析工具厍分析工具厍”快速进行描述统计快速进行描述统计 (2)选择“描述统计”选项,然后单击“确定”按钮,弹出“描述统计”对话框 利用利用“分析工具厍分析工具厍”快速进行描述统计快速进行描述统计 (3)对话框内各选项的含义如下 输入区域:在此输入待分析数据区域的单元格引用。该引用必须由两个或者两个以上按列或按行排列的相邻数据区域组成。分组方式:指出输入区域中的数据是按行还是按列排列 标志位于第一行/列:如果输入区域的第一行或列中包含标志项,则选中此复选框;如果输入区域没有标志项,则不需要选择
11、该复选框,Excel 将在输出表中生成适宜的数据标志。输出区域:在此框中可填写输出结果表左上角单元格地址,用于控制输出结果的存放位置。整个输出结果分为两列,左边一列包含统计标志项,右边一列包含统计值。根据所选择的“分组方式”选项的不同,Excel 将为输入表中的每一行或每一列生成一个两列的统计表。利用利用“分析工具厍分析工具厍”快速进行描述统计快速进行描述统计 新工作表:单击此选项,可在当前工作簿中插入新工作表,并由新工作表的 A1 单元格开始存放计算结果。如果需要给新工作表命名,则在右侧编辑框中键入名称。新工作簿:单击此选项,可创建一新工作簿,并在新工作簿的新工作表中存放计算结果。汇总统计:
12、指定输出表中生成下列统计结果,则选中此复选框。这些统计结果有:平均值、标准误差、中值、众数、标准偏差、方差、峰值、偏斜度、极差(全距)最小值、最大值、总和、样本个数。平均数置信度:如果需要在输出表中包含平均数的置信度,则选中此项,然后在右侧的编辑框中输入所要使用的置信度。例如,若置信度95%时,计算出显著性水平为5%时的平均数置信度。第k大值:如果需要在输出表中包含每个数据区域中的第k个最大值,则选中此项,然后在右侧编辑框中输入k的数值。第k小值:如果需要在输出表中包含每个数据区域中的第k个最小值,则选中此项,然后在右侧编辑框中输入k的数值。利用利用“分析工具厍分析工具厍”快速进行描述统计快速
13、进行描述统计 本例输出结果 利用利用“分析工具分析工具厍厍”快速快速进进行描述行描述统计统计 标准误差 一个100人的大学毕业生样本组在多大程度上代表了所有的大学毕业生。显然,样本组的数量越少,组的平均分和标准差就越有可能不同于真实总体的平均分和标准差。因此,样本和总体之间总是存在误差。标准误差是用于计算样本和总体之间误差程度的统计量。Excel中没有专门计算标准误差的函数,但我们可以通过在单元格中输入“=stdev(数据区域)/sqrt(counta(数据区域)”的公式来计算 样本数量样本标准差标准误差统计方法的分类和选择 统计方法的选择依据:变量的类型、研究设计的类型、分析的目的 每种统计
14、方法都有自己的适用条件 统计方法回顾:统计方法回顾:单因素分析单因素分析 t 检验检验 方差分析方差分析 卡方检验卡方检验 直线回归与相关直线回归与相关 多因素分析多因素分析 多元线性回归多元线性回归 Logistic回归回归 生存分析生存分析 聚类分析聚类分析 判别分析判别分析 .统计方法分类与选择统计方法分类与选择 变量类型 变量值表现 实例 资料类型 数值变量 定量(具体数值)身高(cm)计量资料 分类变量 无序 二分类 对立的两类属性 性别(男,女)计数资料 多分类 不相容的多类属性 血型(A,B,O)有序 多分类 类间有程度差异的属性(又称等级资料)文化程度(初中、高中、大学.)变量
15、的分类 等级资料 统计方法分类与选择统计方法分类与选择 数据类型的相互转换 数据类型可以视研究目的而转换 比如,年龄为计量资料,转换为“未成年、成人”,就是二分类的计数资料,再转换为“婴幼儿、青 年、中年、老年”则为多分类有序的计数资料(等级资料)治疗效果分类“无效、好转、显效、痊愈”为多分类有序的计数资料,分别给予编码0,1,2,3后即便为计量资料了 统计方法分类与选择统计方法分类与选择 不同不同变量类型的数据分析方法选择变量类型的数据分析方法选择 因变量因变量 自变量自变量 数值变量数值变量 分类变量分类变量 有序变量有序变量 数值变量数值变量 相关分析,多元回相关分析,多元回归分析归分析
16、 t t检验检验,方差分析,方差分析,协方差分析,多协方差分析,多元回归分析元回归分析 相关分析,多元回相关分析,多元回归分析归分析 分类变量分类变量 t t检验检验,方差分析,方差分析,logisticlogistic回归分回归分析析,判别分析,判别分析,聚类分析聚类分析 c c2 2检验,检验,logisticlogistic回回归分析归分析 c c2 2检验检验 有序变量有序变量 方差分析,方差分析,logisticlogistic回归分回归分析析,判别分析,判别分析,聚类分析聚类分析 c c2 2检验,检验,logisticlogistic回回归分析归分析 相关分析,相关分析,c c2
17、 2检验检验(自变量是被操纵的变量,而因变量是被测定或被记录的变量)综合研究设计类型和综合研究设计类型和数据类型数据类型的统计分析的统计分析方法选择方法选择 研究设计类型研究设计类型 变量类型变量类型 两组比较两组比较 两组以上比两组以上比较较 实 验前后 比实 验前后 比较较 重复测量重复测量 两变量间的联系两变量间的联系 有重复测量有重复测量的方差的方差分析分析 线性回归线性回归,Pearson相关系相关系数数 数值变量数值变量 t检验检验 方差分析方差分析 配对配对t检验检验 分类变量分类变量 c c2 2检验检验 c c2 2检验检验 配对配对c c2 2检验检验 列联表相关系数列联表
18、相关系数 有序变量有序变量 Mann-Whitney秩和检秩和检验验 Kruskal-Wallis分析分析 Wilcoxon 符符号秩和检号秩和检验验 Spearman 相 关相 关系数系数 各种情形下最常用的统计方法索引 应变量应变量 自变量自变量 统计方法选择统计方法选择 数值数值 单个单个 二分类二分类 T检验检验 多分类多分类 单因素方差分析单因素方差分析 数值数值 相关分析相关分析,回归分析回归分析 多个多个 分类变量为主分类变量为主 方差分析模型方差分析模型 数值变量为主数值变量为主 线性回归模型线性回归模型 应变量应变量 自变量自变量 统计方法选择统计方法选择 有序分类有序分类
19、单个单个 二分类二分类 秩和检验秩和检验 多分类多分类 秩和检验秩和检验 数值数值 Logistic回归分析回归分析 多个多个 分类变量为主分类变量为主 Logistic回归分析回归分析 数值变量为主数值变量为主 判别分析判别分析、Logistic回归分析回归分析 应变量应变量 自变量自变量 统计方法选择统计方法选择 无序分类无序分类(含二分类含二分类)单个单个 二分类二分类 卡方检验卡方检验 多分类多分类 卡方检验卡方检验 数值数值 Logistic回归分析回归分析 多个多个 分类变量为主分类变量为主 Logistic回归分析回归分析、最优尺度分析最优尺度分析 数值变量为主数值变量为主 判别
20、分析判别分析、Logistic回归分析回归分析 希望将变量/纪录分成若干个类别,但类别数不清楚,或各类别的特征不明:聚类分析聚类分析 希望建立判别方程,对以后新进入的案例进行所属类别的预测:判别分析判别分析 需要探索多个变量间的内在联系或内在结构:因子分析、对应分析因子分析、对应分析 生存时间和生存结局都是需要关心的因素,同时数据中存在大量的失访:生存分析生存分析 得到的是时间序列数据,需要对以后的情形加以预测:时间序列分析与预时间序列分析与预测测 考察的特征需要有多个应变量来表示,同时研究多个自变量对他们的影响:多元方差分析多元方差分析 方差分析方差分析 回回归归分析分析 相相关关分析分析
21、假假设检验设检验 常用统计分析与预测方法常用统计分析与预测方法 假设检验假设检验 方差分析方差分析 回归分析回归分析 相关分析相关分析 时间序列分析与预测时间序列分析与预测 假设检验假设检验 假设检验分析的概念 为了推断总体分布函数中的未知参数或总体分布函数,采用先提出假设的方法,然后根据总体的一个样本的值对所提出的假设进行检验,并决定或拒绝所提出的假设,这样的工作称为假设检验。为方便起见,根据研究目的提出的假设称为原假设;其对立面假设称为备择假设 假设检验的步骤(1)构造原假设及备择假设。(2)选择检验统计量及其分布。(3)确定显著性水平。(4)确定决策规则。(5)判断决策,确定是否拒绝原假
22、设。假设检验假设检验 Excel在假设检验方面提供的功能:(1)成对观测值t检验(取同一样本观测前后的观测值)(2)方差相等的双样本t检验(假设总体方差相等的两个样本)(3)方差不等的双样本t检验(假设总体方差不等的两个样本)(4)z检验(两总体均服从正态分布,检验这两总体的平均数是否存在差异)(5)F检验(两总体均服从正态分布,检验这两总体的方差是否存在差异)(6)卡方检验(用来验证两个总体间某个比率之间是否存在显著性差异。卡方检验属于非参数假设检验,适用于布尔型数据或二项分布数据,早期用于生产企业的产品合格率等)(7)信赖区间 假设检验假设检验 Excel中进行成对观测值t检验范例 有10
23、个高血压患者服用A降压药和B降压药,其血压下降的具体数据如图所示:假设检验假设检验 本例要求对两种降压药的疗效有无显著性差异做出判断。本例是对一个总体中抽取的两个样本进行检验,适合使用成对样本均值的“t检验”原假设:两种降压药疗效相同 备择假设:两种降压药疗效不同 本例用“t检验”工具计算,步骤如下:(1)在“数据”选项卡的“分析”组中,单击“数据分析”,弹出“数据分析”对话框,从对话框的“分析工具”列表中选择“t检验:平均值的成对二样本分析”假设检验假设检验 (2)单击“确定”按钮进入t检验:平均值的成对二样本分析”对话框,在对话框中填入相应数据或选项 假设检验假设检验(3)单击“确定”,结
24、果如图所示:假设检验假设检验 说明:t Stat是t检验统计量,可以用它与t临界值进行比较,从而判断原假设是否成立。t统计量绝对值大于t临界值,单侧P值比显著性水平0.05要小,因此拒绝原假设。该结论说明两种降压药的疗效不相同。方差分析方差分析 方差分析概述 方差分析由英国统计学家R.A.Fisher首先提出,以F命名其统计量,故方差分析又称F检验。两个概念:随机因素和可控因素 受不同因素的影响,研究得到的数据会不同。造成结果差异的原因可分成两类:一类是不可控的随机因素的影响,这是人为很难控制的一类影响因素,称为随机因素;另一类是研究中人为施加的可以控制的因素对结果的影响,称为可控因素。例如:
25、在影响教学效果的因素中,有两类因素:一是人为可控制的因素,如教学的方法、教材的使用;还有一类是随机的因素,如学生接受知识的能力(在不按能力水平分班的情况下)。前者就称为可控因素,后者称为随机因素。可以对两个普通班级分别使用两种不同的教学方法或不同的教材,一段时间后进行测试,就可以得到不同教学方法或不同教材对教学效果的影响。方差分析就是实现上述功能的分析方法。方差分析的基本思想是:通过分析研究中不同因素的变异对总变异的贡献大小,从而确定可控因素对研究结果影响力的大小。通过方差分析,分析不同水平的可控因素是否对结果产生了显著影响。如果可控因素的不同水平对结果产生了显著影响,那么它和随机因素共同作用
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 基于 Excel 统计分析 预测
限制150内