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

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

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

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

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

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

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

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

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

9、结果：a 列为数据源区域， a 列中一共包含了 100 个随机数； b 列为随机抽取的 15 个数据。5 t-检验“ 双样本t-检验 ” 分析工具基于每个样本检验样本总体平均值是否相等。这三个工具分别使用不同的假设：样本总体方差相等；样本总体方差不相等；两个样本代表处理前后同一对象上的观察值。对于以下所有三个工具，t-统计值t 被计算并在输出表中显示为“t stat”。数据决定了t 是负值还是非负值。假设基于相等的基础总体平均值，如果t 0，则“p(t =0，则“p(t = t) 单尾 ” 返回t-统计的观察值比 t 更趋向正值的概率。“t 单尾临界值 ” 返回截止值，这样，t-统计的观察值将

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

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

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

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

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

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

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

17、与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 个样本都取自相同的样本总体。另一种假设是除了基于化肥温度单个因素的差异带来的影响之外，特定的化肥，温度值对也会有影响。数据源区域在此输入待分