Excel的数据分析应用(统计)

1、1 Excel2007统计功能加载Excel中统计分析的描述：需要加载数据分析库点击Excel2007文件菜单，选择选项，出现如下对话框点击加载项-转到，出现如下窗口勾选分析工具库，确定即可。打开数据菜单栏。即可看到数据分析2 统计描述首先连接MOLAP，查询出需要统计的数据，此处原始数据无特殊要求，只要满足行或列中为同一属性数值即可。其次选择“数据”-“数据分析”-“描述统计”后，出现属性设置框，依次选择：输入区域：原始数据区域，可以选中多个行或列，注意选择相应的分组方式；如果数据有标志，注意勾选“标志位于第一行”；如果输入区域没有标志项，该复选框将被清除，Excel 将在输出表中生成适宜的

2、数据标志；输出区域可以选择本表、新工作表或是新工作簿；汇总统计：包括有平均值、标准误差(相对于平均值)、中值、众数、标准偏差、方差、峰值、偏斜度、极差、最小值、最大值、总和、总个数、最大值、最小值和置信度等相关项目。其中：中值：排序后位于中间的数据的值；众数：出现次数最多的值；峰值：衡量数据分布起伏变化的指标，以正态分布为基准，比其平缓时值为正，反之则为负；偏斜度：衡量数据峰值偏移的指数，根据峰值在均值左侧或者右侧分别为正值或负值；极差：最大值与最小值的差。第K大(小)值：输出表的某一行中包含每个数据区域中的第 k 个最大(小)值。平均数置信度：数值 95% 可用来计算在显著性水平为 5% 时

3、的平均值置信度。结果：3 随机数发生器“随机数发生器”分析工具可用几个分布之一产生的独立随机数来填充某个区域。可以通过概率分布来表示总体中的主体特征。例如，可以使用正态分布来表示人体身高的总体特征，或者使用双值输出的伯努利分布来表示掷币实验结果的总体特征。“随机数发生器”对话框变量个数 在此输入输出表中数值列的个数。如果没有输入数字，Microsoft Office Excel 将在指定的输出区域中填充所有的列。随机数个数 在此输入要查看的数据点个数。每一个数据点出现在输出表的一行中。如果没有输入数字，Excel 将在指定的输出区域中填充所有的行。分布 在此单击用于创建随机数的分布方法。均匀

4、以下限和上限来表征。其变量是通过对区域中的所有数值进行等概率抽取而得到的。普通的应用使用范围 0 到 1 之间的均匀分布。正态 以平均值和标准偏差来表征。普通的应用使用平均值为 0、标准偏差为 1 的标准正态分布。伯努利 以给定的试验中成功的概率（p 值）来表征。伯努利随机变量的值为 0 或 1。例如，可以在范围 0 到 1 之间抽取均匀分布随机变量。如果变量小于或等于成功的概率，则伯努利随机变量的值为 1；否则，随机变量的值为 0。二项式 以一系列试验中成功的概率（p 值）来表征。例如，可以按照试验次数生成一系列伯努利随机变量，这些变量之和为一个二项式随机变量。泊松以值 来表征， 等于平均值

5、的倒数。泊松分布经常用于表示单位时间内事件发生的次数，例如，汽车到达收费停车场的平均速率。模式 以下界和上界、步幅、数值的重复率和序列的重复率来表征。离散 以数值及相应的概率区域来表征。该区域必须包含两列，左边一列包含数值，右边一列为与该行中的数值相对应的发生概率。所有概率的和必须为 1。参数 在此输入用来表征所选分布的数值。随机数基数 在此输入用来产生随机数的可选数值。可在以后重新使用该数值来生成相同的随机数。输出区域 在此输入对输出表左上角单元格的引用。如果输出表将替换现有数据，Excel 会自动确定输出区域的大小并显示一条消息。新工作表 单击此选项可在当前工作簿中插入新工作表，并从新工作

6、表的 A1 单元格开始粘贴计算结果。若要为新工作表命名，请在框中键入名称。新工作簿 单击此选项可创建新工作簿并将结果添加到其中的新工作表中。选择数据分析中的“随机数发生器”单击“确定” 便打开随机数发生器的操作界面如下：3.1 均匀产生具有两列值域在0,10的随机变量，每列具有8个随机数据的随机数。结果：3.2 正态生成具有均值为0，标准差为1的正态分布的随机数，随机变量个数为2，随机数个数为8。结果：3.3 伯努利 二项分布概率为5%结果：3.4 二项式结果：3.5 泊松结果：3.6 模式结果：每个序列重复2次每个数字间隔2每个数字重复3次3.7 离散首先，需要准备随机数源的数据：用来选择随

7、机数从哪些数值中获取，每个数值能够取得的概率结果：4 抽样数据源区域 在此输入对数据区域的引用，该区域中包含需要进行抽样的总体数据。Microsoft Office Excel 先从第一列中抽取样本，然后是第二列，依此类推。标志 如果数据源区域的第一行或第一列中包含标志项，请选中此复选框。如果数据源区域没有标志项，请清除此复选框，Excel 将在输出表中生成适当的数据标志。抽样方法 单击“周期”或“随机”来指示所需的抽样间隔。周期 在此输入进行抽样的周期间隔。数据源区域中位于间隔点处的数值以及此后每一个间隔点处的数值将被复制到输出列中。当到达数据源区域的末尾时，抽样将停止。样本数 在此输入需要

8、在输出列中显示的随机数的个数。每个数值是从数据源区域中的随机位置上抽取出来的，而且任何数值都可以被多次抽取。输出区域 在此输入对输出表左上角单元格的引用。数据将写在该单元格下方的单列里。如果选择的是“周期”，则输出表中数值的个数等于数据源区域中数值的个数除以采样率。如果选择的是“随机”，则输出表中数值的个数等于样本数。新工作表 单击此选项可在当前工作簿中插入新工作表，并从新工作表的 A1 单元格开始粘贴计算结果。若要为新工作表命名，请在框中键入名称。新工作簿 单击此选项可创建新工作簿并将结果添加到其中的新工作表中4.1 随机抽样结果：A列为数据源区域，A列中一共包含了100个随机数；B列为随机

9、抽取的15个数据。4.2 周期抽样 每10个数抽取一数值，每个数值间隔10。 结果：在第一组10个数内选择第10个数值，然后在向下数10个数选择第10个数值5 t-检验“双样本 t-检验”分析工具基于每个样本检验样本总体平均值是否相等。这三个工具分别使用不同的假设：样本总体方差相等；样本总体方差不相等；两个样本代表处理前后同一对象上的观察值。对于以下所有三个工具，t-统计值 t 被计算并在输出表中显示为“t Stat”。数据决定了 t 是负值还是非负值。假设基于相等的基础总体平均值，如果 t 0，则“P(T =0，则“P(T = t) 单尾”返回 t-统计的观察值比 t 更趋向正值的概率。“t

10、 单尾临界值”返回截止值，这样，t-统计的观察值将大于或等于“t 单尾临界值”的概率就为 Alpha。“P(T = t) 双尾”返回将被观察的 t-统计的绝对值大于 t 的概率。“P 双尾临界值”返回截止值，这样，被观察的 t-统计的绝对值大于“P 双尾临界值”的概率就为 Alpha。5.1 平均值的成对二样本分析当样本中存在自然配对的观察值时（例如，对一个样本组在实验前后进行了两次检验），可以使用此成对检验。此分析工具及其公式可以进行成对双样本学生 t-检验，以确定取自处理前后的观察值是否来自具有相同总体平均值的分布。此 t-检验窗体并未假设两个总体的方差是相等的。变量 1 的区域 在此输入

11、需要分析的第一个数据区域的单元格引用。该区域必须为单行或单列，并且包含与第二个区域相同的数据点数。变量 2 的区域 在此输入需要分析的第二个数据区域的单元格引用。该区域必须为单行或单列，并且包含与第一个区域相同的数据点数。假设平均差 在此输入样本平均值的差值。0（零）值表示假设样本平均值相同。标志 如果数据源区域的第一行或第一列中包含标志项，请选中此复选框。如果数据源区域没有标志项，请清除此复选框，Microsoft Office Excel 将在输出表中生成适当的数据标志。在此输入检验的置信度。该值必须介于 0 到 1 之间。 置信度为与 I 型错误发生概率相关的显著性水平（拒绝真假设）。输

12、出区域 在此输入对输出表左上角单元格的引用。如果输出表将替换现有数据，Excel 会自动确定输出区域的大小并显示一条消息。新工作表 单击此选项可在当前工作簿中插入新工作表，并从新工作表的 A1 单元格开始粘贴计算结果。若要为新工作表命名，请在框中键入名称。新工作簿 单击此选项可创建新工作簿并将结果添加到其中的新工作表中。返回结果：由于t-值的绝对值大于双边检验的t双尾临界值，所以接受原假设两组数据具有相同的均值，即促销前后销售量变化不大促销没有作用。5.2 双样本等方差假设本分析工具可进行双样本学生 t-检验。此 t-检验窗体假设两个数据集取自具有相同方差的分布，故也称作同方差 t-检验。可以

13、使用此 t-检验来确定两个样本是否来自具有相同总体平均值的分布。“t-检验: 双样本等方差假设”对话框变量 1 的区域 在此输入需要分析的第一个数据区域的单元格引用。该区域必须由单列或单行的数据组成。变量 2 的区域 在此输入需要分析的第二个数据区域的单元格引用。该区域必须由单列或单行的数据组成。假设平均差 在此输入样本平均值的差值。0（零）值表示假设样本平均值相同。标志 如果数据源区域的第一行或第一列中包含标志项，请选中此复选框。如果数据源区域没有标志项，请清除此复选框，Microsoft Office Excel 将在输出表中生成适当的数据标志。在此输入检验的置信度。该值必须介于 0 到

14、1 之间。 置信度为与 I 型错误发生概率相关的显著性水平（拒绝真假设）。输出区域 在此输入对输出表左上角单元格的引用。如果输出表将替换现有数据，Excel 会自动确定输出区域的大小并显示一条消息。新工作表 单击此选项可在当前工作簿中插入新工作表，并从新工作表的 A1 单元格开始粘贴计算结果。若要为新工作表命名，请在框中键入名称。新工作簿 单击此选项可创建新工作簿并将结果添加到其中的新工作表中。结果：由于t-值的绝对值大于双边检验的t双尾临界值，所以接受原假设两组数据具有相同的均值，即促销前后销售量变化不大促销没有作用。5.3 双样本异方差假设本分析工具可进行双样本学生 t-检验。此 t-检验

15、窗体假设两个数据集取自具有不同方差的分布，故也称作异方差 t-检验。如同上面的“等方差”情况，可以使用此 t-检验来确定两个样本是否来自具有相同总体平均值的分布。当两个样本中有截然不同的对象时，可使用此检验。当对于每个对象具有唯一一组对象以及代表每个对象在处理前后的测量值的两个样本时，应使用下面的示例中所描述的成对检验。用于确定统计值 t 的公式如下：下面的公式可用于计算自由度 df。因为计算结果一般不是整数，所以 df 的值被舍入为最接近的整数以便从 t 表中获得临界值。因为有可能为 TTEST 计算出一个带有非整数 df 的值，所以 Excel 工作表函数 TTEST 使用计算出的、未进行

16、舍入的 df 值。由于这些决定自由度的不同方式，t-检验 函数和此 t-检验工具的结果将与“异方差”情况中不同。变量 1 的区域 在此输入需要分析的第一个数据区域的单元格引用。该区域必须由单列或单行的数据组成。变量 2 的区域 在此输入需要分析的第二个数据区域的单元格引用。该区域必须由单列或单行的数据组成。假设平均差 在此输入样本平均值的差值。0（零）值表示假设样本平均值相同。标志 如果数据源区域的第一行或第一列中包含标志项，请选中此复选框。如果数据源区域没有标志项，请清除此复选框，Microsoft Office Excel 将在输出表中生成适当的数据标志。在此输入检验的置信度。该值必须介于

17、 0 到 1 之间。 置信度为与 I 型错误发生概率相关的显著性水平（拒绝真假设）。输出区域 在此输入对输出表左上角单元格的引用。如果输出表将替换现有数据，Excel 会自动确定输出区域的大小并显示一条消息。新工作表 单击此选项可在当前工作簿中插入新工作表，并从新工作表的 A1 单元格开始粘贴计算结果。若要为新工作表命名，请在框中键入名称。新工作簿 单击此选项可创建新工作簿并将结果添加到其中的新工作表中。结果：由于计算出来的t值的绝对值大于t双尾临界值，说明促销前后销售量是有变化的。由于t单尾的t值小于t单尾临界值，所以说促销后卷烟的销量更高。5.4 双样本平均差检验双样本平均差检验是在两个变

18、量方差已知的情况加，对样本均值进行比较。该工具的实际应用比较少，因为很少有总体方差完全确定的情况。6 方差分析方差分析工具提供了不同类型的方差分析。具体应使用哪种分析工具需要根据因素的个数以及待检验样本总体中所含样本的个数而定。6.1 单因素方差分析单因素方差分析对两个或者更多样本的数据执行简单的方差分析。此分析可提供过一种假设测试。该假设的内容是：每个样本都取自相同的基础概率分布，而不是对所有样本来说基础概率分布都不同。如果只有两个样本，则可使用工作表函数TTEST。如果有两个以上的样本，则没有使用方便的TTIEST归纳，可改为调用“单因素方差分析”模型。数据源区域 在此输入待分析数据区域的

19、单元格引用。引用必须由两个或两个以上按列或行排列的相邻数据区域组成。分组方式 若要指示数据源区域中的数据是按行还是按列排列，请单击“行” 或“列”。标志位于第一行/标志位于第一列 如果数据源区域的第一行中包含标志项，请选中“标志位于第一行”复选框。如果数据源区域的第一列中包含标志项，请选中“标志位于第一列”复选框。如果数据源区域中没有标志项，则该复选框将被清除。Microsoft Office Excel 将在输出表中生成适当的数据标志。 在此输入要用来计算 F 统计的临界值的置信度。 置信度为与 I 型错误发生概率相关的显著性水平（拒绝真假设）。输出区域 在此输入对输出表左上角单元格的引用。

20、当输出表将替换现有数据，或是输出表超过了工作表的边界时，Excel 会自动确定输出区域的大小并显示一条消息。新工作表 单击此选项可在当前工作簿中插入新工作表，并从新工作表的 A1 单元格开始粘贴计算结果。若要为新工作表命名，请在框中键入名称。新工作簿 单击此选项可创建新工作簿并将结果添加到其中的新工作表中。结果：因为F值（F）小于F比值（F crit）则不拒绝原假设，即：促销前后对销售量的影响不大。6.2 可重复双因素分析此工具可用于当数据可沿着两个不同的维度分类时的情况。例如，在测量植物高度的实验中，可能对植物施用了不同品牌的化肥（例如：A、B、C），并且植物也可能处于不同温度的环境中（例如：高、低）。对于这留队可能的组合（化肥和温度），我们有相同数量的植物高度观察值。使用此方差分析工具，我们可检验：1、 使用不同品牌化肥的植物的高度是否组起相同的基础样本总体。此分析忽略温度。2、 处于不同温度级别环境中的植物的高度是否取自相同基础样本总体。此分析忽略所使用的化肥品牌。无论是否考虑在1中发现的不同品牌化肥之间的差异的影响以及2中发现的不同温度之间差异的影响，代表所有（化肥、温度）值对的6个样本都取自相同的样本总体。另一种假设是除了基于化肥 温度单个因素的差异带来的影响之外，特定的化肥，温度值对也会有影响。数据源区域 在此输入待分析数据区域的单元格引用。引