基于Excel的统计分析与预测.pdf

基于Excel的统计分析与预测 北京师范大学管理学院 印德中 2012.6.课程目录课程目录 预备知识预备知识 1 统计方法分类与选择统计方法分类与选择 2 常用统计分析与预测方法常用统计分析与预测方法 3 Excel数据展现数据展现 4 Excel与其它数据软件与其它数据软件 5 习题与演练习题与演练 6 预备知识预备知识 一、安装分析工具库 二、Excel公式与函数 三、统计分析基础知识 1、统计学概述 2、统计数据集中程度的描述 3、统计数据离散程度的描述 四、利用“分析工具库”快速进行描述统计 安装分析工具库安装分析工具库 分析工具库是Excel提供的一组数据分析工具，是应用Excel进行统计分析必备的一组工具。初次安装Excel时并不自动安装分析工具库，需要手动进行加载和安装。首先，打开Excel的“选项”：Excel2007单击“Microsoft Office 按钮”，然后单击“Excel选项”。Excel2010单击“文件”菜单，然后单击“选项”。安装分析工具库安装分析工具库 安装分析工具库安装分析工具库 安装分析工具库安装分析工具库 安装分析工具库安装分析工具库 Excel公式与函数公式与函数 Excel公式（1）输入公式 以等号开头，然后才是公式的表达式（2）复制公式 利用单元格填充柄，或利用单元格复制和粘贴（3）公式中的引用：相对引用、绝对引用（单元格行和列前都加上$、混合引用（单元格行或列之一前加$）、不同工作表间单元格的引用（单元格前加工作表名！）Excel函数 1.Excel包括多种函数，如数据库函数、财务函数、统计函数等。2.函数的4 个要素：等于号、函数名、括号、参数 3.手工输入函数：输入等于号和函数 4.插入函数：自动求和旁边的箭头“其他函数”统计分析概述统计分析概述 统计学概述 统计学是通过搜集、整理、分析数据而推导分析结果的科学方法 统计分析按研究方法可分为两类：描述统计、推断统计 1 描述统计是指利用计算、排序等方法对一大堆杂乱的原始数据整理，使数据容易阅读。2 推断统计是指根据样本数据推测总体数据的性质，并陈述可能发生的误差。统计分析概述统计分析概述 总体总体 统计描述和推断统计描述和推断 样本样本 统计分析概述统计分析概述 统计学的另一种分类：统计学的另一种分类：理论统计学理论统计学 就是数理统计学，主要阐述统计学的数学原理，其理论基础主要是概率论。内容有：概率理论、抽样理论、实验设计、估计理论、假设检验理论、决策理论、非参数统计、序列分析、随机过程等基本方法原理。应用统计学应用统计学 主要是将以上理论应用于各个学科领域。如社会统计学、经济统计学、教育统计学、生物统计学、气象统计学、医学统计学等，着重研究统计方法在该学科的具体应用，阐明这些方法的统计思想，而不是统计方法数学原理的推导与证明。统计数据集中程度的描述统计数据集中程度的描述 统计数据集中程度的描述 集中程度是指一组数据向其中心值靠拢的倾向。测定集中程度就是确定数据一般水平的代表值或者中间值。集中程度通常用以下指标来反映：最常用的集中程度指标是平均数、中位数、众数 （1）平均数）平均数 （2）中位数）中位数 （3）众数）众数 统计数据集中程度的描述统计数据集中程度的描述 平均数 平均数是所有数据相加的和除以这组数据的个数得出的值。平均数考虑到了整个数据组里的所有数据，小组内的每一个数据，无论值高还是值低，都对平均数有影响 Excel提供了均值函数AVERAGE计算算术平均数 数据的个数数据平均数统计数据集中程度的描述统计数据集中程度的描述 中位数 中位数是按顺序排列的一组数据中居于中间位置的数。当数据个数为奇数时，如：18，23，25，27，30，32，37 很简单，上面的数据中居于中间位置的27就是中位数。当数据个数为偶数时，如：18，23，25，26，30，32，37，40 就要先找到位于中间位置的两个数，然后选择它们的中点，上面就是第4个数和第5个数之间，即26和30之间，把这两个数相加再除以2就可以得到中位数28 Excel提供了中位数函数MEDIAN计算中位数 统计数据集中程度的描述统计数据集中程度的描述 众数 众数是总体中出现次数最多的数值。众数只考虑总体中各数值出现频率的多少，不受极端数值的影响，用来说明总体中大多数单位所达到的一般水平。比如某公司对网上购物的人群进行调查，发现其年龄数据如下：21，35，25，26，40，23，26，30，26，52 那么这里面众数就是26 Excel提供了众数函数MODE用于计算众数 统计数据离散程度的描述统计数据离散程度的描述 数据的离散程度是数据分布的另一个重要特征，它是指各变量值远离其中心值的程度，所以也叫离中趋势。离散程度的测定方法有多种，常用的有以下几种：（1）极差）极差 （2）方差）方差 （3）标准差）标准差 统计数据离散程度的描述统计数据离散程度的描述 极差 极差也称全距，是表示一组数据的最大值和最小值的差。极差是描述数据离散程度的最简单的方法，它表明数据的分布范围。极差越小表示数据的分布越集中。Excel中没有专门的极差函数，但可以利用最大值函数和最小值函数之差计算极差，即MAX()-MIN()统计数据离散程度的描述统计数据离散程度的描述 方差 方差是一组数据中各变量值与均值之差的平方的平均数。方差是一个非常重要的统计量，方差值越大，表明数据间的差异就越大。方差的计算公式是：Excel中提供了计算总体方差的函数VARP，计算样本方差的函数VAR。计算总体方差与计算样本方差有所不同，这是因为计算样本方差要将其作为总体方差的估计量，统计上要求估计量要满足一些条件，如一致性、无偏性及有效性。对于下面要讲的标准差也是同样的道理。数据的个数平均数数据方差2)(统计数据离散程度的描述统计数据离散程度的描述 标准差 由于方差是一个平方后的值，所以我们不能将它直接拿来与原始数据进行比较。但是，如果我们将方差进行平方根计算，所得的值就回到了与平均值和原始数据相同的单位上，这个统计量就是标准差。计算标准差的公式是：Excel提供了计算总体标准差函数STDEVP，计算样本标准差的函数STDEV。数据的个数平均数数据标准差2)(利用利用“分析工具分析工具厍厍”快速快速进进行描述行描述统计统计 操作范例：在一次市场调查中，某种药品一周销售额如图所示，现利用统计数据集中程度和离散程度的描述进行基本的统计分析：利用利用“分析工具厍分析工具厍”快速进行描述统计快速进行描述统计 (1)单击“数据”选项卡上“分析”组中的“数据分析”，弹出“数据分析”对话框。利用利用“分析工具厍分析工具厍”快速进行描述统计快速进行描述统计 (2)选择“描述统计”选项，然后单击“确定”按钮，弹出“描述统计”对话框 利用利用“分析工具厍分析工具厍”快速进行描述统计快速进行描述统计 (3)对话框内各选项的含义如下 输入区域：在此输入待分析数据区域的单元格引用。该引用必须由两个或者两个以上按列或按行排列的相邻数据区域组成。分组方式：指出输入区域中的数据是按行还是按列排列 标志位于第一行/列：如果输入区域的第一行或列中包含标志项，则选中此复选框；如果输入区域没有标志项，则不需要选择该复选框，Excel 将在输出表中生成适宜的数据标志。输出区域：在此框中可填写输出结果表左上角单元格地址，用于控制输出结果的存放位置。整个输出结果分为两列，左边一列包含统计标志项，右边一列包含统计值。根据所选择的“分组方式”选项的不同，Excel 将为输入表中的每一行或每一列生成一个两列的统计表。利用利用“分析工具厍分析工具厍”快速进行描述统计快速进行描述统计 新工作表：单击此选项，可在当前工作簿中插入新工作表，并由新工作表的 A1 单元格开始存放计算结果。如果需要给新工作表命名，则在右侧编辑框中键入名称。新工作簿：单击此选项，可创建一新工作簿，并在新工作簿的新工作表中存放计算结果。汇总统计：指定输出表中生成下列统计结果，则选中此复选框。这些统计结果有：平均值、标准误差、中值、众数、标准偏差、方差、峰值、偏斜度、极差（全距）最小值、最大值、总和、样本个数。平均数置信度：如果需要在输出表中包含平均数的置信度，则选中此项，然后在右侧的编辑框中输入所要使用的置信度。例如，若置信度95%时，计算出显著性水平为5%时的平均数置信度。第k大值：如果需要在输出表中包含每个数据区域中的第k个最大值，则选中此项，然后在右侧编辑框中输入k的数值。第k小值：如果需要在输出表中包含每个数据区域中的第k个最小值，则选中此项，然后在右侧编辑框中输入k的数值。利用利用“分析工具厍分析工具厍”快速进行描述统计快速进行描述统计 本例输出结果 利用利用“分析工具分析工具厍厍”快速快速进进行描述行描述统计统计 标准误差 一个100人的大学毕业生样本组在多大程度上代表了所有的大学毕业生。显然，样本组的数量越少，组的平均分和标准差就越有可能不同于真实总体的平均分和标准差。因此，样本和总体之间总是存在误差。标准误差是用于计算样本和总体之间误差程度的统计量。Excel中没有专门计算标准误差的函数，但我们可以通过在单元格中输入“=stdev(数据区域)/sqrt(counta(数据区域)”的公式来计算 样本数量样本标准差标准误差统计方法的分类和选择 统计方法的选择依据：变量的类型、研究设计的类型、分析的目的 每种统计方法都有自己的适用条件 统计方法回顾：统计方法回顾：单因素分析单因素分析 t 检验检验 方差分析方差分析 卡方检验卡方检验 直线回归与相关直线回归与相关 多因素分析多因素分析 多元线性回归多元线性回归 Logistic回归回归 生存分析生存分析 聚类分析聚类分析 判别分析判别分析 .统计方法分类与选择统计方法分类与选择 变量类型 变量值表现 实例 资料类型 数值变量 定量（具体数值）身高（cm）计量资料 分类变量 无序 二分类 对立的两类属性 性别（男，女）计数资料 多分类 不相容的多类属性 血型（A,B,O）有序 多分类 类间有程度差异的属性（又称等级资料）文化程度（初中、高中、大学.）变量的分类 等级资料 统计方法分类与选择统计方法分类与选择 数据类型的相互转换 数据类型可以视研究目的而转换 比如，年龄为计量资料，转换为“未成年、成人”，就是二分类的计数资料，再转换为“婴幼儿、青 年、中年、老年”则为多分类有序的计数资料（等级资料）治疗效果分类“无效、好转、显效、痊愈”为多分类有序的计数资料，分别给予编码0,1,2,3后即便为计量资料了 统计方法分类与选择统计方法分类与选择 不同不同变量类型的数据分析方法选择变量类型的数据分析方法选择 因变量因变量 自变量自变量 数值变量数值变量 分类变量分类变量 有序变量有序变量 数值变量数值变量 相关分析，多元回相关分析，多元回归分析归分析 t t检验检验,方差分析，方差分析，协方差分析，多协方差分析，多元回归分析元回归分析 相关分析，多元回相关分析，多元回归分析归分析 分类变量分类变量 t t检验检验,方差分析，方差分析，logisticlogistic回归分回归分析析,判别分析，判别分析，聚类分析聚类分析 c c2 2检验，检验，logisticlogistic回回归分析归分析 c c2 2检验检验 有序变量有序变量 方差分析，方差分析，logisticlogistic回归分回归分析析,判别分析，判别分析，聚类分析聚类分析 c c2 2检验，检验，logisticlogistic回回归分析归分析 相关分析，相关分析，c c2 2检验检验（自变量是被操纵的变量，而因变量是被测定或被记录的变量）综合研究设计类型和综合研究设计类型和数据类型数据类型的统计分析的统计分析方法选择方法选择 研究设计类型研究设计类型 变量类型变量类型 两组比较两组比较 两组以上比两组以上比较较 实 验前后 比实 验前后 比较较 重复测量重复测量 两变量间的联系两变量间的联系 有重复测量有重复测量的方差的方差分析分析 线性回归线性回归，Pearson相关系相关系数数 数值变量数值变量 t检验检验 方差分析方差分析 配对配对t检验检验 分类变量分类变量 c c2 2检验检验 c c2 2检验检验 配对配对c c2 2检验检验 列联表相关系数列联表相关系数 有序变量有序变量 Mann-Whitney秩和检秩和检验验 Kruskal-Wallis分析分析 Wilcoxon 符符号秩和检号秩和检验验 Spearman 相 关相 关系数系数 各种情形下最常用的统计方法索引 应变量应变量 自变量自变量 统计方法选择统计方法选择 数值数值 单个单个 二分类二分类 T检验检验 多分类多分类 单因素方差分析单因素方差分析 数值数值 相关分析相关分析，回归分析回归分析 多个多个 分类变量为主分类变量为主 方差分析模型方差分析模型 数值变量为主数值变量为主 线性回归模型线性回归模型 应变量应变量 自变量自变量 统计方法选择统计方法选择 有序分类有序分类 单个单个 二分类二分类 秩和检验秩和检验 多分类多分类 秩和检验秩和检验 数值数值 Logistic回归分析回归分析 多个多个 分类变量为主分类变量为主 Logistic回归分析回归分析 数值变量为主数值变量为主 判别分析判别分析、Logistic回归分析回归分析 应变量应变量 自变量自变量 统计方法选择统计方法选择 无序分类无序分类（含二分类含二分类）单个单个 二分类二分类 卡方检验卡方检验 多分类多分类 卡方检验卡方检验 数值数值 Logistic回归分析回归分析 多个多个 分类变量为主分类变量为主 Logistic回归分析回归分析、最优尺度分析最优尺度分析 数值变量为主数值变量为主 判别分析判别分析、Logistic回归分析回归分析 希望将变量/纪录分成若干个类别，但类别数不清楚，或各类别的特征不明：聚类分析聚类分析 希望建立判别方程，对以后新进入的案例进行所属类别的预测：判别分析判别分析 需要探索多个变量间的内在联系或内在结构：因子分析、对应分析因子分析、对应分析 生存时间和生存结局都是需要关心的因素，同时数据中存在大量的失访：生存分析生存分析 得到的是时间序列数据，需要对以后的情形加以预测：时间序列分析与预时间序列分析与预测测 考察的特征需要有多个应变量来表示，同时研究多个自变量对他们的影响：多元方差分析多元方差分析 方差分析方差分析 回回归归分析分析 相相关关分析分析 假假设检验设检验 常用统计分析与预测方法常用统计分析与预测方法 假设检验假设检验 方差分析方差分析 回归分析回归分析 相关分析相关分析 时间序列分析与预测时间序列分析与预测 假设检验假设检验 假设检验分析的概念 为了推断总体分布函数中的未知参数或总体分布函数，采用先提出假设的方法，然后根据总体的一个样本的值对所提出的假设进行检验，并决定或拒绝所提出的假设，这样的工作称为假设检验。为方便起见，根据研究目的提出的假设称为原假设；其对立面假设称为备择假设 假设检验的步骤（1）构造原假设及备择假设。（2）选择检验统计量及其分布。（3）确定显著性水平。（4）确定决策规则。（5）判断决策，确定是否拒绝原假设。假设检验假设检验 Excel在假设检验方面提供的功能：（1）成对观测值t检验（取同一样本观测前后的观测值）（2）方差相等的双样本t检验（假设总体方差相等的两个样本）（3）方差不等的双样本t检验（假设总体方差不等的两个样本）（4）z检验（两总体均服从正态分布，检验这两总体的平均数是否存在差异）（5）F检验（两总体均服从正态分布，检验这两总体的方差是否存在差异）（6）卡方检验（用来验证两个总体间某个比率之间是否存在显著性差异。卡方检验属于非参数假设检验，适用于布尔型数据或二项分布数据，早期用于生产企业的产品合格率等）（7）信赖区间 假设检验假设检验 Excel中进行成对观测值t检验范例 有10个高血压患者服用A降压药和B降压药，其血压下降的具体数据如图所示：假设检验假设检验 本例要求对两种降压药的疗效有无显著性差异做出判断。本例是对一个总体中抽取的两个样本进行检验，适合使用成对样本均值的“t检验”原假设：两种降压药疗效相同 备择假设：两种降压药疗效不同 本例用“t检验”工具计算，步骤如下：（1）在“数据”选项卡的“分析”组中，单击“数据分析”，弹出“数据分析”对话框，从对话框的“分析工具”列表中选择“t检验：平均值的成对二样本分析”假设检验假设检验 （2）单击“确定”按钮进入t检验：平均值的成对二样本分析”对话框，在对话框中填入相应数据或选项 假设检验假设检验（3）单击“确定”，结果如图所示：假设检验假设检验 说明：t Stat是t检验统计量，可以用它与t临界值进行比较，从而判断原假设是否成立。t统计量绝对值大于t临界值，单侧P值比显著性水平0.05要小，因此拒绝原假设。该结论说明两种降压药的疗效不相同。方差分析方差分析 方差分析概述 方差分析由英国统计学家R.A.Fisher首先提出，以F命名其统计量，故方差分析又称F检验。两个概念：随机因素和可控因素 受不同因素的影响，研究得到的数据会不同。造成结果差异的原因可分成两类：一类是不可控的随机因素的影响，这是人为很难控制的一类影响因素，称为随机因素；另一类是研究中人为施加的可以控制的因素对结果的影响，称为可控因素。例如：在影响教学效果的因素中，有两类因素：一是人为可控制的因素，如教学的方法、教材的使用；还有一类是随机的因素，如学生接受知识的能力（在不按能力水平分班的情况下）。前者就称为可控因素，后者称为随机因素。可以对两个普通班级分别使用两种不同的教学方法或不同的教材，一段时间后进行测试，就可以得到不同教学方法或不同教材对教学效果的影响。方差分析就是实现上述功能的分析方法。方差分析的基本思想是：通过分析研究中不同因素的变异对总变异的贡献大小，从而确定可控因素对研究结果影响力的大小。通过方差分析，分析不同水平的可控因素是否对结果产生了显著影响。如果可控因素的不同水平对结果产生了显著影响，那么它和随机因素共同作用，必然使结果有显著的变化；如果可控因素的不同水平对结果没有显著的影响，那么结果的变化主要由随机因素起作用，和可控因素关系不大。方差分析方差分析 根据可控因素的个数，将方差分析分成单因素方差分析和多因素方差分析。单因素方差分析的可控因素只有一个（一个可控因素可以有多个观察水平），多因素方差分析的可控因素有多个。采用的统计推断方法是计算F统计量，进行F检验。方差分析方差分析 单因素方差分析 单因素方差分析测试某一个可控因素的不同水平是否给观察结果造成了显著差异和变动。例如，培训是否给学生成绩造成了显著影响；不同地区的考生成绩是否有显著的差异等。单因素方差分析F统计量的计算公式为：其中 是组间方差，由组间平方和（SSA）除以组间自由度（k-1）算出；是组内方差，由组内平方和（SSE）除以组内自由度（n-k）算出 )kn/(SSE)1k/(SSAMSMSF组内组间组间MS组内MS方差分析方差分析 单因素方差分析范例 某市场调查公司对不同年龄层的女性每月购买化妆品的费用（单位：元）进行了调查，调查时分别选择了4个年龄段（25岁以下、26岁到35岁、36岁到45岁、46岁以上）的女性，每个年龄段8人，每月购买化妆品的费用数据如图所示：方差分析方差分析 现在要判断这4个年龄层的女性每月购买化妆品的费用是否存在显著性差异。在本例中，化妆品费用是要检验的因素，不同年龄层女性花在化妆品费用可看做该因素的4种水平，这是一个单因素4水平的检验。如何判断年龄层是否对化妆品费用有显著影响呢？由上面的数据可以看出两个特点：一是在同一年龄层，花费的化妆品费用不同；二是在不同年龄层，花费的化妆品费用也不同。这可能是年龄造成的，也可能是其他原因造成的。于是，判断年龄层对化妆品花费的的影响是否显著可以转化为检验化妆品花费额度差异主要是什么原因引起的。方差分析方差分析（1）计算平方和（SS）组间平方和：先计算总平均数（可利用“摘要”表中的结果）(106.25+208.75+327.5+457.5)/4=275 再计算每组平均数与总平均数的离差平方和为551425 组内平方和：先计算每组内各自的平方和，再将所有4组的平方和相加即得到总的组内平方和，可以利用Excel提供的计算平方和的函数DEVSQ()来计算，如下：=DEVSQ(A2:A9)+DEVSQ(B2:B9)+DEVSQ(C2:C9)+DEVSQ(D2:D9)最终得到组内平方和为77175（2）计算自由度（df）组间自由度为：4-1=3 组内自由度为：（8-1）4=28 方差分析方差分析 为什么计算总体方差时用数据个数n而计算样本方差时用自由度n-1呢。在估计总体方差时，总体中的任何一个数都和其他数据相独立，从其中抽出任何一个数都不影响其他数据，每一个数据都是独立的，所以用数据个数作为计算方差的除数。而在数据样本中即一组数据中，当其平均数和前面的数据都已知时，最后一个数据就被固定而不能独立变化了，因此这个样本能够独立自由变化的数目就是（n-1）个。自由度的设定是出于这样一个理由：在总体平均数未知时，用样本平均数去计算方差会受到一个限制要计算方差就必须先知道样本平均数，而样本平均数和n都知道的情况下，数据的总和及平方和就是一个常数了。所以，“最后一个”样本数据就不可以变了，因为它要是变，总和就变了，而这是不允许的。因为我们要的是独立数据。自由度=样本个数-样本数据受约束条件的个数，即df=n-k（df自由度，n样本个数，k约束条件个数）。比如本例为单因素方差分析，只有年龄这一个影响因素，如果加入收入水平这个因素，那么自由度就是n-2。依次类推。方差分析方差分析（3）计算方差（MS）组间方差为：组间平方和/组间自由度 本例为：551425/3=183808.3 组内方差为：组内平方和/组内自由度 本例为：77175/28=2756.25（4）计算F统计量 F统计量=组间方差/组内方差=183808.3/2756.25=68.68783（5）按显著性水平0.05查F分布表：在组间自由度n1=3、组内自由度n2=28交叉处，查得F临界值为2.95。F分布表：http:/202.113.29.3/gdsxjxb/wlkj/windows/artsmath/chapter3/fl5.htm 方差分析方差分析 a a F 分布分布 Fa a(k-1,n-k)临界值临界值 0 拒绝拒绝H0 不拒绝不拒绝H H0 0 F 方差分析方差分析 利用Excel进行单因素方差分析，操作步骤如下：（1）在“数据”选项卡的“分析”组中，单击“数据分析”，弹出“数据分析”对话框，从对话框的“分析工具”列表中选择“方差分析：单因素方差分析”方差分析方差分析 （2）单击“确定”按钮，进入“方差分析：单因素方差分析”对话框，在对话框中输入各选项的值，如图所示：方差分析方差分析（3）单击“确定”，输出结果如图所示：方差分析方差分析 对以上计算结果的说明：（1）在“SUMMARY（摘要）”表中分别给出了4个年龄组的观测数（记录购买化妆品的月数）、求和（购买化妆品费用合计）、平均（平均每个月购买化妆品费用）、方差。其中这里的方差是指每组内每个样本数据的方差，计算方法为每组内的人员每月的费用与本组内费用平均数之差的平方和，再除以自由度求得的。注意这里方差的计算方法与我们前面所讲的有所区别，就是用“自由度”代替了“数据个数”作为除数。（2）自由度（degree of freedom，简写为df）是指样本中独立或能自由变化的数据的个数。举个例子，现在你有500元，需要购买5件商品，那么你最多只能有四件商品的价值是自由的（当然是一定范围内），第五件只能是用剩下的钱了，也应就是说最后一件不能自由购买了，这样我可以说，你的自由度是4。更严谨一点，你有购买五件商品的权力，要求是五件商品平均价格是100，那你其中的四件随意购买，第五个就只能是根据计算得到的数值进行购买了。方差分析方差分析 双因素方差分析 无重复的双因素方差分析：各因素的水平没有重复测试各因素的水平没有重复测试 某连锁店在5个地区建立了门店，记录了一年4个季度的销售额，现要求分析不同地区和不同季度对销售额有无显著影响 方差分析方差分析 利用Excel进行无重复双因素方差分析，操作步骤如下：在“数据”选项卡的“分析”组中，单击“数据分析”，弹出“数据分析”对话框，从对话框的“分析工具”列表中选择“方差分析：无重复双因素方差分析”方差分析方差分析 计算结果 行的F统计量远大于F临界值，说明地区是影响销售额的主要因素，列的F统计量略大于F临界值，说明季节是影响销售额的次要因素 方差分析：无重复双因素分析 SUMMARY 观测数 求和 平均 方差 A地区 4 195 48.75 704.9167 B地区 4 447 111.75 930.9167 C地区 4 375 93.75 814.9167 D地区 4 84 21 36.66667 E地区 4 211 52.75 40.91667 一季度 5 427 85.4 2574.8 二季度 5 246 49.2 1156.7 三季度 5 282 56.4 1232.3 四季度 5 357 71.4 1318.8 方差分析 差异源 SS df MS F P-value F crit 行 21441.8 4 5360.45 17.43897 6.12E-05 3.259167 列 3896.4 3 1298.8 4.225343 0.029583 3.490295 误差 3688.6 12 307.3833 总计 29026.8 19 方差分析方差分析 有重复的双因素方差分析：各因素的不同水平重复测试各因素的不同水平重复测试 某超市对某种商品采用3种不同的包装放在超市的3个不同位置上试销，现有3天的销售量数据，要求分析不同包装、不同超市位置，以及二者的交互作用对销售量的影响 1号包装 2号包装 3号包装 位置1 5 6 4 6 8 3 4 7 5 位置2 7 5 3 8 5 6 9 6 4 位置3 3 6 4 2 6 9 4 5 6 方差分析方差分析 利用Excel进行无重复双因素方差分析，操作步骤如下：在“数据”选项卡的“分析”组中，单击“数据分析”，弹出“数据分析”对话框，从对话框的“分析工具”列表中选择“方差分析：可重复双因素方差分析”方差分析方差分析 如同其它分析一样在此对话框中进行选择，注意这个对话框没有了“标志”一栏，也就是说行列字段名是必选的。另外多了一项“每一样本的行数”，也就是重复试验的次数，本例是3天的销售量，故填入3。方差分析方差分析 方差分析：可重复双因素分析 SUMMARY 1号包装 2号包装 3号包装 总计 位置1 观测数 3 3 3 9 求和 15 21 12 48 平均 5 7 4 5.333333 方差 1 1 1 2.5 位置2 观测数 3 3 3 9 求和 24 16 13 53 平均 8 5.333333 4.333333 5.888889 方差 1 0.333333 2.333333 3.611111 位置3 观测数 3 3 3 9 求和 9 17 19 45 平均 3 5.666667 6.333333 5 方差 1 0.333333 6.333333 4.25 总计 观测数 9 9 9 求和 48 54 44 平均 5.333333 6 4.888889 方差 5.5 1 3.611111 方差分析 差异源 SS df MS F P-value F crit 样本 3.62963 2 1.814815 1.139535 0.341995 3.554557 列 5.62963 2 2.814815 1.767442 0.199145 3.554557 交互 48.59259 4 12.14815 7.627907 0.000888 2.927744 内部 28.66667 18 1.592593 总计 86.51852 26 方差分析方差分析 结果说明：将F统计量与F临界值比较，样本（行）的F统计量小于F临界值，说明位置对销售量的影响不显著，列的F统计量也小于F临界值，说明包装对销售量的影响不显著。但是交互作用的F统计量远大于F临界值，说明必须把包装和位置二者很好地结合起来，才能吸引顾客增加销售量。回归分析回归分析 回归分析概述 由一个或一组非随机因素（自变量）来估计或预测某一个随机因素（因变量）的观测值时，所建立的数学模型及所进行的统计分析，称为回归分析。根据自变量的个数，回归分析分为简单回归分析、多元回归分析。简单回归分析按变量之间关系的形式，分为线性回归分析和非线性回归分析。在拟合一条直线前，检查散点图是很重要的，如果散点图上的点大致落于一条直线上，就适合线性回归分析的方法，否则需要考虑用非线性方法。回归分析回归分析 Excel进行简单回归分析范例 以前面根据支出与收入的数据绘制散点图为例子（散点图是定性，回归分析则是定量）回归分析回归分析 （1）在“布局”选项卡上的“分析”组中，单击“趋势线”“线性趋势线”（由于本散点图所显示的各图点的分布接近直线，所以选择线性趋势线），结果如图所示：回归分析回归分析（2）在“布局”选项卡上的“分析”组中，单击“趋势线”“其他趋势线选项”，弹出“设置趋势线格式”对话框，在“趋势线选项”中选定“显示公式”和“显示R平方值”。单击“关闭”按钮，即可在图表上得到回归方程及其判定系数，如图所示：回归分析回归分析 （3）由可知其回归方程式为y=1.2423*x+5.7761,R的平方值等于0.9007表明收入的变化可用支出的变化来解释的比例占90%，收入与支出之间具有高度正相关，即支出越高，收入越高。以此回归方程式可用来预测不同支出的收入。在Excel中可以通过复制公式来计算。回归分析回归分析 多元回归分析 已知某城镇居民家庭前10年每人全年食品消费支出（单位：元）、城镇居民家庭人均可支配收入（单位：元）、食品价格指数（上年为100）的数据如下，现要求进行回归分析，并给出食品消费支出与人均可支配收入和食品价格指数之间的回归方程 食品支出 人均可支配收入 食品价格指数 1771.99 4282.95 124.7 1904.71 4838.9 107.7 1942.59 5160.3 99.8 1934.5 5425.1 96.8 1941.8 5854 95.8 1971.32 6279.98 97.5 2028 6859.6 100.6 2271.84 7702.8 99.9 2416.92 8472.2 103.4 2709.6 9421.61 109.9 回归分析回归分析 在“数据”选项卡的“分析”组中，单击“数据分析”，弹出“数据分析”对话框，从对话框的“分析工具”列表中选择“回归”回归分析回归分析 回归分析回归分析 SUMMARY OUTPUT 回归统计 Multiple R 0.970707 R Square 0.942273 Adjusted R Square 0.925779 标准误差 78.38834 观测值 10 方差分析 df SS MS F Significance F 回归分析 2 702093.9 351046.9 57.12973 4.62E-05 残差 7 43013.13 6144.733 总计 9 745107 Coefficients 标准误差 t Stat P-value Lower 95%Upper 95%下限 95.0%上限 95.0%Intercept 516.3199 348.5909 1.481163 0.182114-307.967 1340.606-307.967 1340.606 人均可支配收入 0.169864 0.015898 10.68493 1.38E-05 0.132272 0.207455 0.132272 0.207455 食品价格指数 4.64074 3.043717 1.524695 0.171161-2.55651 11.83799-2.55651 11.83799 截距 斜率 斜率 所以最后得出的回归方程是：食品消费516.32+0.17*可支配收入4.64*食品价格指数 相关分析相关分析 相关分析概述 相关分析是一种描述变量之间相关程度的分析方法。在相关分析中，所有变量都是随机因素，它们之间并不存在被解释变量和解释变量的关系，即它们之间不考虑因果关系。也就是说，相关分析问题中的变量都假定为随机因素。简单相关是指两个变量之间的相关关系 多元相关是指3个或3个以上变量之间的相关关系 相关分析相关分析 简单相关分析范例，以绘制散点图中的支出收入数据为例 相关分析相关分析（1）在“数据”选项卡的“分析”组中，单击“数据分析”，弹出“数据分析”对话框，从对话框的“分析工具”列表中选择“相关系数”相关分析相关分析（2）输入相应的数据区域及选项 相关分析相关分析（3）结果如图所示，由此可知，收入与支出的相关系数为0.949075，说明两者高度正相关 家庭人均收入 存款余额 人均居住面积 4282.95 29662.3 16.3 4828.9 38520.8 17 5160.3 46279.8 17.8 5425.1 53407.5 18.7 5854 59621.8 19.4 6279.98 64332.4 20.3 6859.6 73762.4 20.8 7702.8 86910.6 22.8 8472.2 103617.7 23.7 9421.61 119555.4 25 多元相关分析范例 已知某地10年来的家庭人均收入（元）、居民户均储蓄存款余额、人均居住面积如下表，试分析各指标的相关系数 相关分析相关分析 相关分析相关分析（1）在“数据”选项卡的“分析”组中，单击“数据分析”，弹出“数据分析”对话框，从对话框的“分析工具”列表中选择“相关系数”相关分析相关分析 相关分析相关分析 家庭人均收入 存款余额 人均居住面积 家庭人均收入 1 存款余额 0.998206 1 人均居住面积 0.994746 0.993687613 1 从返回的结果看，家庭人均收入、居民储蓄存款余额、人均居住面积这三个指标之间存在较强的正相关 时间序列分析与预测时间序列分析与预测 时间序列是指将历史统计资料按时间顺序排列起来得到的一组数据序列。例如，按月份顺序排列的的某种商品的销售量、按日期顺序排列的某股票的日收盘价、全国若干年内钢铁的年产量 影响时间序列变动的因素：1.长期趋势(trend)T 沿某一方向（持续向上或持续向下）的变动 2.季节变动(seasonal fluctuation)S 在一年内重复出现的周期性波动 3.循环变动(Cyclical fluctuation)C 在一个更长时间区间上存在的一种周期起伏，例如，人口增长过程中发生的几次生育高峰就是人口增长的循环变动现象 4.不规则变动(irregular variations)I 除去趋势、季节变动和循环变动之后的随机波动称为不规则变动 时间序列分析与预测时间序列分析与预测 时间序列分析的直接目的是将数列分解为反映长期趋势、季节变动、循环变动和不规则变动几个部分，并揭示各自的数量大小、影响程度和结合方式 时间序列的因素分解模型有乘法模型、加法模型、混合模型等。最常用的是乘法模型，其形式为Y=TSCI。其中，Y代表时间序列。时间序列分析与预测时间序列分析与预测 移动平均法移动平均法 移动平均法 从原时间序列内第一项数值开始，按一定项数间隔计算序列的平均数，一边逐项向下移动一边计算平均数，最终得到一个由移动平均数构成的新的时间序列 移动平均法主要用来消除随机因素引起不规则变动的影响，把序列进行修匀（smoothing)，显示出现象的长期趋势 移动平均法又分为：一次移动平均法、二次移动平均法、加权移动平均法 移动平均法移动平均法 一次移动平均法示例 例：已知某市1996年到2011年社会商品零售总额如下表（单位：亿元），试对2012年的商品零售总额进行预测 年份 商品零售总额