如何匹配Excel公式中的统计百分位数计算？

Question

Stata使用称为R-2 (https://en.wikipedia.org/wiki/Quantile)的分位数计算方法，而Excel使用带有percentile.inc函数的R-7。我的目标是在Excel中找到一个正确的公式，它可以给出与Stata中使用R-2方法的结果相同的结果。

现在，我可以看到percentile.inc只对奇数和离散样本匹配Stata结果(我正在处理离散样本)。但是，即使是samples shown here也会出现此问题

从概念上讲，在Excel中使用percentile.inc似乎是不正确的，因为它是R-7方法，即使它与R-2方法相匹配，用于奇数和离散样本。

我的问题是，在Excel中使用哪个最简单的公式来匹配Stata百分位数结果是正确的？

Answer 1

因此，从R-2到N=4中的Excel的字面翻译如下所示(假设数据经过排序)：

=(INDEX(A$2:A$5,CEILING(C2*4,1))+INDEX(A$2:A$5,FLOOR(C2*4+1,1)))/2

​

​

如果你尝试将分位数设置为0，它确实会出错，所以这必须是一个特例，就像分位数1一样。我假设Stata在这两种情况下都会给出集合中的最低和最高值？

一个包含所有检查的更动态的公式如下所示：

=IFS(OR(C2<0,C2>1),"Out of range",C2=0,A$2,C2=1,INDEX(A:A,COUNT(A:A)+1),TRUE,(INDEX(A$2:INDEX(A:A,COUNT(A:A)+1),CEILING(C2*COUNT(A:A),1))+INDEX(A$2:INDEX(A:A,COUNT(A:A)+1),FLOOR(C2*COUNT(A:A)+1,1)))/2)

尽管您可以使用Microsoft365中的Let构造来缩短它。

在VBA中将此函数实现为函数可能会更好，该函数将对数据进行排序并返回分位数值，当然，您也可以在Microsoft 365公式中进行排序：

=LET(N,COUNT(A:A),sortedRange,SORT(A$2:INDEX(A:A,N+1)),IFS(OR(C2<0,C2>1),"Out of range",C2=0,INDEX(sortedRange,1),C2=1,INDEX(sortedRange,N),TRUE,(INDEX(sortedRange,CEILING(C2*N,1))+INDEX(sortedRange,FLOOR(C2*N+1,1)))/2))