excel - 在Excel中计算矛兵排名统计的p值(使用或不使用vb​​a)

Question

出于长期原因，我无法使用 SPSS 或其他统计软件包来进行这部分分析。我有两个问题。

有没有办法自动对数据进行排名(我可以稍后分析)。我的变量位于一列(1,2,3,4,5,6....)中，该列已经自行排名。我的另一个变量可以具有 1 到 5 之间的离散值(例如 1 或 2 或 3 或 4 或 5)。尽管在某种程度上这些已经被排名，但它并不是按照斯 PIL 曼想要的方式排名的。我想我可以编写一些 vba 代码来自己对此进行排名，但如果有一种自动方法，那就太好了!

重要的是，我想要计算斯 PIL 曼相关性的唯一原因是它允许我计算一个不因非正态性而产生偏差的 p 值。然而，所有在线指南都告诉我使用表格来查看测试值是否低于 P<.05 或其他的某个水平。如何在 Excel 中根据 N 和 Rho 计算特定的 p 值？我假设某处有一个公式(我在教科书中找不到一个公式!)。

Answer 1

引用Wikipedia Example ，我们可以使用下面的公式来计算自由度 (ν)、Spearman 等级相关系数 (ρ) 和 p 值(使用 t 分布)。

x   y
106 7
86  0
100 27
101 50
99  28
103 29
97  20
113 12
112 6
110 17

ν    8             =COUNT(x)-2
ρ    -0.175757576  =CORREL(RANK.AVG(x,x,1),RANK.AVG(y,y,1))
Pval 0.686405828   =T.DIST(SQRT(ν*ρ^2/(1-ρ^2)),ν,1)

在上面的公式中，已使用定义名称来代替单元格引用。相对于A1，引用为:x=A2:A11、y=B2:B11、ν=B13、ρ=B14。 (可以通过“插入”>“符号”使用希腊字母)

注意:这里假设 RANK.AVG/T.DIST 函数使用 Excel 2010/13(在 VBA 中使用rank_avg/t_dist)