Excel 中的统计学

在许多不同的领域，决策的制定都严重依赖于统计。对数据进行有效的分析对于教育和商业分析至关重要。微软 Excel 作为一款最受欢迎的电子表格应用程序，拥有全面的统计工具包，可以帮助用户进行各种数学分析。本教程提供了使用 Excel 进行数据分析的深入指南，涵盖了从基础统计计算到更深入的分析的方方面面。

开始使用 Excel 的统计函数

Excel 广泛的内置统计功能使您可以执行常见的统计计算。这些函数位于“公式”选项卡下的“统计”区域。

基本描述性统计

描述性统计提供了样本和度量的基本描述。

Excel 提供了多个函数来计算这些数值。

• 均值：一组值的平均值。Excel 中的均值使用 AVERAGE() 函数计算。
• 中位数：当数据值排序后，它表示数据集中间的值。Excel 使用 MEDIAN() 函数计算此值。
• 众数：在一批数据中出现次数最多的值。MODE.MULT() 可以返回多个众数，而 MODE.SNGL() 只返回一个。
• 标准差：它计算值变化的程度或分散程度。Excel 为样本提供 STDEV.S()，为整个总体提供 STDEV.P()。
• 方差：这是另一个分散度量，可以使用 VAR.S() 和 VAR.P() 方法分别计算样本和总体的方差。

使用 Excel 汇总数据

为了理解数据的分布和特性，有必要对其进行汇总。Excel 有一个名为数据透视表 (PivotTables) 的有用功能，可以快速有效地汇总大型数据集。

数据透视表

用户可以重组和汇总特定列和行的数据，以创建合适的报告。它具有高度的可配置性，允许计算特定统计信息以及进行分组和筛选。

创建数据透视表的步骤

• 确定数据范围。
• 在“插入”选项卡上，选择“数据透视表”。
• 确定数据透视表报告的位置。
• 根据需要，将字段拖到“行”、“列”、“值”和“筛选器”区域。

描述性统计工具

Excel 在“数据”选项卡下的“数据分析”工具中提供了一个内置函数，用于进行更全面的统计概述。如果用户选择“描述性统计”，他们就可以生成一个详尽的摘要，其中包括方差、标准差、均值、中位数、众数等。

如何访问

• 点击“数据”选项卡。
• 从“分析”组中，选择“数据分析”。
• 选择“描述性统计”，然后提供输入和输出的范围。

Excel 中的推断统计

推断统计是指使用样本数据对总体进行预测或推断。Excel 中有多种用于执行推断统计的工具。

假设检验

t 检验：假设检验是统计学中的一个重要概念，用于确定数据集中是否有足够的证据得出结论，即某个事件适用于所有个体。

Excel 中提供三种不同的 t 检验（成对双样本、假设方差不等的双样本、假设方差相等的双样本）。为此，使用 T.TEST() 函数。

步骤：

• 从“数据”菜单中选择“数据分析”。
• 选择相关的 t 检验并指定数据范围。

Z 检验：当总体方差已知时，可以使用 Excel 的 Z.TEST() 函数执行 Z 检验。

方差分析：它用于比较多个组之间的均值。Excel 的“单因素方差分析”工具位于“数据分析”下。

步骤：

• 点击“数据”并选择“数据分析”。
• 选择“方差分析：单因素”。
• 输入数据范围并指定所需的输出。

回归分析

回归分析用于理解因变量和自变量之间的关系。Excel 提供了多元回归和简单 线性回归 函数。

• 简单线性回归：这种建模两个变量之间关系的方法涉及将线性方程应用于观察到的数据。可以使用 Excel 的 LINEST() 函数或“数据分析”中的“回归”函数。
• 多元回归：在处理多个自变量时，当加载了“数据分析”加载项时，Excel 的“回归”函数非常有用。

步骤：

• 从“数据”菜单中选择“数据分析”。
• 选择“回归”并提供因变量和自变量的输入范围。

卡方检验

卡方检验用于确定分类变量之间是否存在显着相关性。

• 独立性卡方检验：Excel 中的 CHISQ.TEST() 函数可用于执行此检验。
• 卡方拟合优度检验：它检验个体在多大程度上代表了整个总体。尽管 Excel 中没有简单的方法来执行此检验，但仍可以使用 CHISQ.DIST() 和 CHISQ.INV() 函数来执行。

Excel 中的高级统计分析

Excel 为需要更复杂统计分析的客户提供了多种功能和工具。

相关性和协方差

相关性衡量两个变量之间线性关系的程度和方向。另一方面，协方差表示变量之间线性关系的方向。

• 相关性：使用 CORREL() 函数计算皮尔逊相关系数。
• 协方差：对于总体协方差，Excel 有 COVARIANCE.P()；对于样本协方差，有 COVARIANCE.S()。

移动平均和指数平滑

在时间序列分析中，通常使用连续平滑和移动平均来检测趋势和进行预测。

移动平均：它用于平滑数据中的波动。

步骤：

• 在“数据”选项卡中选择“数据分析”。
• 选择“移动平均”并输入间隔和输入范围。

指数平滑：在这种方法中，通过为最近的观测值分配更高的权重来预测数据。Excel 通过“数据分析”加载项提供了“指数平滑”工具。

统计分布

Excel 提供了用于处理各种统计分布的函数，这些函数对于各种调查至关重要。

• 正态分布：对于指定的平均值和方差，NORM.DIST() 函数给出正态分布。
• 二项分布：BINOM.DIST() 函数计算在特定次数的试验中获得特定次数成功的概率。
• 泊松分布：使用 POISSON.DIST() 函数来表示一个事件在一个给定区间内发生的次数。
• t 分布：在检查小样本数据时，T.DIST() 函数给出学生 t 分布。

统计分析的数据可视化

数据可视化是统计分析的重要组成部分。Excel 提供了多种创建图表和图形的工具，有助于数据分析。

创建图表

直方图：它有助于理解数据的分布情况。Excel 在“数据分析”中的“直方图”工具根据数据范围生成直方图。

步骤：

• 在“数据分析”下选择“直方图”选项。
• 指定分箱范围和输入范围。

散点图：它用于观察两个变量之间的关系。Excel 中的“插入”选项卡可以轻松创建电子表格。

步骤：

• 突出显示数据。
• 转到“插入”>“散点图”并选择您喜欢的格式。

箱线图：尽管 Excel 没有内置的箱线图功能，但可以通过使用堆积柱形图手动创建。

步骤：

• 计算四分位数并创建堆积柱形图。
• 将图形样式化为箱线图外观。

条件格式

Excel 的条件格式通过突出显示重要数据点、趋势和异常值，使统计分析更易于理解。

步骤：

• 确定数据范围。
• 转到“开始”>“条件格式”并选择一种规则类型。
• 自定义格式选项。

在 Excel 中进行有效统计分析的技巧

使用命名范围

通过允许您为单个单元格或单元格范围指定有意义的名称，可以使公式更易于理解和维护。您可以使用“SalesData”等描述性名称来代替引用“A1”之类的单元格坐标。此过程使数据更新更容易，因为它只需要更改范围规范，并且还可以减少错误，尤其是在复杂的计算中。由于命名范围使他人更容易理解数据的上下文，因此它们还可以改善协作。创建命名范围的过程包括选择范围，然后选择“公式”选项卡，然后单击“定义名称”。

记录您的工作

对所有计算、过程和假设进行全面记录有助于使您的研究透明且可重复。它使您能够稍后回顾过程以进行确认或进一步研究。维护结构化的工作簿结构、命名良好的工作表和注释，以帮助您掌握自己的过程。此过程不仅有助于及早发现问题，还能促进同事之间的团队合作和沟通。

验证数据

它涉及查找可能扭曲结果的错误、差异和异常值。为了防止输入不正确或不完整，请首先使用 Excel 内置的数据验证功能设置数据输入规则。此外，通过删除重复项和有效管理缺失值来清理数据集。要查找异常值，请将数据与源进行比较并使用描述性统计。可靠的分析始于经过验证的数据，这有助于您从统计工作中得出更准确的结论和见解。

理解假设

统计程序产生可靠结果的条件称为假设。常态性、同方差性和独立性是常见的假设。例如，许多检验都假定数据呈正态分布。如果这些假设不成立，结果可能会产生误导。在进行任何分析之前，请使用 Excel 的功能（如散点图、直方图和正态性检验，如 Shapiro-Wilk 检验）来检验这些假设。

利用 Excel 加载项

它简化了回归、方差分析和其他复杂统计计算。通过 Excel 中的“加载项”选项激活它，并直接从“数据”选项卡使用高级工具。研究具有特定统计功能和用户友好界面的第三方加载项，例如 XLSTAT 或 Real Statistics，以获得更深入的见解。这些工具简化了数据处理，便于解释和呈现结果。通过利用 Excel 加载项，您可以提高数据分析技能，节省时间并提高统计任务的准确性。