EXCEL第101章数理统计及应用

1、EXCEL第101章数理统计及应用第10章 数理统计及应用本章内容：通过实例，介绍如何使用Excel 2007统计函数和数据分析工具进展数据统计分析与预测。包括描绘性统计、假设检验、方差分析和回归分析等教学目的：掌握利用Excel函数和数据分析工具进展统计分析与预测的根本方法 数理统计是一门对客观不确定现象进展数据搜集、整理和分析的科学其目的是理解客观情况，探究数据内在构造及现象之间的规律性 Excel 2007 可以支持范围广泛的统计计算任务，提供工程和科学统计的根本才能。其中包括：函数数据分析工具第1节 数理统计的根本概念第1节 数理统计的根本概念按功能划分统计函数的种类包括：1频数分布处

2、理2描绘统计量计算3概率计算4参数估计5假设检验6卡方检验7相关、回归分析第1节 数理统计的根本概念按功能划分统计函数的种类包括：1频数分布处理2描绘统计量计算3概率计算4参数估计5假设检验6卡方检验7相关、回归分析第1节 数理统计的根本概念数据分析工具的种类：1统计绘图、制表2描绘统计量计算3参数估计4假设检验5方差分析6相关、回归分析7时间序列分析8抽样9数据变换第1节 数理统计的根本概念使用数据分析工具，需加载：单击“Office按钮,然后单击“Excel选项 单击“加载项,然后在“管理框中,选择“Excel 加载宏单击“转到在“可用加载宏框中,选中“分析工具库复选框，然后单击“确定确定

3、后“数据选项卡中增加了“数据分析子项 在进展数据分析的时候，一般首先是从理解数据的根本特征开场的，即先对数据进展描绘性统计分析Descriptive Analysis，以发现其内在的规律，然后再选择进一步分析的方法。描绘性统计分析主要包括两类：一类是数据集中趋势分析，表示数量的中心位置；另一类是数据的离散程度分析，表示数量的变异程度或称离散程度。两者互相补充，共同反映数据的全貌。 第2节 描绘性统计 数据的集中趋势分析是用来反映数据的一般程度，常用的指标有平均值、中位数和众数等： 作用： 指出一数据资料内变量的中心位置，标志着资料所代表性状的数量程度和质量程度； 作为样本或资料的代表数与其它资

4、料进展比较2.1 数据集中趋势分析平均数 算术平均数(average) 调和平均数(harmean) 几何平均数(geomean)2.1 数据集中趋势分析几何平均可用于平均比率的计算,这里变化比率是在相等长度的时间区间内给出的。假设样本观测值包含某种倒数,那么可用调和平均,特别地,调和平均可用于以不同的速度通过各路段,求总的平均速度;或在各种条件下,求不同密度的流体的平均密度的计算。 调和平均数 某人从C到B的平均速度为30km/h,沿同一道路返回时平均速度为60km/h,求整个往返行程的平均速度为多少? 用Excel调和平均数求解： “=HARMEAN(30,60) 40 即40km/h。2

5、.1 数据集中趋势分析 几何平均数 一雇员在连续三年内的年薪增长率分别为6%、10%和12%,这里增加的百分数是在上一年的薪金根底上计算的,求其三年内的年薪平均增长率? 用Excel几何平均数求解: “=GEOMEAN(1.06,1.1,1.12) %。2.1 数据集中趋势分析中位数(MEDIAN) 2.1 数据集中趋势分析 中位数是样本的50分位点，它受极端数值的干扰很小。假设参加一个很大的干扰值，中值数仅可能有很小的挪动。 几何均值和调和均值同算术平均值一样，对极端数值也较敏感。它们主要用于数据为对数正态分布或偏斜程度较大时。 下例显示上述几种统计量对极端数值的敏感程度 A1:A7= 1

6、1 1 1 1 1 100 =geomean(A1:A7) 1.9307 =median(A1:A7) 1 2.1 数据集中趋势分析几何均值调和均值算术平均值中位数 数据的离散程度分析主要是用来反映数据之间的差异程度，常用的指标有极差、方差、标准差、四分位数间距等 作用： 描绘变量分布的差异程度 衡量和比较均值指标的代表性上下 为抽选样本单位数提供根据2.2 数据离散程度分析 极差(max-min)：是描绘数据分散程度最简单的度量，但假设数据中出现了极端数据，极端数据有可能就是最大值或最小值，因此极差对极端数据非常敏感。 标准差(stdev)和方差(var)是常用的分散程度度量。它们对于正态分

7、布的样本是最优的。但它们对极端数据也是较敏感的。偏离数据整体的一个极端数据可能会使它们的值急剧增大。2.2 数据离散程度分析 四分位数(quartile), 四分位数不受两端个别极大值或极小值的影响，根本能反映数据的离散程度。2.2 数据离散程度分析语法 : quartile(array,quart) array：为需要求得四分位数值的数组区域 quart：决定返回哪一个四分位值。 下例显示上述几种统计量对极端数值的敏感程度 A1:A7= 1 1 1 1 1 1 100 =max(A1:A7)-min(A1:A7) 99 =stdev(A1:A7 =quartile(A1:A7 ,1) 17.

8、2 描绘性统计极差标准差四分位间距2.2 数据离散程度分析 设有甲乙两人，对同一名患者采耳垂血，检查红细胞数万/mm3，每人数五个计数盘，得结果为： 甲 480 490 500 510 520 乙 440 460 500 540 560 假设让你就评价两人的检验技术的好坏，你如何评价？2.2 数据离散程度分析 设有甲乙两人，对同一名患者采耳垂血，检查红细胞数万/mm3，每人数五个计数盘，得结果为： 甲 480 490 500 510 520 乙 440 460 500 540 560 两人计数的均数都是500，能说两人的检验技术一样吗？不能，因为甲的计数结果比较密集，而乙的分散，因此甲的检验精

9、度显然比乙的高。 可以用极差来衡量： 甲计数的极差为520480=40， 乙的为560-440=120。可见乙的计数较甲的波动大。2.2 数据离散程度分析 设有甲乙两人，对同一名患者采耳垂血，检查红细胞数万/mm3，每人数五个计数盘，得结果为： 甲 480 490 500 510 520 乙 440 460 500 540 560 甲乙甲乙480440490460平均500平均500500500标准误差7.071标准误差22.8510540中位数500中位数500520560标准差15.81标准差50.99方差250方差2600第3节 假设检验 假设检验是统计推断的根本问题之一，主要是确定关于

10、样本总体特征的判断是否合理的过程。 先对总体的参数(或分布形式)提出某种假设，然后利用样本信息判断假设是否成立的过程。 假设检验的根本思想和原理 假设检验的步骤如何利用Excel进展总体均值的检验总体抽取随机样本均值 x = 20我认为人口的平均年龄是50岁 提出假设 拒绝假设 别无选择! 作出决策 先对总体参数的详细数值作陈述，然后利用样本信息判断假设是否成立首先对几个必要的名词作简要的解释： 零假设：即初始判断 H0：=0 =50 备选假设(也称对立假设)： H1：0或0 或50 或50 假设检验是在H0 成立的前提下，从样本数据中寻找证据来回绝H0 ，“承受 H1 .假设证据缺乏，那么只

11、能不回绝H0，暂且认为 H0正确第3节 假设检验假设双侧检验单侧检验左侧检验右侧检验原假设H0 : m = m0H0 : m m0H0 : m m0备择假设H1 : m m0H1 : m m0提出假设(例题分析)提出假设(例题分析)首先对几个必要的名词作简要的解释： 统计量： 统计理论中用来对数据进展分析、检验的变量。根据样本观测结果计算得到的，并据以对零假设和备择假设作出决策的某个样本统计量 根据不同的推断要求，可以构造不同的统计量第3节 假设检验显著性程度:在进展假设检验时应该事先规定一个小概率的标准，作为判断的界限，这个小概率标准称为显著性程度是一个概率值原假设为真时，回绝原假设的概率被

12、称为抽样分布的回绝域表示为常用的， 由研究者事先确定第3节 假设检验P值: P值是在H0成立前提下，比样本统计量更极端的概率 假设P，那么回绝H0，承受H1 假设P，回绝H0的样本证据缺乏，就不回绝H0，暂且认为H0成立 根据统计推断结果，结合相应的专业知识，给出一个专业的结论。 第3节 假设检验 1 假设检验的步骤： (1) 提出统计假设，零假设H0和备选假设H1 (2) 规定显著程度 (3) 选取样本统计量 (4) 在显著程度下，算出统计量服从分布的临界值，确定假设参数的回绝域第3节 假设检验决策规那么给定显著性程度，计算得出相应的临界值将检验统计量的值与 程度的临界值进展比较作出决策双侧

13、检验： I统计量I 临界值，回绝H0左侧检验：统计量 临界值，回绝H0 2 总体均值的检验： 单个样本的假设检验(大样本、小样本) (统计函数) 成对观测值的假设检验 (数据分析工具) 第3节 假设检验 2 总体均值的检验： 单个样本的总体均值检验 第3节 假设检验 对单个样本的均值检验, 我们可以根据抽样推断的思路, 用相应函数计算临界值, 来判断是承受还是回绝零假设。 总体均值的检验： (小样本，n 30) ，t 检验 计算t 统计量: 用函数计算临界值(双侧)： TINV(,n-1) 总体均值的检验 (例题分析)【例】一种汽车配件的平均长度要求为12cm，高于或低于该标准均被认为是不合格

14、的。汽车消费企业在购进配件时，通常是经过招标，然后对中标的配件提供商提供的样品进展检验，以决定是否购进。现对一个配件提供商提供的10个样本进展了检验。假定该供货商消费的配件长度服从正态分布，在的显著性程度下，检验该供货商提供的配件是否符合要求？ 总体均值的检验 (例题分析) 2 总体均值的检验： 单个样本的总体均值检验 第3节 假设检验 对单个样本的总体均值检验, 我们可以根据抽样推断的思路, 用相应函数计算临界值, 来判断是承受还是回绝零假设。 总体均值的检验： (大样本，n 30) ，z检验 计算z 统计量: 用函数计算临界值(单侧) ：NORMSINV() 总体均值的检验 (例题分析)【

15、例】一种罐装饮料采用自动消费线消费，每罐的容量是255ml，标准差为5ml。为检验每罐容量是否符合要求，质检人员在某天消费的饮料中随机抽取了40罐进展检验，测得每罐平均容量为。取显著性程度=0.05 ，检验该天消费的饮料容量是否符合标准要求？双侧检验绿色健康饮品绿色健康饮品255255总体均值的检验 (例题分析)【例】一种机床加工的零件尺寸绝对平均误差为。消费厂家现采用一种新的机床进展加工以期进一步降低误差。为检验新机床加工的零件平均误差与旧机床相比是否有显著降低，从某天消费的零件中随机抽取50个进展检验。利用这些样本数据，检验新机床加工的零件尺寸的平均误差与旧机床相比是否有显著降低？ (=0

16、.01) 左侧检验总体均值的检验 (例题分析)总体均值的检验 (例题分析)【例】某一小麦品种的平均产量为5200kg/hm2 。一家研究机构对小麦品种进展了改进以期进步产量。为检验改进后的新品种产量是否有显著进步，随机抽取了36个地块进展试种，得到的样本平均产量为5275kg/hm2，标准差为120/hm2 。试检验改进后的新品种产量是否有显著进步？ (=0.05) 右侧检验总体均值的检验 (例题分析)总体均值的检验 (例题分析) 2 总体均值的检验： 成对观测值的假设检验 第3节 假设检验 均值假设检验： t 检验(平均值的成对二样本分析 双样本等方差、双样本异方差) Z 检验(方差) 方差

17、检验： F检验 第3节 假设检验 t 检验(平均值的成对二样本分析 用以对两组成对数据进展均值是否相等的检验。 实际工作中往往会遇到均值的成比照较, 如采用新教育方法前后学生的成绩、进展体育疗法前后患者的血压等。 当样本中的观察值存在配对关系时，可以使用“成对双样本t-检验。 第3节 假设检验 t 检验(平均值的成对二样本分析H0: D = 0 (D = 1 - 2) H1: D 0规定显著程度：检验统计量: t 统计量服从t分布假设双侧检验左侧检验右侧检验H0m1 = m2m1 m2m1 m2H1m1 m2m1 m2举例： 第3节 假设检验您在人力资源部工作,您想理解一个培训工程是否有效.您

18、搜集到以下考试成绩的数据:姓名前 (1)后 (2)Sam8594Tamika9487Brian7879Mike8788在 程度,这个培训有效吗?举例： 第3节 假设检验t-检验: 成对双样本均值分析前 (1)后 (2) 平均8687方差43.333333338观测值44泊松相关系数0.47643526假设平均差0df3t Stat-0.3061862P(T=t) 单尾0.389741t 单尾临界1.63774435P(T=t) 双尾0.779482t 双尾临界2.35336343 第3节 假设检验 t 检验(双样本等方差平均值的检验H0: 1 - 2 = 0 (1 = 2) H1: 1 - 2

19、 0 (1 2)规定显著程度：检验统计量: t 统计量服从t分布假设双侧检验左侧检验右侧检验H0m1 = m2m1 m2m1 m2H1m1 m2m1 m2 第3节 假设检验举例： 比较甲,乙两种安眠药的疗效。将20名患者分成两组,每组10人.其中10人服用甲药后延长睡眠的时数分别为1.9, 0.8, 1.1, 0.1, -0.1, 4.4, 5.5, 1.6, 4.6, 3.4;另10人服用乙药后延长睡眠的时数分别为0.7, -1.6, -0.2, -1.2, -0.1, 2.4, 2.7, 0.8, 0.0, 2.0.假设服用两种安眠药后增加的睡眠时数服从方差一样的正态分布.试问两种安眠药的疗效有无显著性差异?(=0.10) 第3节 假设检验举例：2.271.7341 ,回绝H0认为两种安眠药的疗效有显著性差异 第3节 假设