Excel 中的 Spearman 秩相关计算器

为了解决“停电现象如何与雷暴的出现相关联？”这个问题，我们可以采用各种统计技术。为此，我们必须首先从各种来源收集大量数据，然后对其进行分析，以寻找可能在其他地方有用的任何关系。确定两个事件是否相互关联的一个有用工具是 Spearman Correlation（斯皮尔曼相关系数）。本教程将通过足够多的示例，解释如何使用 Excel 计算两个数据数组的 Spearman Correlation。

为什么会出现 Spearman Correlation？

Spearman Correlation 是 Pearson Correlation Coefficient（皮尔逊相关系数）的非参数对应项。它确定了两个不同数据集之间的线性相关性，通常用 rs 或 p 表示。

使用 Pearson's Product Moment Correlation 可以确定连续变量之间的线性关系。Pearson Correlation 大致可以表示为偏差

已排序的值为 Rx 和 Ry。为数据集的标准差。

Spearman Correlation 评估值之间的单调关系。

Spearman Coefficient 的完整形式为

• 这是 Pearson 公式的一个重大修改版本。此处，
• 变量 x 和 y 由字母 Rx 和 Ry 表示。
• 平均秩由 R?(x) 和 R?(Y) 表示。

Spearman Correlation 和 Pearson 系数非常接近；但是，在出现异常值的情况下，您可能需要使用 Spearman Correlation。

Spearman Correlation 的应用

1. 如果您的数据中存在异常值，并且您知道它们会影响结果。明智的做法是使用 Spearman Correlation。这是因为异常值对 Spearman Correlation 的影响与对 Pearson Correlation 的影响不同，因为 Spearman 使用的是值的秩而不是实际值。
2. 如果数据之间存在非线性关系，或者它们没有正确分布。在这种情况下，Spearman 系数优于 Pearson 系数。
3. 如果任何变量是定序变量，您应该使用 Spearman Correlation 而不是 Pearson 系数。

Spearman Correlation 系数的值范围为 +1 到 1

• 值为 1 表示与数据的完全相关。这表明两个数据集匹配。
• 值为 -1 表示数据完全负相关。
• 值为 0 表示数据之间没有相关性。

我们将使用以下数据集进行演示。此数据集包含两个数据数组，带有“Math”和“Economics”列标题。我们将检查这两个列的值并计算它们之间的相关性。

1. 使用 Excel 公式计算 Spearman Correlation

以下是 Spearman Correlation 基本近似的示例

在 Excel 中使用传统公式计算 Spearman Correlation，其中 di 是两个秩之间的差值

对于观测数量，它是 n。

如果某个秩有相同的值，此公式将不起作用。我们必须查看秩数据集，以确定此方法是否与我们的数据集很好地配合。

Spearman Correlation Coefficient 的计算方法是，首先使用 RANK.AVG 函数对值进行排序，然后使用这些秩。

步骤：

• 必须首先对 **Math & Economics** 列中的值进行排序。
• 在单元格 **D5** 中，键入以下公式并按 **Enter** 键以完成此操作

• 接下来，使用填充柄将单元格 **D14** 拖动。
• 如您所见，单元格范围 **D5:D14** 中的值已排序。
  
• 使用传统方程在 Excel 中计算 Spearman Correlation。
• 接下来，在单元格 **E5** 中输入并键入以下公式，以对单元格范围 **C5:C14** 进行排序：

• 之后，使用填充柄拖动单元格 **E14**。
• 您现在应该看到单元格 **E5-E14** 范围中值的当前排序。
• 目前，如果我们仔细观察，我们可以看到在 Math 和 Economics 列值的排序中没有相同秩的值。
• 因此，使用我们的方法在 Excel 电子表格中计算 Spearman Correlation 可以顺利完成。
• 我们当前的任务是确定每行秩值的差异。
  
• 只需将以下公式键入单元格 **F5** 并按 Enter 键即可完成此操作

• 之后，使用 **填充柄** 拖动单元格 **F14**。
• 您现在可以看到每行秩值中的差异显示在 **F5-F14** 单元格范围内。
  
• 现在必须找到每行秩值之间差异的平方，可以在单元格 **C5** 到 C14 中找到
• 要完成此操作，请将以下公式键入单元格 **G5** 并按 Enter 键

• 之后，使用填充柄拖动单元格 **G14**。
• 您现在可以看到每个单元格中每行额定值之间差异的平方，范围为 **G5** 到 **G14**。
  
• 在单元格 **G15** 中，使用以下公式获取单元格范围 **G5:G15** 的总和：

• 现在我们拥有计算 Spearman Correlation 所需的所有参数。
• 在单元格 **D16** 中，请输入条目数量；在此例中为 10。
• 在单元格 **D17** 中填入以下公式

• 您将立即获得 Spearman Correlation。
• 从输出的负值可以看出，这两个秩数据列之间存在负相关。
  
• 最终结果清楚地表明，我们获得的值是负值。它表明 Math 和 Economics 列中的值之间存在负相关。换句话说，如果一个列的值增加，另一个列的值就不会增加，反之亦然。

2. 使用 CORREL 函数计算 Spearman Correlation

CORREL 函数提供两个单元格值范围之间的相关性。您可以使用这些值来查找两个变量范围之间的相关性。该值落在 -1 到 +1 的范围内。当值为正时，表示一个数据集的值增加时，另一个数据集的值也随之增加，反之亦然。此外，我们使用 RANK.AVG 函数对条目进行排序。

步骤：

• 首先需要对 Math 和 Economics 列的值进行排序。
• 要完成此操作，请将以下公式键入单元格 **D5** 并按 Enter 键

• 接下来，将填充柄拖动到单元格 **D14**。
• 然后，您将看到单元格范围 **D5:D15** 中的值已排序。
  
• 接下来，在单元格 **E5** 中输入并按 Enter 键，以对单元格 **E5:E14** 的距离进行排序：

• 之后，使用填充柄拖动单元格 **E14**。
• 您现在将看到单元格 **E5-E14** 范围中的数据的排序。
• 接下来，选择单元格 **C17** 并在其中键入以下公式

• 如您在输入公式后所见，单元格 **C17** 现在显示 Spearman correlation。
  

如何在 Excel 中使用图表计算 Spearman Correlation

使用散点图可以轻松计算 R 方值，通过对总值进行平方根即可获得 Spearman Correlation 值。但是，根据趋势线的斜率，可能需要调整值的符号。在继续之前，使用 RATE.AVG 函数对数据进行评分，并在此过程中应用 SQRT 函数。

步骤：

• 需要对 Math 和 Economics 列进行排序。
• 要实现此目的，需要对 Math 和 Economics 列中的值进行排序。
• 要完成此操作，请将以下公式键入单元格 **D5** 并按 Enter 键

• 接下来，使用填充柄将单元格 **D14** 拖动。
• 您现在将看到单元格 **D5** 到 **D14** 范围内的值已排序。
  
• 接下来，在单元格 **E5** 中输入并按 Enter 键，以对单元格 **E5:E14** 的值范围进行排序：

• 使用填充柄拖动单元格 **E14**。
• 您将观察到单元格 **E5 到 E14** 范围中的数据已排序。
  
• 我们必须选择 **RMath** 和 **REconomics** 这两列来生成使用排序后的两列的散点图。
• 之后，在“插入”选项卡的“图表”组中，单击“散点图”选择它。
  
• 您会注意到，当新图表窗口打开时，图表的 X 轴和 Y 轴会伴随着 RMath 列的值和 REconomics 值。
  
• 现在，单击图表侧面的 **图表元素** 图标。
• 要将 **趋势线** 添加到图表中，请勾选 **趋势线** 选项。
• 当您勾选该项目时，图表将显示一条向下倾斜的 **趋势线**。
  
• 然后，从 **趋势线** 选项菜单中选择直方图形状的符号。
• 接下来，勾选在图表上显示 **R-squared** 值的框。
• 现在，观察 **R-value** 在图表上的显示。
• 记下此值。
  
• 在选择单元格 **D16** 后，输入 R2 的值。
• 要找到 Spearman Correlation 值，我们必须对此 R2 值进行平方根运算。
• 将以下公式放入单元格 **D18：**

• 单元格 D18 中的最终 Spearman Correlation 值需要进行小幅修改。要完成此操作，您必须首先观察趋势线的斜率。如果趋势线向下，则应更改 D18 单元格的值的符号。如果斜率向上，则无需更改符号。
• 在这种情况下，趋势线是向下倾斜的。因此，我们必须将 D18 单元格的值的符号从 0.41821 更改为 -0.4821。
  
• 这代表了数据集的最终 Spearman Correlation 值。
• 最终数字的负值表明数据列之间存在负相关。