大多数情况下,当您运行统计时,您希望使用统计软件。这些工具是用来做计算的,比如t检验、卡方检验、相关性等等。Excel不是用来进行数据分析的。但这并不意味着你做不到。
不幸的是,Excel的统计功能并不总是直观的。他们通常会给你一些深奥的结果。因此,我们不使用stats函数,而是使用go to Excel statistics外接程序:数据分析工具包。
尽管Toolpak的拼写相当糟糕,但它包含了大量有用的统计功能。让我们看看我们能用Excel统计做些什么。
虽然您可以不用数据分析工具包来进行统计,但使用它要容易得多。要在Excel 2016中安装Toolpak,请转到“文件”>;“选项”>;“加载项”。
单击“管理:Excel加载项”旁边的“转到”
在生成的窗口中,选中Analysis Toolpak旁边的框,然后单击OK。
如果您将数据分析工具包正确添加到Excel中,您将在“数据”选项卡中看到一个数据分析按钮,分组到“分析”部分:
如果您想要更强大的功能,请务必查看Excel的其他加载项。
无论您正在运行什么统计测试,您可能都希望首先获得Excel的描述性统计数据。这将为您提供有关均值、中位数、方差、标准差和误差、峰度、偏度和各种其他数字的信息。
在Excel中运行描述性统计很容易。单击“数据”选项卡中的“数据分析”,选择“描述性统计”,然后选择输入范围。单击“输入范围”字段旁边的箭头,单击并拖动以选择数据,然后按Enter键(或单击相应的向下箭头),如下面的GIF所示。
之后,确保告诉Excel您的数据是否有标签,您希望输出在新的工作表中还是在同一个工作表上,以及您希望汇总统计信息和其他选项。
之后,点击OK,您将得到描述性统计:
t检验是最基本的统计检验之一,用Toolpak在Excel中很容易计算。单击数据分析按钮并向下滚动,直到看到t-test选项。
您有三个选择:
要测试两个样本的方差是否相等,需要进行F检验。在分析工具列表中找到F-Test Two Sample for Variances,选择它,然后单击OK。
在输入范围框中输入两个数据集。把alpha值保持在0.05,除非你有理由改变它——如果你不知道这意味着什么,就离开吧。最后,单击“确定”。
Excel将在新工作表中显示结果(除非您在当前工作表中选择了输出范围和单元格):
你看到的是P值。如果小于0.05,则方差不相等。所以要运行t检验,应该使用不等方差选项。
要运行t测试,请从“分析工具”窗口中选择适当的测试,并以与F测试相同的方式选择两组数据。将alpha值保留为0.05,然后单击“确定”。
结果包括了t检验需要报告的所有内容:均值、自由度(df)、t统计量以及单尾和双尾检验的P值。如果P值小于0.05,则两个样本存在显著差异。
如果你不确定是使用单尾t检验还是双尾t检验,请查看加州大学洛杉矶分校的解释。
Excel数据分析工具包提供了三种类型的方差分析(ANOVA)。不幸的是,它不能让您运行必要的后续测试,如Tukey或Bonferroni。但是你可以看到几个不同的变量之间有没有关系。
以下是Excel中的三个方差分析测试:
我们将在这里进行单因素分析。在我们的示例中,我们将查看三组数字,分别标记为“干预1”、“干预2”和“干预3”。要运行方差分析,请单击“数据分析”,然后选择“方差分析:单因素”。
选择输入范围并确保告诉Excel您的组是列还是行。我还在这里选择了“第一行中的标签”,以便在结果中显示组名。
点击OK后,我们得到以下结果:
注意P值小于0.05,所以我们得到了一个显著的结果。这意味着在测试中至少有两个组之间存在显著差异。但是,由于Excel不提供测试来确定哪些组不同,所以最好查看摘要中显示的平均值。在我们的例子中,干预3看起来可能是不同的。
这在统计上并不合理。但如果你只是想看看有没有区别,看看是哪个群体造成的,那就行了。
双因素方差分析更为复杂。如果您想了解有关何时使用双因素法的更多信息,请参阅下面的视频索菲亚网站而“无复制”和“有复制”的例子来自真实的统计数据。
在Excel中计算相关性比t检验或方差分析简单得多。使用“数据分析”按钮打开“分析工具”窗口并选择“相关性”。
选择输入范围,将组标识为列或行,并告诉Excel是否有标签。之后,点击OK。
你不会得到任何有意义的度量,但是你可以看到每个组是如何与其他组相关联的。值为1表示绝对相关,表示两个值完全相同。相关值越接近1,相关性越强。
回归是工业中最常用的统计测试之一,Excel为这种计算提供了惊人的能力。我们将在Excel中快速运行多元回归。如果您不熟悉回归,请查看HBR的《为业务使用回归指南》。
假设我们的因变量是血压,两个自变量是体重和盐摄入量。我们想看看哪一个能更好地预测血压(或者两者是否都好)。
单击数据分析并选择回归。这次填写输入范围框时需要小心。输入Y范围框应包含单个因变量。输入X范围框可以包含多个自变量。对于一个简单的回归,不要担心其余的(尽管如果选择了标签,记得告诉Excel)。
下面是我们的计算结果:
点击OK后,你会得到一个大的结果列表。我在这里强调了体重和盐摄入量的P值:
如你所见,体重的P值大于0.05,因此没有显著的关系。然而,盐的P值低于0.05,表明它是一个很好的预测血压的指标。
如果您计划显示回归数据,请记住,您可以在Excel中将回归线添加到散点图中。这是一个伟大的视觉辅助分析。
虽然Excel并不以其统计功能而闻名,但它实际上包含了一些非常有用的功能,例如PowerQuery工具,它对于组合数据集之类的任务非常方便。(学习如何生成您的第一个Microsoft Power Query脚本。)还有数据分析Toolpak statistics插件,它真正展示了Excel的一些最佳功能。我希望您已经学习了如何使用Toolpak,现在您可以自己玩一下,弄清楚如何使用它的更多功能。
有了这个,你就可以把你的Excel技能提高到一个新的水平了,我们的文章介绍了如何使用Excel的Goal Seek功能来处理更多的数据,以及如何使用vlookup搜索值。在某些时候,您可能还想学习如何将Excel数据导入Python。
... 如果您已经在使用数据分析工具包,您将看到数据分析按钮。否则,解算器将自动出现。 ...
... 现在您已经学习了如何使用powerquery附加数据,您有了一个强大的新工具来简化数据。现在数据已加载到Excel中使用,您可以像处理任何表一样处理它。您可以从Excel表格创建图形和图表,也...
...的数据转换工具。它允许您从许多不同的来源导入数据,进行更改,并将数据直接加载到Excel工作簿中。 ...
... 如果您需要执行复杂的数据分析,但不确定从何处开始,请转到本课程。它解释了如何合并来自不同来源的数据、应用逻辑、拆分数据和使用数据透视表函数。 ...