Excel RANK.AVG函数与SPSS Wilcoxon符号秩检验的秩和结果差异问题求助

我来帮你拆解这个问题——核心原因是SPSS和Excel对Wilcoxon秩的计算规则存在两个关键差异，尤其是在处理零差异和相同数值（结）的方式上：

1. SPSS的Wilcoxon秩计算规则（统计规范） #

SPSS在执行Wilcoxon符号秩检验时，会自动遵循以下逻辑：

• 首先完全忽略差异（diff）为0的案例：这些行不会参与任何秩的计算，因为它们没有提供正负差异的信息
• 仅对非零的绝对差异（abs diff）进行排序，然后对相同数值的“结”计算平均秩——这个平均秩是基于所有非零绝对差异的整体排序得出的

2. 你的ExcelRANK.AVG公式的问题 #

从你提供的表格来看，你的Excel计算明显违反了上述规则，导致秩的结果混乱：

• 没有排除零差异行：你的RANK.AVG公式应该只针对diff≠0的行计算，但目前显然把diff=0的行也包含在排序范围内了，打乱了秩的顺序
• 公式范围或排序方向错误：比如表格中abs diff=0.2的行，有的秩是1、2，有的是4.5，这说明你的RANK.AVG没有对所有0.2的行计算统一的平均秩，大概率是引用范围不对，或者排序方向（升序/降序）和SPSS不匹配

3. 让Excel结果和SPSS对齐的修正步骤 #

按照以下方法调整Excel公式，就能得到和SPSS一致的秩和：

• 步骤1：筛选零差异行：只对diff≠0的行计算秩，用IF函数排除零值
• 步骤2：正确使用RANK.AVG：指定绝对引用的范围，并且设置升序排序（第三个参数为1，和SPSS从小到大排秩的规则一致）

假设你的abs diff在E列，数据从E2到E27，那么正确的公式应该是：

=IF(D2<>0, RANK.AVG(E2, $E$2:$E$27, 1), "")

用这个公式重新计算后：

• 所有abs diff=0.2的行都会得到相同的平均秩（因为它们是最小的非零绝对差异，排序位置是1-6，平均秩为(1+2+3+4+5+6)/6=3.5）
• 零差异行的秩会显示为空，不会参与秩和计算
• 最终的正差异秩和、负差异秩和会和SPSS的结果完全匹配

验证你的案例 #

比如你表格中abs diff=0.4的行，在排除零差异后，它们的排序位置应该是第7到第12（假设0.2占了1-6），平均秩就是(7+8+9+10+11+12)/6=9.5，这和SPSS的计算逻辑完全一致。

内容的提问来源于stack exchange，提问作者dave